MYSQL: QUERIES (correct please) 1. Produce a listing: custName, No_of_orders, av

Question

MYSQL: QUERIES (correct please)

1. Produce a listing: custName, No_of_orders, avg_order_amt, where the middle column is the total number of orders for the customer and the last column is the average order amount for that customer. HINT: Use column aliases to get the column headings listed above to display for the output. Also, this requires two aggregate functions to be listed in the SELECT clause.

2.. List the orders that were shipped between January 2003 and December 2003.

3. Find the customerID, customer name, orderID and total value of the order for those orders that have not been shipped. HINT: Orders that have not been shipped have a NULL value for the shipDate.

4. Update the shipDate for orderID 10007 to today’s date.

Meta data:

Explanation / Answer

1)

Select c.cname as custName, sum(o.orderID) as No_of_orders, avg(i.price) as avg_order_amt
From Customer c, Item i
Join Order_ o
On o.customerID = c.customerID and o.itemID = i.itemID;

2)

Select c.customerID, c.cname, o.orderID
From Order_ o
Join Customer c
On c.customerID = o.customerID
Where o.orderDate = Null;

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

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