API tools faq
paste
Guest User

Untitled

a guest
Feb 15th, 2019
373
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 11.93 KB | None | 0 0
raw download clone embed print report
  1. // 1. UPDATE, DELETE, INSERT INTO
  2. // 2. INSERT INTO populates the table with values; with the table name following and then stating VALUES . AFter that, you would write out the values.
  3. INSERT INTO products (id, name, price)
  4. VALUES
  5. (11773, 'South Face Jacket', 174.99),
  6. (11774, 'Big Mountain 2-Person Tent', 219.99),
  7. (11775, 'King Cole Camp Stove', 34.99),
  8. (11776, 'Oregon Trail Mountain Bike', 579.99);
  9.  
  10. UPDATE gets stated first with the table name following, then SET with the column value, then WHERE is stated and includes clause statements
  11. UPDATE products SET price=39.99, name='King Cole Camping Stove' WHERE id=11775;
  12.  
  13. DELETE FROM is typed first and then you state the table name. Afterwards, you type WHERE clauses to state which records you want removed.
  14. DELETE FROM products WHERE id> 11774 AND price < 100;
  15.  
  16. //3. Numbers can be used, like prices for mercahndise. Text can be used like descriptions of a purchase or name of a store. Time can be used like how long someone stays logged into an app.
  17.  
  18. //4.
  19. 4a.
  20. First and last name. (Text)
  21. Whether they sent in their RSVP. (boolean)
  22. Number of guests. (number)
  23. Number of meals. (number)
  24.  
  25. 4b.
  26. Richards-MacBook-Air-2:~ Richard$ createdb -U postgres -w wedding
  27. Richards-MacBook-Air-2:~ Richard$ psql wedding postgres
  28. wedding=# CREATE TABLE invites (first_name text, last_name text, RSVPd boolean, guests_joining numeric(3, 1), meals numeric(3, 1) );
  29. CREATE TABLE
  30. wedding=# SELECT * FROM invites;
  31. first_name | last_name | rsvpd | guests_joining | meals
  32. ------------+-----------+-------+----------------+-------
  33.  
  34. 4c.
  35. wedding=# ALTER TABLE invites ADD COLUMN thx_card boolean;
  36. ALTER TABLE
  37. wedding=# SELECT * FROM invites;
  38. first_name | last_name | rsvpd | guests_joining | meals | thx_card
  39. ------------+-----------+-------+----------------+-------+----------
  40.  
  41. 4d.
  42. wedding=# ALTER TABLE invites DROP COLUMN meals;
  43. ALTER TABLE
  44. wedding=# SELECT * FROM invites;
  45. first_name | last_name | rsvpd | guests_joining | thx_card
  46. ------------+-----------+-------+----------------+----------
  47.  
  48. 4e.
  49. wedding=# ALTER TABLE invites ADD COLUMN tbl_number numeric(2, 0);
  50. ALTER TABLE
  51. wedding=# SELECT * FROM invites;
  52. first_name | last_name | rsvpd | guests_joining | thx_card | tbl_number
  53. ------------+-----------+-------+----------------+----------+------------
  54.  
  55. 4f.
  56. DROP TABLE invites;
  57.  
  58. //5
  59. 5a.
  60. Richards-MacBook-Air-2:~ Richard$ createdb -U postgres -w new_library;
  61. Richards-MacBook-Air-2:~ Richard$ psql new_library postgres
  62. psql (11.1)
  63. Type "help" for help.
  64.  
  65. new_library=# CREATE TABLE books ( ISBN numeric(13, 0), title text, author text, genre text, pub_date date, copies numeric(2, 0), avail_copies numeric(20, 0) );
  66. CREATE TABLE
  67. new_library=# INSERT INTO books (isbn, title, author, genre, pub_date, copies, avail_copies) VALUES (9780060652920, 'Mere Christianity', 'C.S. Lewis', 'Apologetics', '2015-01-10', 20, 5), (9780141439563, 'Great Expectations', 'Charles Dickens', 'Classics', '2002-02-20', 10, 9), (9781250192455, 'A Higher Loyalty', 'James Comey', 'Auto-Bio', '2018-02-14', 5, 5);
  68. INSERT 0 3
  69. new_library=# SELECT * FROM books;
  70. isbn | title | author | genre | pub_date | copies | avail_copies
  71. ---------------+--------------------+-----------------+-------------+------------+--------+--------------
  72. 9780060652920 | Mere Christianity | C.S. Lewis | Apologetics | 2015-01-10 | 20 | 5
  73. 9780141439563 | Great Expectations | Charles Dickens | Classics | 2002-02-20 | 10 | 9
  74. 9781250192455 | A Higher Loyalty | James Comey | Auto-Bio | 2018-02-14 | 5 | 5
  75.  
  76. 5b.
  77. new_library=# UPDATE books SET avail_copies=4 WHERE isbn=9780060652920;
  78. UPDATE 1
  79. new_library=# SELECT * FROM books;
  80. isbn | title | author | genre | pub_date | copies | avail_copies
  81. ---------------+--------------------+-----------------+-------------+------------+--------+--------------
  82. 9780141439563 | Great Expectations | Charles Dickens | Classics | 2002-02-20 | 10 | 9
  83. 9781250192455 | A Higher Loyalty | James Comey | Auto-Bio | 2018-02-14 | 5 | 5
  84. 9780060652920 | Mere Christianity | C.S. Lewis | Apologetics | 2015-01-10 | 20 | 4
  85.  
  86. 5c.
  87. new_library=# DELETE FROM books WHERE isbn=9780141439563;
  88. DELETE 1
  89. new_library=# SELECT * FROM books;
  90. isbn | title | author | genre | pub_date | copies | avail_copies
  91. ---------------+-------------------+-------------+-------------+------------+--------+--------------
  92. 9781250192455 | A Higher Loyalty | James Comey | Auto-Bio | 2018-02-14 | 5 | 5
  93. 9780060652920 | Mere Christianity | C.S. Lewis | Apologetics | 2015-01-10 | 20 | 4
  94. (2 rows)
  95.  
  96. //6
  97. 6a.
  98. Richards-MacBook-Air-2:~ Richard$ createdb -U postgres -w NASA
  99. Richards-MacBook-Air-2:~ Richard$ psql NASA postgres
  100. psql (11.1)
  101. Type "help" for help.
  102.  
  103. NASA=# CREATE TABLE fleet (id numeric(4, 0), name text, inaug_year numeric(4, 0), nat_origin text, mission text, orbiting_body text, in_flight boolean, mi_from_earth numeric);
  104. CREATE TABLE
  105. NASA=# SELECT * FROM fleet;
  106. id | name | inaug_year | nat_origin | mission | orbiting_body | in_flight | mi_from_earth
  107. ----+------+------------+------------+---------+---------------+-----------+---------------
  108. (0 rows)
  109.  
  110. NASA=# INSERT INTO fleet (id, name, inaug_year, nat_origin, mission, orbiting_body, in_flight, mi_from_earth) VALUES (3456, 'ExoMars', 2016, 'Russia', 'atmospheric gas analysis', 'Mars', true, 452034985), (7896, 'OSIRIS', 2018, 'USA', 'obtain asteroid sample', 'asteroid 101955 Bennu', true, 20983475), (3451, 'MESSENGER', 2011, 'USA', 'flyby of mercury', 'Mercury', false, 2938476546);
  111. INSERT 0 3
  112. NASA=# SELECT * FROM fleet;
  113. id | name | inaug_year | nat_origin | mission | orbiting_body | in_flight | mi_from_earth
  114. ------+-----------+------------+------------+--------------------------+-----------------------+-----------+---------------
  115. 3456 | ExoMars | 2016 | Russia | atmospheric gas analysis | Mars | t | 452034985
  116. 7896 | OSIRIS | 2018 | USA | obtain asteroid sample | asteroid 101955 Bennu | t | 20983475
  117. 3451 | MESSENGER | 2011 | USA | flyby of mercury | Mercury | f | 2938476546
  118.  
  119. 6b.
  120. NASA=# DELETE FROM fleet WHERE id=3451;
  121. DELETE 1
  122. NASA=# SELECT * FROM fleet;
  123. id | name | inaug_year | nat_origin | mission | orbiting_body | in_flight | mi_from_earth
  124. ------+---------+------------+------------+--------------------------+-----------------------+-----------+---------------
  125. 3456 | ExoMars | 2016 | Russia | atmospheric gas analysis | Mars | t | 452034985
  126. 7896 | OSIRIS | 2018 | USA | obtain asteroid sample | asteroid 101955 Bennu | t | 20983475
  127.  
  128. 6c.
  129. NASA=# UPDATE fleet SET in_flight=FALSE WHERE id=7896;
  130. UPDATE 1
  131. NASA=# SELECT * FROM fleet;
  132. id | name | inaug_year | nat_origin | mission | orbiting_body | in_flight | mi_from_earth
  133. ------+---------+------------+------------+--------------------------+-----------------------+-----------+---------------
  134. 3456 | ExoMars | 2016 | Russia | atmospheric gas analysis | Mars | t | 452034985
  135. 7896 | OSIRIS | 2018 | USA | obtain asteroid sample | asteroid 101955 Bennu | f | 20983475
  136.  
  137. //7
  138. 7a.
  139. Richards-MacBook-Air-2:~ Richard$ createDB -U postgres -w email
  140. Richards-MacBook-Air-2:~ Richard$ psql email postgres
  141. psql (11.1)
  142. Type "help" for help.
  143.  
  144. email=# CREATE TABLE inbox (id numeric, subject text, add_recipients text, body text, timestamp timestamp, unread boolean, id_email_chain text, sender text);
  145. CREATE TABLE
  146. email=# INSERT INTO inbox VALUES (1, 'vdoc', '--', 'hey richard, just wondering if when you are going to send the song lyrics to wendy?', '2004-10-19 10:23:54', TRUE, 'origional msg', 'john.cai@gpmail.org');
  147. INSERT 0 1
  148. email=# INSERT INTO inbox VALUES (1, 'payment', '--', 'Just a reminder to send in your payment', '2004-10-19 10:23:54', TRUE, 'origional msg', 'sweetwater.support@gmail.com');
  149. INSERT 0 1
  150. email=# UPDATE inbox SET id=2 WHERE subject='payment';
  151. UPDATE 1
  152. email=# INSERT INTO inbox VALUES (3, 'light phone', '--', 'Update for the latest build is coming soon! Hold tight!', '2004-10-19 10:23:54', TRUE, 'originonal msg', 'lightphone@seed.co');
  153. INSERT 0 1
  154. email=# SELECT * FROM inbox;
  155. id | subject | add_recipients | body | timestamp | unread | id_email_chain | sender
  156. ----+-------------+----------------+-------------------------------------------------------------------------------------+---------------------+--------+----------------+------------------------------
  157. 1 | vdoc | -- | hey richard, just wondering if when you are going to send the song lyrics to wendy? | 2004-10-19 10:23:54 | t | origional msg | john.cai@gpmail.org
  158. 2 | payment | -- | Just a reminder to send in your payment | 2004-10-19 10:23:54 | t | origional msg | sweetwater.support@gmail.com
  159. 3 | light phone | -- | Update for the latest build is coming soon! Hold tight! | 2004-10-19 10:23:54 | t | originonal msg | lightphone@seed.co
  160.  
  161. 7b.
  162. email=# DELETE FROM inbox WHERE id=3;
  163. DELETE 1
  164. email=# SELECT * FROM inbox;
  165. id | subject | add_recipients | body | timestamp | unread | id_email_chain | sender
  166. ----+---------+----------------+-------------------------------------------------------------------------------------+---------------------+--------+----------------+------------------------------
  167. 1 | vdoc | -- | hey richard, just wondering if when you are going to send the song lyrics to wendy? | 2004-10-19 10:23:54 | t | origional msg | john.cai@gpmail.org
  168. 2 | payment | -- | Just a reminder to send in your payment | 2004-10-19 10:23:54 | t | origional msg | sweetwater.support@gmail.com
  169.  
  170. 7c.
  171. email=# UPDATE inbox SET unread=FALSE WHERE id=2;
  172. UPDATE 1
  173. email=# SELECT * FROM inbox;
  174. id | subject | add_recipients | body | timestamp | unread | id_email_chain | sender
  175. ----+---------+----------------+-------------------------------------------------------------------------------------+---------------------+--------+----------------+------------------------------
  176. 1 | vdoc | -- | hey richard, just wondering if when you are going to send the song lyrics to wendy? | 2004-10-19 10:23:54 | t | origional msg | john.cai@gpmail.org
  177. 2 | payment | -- | Just a reminder to send in your payment | 2004-10-19 10:23:54 | f | origional msg | sweetwater.support@gmail.com
  178. (2 rows)
  179.  
  180. email=#
  181. email=# UPDATE inbox SET unread=TRUE WHERE id=2;
  182. UPDATE 1
  183. email=# SELECT * FROM inbox;
  184. id | subject | add_recipients | body | timestamp | unread | id_email_chain | sender
  185. ----+---------+----------------+-------------------------------------------------------------------------------------+---------------------+--------+----------------+------------------------------
  186. 1 | vdoc | -- | hey richard, just wondering if when you are going to send the song lyrics to wendy? | 2004-10-19 10:23:54 | t | origional msg | john.cai@gpmail.org
  187. 2 | payment | -- | Just a reminder to send in your payment | 2004-10-19 10:23:54 | t | origional msg | sweetwater.support@gmail.com
  188. (2 rows)
  189.  
  190. email=#
Add Comment
Please, Sign In to add comment
Public Pastes
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!