Extra Credit SELECT Queries
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, 2023, 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
2023? (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').

Transcribed Image Text:

Customer Field Name Key CustID PK int (auto) CustState FK References USState nchar(2) Fname nvarchar(50) Lname nvarchar(50) y DBVac Data Dictionary Data Type Null Default Value Description Record identifier State of residence for customer Customer's first name Customer's last name Gender nchar(1) Y Customer's gender (m or f) HHI int Y Customer's reported household income Marital nvarchar(20) y Customer's marital status (single or married) Children HasPets tinyint nchar(1) Y W Customer's reported number of children in household ("5" = "5+") Whether customer has pets or not (y or n) Product Field Name ModelID Key PK Data Type Null nvarchar(4) ModelName nvarchar(50) MSRP Cost ProdType smallmoney smallmoney nvarchar(10) Default Value Description 2- or 3-letter unique identifier Name of the model Suggested retail price Unit cost Type of product (vac = vaccuum, warr = extended warranty) OrderLine Field Name Key OLID PK SOID ModellD FK References Sales Order FK References Model ModelPrice Data Type int (auto) int nvarchar(4) smallmoney Qty LineTotal tinyint smallmoney Null Default Value Description Record identifier Sales order to which line item bellongs Model included in the line item The price of the modell at time of purchase The quantity of the model purchased The totall value of the line item (= price* qty) Region Field Name Key RegionID PK Region Name Data Type tinyint (auto) nvarchar(50) nvarchar(50) Null Default Value Description Record identifier Name given to the region City where regional salles HQ is located HQCity SalesOrder Field Name SOID Key PK CustID FK References Customer Data Type Null Default Value Description int (auto) Sales Order ID (record identifier) int SODate OrderTotal date smallmoney Customer who placed the sales order The date of the salles order The totall value of the order at the time placed Service Field Name SvcID Key PK CustID FK References Customer Data Type int (auto) int Null Default Value Description Service Order ID (record identifier) Product FK References Product SvcDate nvarchar(4) date SvcDays SveRating decimal(4,1) y tinyint V 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 USState Field Name StateID Key PK Data Type nchar(2) Null Default Value Description RegionID FK References Region tinyint Population int StateName nvarchar(30) USPS (postal) abbreviation used Indicates which region state belongs to Population as of 2016 The full state name (not abbreviated) 2 Customer Field Name Key CustID PK int (auto) CustState FK References USState nchar(2) Fname nvarchar(50) Lname nvarchar(50) y DBVac Data Dictionary Data Type Null Default Value Description Record identifier State of residence for customer Customer's first name Customer's last name Gender nchar(1) Y Customer's gender (m or f) HHI int Y Customer's reported household income Marital nvarchar(20) y Customer's marital status (single or married) Children HasPets tinyint nchar(1) Y W Customer's reported number of children in household ("5" = "5+") Whether customer has pets or not (y or n) Product Field Name ModelID Key PK Data Type Null nvarchar(4) ModelName nvarchar(50) MSRP Cost ProdType smallmoney smallmoney nvarchar(10) Default Value Description 2- or 3-letter unique identifier Name of the model Suggested retail price Unit cost Type of product (vac = vaccuum, warr = extended warranty) OrderLine Field Name Key OLID PK SOID ModellD FK References Sales Order FK References Model ModelPrice Data Type int (auto) int nvarchar(4) smallmoney Qty LineTotal tinyint smallmoney Null Default Value Description Record identifier Sales order to which line item bellongs Model included in the line item The price of the modell at time of purchase The quantity of the model purchased The totall value of the line item (= price* qty) Region Field Name Key RegionID PK Region Name Data Type tinyint (auto) nvarchar(50) nvarchar(50) Null Default Value Description Record identifier Name given to the region City where regional salles HQ is located HQCity SalesOrder Field Name SOID Key PK CustID FK References Customer Data Type Null Default Value Description int (auto) Sales Order ID (record identifier) int SODate OrderTotal date smallmoney Customer who placed the sales order The date of the salles order The totall value of the order at the time placed Service Field Name SvcID Key PK CustID FK References Customer Data Type int (auto) int Null Default Value Description Service Order ID (record identifier) Product FK References Product SvcDate nvarchar(4) date SvcDays SveRating decimal(4,1) y tinyint V 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 USState Field Name StateID Key PK Data Type nchar(2) Null Default Value Description RegionID FK References Region tinyint Population int StateName nvarchar(30) USPS (postal) abbreviation used Indicates which region state belongs to Population as of 2016 The full state name (not abbreviated) 2