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

-- Updates
-- ~~~~~~~~~~~~~~~~~~~~
-- Hannah Williamsn has finally made up his mind what type of property he is looking for - a house costing no more than £850 to rent.
-- RUN an UPDATE query to reflect this.



-- Promote Blake Harper to a Supervisor and increase his salary by 15%;



-- Property rental prices are continuing to increase, and particularly in London. 
-- Run an UPDATE query to increase all rental prices in London (assume London properties are registered with a London branch) by 3%.
-- Hint: first of all write a multi-table query using either a join or subquery to select all properties registered with a London branch
-- to help you work out the correct join statement



-- Faith Sanger was not happy that she was not promoted with her colleague (you promoted him in question 2 above) and has decided to leave. 
-- Update the database to record her leaving date (today) and also reassign any properties that she is managing to her colleague in the same office.
-- NOTE: You are likely to need more than one query to complete this - you can use a SELECT statement to find the information for the second part of the query or use a Subquery.



-- Update the property viewing table to reflect the comments of your choice about the viewings by Jackson Maddern and Gianna Tyson in October. 
-- The comments should be different for each viewing. Note: You are likely to need a query to find the relevant viewings first.


-- Write a query to show all the properties owned by Joshua Frankland and any leases for these properties.
-- The query result should include: OwnerNo, FirstName, LastName, PropertyNo, StreetAddress, Postcode and Rent, LeaseNo, RentStart and RentFinish.



-- NOTE: This is the exercise queston from today's lecture slides
-- If you are finding the following query difficult to solve, skip to the next one and return to this later


-- Joshua Frankland has asked for the rent for his properties that have NEVER been rented to be decreased by 10%.
-- What will be the update query for this?



-- Inserts
-- ~~~~~~~~~~~~~~~~~~~~
-- Create a new lease record (L19025) for property P190033 being rented for £380.00.00 per month by Max Burt (CR19003) from today's date to end in 6 months time.
-- Use the DATEADD function (https://docs.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql) to work out the end date e.g. DATEADD(month, 3, '01-Oct-2019')




-- ADDITIONAL EXERCISES
-- ~~~~~~~~~~~~~~~~~~~~
-- As this lecture is very short, please start working on your assignment or finish the queries from the previous lectures