Задание по основам баз данных и SQL

3_datebases_essentials_and_SQL.txt

1. С помощью SQL (DDL):

> CREATE DATABASE test_guru;

CREATE DATABASE

> \c test_guru

Вы подключены к базе данных "test_guru" как пользователь "postgres".

> CREATE TABLE categories(
 id serial PRIMARY KEY,
 title varchar(25) NOT NULL
 );

> CREATE TABLE tests(
 id serial PRIMARY KEY,
 title varchar(50),
 level int NOT NULL,
 category_id int NOT NULL,
 FOREIGN KEY (category_id) REFERENCES categories (id)
 );

> CREATE TABLE questions(
 id serial PRIMARY KEY,
 body varchar(200) NOT NULL,
 test_id int NOT NULL,
 FOREIGN KEY (test_id) REFERENCES tests (id)
 );

> \d

 public | categories        | таблица            | postgres
 public | categories_id_seq | последовательность | postgres
 public | questions         | таблица            | postgres
 public | questions_id_seq  | последовательность | postgres
 public | tests             | таблица            | postgres
 public | tests_id_seq      | последовательность | postgres



2. Выполните операции CRUD, JOIN:

> INSERT INTO categories(title) VALUES
 ('Frontend'),
 ('Backend'),
 ('UX/UI');

> INSERT INTO tests(title, level, category_id) VALUES
 ('HTML', 1, 1),
 ('CSS', 2, 1),
 ('Ruby', 3, 2),
 ('JavaScript', 4, 2),
 ('Interface', 5, 3);

> INSERT INTO questions(body, test_id) VALUES
 ('Is it Frontend?', 1),
 ('Is it Backend?', 3),
 ('Does it related to design?', 5),
 ('Is it functional language?', 4),
 ('Could it do migrations?', 3);

> SELECT *
 FROM tests
 WHERE level IN (2, 3);
 
 id | title | level | category_id 
----+-------+-------+-------------
  2 | CSS   |     2 |           1
  3 | Ruby  |     3 |           2
(2 строки)

> SELECT *
 FROM questions
 WHERE test_id = 3;

 id |          body           | test_id 
----+-------------------------+---------
  2 | Is it Backend?          |       3
  5 | Could it do migrations? |       3
(2 строки)

> UPDATE tests
 SET title = 'Ruby <3',
 level = 7
 WHERE title = 'Ruby';

> DELETE
 FROM questions
 WHERE test_id = 5;

> SELECT tests.title AS test_title,
 categories.title AS category_title
 FROM tests
 JOIN categories
 ON tests.category_id = categories.id;

 test_title | category_title 
------------+----------------
 HTML       | Frontend
 CSS        | Frontend
 JavaScript | Backend
 Interface  | UX/UI
 Ruby <3    | Backend
(5 строк)

> SELECT questions.body, tests.title
 FROM questions
 JOIN tests
 ON questions.test_id = tests.id;

            body            |   title    
----------------------------+------------
 Is it Frontend?            | HTML
 Is it functional language? | JavaScript
 Could it do migrations?    | Ruby <3
 Is it Backend?             | Ruby <3
(4 строки)