-- DATABASE MANAGEMENT SYSTEMS 2018/19
-- LECTURE 8 QUERIES
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


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

-- A LEFT JOIN is used because the question asks to show ALL properties owned by Kevin Pinegar 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 = 'Kevin' AND LastName = 'Pinegar'



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


-- 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 = 'Kevin' AND LastName = 'Pinegar' 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 = 'Kevin' AND LastName = 'Pinegar' AND LeaseNo IS NULL