What are the sql queries for a, b, and c? the database has already been created.

Question

What are the sql queries for a, b, and c? the database has already been created.

a) Produce list of all product names and number of different customers who have ordered the product

b) Produce list of all product names and number of orders that have included the product

c) Produce list of product names and number of orders when at least quantity of 2 was ordered.

Productlt ProductName ProductNumber ProductLine ProductClass SALES ORDERS CUSTOMER SalesOrderNumber CustomerID OrderDate DueDate ShipDate SubTotal TaxAmt Freight TotalDue CustomerID CustomerType AddressLinel AddressLine2 Product Color PostalCode ProductlistPrice ProductSize ProductSizeUnitMeasureCode Productweight ProductWeightUnitMeasureCode ORDER_DETAILS SalesOrderDetailID SalesOrderNumber ProductID OrderQty UnitPrice UnitPriceDiscount LineTotal

Explanation / Answer

a) Produce list of all product names and number of different customers who have ordered the product

Q:

select P.ProductName, count(O.SalesOrderNumber) from Product P LEFT JOIN OrderDetails O ON P.productID=O.productID where O.SalesOrderNumber IN (Select O.SalesOrderNumber from OrderDetails O LEFT JOIN SalesOrder S on S.SalesOrderNumber=O.SalesOrderNumber where S.CustomerID IN (select CustomerID from Customer)) group by c.customerID

-----------------------------------------------------------------------------------------------------------------------------------

b) Produce list of all product names and number of orders that have included the product

Q:

select P.ProductName, count(O.SalesOrderNumber) from Product P LEFT JOIN OrderDetails O ON

P.ProductID=O.ProductID group by P.ProductID

-----------------------------------------------------------------------------------------------------------------------------------------

c) Produce list of product names and number of orders when at least quantity of 2 was ordered.

Q:

select P.ProductName, count(O.SalesOrderNumber) from Product P LEFT JOIN OrderDetails O ON

P.ProductID=O.ProductID group by P.ProductID having O.OrderQty>1

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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