SHARE
TWEET

Untitled

YOYOPAPANG Sep 17th, 2019 108 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT DISTINCT POSITION FROM staff;
  2.  
  3. SELECT POSITION FROM staff;
  4. SELECT POSITION,COUNT(*) "count" FROM staff GROUP BY POSITION;
  5.  
  6. SELECT * FROM staff;
  7. SELECT POSITION,COUNT(*)"count",MAX(salary),MIN(salary),SUM(salary)
  8. FROM staff GROUP BY POSITION HAVING COUNT(*)>1;
  9.  
  10. SELECT POSITION,COUNT(*)"count",MAX(salary),MIN(salary),SUM(salary)
  11. FROM staff WHERE position<>'Manager' GROUP BY POSITION;
  12.  
  13. SELECT branchno"Branch Number",fname "First Name",COUNT(*)"count"
  14. FROM staff GROUP BY branchno,fname;
  15.  
  16. SELECT * FROM branch b JOIN staff s ON s.branchno = b.branchno;
  17.  
  18. SELECT s.*,b.city FROM branch b JOIN staff s
  19. ON s.branchno = b.branchno;
  20.  
  21. SELECT b.city "Branch City",s.fname "First Name",p.city "Property City"
  22. FROM staff s JOIN branch b ON s.branchno = b.branchno
  23. JOIN propertyforrent p ON s.staffno = p.staffno WHERE b.city ='London';
  24.  
  25. SELECT b.city "Branch City",COUNT(s.fname) "First Name"
  26. FROM staff s JOIN branch b ON s.branchno = b.branchno
  27. JOIN propertyforrent p ON s.staffno = p.staffno
  28. GROUP BY b.city HAVING COUNT(*)>2;
  29.  
  30. SELECT staffno,COUNT(*)"count" FROM propertyforrent  GROUP BY staffno;
  31.  
  32. SELECT s.fname,s.lname,COUNT(*) FROM propertyforrent p JOIN staff s
  33. ON p.staffno = s.staffno
  34. GROUP BY s.fname,s.lname;
  35.  
  36.  
  37. ---แสดงข้อมูลสาขาทั้งหมดพร้อมกับแสดงชื่อเจ้าของที่อยู่ในสาขานั้นๆ
  38. SELECT DISTINCT b.*,pri.fname "First Name" FROM branch b
  39. JOIN staff s ON b.branchno = s.branchno
  40. JOIN propertyforrent p ON s.staffno = p.staffno
  41. JOIN privateowner pri ON p.ownerno = pri.ownerno;
  42.  
  43.  
  44. SELECT DISTINCT pri.fname"First Name",pri.lname"Last Name", pro.*  
  45. FROM propertyforrent pro JOIN privateowner pri
  46. ON pro.ownerno = pri.ownerno;----1
  47.  
  48. SELECT s.staffno"Staff Number",s.fname"First Name"
  49. ,s.lname "Last Name",b.branchno "Branch",b.city"City"
  50. FROM staff s JOIN branch b ON s.branchno = b.branchno;------2
  51.  
  52. SELECT * FROM viewing;
  53. SELECT c.fname"First Name",c.lname "LAst Name",COUNT(*)"View count" FROM client c JOIN viewing v
  54. ON c.clientno = v.clientno GROUP BY c.fname,c.lname HAVING COUNT(*)>1;-----3
  55.  
  56. SELECT s.staffno"Staff Number",b.branchno"Branch number",pro.propertyno
  57. FROM staff s JOIN branch b ON s.branchno = b.branchno
  58. JOIN propertyforrent pro ON s.staffno = pro.staffno;-----4
  59.  
  60. SELECT DISTINCT s.staffno,b.city,b.branchno,pro.TYPE FROM staff s
  61. JOIN branch b ON s.branchno = b.branchno
  62. JOIN propertyforrent pro ON s.staffno = pro.staffno
  63. WHERE pro.TYPE ='House';-----5
  64.  
  65. SELECT c.fname"Fist",c.lname"Last",v.viewdate,p.city
  66. FROM viewing v JOIN client c ON v.clientno = c.clientno
  67. JOIN propertyforrent p ON v.propertyno = p.propertyno
  68. WHERE city='Glasgow';-----6
  69.  
  70. SELECT pri.fname ||pri.lname "Name",COUNT(v.propertyno)"View count"
  71. FROM propertyforrent p JOIN privateowner pri
  72. ON p.ownerno = pri.ownerno JOIN viewing v
  73. ON p.propertyno = v.propertyno GROUP BY pri.fname ||pri.lname,p.city
  74. HAVING city='Glasgow' AND COUNT(v.propertyno)>2;----7
  75.  
  76. SELECT pri.fname "First", pri.lname "Last",s.branchno "Branch number",p.*
  77. FROM propertyforrent p JOIN privateowner pri ON p.ownerno = pri.ownerno
  78. JOIN staff s ON p.staffno = s.staffno WHERE s.branchno='B003';------8
  79.  
  80. SELECT COUNT(p.propertyno) "จำนวนทรัพย์สิน",b.city FROM branch b JOIN staff s ON b.branchno = s.branchno
  81. JOIN propertyforrent p ON s.staffno = p.staffno GROUP BY b.city
  82. ORDER BY COUNT(p.propertyno) DESC; ----9
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top