Домашнее задание студента курса “Язык запросов SQL”

---

Структура базы данных

Домашнее задание №1 - Выбор и фильтрация данных, запрос SELECT

1. Написать запрос для выбора студентов в порядке их регистрации.

mysql> SELECT * FROM Students ORDER BY registration_date;

Результат:

2. Написать запрос для выбора 5 самых дорогих курсов, на которых более 4 студентов, и которые длятся более 10 часов.

mysql> SELECT name, duration, students_count, price FROM Courses WHERE students_count > 4 AND duration > 10 ORDER BY price DESC LIMIT 5;

Результат:

3. Написать один (!) запрос, который выведет одновременно список из имен трёх самых молодых студентов, имен трёх самых старых учителей и названий трёх самых продолжительных курсов.

mysql> (SELECT name FROM Students ORDER BY age LIMIT 3) -> UNION -> (SELECT name FROM Teachers ORDER BY age DESC LIMIT 3) -> UNION -> (SELECT name FROM Courses ORDER BY duration DESC LIMIT 3);

Результат:

Домашнее задание №2 - Функции и выражения, агрегация данных

1. Написать запрос для выбора среднего возраста всех учителей с зарплатой более 10 000.

mysql> SELECT AVG(age) AS average_age_of_teachers_whose_salary_is_higher_than_10k FROM Teachers WHERE salary > 10000;

Результат:

2. Написать запрос для расчета суммы, сколько будет стоить купить все курсы по дизайну.

mysql> SELECT SUM(price) AS total_price_for_design_courses FROM Courses WHERE type = "DESIGN";

Результат:

3. Написать запрос для расчёта, сколько минут (!) длятся все курсы по программированию.

mysql> SELECT SUM(duration * 60) AS duration_of_all_prog_courses_in_minutes -> FROM Courses WHERE type = "PROGRAMMING";

Результат:

Домашнее задание №3 - Группировка, соединение таблиц (JOIN)

1. Напишите запрос, который выводит сумму, сколько часов должен в итоге проучиться каждый студент (сумма длительности всех курсов на которые он подписан).

В результате запрос возвращает две колонки: Имя Студента — Количество часов.

mysql> SELECT Students.name AS student_name, -> SUM(duration) AS duration_of_all_courses -> FROM Courses -> JOIN Subscriptions ON Courses.id = Subscriptions.course_id -> JOIN Students ON Students.id = Subscriptions.student_id -> GROUP BY Students.name;

Результат:

2. Напишите запрос, который посчитает для каждого учителя средний возраст его учеников.

В результате запрос возвращает две колонки: Имя Учителя — Средний Возраст Учеников.

mysql> SELECT Teachers.name AS Teacher_name, -> AVG(Students.age) AS average_age_of_students -> FROM Courses -> JOIN Teachers ON Teachers.id = Courses.teacher_id -> JOIN Subscriptions ON Courses.id = Subscriptions.course_id -> JOIN Students ON Students.id = Subscriptions.student_id -> GROUP BY Teacher_name;

Результат:

3. Напишите запрос, который выводит среднюю зарплату учителей для каждого типа курса (Дизайн/Программирование/Маркетинг и т.д.).

В результате запрос возвращает две колонки: Тип Курса — Средняя зарплата.

mysql> SELECT Courses.type AS course_type, -> AVG(salary) AS average_teachers_salary -> FROM Courses -> JOIN Teachers ON Teachers.id = Courses.teacher_id -> GROUP BY course_type;

Результат: