-- DATABASE MANAGEMENT SYSTEMS 2018/19
-- 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 Isaac Tomlinson to a Supervisior 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



-- Melanie Carr 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 Jason Trevana and Jaxon Harding 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 Kevin Pinegar 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


-- Kevin Pinegar 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 (L18025) for property P180033 being rented for £380.00.00 per month by Andrea Toulson (CR18003) 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')




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