/* *** CS2-A *** */
SELECT *
	FROM CUSTOMER, INVOICE, INVOICE_ITEM ii 
;

/* *** CS2-B *** */
SELECT LastName, FirstName, Phone
	FROM CUSTOMER
;

/* *** CS2-C *** */
SELECT LastName, FirstName, Phone
	FROM CUSTOMER
		WHERE FirstName = 'Nikki'
;

/* *** CS2-D *** */
SELECT LastName, FirstName, Phone, DateIn, DateOut
	FROM CUSTOMER, INVOICE
		WHERE TotalAmount > 100
;

/* *** CS2-E *** */
SELECT LastName, FirstName, Phone
	FROM CUSTOMER
		WHERE FirstName LIKE 'B%'
;

/* *** CS2-F *** */
SELECT LastName, FirstName, Phone
	FROM CUSTOMER
		WHERE LastName LIKE '%cat%'
;

/* *** CS2-G *** */
SELECT LastName, FirstName, Phone
	FROM CUSTOMER
		WHERE Phone Like '_23%'
;

/* *** CS2-H *** */
SELECT MAX(TotalAmount) AS MaxTotalAmount, 
	   MIN(TotalAmount) AS MinTotalAmount
		FROM INVOICE
;

/* *** CS2-I *** */
SELECT AVG(TotalAmount) AS AvgTotalAmount
	FROM INVOICE
;

/* *** CS2-J *** */
SELECT COUNT(CustomerID) AS TotalCustomers
	FROM CUSTOMER
;

/* *** CS2-K *** */
SELECT LastName, FirstName
	FROM CUSTOMER
		GROUP BY LastName, FirstName
;

/* *** CS2-L *** */
SELECT LastName, FirstName, 
	   COUNT(*) AS LastFirstNameCombo
		FROM CUSTOMER
			GROUP BY LastName, FirstName
;

/* *** CS2-M *** */
SELECT LastName, FirstName, Phone
	FROM CUSTOMER
		WHERE CustomerID IN 
			(SELECT CustomerID 
				FROM INVOICE 
					WHERE TotalAmount > 100.0
			)
			ORDER BY LastName ASC, FirstName DESC 
;

/* *** CS2-N *** */
SELECT Cust.LastName, Cust.FirstName, Cust.Phone
	FROM CUSTOMER AS Cust, Invoice AS Inv
		WHERE Inv.TotalAmount > 100.00
			ORDER BY LastName ASC, FirstName DESC
;

/* *** CS2-O *** */
SELECT LastName, FirstName, Phone
	FROM CUSTOMER JOIN INVOICE
		ON CUSTOMER.CustomerID = INVOICE.CustomerID
			WHERE TotalAmount > 100.00
				ORDER BY LastName ASC, FirstName DESC
;

/* *** CS2-P *** */
SELECT LastName, FirstName, Phone
	FROM CUSTOMER
		WHERE CUSTOMER.CustomerID IN 
			(SELECT CustomerID 
			FROM INVOICE 
			WHERE InvoiceNumber IN 
				(SELECT InvoiceNumber
				FROM INVOICE_ITEM
				WHERE Item LIKE 'Dress Shirt'
				)
			)
			ORDER BY LastName ASC, FirstName DESC
;

/* *** CS2-Q *** */
SELECT LastName, FirstName, Phone
	FROM CUSTOMER, INVOICE, INVOICE_ITEM
		WHERE INVOICE_ITEM.Item LIKE 'Dress Shirt'
			ORDER BY LastName ASC, FirstName DESC
;

/* *** CS2-R *** */
SELECT LastName, FirstName, Phone
	FROM CUSTOMER 
		JOIN INVOICE ON CUSTOMER.CustomerID=INVOICE.CustomerID 
		JOIN INVOICE_ITEM ON INVOICE.InvoiceNumber=INVOICE_ITEM.InvoiceNumber
			WHERE Item Like 'Dress Shirt'
				ORDER BY LastName ASC, FirstName DESC
;

/* *** CS2-S *** */
SELECT C.LastName AS CustomerLastName, C.FirstName AS CustomerFirstName, R.LastName AS ReferredByLastName, R.FirstName AS ReferredByFirstName
	FROM CUSTOMER C
		LEFT JOIN CUSTOMER R ON R.CustomerID=C.ReferredBy
;

/* *** CS2-T *** */
SELECT LastName, FirstName, Phone
	FROM CUSTOMER
		JOIN INVOICE ON CUSTOMER.CustomerID=INVOICE.CustomerID
			WHERE INVOICE.InvoiceNumber IN (SELECT InvoiceNumber FROM INVOICE_ITEM WHERE Item LIKE 'Dress Shirt')
				ORDER BY LastName ASC, FirstName DESC
;

/* *** CS2-U *** */
SELECT C.LastName, C.FirstName, C.Phone, 
	"Total Amount" =
	CASE 
		WHEN T.Item = 'Dress Shirt' THEN I.TotalAmount 
		ELSE NULL
	END
	FROM CUSTOMER C, INVOICE I, INVOICE_ITEM T
		WHERE C.CustomerID = I.CustomerID 
		  AND I.InvoiceNumber = T.InvoiceNumber 
				ORDER BY "Total Amount" ASC, LastName ASC, FirstName DESC
;