-- DATABASE MANAGEMENT SYSTEMS 2020
-- LAB EXERCISE week 7b
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- VIEWS
-- ~~~~~~~~~~~~~~~~~~~~
-- NOTE: You do not need the GO statements, but SQL Server will show red squiggly lines if they are not added as shown



-- HORIZONTAL AND VERTICAL VIEWS
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- Using appropriate FILTERS, create two views, one that provides a list of Private property Owners and another that provides a list of Business property Owners.

CREATE VIEW PrivateOwner
AS SELECT OwnerNo, FirstName, LastName, StreetAddress, City, Postcode, PhoneNo, Email
FROM Owner
WHERE BusinessName IS NULL;
GO

CREATE VIEW BusinessOwner
AS SELECT OwnerNo, FirstName, LastName, BusinessName, StreetAddress, City, Postcode, PhoneNo, Email
FROM Owner
WHERE BusinessName IS NOT NULL;
GO


-- Write two separate queries to test the two views created in the last step.
SELECT * FROM PrivateOwner;
SELECT * FROM BusinessOwner;

GO


-- VIEWS CREATED USING SELECT * FROM ...
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- In class you were asked what you thought will happen if:
--  * You initially created a view using a SELECT * FROM Table query and later modified the table by adding new columnes
--  * Later executed a SELECT * FROM View after the updates to the table. Would the results include the new column(s)? 

-- The answer to this question demonstrates one of the disadvantages of views: Structure Restrictions.
-- Run the following set of queries and then provide your answer.

-- Step 1: Create a view using SELECT *
CREATE VIEW StaffTest AS SELECT * FROM Staff;
GO

-- step 2: Test the view
SELECT * FROM StaffTest;

-- Step 3: Now alter the the base table
ALTER TABLE Staff ADD NewTestColumn VARCHAR(10);

-- Step 4: Test the view again. Is the new column included?
SELECT * FROM StaffTest;

-- The queries above demonstrate the Structure restriction disadvantage of views
-- If the defining query for a view uses SELECT * FROM ... (ie select all columns) - the * refers to all columns of the base table AT THE TIME the view was created.
-- If the base table is changed and a new column is added, this column will not show up in the view unless it is dropped and recreated.

-- Step 6: Delete the view as it is not needed.
DROP VIEW StaffTest;

-- Step 5: Delete the new column added as it is not needed
ALTER TABLE Staff DROP COLUMN NewTestColumn;


-- JOINED VIEWS
-- ~~~~~~~~~~~~~~~~~~

-- Run appropriate queries to promote Melanie Scott at branch B0001 to a manager and branch manager.
/*	The steps you will need are:
	1. First you have to update the current branch manager at B0001 because he has resigned (set the end date to today)
	2. Update the current branch manager's leaving date to today too
	3. Then update Jacob's staff position to manager and 
	4. Insert a new record into the branch manager table for Melanie, with a bonus of your choice

*/

/*
	The following two SELECT queries help you to identify:
		The current branch manager at B0001 is Adrian Brooks, S29004
		Melanie Scott's staff number is S29011
*/

SELECT s.*, bm.*
FROM Staff s
JOIN BranchManager bm ON s.StaffNo = bm.StaffNo
WHERE s.BranchNo = 'B0001'

SELECT s.*, bm.*
FROM Staff s
LEFT JOIN BranchManager bm ON s.StaffNo = bm.StaffNo
WHERE s.BranchNo = 'B0001'

-- Solution

UPDATE BranchManager SET EndDate = GETDATE() WHERE StaffNo = 'S29004' AND BranchNo = 'B0001'
UPDATE Staff SET LeavingDate = GETDATE() WHERE StaffNo = 'S29004'
UPDATE Staff SET Position = 'Manager', Salary = 30000 WHERE StaffNo = 'S29011'
INSERT INTO BranchManager (BranchNo, StaffNo, StartDate, Bonus) VALUES ('B0001', 'S29011', GETDATE(), 1500);

GO
-- Now, create a view of ALL CURRENT Branch Managers, including relevant information from the Staff, Branch and Branch Manager table. Use an appropriate filter to only show CURRENT Branch Managers.
CREATE VIEW BranchManagers AS
SELECT b.BranchNo, City, PhoneNo, FirstName, LastName, Email, bm.StartDate
FROM Branch b
JOIN Staff s ON b.BranchNo = s.BranchNo
JOIN BranchManager bm ON s.StaffNo = bm.StaffNo
WHERE bm.EndDate IS NULL

GO
-- Write a query to test the view created in the last step - check to see that the old branch manager is not included in the results.

SELECT * FROM BranchManagers;
SELECT * FROM BranchManager;

GO
-- Taking into consideration confidentiality of data, create a view that provides a list of property details and their leases that can be used for invoicing property owners monthly. 
-- There is no need to invoice an owner if the lease has expired. The list provided by the view should:
-- 	contain relevant information from the Owner, Property and Lease tables.
-- 	make use of an appropriate filter.
-- 	Assume property owner's are charged 15% of the monthly rent.


-- You can use CAST(l.MonthlyRent * 0.15 AS decimal(8,2)) to tidy up the invoice amount

CREATE View OwnerLeaseInvoice AS
SELECT o.OwnerNo, o.FirstName, o.LastName, o.StreetAddress InvStreetAddress, o.City InvCity, o.Postcode InvPostcode, p.PropertyNo, p.StreetAddress, p.Postcode, l.LeaseNo, l.MonthlyRent * 0.15 AS InvoiceAmount
FROM Owner o
JOIN Property p ON o.OwnerNo  = p.OwnerNo
JOIN Lease l ON p.PropertyNo = l.PropertyNo
WHERE RentStart <= GETDATE() AND RentFinish > GETDATE()

GO
-- Write a query to test the view created in the last step.

SELECT * FROM OwnerLeaseInvoice;