database basics

** 1. С помощью SQL (DDL):
* Создайте базу данных test_guru
newaz=# CREATE DATABASE test_guru;

* Таблицу categories с атрибутом title
newaz=# CREATE TABLE categories (
newaz(# id serial PRIMARY KEY,
newaz(# title varchar(25) NOT null
newaz(# );

* Таблицу tests в которой должны быть атрибуты title, level, внешний ключ к таблице categories
newaz=# CREATE TABLE tests (
newaz(# id serial PRIMARY KEY,
newaz(# title varchar(50) NOT null,
newaz(# category_id int NOT null,
newaz(# level integer);

* Таблицу questions в которой должен быть атрибут body и внешний ключ к таблице tests
newaz=# CREATE TABLE questions (
id serial PRIMARY KEY,
body varchar,
test_id int NOT null);

** 2. Выполните операции CRUD, JOIN:
* Создайте 3 строки в таблице categories
newaz=# INSERT INTO categories(title) VALUES
newaz-# ('Frontend'),
newaz-# ('Backend'),
newaz-# ('Mobile Development'),
newaz-# ('Machine Learning');

* Создайте 5 строк в таблице tests (хотя бы 3 из них должны иметь отличное от NULL значение в атрибуте внешнего ключа к таблице categories)
newaz=# INSERT INTO tests(title, level, category_id) VALUES
newaz-# ('HTML', 0, 1),
newaz-# ('GO', 2, 2),
newaz-# ('Ruby', 3, 2),
newaz-# ('CSS', 1, 1),
newaz-# ('Javascript', 3, 1);

* Создайте 5 строк в таблице questions
newaz=# INSERT INTO questions(body, test_id) VALUES
newaz-# ('What is tag mean?', 1),
newaz-# ('How much gems you know?', 3),
newaz-# ('How set the color of text?', 4),
newaz-# ('What is a conditional operator?', 5),
newaz-# ('Who is DHH?', 3);

* Выберите все тесты с уровнем 2 и 3
newaz=# SELECT *
FROM tests
WHERE level = 2 OR level = 3;
 id |   title    | category_id | level
----+------------+-------------+-------
  6 | GO         |           2 |     2
  7 | Ruby       |           2 |     3
  9 | Javascript |           1 |     3
(3 rows)

* Выберите все вопросы для определённого теста
newaz=# SELECT *
newaz-# FROM questions
newaz-# WHERE test_id = 3;
 id |          body           | test_id
----+-------------------------+---------
  2 | How much gems you know? |       3
  5 | Who is DHH?             |       3
(2 rows)

* Обновите атрибуты title и level для строки из таблицы tests с помощью одного запроса
newaz=# UPDATE tests
newaz-# SET title = 'CSS/SCSS',
newaz-# level = 2
newaz-# WHERE title = 'CSS';
UPDATE 1

* Удалите все вопросы для конкретного теста с помощью одного запроса
newaz=# DELETE
newaz-# FROM questions
newaz-# WHERE test_id = 4;
DELETE 1

* С помощью JOIN выберите названия всех тестов и названия их категорий
newaz=# SELECT tests.title, categories.title
FROM tests
JOIN categories
ON tests.category_id = categories.id;
   title    |  title
------------+----------
 HTML       | Frontend
 GO         | Backend
 Ruby       | Backend
 Javascript | Frontend
 CSS/SCSS   | Frontend
(5 rows)

* С помощью JOIN выберите содержание всех вопросов (атрибут body) и названия связанных с ними тестов
newaz=# SELECT questions.body, tests.title
FROM questions
JOIN tests
ON questions.test_id = tests.id;
              body               | title
---------------------------------+-------
 What is a conditional operator? | Javascript