ER-SQL/SQLHomeWork.md

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

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

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

Сущности

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

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

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

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

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

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

SQL

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

CREATE DATABASE blog;
use blog

Таблица post

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

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

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

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

Задание

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

Таблица comment

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

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

Задание

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

Таблица post_to_tag

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

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. Отсортируйте выборку по дате в обратном порядке (самые свежие посты - вверху).