daily pastebin goal
55%
SHARE
TWEET

Untitled

a guest Oct 17th, 2017 404 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. 1.
  2. hotel=# SELECT * FROM guest LEFT OUTER JOIN booking ON guest.phone_number = booking.guest WHERE booking.guest IS NULL;
  3.  first_name | last_name | phone_number |         email         |               address               | check_in | check_out | guest | room
  4. ------------+-----------+--------------+-----------------------+-------------------------------------+----------+-----------+-------+------
  5.  Marcus     | Cruz      |   6304468851 | rgarcia@optonline.net | 103 Water Ave. Grand Forks ND 58201 |          |           |       |
  6.  Thalia     | Cobb      |   2269062721 | mxiao@yahoo.com       | 8929 Hall Court Flushing NY 11354   |          |           |       |
  7. (2 rows)
  8.  
  9.                                                   ^
  10. hotel=# SELECT guest.first_name, guest.last_name, booking.check_in, COUNT(booking.check_in) FROM guest JOIN booking ON guest.phone_number = booking.guest GROUP BY guest.first_name, guest.last_name, booking.check_in HAVING COUNT(*) > 1;
  11.  first_name | last_name |  check_in  | count
  12. ------------+-----------+------------+-------
  13.  Waylond    | Dalton    | 2017-10-22 |     3
  14. (1 row)
  15.  
  16.  
  17. hotel=# SELECT * FROM booking ou JOIN guest ON ou.guest = guest.phone_number  WHERE(SELECT count(*) from booking inr WHERE inr.room = ou.room AND inr.guest = ou.guest) > 1;
  18.   check_in  | check_out  |   guest    | room | first_name | last_name | phone_number |        email        |                address
  19. ------------+------------+------------+------+------------+-----------+--------------+---------------------+---------------------------------------
  20.  2017-11-10 | 2017-11-11 | 1255464478 |    9 | Justine    | Henderson |   1255464478 | fangorn@hotmail.com | 7591 Pheasant St. Schaumburg IL 60193
  21.  2017-11-13 | 2017-11-14 | 1255464478 |    9 | Justine    | Henderson |   1255464478 | fangorn@hotmail.com | 7591 Pheasant St. Schaumburg IL 60193
  22.  2017-11-15 | 2017-11-16 | 1255464478 |    9 | Justine    | Henderson |   1255464478 | fangorn@hotmail.com | 7591 Pheasant St. Schaumburg IL 60193
  23. (3 rows)
  24. 2.
  25. I will use three tables, one for students one for grades and one for classes.
  26. Student table will hold the name, id, phone number address and other personal info on each student.
  27. The class table will hold the class id, the subject, and the teacher assigned to it.
  28. The grades table will have the student id, the class id and the grade earned.
  29.  
  30. 3.
  31. school=# SELECT * FROM student JOIN grades ON student.id = grades.student_id JOIN class ON grades.class_id = class.id WHERE class.id = 1;
  32.            name            | id | student_id | class_id | grade | id | subject
  33. ---------------------------+----+------------+----------+-------+----+----------
  34.  Waylon Dalton             |  1 |          1 |        1 | A     |  1 | Language
  35.  Justine Henderson         |  2 |          2 |        1 | F     |  1 | Language
  36.  Marcus Cruz               |  4 |          4 |        1 | B     |  1 | Language
  37.  Thalia Cobb               |  5 |          5 |        1 | C     |  1 | Language
  38. (4 rows)
  39.  
  40. school=# SELECT grade FROM grades WHERE class_id = 1 ORDER BY grade;
  41.  grade
  42. -------
  43.  A
  44.  B
  45.  C
  46.  F
  47. (4 rows)
  48.        
  49. school=# SELECT class.subject, COUNT(grades.student_id) FROM grades JOIN class ON grades.class_id = class.id GROUP BY class.subject;
  50.      subject      | count
  51. ------------------+-------
  52.  Computer Science |     4
  53.  History          |     4
  54.  Science          |     3
  55.  Math             |     5
  56.  Language         |     4
  57. (5 rows)
  58.  
  59. school=# SELECT class.subject, COUNT(grades.student_id) FROM grades JOIN class ON grades.class_id = class.id GROUP BY class.subject ORDER BY count DESC LIMIT 1;
  60.  subject | count
  61. ---------+-------
  62.  Math    |     5
  63. (1 row)
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