Thinknetica (SQL)

gistfile1.txt

1. С помощью SQL (DDL):
1.1 Создайте базу данных test_guru (PostgreSQL)

postgres=# CREATE DATABASE test_guru;
=> CREATE DATABASE

postgres=# \c test_guru;
You are now connected to database "test_guru" as user "secretpray".
test_guru=# \l
                                   List of databases
    Name    |   Owner    | Encoding |   Collate   |    Ctype    |   Access privileges   
------------+------------+----------+-------------+-------------+-----------------------
....
 test_guru  | secretpray | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)
 

1.2 Таблицу categories с атрибутом title
postgres=#  

CREATE TABLE categories (
  id serial PRIMARY KEY,
  title varchar(50)
);
=> CREATE TABLE

postgres=# \dt
            List of relations
 Schema |    Name    | Type  |   Owner    
--------+------------+-------+------------
 public | categories | table | secretpray
(1 row)

1.3 Таблицу tests в которой должны быть атрибуты title, level, внешний ключ к таблице categories

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

postgres=# \dt
            List of relations
 Schema |    Name    | Type  |   Owner    
--------+------------+-------+------------
 public | categories | table | secretpray
 public | tests      | table | secretpray
(2 rows)

1.4 Таблицу questions в которой должен быть атрибут body и внешний ключ к таблице tests

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

postgres=# \dt
            List of relations
 Schema |    Name    | Type  |   Owner    
--------+------------+-------+------------
 public | categories | table | secretpray
 public | questions  | table | secretpray
 public | tests      | table | secretpray
(3 rows)


2. Выполните операции CRUD, JOIN:
2.1 Создайте 3 строки в таблице categories

postgres=# 
INSERT INTO categories(title) VALUES
 ('frontend'),
 ('Backend'),
 ('Machine learning');
INSERT 0 3

UPDATE categories
SET title = 'Frontend'
WHERE id = 1;

postgres=# SELECT * FROM categories;                                                                                                id |      title       
----+------------------
  1 | Frontend
  2 | Backend
  3 | Machine learning
(3 rows)

2.2 Создайте 5 строк в таблице tests (хотя бы 3 из них должны иметь отличное от NULL значение в атрибуте внешнего ключа к таблице categories)

INSERT INTO tests(category_id, title, level) VALUES
  (1, 'HTML', 0),
  (1, 'CSS', 0), 
  (1, 'CSS', 0),
  (2, '', 1),
  (3, 'Decision trees', 2);
=> INSERT 0 5

SELECT * FROM tests;
 id | category_id |     title      | level 
----+-------------+----------------+-------
  6 |           1 | HTML           |     0
  7 |           1 | CSS            |     0
  8 |           2 | HTTP           |     1
  9 |           2 |                |     1
 10 |           3 | Decision trees |     2
(5 rows)

PS Ошибся: сделал 4 ненулевых. Исправим)

UPDATE tests
SET title = ''
WHERE id = 8;
=> UPDATE 1

SELECT * FROM tests;
 id | category_id |     title      | level 
----+-------------+----------------+-------
  6 |           1 | HTML           |     0
  7 |           1 | CSS            |     0
  9 |           2 |                |     1
 10 |           3 | Decision trees |     2
  8 |           2 |                |     1
(5 rows)


 2.3 Создайте 5 строк в таблице questions
INSERT INTO questions(body, test_id) VALUES 
  ('question about HTML', 6), 
  ('question about CSS', 7), 
  ('question about HTTP', 6), 
  ('question about JavaScript', 7),
  ('question about Decision trees', 10);
=> INSERT 0 5

SELECT * FROM questions;

 id |             body              | test_id 
----+-------------------------------+---------
  6 | question about HTML           |       6
  7 | question about CSS            |       7
  8 | question about HTTP           |       6
  9 | question about JavaScript     |       7
 10 | question about Decision trees |      10
(5 rows)

2.4 Выберите все тесты с уровнем 2 и 3
Хм, у меня 0-2. Снова обновим)))
UPDATE tests
SET level = 3
WHERE id = 10;

UPDATE tests
SET level = 2
WHERE id in (9,8);

UPDATE tests
SET level = 1
WHERE id in (6,7);

SELECT * FROM tests;
id | category_id |     title      | level 
----+-------------+----------------+-------
 10 |           3 | Decision trees |     3
  8 |           2 |                |     2
  9 |           2 |                |     2
  6 |           1 | HTML           |     1
  7 |           1 | CSS            |     1
(5 rows)

Теперь выполним задание:
------------------------
SELECT * FROM tests WHERE level in (2,3);
 id | category_id |     title      | level 
----+-------------+----------------+-------
 10 |           3 | Decision trees |     3
  8 |           2 |                |     2
  9 |           2 |                |     2
(3 rows)

2.5 Выберите все вопросы для определённого теста
Для информации посмотрим что у нас есть в таблице....
SELECT * FROM tests;
 id | category_id |     title      | level 
----+-------------+----------------+-------
 10 |           3 | Decision trees |     3
  8 |           2 |                |     2
  9 |           2 |                |     2
  6 |           1 | HTML           |     1
  7 |           1 | CSS            |     1
(5 rows)

Теперь выполним задание (выберем в test_id = 8:):
------------------------------------------------
SELECT *
FROM questions
WHERE test_id = 8;
 id | body | test_id 
----+------+---------
(0 rows)

2.6 Обновите атрибуты title и level для строки из таблицы tests с помощью одного запроса
UPDATE tests   
SET title = 'PHP', 
  level = 1 
WHERE id = 8;
=> UPDATE 1

postgres=# SELECT * FROM tests;
id | category_id |     title      | level 
----+-------------+----------------+-------
 10 |           3 | Decision trees |     3
  9 |           2 |                |     2
  6 |           1 | HTML           |     1
  7 |           1 | CSS            |     1
  8 |           2 | PHP            |     1
(5 rows)

2.7 Удалите все вопросы для конкретного теста с помощью одного запроса
Осмотримся:
postgres=# SELECT * FROM questions;
 id |             body              | test_id 
----+-------------------------------+---------
  6 | question about HTML           |       6
  7 | question about CSS            |       7
  8 | question about HTTP           |       6
  9 | question about JavaScript     |       7
 10 | question about Decision trees |      10
(5 rows)

Теперь выполним задание:
------------------------
DELETE
FROM questions
WHERE id = 9;
=> DELETE 1

postgres=# SELECT * FROM questions;                                                                                                 id |             body              | test_id 
----+-------------------------------+---------
  6 | question about HTML           |       6
  7 | question about CSS            |       7
  8 | question about HTTP           |       6
 10 | question about Decision trees |      10
(4 rows)

2.8 С помощью JOIN выберите названия всех тестов и названия их категорий
SELECT tests.title, categories.title
FROM tests
JOIN categories
ON tests.category_id = categories.id;

     title      |      title       
----------------+------------------
 Decision trees | Machine learning
                | Backend
 HTML           | Frontend
 CSS            | Frontend
 PHP            | Backend
(5 rows)

Не понятно где чьё))) Так не пойдет. Сделаем через Allias
Красивое решение задания:
------------------------
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  
----------------+------------------
 Decision trees | Machine learning
                | Backend
 HTML           | Frontend
 CSS            | Frontend
 PHP            | Backend
(5 rows)

2.9 С помощью JOIN выберите содержание всех вопросов (атрибут body) и названия связанных с ними тестов

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

             body              |     title      
-------------------------------+----------------
 question about Decision trees | Decision trees
 question about HTTP           | HTML
 question about HTML           | HTML
 question about CSS            | CSS
(4 rows)