✅ Решения

⚡ Кратко: решения

Теория: ORDER BY, LIMIT/OFFSET, агрегаты, GROUP BY, HAVING, JOIN, UNION.
hr: WHERE, LIKE, CASE, AVG.
world: JOIN + фильтр IsOfficial = 'T'; больше всего строк — у стран с языками.
Airlines: GROUP BY, JOIN для билетов, сортировка по агрегату.
Домашнее задание из урока 30 разобрано в разделе Домашнее задание.

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

ORDER BY

ORDER BY сортирует результаты запроса по одному или нескольким столбцам. По умолчанию сортировка идёт по возрастанию (ASC); для сортировки по убыванию используется DESC.

LIMIT и OFFSET

LIMIT ограничивает количество возвращаемых строк, OFFSET пропускает указанное число строк. Вторая страница из 20 строк: LIMIT 20 OFFSET 20.

Агрегирующие функции

COUNT, SUM, AVG, MIN, MAX. Функция AVG() игнорирует NULL при вычислении среднего.

GROUP BY

GROUP BY группирует строки с одинаковыми значениями в указанных столбцах. В SELECT можно выводить только группируемые столбцы и результаты агрегирующих функций.

HAVING

WHERE фильтрует строки до группировки; HAVING фильтрует группы после агрегации. HAVING без GROUP BY допустим, но используется редко — он фильтрует единственную группу из всех строк.

JOIN

Типы: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN. INNER JOIN возвращает только совпадающие строки из обеих таблиц; LEFT JOIN — все строки из левой таблицы и совпавшие из правой.

UNION

UNION удаляет дубликаты; UNION ALL сохраняет все строки. Объединяемые запросы должны иметь одинаковое количество столбцов и совместимые типы данных.

Решения практических задач

Блок 1. База данных hr

1.1

Сотрудники отдела 90

SELECT *
FROM employees
WHERE department_id = 90;

1.2

Сотрудники с зарплатой больше 5000

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 5000;

1.3

Сотрудники с фамилией на L

SELECT first_name, last_name
FROM employees
WHERE last_name LIKE 'L%';

1.4

Поле SALARY_GROUP

SELECT first_name, last_name, salary,
       CASE
         WHEN salary > 10000 THEN 1
         ELSE 0
       END AS salary_group
FROM employees;

1.5

Средняя зарплата ниже 10000

SELECT AVG(salary) AS avg_salary
FROM employees
WHERE salary < 10000;

Блок 2. База данных world

2.1

Страны со столицами

SELECT country.Name AS country_name, city.Name AS capital
FROM country
JOIN city ON country.Capital = city.ID;

2.2

Страны с языками

SELECT country.Name AS country_name, countrylanguage.Language
FROM country
JOIN countrylanguage ON country.Code = countrylanguage.CountryCode;

2.3

Страны с официальными языками

SELECT country.Name AS country_name, countrylanguage.Language
FROM country
JOIN countrylanguage ON country.Code = countrylanguage.CountryCode
WHERE countrylanguage.IsOfficial = 'T';

2.4

Сравнение количества записей

SELECT COUNT(*) FROM country JOIN city ON country.Capital = city.ID;
SELECT COUNT(*) FROM country JOIN countrylanguage ON country.Code = countrylanguage.CountryCode;
SELECT COUNT(*) FROM country JOIN countrylanguage ON country.Code = countrylanguage.CountryCode
WHERE countrylanguage.IsOfficial = 'T';

Больше всего строк будет во втором запросе, потому что одна страна может иметь много языков; меньше всего — в третьем, где остаются только официальные языки.

Блок 3. База данных Airlines

3.1

Самолёты по моделям

SELECT model, COUNT(*) AS aircraft_count
FROM aircraft
GROUP BY model;

3.2

Рейсы по типам лайнеров

SELECT plane, COUNT(*) AS trip_count
FROM trips
GROUP BY plane;

3.3

Средняя цена по классам

SELECT service_class, AVG(price) AS avg_price
FROM tickets
GROUP BY service_class;

3.4

Поездки по убыванию количества билетов

SELECT trips.id, trips.trip_no, COUNT(tickets.id) AS ticket_count
FROM trips
JOIN tickets ON trips.id = tickets.trip_id
GROUP BY trips.id, trips.trip_no
ORDER BY ticket_count DESC;