Tables: Customer Order_items Orders Returns es Mailings Review View Tell me what

Question

Tables: Customer Order_items Orders Returns es Mailings Review View Tell me what you want to do Write a query whose result set answers the questions that follow. Show the query and the resuit set. Place the sql for the queries and other commands that you used to create the queries in a text file with comments. Name the file LabPartsQueriess.sql. 1. What customers have not ordered any products? What order is the second most profitable? How many orders has each customer placed? Can you write a query that demonstrates your understanding of a correlated subquery? Can you write a query that demonstrates your understanding of an out join? Can you write a query that demonstrates your understanding of the DISTINCT key word? > >

Explanation / Answer

Solution:

The first four subparts are done as per Chegg guidelines, please repost others.

If the complete structure of the tables was given, it would have been great but this will also work.

The attributes of the tables are assumed in the queries using logical convention.

1)

SELECT cutomerID FROM Customer INNER JOIN Oder_items INNER JOIN Orders WHERE orderID=='NULL'

Explanation:

OderID being NULL means that the customer has not generated any order ID yet by ordering anything.

2)

SELECT orderID FROM Orders WHERE MIN(Orders.price) > MIN(Oders.price)

Explanation:

Here the second most profitable item is supposed to be the send cheapest item.

3)

SELECT customerID, COUNT(orderID) FROM Customer INNER JOIN Oder_items INNER JOIN Orders WHERE orderID==not null AND Customer.customerID=Customer.customerID

Explanation:

For every customer ID we are counting number of orders done in the database.

4)

Explanation:

The above query is an example of correlated subquery where the orderID of the second most ordered item is extracted.

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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