Розбираємо бази даних та мову 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 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 (tea Як видно, все зроблено чітко та складно. У нас складовий ключ для двох зовнішніх ключів: 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', Перевіримо результат, подивимося на список даних у таблиці студентів: $SELECT * FROM student; і отримаємо: Всього чотири записи, два з них повинні підійти, а два – ні. Підготувавши всі дані до запиту, можемо зробити запит за завданням: $ SELECT * FROM student WHERE last_name LIKE '%oro%'; У результаті Іван та Микола пройшли за списком.

Завдання 7

Наступне завдання читаємо: Вибрати з таблиці 'Student' всі прізвища ('last_name') та кількість їх повторень. Вважати, що у базі є однофамільці. Відсортувати за кількістю у порядку зменшення. Виглядати має так:

last_name	quantity
Петроов	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
Олександр	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
Петроов	Sidorov	7
Ivanov	Smith	5
Петроов	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 Далі додамо книги для першого студента: $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 b.student_id; Записів, звісно, ​​ми ще немає, тим щонайменше, ми можемо побачити, що ми успішно вдалося з'єднати три таблиці. Тепер додаємо групування книг, сортування і ті поля, які нам потрібні: $ SELECT tch.last_name, st.last_name, st.id, COUNT(*) as books 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 як 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 як 'Teacher', sbw.last_name 'Student', sbw.books як 'Books' від studentbook sbw 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
Петроов	9
Ivanov	5

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

Завдання 11

Умова: Вибрати 'Teacher', у яких кількість 'Book' у всіх його 'Student' знаходиться між 7 і 11. Відсортувати за кількістю у порядку зменшення. Виглядати має так:

Teacher's last_name	Book's quantity
Петроов	11
Sidorov	9
Ivanov	7

Ось тут ми і будемо використовувати HAVING. Ми говорабо про нього. Запит буде такий самий, як і до цього, тільки потрібно ще додати умову, що кількість книг буде в певному діапазоні. А як я вже говорив у попередніх статтях, коли нам потрібно зробити фільтрацію під час угруповання та/або над агрегуючими функціями, потрібно використовувати HAVING : $ SELECT tch.last_name 'Teacher' 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 Я виділив частину, яку додав. І, власне, очікуваний результат: Тільки Василенко пройшов цей тур))

Завдання 12

Умова: Вивести всіх 'last_name' та 'name' всіх 'Teacher' та 'Student' з полем 'type' (student або teacher). Сортувати за алфавітом за 'last_name'. Виглядати має так:

last_name	type
Ivanov	student
Kankava	teacher
Smith	student
Sidorov	teacher
Петроов	teacher

Тобто потрібно нам об'єднати два висновки, і для цього якраз є UNION. Іншими словами, ми візьмемо записи від студентів і від вчителів і виведемо разом: $ SELECT last_name, 'teacher' як тип від teacher UNION ALL select last_name, 'student' як тип 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'. Тут потрібно просто видати в результаті запиту рядок, в якому будуть всі книги. Тут знову треба було гуглити. Є така функція - GROUP_CONCAT , за допомогою якої це робиться дуже просто: $ SELECT GROUP_CONCAT(title) from book; І все…)) Усі 14 завдань готові.

Висновки

Ууххх… Це було непросто. Це було цікаво. Завдання того варті, я більш ніж впевнений. Поки робабо ці завдання, пройшлися багатьма речами, які раніше не були відомі:

• SQL VIEW
• GROUP_CONCAT
• UNION

і так далі. Дякую всім, у кого вистачило сил прочитати і повторити те, що я зробив. Хто знає, як зробити запити кращими — пишіть у коментарях, я обов'язково прочитаю їх)

Список всіх матеріалів серії на початку цієї статті.