IN MYSQL: QUERIES 1. Find the orderID and shipDate for all orders shipped from W
ID: 3770556 • Letter: I
Question
IN MYSQL: QUERIES
1. Find the orderID and shipDate for all orders shipped from Warehouse number 115.
2. List the orderID for orders that were shipped from all warehouses that the company has in New York.
3. 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.
4. List the orders that were shipped between January 2003 and December 2003.
5. 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.
6. Update the shipDate for orderID 10007 to today’s date.
7. List the names of the customers in descending order.
METADATA:
Explanation / Answer
Can help u with these
1)
select Orderid, Ship_date
from Shipment
where Warehousenumber = '115'
2)
select Shipment.Orderid
from Shipment, Warehouse
where Shipment.Warehouse = Warehouse.Warehouse#
and Warehouse.City = "New York"
3)
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;
5)
Select c.customerID, c.cname, o.orderID
From Order_ o
Join Customer c
On c.customerID = o.customerID
Where o.orderDate = Null;