Using the SQLEX database used for class exercises on querying, write queries for

Question

Using the SQLEX database used for class exercises on querying, write queries for the following:

(copy-paste your query from SQL server below relevant question)

Queries 1 through 3: 2 points each       Total = 6 points

Produce list of product names that have never been ordered

Produce list of customer id that have never placed any orders

Produce list of all customer id, and number of different products they have ordered (sorted descending order by number of different products)

For products that have sold at least 100 total units, produce list of product name and customer id of customers whose orders total of all products at least $5000

PRODUCT ProductID ProductName ProductNumber ProductLine ProductClass ProductStyle Product Color ProductStandardCost ProductListPrice ProductSize ProductSizeUnitMeasureCode ProductWeight ProductWeightUnitMeasureCode SALES ORDERS CUSTOMER SalesOrderNumber CustomerID OrderDate DueDate ShipDate SubTotal TaxAmt Freight TotalDue CustomerID CustomerType AddressLinel AddressLine2 PostalCode ORDER DETAILS SalesOrderDetailID SalesOrderNumber ProductId OrderQty UnitPrice UnitPriceDiscount LineTotal

Explanation / Answer

1.

Select ProductName from Product,Order_Details where Product.ProductID != Order_Details.ProductID;

2.

Select Customer.CustomerID from Customer,Sales_Orders where Customer.CustomerID != Sales_Orders.CustomerID;

3.

Select Customer.CustomerID,count(Product.ProductID) as NumberOfProducts from Customer inner join Sales_Orders on Customer.CustomerID = Sales_Orders.CustomerID inner join Order_Details on Sales_Orders.SalesOrderNumber = Order_Details.SalesOrderNumber inner join Product on Order_Details.ProductID = Product.ProductID order by count(Product.ProductID) desc;

4.

Select ProductName,Customer.CustomerID from Customer inner join Sales_Orders on Customer.CustomerID = Sales_Orders.CustomerID inner join Order_Details on Sales_Orders.SalesOrderNumber = Order_Details.SalesOrderNumber inner join Product on Order_Details.ProductID = Product.ProductID group by ProductName having OrderQty >= 100 and sum(OrderQty*UnitPrice) >= 5000;

Do ask if any query. Please upvote if it is helpful.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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