Location : Noida (UP) India
Write a backup utility which takes a backup of a given set of folders to a USB drive on your local machine.
The utility should have a configuration file where the user can specify the folders to be backed up, as well as the path to the USB drive.
Make the utility capable of being run over and over without error, as many times during the day as needed.
To submit your work send your running code in text file and mail to careers@saralweb.com with subject "DevOps Assignment - Dec 2022" .
Candidates will be considered in the order of assignment submission. Please submit your assignment as soon as possible.
Location : Noida (UP) India
Education : Graduate or above in computer science / engineering from a recognized institute
Business applications are often modeled as transactional systems, where each transaction performs a specific action (usually update) upon a database with a defined schema.
Further, most transactions have a form type user interface, where the user fills in desired data and hits a button to send the data to the server. Validations may be performed upon the data submitted by the user before sending it to the server.
Given below is a simple example schema for an inventory database:
create table products (
id integer,
name text,
PRIMARY KEY(id)
);
create table customers (
id integer,
name text,
address1 text,
address2 text,
email_id text,
phone text,
PRIMARY KEY(id)
);
create table suppliers (
id integer,
name text,
address1 text,
address2 text,
email_id text,
phone text,
PRIMARY KEY(id)
);
create table sales (
id integer,
customer_id integer,
date date,
PRIMARY KEY(id),
CONSTRAINT fkey_customer
FOREIGN KEY(customer_id)
REFERENCES customers(id)
);
create table sales_items(
sales_id integer,
product_id integer,
qty integer,
sale_price integer,
CONSTRAINT fkey_sales
FOREIGN KEY(sales_id)
REFERENCES sales(id),
CONSTRAINT fkey_products
FOREIGN KEY(product_id)
REFERENCES products(id)
);
create table purchases (
id integer,
vendor_id integer,
date date,
PRIMARY KEY(id),
CONSTRAINT fkey_vendor
FOREIGN KEY(vendor_id)
REFERENCES suppliers(id)
);
create table purchase_items (
purchase_id integer,
product_id integer,
qty integer,
purchase_price integer,
CONSTRAINT fkey_purchase
FOREIGN KEY(purchase_id)
REFERENCES purchases(id),
CONSTRAINT fkey_products
FOREIGN KEY(product_id)
REFERENCES products(id)
);
insert into products values (101, 'product1');
insert into products values (102, 'product2');
insert into products values (103, 'product3');
insert into products values (104, 'product4');
insert into customers values (201, 'customer1', 'noida', 'delhi', 'customer1@xyz.com', '0123456');
insert into customers values (202, 'customer2', 'faridabad', 'gurugram', 'customer2@xyz.com', '0159753');
insert into customers values (203, 'customer3', 'gurugram', 'delhi', 'customer3@xyz.com', '0147852');
insert into suppliers values (301, 'supplier1', 'ghaziabad', 'noida', 'supplier1@xyz.com', '0128452');
insert into suppliers values (302, 'supplier2', 'sonipat', 'gurugram', 'supplier2@xyz.com', '0179793');
insert into suppliers values (303, 'supplier3', 'surat', 'nagpur', 'supplier3@xyz.com', '0117855');
This simple database keeps a record of all sales and purchases by customer and vendor, updating the relevant tables as transactions are done.
NOTE - The schema dump provided is compatible with PostgreSQL database, You can change schema according to database type.
Question 1
Write stored procedures in SQL to implement the purchase and sale transactions.
The purchase transaction inputs are : vendor_id, date, product_id, qty,
purchase_price ...]
// more than one product can be purchased at a time from a vendor
The purchase transaction creates a record of the purchase in appropriate tables.
The sale transaction inputs are : customer_id, date, product_id, qty, sale_price ...]
// more than one product can be sold at a time to a customer.
The sale transaction creates a record of the sale in appropriate tables.
You can assume a given set of products, customers, and vendors and make sample data on your own to test the transactions.
Submit a snapshot of your database with sample data, and the results of executing sale and purchase transactions.
Question 2
Write a stored procedure to generate a stock report for a date range.
select stock_report (date1 date, date2 date);
This report shows the following columns
product_id product_name opening_balance qty_in qty_out qty_net closing_balance_date2
The report calculates opening balances from "beginning of time" to date1, then computes qty_in and qty_out using purchases and sales respectively, then calculates qty_net by qty_in and qty_out between date1 and date2 and finally computes the closing balance on date2.
You may need to write multiple sql statements inside the body of the stock_report stored procedure.
You can use any language for database programming, including PLV8 (embeddeded javascript), PL/SQL or any other embedded language embedded in a SQL server.
Bonus : Can you make the report more efficient ?
To submit your work send your running code in text file and mail to careers@saralweb.com with subject "SDE Assignment - Dec 2022" .
Candidates will be considered in the order of assignment submission. Please submit your assignment as soon as possible.