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

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



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



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



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





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





-- Produce a list of ALL branch codes, how any properties are registered at each branch and how much each branch has earned in rent in DESCENDING order of rent earnings.
/*
	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
*/



-- Extend the query used in the last question to include the branch city location and street address.







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




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