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)....
-
Find the quadratic approximation to f(x) = x + 3 near a = 1. Graph f, the quadratic approximation, and the linear approximation from Example 3 in Section 3.11 on a common screen. What do you conclude?
-
In a "Short-Paragraph", please respond to the following question,and include a "Source" or "Reference" at the end of your paragraph: Question: How are small businesses capable of providing additional...
-
Pay-later options are options for which the buyer is not required to pay the premium up front (i.e., at the time that the contract is entered into). At expiration, the holder of a pay-later option...
-
Thompson Travel borrowed $68,000 on October 1, 2012, by signing a one-year note payable to Metro One Bank. Thompsons interest expense for the remainder of the fiscal year (October through December)...
-
1. Cite an article on Business; verify the specific company/brand and the Biz Prob 2. State the (Primary AMR-oriented) Rsch Prob for that specific company and brand. 3. State what that Brand would...
-
On 1 January 20x1, Delphi Company issued 4% convertible bonds with a face value of $10,000,000 at par. The bond matured on 31 December 20x5. The bond was convertible into ordinary shares of Delphi...
-
Consider a project with a4-year life. The initial cost to set up the project is $1,200,000.This amount is to be linearly depreciated to zero over the life of the project. You expect to sell the...
-
You would again save $10 by buying the game downtown rather than at the campus store, but your cost of making the trip is now $12, not $9. By how much would your economic surplus be smaller if you...
-
Suppose the equilibrium wage for unskilled workers in New Jersey is $7 per hour. How will the wages and employment of unskilled workers in New Jersey change if the state legislature raises the...
-
If you had a choice between your citys (or the nearest large citys) hosting either a sports franchise or a World Cup, which would be more beneficial to your city? Why?
-
Benzene and ethanol (e) form azeotropic mixtures. Prepare a y-x and a P-x-y diagram for the benzene-ethanol system at 45C assuming the mixture is ideal. Compare the results with the experimental data...
-
Suppose your city (or the nearest city housing an NBA franchise) is trying to decide where to build a new arena. Think of two or three possible sites and describe the pros and cons of each. Which...
-
Match the pKa values of the following compounds: CH3CHCOOH CH3CHCHOH Choose... CH3CHCHNH Choose... CH3CHCHNH3* Choose... Choose...
-
Velshi Printers has contracts to complete weekly supplements required by fortysix customers. For the year 2018, manufacturing overhead cost estimates total $600,000 for an annual production capacity...
-
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...
-
If you look at the light spectrum reflected from the surface of a DVD compared to the spectrum reflected from the surface of a CD, youll see that the rainbows from the DVD are more spread out, with...
-
Increasing the density of a material tends to increase the index of refraction. Does light travel faster in seawater or in fresh water?
-
Antireflection coatings for glass usually have an index of refraction that is less than that of glass. Explain how this permits a thinner coating.
Study smarter with the SolutionInn App