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 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, Ican 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. value of Hold' orders. each customer. j. Report the number of orders on Hold for Many to many relationship k. List those orders containing items sold at less than the MSRP. or I. Reports those products that have been sold with a markup of 100% more (i.e., the priceEach is at least twice the buyPrice) delete

Explanation / Answer

Consider an e-commerce website, with the following: Customers can make many orders. Orders can contain many items. Items can have descriptions in many languages. In the above scenario, each customer may have zero, one or multiple orders. But an order can belong to only one customer. This is a typical example of one to many relationship. Also in the above scenario, an order can have multiple items and an item can be in multiple orders, this scenarion depicts a many to many relationship.

One to Many relationship:-

f) SELECT COUNT(orderNumber) FROM Orders,Customers WHERE Orders.customerNumber = Customers.customerNumber AND Customers.customerName = "Herkku Gifts";

Here COUNT() function counts the number of records in the output table.

We have used the cross join on Orders and Customers and separated out the result where the CustomerName is Herkku Gifts and the customer id matches in both the table.

The output table of the cartition product is a table which contain column of both the tables and each row of both the table is mapped to a row of another table. So, this table contain many garbage tuples which can be removed by putting suitable conditions like matching the common foreign key in both the table and other conditons given in the question. It is the easiest way of thinking and solving such type of questions. I have solved a few parts for you as examples.

g) SELECT COUNT(employeeNumber) from Employees, Offices WHERE Offices.city='Boston' and Offices.officeCode = Employees.officeCode;

Here as well Boston can be used to get the OfficeCode which further can be used to find the number of employees.

h) SELECT checkNumber, customerName, amount, sum_amount FROM Payments, Customers, (SELECT SUM(amount) as sum_amount, customerNumber as cn FROM Payments GROUP BY customerNumber) grouped WHERE Payments.amount >= 100000 AND Payments.customerNumber = Customers.customerNumber AND Payments.customerNumber = grouped.cn ORDER BY grouped.sum_amount DESC;

Many to Many Relationship:-

k) SELECT DISTINCT(orderNumber)FROM Products, OrderDetails WHERE Products.productCode = OrderDetails.productCode AND buyPrice < MSRP;

Many to many relationship query is also solved like one to many.

I hope your doubt of how to write SQL for one to many relationships and many to many relationships would be clear.

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

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