SELECT DISTINCT POSITION FROM staff;

SELECT POSITION FROM staff;
SELECT POSITION,COUNT(*) "count" FROM staff GROUP BY POSITION;

SELECT * FROM staff;
SELECT POSITION,COUNT(*)"count",MAX(salary),MIN(salary),SUM(salary)
FROM staff GROUP BY POSITION HAVING COUNT(*)>1;

SELECT POSITION,COUNT(*)"count",MAX(salary),MIN(salary),SUM(salary)
FROM staff WHERE position<>'Manager' GROUP BY POSITION;

SELECT branchno"Branch Number",fname "First Name",COUNT(*)"count"
FROM staff GROUP BY branchno,fname;

SELECT * FROM branch b JOIN staff s ON s.branchno = b.branchno;

SELECT s.*,b.city FROM branch b JOIN staff s
ON s.branchno = b.branchno;

SELECT b.city "Branch City",s.fname "First Name",p.city "Property City"
FROM staff s JOIN branch b ON s.branchno = b.branchno
JOIN propertyforrent p ON s.staffno = p.staffno WHERE b.city ='London';

SELECT b.city "Branch City",COUNT(s.fname) "First Name"
FROM staff s JOIN branch b ON s.branchno = b.branchno
JOIN propertyforrent p ON s.staffno = p.staffno
GROUP BY b.city HAVING COUNT(*)>2;

SELECT staffno,COUNT(*)"count" FROM propertyforrent  GROUP BY staffno;

SELECT s.fname,s.lname,COUNT(*) FROM propertyforrent p JOIN staff s
ON p.staffno = s.staffno
GROUP BY s.fname,s.lname;


---แสดงข้อมูลสาขาทั้งหมดพร้อมกับแสดงชื่อเจ้าของที่อยู่ในสาขานั้นๆ
SELECT DISTINCT b.*,pri.fname "First Name" FROM branch b
JOIN staff s ON b.branchno = s.branchno
JOIN propertyforrent p ON s.staffno = p.staffno
JOIN privateowner pri ON p.ownerno = pri.ownerno;


SELECT DISTINCT pri.fname"First Name",pri.lname"Last Name", pro.*
FROM propertyforrent pro JOIN privateowner pri
ON pro.ownerno = pri.ownerno;----1

SELECT s.staffno"Staff Number",s.fname"First Name"
,s.lname "Last Name",b.branchno "Branch",b.city"City"
FROM staff s JOIN branch b ON s.branchno = b.branchno;------2

SELECT * FROM viewing;
SELECT c.fname"First Name",c.lname "LAst Name",COUNT(*)"View count" FROM client c JOIN viewing v
ON c.clientno = v.clientno GROUP BY c.fname,c.lname HAVING COUNT(*)>1;-----3

SELECT s.staffno"Staff Number",b.branchno"Branch number",pro.propertyno
FROM staff s JOIN branch b ON s.branchno = b.branchno
JOIN propertyforrent pro ON s.staffno = pro.staffno;-----4

SELECT DISTINCT s.staffno,b.city,b.branchno,pro.TYPE FROM staff s
JOIN branch b ON s.branchno = b.branchno
JOIN propertyforrent pro ON s.staffno = pro.staffno
WHERE pro.TYPE ='House';-----5

SELECT c.fname"Fist",c.lname"Last",v.viewdate,p.city
FROM viewing v JOIN client c ON v.clientno = c.clientno
JOIN propertyforrent p ON v.propertyno = p.propertyno
WHERE city='Glasgow';-----6

SELECT pri.fname ||pri.lname "Name",COUNT(v.propertyno)"View count"
FROM propertyforrent p JOIN privateowner pri
ON p.ownerno = pri.ownerno JOIN viewing v
ON p.propertyno = v.propertyno GROUP BY pri.fname ||pri.lname,p.city
HAVING city='Glasgow' AND COUNT(v.propertyno)>2;----7

SELECT pri.fname "First", pri.lname "Last",s.branchno "Branch number",p.*
FROM propertyforrent p JOIN privateowner pri ON p.ownerno = pri.ownerno
JOIN staff s ON p.staffno = s.staffno WHERE s.branchno='B003';------8

SELECT COUNT(p.propertyno) "จำนวนทรัพย์สิน",b.city FROM branch b JOIN staff s ON b.branchno = s.branchno
JOIN propertyforrent p ON s.staffno = p.staffno GROUP BY b.city
ORDER BY COUNT(p.propertyno) DESC; ----9