SHARE
TWEET

Untitled

a guest Dec 12th, 2018 65 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. psql
  2.  
  3. qq=# CREATE DATABASE test_guru;
  4.  
  5.                                 ----- CREATE TABLE -----
  6.  
  7. qq=# CREATE TABLE categories (
  8. qq(# id serial PRIMARY KEY,
  9. qq(# title text NOT null
  10. qq(# );
  11.  
  12. qq=# CREATE TABLE tests (
  13. qq(# id serial PRIMARY KEY,
  14. qq(# title text NOT null,
  15. qq(# level int NOT null,
  16. qq(# category_id int NOT null
  17. qq(# );
  18.  
  19. qq=# CREATE TABLE questions (
  20. qq(# id serial PRIMARY KEY,
  21. qq(# body text NOT null,
  22. qq(# test_id int NOT null
  23. qq(# );
  24.  
  25.                                 ----- INSERT -----
  26.  
  27. qq=# INSERT INTO categories(title) VALUES
  28. qq-# ('android'),
  29. qq-# ('linux'),
  30. qq-# ('macos');
  31.  
  32. qq=# INSERT INTO tests(title, level, category_id) VALUES
  33. qq-# ('Xcode', 1, 3),
  34. qq-# ('App Store', 2, 3),
  35. qq-# ('Google Play', 3, 1),
  36. qq-# ('Центр приложений', 1, 2),
  37. qq-# ('Kotlin', 3, 1);
  38.  
  39. qq=# INSERT INTO questions(body, test_id) VALUES
  40. ('Язык программирования?', 5),
  41. ('Магазин приложений?', 2),
  42. ('Java совместимый язык?', 5),
  43. ('IDE работает только под macos?', 1),
  44. ('Какой-то вопрос', 3);
  45.  
  46.                                 ----- SELECT -----
  47.  
  48. qq=# SELECT *
  49. qq-# FROM tests
  50. qq-# WHERE level IN (2,3);
  51.  
  52. qq=# SELECT *
  53. qq-# FROM questions
  54. qq-# WHERE test_id = 5;
  55.  
  56.                                 ----- UPDATE -----
  57.  
  58. qq=# UPDATE tests
  59. qq-# SET title = 'Software center', level = 3
  60. qq-# WHERE title = 'Центр приложений';
  61.  
  62.                                 ----- DELETE -----
  63.  
  64. qq=# DELETE
  65. qq-# FROM questions
  66. qq-# WHERE test_id = 1;
  67.  
  68.                                 ----- JOIN -----
  69.  
  70. qq=# SELECT tests.title AS tests, categories.title AS categories
  71. qq-# FROM tests
  72. qq-# JOIN categories
  73. qq-# ON tests.category_id = categories.id;
  74.  
  75. qq=# SELECT questions.body, tests.title
  76. qq-# FROM questions
  77. qq-# JOIN tests
  78. qq-# ON questions.test_id = tests.id;
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