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


-- Josiah Tromburg was not happy that he was not promoted with his colleague (you promoted him in question 2 above) and has decided to leave. 
-- Update the database to record his leaving date (today) and also reassign any properties that he is managing to his 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 Avery	Edwards 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 Ella May and any leases for these properties.
-- The query result should include: OwnerNo, FirstName, LastName, PropertyNo, StreetAddress, Postcode and Rent, LeaseNo, RentStart and RentFinish.



-- Ella May has asked for the rent for her properties that are have NEVER been rented to be decreased by 10%.
-- What will be the update query for this?



-- Inserts
-- ~~~~~~~~~~~~~~~~~~~~
-- Create a new lease record (L16025) for property P160033 being rented for £380.00.00 per month by Carlos Tyson (CR16003) 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