Answer:
1. SELECT e.EmployeeID, e.FirstName, e.LastName, COUNT(*) as OrderByEmployee FROM Employees e
JOIN Orders o
ON e.EmployeeID = o.EmployeeID
WHERE YEAR(o.OrderDate) = '1996'
GROUP BY e.EmployeeID,e.FirstName,e.LastName
ORDER BY OrderByEmployee DESC
2. SELECT o.OrderID,c.CustomerID, o.OrderDate,SUM((od.Quantity)*
od.UnitPrice - od.Discount)) as TotalCost FROM Orders o
JOIN [Order Details] od
ON od.OrderID = o.OrderID
JOIN Customers c
ON o.CustomerID = c.CustomerID
WHERE (MONTH(OrderDate)= 7 OR MONTH(OrderDate) = 8) and
YEAR(OrderDate) = 1996
GROUP BY o.OrderID,c.CustomerID, o.OrderDate
ORDER BY TotalCost DESC
3. SELECT c.CustomerID, c.CompanyName, c.City, COUNT(o.OrderID) as TotalOrder, SUM(od.Quantity* od.UnitPrice) as TotalOrderAmount FROM Customers c
JOIN Orders o
ON o.CustomerID = c.CustomerID
JOIN [Order Details] od
ON od.OrderID = o.OrderID
WHERE c.Country = 'USA'
GROUP BY c.CustomerID, c.CompanyName, c.City
ORDER BY c.City, c.CustomerID
4. SELECT c.CustomerID, c.CompanyName, c.City, COUNT(o.OrderID) as TotalOrder, SUM(od.Quantity* od.UnitPrice) as TotalOrderAmount FROM Customers c
JOIN Orders o
ON o.CustomerID = c.CustomerID
JOIN [Order Details] od
ON od.OrderID = o.OrderID
WHERE c.Country = 'USA'
GROUP BY c.CustomerID, c.CompanyName, c.City
HAVING COUNT(o.OrderID) > 3
ORDER BY c.City, c.CustomerID
5. SELECT p.ProductID, p.ProductName, s.ContactName as SupplierName, MAX(o.OrderDate) as LastOrderDateOfProduct FROM Products p
JOIN Categories c
ON c.CategoryID = p.CategoryID
JOIN Suppliers s
ON s.SupplierID = p.SupplierID
JOIN [Order Details] od
ON od.ProductID = p.ProductID
JOIN Orders o
ON o.OrderID = od.OrderID
where c.CategoryName = 'Grains/Cereals'
GROUP BY p.ProductID, p.ProductName, s.ContactName
ORDER BY SupplierName, p.ProductName
6. SELECT p.ProductID, p.ProductName, Count(DISTINCT c.CustomerID ) as OrderByThisManyDistinctCustomer
FROM Products p
JOIN [Order Details] od
ON od.ProductID = p.ProductID
JOIN Orders o
ON o.OrderID = od.OrderID
JOIN Customers c
ON c.CustomerID = o.CustomerID
where YEAR(o.OrderDate) = 1996
GROUP BY p.ProductID, p.ProductName
Explanation:
The six query statements returns data from the SQL server Northwind database. Note that all the return data are grouped together, this is done with the 'GROUP BY' Sql clause.
