/* *** EX1-QACS.A *** */
SELECT *
	FROM CUSTOMER
;

SELECT *
	FROM ITEM
;

SELECT *
	FROM SALE
;

SELECT *
	FROM SALE_ITEM
;

/* *** EX1-QACS.B *** */
SELECT LastName, FirstName, Phone
	FROM CUSTOMER
;

/* *** EX1-QACS.C *** */
SELECT LastName, FirstName, Phone
	FROM CUSTOMER
		WHERE FirstName LIKE 'John'
;

/* *** EX1-QACS.D *** */
SELECT c.LastName, c.FirstName, c.Phone, s.SaleDate, s.Total
	FROM CUSTOMER c, SALE s
		WHERE c.CustomerID = s.CustomerID AND s.Total > 100
;

/* *** EX1-QACS.E *** */
SELECT LastName, FirstName, Phone
	FROM CUSTOMER
		WHERE FirstName LIKE 'D%'
;

/* *** EX1-QACS.F *** */
SELECT LastName, FirstName, Phone
	FROM CUSTOMER
		WHERE LastName LIKE '%ne%'
;

/* *** EX1-QACS.G *** */
SELECT LastName, FirstName, Phone
	FROM CUSTOMER
		WHERE PHONE LIKE '%56_'
;

/* *** EX1-QACS.H *** */
SELECT MAX(Total) AS MaxSalesTotal, MIN(Total) AS MinSalesTotal
	FROM SALE
;

/* *** EX1-QACS.I *** */
SELECT AVG(Total) AS AvgSalesTotal
	FROM SALE
;

/* *** EX1-QACS.J *** */
SELECT COUNT(*) AS NumCustomers
	FROM CUSTOMER
;

/* *** EX1-QACS.K *** */
SELECT LastName, FirstName
	FROM CUSTOMER
		GROUP BY LastName, FirstName
;

/* *** EX1-QACS.L *** */
SELECT LastName, FirstName, COUNT(*) AS NameCombo
	FROM CUSTOMER
		GROUP BY LastName, FirstName
;

/* *** EX1-QACS.M *** */
SELECT LastName, FirstName, Phone
	FROM CUSTOMER
		WHERE CustomerID IN
			(SELECT CustomerID
				FROM SALE
					WHERE Total > 100
			)
		ORDER BY LastName ASC, FirstName DESC
;
			
/* *** EX1-QACS.N *** */
SELECT DISTINCT c.LastName, c.FirstName, c.Phone
	FROM CUSTOMER c, SALE s
		WHERE c.CustomerID = s.CustomerID  AND s.Total > 100
			ORDER BY LastName ASC, FirstName DESC
;

/* *** EX1-QACS.O *** */
SELECT DISTINCT c.LastName, c.FirstName, c.Phone
	FROM CUSTOMER c JOIN SALE s 
		ON c.CustomerID = s.CustomerID
			WHERE s.Total > 100
				ORDER BY LastName ASC, FirstName DESC
;

/* *** EX1-QACS.P *** */
SELECT LastName, FirstName, Phone
	FROM CUSTOMER
		WHERE CustomerID IN
			(SELECT CustomerID
			 FROM SALE
		 	 WHERE SaleID IN
				(SELECT SaleID
				 FROM SALE_ITEM
				 WHERE ItemID IN
					(SELECT ItemID
					 FROM ITEM
					 WHERE ItemDescription LIKE 'Desk Lamp'
					)
				)		
			)
			ORDER BY LastName ASC, FirstName DESC
;

/* *** EX1-QACS.Q *** */
SELECT c.LastName, c.FirstName, c.Phone
	FROM CUSTOMER c, SALE s, Sale_ITEM si, ITEM i
		WHERE c.CustomerID = s.CustomerID
		 AND  s.SaleID = si.SaleID
		 AND  si.ItemID = i.ItemID
		 AND  i.ItemDescription LIKE 'Desk Lamp'
			ORDER BY LastName ASC, FirstName DESC
;

/* *** EX1-QACS.R *** */
SELECT c.LastName, c.FirstName, c.Phone
	FROM CUSTOMER c JOIN SALE s ON c.CustomerID = s.CustomerID
	  JOIN Sale_ITEM si ON s.SaleID = si.SaleID
	  JOIN ITEM i ON si.ItemID = i.ItemID
		WHERE i.ItemDescription LIKE 'Desk Lamp'
			ORDER BY LastName ASC, FirstName DESC
;

/* *** EX1-QACS.S *** */
SELECT c.LastName, c.FirstName, c.Phone
	FROM CUSTOMER c JOIN SALE s ON c.CustomerID = s.CustomerID
	  JOIN Sale_ITEM si ON s.SaleID = si.SaleID
	  	WHERE si.ItemID IN
			(SELECT ItemID 
			 FROM ITEM 
			 WHERE ItemDescription LIKE 'Desk Lamp'
			)
			ORDER BY LastName ASC, FirstName DESC
;

/* *** EX1-QACS.T *** */
SELECT c.LastName, c.FirstName, c.Phone
	FROM CUSTOMER c JOIN SALE s ON c.CustomerID = s.CustomerID
		WHERE s.SaleID IN	
		  (SELECT SaleID
		   FROM SALE_ITEM
		   WHERE ItemID IN
			(SELECT ItemID
			 FROM ITEM
			 WHERE ItemDescription LIKE 'Desk Lamp'
			)
      	  )
			ORDER BY LastName ASC, FirstName DESC
;

/* *** EX1-QACS.U *** */
SELECT c.LastName, c.FirstName, c.Phone, 'Item' = 
	CASE 
		WHEN i.ItemDescription = 'Desk Lamp' THEN i.ItemDescription 
		ELSE ''
	END
	FROM CUSTOMER c JOIN SALE s ON c.CustomerID = s.CustomerID
	  JOIN Sale_ITEM si ON s.SaleID = si.SaleID
	  JOIN ITEM i ON si.ItemID = i.ItemID
		ORDER BY 'Item' DESC, LastName ASC, FirstName DESC
;