Advertisement
Guest User

Untitled

a guest
Jul 23rd, 2019
83
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.54 KB | None | 0 0
  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)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement