LAB7 complete

-----LAB7-----

--1.    List staffs who manage properties with three rooms.
SELECT *
FROM staff s
WHERE s.staffno IN(
    SELECT pf.staffno
    FROM propertyforrent pf
    WHERE pf.rooms = 3);


--2.    List properties that owner’s telephone number starts with 012.
SELECT *
FROM propertyforrent pf
WHERE pf.ownerno IN(
    SELECT po.ownerno
    FROM privateowner po
    WHERE pf.ownerno = po.ownerno
    AND po.telno LIKE '012%');

--3.    List branches that do not have any staff.

--type1
SELECT *
FROM branch b
WHERE b.branchno NOT IN (
    SELECT branchno
    FROM staff);
-- --- ---- -----
--type2
SELECT *
FROM branch b
WHERE NOT EXISTS(
    SELECT s.branchno
    FROM staff s
    WHERE s.branchno = b.branchno);


--4.    List staffs whose salary is less than the average salary of all staffs and staffs who work in the branch located in Glasgow city.
SELECT *
FROM staff s
WHERE s.salary <(
    SELECT AVG(s.salary)
    FROM staff s
)AND EXISTS(
    SELECT b.branchno
    FROM branch b
    WHERE s.branchno = b.branchno
    AND b.city = 'Glasgow');

---------- 4 NOVEMBER 2019 ----------
--5.    List staffs whose salary is greater than the salaries of all staffs who work at branch number B003.
SELECT *
FROM staff s
WHERE s.salary > (
    SELECT MAX(s.salary)
    FROM staff s
    JOIN branch b
    ON s.branchno = b.branchno
    WHERE b.branchno = 'B003'
);

SELECT *
FROM staff s
WHERE s.salary > ALL(
    SELECT s.salary
    FROM staff s
    JOIN branch b
    ON s.branchno = b.branchno
    WHERE b.branchno = 'B003'
);

--6.    List properties that are viewed by clients more than two times.
--type1
SELECT *
FROM propertyforrent pf --EXISTS ถ้าเป็นtrueจะแสดงผลลัพธ์ออกมา
WHERE EXISTS(
    SELECT v.propertyno , COUNT(v.clientno)
    FROM viewing v
    WHERE pf.propertyno = v.propertyno
    GROUP BY v.propertyno
    HAVING COUNT(v.clientno) > 2
);

--type2
SELECT *
FROM propertyforrent
WHERE propertyno IN(
    SELECT propertyno FROM viewing
    GROUP BY propertyno
    HAVING COUNT(clientno)>2
);

-- 7.   List clients who registered in the branch at 163 Main Street.
SELECT *
FROM client c
WHERE c.clientno IN(
    SELECT r.clientno
    FROM registration r
    JOIN branch b
    ON b.branchno = r.branchno
    WHERE c.clientno = r.clientno
    AND b.street = '163 Main Street'
);

-- 8.   In each branch, list staffs whose salary is greater than the average salary in their branch.
--only type2
SELECT *
FROM staff s
WHERE  s.salary > (
            SELECT AVG(salary)
            FROM staff
            WHERE branchno = s.branchno
            GROUP BY branchno);