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

Page 1 CIS 421 Second Exam

Q1: What is the output of the following SQL or PL/SQL code segments

SQL Statement OUTPUT

1 Select Cname , Pname, Quantity From Customers C , Products P, Order O Where C.Cid = O.Cid AND P.Pid = O.Pid AND Region = ‘Irbid’; 2 Select Pname From Products MINUS Select Pname From Products P, Order O Where P.Pid = O.Pid; 3 Declare A NUMBER := 2; B varchar2(20); C number; Begin Loop Select Sname, salary INTO B, C From SalesPerson Where Sid = A; A := A + 2; Exit when A<= 5; END loop; DBMS_output.put_line(B); DBMS_output.put_line(C); End; 4 DECLARE product_row Products%ROWTYPE; BEGIN Select Pname, Category INTO product_row.Pname, product_row.Category From Products Where Pid = 6; IF product_row.Category='Cloth' THEN

Page 2 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

1

For every product, display the product name and customer name for customers who order it.

2

Display product name and category for products 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 3 CIS 421 Second Exam

Q5: Write a PL/SQL program that will Display customer name for customers whose region is ‘Irbid’.

Page 4 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)

Pid Pname Pprice$ Category Product Expiry 1 disks 100 Furniture 1-Nov-11 2 PCs 700 Office 3-Oct-12 3 MACs 800 Office 3-Oct-12 4 Tuna 5 Food 9-Jun-16 11-Jun-17 5 Shirt 25 Cloth 2-Sep-16 6 Yogurt 15 Food 6-May-16 26-May-16

Products

Cid Pid Quantity 1 2 1 2 2 2 3 3 2 4 1 3 5 4 1

Orders SalesPerson

Sid Sname Region Type Quota$ Salary Work_hrs

• vertime

1 Ali Irbid 1 300 400 20 2 Khalid Amman 2 500 325 15 5 3 Qusai Zarqa 1 200 400 20 4 Ali Aqaba 2 200 250 10 5 5 Luay Maan 2 250 75 195 6

Customers

Cid Cname Region 1 Mostafa Irbid 2 Rehab Amman 3 Eyad Zarqa 4 Ahmed Irbid 5 Samer Maan