For this homework you will be using the database that you created for Chapter 4 homework II . Your job: Use the JDBC interface to create and send SQL and Prepared SQL statements (as specified) to accomplish the following tasks: (SQL) Query the database to find all orders from Customer with ID=14 (Prepared Statement): Query the database to find all orders from Customer with ID=14 (Prepared Statement): Add two customers CUSTOMER 1 ID = 15 COMPANY=QBC SPECIALTIES LAST NAME=OREN FIRST NAME=SAM EMAIL ADDRESS [email protected] BUSINESS PHONE (561) 989-0989 CUSTOMER 2 ID = 16 COMPANY=JBL SPECIALTIES LAST NAME=RENO FIRST NAME=MAS EMAIL ADDRESS [email protected] BUSINESS PHONE (445) 895-0215 . . . . . ID Email Address Company 1 Company A 2 Company B 3 Company C 4 Company D 5 Company E 6 Company F 7 Company G 8 Company H 9 Company 10 Company 11 Company K 12 Company L 13 Company M 14 Company N Last Name First Name Bedecs Anna Gratacos Salsona Antonio Axen Thomas Lee Christina O'Donnell Martin Perez-Olaeta Francisco Xie Ming Yang Andersen Elizabeth Mortensen Sven Wacker Roland Krschne Peter Edwards John Ludwick Grilo Carlos Job Title Business Phone Owner (123)555-0100 Owner (123)555-0100 Owner (123)555-0100 Purchasing Mananger (123)555-0100 Owner (123)555-0100 Purchasing Mananger (123)566-0100 Owner (123)555-0100 Purchasing Representative (123)555-0100 Purchasing Mananger (123)566-0100 Purchasing Mananger (123)555-0100 Purchasing Mananger (123)555-0100 Purchasing Mananger (123)566-0100 Purchasing Representative (123)555-0100 Purchasing Representative (123)566-0100 Andre 4 Awaldes 2 Thomas 3 01002000 D100.000 ESBEROR 4 6 7 4000 01.09.2008 0402000 . 10 11 DW24000 Supplier ID ID Description 10 10 10 2 2 8 Product Code 1 NWTB-1 3 NWTCO 3 4 NWTCO 4 5 NWTO5 6 NWTJP 6 7 NWTDFN-7 8 NWTS-8 14 NWTDFN 14 17 NWTCFV-17 19 NWTBGM-19 20 NWTJP-6 21 NWTBGM-21 34 NWTB 34 40 NWTCM-40 Product Name Northwind Traders Chai Northwind Traders Syrup Northwind Traders Cajun Seasoning Northwind Traders Olive Oil Northwind Traders Boysenberry Spread Northwind Traders Dried Pears Northwind Traders Curry Sauce Northwind Traders Walnuts Northwind Traders Fruit Cocktail Northwind Traders Chocolate Biscuits Mix Northwind Traders Marmalade Northwind Traders Scones Northwind Traders Beer Northwind Traders Crab Meat Standard Cast List Price Category 135 18 Beverages 75 10 Condiments 16.5 22 Condiments 16.01 21.35 00 18 75 25 Jarns, Preserves 225 30 Dried Fruit & Nuts 30 40 Sauces 17 44 23 25 Dried Fruit & Nuts 29.25 39 Canned Fruit & Vegetables 69 92 Baked Goods & Mixes 60.75 81 Jams, Preserves 75 10 Baked Goods & Mores 105 14 Beverages 138 18.4 Canned Meat 2 6 1 2 1 4 7 10 Purchase Order ID Product ID 10 Quantity Unit Cost 1 14 3 B 54 91 91 91 4 16 240 241 242 243 24 40 100 40 40 100 40 40 40 Date Received Inventory ID 01/22/2006 01/22/2000 01/22/2000 01/22/2000 01/22/2006 01/22/2001 0122/2000 0122/2006 55 56 40 92 92 12 41 19 22 30 7 0 245 14 17 Retrieve the Last Name of the Customer and the OrderID and Status ID of all orders in the Order relation. Print out the Last Name only of Customers that have placed at least one order. Do this with INNER JOIN : SELECT c.LastName, o OrderID, O.StatusID FROM Customers c INNER JOIN Orders o ON C.ID=o.Order ID GROUP BY c.OrderID HAVING COUNT(0.CustomerID>1; Do this with NATURAL JOIN SELECT c.LastName, o OrderID, O.StatusID FROM Customers c NATURAL JOIN Orders o GROUP BY c. CustomerID HAVING COUNT(0OrderD>1; Retrieve the Last Name of the Customer and OrderID an Status ID for all orders in the Order relation AND the Last Name of Customers that have not placed any orders. SELECT c.LastName, o OrderID, o StatusID FROM Customers c LEFT JOIN ORders o ON C.ID=o.OrderID; Retrieve from the Purchase Order Details table: ProductID, Unit Cost and ProductName. Products table: Product Code, InventoryID Only for all products that are in the Purchase Order Details table. SELECT pod.ProductID, pod.UnitCost, p.ProductName, p.ProductCode, pod.InventoryID FROM Purchase Order Details pod INNER JOIN Products p ON p.ID=p.ProductID: Retrieve from the Products table: Product Code, InventoryID Only for all products that are NOT in the Purchase Order Details table. SELECT p.ProductCode, pod. InventoryID FROM Products p PurchaseOrder Details pod WHERE p.ID pod. ProductID AND p.ID NOT IN(SELECT ProductID FROM Purchase Order Details): For this homework you will be using the database that you created for Chapter 4 homework II . Your job: Use the JDBC interface to create and send SQL and Prepared SQL statements (as specified) to accomplish the following tasks: (SQL) Query the database to find all orders from Customer with ID=14 (Prepared Statement): Query the database to find all orders from Customer with ID=14 (Prepared Statement): Add two customers CUSTOMER 1 ID = 15 COMPANY=QBC SPECIALTIES LAST NAME=OREN FIRST NAME=SAM EMAIL ADDRESS [email protected] BUSINESS PHONE (561) 989-0989 CUSTOMER 2 ID = 16 COMPANY=JBL SPECIALTIES LAST NAME=RENO FIRST NAME=MAS EMAIL ADDRESS [email protected] BUSINESS PHONE (445) 895-0215 . . . . . ID Email Address Company 1 Company A 2 Company B 3 Company C 4 Company D 5 Company E 6 Company F 7 Company G 8 Company H 9 Company 10 Company 11 Company K 12 Company L 13 Company M 14 Company N Last Name First Name Bedecs Anna Gratacos Salsona Antonio Axen Thomas Lee Christina O'Donnell Martin Perez-Olaeta Francisco Xie Ming Yang Andersen Elizabeth Mortensen Sven Wacker Roland Krschne Peter Edwards John Ludwick Grilo Carlos Job Title Business Phone Owner (123)555-0100 Owner (123)555-0100 Owner (123)555-0100 Purchasing Mananger (123)555-0100 Owner (123)555-0100 Purchasing Mananger (123)566-0100 Owner (123)555-0100 Purchasing Representative (123)555-0100 Purchasing Mananger (123)566-0100 Purchasing Mananger (123)555-0100 Purchasing Mananger (123)555-0100 Purchasing Mananger (123)566-0100 Purchasing Representative (123)555-0100 Purchasing Representative (123)566-0100 Andre 4 Awaldes 2 Thomas 3 01002000 D100.000 ESBEROR 4 6 7 4000 01.09.2008 0402000 . 10 11 DW24000 Supplier ID ID Description 10 10 10 2 2 8 Product Code 1 NWTB-1 3 NWTCO 3 4 NWTCO 4 5 NWTO5 6 NWTJP 6 7 NWTDFN-7 8 NWTS-8 14 NWTDFN 14 17 NWTCFV-17 19 NWTBGM-19 20 NWTJP-6 21 NWTBGM-21 34 NWTB 34 40 NWTCM-40 Product Name Northwind Traders Chai Northwind Traders Syrup Northwind Traders Cajun Seasoning Northwind Traders Olive Oil Northwind Traders Boysenberry Spread Northwind Traders Dried Pears Northwind Traders Curry Sauce Northwind Traders Walnuts Northwind Traders Fruit Cocktail Northwind Traders Chocolate Biscuits Mix Northwind Traders Marmalade Northwind Traders Scones Northwind Traders Beer Northwind Traders Crab Meat Standard Cast List Price Category 135 18 Beverages 75 10 Condiments 16.5 22 Condiments 16.01 21.35 00 18 75 25 Jarns, Preserves 225 30 Dried Fruit & Nuts 30 40 Sauces 17 44 23 25 Dried Fruit & Nuts 29.25 39 Canned Fruit & Vegetables 69 92 Baked Goods & Mixes 60.75 81 Jams, Preserves 75 10 Baked Goods & Mores 105 14 Beverages 138 18.4 Canned Meat 2 6 1 2 1 4 7 10 Purchase Order ID Product ID 10 Quantity Unit Cost 1 14 3 B 54 91 91 91 4 16 240 241 242 243 24 40 100 40 40 100 40 40 40 Date Received Inventory ID 01/22/2006 01/22/2000 01/22/2000 01/22/2000 01/22/2006 01/22/2001 0122/2000 0122/2006 55 56 40 92 92 12 41 19 22 30 7 0 245 14 17 Retrieve the Last Name of the Customer and the OrderID and Status ID of all orders in the Order relation. Print out the Last Name only of Customers that have placed at least one order. Do this with INNER JOIN : SELECT c.LastName, o OrderID, O.StatusID FROM Customers c INNER JOIN Orders o ON C.ID=o.Order ID GROUP BY c.OrderID HAVING COUNT(0.CustomerID>1; Do this with NATURAL JOIN SELECT c.LastName, o OrderID, O.StatusID FROM Customers c NATURAL JOIN Orders o GROUP BY c. CustomerID HAVING COUNT(0OrderD>1; Retrieve the Last Name of the Customer and OrderID an Status ID for all orders in the Order relation AND the Last Name of Customers that have not placed any orders. SELECT c.LastName, o OrderID, o StatusID FROM Customers c LEFT JOIN ORders o ON C.ID=o.OrderID; Retrieve from the Purchase Order Details table: ProductID, Unit Cost and ProductName. Products table: Product Code, InventoryID Only for all products that are in the Purchase Order Details table. SELECT pod.ProductID, pod.UnitCost, p.ProductName, p.ProductCode, pod.InventoryID FROM Purchase Order Details pod INNER JOIN Products p ON p.ID=p.ProductID: Retrieve from the Products table: Product Code, InventoryID Only for all products that are NOT in the Purchase Order Details table. SELECT p.ProductCode, pod. InventoryID FROM Products p PurchaseOrder Details pod WHERE p.ID pod. ProductID AND p.ID NOT IN(SELECT ProductID FROM Purchase Order Details)