/* *** CS7-MI.M *** */
CREATE VIEW EmployeeSupervisorView AS
	SELECT
		E1.LastName AS EmployeeLastName,
		E1.FirstName AS EmployeeFirstName,
		E1.Position AS EmployeePosition,
		E2.LastName AS SupervisorLastName,
		E2.firstName AS SupervisorFirstName
			FROM EMPLOYEE E1
				INNER JOIN EMPLOYEE E2 on E1.Supervisor = E2.EmployeeId
;

SELECT * FROM EmployeeSupervisorView;

/* *** CS7 - MI.N *** */
CREATE VIEW PurchaseSummaryView AS
	SELECT PurchaseItemID, PurchaseDate, ItemDescription, PriceUSD
		FROM PURCHASE_ITEM;

SELECT * FROM PurchaseSummaryView;


/* *** CS7 - MI.O *** */
CREATE FUNCTION StoreContactAndPhone(@StoreContact CHAR(25), @ContactPhone VARCHAR(32))
	RETURNS VARCHAR(59)
		AS
			BEGIN
				RETURN (SELECT RTRIM(@StoreContact) + ':' + SPACE(1) + @ContactPhone)
			END
			
/* *** CS7 - MI.P *** */	
CREATE VIEW StorePurchaseHistoryView AS
	SELECT STORE.StoreName, STORE.Phone, STORE.Contact, I.PurchaseItemID, I.PurchaseDate, I.ItemDescription, I.PriceUSD
		FROM STORE, PURCHASE_ITEM I
			WHERE STORE.StoreID = I.StoreID
;

SELECT * FROM StorePurchaseHistoryView;

/* *** CS7 - MI.Q *** */
CREATE VIEW StoreContactPurchaseHistoryView AS
	SELECT STORE.StoreName, dbo.StoreContactAndPhone(STORE.Contact, STORE.Phone) AS ContactAndPhone, I.PurchaseItemID, I.PurchaseDate, I.ItemDescription, I.PriceUSD
		FROM STORE, PURCHASE_ITEM I
			WHERE STORE.StoreID = I.StoreID
;

SELECT * FROM StoreContactPurchaseHistoryView;

/* *** CS7 - MI.R *** */
CREATE VIEW StoreHistoryView AS
	SELECT StoreName, SUM(CAST(PriceUSD AS INT)) AS StorePurchaseHistory
		FROM dbo.StorePurchaseHistoryView
			GROUP BY StoreName
;


SELECT * FROM StoreHistoryView;

/* *** CS7 - MI.S *** */
CREATE VIEW MajorSources AS
	SELECT StoreName
		FROM StoreHistoryView
			WHERE StorePurchaseHistory > 100000
;

SELECT * FROM MajorSources;