66 lines
1.8 KiB
SQL
66 lines
1.8 KiB
SQL
/* *** 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; |