JavaRush /Java блог /Java-проекты /Разбираем базы данных и язык SQL. (Часть 4 — проверка дом...
Roman Beekeeper
35 уровень

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

Статья из группы Java-проекты
Статья из серии о создании Java-проекта (ссылки на другие материалы — в конце). Ее цель — разбор ключевых технологий, итог — написание телеграм-бота. Предыдущие статьи и разбор домашнего задания по базам данных: 1, 2, 3. Всем, у кого хватило терпения и выдержки, всем, кто идет со мной уже четвертую статью — вы молодцы. Как говорится, дорогу осилит идущий. На этой неделе выйдет заключительная статья о Базах Данных, в которой мы поговорим о типах связей и джоинах (соединениях). Но перед тем, как мы разберемся с новой информацией — проверим домашнее задание… Я прям училкой себя почувствовал. Не серчайте на меня: педагогического образования у меня нет, маемо шо маемо. Поскольку на прошлой неделе подробная проверка ДЗ заняла львиную долю материала, я решил разбить разбор домашки и обзор нового материала на две части."Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 1

Собственно, разбор домашки

Я определенно рад тому, что находятся люди, которые делают ДЗ и говорят об этом. Это здорово! Я максимально уверен, что просто прочитать без закрепления знаний — это путь в никуда. Поэтому все, кто сделал или пытался сделать, — респект. Напомню условия заданий:
  1. Разобраться с оператором HAVING и написать пример запроса для таблиц из нашего примера. Если нужно добавить какие-то поля или ещё значений, чтобы было нагляднее — добавляйте. Кто хочет — пишите в комментариях свой пример решения — так я его еще и проверить смогу, если успею.
  2. Установить MySQL Workbench для работы с БД через UI. Я думаю, что мы уже достаточно практиковались работе из консоли. Подключиться к БД. Если используете что-то другое для работы с БД — смело скипайте это задание. Здесь и дальше я буду использовать только MySQL Workbench.
  3. Написать запросы на получения по нашим данным:
    1. самого мало/много численной страны;
    2. среднее количество жителей в стране;
    3. среднее количество жителей в странах, чьи имена оканчиваются на “a”;
    4. количество стран, у которых население больше четырех миллионов;
    5. отсортировать страны по уменьшению количества жителей;
    6. отсортировать страны по имени в натуральном порядке.

Поговорим о HAVING

Знание оператора Having может помочь вам пройти не одно собеседование, где будут задачи на SQL. Поэтому понять его крайне важно. Так уж получилось, что использовать условия для агрегирующих функций (SUM, MIN, MAX, AVG) нельзя. К тому же, HAVING используют для полей, которые группируются. Что это значит? Например, если мы хотим получить страны, где среднее количество жителей в городах больше 50 000 жителей, без использования HAVING нам не обойтись. Как я понимаю, сделано это потому, что агрегация происходит уже после того, как выполнится оператор WHERE и нельзя добавить в него значения агрегации, которые будут подсчитаны позже. Даже если пока что мои суждения не сильно добавляют понимания, можно просто принять это как факт и идти с ним. В программировании часто получается так, что если в один момент что-то непонятно, это вполне может означать, что мозг просто еще не переварил это. Переспите с этой мыслью, и на следующий день все станет яснее.

Установка MySQL Workbench

Здесь и далее я буду использовать именно Workbench для запросов. Покажу, что нужно для установки и создания соединения с базой данных. Это продукт от Oracle, поэтому нужно просто пойти на их сайт и выбрать нужную версию и операционную систему. Для этого перейдем по этой ссылке:"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 2Здесь вы можете выбрать именно ту операционную систему, которая вам нужна. Нажимаем Download, но вместо загрузки мы увидим такое окно:"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 3Не теряемся, просто ищем кнопочку с названием No thanks, just start my download, и начнется скачивание. Зачем они это делают? Наверное, чтобы больше регистрировалось у них, нам это не важно. После успешной загрузки запускаем установочный файл. На MacOS это выглядит так:"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 4Просто переносим значок — и все, установка завершена. Уже не так сложно, как установка самого MySQL, правда? Или уже просто привыкли и стали более опытные ;) Вторая часть этой задачи — установить соединение с нашей базой данных. Что для этого нужно? Нажимаем плюсик рядом с MySQL Connections:"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 5В появившемся окне вводим необходимые данные:
  • Connection Name — имя нашего соединения. Пишите максимально понятные имена, чтобы потом не было проблем с идентификацией. Я этому соединению даю имя JRTB_DB;
  • Hostname — уже будет задан как локальный 127.0.0.1 (он же localhost). В нашем случае ничего менять не нужно, так как БД установлена на компьютере, а вот если БД где-то в другом месте, то и хост (ip той машины, на которой запущена БД), соответственно, изменить нужно;
  • Username — также по необходимости можно задавать своего юзера. Если вы не добавляли ничего в этом ключе, оставьте его неизменным;
  • Password — нажимаем Store in Keychain и задаем именно тот пароль, который вы задавали у себя. Я оставил все по-простому — root.
Чтобы проверить, будет ли соединение, нажимаем Test Connection:"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 6Ну и если все сделано было правильно, результат не заставит себя ждать:"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 7Теперь у нас есть сохраненное соединение в БД, и не нужно будет каждый раз создавать соединение, заполнять имя и пароль. И будет выглядеть это счастье вот так:"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 8Заходим в только что созданное соединение и видим окно для запросов. Чтобы быть уверенным, что все правильно, проверим список баз данных, зайдем в нашу и получим все данные о городах:"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 9Причем здесь мы получаем картбланш на то, что нам нужно. Первая секция отвечает за скрипт, который мы вводим. Далее, в Result Grid, мы видим результат последней операции в скрипте. А в Action Output показан список операций и его результат. Очень полезная вещь, хочу я вам сказать: с ее помощью можно следить за скоростью выполнения определенных скриптов. Почему это важно? Одна из самых распространенных проблем в скорости выполнения задач в приложении — это скорость выполнения запросов в БД. Здесь их можно будет быстро и удобно проверить руками.

Пишем необходимые запросы

У нас всего 7 запросов, которые нужно выполнить, поехали!

  1. Получить самую многочисленную страну. Здесь можно пойти хитро и несколькими маршрутами:

  • По данным таблицы country

Тогда необходимо просто отсортировать наш запрос по населению и взять только одну запись. Для этого дела в конце скрипта нужно добавить оператор LIMIT и указать необходимое количество: $ SELECT * FROM country ORDER BY population DESC LIMIT 1;"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 10

  • По данным таблицы city

Здесь все интереснее, потому что запрос будет сложнее, но и интереснее. Так как мы еще не имеем понятия о джоинах, можем получить только ID-шник страны: $ SELECT country_id, SUM(population) FROM city GROUP BY country_id ORDER BY SUM(population) DESC LIMIT 1;"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 11Здесь мы сделали прикольную вещь — собрали сумму населения всех известных городов по каждой стране, отсортировали по этой сумме и взяли первый элемент. Ну как, здорово? Я вот в восторге :D После такого сразу ощущаешь себя гуру запросов… (ненадолго, конечно))

  1. Получить самую малочисленную страну. Здесь можно пойти хитро и несколькими маршрутами

В этом случае все будет ровно так же. Разница лишь в том, что сортировать будет обратно — и все. Поэтому просто пишу запросы:

  • По данным таблицы city

$ SELECT country_id, SUM(population) FROM city GROUP BY country_id ORDER BY SUM(population) LIMIT 1;

  • По данным таблицы country

$ SELECT * FROM country ORDER BY population LIMIT 1; А результат уже посмотрите сами!

  1. Среднее количество жителей в стране

Вот опять ТЗ стоит как-то не точно, как будто писал менеджер... Почему я так решил? Потому что неясно, в какой таблице работать. Но это нормально: задач, в которых будет сразу все понятно и ясно, просто не бывает. Поэтому нужно внимательно вчитываться в задачи, и если есть вопросы — сразу задавать их! Это так, ремарка. С учетом данных, которые есть у нас в БД, будем искать по данным из городов. Для этого пишем следующий запрос: $ SELECT country_id, AVG(population) FROM city GROUP BY country_id;"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 12Здесь все просто: мы используем функцию AVG и группируем наши записи городов по странам.

  1. Среднее количество жителей в странах, чьи имена заканчиваются на “a”

Здесь будет небольшое изменение по запросу. Нужно добавить фильтрацию по именам, прежде чем мы будем делать группировку. Делаю я домашку, как и все студенты, перед публикацией этой статьи, и понимаю, что без джоинов эту задачу не решить. Почему? Потому что помимо ID-шника страны нам нужно еще получить его имя. А это нельзя сделать без соединения двух таблиц в одну запись. Поэтому я сделаю эту задачу, конечно, но это мой косяк…))) Хотел придумать задачу с использованием LIKE в запросе…) $ SELECT ci.country_id, AVG(ci.population) FROM city ci INNER JOIN country co ON ci.country_id = co.id WHERE co.name LIKE "%a" GROUP BY country_id;"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 13Что здесь произошло? Вначале мы соединили записи с таблицы city и country по внешнему ключу country_id, отфильтровали по именам стран, чтобы они заканчивались на “a”, и уже потом группировали по country_id.

  1. Количество стран, у которых население больше четырех миллионов

Здесь нам нужно просто использовать функцию COUNT и добавить фильтрацию на население: $ SELECT COUNT(*) from country WHERE population > 4000000; В результате узнаем, что таких стран 3. Правильно ли это? Да, только Молдова не проходит этот рубеж.

  1. Отсортировать страны по уменьшению количества жителей

Чтобы сделать это, нужно использовать уже известный нам оператор ORDER BY. Но учтите, что по умолчанию сортировка идёт в натуральном порядке. Для чисел это значит, что сортируется по возрастанию, для строк — что начиная с первых символов. Если нам нужна сортировка по убыванию, нам нужен обратный от натурального: $ SELECT * FROM country ORDER BY population DESC;"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 14

  1. Отсортировать страны по имени в натуральном порядке

Вот здесь нам и пригодятся знания того, что такое натуральный порядок. Так как он по умолчанию, для нас это проще простого: $ SELECT * FROM country ORDER BY name;"Java-проект от А до Я": разбираем базы данных и язык SQL. Часть 4 — проверка домашнего задания - 15

Вместо вывода

Так уж получилось, что размер решения домашнего задания получится очень большой, поэтому мы сделаем исключение: я публикую эту статью с проверкой, а в пятницу опубликую новый материал со связями и джоинами. Всем спасибо за прочтение. До пятницы!

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

Комментарии (49)
ЧТОБЫ ПОСМОТРЕТЬ ВСЕ КОММЕНТАРИИ ИЛИ ОСТАВИТЬ КОММЕНТАРИЙ,
ПЕРЕЙДИТЕ В ПОЛНУЮ ВЕРСИЮ
Kirill Уровень 18
1 апреля 2023
А как в 3 и 4 задании еще присовокупить столбец с названием страны. Чтобы столбцы были id, name, avg(population)? Исходя из подсчета avg по таблице с городами.
Кирилл Уровень 35
11 марта 2023
Все очень классно. Спасибо! Я уже несколько месяцев решал SQL на хаккер ранке, поэтому с задачами справился, но поработать не в синтетической среде, а в той, которая вот по настоящему работает - огромное удовольствие.
YesOn Уровень 13
25 января 2022

"Получить самую многочисленную страну. Здесь можно пойти хитро и несколькими маршрутами:
По данным таблицы country
Тогда необходимо просто отсортировать наш запрос по населению и взять только одну запись. Для этого дела в конце скрипта нужно добавить оператор LIMIT и указать необходимое количество:
$ SELECT * FROM country ORDER BY population DESC LIMIT 1;
На мой взгляд (новичка в этой теме) было бы логично для решения этой задачи поступить в данной ситуации так: SELECT id, name, MAX(population) FROM country; Но этот запрос не срабатывает и пишет про ошибку: Для тех, кто также как и я озадачился своеобразной логикой применения функции MAX в SQL при выполнении домашнего задания, сообщаю: есть способ лучше и логичнее (хоть и немного длиннее) и без всяких ORDER или GROUP:

SELECT id, name, population FROM country WHERE population = (SELECT MAX(population) FROM country);
Называется он вложенный запрос (источник). Хотя вот сейчас перечитал ещё раз про ORDER BY, DESC и LIMIT, посмотрел решение автора статьи и стало всё ясно, как белый день.
Javart Уровень 11
7 августа 2021
Застрял на пункте с подключением нашей БД к MySQL Workbench. Добавил базу и нажал Test Connection все корректно отобразилось. Перехожу в базу, пишу в консоли SHOW DATABASES; и ничего не происходит, тупо на след строку перепрыгивает. В чем может быть проблема?
Павел Игонин Уровень 23
16 июня 2021
Заметил что часто скриншоты отличаются от происходящего в тексте. Да, и бох и с ним. Но вот в шестом задании на скрине отсутствует сортировка, эт уж форменный беспредел.
Roman Beekeeper Уровень 35
11 марта 2021
⚡️UPDATE⚡️ Друзья, создал телеграм-канал 🤓, в котором освещаю свою писательскую деятельность и свою open-source разработку в целом. Не хотите пропустить новые статьи? Присоединяйтесь ✌️
Алексей К. Уровень 41
28 января 2021
Друзья, кто нибудь может объяснить, есть ли принципиальная разница между запросом который приведен в статье:

SELECT ci.country_id, AVG(ci.population) 
FROM city ci 
INNER JOIN country co ON ci.country_id = co.id 
WHERE co.name LIKE "%a" 
GROUP BY country_id;
и таким вариантом:

SELECT ci.country_id, AVG(ci.population) 
FROM city ci, country co 
WHERE ci.country_id = co.id AND co.name LIKE "%a" 
GROUP BY country_id;
UPD: Если кого-то это тоже интересует, вот ответ
Igor Уровень 18
26 января 2021
Для тех у кого macOs Big Sur. Oracle все еще работает над фиксом бага Bug #101328 последней версии MySQL Workbench (v.8), я установил версию v.6 из архива и все работает.
Сергей Бычков Уровень 13
21 января 2021
+