Продолжаем изучать базы данных в рамках курса python_advanced. Предыдущие части можно лицезреть тут:
Итак, у нас есть база данных, с примерно, вот такими, пятью таблицами:
И нам нужно сделать соответствующие SQL-запросы, дабы найти:
Первая задача
Узнайте, кто из преподавателей задаёт самые сложные задания. Другими словами, задания какого преподавателя получают в среднем худшие оценки.
Вот такой запрос у меня получился к базе данных. Если попытаться объяснить это русским языком то получается что-то типа этого: внутренним SELECT'ом из трех таблиц берем данные, группируем по ag.assisgnment_id, и получившуюся таблицу обрабатываем внешним SELECT'ом, где выводим среднюю оценку и фамилию искомого преподавателя.
Идём дальше...
Вторая задача
Дирекция школы решила наградить лучших учеников грамотами, но вот беда, в принтере картриджа хватит всего на 10 бланков. Выберите 10 лучших учеников с самым высоким средним баллом. Не забудьте отсортировать список в нисходящем порядке.
В данном запросе, как и в предыдущем, используются также два SELECT'a. Во внутреннем SELECT'е объединяются две таблицы assignments_grades и students, получаем имя студента и среднюю оценку всех студентов, группируем таблицу по student_id и передаем во внешний SELECT. Где мы уже сортируем таблицу по убыванию (то есть лучшие ученики будут в самом верху таблицы) и ограничиваем список учеников конструкцией LIMIT 10.
Третья задача
Используя вложенные запросы, найдите всех учеников того преподавателя, который задаёт самые простые задания, то есть те задания, за которые ученики получают самый высокий средний балл.
* усложнённое задание (по желанию): напишите этот же запрос с использованием одного из join.
Вот тут уже у меня получилась большая куча SELECT'ов. Выглядит довольно таки устрашающе, но:
Это самый, пожалуй, дурацкий запрос получился, но он работает, я подозреваю что его можно оптимизировать, убрав несколько SELECT'ов. Но то что работает - переделывать уже не буду ибо надо идти дальше по "граниту учёбы".
4. Идем дальше...
Используя вложенные запросы, посчитайте среднее, максимальное и минимальное количество просроченных заданий для каждого класса.
Тут тоже не фантастиш ответ, но куратора мой ответ удовлетворил, поэтому оставлю так как есть. А по сути, в этом задании самое главное - понять что от тебя требуется в данном запросе сделать. Я долго не мог понять как это может быть минимальное, максимальное и среднее количество просроченных заданий?????
Если быть близким к заданию, то нужно найти - в какой группе больше всего просрочено заданий, в какой меньше всего и в какой среднее количество просрочек. Если с максимумом и минимумом не возникает вопросов то со средним количеством просрочек (ответ: 24 ) - тут уже появляются вопросы, потому как числа 24 в списке просрочек не существует, значит нужно подгонять близко к этому числу, но в какую сторону? В большую или меньшую? Вопросов больше чем ответов.
В общем, я решил задачу вот так:
Но по уму, ответ должен быть вот таким:
Поэтому оставил всё как есть, и отправил задание куратору на проверку, в надежде что если он не примет, то хоть подскажет как действовать... Но куратор принял...
5.Продолжаем...
Проанализируйте все группы по следующим критериям: общее количество учеников, средняя оценка, количество учеников, которые не сдали работы, количество учеников, которые просрочили дедлайн, количество повторных попыток сдать работу.
Вот на этом задании я очень долго сидел, мне казалось бы я понимаю принцип SQL-запросов, но что-то пошло не так и я завис... Даже ради того чтобы отвиснуть начал просмотр курса по SQL:
И почти закончил его... Немного осталось, так что завершу этот курс.
И вот что у меня по итогу получилось, я создал 5 различных запросов, без всякого форматирования по строкам записал:
Читабельность нарушена ради того, чтобы выполнять каждый запрос построчно. Одна строчка - один запрос. Вообще в данном модуле у меня такая схема: запрос в виде файла query.sql и файла main.py который этот запрос обрабатывает, вот как это выглядит в этом задании:
Ну и вывод программы:
6. Заключительное задание
Используя подзапросы, выведите среднюю оценку за те задания, где ученикам нужно было что-то прочитать и выучить.
Вот такую конструкцию я собрал...
Что-то вроде эпилога:
SQL-запросы очень хитрый предмет, когда в этом разберешься то и объяснять особо не чего. Но пока в голове всё не сложилось - сидишь и тупишь, не понимая почему так или эдак. И почему в этом месте у тебя ошибка.
С этим заданием я возился почти две недели. Видимо оно должно было созреть в голове. Возможно, еще не всё окончательно сложилось...
Как бы то ни было - двигаемся вперёд, ни на кого не оглядываемся и ни с кем себя не сравниваем, разве что только с собой, прошлым...
Ну и традиционное:
Да прЕбудет с вами СИЛА! ;-)