lab5

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



----- PRACTICE 2 -----
SELECT pf.* FROM privateowner po JOIN propertyforrent pf ON po.ownerno = pf.ownerno;
-- 1. List full details of all properties for rent along with owner’s name (first name and last name).

SELECT staffno, fname, lname, b.city FROM staff s JOIN branch b ON s.branchno = b.branchno;
-- 2. List the staff number, first and last names and branch city of all staffs.

SELECT DISTINCT c.fname, COUNT(v.viewdate)
FROM client c JOIN viewing v ON c.clientno = v.clientno
GROUP BY c.fname HAVING COUNT(v.viewdate)>1;
-- 3. List the name of clients who have viewed the properties more than one time.

SELECT s.staffno, s.branchno, po.propertyno
FROM propertyforrent po JOIN staff s ON po.staffno = s.staffno;
-- 4. List the number of properties handled by each staff member, along with the branch number of the member of staff

SELECT DISTINCT s.fname, b.city
FROM branch b JOIN staff s ON b.branchno = s.branchno
    JOIN propertyforrent p ON s.staffno = p.staffno
    WHERE TYPE = 'House';
-- 5. List the branch cities that staffs manage the properties that are house type. Remove the duplicate records.

SELECT *
FROM client c JOIN viewing v ON c.clientno = v.clientno
JOIN registration r ON c.clientno = r.clientno
JOIN branch b ON r.branchno = b.branchno
JOIN staff s ON r.staffno = s.staffno;



SELECT c.fname || ' ' || c.lname AS "client name",
    v.viewdate
FROM client c JOIN viewing v ON c.clientno = v.clientno
JOIN registration r ON c.clientno = r.clientno
JOIN branch b ON r.branchno = b.branchno
JOIN staff s ON r.staffno = s.staffno
WHERE b.city = 'Glasgow';
-- 6. List the client name and view date of all viewings on properties that are managed by staff's branch office in Glasgow.

SELECT po.fname || ' ' || po.lname AS "name" , COUNT(v.viewdate)
FROM propertyforrent pf JOIN privateowner po ON pf.ownerno = po.ownerno
    JOIN viewing v ON pf.propertyno = v.propertyno
    WHERE pf.city = 'Glasgow'
    GROUP BY po.fname || ' ' || po.lname
    HAVING COUNT(pf.propertyno)>2;
-- 7. List the name of owners whose properties locate in Glasgow and clients have viewed the properties of the same owner more than two times.


SELECT s.branchno,
        po.fname || ' ' || po.lname AS "owner's name",
        pf.propertyno,
        pf.street,
        pf.city,
        pf.postcode,
        pf.TYPE,
        pf.rooms,
        pf.rent,
        pf.staffno,
        pf.ownerno
FROM propertyforrent pf JOIN privateowner po ON pf.ownerno = po.ownerno
JOIN staff s ON pf.staffno = s.staffno
WHERE s.branchno = 'B003';
-- 8. List full details of all properties for rent along with owner’s name (first name and last name).  Show only the properties that are managed by staffs in Branch No B003.


SELECT b.city,COUNT(pf.propertyno)
FROM propertyforrent pf JOIN staff s ON pf.staffno = s.staffno
    JOIN branch b ON s.branchno = b.branchno
GROUP BY b.city;
-- 9. How many properties are managed in each branch city?