Hailee Morrison (Customer 211) has adopted a pet llama, gotten married, moved to Alaska (state code...

    

Transcribed Image Text:

Hailee Morrison (Customer 211) has adopted a pet llama, gotten married, moved to Alaska (state code is AK), and changed their last name to Stonewall. Edit this customer's record to reflect these changes. Include in your submission (1) the query that you would use to make this change to the database and (2) the output of a query that verifies the record has been appropriately updated AS WELL AS the query you used to generate this output. (9 points) 12. Finally, come up with an original query that you think could help lead to important insight for the company (8 points). The query should not be something "obvious" (e.g., don't count the number of products the company sells), should include more than one table, and should be ordered meaningfully. For your query, report each of the following: a. Describe what the query will output (i.e., what are you trying to show with the query?). b. Provide the SQL query itself. c. Provide the query's output (first 10 lines only). d. Describe how DustBunny might use this query to gain insight into their business For any students who want to earn a little extra credit, consider the following additional problems (1.5-2 points each). 13. For each order placed on August 18th, 2021, how many unit items (a unit item is 1 vacuum cleaner; two of the same model would be 2 unit items; one of one model and one of another would also be 2 unit items) were purchased? Output only those orders that included two or more unit items, include in your output the order number and the number of unit items, and order your output by order number (lowest to highest). (1.5 points) 14. What is the total value of all orders placed for each region? The output should include the name of the region as well as the total value of all orders placed there. Order results alphabetically by region. Based on these results, to which region do you think the company marketed most heavily during the early part of August 2021? (2 points) 15. Which three customers bought the highest number of MadHare units during the period covered by the database? Your output should include first name, last name, and the number of units purchased and should include information only about the three customers with the highest number of MadHare units. Be sure your query references the Model table (i.e., don't look up the model number for MadHare, instead include in your query content that figures out which records refer to 'MadHare). (1.5 points) Customer DBVac ERD & Data Dictionary PK CustID FK CustState FName LNam Gender HHI Marital Children HasPets USState PK StateID FK RegionID StateName Population Region PK RegionID RegionName HQCity SalesOrder PK SOID FK PK FK FK CustID SODate Order Total Service SvcID CustID Product SvcDate SvcDays SvcRating | OrderLine PK OLID FK SOID FK ModelID ModelPrice Qty Line Total Product PK ModelID Model Name MSRP Cost ProdType Customer Field Name CustID CustState Fname Lname Gender Marital Children HasPets Product Field Name Key ModellD PK ModelName MSRP Cost ProdType OrderLine Field Name OLID SOID ModellD ModelPrice Qty LineTotal RegionID RegionName HQCity SalesOrder Field Name SOID CustID SODate OrderTotal Region Field Name Key PK Service Field Name SvcID CustID Product SvcDate Key PK FK References USState Svc Days SvcRating Key PK StatelD RegionID Population StateName FK References SalesOrder FK References Model Key PK FK References Customer Key PK FK References Customer FK References Product USState Field Name Key PK FK References Region Data Type int (auto) nchar(2) nvarchar(50) nvarchar(50) y nchar(1) int y Y nvarchar(20) Y tinyint nchar(1) Y Y Data Type nvarchar(4) nvarchar(50) smallmoney smallmoney nvarchar(10) Data Type int (auto) int nvarchar(4) smallmoney tinyint smallmoney Data Type tinyint (auto) nvarchar(50) nvarchar(50) Data Type int (auto) int date smallmoney Data Type int (auto) int nvarchar(4) date Null nvarchar(30) Default Value Description Record identifier Null Default Value Description Null State of residence for customer Customer's first name Customer's last name Customer's gender (m or f) Customer's reported household income Customer's marital status (single or married) Customer's reported number of children in household ("5" = "5+") Whether customer has pets or not (y or n) decimal(4,1) y tinyint y 2- or 3-letter unique identifier Name of the model Null Default Value Description Suggested retail price Unit cost Type of product (vac = vaccuum, warr = extended warranty) Record identifier Sales order to which line item belongs Model included in the line item The price of the model at time of purchase The quantity of the model purchased The total value of the line item (= price * qty) Default Value Description Record identifier Name given to the region City where regional sales HQ is located Null Default Value Description Sales Order ID (record identifier) Customer who placed the sales order The date of the sales order The total value of the order at the time placed Null Default Value Description Service Order ID (record identifier) Customer who sent product in for servicing The product that was sent in Date product was received into service center Number of days spent servicing product Customer's survey rating of satisfaction with service Data Type Null Default Value Description nchar(2) tinyint int USPS (postal) abbreviation used Indicates which region state belongs to Population as of 2016 The full state name (not abbreviated) Hailee Morrison (Customer 211) has adopted a pet llama, gotten married, moved to Alaska (state code is AK), and changed their last name to Stonewall. Edit this customer's record to reflect these changes. Include in your submission (1) the query that you would use to make this change to the database and (2) the output of a query that verifies the record has been appropriately updated AS WELL AS the query you used to generate this output. (9 points) 12. Finally, come up with an original query that you think could help lead to important insight for the company (8 points). The query should not be something "obvious" (e.g., don't count the number of products the company sells), should include more than one table, and should be ordered meaningfully. For your query, report each of the following: a. Describe what the query will output (i.e., what are you trying to show with the query?). b. Provide the SQL query itself. c. Provide the query's output (first 10 lines only). d. Describe how DustBunny might use this query to gain insight into their business For any students who want to earn a little extra credit, consider the following additional problems (1.5-2 points each). 13. For each order placed on August 18th, 2021, how many unit items (a unit item is 1 vacuum cleaner; two of the same model would be 2 unit items; one of one model and one of another would also be 2 unit items) were purchased? Output only those orders that included two or more unit items, include in your output the order number and the number of unit items, and order your output by order number (lowest to highest). (1.5 points) 14. What is the total value of all orders placed for each region? The output should include the name of the region as well as the total value of all orders placed there. Order results alphabetically by region. Based on these results, to which region do you think the company marketed most heavily during the early part of August 2021? (2 points) 15. Which three customers bought the highest number of MadHare units during the period covered by the database? Your output should include first name, last name, and the number of units purchased and should include information only about the three customers with the highest number of MadHare units. Be sure your query references the Model table (i.e., don't look up the model number for MadHare, instead include in your query content that figures out which records refer to 'MadHare). (1.5 points) Customer DBVac ERD & Data Dictionary PK CustID FK CustState FName LNam Gender HHI Marital Children HasPets USState PK StateID FK RegionID StateName Population Region PK RegionID RegionName HQCity SalesOrder PK SOID FK PK FK FK CustID SODate Order Total Service SvcID CustID Product SvcDate SvcDays SvcRating | OrderLine PK OLID FK SOID FK ModelID ModelPrice Qty Line Total Product PK ModelID Model Name MSRP Cost ProdType Customer Field Name CustID CustState Fname Lname Gender Marital Children HasPets Product Field Name Key ModellD PK ModelName MSRP Cost ProdType OrderLine Field Name OLID SOID ModellD ModelPrice Qty LineTotal RegionID RegionName HQCity SalesOrder Field Name SOID CustID SODate OrderTotal Region Field Name Key PK Service Field Name SvcID CustID Product SvcDate Key PK FK References USState Svc Days SvcRating Key PK StatelD RegionID Population StateName FK References SalesOrder FK References Model Key PK FK References Customer Key PK FK References Customer FK References Product USState Field Name Key PK FK References Region Data Type int (auto) nchar(2) nvarchar(50) nvarchar(50) y nchar(1) int y Y nvarchar(20) Y tinyint nchar(1) Y Y Data Type nvarchar(4) nvarchar(50) smallmoney smallmoney nvarchar(10) Data Type int (auto) int nvarchar(4) smallmoney tinyint smallmoney Data Type tinyint (auto) nvarchar(50) nvarchar(50) Data Type int (auto) int date smallmoney Data Type int (auto) int nvarchar(4) date Null nvarchar(30) Default Value Description Record identifier Null Default Value Description Null State of residence for customer Customer's first name Customer's last name Customer's gender (m or f) Customer's reported household income Customer's marital status (single or married) Customer's reported number of children in household ("5" = "5+") Whether customer has pets or not (y or n) decimal(4,1) y tinyint y 2- or 3-letter unique identifier Name of the model Null Default Value Description Suggested retail price Unit cost Type of product (vac = vaccuum, warr = extended warranty) Record identifier Sales order to which line item belongs Model included in the line item The price of the model at time of purchase The quantity of the model purchased The total value of the line item (= price * qty) Default Value Description Record identifier Name given to the region City where regional sales HQ is located Null Default Value Description Sales Order ID (record identifier) Customer who placed the sales order The date of the sales order The total value of the order at the time placed Null Default Value Description Service Order ID (record identifier) Customer who sent product in for servicing The product that was sent in Date product was received into service center Number of days spent servicing product Customer's survey rating of satisfaction with service Data Type Null Default Value Description nchar(2) tinyint int USPS (postal) abbreviation used Indicates which region state belongs to Population as of 2016 The full state name (not abbreviated)

Answer rating: 100% (QA)

Unfortunately I am unable to directly run SQL queries or verify their outputs against a database as I do not have access to execute SQL commands or ac... View the full answer