Refer to the table and field names below for this database. SALE Column Name Condensed Type...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Refer to the table and field names below for this database. SALE Column Name Condensed Type Nullable Sale D SaleBuyer D SaleDate BUYER int int datetime No No No Column Name Condensed Type Nullable BuyerlD int BuyerLastNa... char(20) BuyerFirstNa... char(20) BuyerAddress char(30) BuyerCity char(20) char(2) BuyerState BuyerZIP char(10) BuyerPhone char(14) BuyerNotes varchar(500) No No No No No No No Yes Yes SALE_LINE_ITEM Column Name Condensed Type Nullable ItemID ItemSale!D ItemProdID ItemQty ItemUnitPrice SUPPLIER int int int numeric(8, 2) numeric(8,2) No No No No No Column Name Condensed Type Nullable Supplier D int SupplierName char(30) SupplierCont... char(50) SupplierNotes varchar(200) SupplierPhone char(14) No No Yes Yes Yes PRODUCT Column Name Condensed Type Nullable ProdID No int ProdName char(30) ProdCategory char(12) ProdDescripti... varchar(300) No No Yes No ProdSupplier D int ProdinyQty int ProdUnitPrice numeric(8, 2) Yes Yes ProdUnitCost numeric(8, 2) Yes Section 2: SQL (40%) Single table queries: 1. Write the SQL statement that will count the number of rows in the SALE_ITEM table. Code: Results: 2. Find all the products Trent sells that include the word "siding". List the product ID, product name and description. Code: Results: 3. How much money (cost of all products) is tied up in inventory? Code: Results: 4. List the name, quantity in stock, and cost of each product where there are more than 25 in stock and the cost is less than 200 dollars. Sort by quantity beginning with the highest amount and then by cost. Code: Results: Multiple table queries using subqueries 5. List all buyers who live in Greenville, NC who have purchased products from Trent Hardware. Sort by Last Name and then First Name. (Use a subquery.) Code: Results: 6. Have any products listed in Trent Hardware's PRODUCT table never been sold? If so, list the unique description of the product(s) that have never been sold. (Use a subquery.) Code: Results: 7. Using a SQL subquery, confirm the referential integrity constraint has ensured only Buyers listed in the BUYERS table exist in the SALE table. Code: Results: Multiple table queries using JOINS 8. Display the product description, the quantity sold, the unit price, and a total-line-price for Sale!D = 12. Display the line-item total as "LineTotal". The Output should have these columns: Results Messages ProdDescription ItemQty Item UnitPrice LineTotal (Hint: If you don't know where to begin, use the relationship image on page 1 and trace a path from ProdDescription in PRODUCT table to Sale!D in the SALE table, making note of primary and foreign keys that link the tables. Consider using those keys in your joins.) Code: Results: 9. List the count of different buyers who purchased siding products during 2021. Be sure to string their last and then first name together leaving no extra spaces between them. Sort by buyer name. Code: Results: wwwwwwwwwww 10. Write a query to show Product Category and "Total Profit" (use that term as column heading in your output) for the year 2021. Profit is calculated as ItemUnitPrice - ProdUnitCost. Group the results by Product Category and sort the list in descending order by TotalProfit. (NOTE: Since this is a fictional company, there may be large differences between what an item sold for and what it's listed cost or price is in the product table, and therefore don't be surprised by negative numbers in some categories.) Code: Result: Refer to the table and field names below for this database. SALE Column Name Condensed Type Nullable Sale D SaleBuyer D SaleDate BUYER int int datetime No No No Column Name Condensed Type Nullable BuyerlD int BuyerLastNa... char(20) BuyerFirstNa... char(20) BuyerAddress char(30) BuyerCity char(20) char(2) BuyerState BuyerZIP char(10) BuyerPhone char(14) BuyerNotes varchar(500) No No No No No No No Yes Yes SALE_LINE_ITEM Column Name Condensed Type Nullable ItemID ItemSale!D ItemProdID ItemQty ItemUnitPrice SUPPLIER int int int numeric(8, 2) numeric(8,2) No No No No No Column Name Condensed Type Nullable Supplier D int SupplierName char(30) SupplierCont... char(50) SupplierNotes varchar(200) SupplierPhone char(14) No No Yes Yes Yes PRODUCT Column Name Condensed Type Nullable ProdID No int ProdName char(30) ProdCategory char(12) ProdDescripti... varchar(300) No No Yes No ProdSupplier D int ProdinyQty int ProdUnitPrice numeric(8, 2) Yes Yes ProdUnitCost numeric(8, 2) Yes Section 2: SQL (40%) Single table queries: 1. Write the SQL statement that will count the number of rows in the SALE_ITEM table. Code: Results: 2. Find all the products Trent sells that include the word "siding". List the product ID, product name and description. Code: Results: 3. How much money (cost of all products) is tied up in inventory? Code: Results: 4. List the name, quantity in stock, and cost of each product where there are more than 25 in stock and the cost is less than 200 dollars. Sort by quantity beginning with the highest amount and then by cost. Code: Results: Multiple table queries using subqueries 5. List all buyers who live in Greenville, NC who have purchased products from Trent Hardware. Sort by Last Name and then First Name. (Use a subquery.) Code: Results: 6. Have any products listed in Trent Hardware's PRODUCT table never been sold? If so, list the unique description of the product(s) that have never been sold. (Use a subquery.) Code: Results: 7. Using a SQL subquery, confirm the referential integrity constraint has ensured only Buyers listed in the BUYERS table exist in the SALE table. Code: Results: Multiple table queries using JOINS 8. Display the product description, the quantity sold, the unit price, and a total-line-price for Sale!D = 12. Display the line-item total as "LineTotal". The Output should have these columns: Results Messages ProdDescription ItemQty Item UnitPrice LineTotal (Hint: If you don't know where to begin, use the relationship image on page 1 and trace a path from ProdDescription in PRODUCT table to Sale!D in the SALE table, making note of primary and foreign keys that link the tables. Consider using those keys in your joins.) Code: Results: 9. List the count of different buyers who purchased siding products during 2021. Be sure to string their last and then first name together leaving no extra spaces between them. Sort by buyer name. Code: Results: wwwwwwwwwww 10. Write a query to show Product Category and "Total Profit" (use that term as column heading in your output) for the year 2021. Profit is calculated as ItemUnitPrice - ProdUnitCost. Group the results by Product Category and sort the list in descending order by TotalProfit. (NOTE: Since this is a fictional company, there may be large differences between what an item sold for and what it's listed cost or price is in the product table, and therefore don't be surprised by negative numbers in some categories.) Code: Result:
Expert Answer:
Answer rating: 100% (QA)
It appears youve sent images containing a database schema and a set of SQL exercise questions The database consists of several tables namely SALE SALELINEITEM BUYER SUPPLIER and PRODUCT The SQL exerci... View the full answer
Related Book For
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill
Posted Date:
Students also viewed these databases questions
-
Understanding the significance of route optimization can help me streamline delivery schedules, reducing costs and improving delivery times. Additionally, insights into demand forecasting can aid in...
-
The following additional information is available for the Dr. Ivan and Irene Incisor family from Chapters 1-5. Ivan's grandfather died and left a portfolio of municipal bonds. In 2012, they pay Ivan...
-
Refer to the table in Problem 3.8. In Problem 3.8, the City Commission of Nashville has decided to build a botanical garden and picnic area in the heart of the city for the recreation of its...
-
Go out onto the Web and compare three shopping bots for a product you are interested in (e.g., www.mysimon.com, www.bottomdollar.com, www.shopzilla.com, www.shopping.com, or www.pricegrabber.com)....
-
The two blocks in FIGURE 13-34 have the same mass, m. All the springs have the same force constant, k, and are at their equilibrium length. When the blocks are set into oscillation, (a) Is the period...
-
Indicate whether each of the following statements is true or false by writing T or F in t he a nswer c olumn. Estoppel is a legal principle that can prevent an owner from recovering his or her own...
-
Suppose the exchange rate for the Swiss franc is quoted as SF 1.50 in the spot market and SF 1.53 in the 90-day forward market. a. Is the dollar selling at a premium or a discount relative to the...
-
Puget Sound Divers is a company that provides diving services such as underwater ship repairs to clients in the Puget Sound area. The companys planning budget for May appears below: Required: During...
-
Garden pro Corporation has sales of $4,351,375; income tax of $422,665; the selling, general and administrative expenses of $294,243; depreciation of $316,124; cost of goods sold of $2,566,710; and...
-
For each of the following lists of premises, derive the conclusion and supply the justification for it. There is only one possible answer for each problem. (1) 1. G F 2. F 3. _______ ____ (2) 1. S...
-
ZiLLP, a CA CPA firm invites your client to a free lunch education event with other prospects. Your client goes and is then invited to a free review of their accounting and tax needs. Your client...
-
discuss the impact of inclusive talent management practices, such as diversity recruitment strategies, mentoring and sponsorship programs, and inclusive leadership development initiatives, on...
-
An S corporation can only have 1 class of stock, and all shareholders must have the same rights to distributions and liquidation proceeds. Therefore, Mr. and Mrs. B would not be able to issue...
-
On January 1 , 2 0 2 5 , Cullumber Company purchased 3 0 % of Ivanhoe Corporation\'s common stock; no goodwill resulted from the purchase. Cullumber appropriately carries this investment at equity...
-
Your brother is sitting next to your female friend, and you are praying he says nothing stupid. Out of his mouth comes "What's your mass, my little friend?" You are mortified, because then he says "I...
-
Describe the different management styles their effects on an IT project and make recommendations for motivating and managing teams, based on a IT project that revolves around migrating a Bank from a...
-
Calculate the monthly finance charge for the credit card transaction assume that it takes 1 0 days for a payment to be received and recorded and then the month is 3 0 days long round your answer to...
-
Rosalie owns 50% of the outstanding stock of Salmon Corporation. In a qualifying stock redemption, Salmon distributes $80,000 to Rosalie in exchange for one-half of her shares, which have a basis of...
-
Diane is a single taxpayer who qualifies for the earned income credit. Diane has two qualifying children who are 3 and 5 years old. During 2012, Diane's wages are $17,100 and she receives dividend...
-
Bill and Guilda each own 50 percent of the stock of Radiata Corporation, an S corporation. Guilda's basis in her stock is $25,000. On July 31, 2012, Bill sells his stock, with a basis of $40,000, to...
-
Ann hires a nanny to watch her two children while she works at a local hospital. She pays the 19-year-old nanny $125 per week for 48 weeks during the current year. a. What is the employer's portion...
-
Explain how property and liability insurance protect.
-
Describe how insurance uses different risk management methods to reduce risk.
-
Explain the difference between actual cash value and replacement value.
Study smarter with the SolutionInn App