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 randomized experiment was conducted to see how others opinions affect a users ratings online. Whenever a comment was added on a certain social news site, researchers gave it an up vote, down vote,...
-
In Exercises 23 through 28, compute the elasticity of demand for the given demand function D(p) and determine whether the demand is elastic, inelastic, or of unit elasticity at the indicated price p....
-
Selecting cards at random. In a standard deck of 52 cards, there are 13 spades, 13 hearts, 13 diamonds, and 13 clubs. How would you assign digits for a simulation to determine the suit (spades,...
-
The local residents of Greene County, a small, rural, mostly minority community, have recently learned that a major oil company is putting a refinery in the county. The residents ask for an...
-
The journal entry to record the completion of a job in a job order cost system is?
-
Given a database of the results of an election, find the number of seats won by each party. There are some rules to going about this: There are many constituencies in a state and many candidates who...
-
(d) Can you understand how important the parameters of a distribution are? What do the parameters of a distribution tell us about the distribution? (f) At this point your graph should have four...
-
The financial statements for the Columbia Sportswear Company can be found in Appendix A, and Under Armour, Inc.'s financial statements can be found in Appendix B at the end of this book. Required a....
-
Use the data from SE3-8 to prepare the closing entries for The Decade Company. Close the temporary accounts to income summary. The balance of \(\$ 8,500\) in the retained earnings account is from the...
-
Adjusting Entries The following selected accounts appear in the Birch Company's unadjusted trial balance as of December 31, the end of the fiscal year (all accounts have normal balances): Required...
-
Closing Entries Use the information provided in E3-5A to prepare journal entries to close the accounts using the Income Summary account. After these entries are posted, what is the balance in the...
-
Ceva, Inc. manufactures and services jet engines for air carriers. The engines cost \($10\) to \($40\) million each, depending on the specifications and plane. A 10-year service contract for a single...
-
In September, Larson Inc. sold 45,000 units of its only product for $284,000, and incurred a total cost of $265,000, of which $29,000 were fixed costs. The flexible budget for September showed total...
-
Listed below are several terms and phrases associated with basic assumptions, broad accounting principles, and constraints. Pair each item from List A (by letter) with the item from List B that is...
-
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...
-
What qualitative characteristics make accounting information useful? AppendixLO1
-
What are generally accepted accounting principles? AppendixLO1
-
What is the basic objective of financial information? AppendixLO1
Study smarter with the SolutionInn App