SQL Практика 1 - ответы

SQL Практика

SQL Практика 1
ответы Java

Практика 1 — Основы, DDL, SELECT, NULL

Постарайся решить задачи самостоятельно, не подсматривай в ответы

SELECT и фильтрация

Задача 1: Выбрать всех активных пользователей

SELECT * FROM users WHERE status = 'active';

Задача 2: Найти пользователей старше 25 лет из Москвы

SELECT * FROM users
WHERE age > 25 AND city = 'Москва';

Задача 3: Найти товары дешевле 1000 или дороже 10 000

SELECT * FROM products
WHERE price < 1000 OR price > 10000;

Задача 4: Найти пользователей с email на gmail.com

SELECT * FROM users
WHERE email LIKE '%@gmail.com';

Задача 5: Найти товары категорий «Электроника» и «Одежда»

SELECT * FROM products
WHERE category IN ('Электроника', 'Одежда');

Задача 6: Найти заказы за январь 2024

-- ПРАВИЛЬНО (работает с TIMESTAMP и DATE):
SELECT * FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01';

⚠️ Важно для собеседования:

-- НЕПРАВИЛЬНО использовать BETWEEN для дат с временем!
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';
-- Пропустит записи 2024-01-31 01:00:00 и позже!

Правило: Всегда используйте >= AND < с полуоткрытым интервалом для дат.

Задача 7: Найти товары с остатком от 10 до 100 штук

SELECT * FROM products
WHERE stock BETWEEN 10 AND 100;

Сортировка и ограничение

Задача 8: Вывести топ-10 самых дорогих товаров

SELECT * FROM products
ORDER BY price DESC
LIMIT 10;

Задача 9: Вывести пользователей, отсортированных по городу, затем по имени

SELECT * FROM users
ORDER BY city, name;

Задача 10: Вывести 5 последних заказов

SELECT * FROM users WHERE status = 'active';

Задача 11: Получить товары с 11 по 20 (пагинация)

SELECT * FROM products
ORDER BY id
LIMIT 10 OFFSET 10;

Работа с датами

Задача 12: Найти пользователей, зарегистрированных сегодня

SELECT * FROM users
WHERE created_at::date = CURRENT_DATE;

Задача 13: Найти заказы за последние 7 дней

SELECT * FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '7 days';

Задача 14: Извлечь год и месяц из даты

SELECT
   id,
   EXTRACT(YEAR FROM order_date) AS year,
   EXTRACT(MONTH FROM order_date) AS month
FROM orders;

Задача 15: Найти разницу в днях между датами

SELECT
   id,
   order_date,
   CURRENT_DATE - order_date AS days_ago
FROM orders;

Строковые функции

Задача 16: Извлечь имя и фамилию из полного имени

-- Извлечь первое слово (имя):
SELECT SPLIT_PART(name, ' ', 1) AS first_name FROM users;

-- Извлечь второе слово (фамилия):
SELECT SPLIT_PART(name, ' ', 2) AS last_name FROM users;

-- Оба вместе:
SELECT
   SPLIT_PART(name, ' ', 1) AS first_name,
   SPLIT_PART(name, ' ', 2) AS last_name
FROM users;

Задача 17: Привести email к нижнему регистру

SELECT LOWER(email) FROM users;

Задача 18: Найти длину имени

SELECT name, LENGTH(name) AS name_length FROM users;

Работа с NULL

Задача 20: Найти пользователей без указанного возраста

SELECT * FROM users WHERE age IS NULL;

-- НЕПРАВИЛЬНО (никогда не работает!):
SELECT * FROM users WHERE age = NULL;

Задача 21: Найти пользователей БЕЗ указанного email

-- Правильно:
SELECT * FROM users WHERE email IS NULL;

-- НЕПРАВИЛЬНО (никогда не работает!):
SELECT * FROM users WHERE email = NULL;

Задача 22: Посчитать пользователей С email и БЕЗ email

SELECT
   COUNT(*) AS total,
   COUNT(email) AS with_email,
   COUNT(*) - COUNT(email) AS without_email
FROM users;

Задача 23: Заменить NULL на значение по умолчанию

-- Для одного поля:
SELECT name, COALESCE(phone, 'Не указан') AS phone FROM users;

-- Для нескольких (первое не-NULL):
SELECT name, COALESCE(email, phone, 'Нет контакта') AS contact
FROM users;

-- Для чисел (в расчётах):
SELECT name, COALESCE(age, 0) AS age FROM users;

Задача 24: Избежать деления на ноль с NULLIF

-- Проблема: деление на 0
SELECT price / stock AS price_per_item FROM products;  -- ERROR при stock = 0

-- Решение: NULLIF превращает 0 в NULL, а x / NULL = NULL
SELECT price / NULLIF(stock, 0) AS price_per_item FROM products;

-- С обработкой результата:
SELECT COALESCE(price / NULLIF(stock, 0), 0) AS price_per_item FROM products;

Задача 25: Безопасный NOT IN (без проблем с NULL)

-- ОПАСНО: если в orders есть user_id = NULL, вернёт 0 строк!
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

-- БЕЗОПАСНО вариант 1: фильтровать NULL
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);

-- БЕЗОПАСНО вариант 2: NOT EXISTS (рекомендуется)
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Задача 26: Сортировка с NULL в нужное место

-- NULL в конец (при ASC):
SELECT * FROM users ORDER BY last_login NULLS LAST;

-- NULL в начало:
SELECT * FROM users ORDER BY last_login NULLS FIRST;

-- Альтернатива через COALESCE:
SELECT * FROM users ORDER BY COALESCE(last_login, '1970-01-01');

CASE WHEN

Задача 27: Категоризировать пользователей по возрасту

SELECT
   name,
   age,
   CASE
       WHEN age < 18 THEN 'Несовершеннолетний'
       WHEN age BETWEEN 18 AND 25 THEN 'Молодой'
       WHEN age BETWEEN 26 AND 45 THEN 'Взрослый'
       WHEN age BETWEEN 46 AND 65 THEN 'Старший'
       ELSE 'Пенсионер'
   END AS age_group
FROM users;

Задача 28: Перевести статус заказа с английского на русский (CASE)

Статусы хранятся как 'pending', 'completed', 'shipped', 'cancelled'. Выведи id, amount и новую колонку status_textс русским названием статуса.

SELECT
   id,
   amount,
   CASE status
       WHEN 'pending' THEN 'Ожидает обработки'
       WHEN 'completed' THEN 'Выполнен'
       WHEN 'shipped' THEN 'Отправлен'
       WHEN 'cancelled' THEN 'Отменён'
       ELSE 'Неизвестный статус'
   END AS status_text
FROM orders;

Задача 29: Применить скидку в зависимости от суммы заказа

SELECT
   id,
   amount,
   CASE
       WHEN amount >= 50000 THEN amount * 0.85  -- скидка 15%
       WHEN amount >= 20000 THEN amount * 0.90  -- скидка 10%
       WHEN amount >= 10000 THEN amount * 0.95  -- скидка 5%
       ELSE amount
   END AS discounted_amount
FROM orders;

Дополнительные задачи

Задача 30: DELETE vs TRUNCATE

-- DELETE — удаляет строки по одной, можно откатить, триггеры срабатывают
DELETE FROM logs WHERE created_at < '2023-01-01';

-- TRUNCATE — мгновенно очищает всю таблицу
TRUNCATE TABLE logs;
-- В PostgreSQL TRUNCATE можно откатить внутри транзакции!

-- TRUNCATE сбрасывает счётчик SERIAL/IDENTITY:
TRUNCATE TABLE logs RESTART IDENTITY;

Характеристика	DELETE	TRUNCATE
Скорость	Медленно	Мгновенно
WHERE	Да	Нет
Триггеры	Да	Нет
Откат	Да	Да (PostgreSQL)
VACUUM нужен	Да	Нет

Задача 31: UNION vs UNION ALL

-- UNION — убирает дубликаты (медленнее)
SELECT city FROM users
UNION
SELECT city FROM customers;

-- UNION ALL — оставляет дубликаты (быстрее)
SELECT city FROM users
UNION ALL
SELECT city FROM customers;

Задача 32: Разница между COUNT (*), COUNT (column), COUNT (DISTINCT column)

SELECT
   COUNT(*) AS total_rows,           -- все строки (включая NULL)
   COUNT(email) AS with_email,       -- строки где email NOT NULL
   COUNT(DISTINCT city) AS cities    -- уникальные города (без NULL)
FROM users;

-- Пример:
-- COUNT(*) = 3
-- COUNT(email) = 2  (если один email = NULL)
-- COUNT(DISTINCT city) = 1  (если все из одного города)

Шпаргалка

Задача	Ключевой приём
Фильтрация	WHERE + условия
Несколько значений	IN (...)
Диапазон дат	>= AND < (НЕ BETWEEN для TIMESTAMP!)
Шаблон	LIKE '%...%'
Сортировка	ORDER BY ... DESC
Ограничение	LIMIT N OFFSET M
NULL	IS NULL, COALESCE
Условная логика	CASE WHEN

Частые ошибки на собеседованиях

BETWEEN с датами — включает обе границы, опасно для TIMESTAMP
= NULL — не работает! Используйте IS NULL
NOT IN с NULL — возвращает 0 строк если подзапрос содержит NULL
SELECT * — не используйте в production коде
LIKE без % — не делает поиск по подстроке