For this homework assignment you will need to utilize the Classic Cars informati

Question

For this homework assignment you will need to utilize the Classic Cars information located here - http://richardtwatson.com/dm6e/Reader/ClassicModels.html use this database to answer these questions. I only need to see the SQL you write to answer each of these queries. One to many relationship

One to many relationship

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 priceEach is at least twice the buyPrice)

m. List the products ordered on a Monday.

n. What is the quantity on hand for products listed on 'On Hold' orders?

Explanation / Answer

One to many relationship

i.

Select Orders.OrderNumber, quantityOrdered*priceEach as Value from Orders inner join OrderDetails on Orders.orderNumber = OrderDetails.orderNumber where status = 'On Hold';

j.

Select Customers.customerNumber ,count(orderNumber),status from Customers inner join Orders on Customers.customerNumber = Orders.customerNumber group by Customers.customerNumber,status having status = 'On Hold';

Many to many relationship

k.

Select * from Orders inner join OrderDetails on Orders.orderNumber = OrderDetails.orderNumber inner join Products on OrderDetails.productCode = Products.productCode where buyPrice < MSRP;

l.

Select * from Products inner join OrderDetails on OrderDetails.productCode = Products.productCode where priceEach >= 2*buyPrice;

m.

Select Products.* from Orders inner join OrderDetails on Orders.orderNumber = OrderDetails.orderNumber inner join Products on OrderDetails.productCode = Products.productCode where DATEPART(weekday, orderDate) = '2';

n.

Select Products.productCode, quantityInStock from Products inner join OrderDetails on Products.productCode = OrderDetails.productCode inner join Orders on OrderDetails.orderNumber = Orders.orderNumber where status = 'On Hold';

Do ask if any doubt. Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

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