SQL Assignment 5

SQLAssignment5MB.sql

/*
--	FILE: SQLAssignment5MB.sql
--	AUTHOR: Marcus Bizal
--	DATE: 2015-07-14
--
--  	NOTE: These queries use the Northwind sample database.
*/

-- 1.
-- Find out how many orders were shipped late. An order is shipped late if the shipped date is later than the required date. Call the expression Shipped Late.

SELECT COUNT(OrderID) AS ShippedLate
FROM Orders
WHERE ShippedDate > RequiredDate;

-- 2. 
-- List the Product ID, Product Name, Units in Stock and CategoryID of all products in Category 2 whose number of units in stock is more than average of all products.

SELECT ProductID, ProductName, UnitsInStock, CategoryID
FROM Products
WHERE CategoryID = 2
AND UnitsInStock > (SELECT AVG(UnitsInStock) FROM Products);

-- 3. 
-- List the Product ID, Product Name, Units in Stock and CategoryId of all products whose number of units in stock is more than the average units in stock of the products in Category 2.

SELECT ProductID, ProductName, UnitsInStock, CategoryID
FROM Products
WHERE UnitsInStock > (SELECT AVG(UnitsInStock) FROM Products WHERE CategoryID = 2);

-- 4. 
-- Using a subquery, list the Customer ID, Contact name, and country for all customers who do not live in USA, UK, Australia or Canada.

SELECT CustomerID, ContactName, Country
FROM Customers
WHERE Country NOT IN ('USA', 'UK', 'Australia', 'Canada'); -- Subquery is not necissary here. It would be redundant.

-- 5. 
-- Using a subquery find all customers with no orders.

SELECT *
FROM Customers
WHERE NOT EXISTS (SELECT * FROM Orders WHERE Orders.CustomerID = Customers.CustomerID);

-- 6. 
-- List the Employee ID and employee name (concatenate first and last names) who report to Andrew Fuller. Order the list by last name.

SELECT EmployeeID, FirstName + ' ' + LastName AS EmployeeName
FROM Employees
WHERE ReportsTo = 2
ORDER BY LastName;


-- 7.
-- Show the Customer ID, Company Name and Contact Name of all customers with a company name that is, at most, 12 characters in length.

SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE LEN(CompanyName) <= 12;