-- DATABASE MANAGEMENT SYSTEMS 2020
-- LAB EXERCISE week 6a
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- Multi-Table Queries
-- ~~~~~~~~~~~~~~~~~~~~
-- Produce a list of property owners and the properties they own. 
-- The list should show the property owner's client number, name and phone number as well as the property number and address of the properties they own. 
-- Sort the list by city and owner number.

SELECT o.OwnerNo, FirstName, LastName, PhoneNo, PropertyNo, p.StreetAddress, p.City, p.Postcode
FROM [Owner] o
INNER JOIN Property p ON o.OwnerNo = p.OwnerNo
ORDER BY city, o.OwnerNo;

-- If I Change the query above to a LEFT JOIN, it will include ALL owners whether or not they have any properties registered with Dream Home

SELECT o.OwnerNo, FirstName, LastName, PhoneNo, PropertyNo, p.StreetAddress, p.City, p.Postcode
FROM [Owner] o
LEFT JOIN Property p ON o.OwnerNo = p.OwnerNo
ORDER BY city, o.OwnerNo;


-- Produce a list of all staff supervising other staff members. 
-- The list should show the branch number and names of the supervising staff, and the names and positions of the staff they are supervising.
-- NOTE: Use appropriate aliases to distinguish the column names (staff no, first name & last name) in the query results
-- Sort the list by branch number

-- The supervisor is the parent relationship to the staff

SELECT sup.BranchNo, sup.StaffNo SupStaffNo, sup.FirstName SupFirstName, sup.LastName SupLastName, s.StaffNo, s.FirstName, s.LastName, s.Position
FROM Staff sup
INNER JOIN Staff s ON sup.StaffNo = s.SupervisorStaffNo
ORDER BY sup.BranchNo;

-- The following query helps you to understand the supervisor-staff relationship for the above query (look at the SupervisorStaffNo column for each member of staff)
SELECT * FROM Staff ORDER BY BranchNo



-- For each branch, produce a list of branch numbers, the phone number and city the branch is located in, including the name of the branch manager and the manager bonus amount.

SELECT b.BranchNo, b.PhoneNo, b.City, s.StaffNo, s.FirstName, s.LastName, bm.Bonus
FROM Branch b
INNER JOIN BranchManager bm ON b.BranchNo = bm.BranchNo
INNER JOIN Staff s ON bm.StaffNo = s.StaffNo;


-- Produce a list of all viewings booked so far this month, and include the property number and address details, and the client's name and contact details in the results. 
-- Sort the results by the most recently viewed properties.

SELECT ViewingDateTime, p.PropertyNo, p.StreetAddress, p.City, p.Postcode, c.FirstName, c.LastName, PhoneNo, Email
FROM Viewing v
INNER JOIN Property p ON v.PropertyNo = p.PropertyNo
INNER JOIN Client c ON v.ClientNo = c.ClientNo
WHERE ViewingDateTime BETWEEN '01-Nov-2019' AND '30-Nov-2019'
ORDER BY ViewingDateTime DESC



-- List ALL Owners (OwnerNo, Name, Phone No and Email) and their Properties (Property No, Address), including owners who have not yet registered any properties with Dream Home.
-- Apply appropriate sorting
-- The LEFT JOIN ensures all Owners are included in the results whether or not they have any properties

SELECT o.OwnerNo, FirstName, LastName, PhoneNo, Email, PropertyNo, p.StreetAddress, p.Postcode, p.City
FROM Owner o
LEFT JOIN Property p ON o.OwnerNo = p.OwnerNo
ORDER BY o.OwnerNo



-- Produce a list of ALL branch codes and how much each branch has earned in rent in descending order of rent earnings.
-- Using a RIGHT JOIN for Branch ensures all branches are included in the results regardless of whether or not they had any leases
/*
	HINTs: 
	The property table contains the branch number - this is your first table
	What table stores details of properties rented?
	How would you calculate the amount of rent paid for the rental period
	Write the query for the above first
	What type of JOIN will you need to include ALL branches?

	How would you calculate the total sum of the rent for each branch?
	Extend your query to calculate this

	Think aggregates again in terms of number of properties
	
	If you add a column alias to the calculated column, you will be able to sort by this column
*/

select * FROM Branch

SELECT b.BranchNo, COUNT(p.PropertyNo) numProperties, SUM(monthlyRent * duration) totalRent
FROM Lease l
INNER JOIN Property p ON l.PropertyNo = p.propertyNo
RIGHT JOIN Branch b ON p.BranchNo = b.BranchNo
GROUP BY b.branchNo
ORDER BY totalRent DESC;

-- Extend the query used in the last question to include the branch city location and street address.
-- Remember to add b.city  and b.StreetAddress in the GROUP BY Clause as they are not used with an aggregate function.

SELECT b.BranchNo, b.StreetAddress, b.City, COUNT(p.PropertyNo) numProperties, SUM(monthlyRent * duration) totalRent
FROM Lease l
INNER JOIN Property p ON l.PropertyNo = P.propertyNo
RIGHT JOIN Branch b ON b.BranchNo = p.BranchNo
GROUP BY b.branchNo, b.City, b.StreetAddress
ORDER BY totalRent DESC;




-- Set Operations
-- ~~~~~~~~~~~~~~~~~~~~
-- DreamHome has become slightly corrupt :) and has decided to sell client names to a marketing firm. 
-- Construct a list of all client names, that is, Clients who are Property Owners and Clients who are Prospective Renters.
-- The list should include the contact phone numbers and email addresses of the clients

SELECT FirstName, LastName, Email, PhoneNo FROM [Owner]
UNION
SELECT FirstName, LastName, Email, PhoneNo FROM Client;



-- A Final Multi-Table Query
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Provide a list of leases that are still running, i.e. lease has not ended yet 
-- Include the rent start and finish, rent paid, the client name and contact phone no and email address and the address and tyoe of property that was rented.
SELECT l.LeaseNo, c.ClientNo, FirstName, LastName, PhoneNo, Email, RentStart, RentFinish, MonthlyRent, p.PropertyNo, p.Type, StreetAddress, Postcode
FROM Lease l
JOIN Property p ON l.PropertyNo = p.PropertyNo
JOIN Client c ON l.ClientNo = c.ClientNo
WHERE RentFinish > GETDATE()
ORDER BY RentFinish DESC, RentStart



-- EXISTS and NOT EXISTS Query
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~
-- List all properties that have not had a viewing booked.

SELECT p.PropertyNo, OwnerNo, StreetAddress, City, Postcode, [Type], Rooms, Rent
FROM Property p
WHERE NOT EXISTS (SELECT * FROM Viewing v WHERE v.PropertyNo = p.PropertyNo); 