# Домашнее задание по SQL.

Данный практикум предназнaчен для усвоения ER, SQL, запросов в области Web.

## Предметная область и связи.

### Сущности

Рассмотрим обычный блог. В блоге есть:

- Посты.
- Комменты к постам.
- Тэги постов.

Это основные сущности, каждой из которых будет соответствовать таблица в **mysql**.

### Отношения и связи

- К *каждому* посту может быть несколько комментов. А значит, это связь **один-ко-многим** (к одному посту - несколько комментов)
- Комментарии могут быть в форме дерева, т. е. у каждого комментария может быть родитель (комментарий, на который ответили) - опять же связь **один-ко-многим**.
- У *каждого* поста может быть несколько тэгов; у *каждого* тэга - несколько постов (связь **многие-ко-многим** через смежную таблицу).

Таким образом к трем таблицам сущностей добавляется четвертая - смежная таблица **тэги-к-постам**

## SQL

Опишем структуру таблиц:

```mysql
CREATE DATABASE blog;
use blog
```

### Таблица `post`

```mysql
CREATE table post (
    post_id SERIAL,
    post_title VARCHAR(255),
    post_text  TEXT,
    post_create_datetime DATETIME,
    post_update_datetime TIMESTAMP
);
```

Вставим тестовые данные в таблицу:

```mysql
INSERT INTO post SET 
    post_title = 'subject', 
    post_text = "text body",
    post_create_datetime = NOW();
```

поля `post_id` и `post_update_datetime` заполнятся автоматически;

#### Задание

Добавьте несколько записей с осмысленными названиями и текстом.

### Таблица `comment`

```mysql
CREATE TABLE `comment` (
     `comment_id` SERIAL,
     `post_id` bigint(20) DEFAULT NULL,
     `comment_parent_id` bigint(20) DEFAULT NULL,
     `comment_username` varchar(255) DEFAULT NULL,
     `comment_text` text,
     `comment_datetime` TIMESTAMP
);
```

#### Задание
1. Добавьте несколько комментариев к вашим постам, установите связь (занесите в `comment.post_id` `post_id` из тех или иных постов). Так же добавьте
   ответные комментарии к комментариям, т. е. задайте `comment_parent_id`. Оставьте один пост *без* комментариев для понимания различий между 
   `INNER JOIN` и `LEFT JOIN`
2. Сделайте запросы выборки:
   - Всех комментов к посту с `post_id` = 2
   - Запрос, используя `INNER JOIN` (используя `USING(post_id)` или `ON (post.post_id = comment.post_id)`) или `WHERE` из таблицы постов и комментов, 
      Отметьте для себя отсутствие поста без комментов в выборке.
   - Запрос, используя `LEFT JOIN`, который выведет все посты, и комменты к ним, включая тот, у которого нет комментов.
   - Сделайте запрос, который выбирает посты, их комменты и их подкомментарии, используя `AS` и `comment_parent_id`
3. Сделайте запросы выборки и группировки:
   - Выведите все посты и количество комментариев к ним, используя `GROUP BY` и `count(comment.comment_id)`.
   - Выведите все посты, самый старый и самый свежий комментарий, используя агрегирующие функции `MAX` и `MIN` по полю `comment_datetime`

### Таблица `tag`

```mysql
CREATE TABLE `tag` (
    tag_id SERIAL,
    tag_title VARCHAR(128)
)
```

#### Задание

Добавьте разных тэгов.

### Таблица `post_to_tag`
Эта таблица предназначена для организации связи тэга и поста:

```mysql
CREATE TABLE post_to_tag(
    post_to_tag_id SERIAL,
    post_id BIGINT,
    tag_id BIGINT
);
```

#### Задание
1. Добавьте записи (связи) используя существующие `post_id` и `tag_id`
2. Выведите все тэги каждого поста используя `LEFT JOIN` от таблицы `post` к таблице `tag` через смежную таблицу `post_to_tag`
3. Используя `GROUP BY` подсчитайте количество тэгов у всех постов в колонке под именем `tagsCount`. Используйте `AS` для задания псевдонимов.
4. Используя `GROUP BY` выведите все тэги для каждого поста в поле `tagsList`. Используйте `GROUP_CONCAT` для объединения тэгов в одну строку.
5. Сделайте поиск постов по тэгу, используя `WHERE` и какой-то `tag_title`. Отсортируйте выборку по дате в обратном порядке (самые свежие посты - вверху).