8. For every manager (i. e., group on EMP_MGR), display the manager employee code (EMP_MGR) along...

  

Transcribed Image Text:

8. For every manager (i. e., group on EMP_MGR), display the manager employee code (EMP_MGR) along with the total number (i. e., use COUNT() here) of employees s/he manages (requires a WHERE clause checking all EMP_MGRs that are not null). [hint: you should have 3 rows of output]. 9. Display the product description, the full customer name and the customer balance for products ordered by customers having a customer balance between $200.00 and $400 (inclusive). Order by customer first name, then last name, then initial. Look at the table layouts above and determine which tables to include here. You will need to use all tables and join those appropriately in the WHERE clause using PK/FK pairs (3 joins). The BETWEEN keyword should be used in the WHERE clause for the proper balances [hint: you should have 4 rows of output ordered properly]. 10. For every invoice, display the invoice number, invoice date and the total dollar amount for all products purchased in the invoice, ordered by invoice number in descending order and then by invoice date in ascending order. Here you will use an aggregate SUM(x,y) function in your SELECT clause to calculate the total dollar amount (a calculated field-name it with the alias TOTAL). Don't forget to join on the tables as well. [hint: you should have 8 rows of output ordered properly]. 11. Write SQL queries that use a set operation to display the UNION of the first and last names in table CUSTOMER and the first and last names in table EMPLOYEE. Include duplicates in the output [hint: you should have 27 rows of output]. 12. Create a SQL query that uses an uncorrelated subquery and no joins to display the descriptions for products provided by a vendor in area code 904. Remember, the main difference between a correlated subquery and uncorrelated subquery is that, in a correlated subquery, the inner query is dependent upon the outer query, while, with an uncorrelated query, the inner query does not depend upon the outer query. Instead, it runs just once. You should NOT use union, minus, or intersect in this query. The outer query will use the PRODUCT table and the inner query will use the VENDOR table [hint: you should have 3 records of output]. INV_NUMBER LINE_NUMBER P_CODE LINE_UNITS 1 13-Q2/P2 2 23109-HB 1 54778-2T 1 2238/QPD 2 1546-QQ2 1001 1001 1002 1003 1003 1003 1004 1004 1005 1006 1006 1006 1006 1007 1007 1008 1008 1008 INV_NUMBER CUS_CODE INV_DATE 10014 1/16/2019 10011 1/16/2019 1/16/2019 10012 10011 1/17/2019 10018 1/17/2019 10014 1/17/2019 10015 1/17/2019 10011 1/17/2019 1001 1002 1003 1004 1005 1006 1007 1008 CUS_CODE CUS_LNAME 10010 Ramas 10011 Dunne 10012 Smith 10013 Olowski 10014 Orlando 10015 O'Brian 10016 Brown 10017 Williams 10018 Farriss 10019 Smith 3 13-Q2/P2 1 54778-2T 2 23109-HB 1 PVC23DRT 1 SM-18277 2 2232/QTY 3 23109-HB 4 89-WRE-Q 1 13-Q2/P2 2 54778-2T 1 PVC23DRT 2 WR3/TT3 3 23109-HB Myron Amy James George Anne Olivette A K W F - CUS_FNAME CUS_INITIAL CUS_AREACODE CUS_PHONE CUS_BALANCE Alfred 615 844-2573 713 894-1238 Leona Kathy 615 894-2285 Paul 615 894-2180 615 222-1672 713 442-3381 615 297-1228 615 290-2556 713 382-7185 615 297-3809 B G 1 1 2 1 1 5 3 2 12 3 1 1 1 2 1 5 3 1 G K LINE_PRICE 14.99 9.95 4.99 38.95 39.95 14.99 4.99 9.95 5.87 6.99 109.92 9.95 256.99 14.99 4.99 5.87 119.95 9.95 0 0 345.86 536.75 0 0 221.19 768.93 216.55 0 P_CODE P_DESCRIPT 11QER/31 Power painter, 15 psi., 3-nozzle 13-Q2/P2 7.25-in. pwr. saw blade 14-Q1/L3 9.00-in. pwr. saw blade 1546-QQ2 Hrd. cloth, 1/4-in., 2x50 1558-QW1 Hrd. cloth, 1/2-in., 3x50 2232/QTY B\&D jigsaw, 12-in. blade 2232/QWE B\&D jigsaw, 8-in. blade 2238/QPD B\&D cordless drill, 1/2-in. 23109-HB Claw hammer 23114-AA Sledge hammer, 12 lb. 54778-2T Rat-tail file, 1/8-in. fine 89-WRE-Q Hicut chain saw, 16 in. PVC23DRT PVC pipe, 3.5-in., 8-ft SM-18277 1.25-in. metal screw, 25 SW-23116 2.5-in. wd. screw, 50 WR3/TT3 Steel, 4'x8'x1/6", .5" mesh P_INDATE 11/3/2011 12/13/2011 11/13/2011 1/15/2012 1/15/2012 12/30/2011 12/24/2011 1/20/2012 1/20/2012 1/2/2012 12/15/2011 2/7/2012 2/20/2012 3/1/2012 2/24/2012 1/17/2012 V_CONTACT Smithson V CODE V_NAME 21225 Bryson, Inc. 21226 SuperLoo, Inc. Flushing 21231 D and E Supply Singh 21344 Gomez Bros. Ortega 22567 Dome Supply Smith 23119 Randsets Ltd. Anderson 24004 Brackman Bros. Browning 24288 ORDVA, Inc. Hakford 25443 B and K, Inc. Smith 25501 Damal Supplies 25595 Rubi Systems Smythe Orton P_QOH P_MIN 8 32 18 15 23 8 6 12 23 8 43 11 188 172 237 18 V_AREACODE 5 15 12 8 5 5 5 SOSOSHKOS 5 10 5 20 5 75 75 100 5 P_PRICE P_DISCOUNT V_CODE 0 25595 0.05 21344 0 21344 0 23119 0 23119 0.05 24288 V PHONE 615 223-3234 904 215-8995 615 228-3245 615 889-2546 901 678-1419 901 678-3998 615 228-1410 615 898-1234 904 227-0093 615 890-3529 904 456-0092 109.99 14.99 17.49 39.95 43.99 109.92 99.87 38.95 9.95 14.4 4.99 256.99 5.87 6.99 8.45 119.95 V_STATE TN FL TN KY GA GA TN TN FL TN FL 0.05 24288 0.05 25595 0.1 21225 Y N Y N N Y N Y N N Y 0.05 - 0 21344 0.05 24288 0- 0 21225 0 21231 0.1 25595 V_ORDER EMP_NUM EMP_TITLE 100 Mr. 101 Ms. 102 Mr. 103 Ms. 105 Mr. 104 Mr. 106 Mrs. 107 Mr. 108 Mr. 109 Mr. 110 Mrs. 111 Mr. 112 Mr. 113 Ms. 114 Ms. 115 Mrs. 116 Mr. EMP_LNAME Kolmycz Lewis Vandam Jones Williams Lange Smith Diante Wiesenbach Smith Genkazi Washington Johnson Smythe Brandon Saranda Smith Column1 Column EMP_NUM EMP_TITLE EMP_FNAME EMP_INITIAL George Rhonda Rhett Anne Robert John EMP_HIRE_DATE EMP_AREACODE EMP_PHONE EMP_MGR Jeanine Jorge Paul George Leighla Rupert Edward Melanie Marie Hermine Michael Column2 Null? NOT NULL EMP_LNAME NOT NULL EMP_FNAME NOT NULL EMP_INITIAL EMP_DOB - D G M D P K D R K W E E P G R A Column3 Type NUMBER CHAR(10) VARCHAR2(15) VARCHAR2(15) CHAR(1) DATE DATE CHAR(3) CHAR(8) NUMBER EMP DOB 6/15/1942 3/19/1965 11/14/1958 10/16/1974 3/14/1975 11/8/1971 2/12/1968 8/21/1974 2/14/1966 6/18/1961 5/19/1970 1/3/1966 5/14/1961 9/15/1970 11/2/1956 7/25/1972 11/8/1965 EMP_HIRE DATE EMP 3/15/1985 4/25/1986 12/20/1990 8/28/1994 11/8/1998 10/20/1994 1/5/1989 7/2/1994 11/18/1992 4/14/1989 12/1/1990 6/21/1993 12/1/1983 5/11/1999 11/15/1979 4/23/1993 12/10/1988 8. For every manager (i. e., group on EMP_MGR), display the manager employee code (EMP_MGR) along with the total number (i. e., use COUNT() here) of employees s/he manages (requires a WHERE clause checking all EMP_MGRs that are not null). [hint: you should have 3 rows of output]. 9. Display the product description, the full customer name and the customer balance for products ordered by customers having a customer balance between $200.00 and $400 (inclusive). Order by customer first name, then last name, then initial. Look at the table layouts above and determine which tables to include here. You will need to use all tables and join those appropriately in the WHERE clause using PK/FK pairs (3 joins). The BETWEEN keyword should be used in the WHERE clause for the proper balances [hint: you should have 4 rows of output ordered properly]. 10. For every invoice, display the invoice number, invoice date and the total dollar amount for all products purchased in the invoice, ordered by invoice number in descending order and then by invoice date in ascending order. Here you will use an aggregate SUM(x,y) function in your SELECT clause to calculate the total dollar amount (a calculated field-name it with the alias TOTAL). Don't forget to join on the tables as well. [hint: you should have 8 rows of output ordered properly]. 11. Write SQL queries that use a set operation to display the UNION of the first and last names in table CUSTOMER and the first and last names in table EMPLOYEE. Include duplicates in the output [hint: you should have 27 rows of output]. 12. Create a SQL query that uses an uncorrelated subquery and no joins to display the descriptions for products provided by a vendor in area code 904. Remember, the main difference between a correlated subquery and uncorrelated subquery is that, in a correlated subquery, the inner query is dependent upon the outer query, while, with an uncorrelated query, the inner query does not depend upon the outer query. Instead, it runs just once. You should NOT use union, minus, or intersect in this query. The outer query will use the PRODUCT table and the inner query will use the VENDOR table [hint: you should have 3 records of output]. INV_NUMBER LINE_NUMBER P_CODE LINE_UNITS 1 13-Q2/P2 2 23109-HB 1 54778-2T 1 2238/QPD 2 1546-QQ2 1001 1001 1002 1003 1003 1003 1004 1004 1005 1006 1006 1006 1006 1007 1007 1008 1008 1008 INV_NUMBER CUS_CODE INV_DATE 10014 1/16/2019 10011 1/16/2019 1/16/2019 10012 10011 1/17/2019 10018 1/17/2019 10014 1/17/2019 10015 1/17/2019 10011 1/17/2019 1001 1002 1003 1004 1005 1006 1007 1008 CUS_CODE CUS_LNAME 10010 Ramas 10011 Dunne 10012 Smith 10013 Olowski 10014 Orlando 10015 O'Brian 10016 Brown 10017 Williams 10018 Farriss 10019 Smith 3 13-Q2/P2 1 54778-2T 2 23109-HB 1 PVC23DRT 1 SM-18277 2 2232/QTY 3 23109-HB 4 89-WRE-Q 1 13-Q2/P2 2 54778-2T 1 PVC23DRT 2 WR3/TT3 3 23109-HB Myron Amy James George Anne Olivette A K W F - CUS_FNAME CUS_INITIAL CUS_AREACODE CUS_PHONE CUS_BALANCE Alfred 615 844-2573 713 894-1238 Leona Kathy 615 894-2285 Paul 615 894-2180 615 222-1672 713 442-3381 615 297-1228 615 290-2556 713 382-7185 615 297-3809 B G 1 1 2 1 1 5 3 2 12 3 1 1 1 2 1 5 3 1 G K LINE_PRICE 14.99 9.95 4.99 38.95 39.95 14.99 4.99 9.95 5.87 6.99 109.92 9.95 256.99 14.99 4.99 5.87 119.95 9.95 0 0 345.86 536.75 0 0 221.19 768.93 216.55 0 P_CODE P_DESCRIPT 11QER/31 Power painter, 15 psi., 3-nozzle 13-Q2/P2 7.25-in. pwr. saw blade 14-Q1/L3 9.00-in. pwr. saw blade 1546-QQ2 Hrd. cloth, 1/4-in., 2x50 1558-QW1 Hrd. cloth, 1/2-in., 3x50 2232/QTY B\&D jigsaw, 12-in. blade 2232/QWE B\&D jigsaw, 8-in. blade 2238/QPD B\&D cordless drill, 1/2-in. 23109-HB Claw hammer 23114-AA Sledge hammer, 12 lb. 54778-2T Rat-tail file, 1/8-in. fine 89-WRE-Q Hicut chain saw, 16 in. PVC23DRT PVC pipe, 3.5-in., 8-ft SM-18277 1.25-in. metal screw, 25 SW-23116 2.5-in. wd. screw, 50 WR3/TT3 Steel, 4'x8'x1/6", .5" mesh P_INDATE 11/3/2011 12/13/2011 11/13/2011 1/15/2012 1/15/2012 12/30/2011 12/24/2011 1/20/2012 1/20/2012 1/2/2012 12/15/2011 2/7/2012 2/20/2012 3/1/2012 2/24/2012 1/17/2012 V_CONTACT Smithson V CODE V_NAME 21225 Bryson, Inc. 21226 SuperLoo, Inc. Flushing 21231 D and E Supply Singh 21344 Gomez Bros. Ortega 22567 Dome Supply Smith 23119 Randsets Ltd. Anderson 24004 Brackman Bros. Browning 24288 ORDVA, Inc. Hakford 25443 B and K, Inc. Smith 25501 Damal Supplies 25595 Rubi Systems Smythe Orton P_QOH P_MIN 8 32 18 15 23 8 6 12 23 8 43 11 188 172 237 18 V_AREACODE 5 15 12 8 5 5 5 SOSOSHKOS 5 10 5 20 5 75 75 100 5 P_PRICE P_DISCOUNT V_CODE 0 25595 0.05 21344 0 21344 0 23119 0 23119 0.05 24288 V PHONE 615 223-3234 904 215-8995 615 228-3245 615 889-2546 901 678-1419 901 678-3998 615 228-1410 615 898-1234 904 227-0093 615 890-3529 904 456-0092 109.99 14.99 17.49 39.95 43.99 109.92 99.87 38.95 9.95 14.4 4.99 256.99 5.87 6.99 8.45 119.95 V_STATE TN FL TN KY GA GA TN TN FL TN FL 0.05 24288 0.05 25595 0.1 21225 Y N Y N N Y N Y N N Y 0.05 - 0 21344 0.05 24288 0- 0 21225 0 21231 0.1 25595 V_ORDER EMP_NUM EMP_TITLE 100 Mr. 101 Ms. 102 Mr. 103 Ms. 105 Mr. 104 Mr. 106 Mrs. 107 Mr. 108 Mr. 109 Mr. 110 Mrs. 111 Mr. 112 Mr. 113 Ms. 114 Ms. 115 Mrs. 116 Mr. EMP_LNAME Kolmycz Lewis Vandam Jones Williams Lange Smith Diante Wiesenbach Smith Genkazi Washington Johnson Smythe Brandon Saranda Smith Column1 Column EMP_NUM EMP_TITLE EMP_FNAME EMP_INITIAL George Rhonda Rhett Anne Robert John EMP_HIRE_DATE EMP_AREACODE EMP_PHONE EMP_MGR Jeanine Jorge Paul George Leighla Rupert Edward Melanie Marie Hermine Michael Column2 Null? NOT NULL EMP_LNAME NOT NULL EMP_FNAME NOT NULL EMP_INITIAL EMP_DOB - D G M D P K D R K W E E P G R A Column3 Type NUMBER CHAR(10) VARCHAR2(15) VARCHAR2(15) CHAR(1) DATE DATE CHAR(3) CHAR(8) NUMBER EMP DOB 6/15/1942 3/19/1965 11/14/1958 10/16/1974 3/14/1975 11/8/1971 2/12/1968 8/21/1974 2/14/1966 6/18/1961 5/19/1970 1/3/1966 5/14/1961 9/15/1970 11/2/1956 7/25/1972 11/8/1965 EMP_HIRE DATE EMP 3/15/1985 4/25/1986 12/20/1990 8/28/1994 11/8/1998 10/20/1994 1/5/1989 7/2/1994 11/18/1992 4/14/1989 12/1/1990 6/21/1993 12/1/1983 5/11/1999 11/15/1979 4/23/1993 12/10/1988

Answer rating: 100% (QA)

8 Display the manager employee code along with the total nu... View the full answer