SHARE
TWEET

Untitled

a guest Jul 23rd, 2019 62 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. test_guru=# CREATE TABLE categories (
  2. test_guru(#  id serial PRIMARY KEY,
  3. test_guru(#  title varchar(25) NOT null
  4. test_guru(# );
  5. CREATE TABLE
  6. test_guru=# SELECT *
  7. test_guru-# FROM categories;
  8.  id | title
  9. ----+-------
  10. (0 rows)
  11.  
  12. test_guru=# CREATE TABLE tests (
  13. test_guru(#  id serial PRIMARY KEY,
  14. test_guru(#  title varchar(50) NOT null,
  15. test_guru(#  level int NOT null,
  16. test_guru(#  category_id int NOT null
  17. test_guru(# );
  18. CREATE TABLE
  19. test_guru=# SELECT *
  20. test_guru-# FROM tests
  21. test_guru-# ;
  22.  id | title | level | category_id
  23. ----+-------+-------+-------------
  24. (0 rows)
  25.  
  26. test_guru=# CREATE TABLE questions (
  27. test_guru(#  body varchar(250) NOT null,
  28. test_guru(#  test_id int NOT null
  29. test_guru(# );
  30. CREATE TABLE
  31. test_guru=# SELECT *
  32. test_guru-# FROM questions;
  33.  body | test_id
  34. ------+---------
  35. (0 rows)
  36.  
  37. test_guru=# INSERT INTO categories(title) VALUES
  38. test_guru-#  ('Frontend'),
  39. test_guru-#  ('Backend'),
  40. test_guru-#  ('Machine Learning');
  41. INSERT 0 3
  42. test_guru=# SELECT *
  43. test_guru-# FROM categories;
  44.  id |      title      
  45. ----+------------------
  46.   1 | Frontend
  47.   2 | Backend
  48.   3 | Machine Learning
  49. (3 rows)
  50.  
  51. test_guru=# INSERT INTO tests(title, level, category_id) VALUES
  52. test_guru-#  ('HTML', 1, 1),
  53. test_guru-#  ('Python', 2, 2),
  54. test_guru-#  ('R', 2, 3),
  55. test_guru-#  ('Ruby', 2, 2),
  56. test_guru-#  ('JavaScript', 3, 1);
  57. INSERT 0 5
  58. test_guru=# SELECT *
  59. test_guru-# FROM tests;
  60.  id |   title    | level | category_id
  61. ----+------------+-------+-------------
  62.   1 | HTML       |     1 |           1
  63.   2 | Python     |     2 |           2
  64.   3 | R          |     2 |           3
  65.   4 | Ruby       |     2 |           2
  66.   5 | JavaScript |     3 |           1
  67. (5 rows)
  68.  
  69. test_guru=# INSERT INTO questions(body, test_id) VALUES
  70. test_guru-#  ('Which of these is not a Python datatype?', 2),
  71. test_guru-#  ('Which of the following method make a vector of repeated values?', 3),
  72. test_guru-#  ('What does HTML stand for?', 1),
  73. test_guru-#  ('What is the name of the element that is used to define what an object looks like after it is created?', 4),
  74. test_guru-#  ('Where is the correct place to insert a JavaScript?', 5);
  75. INSERT 0 5
  76. test_guru=# SELECT *
  77. test_guru-# FROM questions;
  78.                                                  body                                                  | test_id
  79. -------------------------------------------------------------------------------------------------------+---------
  80.  Which of these is not a Python datatype?                                                              |       2
  81.  Which of the following method make a vector of repeated values?                                       |       3
  82.  What does HTML stand for?                                                                             |       1
  83.  What is the name of the element that is used to define what an object looks like after it is created? |       4
  84.  Where is the correct place to insert a JavaScript?                                                    |       5
  85. (5 rows)
  86.  
  87. test_guru=# SELECT *
  88. test_guru-# FROM tests
  89. test_guru-# WHERE NOT level = 1;
  90.  id |   title    | level | category_id
  91. ----+------------+-------+-------------
  92.   2 | Python     |     2 |           2
  93.   3 | R          |     2 |           3
  94.   4 | Ruby       |     2 |           2
  95.   5 | JavaScript |     3 |           1
  96. (4 rows)
  97.  
  98. test_guru=# SELECT body, test_id
  99. test_guru-# FROM questions
  100. test_guru-# WHERE test_id = 4;
  101.                                                  body                                                  | test_id
  102. -------------------------------------------------------------------------------------------------------+---------
  103.  What is the name of the element that is used to define what an object looks like after it is created? |       4
  104. (1 row)
  105.  
  106. test_guru=# UPDATE tests
  107. test_guru-# SET title = 'Ruby Language', level = 3
  108. test_guru-# WHERE title = 'Ruby';
  109. UPDATE 1
  110. test_guru=# SELECT *
  111. test_guru-# FROM tests;
  112.  id |     title     | level | category_id
  113. ----+---------------+-------+-------------
  114.   1 | HTML          |     1 |           1
  115.   2 | Python        |     2 |           2
  116.   3 | R             |     2 |           3
  117.   5 | JavaScript    |     3 |           1
  118.   4 | Ruby Language |     3 |           2
  119. (5 rows)
  120.  
  121. test_guru=# DELETE
  122. test_guru-# FROM questions
  123. test_guru-# WHERE test_id = 5;
  124. DELETE 1
  125. test_guru=# SELECT *
  126. test_guru-# FROM questions
  127. test_guru-# ;
  128.                                                  body                                                  | test_id
  129. -------------------------------------------------------------------------------------------------------+---------
  130.  Which of these is not a Python datatype?                                                              |       2
  131.  Which of the following method make a vector of repeated values?                                       |       3
  132.  What does HTML stand for?                                                                             |       1
  133.  What is the name of the element that is used to define what an object looks like after it is created? |       4
  134. (4 rows)
  135.  
  136. test_guru=# SELECT *
  137. test_guru-# FROM tests
  138. test_guru-# JOIN categories
  139. test_guru-# ON tests.category_id = categories.id;
  140.  id |     title     | level | category_id | id |      title      
  141. ----+---------------+-------+-------------+----+------------------
  142.   1 | HTML          |     1 |           1 |  1 | Frontend
  143.   2 | Python        |     2 |           2 |  2 | Backend
  144.   3 | R             |     2 |           3 |  3 | Machine Learning
  145.   5 | JavaScript    |     3 |           1 |  1 | Frontend
  146.   4 | Ruby Language |     3 |           2 |  2 | Backend
  147. (5 rows)
  148.  
  149. test_guru=# SELECT title, body
  150. test_guru-# FROM tests
  151. test_guru-# JOIN questions
  152. test_guru-# ON tests.id = questions.test_id;
  153.      title     |                                                 body                                                  
  154. ---------------+-------------------------------------------------------------------------------------------------------
  155.  HTML          | What does HTML stand for?
  156.  Python        | Which of these is not a Python datatype?
  157.  R             | Which of the following method make a vector of repeated values?
  158.  Ruby Language | What is the name of the element that is used to define what an object looks like after it is created?
  159. (4 rows)
  160.  
  161.  
  162. ______________REMARKS_______________
  163.  
  164. ### FIX column name duplicate for title in categories and assignment request
  165. test_guru=# ALTER TABLE
  166. test_guru-# categories
  167. test_guru-# RENAME COLUMN title to category_title;
  168. ALTER TABLE
  169. test_guru=# SELECT *
  170. test_guru-# FROM categories;
  171.  id |  category_title  
  172. ----+------------------
  173.   1 | Frontend
  174.   2 | Backend
  175.   3 | Machine Learning
  176. (3 rows)
  177.  
  178. test_guru=# SELECT title, category_title
  179. test_guru-# FROM tests
  180. test_guru-# JOIN categories
  181. test_guru-# ON tests.category_id = categories.id;
  182.      title     |  category_title  
  183. ---------------+------------------
  184.  HTML          | Frontend
  185.  Python        | Backend
  186.  R             | Machine Learning
  187.  JavaScript    | Frontend
  188.  Ruby Language | Backend
  189. (5 rows)
  190.  
  191.  
  192. ### FIX FK for category_id
  193. test_guru=# ALTER TABLE tests
  194. test_guru-# ADD FOREIGN KEY (category_id) REFERENCES categories(id);
  195. ALTER TABLE
  196. test_guru=# SELECT *
  197. test_guru-# FROM tests;
  198.  id |     title     | level | category_id
  199. ----+---------------+-------+-------------
  200.   1 | HTML          |     1 |           1
  201.   2 | Python        |     2 |           2
  202.   3 | R             |     2 |           3
  203.   5 | JavaScript    |     3 |           1
  204.   4 | Ruby Language |     3 |           2
  205. (5 rows)
  206.  
  207. test_guru=# ALTER TABLE questions
  208. test_guru-# ADD FOREIGN KEY (test_id) REFERENCES tests(id);
  209. ALTER TABLE
  210. test_guru=# SELECT *
  211. test_guru-# FROM questions;
  212.                                                  body                                                  | test_id
  213. -------------------------------------------------------------------------------------------------------+---------
  214.  Which of these is not a Python datatype?                                                              |       2
  215.  Which of the following method make a vector of repeated values?                                       |       3
  216.  What does HTML stand for?                                                                             |       1
  217.  What is the name of the element that is used to define what an object looks like after it is created? |       4
  218. (4 rows)
  219.  
  220.  
  221. ### FIX selection request
  222. test_guru=# SELECT *
  223. test_guru-# FROM tests
  224. test_guru-# WHERE level = 2 OR level = 3;
  225.  id |     title     | level | category_id
  226. ----+---------------+-------+-------------
  227.   2 | Python        |     2 |           2
  228.   3 | R             |     2 |           3
  229.   5 | JavaScript    |     3 |           1
  230.   4 | Ruby Language |     3 |           2
  231. (4 rows)
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