Практика: Сложные SELECT-запросы

Онлайн редактор кода для PostgreSQL

Здесь вы можете попробовать свои силы в программировании на языке SQL для работы с PostgreSQL. Для этого мы предоставляем вам онлайн редактор кода, в котором вы можете написать свой код и запустить его.

❗️ При обновлении страницы код пропадёт, по этому, сохраните свой код куда-нибудь, если он важный.

Задание 1 — Простые JOIN + фильтры

Задание 1: Базовые JOIN + WHERE

⏱️ Примерное время: 10-15 минут

Напиши запрос, который будет выводить данные из твоего варианта (INNER JOIN + LEFT JOIN). Добавь фильтры по major, status, grade.

Требования:

• используй students s INNER/LEFT JOIN enrollments e ON ... JOIN courses c ON ...
• выводи: имя студента, название курса, оценка, статус
• ORDER BY student_name, course_name
• LIMIT 20 для удобства

Пример:

SELECT 
    s.first_name || ' ' || s.last_name AS student,
    c.course_name,
    e.grade,
    e.status
FROM students s
INNER JOIN enrollments e ON s.student_id = e.student_id
INNER JOIN courses c ON e.course_id = c.course_id
WHERE e.status = 'active'
ORDER BY student, c.course_name
LIMIT 20;

Задание 2 — Агрегаты + GROUP BY + HAVING

Задание 2: Группировка и агрегаты

⏱️ Примерное время: 20-25 минут

Напиши запросы с GROUP BY, COUNT, AVG, MIN/MAX, STRING_AGG, FILTER, HAVING.

Требования:

• GROUP BY major / city / department / year
• агрегаты: COUNT(*), AVG(gpa), ROUND(...,2), STRING_AGG
• HAVING COUNT(*) > 2 / AVG(gpa) > 3.5
• ORDER BY убыванию

Пример:

SELECT 
    major,
    COUNT(*) AS student_count,
    ROUND(AVG(gpa), 2) AS avg_gpa,
    COUNT(*) FILTER (WHERE gpa >= 3.7) AS excellent
FROM students
GROUP BY major
HAVING COUNT(*) >= 2
ORDER BY avg_gpa DESC;

Задание 3 — Подзапросы + EXISTS / NOT EXISTS

Задание 3: Подзапросы и EXISTS

⏱️ Примерное время: 25-35 минут

Используй подзапросы (IN, EXISTS, коррелированные) для фильтрации и вычислений.

Требования:

• минимум 3–4 варианта: IN, EXISTS, коррелированный, ANY/ALL
• WHERE EXISTS / NOT EXISTS / > ALL / IN (SELECT ...)
• ROUND(AVG(...), 2) где нужно

Пример:

SELECT 
    first_name || ' ' || last_name AS student,
    major,
    gpa
FROM students s
WHERE gpa > (
    SELECT AVG(gpa)
    FROM students s2
    WHERE s2.major = s.major
)
ORDER BY major, gpa DESC;

Задание 4 — LATERAL + DISTINCT ON

Задание 4: LATERAL JOIN + DISTINCT ON

⏱️ Примерное время: 35-45 минут

Используй LATERAL для топ-N и DISTINCT ON для уникальных записей.

Требования:

• LATERAL + LIMIT 3 / 5
• DISTINCT ON (student_id) ORDER BY ... DESC
• CROSS JOIN LATERAL (...) AS top

Пример:

SELECT 
    major,
    top_student.student_name,
    top_student.gpa
FROM (
    SELECT DISTINCT ON (major) major
    FROM students
) m
CROSS JOIN LATERAL (
    SELECT 
        first_name || ' ' || last_name AS student_name,
        gpa
    FROM students s
    WHERE s.major = m.major
    ORDER BY gpa DESC
    LIMIT 1
) AS top_student;

Задание 5 — Итоговое: Аналитический отчёт

Задание 5: Полный аналитический отчёт (итоговое)

⏱️ Примерное время: 45-70 минут

Создай 1 большой отчёт + 3–4 отдельных аналитических запроса по базе university.

Требования:

• минимум 3 JOIN (INNER/LEFT)
• GROUP BY + HAVING
• подзапросы / EXISTS / LATERAL
• агрегаты + FILTER / STRING_AGG / JSON_AGG
• оформить красиво: AS student_name, ROUND(...,2)
• ORDER BY + LIMIT 20

Пример структуры отчёта:

-- Успеваемость по специальностям
SELECT 
    major,
    COUNT(*) AS students,
    ROUND(AVG(gpa), 3) AS avg_gpa,
    COUNT(*) FILTER (WHERE gpa >= 3.7) AS excellent,
    ROUND(100.0 * COUNT(*) FILTER (WHERE gpa >= 3.7) / COUNT(*), 1) AS excellent_pct
FROM students
GROUP BY major
HAVING COUNT(*) >= 2
ORDER BY avg_gpa DESC;

Это задание — полноценный аналитический отчёт, который можно показать на собеседовании.

подсказка

Сложные SELECT — это когда ты начинаешь думать как аналитик, а не просто как разработчик.
JOIN + подзапросы + агрегаты + LATERAL + FILTER — это 90% всех реальных отчётов в PostgreSQL.