Семинар 4. Сложные запросы, подзапросы. Целостность.

1. Сложные запросы и подзапросы

1.1. Структура запроса

Порядок написания запроса

SELECT
  [ALL | DISTINCT [ON (expression [, ...] )] ]
  [* | expression [AS output_name] [, ...] ]
[FROM from_item [, ...] ]
[WHERE condition]
[GROUP BY grouping_element [, ...]]
[HAVING condition]
[ORDER BY expression [ASC | DESC | USING operator] [NULLS {FIRST | LAST}]
     [, ...]]
[LIMIT {count | ALL}]
[OFFSET start [ROW | ROWS]]
[FETCH {FIRST | NEXT} [count] {ROW | ROWS} ONLY]

1.2 Ключевое слово WITH

WITH предоставляет способ записывать дополнительные операторы для применения в больших запросах. Эти операторы, которые также называют общими табличными выражениями (Common Table Expressions, CTE), можно представить как определения временных таблиц, существующих только для одного запроса. Более подробно про СТЕ будет на следующих семинарах. Пример:

WITH 
    regional_sales AS (
        SELECT 
            region, 
            SUM(amount) AS total_sales
        FROM 
            orders
        GROUP BY 
            region
    ), 
    top_regions AS (
        SELECT 
            region
        FROM 
            regional_sales
        WHERE 
            total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
   )
SELECT 
    region,
    product,
    SUM(quantity) AS product_units,
    SUM(amount) AS product_sales
FROM 
    orders
WHERE 
    region IN (SELECT region FROM top_regions)
GROUP BY 
    region, 
    product;

1.3 Подзапросы

Подзапрос — запрос, содержащийся в другом SQL-запросе. Запрос, содержащий другой подзапрос, называется содержащим выражением.

  • Подзапрос всегда заключен в круглые скобки и обычно выполняется до содержащего выражения.
  • Подзапросы могут вкладываться друг в друга.
  • В SELECT’e подзапросы можно использовать во всех разделах, кроме GROUP BY.

Классификация подзапросов:

  1. По взаимодействию с содержащим выражением:
    • Связанные (т.е. ссылающиеся на столбцы основного запроса):
      • Для написания таких запросов полезно использование алиасов. (SELECT … AS T)
      • Для случаев, когда в основном запросе и в подзапросе используется одна и та же таблица, использование алиасов обязательно!
      • Выполняются для каждой строки содержащего выражения.
    • Несвязанные (т.е. полностью самодостаточные и не зависящие от основного запроса) — выполняются перед выполнением содержащего выражения.
  2. По результату выполнения:
    • Скалярные (1 столбец и 1 строка)
    • Нескалярные

Использование подзапросов:

Примеры использования подзапросов в DELETE, UPDATE и INSERT:

DELETE FROM Classes
WHERE NOT EXISTS (SELECT * FROM Marks WHERE Marks.class_id = Classes.id);

UPDATE Classes
   SET featured = 1
 WHERE (SELECT count(*) FROM marks WHERE class_id = id) > 10;

INSERT INTO BestStudents2024(`name`, `surname`)
SELECT name, surname FROM Students
WHERE (SELECT AVG(mark) FROM Marks WHERE Students.id = Marks.student_id) > (SELECT AVG(mark) FROM Marks);

1.4 Предикаты (для подзапросов вида 1 столбец и несколько строк)

  • EXISTS — Значением условия EXISTS является TRUE в том и только в том случае, когда мощность таблицы-результата подзапроса больше нуля, иначе значением условия является FALSE:
SELECT SupplierName
  FROM Suppliers
WHERE EXISTS(
        SELECT ProductName
          FROM Products
         WHERE SupplierId = Suppliers.supplierId
           AND Price < 20);
  • IN — Предикат IN для подзапросов работает так же, как и для обычных запросов (проверка наличия значения в списке):
SELECT emp_id 
     , fname
     , lname
     , title
FROM employee
WHERE emp_id IN(
        SELECT superior_emp_id
          FROM employee);
  • ALL — TRUE, если результат подзапроса пуст или значение предиката равно TRUE для каждой строки подзапроса; если хотя бы что-то FALSE, то вернет FALSE, во всех остальных случаях вернет UNKNOWN:
SELECT EMP_NO
  FROM EMP
 WHERE DEPT_NO = 65
   AND EMP_SAL >= ALL(
        SELECT EMP1.EMP_SAL
        FROM EMP AS EMP1
        WHERE EMP.DEPT_NO = EMP1.DEPT_NO);
  • ANY — FALSE, если результат подзапроса пуст или значение условия равно FALSE для каждой строки подзапроса; если хотя бы что-то TRUE, то вернет TRUE, в остальных случаях вернет UNKNOWN:
SELECT EMP_NO
  FROM EMP
WHERE DEPT_NO = 65
  AND EMP_SAL > ANY(
        SELECT EMP1.EMP_SAL
        FROM EMP AS EMP1
        WHERE EMP.DEPT_NO = EMP1.DEPT_NO);
  • CREATE TABLE AS — создаёт таблицу и наполняет её данными, полученными в результате выполнения SELECT. Столбцы этой таблицы получают имена и типы данных в соответствии со столбцами результата SELECT (хотя имена столбцов можно переопределить, добавив явно список новых имен столбцов).
CREATE TABLE NEW_TABLE AS
    SELECT *
      FROM OLD_TABLE;

CREATE TABLE AS напоминает создание представления, но на самом деле есть значительная разница: эта команда создает новую таблицу и выполняет запрос только раз, чтобы наполнить таблицу начальными данными. Последующие изменения в исходных таблицах запроса в новой таблице отражаться не будут. С представлением, напротив, определяющая его команда SELECT выполняется при каждой выборке из него.

3. Практическое задание (сложные запросы + подзапросы)

  1. С помощью скалярного подзапроса найти имена преподавателей, которые получили разово минимальную выплату за все время.
  2. С помощью скалярного подзапроса найти имена преподавателей, у которых выплата по тому или иному типу была максимальной. Вывести имя преподавателя и тип выплаты, используя case или decode (1 – выплата за семинарские занятия, 2 – выплата за лекционные занятия, 3 – премиальная часть);
  3. С помощью подзапроса и предиката IN вывести имена преподавателей, тип и сумму выплат по каждому типу за все время работы, при условии, что у преподавателя была выплата за лекционные занятия.
  4. С помощью EXISTS вывести имена преподавателей, тип и сумму выплат по каждому типу за все время работы, при условии, что у преподавателя была выплата за лекционные занятия;
  5. С помощью аналитической функции COUNT найти количество выплат по каждому преподавателю за каждое число; выведите также размер каждой выплаты.
  6. С помощью аналитической функции SUM найти общий размер выплат по каждому преподавателю за каждое число; выведите также размер каждой выплаты.
  7. С помощью аналитической функции SUM и сортировки найти суммарные выплаты по каждому преподавателю за каждое число (по нарастанию).