Hailee Morrison (Customer 211) has adopted a pet llama, gotten married, moved to Alaska (state code...
Fantastic news! We've Found the answer you've been seeking!
Question:
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)
Expert Answer:
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
Related Book For
Smith and Roberson Business Law
ISBN: 978-0538473637
15th Edition
Authors: Richard A. Mann, Barry S. Roberts
Posted Date:
Students also viewed these databases questions
-
Apply the model of Group Dethe industry note from Hill and Farkas (CR). sign (Video Lecture) to the Varsity and Junior Varsity Army crew teams. Specifically, examine each stage of group design as...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Create an introduction as well as a conclusion for the following below Purpose of motivation in the workplace The concept of motivation has a central position across the field of psychology....
-
The object is used to execute a static Oracle query, but the object is used to execute a dynamic Oracle query with IN and OUT parameters. a. PreparedStatement, Statement b. Statement,...
-
(a) Imagine for a moment that the Earth has no atmosphere. Over the period of a year, is the number of daylight hours at your home greater than, less than, or equal to the number of nighttime hours?...
-
The primary conductor in Figure 10.2 is one phase of a three-phase transmission line operating at \(345 \mathrm{kV}, 700 \mathrm{MVA}, 0.95\) power factor lagging. The CT ratio is \(1200: 5\), and...
-
Baltimore Inc. reported pretax GAAP income of \(\$ 45,000\) in 2020 . In analyzing differences between GAAP income and taxable income, the company determined that it had deducted \(\$ 5,000\) in...
-
Smart Video Company is a manufacturer of videoconferencing products. Maintaining the videoconferencing equipment is an important area of customer satisfaction. A recent downturn in the computer...
-
Fresh Foods established a petty cash fund of $81 on January 2. On January 31, the fund contained cash of $8.10 and vouchers for the following cash payments: Office supplies Maintenance expense...
-
Elizabeth Burke wants to develop a model to more effectively plan production for the next year. Currently, PLE has a planned capacity of producing 9,100 mowers each month, which is approximately the...
-
What was the gross margin for 2022? Selected information from the accounting records of the Bulldogs Inc. is as follows: P900,000 P1,000,000 5 to 1 Net Accounts Receivable - December 31, 2021 Net...
-
A company has the following long-term capital outstanding as on 31 March 2019: (a) 10% debentures with a face value of 500,000. The debentures were issued in 2012 and are due on 31 March 2019. The...
-
Tessa and Nora are partners in Shining Lights Company. The company earned a net income of $ 6 0 , 0 0 0 during the fiscal year. According to the partnership agreement, Tessa is to receive a $ 4 0 , 0...
-
CarniTrin is a manufacturer of Carnival costumes in a highly competitive market. The company's management team is seeking guidance on the use of financial performance measures to identify the key...
-
Alex Corporation provides the following information for the year: Net sales Operating income Average assets Target rate of return Division A $410,000 102,500 670,000 15% Calculate the residual income...
-
How do theories of organizational change and resistance inform strategic initiatives aimed at fostering organizational agility and adaptation in turbulent environments ?
-
A put option in finance allows you to sell a share of stock at a given price in the future. There are different types of put options. A European put option allows you to sell a share of stock at a...
-
The first national bank pays a 4% interest rate compound continuously. The effective annual rate paid by the bank is __________. a. 4.16% b. 4.20% c. 4.08% d. 4.12%
-
While assistant treasurer of Travco Corporation, Frank Mitchell caused two checks, each payable to a fictitious company, to be drawn on Travcos account with Brown City Savings Bank. In each case,...
-
Barnes accepted Clarks offer to sell to him a portion of Clarks coin collection. Clark forgot that his prized $20 gold piece at the time of the offer and acceptance was included in the portion which...
-
Murphy, while a guest at a motel operated by the Betsy-Len Motor Hotel Corporation, sustained injuries from a fall allegedly caused by negligence in maintaining the premises. At that time, Betsy-Len...
-
Assume Company A and Company B each had a balance in deferred tax assets of \(\$ 250,000\). However, Company A has a valuation allowance related to the deferred tax asset of \(\$ 200,000\) while...
-
Maui Resort Inc. determined that the balance in its deferred tax asset account on December 31, 2020, was \(\$ 50,000\). Management reviewed all available positive and negative evidence to estimate...
-
Rangee Rover Inc. recorded pretax GAAP income of \(\$ 89,000\) in 2020. The GAAP basis of accounts receivable (net) is \(\$ 6,000\) less than the tax basis of accounts receivable. Assuming a tax rate...
Study smarter with the SolutionInn App