-- DATABASE MANAGEMENT SYSTEMS 2019/20
-- LAB EXERCISE 9
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- 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.



GO
-- Write two separate queries to test the two views created in the last step.


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;


-- DISCUSSION
-- 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 Jacob, with a bonus of your choice

*/


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 (do not include people who are no longer branch managers).



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.



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.



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