mysql database

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 ?

Submission Guidelines