In this section, you will have to create a query based on the table structure be

Question

In this section, you will have to create a query based on the table structure below. You can try out the result of the query here: http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

In each number, you will be given a statement on what to show and also a sample of the result. You will then write down the query needed to show that data.

1.      Show all orders created by Steven Buchanan

2.      Show the Grand Total of each order value

3.      Show the product supplier list + category. Show it in order of category name from A to Z

4.      Show the number of order handled per shipper

5.      Show list of each product name with their categories and how many has been sold, then order it by Category Name from A to Z

Shipper ShipperlD ShipperName Phone Customers Orders Products Categories CustomerlD CustomerName ContactName OrderlD CustomerlD EmployeelD OrderDate ShipperlD ProductID ProductName SupplierlD CategorylD CategorylD CategoryName Description PostalCode EmployeelD LastName FirstName BirthDate OrderDetailD OrderlD ProductlD SupplierlD SupplierName ContactName PostalCode

Explanation / Answer

1)

SELECT *
FROM Orders
where EmployeeID in(select EmployeeID from employees where FirstName='Steven' and LastName='Buchanan');

3)

select *
from Products p,Suppliers s,Categories c
where p.categoryID=c.categoryID and p.SupplierID=s.SupplierID

4)

select s.ShipperName,s.phone,count(o.orderID)
from Orders o,shippers s
where o.ShipperId=s.shipperid
group by s.shipperName,s.phone

5)

select c.categoryname,p.productname,count(o.quantity)
from Products p,categories c,orderdetails o
where p.categoryID=c.categoryid
and p.productID=o.productID
group by c.categoryname,p.productname
order by c.categoryname

2)

select o.orderID,c.customerName,o.orderDate,sum(od.quantity*p.price) as grandtotal
from orders o,orderdetails od,customers c,products p
where c.customerID=o.customerID
and o.orderID=od.orderID
and p.productID=od.productID
group by o.orderID,c.customerName,o.orderDate

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

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