Question

Questions are based on the Northwind database.

a. A data dictionary (Excel file) that describes each of the tables. (Attached in question)

b.    Write good, clean SQL that answers the following questions.

c.     Separate your queries as I have done in the following example. End each query with a semicolon (;) and insert the word "go" between each query. Queries can span multiple lines if required.

Select CustomerID from Customers;

go

Select Count(*) from Employees;

go

Select max(productID) from Products;

Condensed... int nvarchar(40) int Employee Territories Column Name Condensed T... EmployeeID int 8 TerritoryID nvarchar(20) Order Details Column ... Conde... Order ID int 099 ProductID int UnitPrice money Quantity smallint Discount Products Column Name * ProductID Product Name Supplier ID CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued Condensed... int nchar(5) int datetime datetime datetime int nvarchar(20) real Customers Column Name Condensed... * CustomerID nchar(5) CompanyName nvarchar(40) Contact Name nvarchar(30) ContactTitle nvarchar(30) Address nvarchar(60) City nvarchar(15) Region nvarchar(15) PostalCode nvarchar(10) Country nvarchar(15) Phone nvarchar(24) Fax nvarchar(24) money smallint Orders Column Name OrderID Customer ID EmployeeID OrderDate RequiredDate ShippedDate Ship Via Freight ShipName ShipAddress Ship City ShipRegion ShipPostalCode Ship Country T int - smallint Smallint bit Categories Column Name Condensed... 8 CategoryID int CategoryName nvarchar(15) Description ntext Picture image Condensed ... Employees Column Name 8 EmployeeID LastName money nvarchar(40) nvarchar(60) nvarchar(15) nvarchar(15) nvarchar(10) nvarchar(15) int FirstName Title CustomerCustomerDemo Column Name Condensed T... Customer ID nchar(5) * Customer... nchar(10) Suppliers Column Name Condensed... SupplierID int CompanyName nvarchar(40) Contact Name nvarchar(30) ContactTitle nvarchar(30) Address nvarchar(60) City nvarchar(15) Region nvarchar(15) PostalCode nvarchar(10) Country nvarchar(15) Phone nvarchar(24) Fax nvarchar(24) HomePage ntext Territories Column Name TerritoryID TerritoryDescription RegionID Condensed... nvarchar(20) nchar(50) int TitleOfCourtesy BirthDate HireDate Address City Region PostalCode Country HomePhone Extension nvarchar(20) nvarchar(10) nvarchar(30) nvarchar(25) datetime datetime nvarchar(60) nvarchar(15) nvarchar(15) nvarchar(10) nvarchar(15) nvarchar(24) nvarchar(4) image ntext Shippers Column Name Condensed... ShipperID CompanyName nvarchar(40) Phone nvarchar(24) Customer Demographics Column Name Conden... CustomerTypeID nchar(10) Customer Descntext Photo Region Column Name 9 RegionID RegionDescription Conden... int nchar(50) Notes ReportsTo PhotoPath int nvarchar(255)

18. Produce a count of the employees by each sales region

19. List the dollar values for sales by region?

20. What is the average value of a sales order?

21. List orders (OrderID, OrderDate, Customer Name) where the total order value is greater than the average value of a sales order?

22. Produce a customer report (must also include those we have not yet done business with) showing CustomerID, Customer name and total sales made to that customer

23. List all products that need to be re-ordered. Do not include discontinued products in this report.

24. List all customers that we made a sale to in the year 1996

25. List all customers that we did not make a sale to in the year 1996

Answer 1

18.

Select count(EmployeeID) from Employees group by region;

19.

Select region,Sum(unitPrice*quantity) from OrderDetails inner join Orders on OrderDetails.orderID = Orders.orderID inner join Employees on Orders.EmployeeID = Employees.EmployeeID group by region;

20.

Select avg(unitPrice*quantity) from OrderDetails;

21.

Select OrderID,OrderDate,CompanyName from Customers inner join Orders on Customers.customerID = Orders.customerID inner join OrderDetails on OrderDetails.orderID = Orders.orderID group by OrderID,OrderDate,CompanyName having sum(quantity*unitPrice) > avg(sum(quantity*unitPrice));

22.

Select CustomerID , CompanyName , Sum(UnitPrice*quantity) from Customers left join Orders on Customers.customerID = Orders.customerID inner join OrderDetails on OrderDetails.orderID = Orders.orderID;

23.

Select * from products where reorderlevel = 0 and discontinued = 'F';

24.

Select * from Customers,Orders where Customers.customerID = Orders.customerID and year(orderDate) = 1996;

25.

Select * from Customers,Orders where Customers.customerID != Orders.customerID and year(orderDate) = 1996;

Do ask if any doubt. Please up-vote.