LAB7 1-4

-----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');

--5.    List staffs whose salary is greater than the salaries of all staffs who work at branch number B003.
--6.    List properties that are viewed by clients more than two times.
--7.    List clients who registered in the branch at 163 Main Street.
--8.    In each branch, list staffs whose salary is greater than the average salary in their branch.