Q.1 List order number (ORDER_CODE), order date (ORDDATETIME) and the customer's last name (CUS_LNAME) for all orders, including the orders which contain no customer names.

Q.2 For a given customer order number, say '333111', list all the order product description (P_DESCRIPT), price (P_PRICE) and the corresponding vendor name (V_NAME) if available.

/* This script will completely update/reload the "product ordering database" required by Quesiton beta of the Additional Exercises in Practical 10. It will first remove these tables, and then reconstruct the tables and fill in the data. If these tables (order_detail,product,vendor,cus_order,customer) have not been constructed yet, then just comment off the next 2 "drop" statements (by adding "--" in front of the "drop" word) */ -- reconstruct the tables made in Practical 7 -- with minor modifications to allow such as NULL values -- for certain fields (for testing OUTER JOINs) drop table order_detail, product; drop table vendor, cus_order, customer; CREATE TABLE VENDOR ( V_CODE NUMERIC(5) NOT NULL, V_NAME VARCHAR(35) NOT NULL, V_CONTACT VARCHAR(15) NOT NULL, V_AREACODE VARCHAR(3) NOT NULL, V_PHONE VARCHAR(8) NOT NULL, V_STATE VARCHAR(3) NOT NULL, V_ORDER VARCHAR(1) NOT NULL, CONSTRAINT VENDOR_PK PRIMARY KEY (V_CODE)); CREATE TABLE PRODUCT ( P_CODE varchar(10) CONSTRAINT PRODUCT_P_CODE_PK PRIMARY KEY, P_DESCRIPT varchar(35), -- NULL now allowed P_INDATETIME DATETIME NOT NULL, -- total number of products current in stock P_ONHAND NUMERIC(4) NOT NULL, -- recommended minimum number of products to be sold in each order P_MIN NUMERIC(4) NOT NULL, P_PRICE NUMERIC(8,2) NOT NULL, P_DISCOUNT NUMERIC(4,2) NOT NULL CHECK (P_DISCOUNT >= 0 AND P_DISCOUNT < 1), V_CODE NUMERIC(5), CONSTRAINT PRODUCT_V_CODE_FK FOREIGN KEY (V_CODE) REFERENCES VENDOR); CREATE TABLE CUSTOMER ( CUS_CODE NUMERIC(6) CONSTRAINT CUSTOMER_PK PRIMARY KEY, CUS_LNAME varchar(15) NOT NULL, CUS_FNAME varchar(15) NOT NULL, CUS_INITIAL CHAR(1), CUS_AREACODE VARCHAR(3), -- NULL now allowed CUS_PHONE VARCHAR(8) NOT NULL, CUS_BALANCE NUMERIC(9,2) DEFAULT 0.00); CREATE TABLE CUS_ORDER ( ORDER_CODE NUMERIC(6) CONSTRAINT ORDER_PK PRIMARY KEY, CUS_CODE NUMERIC(6) -- NULL now allowd CONSTRAINT CUS_ORDER_FK FOREIGN KEY(CUS_CODE) REFERENCES CUSTOMER, ORDDATETIME DATETIME); CREATE TABLE ORDER_DETAIL( ORDER_CODE NUMERIC(6), PRODUCT_CODE VARCHAR(10), QUANTITY NUMERIC(4) DEFAULT 1 CHECK (QUANTITY >=0), CONSTRAINT ORD_DETAIL_PK PRIMARY KEY(ORDER_CODE,PRODUCT_CODE), CONSTRAINT ORD_DETAIL_FK1 FOREIGN KEY (ORDER_CODE) REFERENCES CUS_ORDER(ORDER_CODE), CONSTRAINT ORD_DETAIL_FK2 FOREIGN KEY (PRODUCT_CODE) REFERENCES PRODUCT(P_CODE)); -- insert the basic records used in Practical 7 SET NOCOUNT ON INSERT INTO Vendor VALUES (21225,'Bryson, Inc.','Stan Smithson','02','223-3234','NSW','Y'); INSERT INTO Vendor VALUES (21226,'Superloo, Inc.','Mary Flushing','02','215-8995','NSW','N'); INSERT INTO Vendor VALUES (21227,'Blackwell','Bob Jones','02','215-9999','NSW','N'); INSERT INTO Vendor VALUES (21999,'ABC Victoria','Ali Nasour','03','662-8789','VIC','N'); INSERT INTO Vendor VALUES (22333,'Stewart Brothers','Yan Wong','03','445-8888','VIC','N'); INSERT INTO Vendor VALUES (66777,'Buchner Pty Ltd','Stu Buchner','02','215-4444','NSW','N'); INSERT INTO Vendor VALUES (33322,'Blue Seas','Lee','03','395-8995','VIC','Y'); INSERT INTO Product VALUES ('11QER/31','Power painter, 15 psi., 3-nozzle','03-NOV-03',8,5,109.99,0.00,21225); INSERT INTO Product VALUES ('13-Q2/P2','7.25-in. pwr. saw blade','13-DEC-03',32,15,14.99, 0.05,22333); INSERT INTO Product VALUES ('BRT-345','Titanium drill bit', '18-OCT-02', 75, 10, 4.50, 0.06, 21225); INSERT INTO Product VALUES ('13-Q3/P4','10.5-in. pwr. saw blade','13-DEC-03',3,15,54.00, 0.05,22333); INSERT INTO Product VALUES ('LZQ202','10.5-in. pwr. saw blade','13-NOV-03',3,15,54.00, 0.05,66777); INSERT INTO Product VALUES ('PB101','professional paintbrush pack','11-DEC-03',71,15,11.50, 0.05,66777); INSERT INTO Customer VALUES (555555, 'Jackson', 'Suzanne', 'S', '07', '12345678', 5523.76); INSERT INTO Customer VALUES (888888, 'Hazal', 'Ali', 'A','08', '98989898', 0.0); INSERT INTO Customer VALUES (333333, 'Wang', 'Phan', 'P','08', '56565656', 10.05); INSERT INTO Customer VALUES (111111, 'Tudor-Smith', 'Toby', 'T','03', '98989898', 978.45); INSERT INTO Cus_order VALUES (444555, 888888, '23-SEP-05'); INSERT INTO Cus_order VALUES (444333, 888888, '24-SEP-05'); INSERT INTO Cus_order VALUES (333111, 888888, '23-SEP-05'); INSERT INTO Cus_order VALUES (555555, 333333, '23-SEP-05'); INSERT INTO Cus_order VALUES (999999, 333333, '20-SEP-05'); INSERT INTO ORDER_DETAIL VALUES (999999, 'LZQ202', 10); -- not all in stock INSERT INTO ORDER_DETAIL VALUES (999999, 'BRT-345', 15); INSERT INTO ORDER_DETAIL VALUES (999999, 'PB101', 20); INSERT INTO ORDER_DETAIL VALUES (555555, 'BRT-345',20); INSERT INTO ORDER_DETAIL VALUES (555555, 'PB101', 20); INSERT INTO ORDER_DETAIL VALUES (333111, 'BRT-345', 25); INSERT INTO ORDER_DETAIL VALUES (333111, '11QER/31', 5); SET NOCOUNT OFF -- insert additional records SET NOCOUNT ON INSERT INTO Product VALUES ('T-101', 'Impact Drill', '03-NOV-03',8,5,109.99,0.00,NULL); INSERT INTO Cus_order VALUES (111666, NULL, '25-SEP-05'); INSERT INTO ORDER_DETAIL VALUES (111666, 'LZQ202', 1); INSERT INTO ORDER_DETAIL VALUES (111666, 'T-101', 1); INSERT INTO ORDER_DETAIL VALUES (333111, 'T-101', 1); SET NOCOUNT OFF