DROP TABLE OrderLine_T CASCADE CONSTRAINTS ; DROP TABLE Product_T CASCADE CONSTRAINTS ; DROP TABLE Order_T CASCADE CONSTRAINTS ; DROP TABLE Customer_T CASCADE CONSTRAINTS ; CREATE TABLE Customer_T (CustomerID NUMBER(11,0) NOT NULL, CustomerName VARCHAR2(25) NOT NULL, CustomerAddress VARCHAR2(30) , CustomerCity VARCHAR2(20) , CustomerState CHAR(2) , CustomerPostalCode VARCHAR2(10) , CONSTRAINT Customer_PK PRIMARY KEY (CustomerID)); CREATE TABLE Order_T (OrderID NUMBER(11,0) NOT NULL, CustomerID NUMBER(11,0) , OrderDate DATE DEFAULT SYSDATE , CONSTRAINT Order_PK PRIMARY KEY (OrderID), CONSTRAINT Order_FK1 FOREIGN KEY (CustomerID) REFERENCES Customer_T(CustomerID)); CREATE TABLE Product_T (ProductID NUMBER(11,0) NOT NULL, ProductDescription VARCHAR2(50) , ProductFinish VARCHAR2(20) , ProductStandardPrice DECIMAL(6,2) , CONSTRAINT Product_PK PRIMARY KEY (ProductID)); CREATE TABLE OrderLine_T (OrderID NUMBER(11,0) NOT NULL, ProductID NUMBER(11,0) NOT NULL, OrderedQuantity NUMBER(11,0) , CONSTRAINT OrderLine_PK PRIMARY KEY (OrderID, ProductID), CONSTRAINT OrderLine_FK1 FOREIGN KEY (OrderID) REFERENCES Order_T(OrderID), CONSTRAINT OrderLine_FK2 FOREIGN KEY (ProductID) REFERENCES Product_T(ProductID)); delete from OrderLine_T; delete from Product_T; delete from Order_T; delete from Customer_T; INSERT INTO Customer_T (CustomerID, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode) VALUES (1, 'Contemporary Casuals', '1355 S Hines Blvd', 'Gainesville', 'FL', '32601-2871');

INSERT INTO Customer_T (CustomerID, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode) VALUES (2, 'Value Furniture', '15145 S.W. 17th St.', 'Plano', 'TX', '75094-7743'); INSERT INTO Customer_T (CustomerID, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode) VALUES (3, 'Home Furnishings', '1900 Allard Ave.', 'Albany', 'NY', '12209-1125'); INSERT INTO Customer_T (CustomerID, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode) VALUES (4, 'Eastern Furniture', '1925 Beltline Rd.', 'Carteret', 'NJ', '07008-3188'); INSERT INTO Customer_T (CustomerID, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode) VALUES (5, 'Impressions', '5585 Westcott Ct.', 'Sacramento', 'CA', '94206-4056'); INSERT INTO Customer_T (CustomerID, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode) VALUES (6, 'Furniture Gallery', '325 Flatiron Dr.', 'Boulder', 'CO', '80514-4432'); INSERT INTO Customer_T (CustomerID, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode) VALUES (7, 'Period Furniture', '394 Rainbow Dr.', 'Seattle', 'WA', '97954-5589'); INSERT INTO Customer_T (CustomerID, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode) VALUES (8, 'California Classics', '816 Peach Rd.', 'Santa Clara', 'CA', '96915-7754'); INSERT INTO Customer_T (CustomerID, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode) VALUES (9, 'M and H Casual Furniture', '3709 First Street', 'Clearwater', 'FL', '34620-2314'); INSERT INTO Customer_T (CustomerID, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode) VALUES (10, 'Seminole Interiors', '2400 Rocky Point Dr.', 'Seminole', 'FL', '34646-4423'); INSERT INTO Customer_T (CustomerID, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode) VALUES (11, 'American Euro Lifestyles', '2424 Missouri Ave N.', 'Prospect Park', 'NJ', '07508-5621'); INSERT INTO Customer_T (CustomerID, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode) VALUES (12, 'Battle Creek Furniture', '345 Capitol Ave. SW', 'Battle Creek', 'MI', '49015-3401'); INSERT INTO Customer_T (CustomerID, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode) VALUES (13, 'Heritage Furnishings', '66789 College Ave.', 'Carlisle', 'PA', '17013-8834'); INSERT INTO Customer_T (CustomerID, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode) VALUES (14, 'Kaneohe Homes', '112 Kiowai St.', 'Kaneohe', 'HI', '96744- 2537'); INSERT INTO Customer_T (CustomerID, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode)

VALUES (15, 'Mountain Scenes', '4132 Main Street', 'Ogden', 'UT', '84403- 4432'); INSERT INTO Order_T (OrderID, OrderDate, CustomerID) VALUES (1001, '21/Oct/18', 1); INSERT INTO Order_T (OrderID, OrderDate, CustomerID) VALUES (1002, '21/Oct/18', 8); INSERT INTO Order_T (OrderID, OrderDate, CustomerID) VALUES (1003, '22/Oct/18', 15); INSERT INTO Order_T (OrderID, OrderDate, CustomerID) VALUES (1004, '22/Oct/18', 5); INSERT INTO Order_T (OrderID, OrderDate, CustomerID) VALUES (1005, '24/Oct/18', 3); INSERT INTO Order_T (OrderID, OrderDate, CustomerID) VALUES (1006, '24/Oct/18', 2); INSERT INTO Order_T (OrderID, OrderDate, CustomerID) VALUES (1007, '27/Oct/18', 11); INSERT INTO Order_T (OrderID, OrderDate, CustomerID) VALUES (1008, '30/Oct/18', 12); INSERT INTO Order_T (OrderID, OrderDate, CustomerID) VALUES (1009, '05/Nov/18', 4); INSERT INTO Order_T (OrderID, OrderDate, CustomerID) VALUES (1010, '05/Nov/18', 1); INSERT INTO Product_T (ProductID, ProductDescription, ProductFinish, ProductStandardPrice) VALUES (1, 'End Table', 'Cherry', 175); INSERT INTO Product_T (ProductID, ProductDescription, ProductFinish, ProductStandardPrice) VALUES (2, 'Coffee Table', 'Natural Ash', 200); INSERT INTO Product_T (ProductID, ProductDescription, ProductFinish, ProductStandardPrice) VALUES (3, 'Computer Desk', 'Natural Ash', 375); INSERT INTO Product_T (ProductID, ProductDescription, ProductFinish, ProductStandardPrice) VALUES (4, 'Entertainment Center', 'Natural Maple', 650); INSERT INTO Product_T (ProductID, ProductDescription, ProductFinish, ProductStandardPrice) VALUES (5, 'Writers Desk', 'Cherry', 325); INSERT INTO Product_T (ProductID, ProductDescription, ProductFinish, ProductStandardPrice) VALUES (6, '8-Drawer Desk', 'White Ash', 750); INSERT INTO Product_T (ProductID, ProductDescription, ProductFinish, ProductStandardPrice) VALUES (7, 'Dining Table', 'Natural Ash', 800); INSERT INTO Product_T (ProductID, ProductDescription, ProductFinish, ProductStandardPrice) VALUES (8, 'Computer Desk', 'Walnut', 250); INSERT INTO OrderLine_T (OrderID, ProductID, OrderedQuantity)

VALUES (1001, 1, 2); INSERT INTO OrderLine_T (OrderID, ProductID, OrderedQuantity) VALUES (1001, 2, 2); INSERT INTO OrderLine_T (OrderID, ProductID, OrderedQuantity) VALUES (1001, 4, 1); INSERT INTO OrderLine_T (OrderID, ProductID, OrderedQuantity) VALUES (1002, 3, 5); INSERT INTO OrderLine_T (OrderID, ProductID, OrderedQuantity) VALUES (1003, 3, 3); INSERT INTO OrderLine_T (OrderID, ProductID, OrderedQuantity) VALUES (1004, 6, 2); INSERT INTO OrderLine_T (OrderID, ProductID, OrderedQuantity) VALUES (1004, 8, 2); INSERT INTO OrderLine_T (OrderID, ProductID, OrderedQuantity) VALUES (1005, 4, 3); INSERT INTO OrderLine_T (OrderID, ProductID, OrderedQuantity) VALUES (1006, 4, 1); INSERT INTO OrderLine_T (OrderID, ProductID, OrderedQuantity) VALUES (1006, 5, 2); INSERT INTO OrderLine_T (OrderID, ProductID, OrderedQuantity) VALUES (1006, 7, 2); INSERT INTO OrderLine_T (OrderID, ProductID, OrderedQuantity) VALUES (1007, 1, 3); INSERT INTO OrderLine_T (OrderID, ProductID, OrderedQuantity) VALUES (1007, 2, 2); INSERT INTO OrderLine_T (OrderID, ProductID, OrderedQuantity) VALUES (1008, 3, 3); INSERT INTO OrderLine_T (OrderID, ProductID, OrderedQuantity) VALUES (1008, 8, 3); INSERT INTO OrderLine_T (OrderID, ProductID, OrderedQuantity) VALUES (1009, 4, 2); INSERT INTO OrderLine_T (OrderID, ProductID, OrderedQuantity) VALUES (1009, 7, 3); INSERT INTO OrderLine_T (OrderID, ProductID, OrderedQuantity) VALUES (1010, 8, 10); describe OrderLine_T; describe Product_T; describe Order_T; describe Customer_T; select * from OrderLine_T; select * from Product_T; select * from Order_T; select * from Customer_T; COMMIT;

PART A - Working with a Data Set in Oracle SQL Live (Guided) Run the data set in Oracle live SQL. A error might occur on the drop statements at the start of the execution but ignore that. Reason: The first lines of the file (DROP xxx ) are put there to in case you run the program more than once make sure that all tables are visible and contain data. Once you have the data set up and running you will find a number of tables. PART B - Answer the following questions B1. Briefly describe each of those tables giving its name, attributes and purpose (1 point) B2. Using the data available answer the following question: Which customers bought a natural ash computer desk? (1.5 points) In order to get this info you will need to: 2.1 Proceed to join/merge the tables before extracting any useful information. Join the tables by giving them the following names : CUSTOMER_T CUS ORDER_T ORD ORDERLINE_T ORL Look for the matching criteria and join them using those criteria. Provide a copy of the SQL query you have used for this purpose as the answer for this section. 2.2. Now we have effectively a single table of all the data. State the SQL query related to select customers that bought natural ash computer desks. ( Hint: PRODUCTID of natural ash computer desk is 3 ) 2.3 Finally, provide the results of the query in 2.2 as your answer for this section plus a pic of the table. B3. Using joints and/or conditionals (1.5 point) 3.1 Do a query in the same tables of section 2 to find all products made in cherry and all products made in walnut. 3. 2. Submit your query and table (pic) in one file Once you finish do not forget to upload your answers to Canvas before the end of the lab