-- DATABASE MANAGEMENT SYSTEMS 2017/18
-- LAB EXERCISE 8
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- Updates
-- ~~~~~~~~~~~~~~~~~~~~
-- Jaden Maddern 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 Brianna Cooley to a Supervisior and increase her 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



-- Bella Johnson 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 Logan	Huckle and Bryson Hollands in November. 
-- The comments should be different for each viewing. Note: You are likely to need a query to find the relevant viewing first.


-- NOTE: We have solved the following two queries in class together
-- See the solution on the last two slides for today's lecture
-- ~~~~~~~~~~~~~~~~~~~~
-- Write a query to show all the properties owned by Christopher Morse and any leases for these properties.
-- The query result should include: OwnerNo, FirstName, LastName, PropertyNo, StreetAddress, Postcode and Rent, LeaseNo, RentStart and RentFinish.



-- Christopher Morse 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 (L17025) for property P170033 being rented for £380.00.00 per month by Liam Jermyn (CR17003) from today's date to end in 6 months time.
-- Use the DATEADD function (https://msdn.microsoft.com/en-gb/library/ms186819.aspx) to work out the end date e.g. DATEADD(month, 3, '01-Oct-2016')




-- ADDITIONAL Exercises
-- As this lecture is very short, I have provided some additional Exam Revision Questions
-- ~~~~~~~~~~~~~~~~~~~~

/*
	Code IT and C Case Study
	Staff			(staffNo, name, dept, skillCode)
	Skill			(skillCode, description, chargeOutRate)
	Project			(projectNo, startDate, endDate, budget, projectManagerStaffNo)
	Booking			(staffNo, projectNo, dateWorkedOn, timeWorkedOn)

	where 
	Staff	contains staff details and staffNo is the key
	Skill	contains descriptions of skill codes (e.g. Programmer, Analyst, Manager, etc.) and the charge out rate per hour for that skill; the key is skillCode
	Project	contains project details and projectNo is the key
	Booking	contains details of the date and the number of hours that a member of staff worked on a project and the key is staffNo/projectNo

	NOTE: DELETE the created tables from your DreamHome database when you are done testing your queries

*/


-- i. Create the Skill Table (with the primary key)


-- ii. Create the Staff table (with the primary key and foreign key) and any constraints.


-- iii. Create the Project table without the primary key and foreign key(s), but adding in all columns and column constraints including at least one check constraint


-- iv.	Alter the Project table by adding the primary key.


-- v. Add the foreign key(s) to the Project table