Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement