Answer to Question #259684 in Databases | SQL | Oracle | MS Access for Agy

Question

The following stored procedures are required :

a) Return store details showing purchases done on a given date and the current stock of a given product

b) The system should ensure that during the quality check, if the product has a defect, it should be recorded in the

defect table and an appropriate message should be displayed

Answer 1

SOLUTION FOR THE ABOVE QUESTION

While solving this problem, we will assume that we have a table called defect_table with product_name column and table Stores,

with product_name column, purchases column, date column and current_stock column,

 defect_column(having either value 'YES' or 'NO")

The following stored procedures are required :

a) Return store details showing purchases done on a given date and the current stock of a given product

  ANSWER.

//create a procedure called Store_details

//This procedure will display the the product name, the purchases done, 

and the current stock fetched from the Stores table where date is equal to the given one 

CREATE PROCEDURE Store_details

AS

SELECT product_name,purchases,current_stock FROM Stores WHERE date= '2021-11-01'

GO;

b) The system should ensure that during the quality check, if the product has a defect, 

it should be recorded in the defect table and an appropriate message should be displayed

 ANSWER.

//create a procedure called Quality_check

//This procedure will add a product name to the defect table if it finds any defect item from the Stores table

CREATE PROCEDURE Quality_check

AS

SELECT product_name FROM Stores WHERE defect = 'YES'

INSERT INTO defect_table(defect_product_name)

VALUES (product_name );