/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg, and Yoder 										*/
/*	Database Concepts (8th Edition) Chapter 03 	       							*/
/*																				*/
/*	Heather Sweeny Designs database	- Chapter 3 Data							*/
/*																				*/
/*	These are the Microsoft SQL Server 2014/2016 SQL code solutions				*/
/*																				*/
/********************************************************************************/

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

SELECT * FROM CONTACT;

SELECT * FROM CUSTOMER;

SELECT * FROM INVOICE;

SELECT * FROM LINE_ITEM;

SELECT * FROM PRODUCT;

SELECT * FROM SEMINAR;

SELECT * FROM SEMINAR_CUSTOMER;

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

SELECT		LastName, FirstName, Phone
FROM		CUSTOMER
WHERE		City = 'Dallas';

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

SELECT		LastName, FirstName, Phone
FROM		CUSTOMER
WHERE		City = 'Dallas'
	AND		LastName LIKE 'T%';

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

-- Two table solution -  starting with LINE_ITEM

SELECT	InvoiceNumber
FROM	LINE_ITEM
WHERE	ProductNumber in
		(SELECT ProductNumber
		 FROM	PRODUCT
		 WHERE	PRODUCT.ProductDescription=
				'Heather Sweeney Seminar Live in Dallas on 25-OCT-15');

-- Three table solution - starting with INVOICE

SELECT		InvoiceNumber
FROM		INVOICE
WHERE		InvoiceNumber IN
			(SELECT InvoiceNumber
			 FROM	LINE_ITEM
			 WHERE	ProductNumber in
					(SELECT ProductNumber
					 FROM	PRODUCT
					 WHERE	PRODUCT.ProductDescription=
							'Heather Sweeney Seminar Live in Dallas on 25-OCT-15'));

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

-- Two table solution 

SELECT		LI.InvoiceNumber
FROM		LINE_ITEM AS LI JOIN PRODUCT AS P
	ON		LI.ProductNumber = P.ProductNumber
WHERE		P.ProductDescription = 'Heather Sweeney Seminar Live in Dallas on 25-OCT-15';

-- Three table solution 

SELECT		I.InvoiceNumber
FROM		INVOICE AS I JOIN LINE_ITEM AS LI
	ON		I.InvoiceNumber = LI.InvoiceNumber
			JOIN PRODUCT AS P
	ON		LI.ProductNumber = P.ProductNumber
WHERE		P.ProductDescription = 'Heather Sweeney Seminar Live in Dallas on 25-OCT-15';

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

SELECT		DISTINCT FirstName, LastName, Phone
FROM		CUSTOMER AS C JOIN SEMINAR_CUSTOMER AS SC
	ON		C.CustomerID = SC.CustomerID
			JOIN SEMINAR AS S
	ON		SC.SeminarID = S.SeminarID
WHERE		S.SeminarTitle = 'Kitchen on a Big D Budget';

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

SELECT		DISTINCT FirstName, LastName, Phone,
			P.ProductNumber, P.ProductDescription
FROM		CUSTOMER AS C JOIN INVOICE AS I
	ON		C.CustomerID = I.CustomerID
			JOIN	LINE_ITEM AS LI
	ON 		I.InvoiceNumber = LI.InvoiceNumber
			JOIN	PRODUCT AS P
	ON		LI.ProductNumber = P.ProductNumber
WHERE		P.ProductNumber LIKE 'VK%'
ORDER BY	LastName DESC, FirstName DESC, ProductNumber DESC;

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

SELECT		S.SeminarID, S.SeminarDate, S.Location, S.SeminarTitle,
			SC.CustomerID, C.LastName, C.FirstName
FROM		CUSTOMER as C JOIN SEMINAR_CUSTOMER as SC
	ON		C.CustomerID = SC.CustomerID
			RIGHT OUTER JOIN SEMINAR as S
	ON		SC.SeminarID = S.SeminarID;

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

SELECT		C.CustomerID, C.LastName, C.FirstName, I.InvoiceNumber,
			LI.ProductNumber, P.ProductType, P.ProductDescription
FROM		CUSTOMER as C JOIN INVOICE as I
	ON		C.CustomerID = I.CustomerID
			JOIN LINE_ITEM as LI
	ON		I.InvoiceNumber = LI.InvoiceNumber
			RIGHT OUTER JOIN PRODUCT as P
	ON		LI.ProductNumber = P.ProductNumber;


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

SELECT		SUM (SubTotal) AS SumOfSubTotal
FROM		INVOICE;

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

SELECT		AVG (SubTotal) AS AverageOfSubTotal
FROM		INVOICE;

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

SELECT		SUM (SubTotal) AS SumOfSubTotal,
			AVG (SubTotal) AS AverageOfSubTotal
FROM		INVOICE;

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

SELECT		*
FROM		PRODUCT
WHERE		ProductNumber = 'VK004';

UPDATE		PRODUCT
	SET		UnitPrice = 34.95
	WHERE	ProductNumber = 'VK004';

SELECT		*
FROM		PRODUCT
WHERE		ProductNumber = 'VK004';

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

UPDATE		PRODUCT
	SET		UnitPrice = 24.95
	WHERE	ProductNumber = 'VK004';

SELECT		*
FROM		PRODUCT
WHERE		ProductNumber = 'VK004';

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

/* ANSWER COMMENTED OUT BECAUSE YOU PROBABLY DON'T WANT
	TO RUN IT !!! */

/*
DELETE FROM INVOICE;
DELETE FROM PRODUCT;
DELETE FROM CONTACT;
DELETE FROM SEMINAR_CUSTOMER;
DELETE FROM SEMINAR;
DELETE FROM CUSTOMER;
*/

