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', 'firstname.lastname@example.org', '0123456'); insert into customers values (202, 'customer2', 'faridabad', 'gurugram', 'email@example.com', '0159753'); insert into customers values (203, 'customer3', 'gurugram', 'delhi', 'firstname.lastname@example.org', '0147852'); insert into suppliers values (301, 'supplier1', 'ghaziabad', 'noida', 'email@example.com', '0128452'); insert into suppliers values (302, 'supplier2', 'sonipat', 'gurugram', 'firstname.lastname@example.org', '0179793'); insert into suppliers values (303, 'supplier3', 'surat', 'nagpur', 'email@example.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.
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.
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.
Bonus : Can you make the report more efficient ?
To submit your work send your running code in text file and mail to firstname.lastname@example.org with subject "SDE Assignment - Nov 2022" .
Candidates will be considered in the order of assignment submission. Please submit your assignment as soon as possible.