-- DATABASE MANAGEMENT SYSTEMS 2020
-- LAB EXERCISE week 7
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

UPDATE Client
SET PropertyTypePref = 'House',
MaxRent = 850.00
WHERE FirstName = 'Hannah' AND LastName = 'Williamsn';

-- Test changes using
SELECT * FROM Client
WHERE FirstName = 'Hannah' AND LastName = 'Williamsn';


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

UPDATE Staff
SET salary = salary * 1.15,
Position = 'Supervisor'
WHERE FirstName = 'Blake' AND LastName = 'Harper';


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


-- Selecting all properties registered with a London Branch using a JOIN
SELECT p.*
FROM Branch b
JOIN Property p ON b.BranchNo = p.BranchNo
WHERE b.City = 'London'

-- Selecting all properties registered with a London Branch using a subquery
SELECT * FROM Property WHERE BranchNo IN (SELECT BranchNo FROM Branch WHERE City = 'London')

-- UPDATE using a JOIN
UPDATE Property
SET Rent = Rent * 1.03
FROM Branch b
JOIN Property p ON b.BranchNo = p.BranchNo
WHERE b.City = 'London' 

-- ALTERNATIVE UPDATE using a Subquery
UPDATE Property
SET Rent = Rent * 1.03
WHERE BranchNo IN (SELECT BranchNo FROM Branch WHERE City = 'London')


-- 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 leaving date
UPDATE Staff
SET LeavingDate = GETDATE()
WHERE FirstName = 'Faith' AND LastName = 'Sanger';

-- Update the property table to reflect the change using Subqueries
UPDATE Property
SET managedBy = (SELECT StaffNo FROM Staff WHERE FirstName = 'Blake' AND LastName = 'Harper')
WHERE managedBy = (SELECT StaffNo FROM Staff WHERE FirstName = 'Faith' AND LastName = 'Sanger');


-- Alternatively, find Faith's (S28012) colleague - Blake Harper (S29001)
SELECT * FROM Staff WHERE branchNo = (SELECT branchNo FROM Staff WHERE firstName = 'Faith' AND lastName = 'Sanger')

-- Update the property table to reflect the change using the Staff numbers
UPDATE Property
SET managedBy = 'S29001'
WHERE managedBy = 'S28012';



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

-- First find the relevant viewing by Jackson Maddern and Gianna Tyson in October, so you know what to update.
-- Jackson Maddern (CR19022) viewed property (P190018) on 2019-10-25 16:00:00 and Property (P190048) on 2019-10-27 17:00:00
-- Gianna Tyson (CR19011) viewed Property (P190050) on 2019-10-31 14:00:00


-- NOTE if a property is viewed more than once, you will need to add AND ViewingDateTime = ... to your query as shown in the first example
SELECT v.*, FirstName, LastName
FROM Client c
JOIN Viewing v ON c.ClientNo = v.ClientNo
WHERE ViewingDateTime BETWEEN '01-Oct-2019' AND '31-Oct-2019' AND (FirstName = 'Jackson' AND LastName = 'Maddern' OR FirstName = 'Gianna' AND LastName = 'Tyson')
ORDER BY ViewingDateTime

UPDATE Viewing
SET Comment = 'Ok, not bad at all'
WHERE ClientNo = 'CR19022' AND PropertyNo = 'P190018' AND ViewingDateTime = '2019-10-25 16:00:00';


UPDATE Viewing
SET Comment = 'Did not like'
WHERE ClientNo = 'CR19022' AND PropertyNo = 'P190048';


UPDATE Viewing
SET Comment = 'Hmmm, I think I need another look'
WHERE ClientNo = 'CR19011' AND PropertyNo = 'P190050';






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

-- A LEFT JOIN is used because the question asks to show ALL properties owned by Joshua Frankland and ANY Leases
-- Using a left join will include all results from the table on the left side of the join (Property) even if they have a matching lease or not

SELECT o.OwnerNo, FirstName, LastName, p.PropertyNo, p.StreetAddress, p.Postcode, Rent, LeaseNo, RentStart, RentFinish, MonthlyRent
FROM [Owner] o
INNER JOIN Property p ON o.OwnerNo = p.OwnerNo
Left JOIN Lease l ON p.PropertyNo = l.PropertyNo
WHERE FirstName = 'Joshua' AND LastName = 'Frankland'


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


-- To make the previous query show properties that have never been rented you need to add: 
-- AND LeaseNo IS NULL


SELECT o.OwnerNo, FirstName, LastName, p.PropertyNo, p.StreetAddress, p.Postcode, Rent, LeaseNo, RentStart, RentFinish, MonthlyRent
FROM [Owner] o
INNER JOIN Property p ON o.OwnerNo = p.OwnerNo
Left JOIN Lease l ON p.PropertyNo = l.PropertyNo
WHERE FirstName = 'Joshua' AND LastName = 'Frankland' AND LeaseNo IS NULL


-- The update will therefore be:
UPDATE Property
SET Rent = Rent * 0.90
FROM [Owner] o
INNER JOIN Property p ON o.OwnerNo = p.OwnerNo
Left JOIN Lease l ON p.PropertyNo = l.PropertyNo
WHERE FirstName = 'Joshua' AND LastName = 'Frankland' AND LeaseNo IS NULL


-- 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')

-- The insert statement uses a formula to calculate the deposit paid: Rent (380) * 1.5
INSERT INTO Lease(LeaseNo, ClientNo, PropertyNo, RentStart, RentFinish, MonthlyRent, PaymentMethod, DepositPaid)
VALUES ('L19025', 'CR19003', 'P190033', GETDATE(), DATEADD(month, 6, GetDate()), 380.00, 'Direct Debit', 380 * 1.5);


-- Test the results
SELECT * FROM Lease WHERE LeaseNo = 'L19025'

-- If you wish to delete the added lease:
DELETE FROM Lease WHERE LeaseNo = 'L19025'



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