lab5 part1 complete

----- PRACTICE 1 -----
SELECT * FROM propertyforrent;

SELECT COUNT(*) FROM  propertyforrent;
-- 1. How many clients are there?

SELECT COUNT(*) FROM propertyforrent WHERE TYPE = 'Flat';
-- 2. How many clients whose preferred property type are Flat?

SELECT AVG(rent) FROM propertyforrent WHERE TYPE = 'Flat' AND rooms = 3;
-- 3. What is average rent for three-room flat?

SELECT POSITION, COUNT(staffno), MIN(salary), MAX(salary), AVG(salary)
    FROM staff
    GROUP BY POSITION;
-- 4. What are the number of staff and the minimum, maximum and average staff salary in each staff position?

SELECT * FROM viewing;

SELECT clientno, COUNT(clientno)
    FROM viewing
    GROUP BY clientno HAVING COUNT(clientno)>1;
-- 5. Which clients have viewed the properties more than one time?

SELECT * FROM propertyforrent;

SELECT ownerno, COUNT(*)
    FROM propertyforrent
    GROUP BY ownerno;
-- 6. How many properties are owned by each property owner?

SELECT propertyno, COUNT(staffno),TYPE
    FROM propertyforrent
    GROUP BY propertyno, TYPE;
-- 7. Count the number of properties by staff number and type of property

SELECT * FROM registration;

SELECT branchno, COUNT(clientno) FROM registration
GROUP BY branchno;
-- 8. How many customers register in each branch? Show the branch number and the number of customers.

SELECT * FROM propertyforrent;

SELECT TYPE, COUNT(TYPE)
FROM propertyforrent
GROUP BY TYPE;
-- 9. Find out the total number of properties rented by type of property.

SELECT city, TYPE, AVG(rent)
FROM propertyforrent
GROUP BY city, TYPE;
-- 10.Find out the average rent of property rented by city and by type of property.