БЛОК 1. ВОПРОСЫ Для самопроверки Postgresql

Тема 1. Базовый синтаксис

  1. Напишите запрос, который выводит список клиентов (CompanyName) и общее количество их заказов. Отсортируйте по убыванию количества заказов.

  2. Выведите список заказов (OrderID, OrderDate, CustomerID) за 1997 год, отсортированный по дате от новых к старым.

  3. Найдите 5 сотрудников (Employees), которые обработали больше всего заказов. Выведите их имена (FirstName, LastName) и количество заказов.

  4. Вывести список клиентов и их менеджеров (Employees), которые закреплены за ними. Если у клиента нет менеджера – показать NULL.

  5. Найти общую сумму продаж (Quantity * UnitPrice) по каждому сотруднику за 1997 год. Вывести сотрудника и сумму.

  6. Вывести список товаров (ProductName), цена которых > 50, но которые никогда не заказывались (нет записей в Order Details).

  7. Вывести количество заказов по странам доставки (ShipCountry), исключив страны с количеством заказов < 10.

Тема 2. Подзапросы

  1. Найдите товары, цена которых выше средней цены всех товаров в их категории.

  2. Вывести список клиентов, у которых сумма всех заказов больше средней суммы заказов по всем клиентам.

  3. Найти заказы, которые были доставлены с задержкой (ShippedDate > RequiredDate). Выведите OrderID, дату заказа, требуемую дату и фактическую дату доставки. Отсортируйте по максимальной задержке.

  4. Вывести клиентов (CompanyName), у которых есть хотя бы один заказ с общей суммой (с учетом скидки) больше 5000$.

  5. Найти категории, в которых количество товаров больше среднего количества товаров по всем категориям.

Тема 3. CTE (WITH … AS)

  1. Используя CTE, посчитайте суммарную выручку по каждому месяцу 1997 года, а затем найдите месяц с максимальной выручкой.

  2. С помощью CTE постройте витрину: клиент → сумма его заказов. Затем проранжируйте клиентов по сумме заказов и выведите топ-10.

  3. Используя CTE, найдите клиентов, которые сделали более 5 заказов в 1997 году, и их средний чек.

Тема 4. Оконные функции

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

  2. Для каждого товара покажите его цену и цену предыдущего товара (по алфавиту) и разницу между ними.

  3. Для каждого клиента покажите его последний заказ (OrderID и OrderDate) и количество дней между последним и предпоследним заказом.

  4. Проранжируйте сотрудников по общей сумме продаж, которые они принесли в 1997 году, используя RANK() и DENSE_RANK().

  5. Для каждого заказа посчитайте накопительную сумму (кумулятивную) по дате заказа (от самых ранних к поздним).

Тема 5. Работа с датами

  1. Вывести список заказов за последние 30 дней от текущей даты (используйте CURRENT_DATE).

  2. Вывести количество заказов по кварталам 1997 года.

  3. Найдите все заказы, у которых ShippedDate приходится на выходные дни (суббота или воскресенье). Используйте функцию EXTRACT или DATE_PART.

  4. Рассчитайте среднюю продолжительность доставки (в днях) по каждому месяцу заказа (OrderDate - ShippedDate). Выведите месяц заказа и среднюю задержку.

Тема 6. Оптимизация и типы данных

  1. Предположим, что у нас есть таблица Orders с 10 млн записей. Какой индекс вы бы создали, чтобы ускорить запрос: SELECT * FROM Orders WHERE CustomerID = 'ALFKI' AND OrderDate BETWEEN '1997-01-01' AND '1997-12-31'?

  2. Почему в таблице Products для поля UnitPrice лучше использовать DECIMAL(10,2), а не FLOAT?

  3. Как мы можем увидеть план выполнения запроса в PostgreSQL? Что показывает параметр «cost»?