-- DATABASE MANAGEMENT SYSTEMS 2019/20
-- LAB EXERCISE 6
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- Grouping
-- ~~~~~~~~~~~~~~~~~~~~
-- How many clients does each branch have registered with them?
SELECT BranchNo, COUNT(ClientNo) NumClients 
FROM Client 
GROUP BY BranchNo;


-- Find out how many properties are currently registered for the different property types and what the average price of renting each property type is.
-- I have used CAST(... AS decimal(8,2)) to format the average values
SELECT [Type], COUNT(PropertyNo) NumProperties, CAST(AVG(rent) AS decimal(8,2)) AvgRentalPrice
FROM Property
GROUP BY [type];


-- List the staff number and number of properties managed by each member of staff.
SELECT ManagedBy, COUNT(PropertyNo) NumProperties 
FROM Property
GROUP BY ManagedBy


-- Find out the number of properties being managed by members of staff who are currently managing over 4 properties.
SELECT ManagedBy, COUNT(PropertyNo) NumProperties 
FROM Property
GROUP BY ManagedBy 
HAVING COUNT(PropertyNo) > 4;

-- Find out the number of viewings booked for all properties
SELECT propertyNo, COUNT(viewingDateTime) NumViewings 
FROM Viewing
GROUP BY propertyNo;



-- Subqueries
-- ~~~~~~~~~~~~~~~~~~~~
-- List all properties, showing property number, address and type, with rental prices less than the national average rental price and by how much they are less than this average.
SELECT PropertyNo, [Type], StreetAddress, City, Postcode, (SELECT AVG(rent) FROM Property) - Rent RentDifference
FROM Property
WHERE Rent < (SELECT AVG(Rent) FROM Property);


-- Adding CAST ... AS decimal(n, 2) to format the Rent Difference
SELECT PropertyNo, [Type], StreetAddress, City, Postcode, CAST((SELECT AVG(rent) FROM Property) - Rent AS decimal(10,2)) RentDifference
FROM Property
WHERE Rent < (SELECT AVG(Rent) FROM Property);



-- List all properties, showing the property number, type, address and managed by, registered with Dream Home that have NEVER BEEN Rented.
-- Hint you will need to use NOT IN
SELECT PropertyNo, [Type], StreetAddress, City, Postcode, ManagedBy
FROM Property
WHERE PropertyNo NOT IN (SELECT PropertyNo FROM Lease) 


-- How much has Branch, B0003, earned from renting properties?
-- Hint: You will need to refer to one of the queries from last week as well as use a subquery to find the properties for branch B0003
SELECT SUM(MonthlyRent * Duration) TotalRent 
FROM Lease 
WHERE PropertyNo IN (SELECT Propertyno FROM Property WHERE BranchNo = 'B0003')

