PHP/SQLHomeWork.md

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

Данный практикум предназнчен для усвоения 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_create_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 или WHERE из таблицы постов и комментов, используя USING(post_id) или ON (post.post_id = comment.post_id) Отметьте для себя отсутствие поста без комментов в выборке.
   • Запрос, используя LEFT JOIN, который выведет все посты, включая тот, у которого нет комментов.
   • Сделайте запрос, который выбирает посты, их комменты и их подкомментарии, используя AS и comment_parent_id
3. Сделайте запросы выборки и группировки:
   • Выведите все посты и количество комментариев к ним, используя GROUP BY и count(comment.comment_id).
   • Выведите все посты, самый старый и самый свежий комментарий, используя агрегирующие функции MAX и MIN по полю comment_datetime

Таблица tag