1 and customer name for customers who order it. Display product - PDF document

Q1: What is the output of the following SQL or PL/SQL code segments SQL Statement OUTPUT Select Cname , Pname, Quantity From Customers C , Products P, Order O 1 Where C.Cid = O.Cid AND P.Pid = O.Pid AND Region = ‘Irbid’; Select Pname From Products MINUS 2 Select Pname From Products P, Order O Where P.Pid = O.Pid; Declare A NUMBER := 2; B varchar2(20); C number; Begin Loop Select Sname, salary INTO B, C From SalesPerson 3 Where Sid = A; A := A + 2; Exit when A<= 5; END loop; DBMS_output.put_line(B); DBMS_output.put_line(C); End; DECLARE product_row Products%ROWTYPE; BEGIN Select Pname, Category INTO product_row.Pname, 4 product_row.Category From Products Where Pid = 6; IF product_row.Category='Cloth' THEN Page 1 CIS 421 Second Exam

DBMS_OUTPUT.PUT_LINE('Product No' || product_row.Pid || ‘ is of type Cloth'); ELSE DBMS_OUTPUT.PUT_LINE('Product No' || to_char(product_row.Pid) || ‘ is NOT of type Cloth'); END IF; END; Q2: Write SQL statements to answer each of the following Problem description For every product, display the product name 1 and customer name for customers who order it. Display product name and category for products 2 which were ordered by customer number 5 Q3: Write a PL/SQL program that will delete all expired items from Products table, the program will also print the names and the categories of all these items Page 2 CIS 421 Second Exam

Q5: Write a PL/SQL program that will Display customer name for customers whose region is ‘Irbid’. Page 3 CIS 421 Second Exam

Consider the following Relational database schema and state when answering the exam questions. Products (Pid, Pname, Pprice$, Category, Product, Expiry) Orders (Cid, Pid, Quantity) SalesPerson (Sid, Sname, Region, Type, Quota$, Salary, Work_hrs, overtime) Customers (Cid, Cname, Region) Products Orders Product Expiry Cid Pid Quantity Pid Pname Pprice$ Category 1 100 1-Nov-11 1 disks Furniture 1 2 2 700 3-Oct-12 2 PCs Office 2 2 800 3-Oct-12 2 3 MACs Office 3 3 5 9-Jun-16 11-Jun-17 3 4 Tuna Food 4 1 25 2-Sep-16 5 Shirt Cloth 1 5 4 15 6-May-16 26-May-16 6 Yogurt Food SalesPerson Type Salary Work_hrs overtime Sid Sname Region Quota$ 1 400 20 1 Ali Irbid 300 2 325 15 5 2 Khalid Amman 500 1 400 20 3 Qusai Zarqa 200 2 250 10 5 4 Ali Aqaba 200 2 75 195 6 5 Luay Maan 250 Customers Cid Cname Region Irbid 1 Mostafa Amman 2 Rehab Zarqa 3 Eyad Irbid 4 Ahmed Maan 5 Samer Page 4 CIS 421 Second Exam