Question

Create the following SQL Server queries that access the Northwind database

1. Same as problem 3 but, limit the list to all customers who placed 3 or more orders.
2. The product id, product name, last date that the product was ordered for all items in the Grains/Cereals category. (Hint: Use MAX)
3. The product ID, product name, and number of distinct customers who ordered that product in 1996

Thank you.

Categories Customers Employees Order Details Orders Products Shippers 9 CategoryID CategoryName Description Picture 1 OrderID 1 ProductID UnitPrice Quantity Discount 1 ShipperID Company Name Phone Customer CompanyName ContactName ContactTitle Address City Region PostalCode Country Phone Fax Employeeld LastName FirstName Title TitleOfCourtesy Birth Date HireDate Address 1 OrderID CustomerlD Employeeld Order Date Required Date ShippedDate ShipVia Freight ShipName ShipAddress ShipCity ShipRegion ShipPostal Code Ship Country 1 ProductID Product Name SupplierlD CategoryID QuantityPerUnit UnitPrice UnitsInStock Units OnOrder ReorderLevel Discontinued Suppliers City Region Postal Code Country Home Phone Extension Photo Notes Reports To 1 Supplier D Company Name ContactName ContactTitle Address City Region Postal Code Country Phone Fax HomePage

Answer 1

This demonstration is using Microsoft SQL Sever.

Question 1:

SQL Query :

select customers.customerID,CompanyName from customers,orders
where customers.customerID=orders.customerID
group by customers.customerID,CompanyName
having count(OrderID)>=3;

Explanation :SQL query given above will join two tables customers,orders based on customerID and will return the result.

****************************

Question 2:

SQL Query :

select products.productID, ProductName,max(OrderDate) as 'last date'
from
products,[Order Details],orders,Categories
where
products.productID=[Order Details].productID and
[Order Details].OrderID=orders.OrderID and
products.CategoryID=Categories.CategoryID and
CategoryName='Grains/Cereals'
group by products.productID, ProductName;

Explanation :The SQL query given above will join four tables products,[Order Details],orders,Categories and will return the result.

****************************

Question 3:

SQL Query :

select products.productID, ProductName,
count(distinct(orders.customerID)) as ' number of distinct customers'
from
products,[Order Details],orders,customers
where
products.productID=[Order Details].productID and
[Order Details].OrderID=orders.OrderID and
orders.customerID=customers.customerID and
year(orderdate)=1996
group by products.productID, ProductName;

Explanation :This SQL query will join products,[Order Details],orders,customers tables and return the result.

****************************