---

Задание 11. Сортировка и фильтрация
Цель: научиться выбирать данные по условию и сортировать.
\c student_db

-- Студенты старше 20 лет, отсортированные по возрасту (убывание)
SELECT full_name, age FROM students
WHERE age > 20
ORDER BY age DESC;

-- Студенты с возрастом от 19 до 21
SELECT full_name, age FROM students
WHERE age BETWEEN 19 AND 21
ORDER BY age;

-- Поиск по шаблону: все с фамилией на "И"
SELECT * FROM students WHERE full_name LIKE 'И%';
Сдать: скриншот трёх результатов.
Задание 12. DISTINCT и агрегаты
Цель: уникальные значения и агрегатные функции.
-- Уникальные возрасты
SELECT DISTINCT age FROM students ORDER BY age;

-- Общая статистика
SELECT
    COUNT(*)          AS total_students,
    MIN(age)          AS min_age,
    MAX(age)          AS max_age,
    AVG(age)::numeric(4,2) AS avg_age,
    SUM(age)          AS sum_age
FROM students;
Ожидаемо (после задания 5, 4 студента: 21, 21, 19, 22):
· total_students = 4, min = 19, max = 22, avg ≈ 20.75, sum = 83
Сдать: скриншот обоих запросов.
Задание 13. Группировка с HAVING
Цель: отфильтровать группы после GROUP BY.
-- Возрасты, которые встречаются больше одного раза
SELECT age, COUNT(*) AS cnt
FROM students
GROUP BY age
HAVING COUNT(*) > 1
ORDER BY age;

-- Количество студентов по первой букве имени
SELECT LEFT(full_name, 1) AS first_letter, COUNT(*) AS cnt
FROM students
GROUP BY first_letter
ORDER BY first_letter;
Сдать: скриншот результатов.
Задание 14. Обновление с RETURNING и CASE
Цель: массовое обновление + возврат изменённых строк.
-- Повысить возраст всем младше 20 на 1 год и показать, кто изменился
UPDATE students
SET age = age + 1
WHERE age < 20
RETURNING id, full_name, age;

-- Добавить колонку-категорию через CASE
SELECT full_name, age,
       CASE
           WHEN age < 20 THEN 'юный'
           WHEN age BETWEEN 20 AND 21 THEN 'средний'
           ELSE 'старший'
       END AS category
FROM students
ORDER BY age;
Сдать: результат UPDATE ... RETURNING + результат с CASE.
Задание 15. Добавление новой колонки и её заполнение
Цель: изменить структуру существующей таблицы.
ALTER TABLE students ADD COLUMN phone VARCHAR(20);

UPDATE students SET phone = '+7-900-000-00-01' WHERE id = 1;
UPDATE students SET phone = '+7-900-000-00-02' WHERE id = 2;
UPDATE students SET phone = '+7-900-000-00-03' WHERE id = 3;
UPDATE students SET phone = '+7-900-000-00-04' WHERE id = 4;

SELECT id, full_name, phone FROM students ORDER BY id;
Сдать: скриншот \d students (с новой колонкой) + SELECT с телефонами.
Задание 16. JOIN двух таблиц
Цель: связать students с новой таблицей groups.
CREATE TABLE groups (
    id SERIAL PRIMARY KEY,
    group_name VARCHAR(20) NOT NULL
);

INSERT INTO groups (group_name) VALUES
('ИС-21'), ('ИС-22'), ('ПИ-21');

ALTER TABLE students ADD COLUMN group_id INT REFERENCES groups(id);

UPDATE students SET group_id = 1 WHERE id IN (1, 3);
UPDATE students SET group_id = 2 WHERE id = 2;
UPDATE students SET group_id = 3 WHERE id = 4;

-- INNER JOIN
SELECT s.full_name, s.age, g.group_name
FROM students s
JOIN groups g ON s.group_id = g.id
ORDER BY s.id;

-- LEFT JOIN (покажет и студентов без группы, если есть)
SELECT s.full_name, g.group_name
FROM students s
LEFT JOIN groups g ON s.group_id = g.id
ORDER BY s.id;
Сдать: скриншот обоих JOIN-запросов.
Задание 17. Подзапросы
Цель: использовать SELECT внутри SELECT.
-- Студенты старше среднего возраста
SELECT full_name, age
FROM students
WHERE age > (SELECT AVG(age) FROM students)
ORDER BY age;

-- Студенты из группы "ИС-21" через подзапрос
SELECT full_name
FROM students
WHERE group_id = (SELECT id FROM groups WHERE group_name = 'ИС-21');

-- EXISTS: есть ли студенты младше 20
SELECT EXISTS(SELECT 1 FROM students WHERE age < 20) AS has_young;
Сдать: скриншот трёх запросов.
Задание 18. Транзакции (BEGIN / COMMIT / ROLLBACK)
Цель: понять атомарность операций.
-- Успешная транзакция
BEGIN;
UPDATE students SET age = age + 1 WHERE id = 1;
INSERT INTO students (full_name, age, email)
VALUES ('Тестов Тест', 30, 'test@mail.ru');
COMMIT;
SELECT * FROM students ORDER BY id;

-- Откат транзакции
BEGIN;
DELETE FROM students;