Answer the following questions for the sample database of the book. Note that your queries must...

  
 

Transcribed Image Text:

Answer the following questions for the sample database of the book. Note that your queries must be written based on the data provided in the question, not based on the values in the tables. For example if the question asks to find the orders of Dan Roberts, in your query, you must use the name Dan Roberts, not the EMPL_NUM of Dan Roberts (101) which you can obtain by looking at tables. Do not make assumptions using table values, they may change by time. New employees can be hired; new customers can work with the company, etc. For each question, give query and the output table you obtained in MS-SQL 1) Write a query that returns a table the columns of which shows the following information for each manufacturer manufacturer's ID • the numbers of different customers who ordered its product • the total amount of orders given for its all products. 2) Solve the previous question but this time print the same information for only the manufacturers who sold products to at least two different customers in 2008. 3) Write a query that lists the following information for the customers who ordered a reducer: all information of the customer and the total amount of the orders made for the reducer. 4) Consider the orders made by a customer which are taken by the by sales representative assigned to it. For each customer, list its ID number together with the total amount and total quantity of such orders. 5) Write a query that lists types and prices of products which are ordered by at least two different customers whose credit limit is more than 16000. 6) Consider the employees whose sales is more than 200,000. List the names and titles of such employees managing at least two offices together with the number of offices they manage. CUST ID 101 ACP Inc. 102 Second Corp. 103 Bass Mig 104 Stanley & Sons 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 EMP ID 1 2 3 4 CUSTOMERS 5 6 7 8 9 10 COMP_NAME Msc International HM Corp. Jack Mfg. Betacorp SMA Assoc. Torium Corp. Tory Brothers Will & Grace Eva Sinclair Arkas Lines Enrico Enterprises Gay Lussac Corp. Roro Inc. Southeast Systems Bowers & Wilkins Vogue Associates OPL Investments FL NAME Vanessa Abrams Blair Waldorf Carter Baizen Nathaniel Archibald Ivy Dickens Daniel Humphery Georgina Sparks Charles Bass Serena Woodsen Ben Donovan CST REP MAX CREDIT 65000,00 50000,00 45000,00 20000,00 3 1 5 2 10 55000,00 1 25000,00 6 80000,00 8 38000,00 3 15000,00 2 25000,00 7 40000,00 9 75000,00 6 23000,00 5 30000.00 2 95000,00 2 100000,00 9 64000,00 8 60000,00 4 45000,00 3 55000,00 1 13000,00 AGE 55 28 49 35 35 32 29 25 31 31 OFFICE 42 51 42 42 40 42 52 51 41 NULL EMPLOYEES TITLE Purchasing Mgr Sales Rep HIRE DATE 2015-12-20 00:00:00.000 2014-04-15 00:00:00.000 2004-03-04 00:00:00.000 Sales Mar 2002-07-18 00:00:00.000 Supply-Chain Rep 2008-03-22 00:00:00.000 HR Rep HR Mar Purchasing Rep Purchasing Rep Sales Rep Sales Rep OFFICE ID 40 41 42 51 52 LOCATION 8 4 6 4 2011-07-15 00:00:00.000 NULL 2016-03-15 00:00:00.000 8 2016-12-12 00:00:00.000 6 2017-09-23 00:00:00.000 6 2007-02-25 00:00:00.000 1 Detroit Boston Seattle Houston Miami OFFICES REGION MANAGER OFF TARGET OFF SALES 6 4 5 8 8 Northern Northern Northern Southern Southern SUPERVISOR EMP TARGET EMP SALES 4 450000,00 567345,00 450000,00 376900,00 324500,00 985678.00 400000,00 525678.00 1450000.00 789567,00 300000.00 675489,00 200000,00 167329,00 287956,00 150000,00 900000,00 NULL 445723,00 45698,00 750000,00 698345.00 123876,00 367995,00 575986,00 478812,00 40000,00 675000,00 490000,00 148500,00 ORDER_NUM ORDER DATE 200001 2008-11-16 00:00:00.000 200002 2009-02-12 00:00:00.000 200003 2011-12-02 00:00:00.000 200004 2009-03-11 00:00:00.000 200005 2006-09-11 00:00:00.000 200006 2003-05-28 00:00:00.000 200007 2009-03-03 00:00:00.000 200008 2005-10-15 00:00:00.000 200009 2018-05-13 00:00:00.000 200010 2015-11-28 00:00:00.000 200011 2013-03-09 00:00:00.000 200012 2017-11-26 00:00:00.000 200013 2003-08-25 00:00:00.000 200014 2009-06-18 00:00:00.000 200015 2009-05-19 00:00:00.000 200016 2018-05-21 00:00:00.000 200017 2013-02-09 00:00:00.000 200018 2012-12-12 00:00:00.000 200019 2012-12-21 00:00:00.000 200020 2008-03-05 00:00:00.000 200021 2017-04-28 00:00:00.000 200022 2011-04-16 00:00:00.000 200023 2010-03-11 00:00:00.000 200024 2009-02-05 00:00:00.000 200025 200026 2007-02-26 00:00:00.000 200027 2009-03-11 00:00:00.000 200028 200029 200030 2008-11-28 00:00:00.000 2013-05-12 00:00:00.000 2016-03-19 00:00:00.000 2015-02-22 00:00:00.000 CUST_NUM REP_NUM MAN 117 6 CHI 5 HAM 6 AX 111 101 118 102 8 107 112 103 118 108 121 103 114 121 114 103 112 109 107 118 111 108 120 106 106 108 118 ORDERS 103 111 113 1 10 8 5 8 8 9 7 MAL 5 HAM 8 SCR 7 AX 2 HAM 5 HAM SAW SAW Io19/00 7 SCR HAM 8 HAM CHI HAM MAL AX CHI HAM CHI HAM 2 SAW 2 CHI 2 SCR 9 SAW 8 SCR 5 HAM 3 HAM 1 CHI 3 1 PROD 45AG 303 212 ZTO1 304 307 47AG 304 101 211 101 304 ZTO2 212 307 302 85C 86C 48AG 302 46AG 305 87C 48AG ZTO3 908 ZTO1 306 305 47AG QUANT ORD_PRICE 32400,00 36985,00 2675,00 2530,00 4975,00 6 32400,00 11 50000,00 25 4378,00 2 745,00 15 25 9 3 36 11 2370,00 2 743,00 811,00 7 21 11 7 55 4 21 7 9 5 4 8 23 7 2 3 12 CE 21 6 6200,00 3520,00 24000,00 5034,00 3590,00 42460,00 735,00 940,00 18000,00 245,00 4590,00 2765,00 3250,00 7435,00 868,00 32400,00 700,00 33480,00 MAN_ID PROD_ID 211 212 45AG 46AG 47AG 48AG 301 302 303 AX AX CHI CHI CHI CHI HAM HAM HAM HAM HAM HAM HAM MAL MAL MAL SAW SAW SAW SAW SAW SAW SCR SCR SCR 304 305 306 307 101 102 103 305 306 307 308 309 310 211 212 213 PRODUCTS TYPE MEDIUM AX LARGE AX CHISEL PIN LEFT CHISEL RIGHT CHISEL CHISEL LINK SIZE 1 HAMMER SIZE 2 HAMMER SIZE 3 HAMMER SIZE 4 HAMMER HAMMER ADJUSTER HAMMER REMOVER HAMMER INSTALLER SIZE 1 MALLET SIZE 2 MALLET SIZE 3 MALLET 300-LB HANDSAW 500-LB HANDSAW 900-LB HANDSAW HANDSAW HOLDER HANDSAW HEAD HANDSAW RETAINER SCRAPER SCRAPER SCRAPER PRICE AV_QUANT 15 100 37 63 3 78 160 58 25 0 4500,00 2580,00 373,00 9478,00 $246,00 745,00 100,00 55,00 225,00 300,00 150,00 70,00 360,00 648,00 350,00 780,00 98,00 45,00 985,00 145,00 250,00 452,00 78,00 127,00 1000,00 48 277 365 5 85 96 195 157 195 7 89 16 99 0 5 Answer the following questions for the sample database of the book. Note that your queries must be written based on the data provided in the question, not based on the values in the tables. For example if the question asks to find the orders of Dan Roberts, in your query, you must use the name Dan Roberts, not the EMPL_NUM of Dan Roberts (101) which you can obtain by looking at tables. Do not make assumptions using table values, they may change by time. New employees can be hired; new customers can work with the company, etc. For each question, give query and the output table you obtained in MS-SQL 1) Write a query that returns a table the columns of which shows the following information for each manufacturer manufacturer's ID • the numbers of different customers who ordered its product • the total amount of orders given for its all products. 2) Solve the previous question but this time print the same information for only the manufacturers who sold products to at least two different customers in 2008. 3) Write a query that lists the following information for the customers who ordered a reducer: all information of the customer and the total amount of the orders made for the reducer. 4) Consider the orders made by a customer which are taken by the by sales representative assigned to it. For each customer, list its ID number together with the total amount and total quantity of such orders. 5) Write a query that lists types and prices of products which are ordered by at least two different customers whose credit limit is more than 16000. 6) Consider the employees whose sales is more than 200,000. List the names and titles of such employees managing at least two offices together with the number of offices they manage. CUST ID 101 ACP Inc. 102 Second Corp. 103 Bass Mig 104 Stanley & Sons 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 EMP ID 1 2 3 4 CUSTOMERS 5 6 7 8 9 10 COMP_NAME Msc International HM Corp. Jack Mfg. Betacorp SMA Assoc. Torium Corp. Tory Brothers Will & Grace Eva Sinclair Arkas Lines Enrico Enterprises Gay Lussac Corp. Roro Inc. Southeast Systems Bowers & Wilkins Vogue Associates OPL Investments FL NAME Vanessa Abrams Blair Waldorf Carter Baizen Nathaniel Archibald Ivy Dickens Daniel Humphery Georgina Sparks Charles Bass Serena Woodsen Ben Donovan CST REP MAX CREDIT 65000,00 50000,00 45000,00 20000,00 3 1 5 2 10 55000,00 1 25000,00 6 80000,00 8 38000,00 3 15000,00 2 25000,00 7 40000,00 9 75000,00 6 23000,00 5 30000.00 2 95000,00 2 100000,00 9 64000,00 8 60000,00 4 45000,00 3 55000,00 1 13000,00 AGE 55 28 49 35 35 32 29 25 31 31 OFFICE 42 51 42 42 40 42 52 51 41 NULL EMPLOYEES TITLE Purchasing Mgr Sales Rep HIRE DATE 2015-12-20 00:00:00.000 2014-04-15 00:00:00.000 2004-03-04 00:00:00.000 Sales Mar 2002-07-18 00:00:00.000 Supply-Chain Rep 2008-03-22 00:00:00.000 HR Rep HR Mar Purchasing Rep Purchasing Rep Sales Rep Sales Rep OFFICE ID 40 41 42 51 52 LOCATION 8 4 6 4 2011-07-15 00:00:00.000 NULL 2016-03-15 00:00:00.000 8 2016-12-12 00:00:00.000 6 2017-09-23 00:00:00.000 6 2007-02-25 00:00:00.000 1 Detroit Boston Seattle Houston Miami OFFICES REGION MANAGER OFF TARGET OFF SALES 6 4 5 8 8 Northern Northern Northern Southern Southern SUPERVISOR EMP TARGET EMP SALES 4 450000,00 567345,00 450000,00 376900,00 324500,00 985678.00 400000,00 525678.00 1450000.00 789567,00 300000.00 675489,00 200000,00 167329,00 287956,00 150000,00 900000,00 NULL 445723,00 45698,00 750000,00 698345.00 123876,00 367995,00 575986,00 478812,00 40000,00 675000,00 490000,00 148500,00 ORDER_NUM ORDER DATE 200001 2008-11-16 00:00:00.000 200002 2009-02-12 00:00:00.000 200003 2011-12-02 00:00:00.000 200004 2009-03-11 00:00:00.000 200005 2006-09-11 00:00:00.000 200006 2003-05-28 00:00:00.000 200007 2009-03-03 00:00:00.000 200008 2005-10-15 00:00:00.000 200009 2018-05-13 00:00:00.000 200010 2015-11-28 00:00:00.000 200011 2013-03-09 00:00:00.000 200012 2017-11-26 00:00:00.000 200013 2003-08-25 00:00:00.000 200014 2009-06-18 00:00:00.000 200015 2009-05-19 00:00:00.000 200016 2018-05-21 00:00:00.000 200017 2013-02-09 00:00:00.000 200018 2012-12-12 00:00:00.000 200019 2012-12-21 00:00:00.000 200020 2008-03-05 00:00:00.000 200021 2017-04-28 00:00:00.000 200022 2011-04-16 00:00:00.000 200023 2010-03-11 00:00:00.000 200024 2009-02-05 00:00:00.000 200025 200026 2007-02-26 00:00:00.000 200027 2009-03-11 00:00:00.000 200028 200029 200030 2008-11-28 00:00:00.000 2013-05-12 00:00:00.000 2016-03-19 00:00:00.000 2015-02-22 00:00:00.000 CUST_NUM REP_NUM MAN 117 6 CHI 5 HAM 6 AX 111 101 118 102 8 107 112 103 118 108 121 103 114 121 114 103 112 109 107 118 111 108 120 106 106 108 118 ORDERS 103 111 113 1 10 8 5 8 8 9 7 MAL 5 HAM 8 SCR 7 AX 2 HAM 5 HAM SAW SAW Io19/00 7 SCR HAM 8 HAM CHI HAM MAL AX CHI HAM CHI HAM 2 SAW 2 CHI 2 SCR 9 SAW 8 SCR 5 HAM 3 HAM 1 CHI 3 1 PROD 45AG 303 212 ZTO1 304 307 47AG 304 101 211 101 304 ZTO2 212 307 302 85C 86C 48AG 302 46AG 305 87C 48AG ZTO3 908 ZTO1 306 305 47AG QUANT ORD_PRICE 32400,00 36985,00 2675,00 2530,00 4975,00 6 32400,00 11 50000,00 25 4378,00 2 745,00 15 25 9 3 36 11 2370,00 2 743,00 811,00 7 21 11 7 55 4 21 7 9 5 4 8 23 7 2 3 12 CE 21 6 6200,00 3520,00 24000,00 5034,00 3590,00 42460,00 735,00 940,00 18000,00 245,00 4590,00 2765,00 3250,00 7435,00 868,00 32400,00 700,00 33480,00 MAN_ID PROD_ID 211 212 45AG 46AG 47AG 48AG 301 302 303 AX AX CHI CHI CHI CHI HAM HAM HAM HAM HAM HAM HAM MAL MAL MAL SAW SAW SAW SAW SAW SAW SCR SCR SCR 304 305 306 307 101 102 103 305 306 307 308 309 310 211 212 213 PRODUCTS TYPE MEDIUM AX LARGE AX CHISEL PIN LEFT CHISEL RIGHT CHISEL CHISEL LINK SIZE 1 HAMMER SIZE 2 HAMMER SIZE 3 HAMMER SIZE 4 HAMMER HAMMER ADJUSTER HAMMER REMOVER HAMMER INSTALLER SIZE 1 MALLET SIZE 2 MALLET SIZE 3 MALLET 300-LB HANDSAW 500-LB HANDSAW 900-LB HANDSAW HANDSAW HOLDER HANDSAW HEAD HANDSAW RETAINER SCRAPER SCRAPER SCRAPER PRICE AV_QUANT 15 100 37 63 3 78 160 58 25 0 4500,00 2580,00 373,00 9478,00 $246,00 745,00 100,00 55,00 225,00 300,00 150,00 70,00 360,00 648,00 350,00 780,00 98,00 45,00 985,00 145,00 250,00 452,00 78,00 127,00 1000,00 48 277 365 5 85 96 195 157 195 7 89 16 99 0 5

Answer rating: 100% (QA)

Answer ANS 1 select PRODUCTSPRODIDcountDISTINCT ORDERSCUSTIDsumORDERSORDPRICE from PRODUC... View the full answer