For this homework assignment you will need to utilize the Classic Models informa

Question

For this homework assignment you will need to utilize the Classic Models information located here - http://richardtwatson.com/dm6e/Reader/ClassicModels.html. Your instructor will provide access to this particular database in SQL Server. I only need to see the SQL you write to answer each of these queries. Because I have access to the same dataset that you do, I can run the queries myself to see the results they provide. Be prepared to hand this in at the beginning of class and remember to put your name and student ID in the header of the document. Write the SQL to answer the following queries. Single entity a. Report those payments greater than $100,000. b. List the products in each product line. c. How many products in each product line? d. What is the minimum payment received? e. List all payments greater than twice the average payment. One to many relationship f. How many orders have been placed by Herkku Gifts? g. Who are the employees in Boston? h. Report those payments greater than $100,000. Sort the report so the customer who made the highest payment appears first. i. List the value of 'On Hold' orders. j. Report the number of orders 'On Hold' for each customer. Many to many relationship k. List those orders containing items sold at less than the MSRP. l. Reports those products that have been sold with a markup of 100% or more (i.e., the price Each is at least twice the buy Price)

Explanation / Answer

a. Report those payments greater than $100,000.
SELECT * FROM Payments WHERE amount > 100000;

b. List the products in each product line.
SELECT * FROM Products ORDER BY productLine;

c. How many products in each product line?
SELECT productLine, count(*) FROM Products GROUP BY productLine;

d. What is the minimum payment received?
SELECT min(amount) FROM Payements;

e. List all payments greater than twice the average payment?
SELECT * FROM Payments
WHERE amount > 2 * (SELECT avg(amount) FROM Payments);

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.