-- DATABASE MANAGEMENT SYSTEMS 2016/17
-- 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

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

-- Try the following set of statements one by one to test 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 table will not show up in the view unless it is dropped and recreated.
-- 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;

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



-- Run the following queries to promote Zachary Harding to a manager and branch manager
UPDATE BranchManager SET EndDate = GETDATE() WHERE StaffNo = 'S26004' AND BranchNo = 'B0001'
UPDATE Staff SET Position = 'Manager', Salary = 30000 WHERE StaffNo = 'S25011'
INSERT INTO BranchManager (BranchNo, StaffNo, StartDate, Bonus) VALUES ('B0001', 'S25011', 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.



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



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.



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