Разбираем базы данных и язык SQL. (Часть 6 — Проверка финального задания) - "Java-проект от А до Я"

Статья из серии о создании Java-проекта (ссылки на другие материалы — в конце). Ее цель — разбор ключевых технологий, итог — написание телеграм-бота. В этой части — разбор финального задания по БД.Приветствую вас, дорогие читатели. Сегодня будем разбирать по полочкам задание из последней статьи по БД. Оно интересно тем, что предназначено для собеседования на уровень Middle. То есть после этого задания уже можно идти на собеседование, и как минимум часть из того, что относится к реляционным БД, вы успешно пройдете. Я знаю, насколько может быть нужной эта статья, и поэтому вложу весь свой опыт, чтобы сделать ее полезной и интересной. И если вы не уснете на середине статьи, то это будет означать, что цель свою я достиг. Не буду повторять полностью задание: буду цитировать его перед выполнением каждой задачи, подчеркивая ее курсивом. Я ожидаю, что все, кто читает эту статью, пройдет все запросы у себя в БД и получит то же самое. Это принесет максимальную пользу для дела. А я буду немного счастливее от мысли, что помог кому-то в нашем нелегком деле)

Задание 1

Написать SQL script создания таблицы ‘Student’ с полями: id (primary key), name, last_name, e_mail (unique). Такое мы уже делали, так что проблем не должно быть. В скрипте нужно указать первичный ключ и уникальное поле, которое отличается от первичного. Для начала создадим новую БД для такого дела: $ CREATE DATABASE final_task; И воспользуемся этой БД: $ USE final_task; Когда среда настроена и подготовлена для выполнения задания, можем записать следующий скрипт: $ CREATE TABLE student ( id INT AUTO_INCREMENT, name VARCHAR(40), last_name VARCHAR(50), email VARCHAR(100), PRIMARY KEY (id), UNIQUE (email) ); Пока что ничего нового по сравнению с тем, что мы уже проходили, нет. Какие-то комментарии излишни, поехали дальше.

Задание 2-3

Написать SQL script создания таблицы ‘Book’ с полями: id, title (id + title = primary key). Связать ‘Student’ и ‘Book’ связью ‘Student’ one-to-many ‘Book’. Совместим две задачи в одну, чтобы было быстрее и удобнее. Как добавить отдельно внешний ключ, я уже говорил в предыдущих статьях. Чтобы добавить, нужно вспомнить, как мы делаем связи и за счет чего. Предыдущая статья вам в помощь, а потом вот скрипт: $ CREATE TABLE book ( id INT, title VARCHAR(100), student_id INT DEFAULT NULL, PRIMARY KEY (id, title), FOREIGN KEY (student_id) REFERENCES student (id) ); Вот таким несложным образом мы добавили составной ключ для нашей таблицы PRIMARY KEY (id, title), теперь ключом будет именно пара. Это значит, что может быть больше одного одинакового значения поля id в таблице. И точно так же и для title.

Задание 4

Написать SQL script создания таблицы ‘Teacher’ с полями: id (primary key), name, last_name, e_mail (unique), subject. Продолжаем подготавливать нашу БД для запросов, создаем таблицу учителя: $ CREATE TABLE teacher( id INT AUTO_INCREMENT, name VARCHAR(30), last_name VARCHAR(30), email VARCHAR(100), subject VARCHAR(40), PRIMARY KEY (id), UNIQUE (email) ); Пока что несложно, да? Три задания уже позади!

Задание 5

Связать ‘Student’ и ‘Teacher’ связью ‘Student’ many-to-many Teacher’. Вот это уже интереснее! Как раз говорили об этом в прошлый раз. Напомню, что необходимо, чтобы этого достичь: нужно создать промежуточную таблицу, которая хранила бы пары “студент-учитель”. При ее помощи можно будет создать связь многие-ко-многим. Поэтому создадим таблицу students_x_techers. Подход к именованию открыт и может быть еще и таким: student_teacher. $ CREATE TABLE students_x_teachers ( student_id INT NOT NULL, teacher_id INT NOT NULL, PRIMARY KEY (student_id, teacher_id), FOREIGN KEY (student_id) REFERENCES student(id), FOREIGN KEY (teacher_id) REFERENCES teacher(id) ); Как видно, все сделано четко и связно. У нас составной ключ для двух внешних ключей: для student_id и teacher_id. Зачем еще и внешний ключ? Для того, чтобы мы были уверены, что записи в таблицах студент и учитель существуют для записываемых пар.

Задание 6

Выбрать ‘Student’, у которых в фамилии есть ‘oro’, например, ‘Sidorov’, ‘Voronovsky’. Чтобы было нам интересно и наглядно, я предлагаю вначале добавить нескольких студентов, чтобы часть из них подходила для этого запроса, а часть — нет. Поэтому запишем тех, кто должен попасть в результате запроса: $ INSERT INTO student (name, last_name, email) VALUES ('Ivan', 'Sidorov', 'ivan.sidorov@gmail.com'); $ INSERT INTO student (name, last_name, email) VALUES ('Nikolay', 'Voronovsky', 'nikolay.voronovsky@gmail.com'); И тех, кто попасть не должен: $ INSERT INTO student (name, last_name, email) VALUES ('Roman', 'Fortny', 'roman.fortny@gmail.com'); $ INSERT INTO student (name, last_name, email) VALUES(‘Kostya’, ‘Petrov’, ‘kostya.petrov@gmail.com’); Проверим результат, посмотрим на список данных в таблице студентов: $ SELECT * FROM student; и получим:Всего четыре записи, две из них должны подойти, а две — нет. Подготовив все данные к запросу, можем сделать запрос по самому заданию: $ SELECT * FROM student WHERE last_name LIKE ‘%oro%’;В итоге Иван и Николай прошли по списку.

Задание 7

Следующее задание, читаем: Выбрать из таблицы ‘Student’ все фамилии (‘last_name’) и количество их повторений. Считать, что в базе есть однофамильцы. Отсортировать по количеству в порядке убывания. Выглядеть должно так:

last_name	quantity
Petrov	15
Ivanov	12
Sidorov	3

Для наглядности нужно добавить еще данных. Не мудрствуя лукаво, добавим Петровых, Ивановых и Сидоровых, родства не знающих ;) Почту не буду придумывать, просто исключим ее в новых записях. Выполним 12 раз следующую команду: $ INSERT INTO student (name, last_name) VALUES ('Ivan', 'Ivanov'); Добавим 15 Петровых: $ INSERT INTO student (name, last_name) VALUES ('Petr', 'Petrov'); И двоих Сидоровых (один же уже есть у нас))): $ INSERT INTO student (name, last_name) VALUES ('Sidor', 'Sidorov'); Теперь данные готовы. Чтобы получить такие данные, нужно сделать группировку, чтобы сделать группировку, нужно воспользоваться оператором Group By, и делать это нужно по полю last_name. Также можно заметить, что количество повторений обозначено как quantity, и здесь тоже нужно вспомнить, как делать алиасы в SQL: $ SELECT last_name, COUNT(*) as quantity FROM student GROUP BY last_name ORDER BY COUNT(*) DESC;Таки я перестарался с Петровыми — получилось 16))

Задание 8

Условие: Выбрать из ‘Student’ топ 3 самых повторяющихся имен ‘name’. Отсортировать по количеству в порядке убывания. Выглядеть должно так:

name	quantity
Alexander	27
Sergey	10
Peter	7

О, для этого дела у нас уже есть Иваны, Петры и Сидоры. Поэтому добавлять их не нужно. Сортировать мы уже тоже умеем. Единственное, о чем сегодня мы не говорили — как выбрать определенное количество записей. Это мелькало уже в предыдущих решениях задач по БД. Кто не читал — прочитайте. Остальным — сразу к делу: $ SELECT name, COUNT(*) as quantity FROM student GROUP BY name ORDER BY COUNT(*) DESC LIMIT 3;Как видно из запроса, если знать очередность операторов в запросе SELECT, никаких проблем с выполнением такого запроса не будет. И это задание все еще нам по зубам. И тех знаний, которые были изложены прежде, вполне достаточно для решения этой задачи.

Задание 9

Условие задания: Выбрать ‘Student’ у которых самое большое количество ‘Book’ и связанных с ним ‘Teacher’. Отсортировать по количеству в порядке убывания. Выглядеть должно так:

Teacher’s last_name	Student’s last_name	Book’s quantity
Petrov	Sidorov	7
Ivanov	Smith	5
Petrov	Kankava	2

Так, это задание уже явно сложнее предыдущего, да? Не мудрено: здесь пахнет джоином… и даже не одним) Вначале нам нужно понять, что делать… Видно, что Book’s quantity требует группировку. Но чего? И по чему группировать? В запросе фигурируют три таблицы, группировка, сортировка. Судя по тому, что не показаны записи, где нет книг, имеется в виду, что нужно брать именно INNER JOIN. Для LEFT JOIN тоже сделаем запрос, чтобы не было с этим проблем. Причем есть несколько вариантов. Первое, что делаем — соединяем три таблицы в одну запись. Далее группируем по студенту и присоединяем к нему имя учителя. Что будем выбирать? Фамилию учителя, студента и количество книг. Добавим данные для запроса:

• трех учителей;
• десять книг;
• связать двух студентов с тремя учителями.

Три учителя

$ INSERT INTO teacher(last_name) VALUES ('Matvienko'); $ INSERT INTO teacher(last_name) VALUES ('Shevchenko'); $ INSERT INTO teacher(last_name) VALUES ('Vasilenko');

10 книг

Возьму айдишники 1 и 2 студентов. К ним привяжу книги. Так как не поставили AUTO_INCREMENT, то чтобы не писать каждый раз новый ID, нужно сделать следующее: $ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCREMENT; Далее добавим книги для первого студента: $ INSERT INTO book (title, student_id) VALUES('book1', 1); $ INSERT INTO book (title, student_id) VALUES('book2', 1); $ INSERT INTO book (title, student_id) VALUES('book3', 1); $ INSERT INTO book (title, student_id) VALUES('book4', 1); $ INSERT INTO book (title, student_id) VALUES('book5', 1); $ INSERT INTO book (title, student_id) VALUES('book6', 1); И книги для второго студента: $ INSERT INTO book (title, student_id) VALUES('book7', 2); $ INSERT INTO book (title, student_id) VALUES('book8', 2); $ INSERT INTO book (title, student_id) VALUES('book9', 2); $ INSERT INTO book (title, student_id) VALUES('book10', 2);

Связи учитель-студент

Для этого дела добавим в таблицу students_x_teachers: $ INSERT INTO students_x_teachers VALUES (1,1); $ INSERT INTO students_x_teachers VALUES (1,2); $ INSERT INTO students_x_teachers VALUES (2,3);

Реализуем запрос

Делаем первый этап — связываем три таблицы в одну запись: $ SELECT * FROM teacher tch INNER JOIN students_x_teachers st_x_tch ON tch.id = st_x_tch.teacher_id INNER JOIN student st ON st_x_tch.student_id = st.id INNER JOIN book b ON st.id = b.student_id;Записей, конечно, у нас еще нет, тем не менее, мы можем увидеть, что у нас успешно получилось соединить три таблицы. Теперь добавляем группировку книг, сортировку и те поля, которые нам нужны: $ SELECT tch.last_name, st.last_name, st.id, COUNT(*) as books FROM student st INNER JOIN book b ON st.id = b.student_id INNER JOIN students_x_teachers st_x_tch ON st.id = st_x_tch.student_id INNER JOIN teacher tch ON tch.id = st_x_tch.teacher_id GROUP BY st.id ORDER BY books DESC;Но получаем ошибку в SQL и такой ответ: Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'final_task.tch.last_name' which is not functionally dependent on columns in GROUP BY clause Не получается взять эти элементы потому, что есть связь многие-ко-многим между учителем и студентом. И правда: мы не можем получить только одного учителя к студенту. Поэтому пойдем другим путем. Воспользуемся такой штукой, как View SQL. Идея какая: мы создаем отдельно вью, которое представляет собой новую таблицу, уже с нужной нам группировкой. И уже к этой таблице мы добавим нужные фамилии учителей. Но учитываем тот факт, что может быть больше одного учителя, поэтому записи будут повторяться. Создаем вью: $ CREATE VIEW studentBooks as SELECT st.last_name,st.id,COUNT(*) as books FROM student st INNER JOIN book b ON st.id=b.student_id GROUP BY st.id ORDER BY books DESC; Далее работаем с этим вью как с простой таблицей, у которой есть три поля: student last_name, student_id и books count. По айдишнику студента можем добавить и учителя через два джоина: $ SELECT tch.last_name as 'Teacher', sbw.last_name 'Student', sbw.books as 'Books' from studentbook sbw INNER JOIN students_x_teachers stch ON sbw.id = stch.student_id INNER JOIN teacher tch ON tch.id = stch.teacher_id; И вот теперь будет результат:Хух! Вот это запросик, да?) Вышло, как и предполагали: у студента с id=1 шесть книг и два учителя, а у студента с id=2 — четыре книги и один учитель.

Задание 10

Условие: Выбрать ‘Teacher’, у которых самое большое количество ‘Book’ у всех его ‘Student’. Отсортировать по количеству в порядке убывания. Выглядеть должно так:

Teacher’s last_name	Book’s quantity
Petrov	9
Ivanov	5

Здесь нам можно воспользоваться уже готовым запросом из предыдущего задания. Что нам нужно в нем изменить? У нас уже есть эти данные, только нужно добавить еще одну группировку и убрать из данных на вывод имя студента. Но для начала добавим еще одного студента к учителю, чтобы результат был интереснее. Для этого напишем: $ INSERT INTO students_x_teachers VALUES (2, 1); И сам запрос: $ SELECT tch.last_name as 'Teacher', SUM(sbw.books) as 'Books' from studentbook sbw INNER JOIN students_x_teachers stch ON sbw.id = stch.student_id INNER JOIN teacher tch ON tch.id = stch.teacher_id GROUP BY tch.id; В результате получим:что у учителя Василенко 10 книг, а у Шевченко — 6…)

Задание 11

Условие: Выбрать ‘Teacher’, у которых количество ‘Book’ у всех его ‘Student’ находится между 7-ю и 11-и. Отсортировать по количеству в порядке убывания. Выглядеть должно так:

Teacher’s last_name	Book’s quantity
Petrov	11
Sidorov	9
Ivanov	7

Вот здесь-то мы и будем использовать HAVING. Мы говорили о нем. Запрос будет ровно такой же, как и до этого, только нужно еще добавить условие, что количество книг будет в определенном диапазоне. А как я уже говорил в предыдущих статьях, когда нам нужно сделать фильтрацию во время группировки и/или над агрегирующими функциями, нужно использовать HAVING: $ SELECT tch.last_name as 'Teacher', SUM(sbw.books) as 'Books' from studentbook sbw INNER JOIN students_x_teachers stch ON sbw.id = stch.student_id INNER JOIN teacher tch ON tch.id = stch.teacher_id GROUP BY tch.id HAVING SUM(sbw.books) > 6 AND SUM(sbw.books) < 12; Я выделил часть, которую добавил. И, собственно, ожидаемый результат:Только Василенко прошел(шла) этот тур))

Задание 12

Условие: Вывести всех ‘last_name’ и ‘name’ всех ‘Teacher’ и ‘Student’ с полем ‘type‘ (student или teacher). Отсортировать в алфавитном порядке по ‘last_name’. Выглядеть должно так:

last_name	type
Ivanov	student
Kankava	teacher
Smith	student
Sidorov	teacher
Petrov	teacher

То есть нужно нам объединить два вывода, и для этого как раз есть UNION. Иными словами, мы возьмем записи от студентов и от учителей и выведем вместе: $ SELECT last_name, 'teacher' as type from teacher UNION ALL select last_name, 'student' as type from student ORDER BY last_name;И там будут и учителя, и студенты. Казалось бы все просто, но это когда уже смотрим на результат. А так нужно догадаться до двух вещей.

Задание 13

Условие: Добавить к существующей таблице ‘Student’ колонку ‘rate’, в которой будет храниться курс, на котором студент сейчас находится (числовое значение от 1 до 6). ALTER TABLE student ADD CONSTRAINT check_rate CHECK (rate > 0 AND rate < 7); Здесь мы делаем добавление поля через ALTER TABLE и CHECK, чтобы установить ограничение на это поле с 1 до 6.

Задание 14

Условие: Этот пункт не обязателен к выполнению, но будет плюсом. Написать функцию, которая пройдется по всем ‘Book’ и выведет через запятую все ‘title’. Здесь нужно просто выдать в результате запроса строку, в которой будут все title книг. Здесь опять нужно было гуглить. Есть такая функция — GROUP_CONCAT, при помощи которой это делается очень просто: $ SELECT GROUP_CONCAT(title) from book;И все…)) Все 14 заданий готовы.

Выводы

Ууххх… Это было непросто. Это было интересно. Задания того стоили, я более чем уверен. Пока делали эти задачи, прошлись по многим вещам, которые ранее не были известны:

• SQL VIEW
• GROUP_CONCAT
• UNION

и так далее. Всем спасибо, у кого хватило сил прочитать и повторить то, что я сделал. Кто знает, как сделать запросы лучше — пишите в комментариях, я обязательно прочитаю их)

Список всех материалов серии в начале этой статьи.