Write the appropriate SQL statement that either answers the questions or retriev

Question

Write the appropriate SQL statement that either answers the questions or retrieves the requested values. 7-51. Write a SQL query to list each customer who bought at least one product that belongs to product line Cherry Tree in October 2015. List each customer only once. 7-52. Modify 7-51 so that you include the number of products in product line Cherry Tree that the customer ordered in October 2015. 7-53. Modify 7-52 so that the list includes the number of products each customer bought in each product line in October 2015. 7-60. Produce a list of all the products (show product description) and the number of times each product has been ordered 7-65. Display the customer ID, name, and order ID for all customer orders. For those customers who do not have any orders, include them in the display once, with a 0 value for OrderID:

Explanation / Answer

Write a SQL query to list each customer who bought at least one product that belongs to product line Cherry Tree in October 2015. List each customer only once.

Query:

select distinct CustomerID from Order_T where OrderID in (select distinct OrderID from OrderLine_T where ProductID in (select ProductID from Product_T where ProductLineID=(select ProductLineID from ProductLine_T where ProductLineName='Cherry Free'))) and OrderDate between '2015-10-01' and '2015-10-31';

explanation:

1. The inner most query,

select ProductLineID from ProductLine_T where ProductLineName='Cherry Free'

is used to retrieve the ProductLine_ID of 'Cherry Free' from productline table.

2. next,

select distinct OrderID from OrderLine_T where ProductID in (select ProductID from Product_T where ProductLineID=(select ProductLineID from ProductLine_T where ProductLineName='Cherry Free')

the above query retrieves all the OrderID of 'cherry Free' productline from orderline table.

3. Finally,

The whole query retrieves the Customer ID who bought at least one product that belongs to product line Cherry Tree in October 2015.

The keyword distinct is used to remove duplicate CustomerID and OrderID.

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

Modify 7-51 so that you include the number of products in product line Cherry Tree that the customer ordered in October 2015. 7-53.

Query: The retrieval is similar to previous query. The additional task is to retrieve count of orders.

select distinct CustomerID,count(OrderID) as numberOfOrders from Order_T where OrderID in (select distinct OrderID from OrderLine_T where ProductID in (select ProductID from Product_T where ProductLineID in (select ProductLineID from ProductLine_T where ProductLineName='Cherry Free'))) and OrderDate between '2015-10-01' and '2015-10-31' group by CustomerID

Explanation: In the above query the extra thing is retrieval of count.

For that, we have used,

count(OrderID) as numberOfOrders

This retrieves the count of OrderID and will be displayed as numberOfOrders in the result.

And Finally grouping the count based on Customer using,

group by CustomerID.

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

Modify 7-52 so that the list includes the number of products each customer bought in each product line in October 2015.

Query:

select distinct CustomerID,count(OrderID) as numberOfOrders from Order_T where OrderID in (select distinct OrderID from OrderLine_T where ProductID in (select ProductID from Product_T where ProductLineID in(select ProductLineID from ProductLine_T))) and OrderDate between '2015-10-01' and '2015-10-31' group by CustomerID

Explanation:

Just removing,

where ProductLineName='Cherry Free' from the previous query will give you the number of products each customer bought in each product line in October 2015.

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

Produce a list of all the products (show product description) and the number of times each product has been ordered

Query:

select Prod.ProductID,Prod.ProductDescription,count(Ord.OrderID) as numberoforders from Product_T Prod, OrderLine_T Ord where Prod.ProductID = Ord.ProductID group by Ord.ProductID

Explanation:

The above query will retrieve the ProductId and ProductDesciption from Product Table along with the number of times the product is ordered. The relation between Orders and Product is ProductLine table, as we have both OrderID and ProductID in it.

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

Display the customer ID, name, and order ID for all customer orders. For those customers who do not have any orders, include them in the display once, with a 0 value for OrderID:

Query:

Select Cust.CustomerID, Cust.CustomerName, OrderID from Customer_T Cust, Order_T Ord where Cust.CustomerID = Ord.CUstomerID

Union

Select CustomerID,CustomerName, 0 from Customer_T where not exists (Select * from Order_T where Order_T.CustomerID = Customer_T.CustomerID);

Explanation:

The above query displays all the customer details and displays OrderID as 0 when not hab=ving orders in the retrieval result.

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

Please upvote if you are happy with the answer

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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