murach's SQL SERVER 2016: Guitar Shop

guitarShop2.sql

/*
	Ch. 4 Guitar Shop
	10/11/17
*/

/*
Exercise: 1 (COMPLETE)
	Write a SELECT statement that joins the Categories table to the Products table and returns these columns:
		CategoryName, ProductName, ListPrice
	Sort the result set by CategoryName and then by ProductName in ASC order.
*/
SELECT CategoryName
	, ProductName
	, ListPrice
FROM Categories JOIN Products 
	ON Categories.CategoryID = Products.CategoryID
ORDER BY CategoryName
	, ProductName

/*
Exercise: 2 (COMPLETE)
	Write a SELECT statement that joins the Customers table to the Addresses table and returns these columns:
		FirstName, LastName, Line1, City, State, ZipCode
	Return one row for each address for the customer with an email address of allan.sherwood@yahoo.com
*/
SELECT FirstName
	, LastName
	, Line1
	, City
	, State
	, ZipCode
FROM Customers JOIN Addresses 
	ON Customers.CustomerID = Addresses.CustomerID
WHERE EmailAddress = 'allan.sherwood@yahoo.com'

/*
Exercise: 3 (COMPLETE)
	Write a SELECT statement that joins the Customers table to the Addresses table and returns these columns:
		FirstName, LastName, Line1, City, State, ZipCode
	Return one row for each customer, but only return addresses that are the shipping address for a customer.
*/
SELECT FirstName
	, LastName
	, Line1
	, City
	, State
	, ZipCode
FROM Customers JOIN Addresses
	ON Customers.CustomerID = Addresses.CustomerID
WHERE ShippingAddressID = AddressID

/*
Exercise: 4 (COMPLETE)
	Write a SELECT statement that joins the Customers, Orders, OrderItems, and Product Tables.
	This statement should return these columns:
		LastName, FirstName, OrderDate, ProductName, ItemPrice, DiscountAmount, and Quantity.
	Use aliases for the tables.
	Sort the final result set by LastName, OrderDate, and ProductName.
*/
SELECT LastName AS [Customer Last Name]
	, FirstName AS [Customer First Name]
	, OrderDate AS [Order Date]
	, ProductName AS [Name of Product]
	, ItemPrice AS [Item Price]
	, DiscountAmount AS [Discount]
	, Quantity AS [Amount Ordered]
FROM Customers  
	JOIN Orders	ON Customers.CustomerID = Orders.CustomerID
	JOIN OrderItems ON OrderItems.OrderID = Orders.OrderID
	JOIN Products ON Products.ProductID = OrderItems.ProductID
ORDER BY LastName
	, OrderDate
	, ProductName

/*
Exercise: 5 (COMPLETE)
	Write a SELECT statement that returns the ProductName and ListPrice columns from the Products table.
	Return one row for each product that has the same list price as another product.
	(Hint: Use a self-join to check that the ProductID columns aren't equal but the ListPrice column is equal.)
	Sort the result set by ProductName.
*/
SELECT Products1.ProductName
	, Products1.ListPrice
FROM Products AS Products1 
	JOIN Products AS Products2 ON Products1.ProductID <> Products2.ProductID
	AND Products1.ListPrice = Products2.ListPrice
ORDER BY ProductName

/*
Exercise: 6 (??? Supposed to be an empty return set? CategoryName is the only one with NULL values.)
	Write a SELECT statement that returns these two columns:
		CategoryName, The CategoryName column from the Categories table 
		ProductID, The ProductID column from the Products table
	Return one row for each category that has never been used.
	(Hint: Use an outer join and only return rows where the ProductID column contains a null value.)
*/
SELECT CategoryName
	, ProductID
FROM Categories FULL
	JOIN Products ON Categories.CategoryID = Products.ProductID
WHERE ProductID IS NULL

/*
Exercise: 7 (COMPLETE)
	Use the UNION operator to generate a result set consisting of three columns from the Orders table:
		ShipStatus, A calculated column that contains a value of SHIPPED or NOT SHIPPED
		OrderID, The OrderID column
		OrderDate, The OrderDate column
	If the order has a value in the ShipDate column, the ShipStatus column should contain a value of SHIPPED.
	Otherwise, it should contain a value of NOT SHIPPED.
	Sort the final result set by OrderDate
*/
	SELECT 'Shipped' AS [Status]
		, OrderID AS [Order ID]
		, OrderDate AS [Order Date]
	FROM Orders
	WHERE ShipDate IS NOT NULL
UNION
	SELECT 'Not Shipped' AS [Status]
		, OrderID AS [Order ID]
		, OrderDate AS [Order Date]
	FROM Orders
	WHERE ShipDate IS NULL
ORDER BY OrderDate