SHARE
TWEET

LAB6

panaewboi Oct 21st, 2019 (edited) 98 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --1.    List all clients who have viewed a property. Include clients who haven’t viewed any property.
  2. --The result should show the client number, first name, property number and view date.
  3. SELECT c.clientno AS "client number", c.fname AS "first name", v.propertyno AS "property number", v.viewdate AS "view date" FROM client c  
  4. LEFT JOIN viewing v
  5. ON c.clientno =  v.clientno;
  6.  
  7. --2.    List all staffs who are not responsible to any property.
  8. SELECT * FROM staff s
  9. LEFT JOIN propertyforrent p
  10. ON s.staffno = p.staffno
  11. WHERE p.staffno IS NULL;
  12.  
  13. --3.    List all properties that are not viewed by any client
  14. --and the properties are managed by staffs who work in the branch located at 22 Deer Road street.
  15. SELECT * FROM propertyforrent p
  16. LEFT JOIN viewing v ON p.propertyno = v.propertyno
  17. JOIN staff s ON p.staffno = s.staffno
  18. JOIN branch b ON s.branchno = b.branchno
  19. WHERE v.propertyno IS NULL
  20. AND b.street = '22 Deer Road';
  21.  
  22. --distinct >> intersect
  23. --outer join ใช้เมื่อต้องการข้อมูลมากกว่า1คอลัมน์
  24.  
  25. SELECT staffno FROM staff
  26. minus
  27. SELECT staffno FROM propertyforrent;
  28.  
  29. SELECT s.staffno FROM staff s
  30. JOIN propertyforrent p
  31. ON s.staffno = p.staffno;
  32.  
  33.  
  34. --Using SET operators
  35. --4.    List all people in the Dream Home database.
  36. --The result should show the person number, first name and last name from the Staff, PrivateOwner, and Client tables.
  37. SELECT staffno "person number", fname "first name",lname "last name" FROM staff
  38. UNION
  39. SELECT ownerno  "person number", fname "first name",lname "last name" FROM privateowner
  40. UNION
  41. SELECT clientno "person number", fname "first name",lname "last name" FROM client;
  42.  
  43. --5.    List the property number of properties that were never viewed by any clients.
  44. SELECT propertyno FROM propertyforrent
  45. MINUS
  46. SELECT propertyno FROM viewing;
  47.  
  48. --6.    List all cities where there is either a branch office or a property for rent (but not both)
  49. (SELECT city FROM  branch
  50. UNION
  51. SELECT city FROM propertyforrent)
  52. MINUS
  53. (SELECT city FROM  branch
  54. INTERSECT
  55. SELECT city FROM propertyforrent);
  56.  
  57. --6
  58. (SELECT city FROM  branch
  59. MINUS
  60. SELECT city FROM propertyforrent)
  61. UNION
  62. (SELECT city FROM  branch
  63. MINUS
  64. SELECT city FROM propertyforrent);
  65.  
  66. --7.    List the property number and the full details of all private owners whose property was never viewed by clients.
  67. --(Hint: Use a join and a set operator) //ใช้ outer query
  68. SELECT p.propertyno ,po.*
  69. FROM propertyforrent p
  70. JOIN privateowner po
  71. ON p.ownerno = po.ownerno
  72. LEFT JOIN viewing v
  73. ON p.propertyno = v.propertyno
  74. minus
  75. SELECT p.propertyno  ,po.*
  76. FROM propertyforrent p
  77. JOIN privateowner po
  78. ON p.ownerno = po.ownerno
  79. JOIN viewing v
  80. ON p.propertyno = v.propertyno;
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