---
# System prepended metadata

title: Домашнее задание к занятию "6.2. SQL"

---

# Домашнее задание к занятию "6.2. SQL"

## Задача 1

Используя docker поднимите инстанс PostgreSQL (версию 12) c 2 volume, 
в который будут складываться данные БД и бэкапы.

Приведите получившуюся команду или docker-compose манифест.

### Команда получилась такой.

`docker run --name postgre1 -d -e POSTGRES_HOST_AUTH_METHOD=trust -v 'C:\Users\vah\docker\07-sql:/var/lib/postgresql/data' -v 'C:\Users\vah\docker\07-sql-backup:/tmp/backup' postgres:12`

подключаюсь в ту же систему так:
`docker exec -it postgre1 psql -U postgres -d test_db`

## Задача 2

В БД из задачи 1: 
- создайте пользователя test-admin-user и БД test_db
- в БД test_db создайте таблицу orders и clients (спeцификация таблиц ниже)
- предоставьте привилегии на все операции пользователю test-admin-user на таблицы БД test_db
- создайте пользователя test-simple-user  
- предоставьте пользователю test-simple-user права на SELECT/INSERT/UPDATE/DELETE данных таблиц БД test_db

Таблица orders:
- id (serial primary key)
- наименование (string)
- цена (integer)

Таблица clients:
- id (serial primary key)
- фамилия (string)
- страна проживания (string, index)
- заказ (foreign key orders)

Приведите:
- итоговый список БД после выполнения пунктов выше,
- описание таблиц (describe)
- SQL-запрос для выдачи списка пользователей с правами над таблицами test_db
- список пользователей с правами над таблицами test_db

### Ответ:

Вот как я это делал:
```
CREATE DATABASE test_db;
\c test_db
CREATE USER "test-admin-user";
CREATE TABLE orders (id SERIAL PRIMARY KEY, name VARCHAR, price INT);
CREATE TABLE clients (id SERIAL PRIMARY KEY, fullname VARCHAR, country VARCHAR, zakaz_id SERIAL DROP NOT NULL, FOREIGN KEY (zakaz_id) REFERENCES orders (id));
CREATE INDEX country_idx ON clients (country);
GRANT ALL ON orders, clients TO "test-admin-user";
CREATE USER "test-simple-user";
GRANT SELECT,INSERT,UPDATE,DELETE ON orders, clients to "test-simple-user";
```


Вот список таблиц:
```
test_db=# SELECT table_name FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','pg_catalog');
 table_name
------------
 orders
 clients
(2 rows)
```

Вот список БД с описанием:
```
test_db=# \l+

                                                                   List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   |  Size   | Tablespace |   Description
-----------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |                       | 8113 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +| 7801 kB | pg_default | unmodifiable empty database
           |          |          |            |            | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +| 7801 kB | pg_default | default template for new databases
           |          |          |            |            | postgres=CTc/postgres |         |            |
 test_db   | postgres | UTF8     | en_US.utf8 | en_US.utf8 |                       | 7801 kB | pg_default |
(4 rows)
```

Список таблиц и полей:
```
test_db=# \d+


```

Детальная информация о таблице clients
```
test_db=# \d+ clients
 
 
```

Детальная информация о таблице orders
```
test_db=# \d+ orders

                               List of relations
 Schema |         Name         |   Type   |  Owner   |    Size    | Description
--------+----------------------+----------+----------+------------+-------------
 public | clients              | table    | postgres | 8192 bytes |
 public | clients_id_seq       | sequence | postgres | 8192 bytes |
 public | clients_zakaz_id_seq | sequence | postgres | 8192 bytes |
 public | orders               | table    | postgres | 8192 bytes |
 public | orders_id_seq        | sequence | postgres | 8192 bytes |
(5 rows)
```


Запрос и список пользователей с правами над таблицами test_db

```
SELECT table_name,grantee,privilege_type 
FROM information_schema.table_privileges
WHERE table_schema NOT IN ('information_schema','pg_catalog');

 table_name |     grantee      | privilege_type
------------+------------------+----------------
 orders     | postgres         | INSERT
 orders     | postgres         | SELECT
 orders     | postgres         | UPDATE
 orders     | postgres         | DELETE
 orders     | postgres         | TRUNCATE
 orders     | postgres         | REFERENCES
 orders     | postgres         | TRIGGER
 clients    | postgres         | INSERT
 clients    | postgres         | SELECT
 clients    | postgres         | UPDATE
 clients    | postgres         | DELETE
 clients    | postgres         | TRUNCATE
 clients    | postgres         | REFERENCES
 clients    | postgres         | TRIGGER
 orders     | test-admin-user  | INSERT
 orders     | test-admin-user  | SELECT
 orders     | test-admin-user  | UPDATE
 orders     | test-admin-user  | DELETE
 orders     | test-admin-user  | TRUNCATE
 orders     | test-admin-user  | REFERENCES
 orders     | test-admin-user  | TRIGGER
 clients    | test-admin-user  | INSERT
 clients    | test-admin-user  | SELECT
 clients    | test-admin-user  | UPDATE
 clients    | test-admin-user  | DELETE
 clients    | test-admin-user  | TRUNCATE
 clients    | test-admin-user  | REFERENCES
 clients    | test-admin-user  | TRIGGER
 orders     | test-simple-user | INSERT
 orders     | test-simple-user | SELECT
 orders     | test-simple-user | UPDATE
 orders     | test-simple-user | DELETE
 clients    | test-simple-user | INSERT
 clients    | test-simple-user | SELECT
 clients    | test-simple-user | UPDATE
 clients    | test-simple-user | DELETE
(36 rows)
```

Аналогичный запрос (видимо, в этих таблицах идентичные данные?)

```
test_db=# SELECT table_name,grantee,privilege_type 
FROM information_schema.role_table_grants
WHERE table_schema NOT IN ('information_schema','pg_catalog');
```



## Задача 3

Используя SQL синтаксис - наполните таблицы следующими тестовыми данными:

Таблица orders

|Наименование|цена|
|------------|----|
|Шоколад| 10 |
|Принтер| 3000 |
|Книга| 500 |
|Монитор| 7000|
|Гитара| 4000|

Таблица clients

|ФИО|Страна проживания|
|------------|----|
|Иванов Иван Иванович| USA |
|Петров Петр Петрович| Canada |
|Иоганн Себастьян Бах| Japan |
|Ронни Джеймс Дио| Russia|
|Ritchie Blackmore| Russia|

Используя SQL синтаксис - вычислите количество записей в каждой таблице и 
приведите в ответе запрос и получившийся результат.

### Ответ:

```
INSERT INTO orders (name,price) VALUES
('Шоколад',10),
('Принтер',3000),
('Книга',500),
('Монитор',7000),
('Гитара',4000);
INSERT 0 5
```


Наполнил, сначала пришлось сделать одну колонку NOT NULL
`ALTER TABLE clients ALTER COLUMN zakaz_id DROP NOT NULL;`

```
INSERT INTO clients (fullname,country,zakaz_id) VALUES
('Иванов Иван Иванович','USA',NULL),
('Петров Петр Петрович','Canada',NULL),
('Иоганн Себастьян Бах','Japan',NULL),
('Ронни Джеймс Дио','Russia',NULL),
('Ritchie Blackmore','Russia',NULL);
INSERT 0 5
```

Посчитал:
```
test_db=# select count(*) from clients;
 count
-------
     5
```


## Задача 4

Часть пользователей из таблицы clients решили оформить заказы из таблицы orders.

Используя foreign keys свяжите записи из таблиц, согласно таблице:

|ФИО|Заказ|
|------------|----|
|Иванов Иван Иванович| Книга |
|Петров Петр Петрович| Монитор |
|Иоганн Себастьян Бах| Гитара |

Приведите SQL-запросы для выполнения данных операций.

Приведите SQL-запрос для выдачи всех пользователей, которые совершили заказ, а также вывод данного запроса.
 
### Ответ:
Если я правильно понял, то вот запросы на изменение.

```
UPDATE clients
SET zakaz_id = (SELECT id FROM orders WHERE name = 'Книга')
WHERE fullname = 'Иванов Иван Иванович';

UPDATE clients
SET zakaz_id = (SELECT id FROM orders WHERE name = 'Монитор')
WHERE fullname = 'Петров Петр Петрович';

UPDATE clients
SET zakaz_id = (SELECT id FROM orders WHERE name = 'Гитара')
WHERE fullname = 'Иоганн Себастьян Бах';
```

```
select * from clients;
 id |       fullname       | country | zakaz_id
----+----------------------+---------+----------
  9 | Ронни Джеймс Дио     | Russia  |
 10 | Ritchie Blackmore    | Russia  |
  6 | Иванов Иван Иванович | USA     |        3
  7 | Петров Петр Петрович | Canada  |        4
  8 | Иоганн Себастьян Бах | Japan   |        5
  
select * from orders;
 id |  name   | price
----+---------+-------
  1 | Шоколад |    10
  2 | Принтер |  3000
  3 | Книга   |   500
  4 | Монитор |  7000
  5 | Гитара  |  4000
```
  

## Задача 5

Получите полную информацию по выполнению запроса выдачи всех пользователей из задачи 4 
(используя директиву EXPLAIN).

Приведите получившийся результат и объясните что значат полученные значения.

### Ответ:
```
EXPLAIN select * from clients;
                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on clients  (cost=0.00..18.10 rows=810 width=72)
(1 row)
```

**Seq Scan** — означает, что используется последовательное, блок за блоком, чтение данных таблицы `clients`
**Cost** - некая виртуальная величина призванная оценить затратность операции. Первое значение 0.00 — затраты на получение первой строки. Второе — 18.10 — затраты на получение всех строк.
Единица измерения cost – «извлечение одной страницы в последовательном (sequential) порядке». То есть оценивается и время, и использование ресурсов.

**rows** — приблизительное количество возвращаемых строк при выполнении операции Seq Scan. Это значение возвращает планировщик.

**width** - это оценка PostgreSQL того, сколько, в среднем, байт содержится в одной строке, возвращенной в рамках данной операции

Как я понял, вывод этой информации - ожидания планировщика.
А если дать команду `analyze` и повторить запрос, то количество строк будет более реалистичным, и cost поменяется - потому что БД проведёт анализ.

```
analyze clients;

EXPLAIN select * from clients;
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on clients  (cost=0.00..1.05 rows=5 width=47)
(1 row)
```


## Задача 6

Создайте бэкап БД test_db и поместите его в volume, предназначенный для бэкапов (см. Задачу 1).

Остановите контейнер с PostgreSQL (но не удаляйте volumes).

Поднимите новый пустой контейнер с PostgreSQL.

Восстановите БД test_db в новом контейнере.

Приведите список операций, который вы применяли для бэкапа данных и восстановления. 


Ответ:
Бэкап

Роли
`docker exec -it postgre1 pg_dumpall -U postgres --roles-only -f /tmp/backup/roles.sql`

База
`docker exec -it postgre1 pg_dump -h localhost -U postgres -F t -f /tmp/backup/backup_1.tar test_db`

Восстановление

Запускаем новый контейнер
`docker run --name postgre2 -d -e POSTGRES_HOST_AUTH_METHOD=trust -v 'C:\Users\vah\docker\07-sql-2:/var/lib/postgresql/data' -v 'C:\Users\vah\docker\07-sql-backup:/tmp/backup' postgres:12`

Создаём базу
`docker exec -it postgre2 psql -U postgres -c "CREATE DATABASE test_db WITH ENCODING='UTF-8';"`

Восстанавливаем роли из бэкапа (если нужно)
`docker exec -it postgre2 psql -U postgres -f /tmp/backup/roles.sql`

Восстанавливаем базу из бэкапа
`docker exec -it postgre2 pg_restore -U postgres -Ft -v -d test_db /tmp/backup/backup_1.tar`


