/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg, Yoder - DBC (8th Edition) CH03					*/
/*																				*/
/*  James River Jewelry Database Queries										*/
/*																				*/
/*	These are the MySQL 5.7							SQL code solutions			*/
/*																				*/
/********************************************************************************/

/*****   Questions A and B   ***********************************************************/

/*****   James_River_Jewelry CREATE TABLE statements   **************************/

/*       See the files:	 DBC-e08-MySQL-JRJ-Create-Tables.sql					*/

/*****   Question C   ***********************************************************/

/*****   James_River_Jewelry INSERT statements   ********************************/

/*       See the files:	 DBC-e08-MySQL-JRJ-Insert-Data.sql						*/

/*****   Question D   ***********************************************************/

/*****   CUSTOMER DATA  *********************************************************/

SELECT * FROM CUSTOMER;

/*****   ITEM DATA  *************************************************************/

SELECT * FROM ITEM;

/*****   PURCHASE DATA   ********************************************************/

SELECT * FROM PURCHASE;

/*****   PURCHASE_ITEM   ********************************************************/

SELECT * FROM PURCHASE_ITEM;

/*****   Question E   ***********************************************************/

SELECT		ItemNumber, ItemDescription
FROM		ITEM
WHERE		Cost > 100;

/*****   Question F   ***********************************************************/

SELECT		ItemNumber, ItemDescription
FROM		ITEM
WHERE		Cost > 100
	AND		ArtistLastName LIKE '%son';

/*****   Question G   ***********************************************************/

SELECT		LastName, FirstName
FROM		CUSTOMER
WHERE		CustomerID IN
			(SELECT		CustomerID
			 FROM		PURCHASE
			 WHERE		PreTaxAmount > 200);

/*****   Question H   ***********************************************************/

SELECT		LastName, FirstName
FROM		CUSTOMER JOIN PURCHASE
			ON	CUSTOMER.CustomerID = PURCHASE.CustomerID
WHERE		PreTaxAmount > 200;

/*****   Question I   ***********************************************************/

SELECT		LastName, FirstName
FROM		CUSTOMER
WHERE		CustomerID IN
			(SELECT	CustomerID
			 FROM		PURCHASE
			 WHERE		InvoiceNumber IN
						(SELECT	InvoiceNumber
						 FROM		PURCHASE_ITEM
						 WHERE		RetailPrice > 50));

/*****   Question J   ***********************************************************/

SELECT		DISTINCT LastName, FirstName
FROM		CUSTOMER JOIN PURCHASE
	ON		CUSTOMER.CustomerID = PURCHASE.CustomerID
			JOIN PURCHASE_ITEM
	ON		PURCHASE.InvoiceNumber = PURCHASE_ITEM.InvoiceNumber
WHERE		RetailPrice > 50;


/*****   Question K   ***********************************************************/

SELECT		LastName, FirstName
FROM		CUSTOMER
WHERE		CustomerID IN
			(SELECT		CustomerID
			 FROM		PURCHASE
			 WHERE		InvoiceNumber IN
						(SELECT		InvoiceNumber
						 FROM		PURCHASE_ITEM
						 WHERE		ItemNumber IN
									(SELECT		ItemNumber
									 FROM		ITEM
									 WHERE		ArtistLastName LIKE 'J%')));

/*****   Question L   ***********************************************************/

SELECT		DISTINCT LastName, FirstName
FROM		CUSTOMER JOIN PURCHASE
		ON	CUSTOMER.CustomerID = PURCHASE.CustomerID
			JOIN	PURCHASE_ITEM
		ON	PURCHASE.InvoiceNumber = PURCHASE_ITEM.InvoiceNumber
			JOIN	ITEM
		ON	PURCHASE_ITEM.ItemNumber = ITEM.ItemNumber
WHERE		ArtistLastName LIKE 'J%';

/*****   Question M   ***********************************************************/

SELECT		LastName, FirstName, SUM(PreTaxAmount) AS TotalPreTaxAmount
FROM		CUSTOMER JOIN PURCHASE
			ON CUSTOMER.CustomerID = PURCHASE.CustomerID
GROUP BY	LastName, FirstName;

/*****   Question N   ***********************************************************/

SELECT		ArtistLastName, ArtistFirstName, SUM(PreTaxAmount) AS TotalPreTaxAmount
FROM		ITEM JOIN PURCHASE_ITEM
			ON	ITEM.ItemNumber = PURCHASE_ITEM.ItemNumber
				JOIN	PURCHASE
						ON	PURCHASE_ITEM.InvoiceNumber = PURCHASE.InvoiceNumber
GROUP BY	ArtistLastName, ArtistFirstName
ORDER BY	ArtistLastName, ArtistFirstName;

/*****   Question O   ***********************************************************/

SELECT		ArtistLastName, ArtistFirstName, SUM(PreTaxAmount) AS TotalPreTaxAmount
FROM		ITEM JOIN PURCHASE_ITEM
		ON	ITEM.ItemNumber = PURCHASE_ITEM.ItemNumber
			JOIN	PURCHASE
		ON	PURCHASE_ITEM.InvoiceNumber = PURCHASE.InvoiceNumber
WHERE		PreTaxAmount <= 25
GROUP BY	ArtistLastName, ArtistFirstName
ORDER BY	ArtistLastName DESC, ArtistFirstName DESC;

/*****   Question P   ***********************************************************/

SELECT		LastName, FirstName, PURCHASE.InvoiceNumber, InvoiceDate,
			PURCHASE_ITEM.ItemNumber, ItemDescription,
			ArtistLastName, ArtistFirstName
FROM		CUSTOMER JOIN PURCHASE
		ON	CUSTOMER.CustomerID = PURCHASE.CustomerID
			JOIN	PURCHASE_ITEM
		ON	PURCHASE_ITEM.InvoiceNumber = PURCHASE.InvoiceNumber
			RIGHT JOIN	ITEM
		ON  PURCHASE_ITEM.ItemNumber = ITEM.ItemNumber
ORDER BY	ArtistLastName, ArtistFirstName;



/*****   Question Q   ***********************************************************/

SELECT * FROM ITEM;

UPDATE		ITEM
	SET		ArtistFirstName = 'Rex'
	WHERE	ArtistLastName = 'Baxter';

SELECT * FROM ITEM;

/*       To undo:                                                               */

UPDATE		ITEM
	SET		ArtistFirstName = 'Sam'
	WHERE	ArtistLastName = 'Baxter';

SELECT * FROM ITEM;


/*****   Question R   ***********************************************************/

UPDATE		ITEM
	SET		ArtistLastName = 'temp'
	WHERE	ArtistLastName = 'Baker';

UPDATE		ITEM
	SET		ArtistLastName = 'Baker'
	WHERE	ArtistLastName = 'Baxter';

UPDATE		ITEM
	SET		ArtistLastName = 'Baxter'
	WHERE	ArtistLastName = 'temp';

SELECT * FROM ITEM;
 
/*       To undo:                                                               */

UPDATE		ITEM
SET			ArtistLastName = 'temp'
WHERE		ArtistLastName = 'Baxter';

UPDATE		ITEM
SET			ArtistLastName = 'Baxter'
WHERE		ArtistLastName = 'Baker';

UPDATE		ITEM
SET			ArtistLastName = 'Baker'
WHERE		ArtistLastName = 'temp';

SELECT * FROM ITEM;
 
/*****   Question S   ***********************************************************/

/*       DO NOT RUN THE SOLUTION TO QUESTION S!									*/

/* DELETE FROM PURCHASE; */
/*    This CASCADES through PURCHASE_ITEM,            */
/*    so DELETE FROM PURCHASE_ITEM; is NOT needed.    */
/* DELETE FROM ITEM; */
/* DELETE FROM CUSTOMER; */

/********************************************************************************/
