Q.1 Pick a specific customer CUS_CODE, list the names of all the vendors who ever supplied parts to this customer. Do not repeat the vendor names in the result.

Q.2 List all the customer order (ORDER_CODE) and the corresponding total cost for each order.

Expecting in SQL server management studion or lite not anything else

/* 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) NOT NULL, 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) DEFAULT '02' NOT NULL CHECK(CUS_AREACODE IN ('03','07','08')), 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) NOT NULL /* 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)); 

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