/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg, Yoder - DBC (8th Edition) CH03, AppE				*/
/*																				*/
/*  The Queen Anne Curiosity Shop [QACS] Database Queries						*/
/*																				*/
/*	These are the MySQL 5.7 SQL code solutions									*/
/*																				*/
/********************************************************************************/

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

/*****   QACS CREATE TABLE statements   *****************************************/

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

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

/*****   QACS INSERT statements   ***********************************************/

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

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

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

SELECT * FROM CUSTOMER;

/*****   EMPLOYEE DATA  *********************************************************/

SELECT * FROM EMPLOYEE;

/*****   VENDOR DATA  ***********************************************************/

SELECT * FROM VENDOR;

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

SELECT * FROM ITEM;

/*****   SALE DATA   ************************************************************/

SELECT * FROM SALE;

/*****   SALE_ITEM   ************************************************************/

SELECT * FROM SALE_ITEM;

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

SELECT		ItemID, ItemDescription
FROM		ITEM
WHERE		ItemPrice >= 1000;

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

SELECT		ItemID, ItemDescription
FROM		ITEM, VENDOR
WHERE		ItemPrice >= 1000
	AND		CompanyName LIKE 'New%'
	AND		ITEM.VendorID = VENDOR.VendorID;

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

SELECT		LastName, FirstName, Phone
FROM		CUSTOMER
WHERE		CustomerID IN 
			(SELECT CustomerID
			 FROM SALE
			 WHERE SaleID = 1);

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

SELECT		LastName, FirstName, Phone
FROM		CUSTOMER JOIN SALE
	ON		CUSTOMER.CustomerID = SALE.CustomerID 
WHERE		SaleID = 1;

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

SELECT		LastName, FirstName, Phone
FROM		CUSTOMER
WHERE		CustomerID IN 
			(SELECT CustomerID
			 FROM SALE
			 WHERE SaleID IN (1, 2, 3));

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

SELECT		LastName, FirstName, Phone
FROM		CUSTOMER AS C JOIN SALE AS S
	ON		C.CustomerID = S.CustomerID
WHERE		SaleID IN (1, 2, 3);

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

SELECT		LastName, FirstName, Phone
FROM		CUSTOMER
WHERE		CustomerID IN 
			(SELECT CustomerID
			 FROM SALE
			 WHERE SubTotal > 500);

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

	
/*       For each CUSTOMER at most once:                                         */

SELECT  	DISTINCT LastName, FirstName, Phone
FROM		CUSTOMER AS C JOIN SALE AS S
	ON		C.CustomerID = S.CustomerID
WHERE		SubTotal > 500;

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

SELECT		LastName, FirstName, Phone
FROM		CUSTOMER
WHERE		CustomerID IN 
			(SELECT 	CustomerID
			 FROM 		SALE
			 WHERE		SaleID IN
						(SELECT		SaleID
						 FROM		SALE_ITEM
						 WHERE		ItemPrice  > 500));

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


/*       For each CUSTOMER only once:                                         */

SELECT		DISTINCT LastName, FirstName, Phone
FROM		CUSTOMER AS C JOIN SALE AS S
		ON 	C.CustomerID = S.CustomerID
			JOIN SALE_ITEM as SI
		ON 	S.SaleID = SI.SaleID
WHERE		ItemPrice > 500;


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

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 		VendorID IN
												(SELECT VendorID
												 FROM VENDOR
												 WHERE CompanyName LIKE 'L%'))));

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

/*       For each CUSTOMER only once:                                           */

SELECT		DISTINCT LastName, FirstName, C.Phone
FROM		CUSTOMER AS C JOIN SALE AS S
		ON	C.CustomerID = S.CustomerID
			JOIN SALE_ITEM AS SI
		ON	S.SaleID = SI.SaleID
			JOIN ITEM AS I
		ON	SI.ItemID = I.ItemID
			JOIN VENDOR AS V
		ON	I.VendorID = V.VendorID
WHERE		CompanyName LIKE 'L%';

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

SELECT 		CUSTOMER.CustomerID, LastName, FirstName, Phone,
			SUM(SubTotal)AS SumOfSubTotal
FROM		CUSTOMER JOIN SALE
		ON	CUSTOMER.CustomerID = SALE.CustomerID
GROUP BY 	CUSTOMER.CustomerID, LastName, FirstName, Phone
ORDER BY 	CUSTOMER.CustomerID DESC;

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

SELECT * FROM VENDOR;

UPDATE		VENDOR
	SET		CompanyName = 'Linens and Other Stuff'
	WHERE	CompanyName = 'Linens and Things';

SELECT * FROM VENDOR;

/*       To undo Question R:                                                    */

UPDATE		VENDOR
	SET		CompanyName = 'Linens and Things'
	WHERE	CompanyName = 'Linens and Other Stuff';
	
SELECT * FROM VENDOR;

/*****   Question S   ***********************************************************/

/*       To undo Question R:                                                    */

UPDATE		VENDOR
	SET		CompanyName = 'Linens and Things'
	WHERE	CompanyName = 'Linens and Other Stuff';

SELECT * FROM VENDOR;

UPDATE		VENDOR
	SET		CompanyName = 'Temp'
	WHERE	CompanyName = 'Linens and Things';

UPDATE		VENDOR
	SET		CompanyName = 'Linens and Things'
	WHERE	CompanyName = 'Lamps and Lighting';

UPDATE		VENDOR
	SET		CompanyName = 'Lamps and Lighting'
	WHERE	CompanyName = 'Temp';

SELECT * FROM VENDOR;

/*       To undo Question S:                                                    */

UPDATE		VENDOR
	SET		CompanyName = 'Temp'
	WHERE	CompanyName = 'Lamps and Lighting';

UPDATE		VENDOR
	SET		CompanyName = 'Lamps and Lighting'
	WHERE	CompanyName = 'Linens and Things';

UPDATE		VENDOR
	SET		CompanyName = 'Linens and Things'
	WHERE	CompanyName = 'Temp';

SELECT * FROM VENDOR;


/*****   Question T   ***********************************************************/

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

/* DELETE FROM SALE; */
/* This CASCADES through SALE_ITEM, so DELETE FROM SALE_ITEM; is NOT needed */
/* DELETE FROM ITEM; */
/* DELETE FROM VENDOR; */
/* DELETE FROM EMPLOYEE; */
/* DELETE FROM CUSTOMER; */

/*****   Question U   ***********************************************************/

CREATE TABLE PHONE_NUMBER (
		VendorID		INT		NOT NULL,
		Phone			CHAR(12)	NOT NULL,
		PhoneType		CHAR(25)	NULL,
		CONSTRAINT		PHONE_NUMBER_PK PRIMARY KEY (VendorID, Phone),
		CONSTRAINT		PHONE_NUMBER_VENDOR_FK	FOREIGN KEY (VendorID)
						REFERENCES VENDOR (VendorID)
	);

/*  To insert phones one at a time:  */
INSERT INTO PHONE_NUMBER VALUES (1, '206-325-6755', NULL);
INSERT INTO PHONE_NUMBER VALUES (1, '206-329-9675', 'Fax');
INSERT INTO PHONE_NUMBER VALUES (2, '206-325-7866', NULL);
INSERT INTO PHONE_NUMBER VALUES (2, '206-329-9786', 'Fax');
INSERT INTO PHONE_NUMBER VALUES (3, '206-325-8977', NULL);
INSERT INTO PHONE_NUMBER VALUES (3, '206-329-9897', 'Fax');
INSERT INTO PHONE_NUMBER VALUES (4, '425-746-5433', NULL);
INSERT INTO PHONE_NUMBER VALUES (5, '425-746-4322', NULL);
INSERT INTO PHONE_NUMBER VALUES (6, '206-325-9088', NULL);
INSERT INTO PHONE_NUMBER VALUES (6, '206-329-9908', 'Fax');
INSERT INTO PHONE_NUMBER VALUES (7, '425-635-7566', NULL);
INSERT INTO PHONE_NUMBER VALUES (8, '425-635-9788', NULL);
INSERT INTO PHONE_NUMBER VALUES (8, '425-639-9978', 'Fax');
INSERT INTO PHONE_NUMBER VALUES (9, '415-422-2121', NULL);
INSERT INTO PHONE_NUMBER VALUES (9, '415-423-5212', 'Fax');
INSERT INTO PHONE_NUMBER VALUES (10, '415-422-3232', NULL);
INSERT INTO PHONE_NUMBER VALUES (10, '415-429-9323', 'Fax');

/*  To insert phones with fewer INSERT statements:  */
INSERT INTO PHONE_NUMBER (VendorID, Phone, PhoneType)
			SELECT VendorID, Phone, NULL
			FROM VENDOR;

INSERT INTO PHONE_NUMBER (VendorID, Phone, PhoneType)
			SELECT VendorID, Fax, 'Fax'
			FROM VENDOR
			WHERE Fax IS NOT NULL;

ALTER TABLE VENDOR
	DROP COLUMN Phone;

ALTER TABLE VENDOR
	DROP COLUMN Fax;

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