I need assistance with the following SQL queries.

Northwind SQL Queries

I need assistance with questions 20 through 32. I was able to get started on this assignment but now I'm stuck. Please help!

/* DB Design & SQL Northwind SQL Lab

Write the SQL queries using the database defined.*/

use Northwind;

-- Use Northwind Database /* 1. For each product list its name, unit price, and how many units we have in stock. */ SELECT ProductName, UnitPrice, UnitsInStock FROM Products;

/* 2.List the product name and units in stock for any product that has a units in stock greater than 10 and less than 50. */ SELECT * FROM Products WHERE UnitsInStock > 10 AND UnitsInStock

/* 3.List the product name, unit price for each product with a unit price greater than $100. Sort the list with the largest unit price on top. */ SELECT * FROM Products WHERE UnitPrice > 100 ORDER BY ProductName asc;

/*4. Create a list of products that should be re-ordered (Note: the products should not be discontinued (discontinued: 1=True; 0=False and total on hand and products on order should be less than the reorder level. */ SELECT ProductName, UnitsOnOrder , UnitsInStock FROM Products WHERE (((Discontinued)=1) AND ((UnitsInStock)

/* 5.Create a list of products that have been discontinued */ SELECT * FROM Products WHERE Discontinued = 1;

/* 6. Create a list of all the products (prod_id and name) if all the following are true (7 records) Supplierid = 2, 5, 16, 8, or 9 Categoryid = 1, 2, or 4 Unitprice > 15.00 */ SELECT ProductID, ProductName FROM Products WHERE (Supplierid = '2' OR Supplierid = '5' OR Supplierid = '16' OR Supplierid = '8' OR Supplierid = '9') AND (CategoryID = '1' OR CategoryID = '2' OR CategoryID = '4') AND (UnitPrice > '15.00');

/* 7. Create a list of all the products (prod_id and name) if all the following are true (11 records)

Supplierid = 2, 5, 16, 8, or 9 AND Categoryid = 1, 2, or 4 AND Unitprice > 15.00 OR Supplierid = 1, 4, 8 AND categoryid= 3 or 4 */ SELECT ProductID, ProductName FROM Products WHERE (Supplierid = '2' OR Supplierid = '5' OR Supplierid = '16' OR Supplierid = '8' OR Supplierid = '9') AND (CategoryID = '1' OR CategoryID = '2' OR CategoryID = '4') AND (UnitPrice > '15.00') OR (SupplierID = 1 OR SupplierID = 4 OR SupplierID = 8) AND (CategoryID = 3 OR CategoryID = 4);

/* 8. Create a list of product names that have the second letter of the name = h (8 records) */ SELECT ProductName FROM Products WHERE ProductName LIKE '_h%';

/* 9. Create a list of product names that have the second letter of the name = a and the last letter = e (2 records) */ SELECT ProductName FROM Products WHERE ProductName LIKE '_a%' AND ProductName LIKE '%_e';

/* 10. List all the customers that have one of the following fields NULL (Region or Fax). Also the title of the contact should be Owner Sort the list by contact name (14 records) */ SELECT * FROM Customers WHERE ContactTitle LIKE 'Owner' AND (Region IS NULL OR Fax IS NULL);

/* 11. List each employees name (first and last in one column) and their birthdate. Sort the list by birthdate. */ SELECT FirstName+' '+LastName as EmployeeName, BirthDate as birthday FROM Employees ORDER by BirthDate;

/* 12. Which employees were born in 1963? */ /*Michael Suyama and Janet Leverling*/ SELECT FirstName+' '+LastName as EmployeeName, BirthDate as birthday FROM Employees WHERE BirthDate LIKE '1963%' ORDER by BirthDate;

/* 13. How many employees does Northwind have? */ /* 9 */ SELECT * FROM Employees;

/*14. For each customer (customer id) list the date of the first order they placed and the date of the last order they placed. */ SELECT DISTINCT customers.CustomerID, orders.OrderDate FROM Customers, Orders WHERE OrderDate = (select max(OrderDate) from Orders) OR OrderDate = (select min(OrderDate) from Orders) ORDER BY CustomerID;

/* 15. Using question 14, only list customers where there last order was in 2011. Sort the list by customer. */ SELECT DISTINCT customers.CustomerID, orders.OrderDate FROM Customers, Orders WHERE OrderDate = (select min(OrderDate) from Orders) OR YEAR(OrderDate) = YEAR(2011) ORDER BY CustomerID;

/* 16. Which employees were born in the month of July? */ SELECT FirstName, LastName, BirthDate FROM Employees where MONTH(BirthDate)=7;

/* 17. How many orders has Northwind taken? (Answer 830) */ SELECT * FROM Orders

/* 18. How many orders were placed per year? */ SELECT COUNT(DISTINCT OrderDate) AS OrdersPerYear FROM Orders Group by YEAR(OrderDate)

/* 19. How many orders by month for each year? Make sure the list is in order by year and month? */ SELECT { fn MONTHNAME(OrderDate) } AS MonthName, YEAR(OrderDate) AS Year, count(OrderDate) as Total FROM [Orders] GROUP BY { fn MONTHNAME(OrderDate) }, MONTH(OrderDate), YEAR(OrderDate) order by Year(orderDate),month(OrderDate)

/* 20. Using question 19, list only the months where Northwind have less than 25 orders. (3 records) */

/* 22. List the total amount of sales for all orders.*/

/* 23. For each order detail, list the orderid, productid, and the total sale price (include the discount). HINT: If my quantity was 10, each one cost $20 and had a discount of 10%, my formula may appear as (10*(20*(1 -.10)) result would be 180. */

/* 24. List the total amount of sales for all orders. (with discounts included). Result: 1265793.03974152 */

/* 25. How old is each employee? List the oldest at the top of the list. HINT: Can use DateDiff(interval, date1, date2) function: DATEDIFF(dd, Birthdate, SYSDATETIME())/365 AS Age */

/* 26. Create a list of suppliers (companyname, contactname) and the products (product name) they supply. Sort the list by supplier, then product (77 Records) */

/* 27. Create a list of customers (companyname) and some information about each order (orderid, orderdate, shipdate) they have placed. (830 Records) */

/* 28. Create list of products that were shipped to customers on 04/18/2012. (4 Records) */

/* 29. Create a list of customers that have ordered Tofu. Make sure to list each customer only once. (18 Records) */

/*30. Create a list of customers that have placed and order in 2011 and 2012. Sort the list by customer contact. (65 Records) */

/* 31. Create a mailing list to send information to all the customers, employees, and suppliers. Sort the list by city. (129 records) */

/* 32. Create a view called NumCustomerOrders which lists all the customers and the number of orders they have placed. Be sure to list the customer even if they have not placed an order. (91 records) */

Northwind Database - Entity Relationship Diagram Suppliers Y SupplierID CompanyName ContactName ContactTitle Address City Region PostalCode Country Phone Fax HomePage Products 8 ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock Units OnOrder Reorderlevel Discontinued Order_Details OrderID 9 ProductID UnitPrice Quantity Discount Orders OrderID CustomerID EmployeeID OrderDate RequiredDate ShippedDate Ship Via Freight ShipName ShipAddress Ship City ShipRegion ShipPostalCode Ship Country Customers Y CustomerID Company Name Contact Name Contact Title Address City Region PostalCode Country Phone Fax Navigation Pane Categories 7 CategoryID CategoryName Description Picture Shippers 8 ShipperID Company Name Phone Employees Y EmployeeID LastName FirstName Title TitleOfCourtesy BirthDate HireDate Address City Region PostalCode Country Home Phone Extension Photo Notes ReportsTo Northwind Database - Entity Relationship Diagram Suppliers Y SupplierID CompanyName ContactName ContactTitle Address City Region PostalCode Country Phone Fax HomePage Products 8 ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock Units OnOrder Reorderlevel Discontinued Order_Details OrderID 9 ProductID UnitPrice Quantity Discount Orders OrderID CustomerID EmployeeID OrderDate RequiredDate ShippedDate Ship Via Freight ShipName ShipAddress Ship City ShipRegion ShipPostalCode Ship Country Customers Y CustomerID Company Name Contact Name Contact Title Address City Region PostalCode Country Phone Fax Navigation Pane Categories 7 CategoryID CategoryName Description Picture Shippers 8 ShipperID Company Name Phone Employees Y EmployeeID LastName FirstName Title TitleOfCourtesy BirthDate HireDate Address City Region PostalCode Country Home Phone Extension Photo Notes ReportsTo