PL/SQL project. please show me all steps. Run the script, C9_BBcreate.sql, to cr

Question

PL/SQL project. please show me all steps.

Run the script, C9_BBcreate.sql, to create the tables required for this lab. Create a table named BB_SALES_SUM that holds the product ID, total sales in dollars, and the total quantity sold for each product. Create a trigger named BB_SALESUM_TRG that updates the BB_SALES_SUM accordingly when an order is confirmed or the ORDERPLACED column in the BB_BASKET table is updated to 1. Test your trigger with the following basket IDs: 14, 15, and 16. Create a folder named, fullname_lab11. Copy your source codes and the output file to the folder. Zip the folder and upload it to Blackboard.

Explanation / Answer

CREATE OR REPLACE TRIGGER BB_SALESUM_TRG AFTER UPDATE OF ORDERED ON BB_BASKET FOR EACH ROW WHEN (new.ordered = 1) BEGIN INSERT INTO bb_sales_sum(product_id, tot_sales, tot_qty number) SELECT product_id, sum(b.total), sum(b.quantity) FROM bb_basket b INNER JOIN bb_basketitem bi ON b.basket_id = bi.basket_id WHERE b.basketid = :new.basket_id GROUP BY product_id END;

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.