Lab Schedule

#
Date
Description
Lab1
28 March 2022Description of Term Project and Introduction to Oracle Live SQL
Lab2
4 April 2022

Table Design and construction for Term Project

Complete subtasks in Lab2

Lab311 April 2022

Table Design and construction for Term Project

Complete subtasks in Lab3

Lab49 May 2022

Table Design and construction for Term Project

Complete subtasks in Lab4

Lab5
23 May 2022

Table Design and construction for Term Project

Complete subtasks in Lab5

Lab630 May 2022
Finalization of the Term Project


Lab Materials:

Lab 1  (LiveSQL)

For CMPE353  Lab 2-6 (Online Book Store)  

https://livesql.oracle.com

Create Table

Script For the First Lab

.https://livesql.oracle.com/apex/livesql/s/nar66so12ivc74wnaucg2t4fn

.link

Loop in oracle

Function1  Function2

Script

last updated: 28/March/2022

For testing the trigger:

SELECT * FROM OrderList

INSERT INTO OrderItems VALUES (2,5,9)

SELECT * FROM OrderList




CREATE TRIGGER update_tri9 AFTER UPDATE ON orderitems 
for each row 
 
DECLARE 
TrnsTyp VARCHAR2(10); 
sum_t_p number; 
BEGIN 
   TrnsTyp := CASE   
         WHEN UPDATING THEN 'UPDATE' 
         WHEN DELETING THEN 'DELETE' 
   END; 
    
select sum(total_price) into sum_t_p 
from orderlist natural inner join orderitems; 
    
update orderList         
set total_price = sum_t_p   
WHERE order_ID  =:new.order_ID; 
 
END; 


CREATE TRIGGER update_orderlist

AFTER INSERT OR UPDATE OR DELETE ON OrderItems BEGIN UPDATE OrderList SET TotalPrice =

NVL((SELECT SUM(Count * BookPrice) FROM OrderItems

NATURAL JOIN Books WHERE OrderID = Orderlist.OrderID),0); END;