LAB6

--1.    List all clients who have viewed a property. Include clients who haven’t viewed any property.
--The result should show the client number, first name, property number and view date.
SELECT c.clientno AS "client number", c.fname AS "first name", v.propertyno AS "property number", v.viewdate AS "view date" FROM client c
LEFT JOIN viewing v
ON c.clientno =  v.clientno;

--2.    List all staffs who are not responsible to any property.
SELECT * FROM staff s
LEFT JOIN propertyforrent p
ON s.staffno = p.staffno
WHERE p.staffno IS NULL;

--3.    List all properties that are not viewed by any client
--and the properties are managed by staffs who work in the branch located at 22 Deer Road street.
SELECT * FROM propertyforrent p
LEFT JOIN viewing v ON p.propertyno = v.propertyno
JOIN staff s ON p.staffno = s.staffno
JOIN branch b ON s.branchno = b.branchno
WHERE v.propertyno IS NULL
AND b.street = '22 Deer Road';

--distinct >> intersect
--outer join ใช้เมื่อต้องการข้อมูลมากกว่า1คอลัมน์

SELECT staffno FROM staff
minus
SELECT staffno FROM propertyforrent;

SELECT s.staffno FROM staff s
JOIN propertyforrent p
ON s.staffno = p.staffno;


--Using SET operators
--4.    List all people in the Dream Home database.
--The result should show the person number, first name and last name from the Staff, PrivateOwner, and Client tables.
SELECT staffno "person number", fname "first name",lname "last name" FROM staff
UNION
SELECT ownerno  "person number", fname "first name",lname "last name" FROM privateowner
UNION
SELECT clientno "person number", fname "first name",lname "last name" FROM client;

--5.    List the property number of properties that were never viewed by any clients.
SELECT propertyno FROM propertyforrent
MINUS
SELECT propertyno FROM viewing;

--6.    List all cities where there is either a branch office or a property for rent (but not both)
(SELECT city FROM  branch
UNION
SELECT city FROM propertyforrent)
MINUS
(SELECT city FROM  branch
INTERSECT
SELECT city FROM propertyforrent);

--6
(SELECT city FROM  branch
MINUS
SELECT city FROM propertyforrent)
UNION
(SELECT city FROM  branch
MINUS
SELECT city FROM propertyforrent);

--7.    List the property number and the full details of all private owners whose property was never viewed by clients.
--(Hint: Use a join and a set operator) //ใช้ outer query
SELECT p.propertyno ,po.*
FROM propertyforrent p
JOIN privateowner po
ON p.ownerno = po.ownerno
LEFT JOIN viewing v
ON p.propertyno = v.propertyno
minus
SELECT p.propertyno  ,po.*
FROM propertyforrent p
JOIN privateowner po
ON p.ownerno = po.ownerno
JOIN viewing v
ON p.propertyno = v.propertyno;