SQL pros Help please!!
Requirement E: List Customer Orders that are not yet assigned to any employee to fill and assigned to Employee #75 (Frank Pellow) but not filled yet. You need to use the NULL value in Filled Yes No and Employee ID Filled Order as well as Employee ID Filled Order is 75. List Order No, Order Date, Filled Yes No, and Employee ID Filled Order. Format Order Date to 'MM/DD/YYYY' and name the column as shown below. Sort the query result by Order Date and then Order No. Study WorldWidelmporters SQ Practice #1 in the Ch6 & Ch7 module in Canvas to learn how to format Order Date Take the screen shot of the entire monitor, paste it in the Word document, and do not trim any part For Your Knowledge: A similar query can be used to display Unfilled Orders (Mock Design 3 of HW1) "Detail" link on the page contains Order No. When you click the link, the next page will use Order No to display the details of the order . Order #611 was already filled. Order #613 was assigned to Employee 78 (Krishna Sunka m murali) but e. at least one of the ordered products has not been picked up yet by him. Orders 612, 614, and 615 are not yet assigned to anyone. To display the Employee Name, you need to know how joir tblCustomerOrder and tblEmployee. You will write a query that displays them in HW4 Sample Que OrderNo OrderDate FlledYesNo EmployeelDFilledOrder 1609 2 610 3 612 4 614 5 615 02/20/2018 NULL 02/20/2018 NULL 02/20/2018 NULL 02/20/2018 NULL 02/20/2018 NULL 75 75 NULL NULL NULL Employee Name: Frank Pellow Job Tite: Warehouse assistant Unfilled Orders Employee Assigned to Fill Pellow, Frank Pellow, Frank None None None No of Products Order No 609 610 612 614 615 Order Date 02/20/2018 Bike Satellite Inc. 02/20/2018 A4 Sports 02/20/2018 Proseware, Inc 02/20/2018 G&K Bicycle Corp 02/20/2018 GMA Ski & Bike Company Name Detail Detail Detail Detail Detail f. Requirement F: Count how many different products were ordered in each customer order (You need to count the number of records in tblOrderLine for each different Order No). Display only query result for Order No 609, 610, 612, 614, and 615 using the IN command. List Order No and the counted number of records as NoOfProducts. Sort the query result by Order No Take the screen shot of the entire monitor, paste it in the Word document, and do not trim any part. For Your Knowledge: A similar query can be used to display Unfilled Orders (Mock Design 3 of HW1). To display all of the Order No, Order Date, Company Name, Employee Name and No of Products, you need to know how to OUTER join tblCompany, tblCustomerOrder, tblEmployee and tblOrderLine. You will write a query that displays them in HW4 Sample Query result:5 records OrderNo NoOfProducts 1609 3 612 Requirement E: List Customer Orders that are not yet assigned to any employee to fill and assigned to Employee #75 (Frank Pellow) but not filled yet. You need to use the NULL value in Filled Yes No and Employee ID Filled Order as well as Employee ID Filled Order is 75. List Order No, Order Date, Filled Yes No, and Employee ID Filled Order. Format Order Date to 'MM/DD/YYYY' and name the column as shown below. Sort the query result by Order Date and then Order No. Study WorldWidelmporters SQ Practice #1 in the Ch6 & Ch7 module in Canvas to learn how to format Order Date Take the screen shot of the entire monitor, paste it in the Word document, and do not trim any part For Your Knowledge: A similar query can be used to display Unfilled Orders (Mock Design 3 of HW1) "Detail" link on the page contains Order No. When you click the link, the next page will use Order No to display the details of the order . Order #611 was already filled. Order #613 was assigned to Employee 78 (Krishna Sunka m murali) but e. at least one of the ordered products has not been picked up yet by him. Orders 612, 614, and 615 are not yet assigned to anyone. To display the Employee Name, you need to know how joir tblCustomerOrder and tblEmployee. You will write a query that displays them in HW4 Sample Que OrderNo OrderDate FlledYesNo EmployeelDFilledOrder 1609 2 610 3 612 4 614 5 615 02/20/2018 NULL 02/20/2018 NULL 02/20/2018 NULL 02/20/2018 NULL 02/20/2018 NULL 75 75 NULL NULL NULL Employee Name: Frank Pellow Job Tite: Warehouse assistant Unfilled Orders Employee Assigned to Fill Pellow, Frank Pellow, Frank None None None No of Products Order No 609 610 612 614 615 Order Date 02/20/2018 Bike Satellite Inc. 02/20/2018 A4 Sports 02/20/2018 Proseware, Inc 02/20/2018 G&K Bicycle Corp 02/20/2018 GMA Ski & Bike Company Name Detail Detail Detail Detail Detail f. Requirement F: Count how many different products were ordered in each customer order (You need to count the number of records in tblOrderLine for each different Order No). Display only query result for Order No 609, 610, 612, 614, and 615 using the IN command. List Order No and the counted number of records as NoOfProducts. Sort the query result by Order No Take the screen shot of the entire monitor, paste it in the Word document, and do not trim any part. For Your Knowledge: A similar query can be used to display Unfilled Orders (Mock Design 3 of HW1). To display all of the Order No, Order Date, Company Name, Employee Name and No of Products, you need to know how to OUTER join tblCompany, tblCustomerOrder, tblEmployee and tblOrderLine. You will write a query that displays them in HW4 Sample Query result:5 records OrderNo NoOfProducts 1609 3 612
