137.85K

Category:

database

Подзапросы. Общие табличные выражения (CTE)

1.

Подзапросы. Общие
табличные выражения (CTE)
• В каком месте основного запроса могут
встречаться подзапросы.
• Простые вложенные подзапросы.
• Общие табличные выражения (CTE).
• Коррелирующие вложенные подзапросы.
• Реализация операции деления в SQL.

2.

Структура запроса
1) Select
2) From
3) Where
4) Group by
5) Having

3.

1. Подзапросы в предложении Select
Задача
Найти разницу между средними значениями цены
портативных компьютеров и ПК, то есть насколько в
среднем портативный компьютер стоит дороже, чем ПК.

4.

5.

2. Подзапросы в предложении From
Подзапрос, возвращающий множество строк и
содержащий несколько столбцов может
использоваться в предложении FROM.
Задача
Вывести производителя, тип, модель и частоту
процессора для Портативных компьютеров,
частота процессора которых превышает 600 МГц.

6.

2. Подзапросы в предложении From
Решение без подзапросов
SELECT maker, 'laptop' AS type, l.model, speed
FROM laptop l, product p
WHERE speed > 600 and l.model = p.model

7.

2. Подзапросы в предложении From
Использование подзапросов, позволяет ограничить набор столбцов и/или
строк при выполнении операции соединения таблиц.
Задача
Вывести производителя, тип, модель и частоту процессора для Портативных
компьютеров, частота процессора которых превышает 600 МГц.
SELECT prod.maker, lap.*
FROM
(SELECT 'laptop' AS type, model, speed FROM laptop WHERE speed > 600 ) AS
lap
INNER JOIN
(SELECT maker, model FROM product ) AS prod ON lap.model = prod.model;
maker
type
model
speed
B
laptop
1750
750
A
laptop
1752
750

8.

Задача 55. Для каждого класса определите год, когда был спущен
на воду первый корабль этого класса. Если год спуска на воду
головного корабля неизвестен, определите минимальный год
спуска на воду кораблей этого класса. Вывести: класс, год.
SELECT c.class, min_launched
FROM classes c LEFT JOIN
(SELECT class, MIN(launched) min_launched
FROM ships
GROUP BY class ) AS t
ON c.class = t.class

9.

3. Подзапросы в предложении Where / Having
Вложенные подзапросы
• Вложенный подзапрос - это подзапрос, заключенный в
круглые скобки и вложенный в WHERE (HAVING) фразу
предложения SELECT или других предложений,
использующих WHERE фразу.
• Вложенный подзапрос создан для того, чтобы при отборе
строк таблицы, сформированной основным запросом,
можно было использовать данные из других таблиц

10.

3. Подзапросы в предложении Where / Having
3.1 Подзапрос, который возвращает единственное
значение
Задача. Найти модели и цены ПК, стоимость которых
превышает минимальную стоимость портативных
компьютеров
SELECT DISTINCT model, price
FROM PC
WHERE price > (SELECT MIN(price) FROM Laptop );

11.

Функции в подзапросе
10
Найдите модели принтеров, имеющих самую высокую цену.
Вывести: model, price
SELECT DISTINCT model, price
FROM Printer
WHERE price = (SELECT MAX(price) FROM Printer )
18
Найдите производителей самых дешевых цветных принтеров.
Вывести: maker, price
select distinct maker, price
from Product join Printer on Product.model=Printer.model
where price=(SELECT MIN(price) AS Min_price
FROM Printer
where color='y') and color='y'

12.

3. Подзапросы в предложении Where / Having
3.2 Подзапрос, который возвращает множество
значений
SELECT DISTINCT model, price
FROM PC
WHERE price = any (SELECT price FROM Laptop);

13.

Задача. Вывести список производителей, выпускающих
модели ПК по цене больше 500.
select distinct maker
from product
where model in (select model from PC where price >500)
-- Другое решение
select distinct maker
from product p , pc
where p.model=pc.model and price >500
При выполнении второго запроса система должна
одновременно обрабатывать данные из двух таблиц, тогда
как в варианте с подзапросами эти таблицы
обрабатываются поочередно.

14.

Использование одной и той же таблицы во внешнем
и вложенном подзапросе
Задача
Выдать список производителей, которые производят хотя
бы один тип продуктов, выпускаемый производителем E.
Решение
SELECTDISTINCT maker
FROM product
WHERE type IN (SELECT type
FROM product
WHERE maker ='E')
Результат: A, B, D, E

15.

Простые вложенные подзапросы
• Простые вложенные подзапросы
используются для представления
множества значений, исследование
которых должно осуществляться в какомлибо предикате IN
• Рассмотрены были выше

16.

• Простые вложенные подзапросы
обрабатываютcя системой "снизу вверх".
• Первым обрабатывается вложенный
подзапрос самого нижнего уровня.
• Множество значений, полученное в
результате его выполнения, используется
при реализации подзапроса более высокого
уровня и т. д.

17.

Общие табличные выражения (CTE)
Общее табличное выражение (от англ. Common Table
Expressions, CTE) - временный именованный набор данных,
полученный из простого запроса и определённый в области
действия операций SELECT, INSERT, UPDATE или DELETE.
Задача 24
Перечислите номера моделей любых типов, имеющих
самую высокую цену по всей имеющейся в базе данных
продукции
Вспомним задачу 10
Найдите модели принтеров, имеющих самую высокую цену.
Вывести: model, price

18.

Задача 24
Перечислите номера моделей любых типов,
имеющих самую высокую цену по всей
имеющейся в базе данных продукции
Задача 10
Найдите модели принтеров, имеющих самую
высокую цену. Вывести: model, price

19.

Задача 10 (вывод только моделей)
Найдите модели принтеров, имеющих самую высокую цену. Вывести:
model.
SELECT DISTINCT model
FROM Printer
WHERE price >= ALL(SELECT price FROM Printer)
Задача 24
Перечислите номера моделей любых типов, имеющих самую высокую
цену по всей имеющейся в базе данных продукции
SELECT DISTINCT model
FROM ALL_MODEL
WHERE price >=ALL (SELECT price FROM ALL_MODEL)

20.

SELECT DISTINCT model FROM (
SELECT model, price FROM PC where price is not null
UNION
SELECT model, price FROM Laptop where price is not null
UNION
SELECT model, price FROM Printer where price is not null
) all_price
WHERE price > = ALL(SELECT price FROM (
SELECT model, price FROM PC where price is not null
UNION
SELECT model, price FROM Laptop where price is not null
UNION
SELECT model, price FROM Printer where price is not null
) all_price )

21.

WITH all_price AS
(
SELECT model, price FROM PC where price is not null
UNION
SELECT model, price FROM Laptop where price is not null
UNION
SELECT model, price FROM Printer where price is not null
)
SELECT DISTINCT model
FROM all_price
WHERE price >= all(select price from all_price)
Общие табличные выражения позволяют существенно
уменьшить объем кода, если многократно приходится
обращаться к одним и тем же производным таблицам.

22.

Задача
Найти максимальную сумму прихода/расхода
среди всех 4-х таблиц базы данных
"Вторсырье", а также тип операции, дату и
пункт приема, когда и где она была
зафиксирована.

23.

SELECT inc AS max_sum, type, date, point
FROM
( SELECT inc, 'inc' type, date, point FROM Income
UNION ALL
SELECT inc, 'inc' type, date, point FROM Income_o
UNION ALL
SELECT out, 'out' type, date, point FROM Outcome_o
UNION ALL
SELECT out, 'out' type, date, point FROM Outcome ) X
WHERE inc >= ALL( SELECT inc FROM Income
UNION ALL SELECT inc FROM Income_o
UNION ALL SELECT out FROM Outcome_o
UNION ALL SELECT out FROM Outcome );
Использованы две производные таблицы, полученные
через объединение четырех таблиц

24.

WITH Inc_Out AS (
SELECT inc, 'inc' type, date, point FROM Income
UNION ALL
SELECT inc, 'inc' type, date, point FROM Income_o
UNION ALL
SELECT out, 'out' type, date, point FROM Outcome_o
UNION ALL
SELECT out, 'out' type,date, point FROM Outcome )
SELECT inc AS max_sum, type, date, point
FROM Inc_Out
WHERE inc >= ALL ( SELECT inc FROM Inc_Out);
max_sum
type
date
point
18000.0000
inc
2001-1002 00:00:00.000
3

25.

WITH Inc_Out(m_sum, type, date, point) AS (
SELECT inc, 'inc' type, date, point FROM Income
UNION ALL
SELECT inc, 'inc' type, date, point FROM Income_o
UNION ALL
SELECT out, 'out' type, date, point FROM Outcome_o
UNION ALL
SELECT out, 'out' type,date, point FROM Outcome )
SELECT 'max' min_max,* FROM Inc_Out
WHERE m_sum >= ALL( SELECT m_sum FROM Inc_Out)
UNION ALL
SELECT 'min', * FROM Inc_Out
WHERE m_sum <= ALL( SELECT m_sum FROM Inc_Out);
min_max
m_sum
type
date
point
max
18000.0000
inc
2001-1002 00:00:00.000
3
min
1150.0000
out
2001-0914 00:00:00.000
3

26.

Использование СТЕ для решения задачи «снизу вверх»
Задача. БД Аэрофлот.
Найти пассажиров, которые летали не менее, чем тремя разными
авиакомпаниями, при этом одинаковое число раз.
With psg_comp as(
Select id_psg, trip_no, id_comp
From trip as t join pass_in_trip as pit on t.trip_no=pit.trip_no),
Psg_comp_count as (
Select id_psg, id_comp, count(*) n
From psg_comp
Group by id_psg, id_comp),

27.

Использование СТЕ для решения задачи «снизу вверх»
Задача. БД Аэрофлот.
Найти пассажиров, которые летали не менее, чем тремя разными авиакомпаниями,
при этом одинаковое число раз.
With PSG_COMP as(
Select id_psg, id_comp
From trip as t join pass_in_trip as pit on t.trip_no=pit.trip_no),
PSG_COMP_N as (
Select id_psg, id_comp, count(*) n
From psg_comp
Group by id_psg, id_comp),
PSG as (
Select id_psg
From PSG_COMP_N
Group by id_psg
Having count(id_comp)>=3 and max(n)=min(n))
Select name
from passenger
Where id_psg in (select id_psg from PSG)

28.

Использование СТЕ для решения задачи «снизу вверх»
Задача. БД Аэрофлот.
Найти пассажиров, которые летали не менее, чем тремя разными
авиакомпаниями, при этом одинаковое число раз.
With PSG_COMP_N as (
Select id_psg, id_comp , count (*) N
From trip as t join pass_in_trip as pit on t.trip_no=pit.trip_no
Group by id_psg, id_comp),
PSG as ( Select id_psg
From PSG_COMP_N
Group by id_psg
Having count(id_comp)>=3 and max(n)=min(n))
Select name
from passenger
Where id_psg in (select id_psg from PSG)

29.

Использование СТЕ для решения задачи «снизу вверх»
Задача. БД Аэрофлот.
Найти пассажиров, которые летали не менее, чем тремя разными авиакомпаниями, при
этом одинаковое число раз.
Select name
from passenger
Where id_psg in
( Select id_psg
From ( Select id_psg, id_comp, count(*) n
From trip as t join pass_in_trip as pit on t.trip_no=pit.trip_no
Group by id_psg, id_comp
) as PSG_COMP_N
Group by id_psg
Having count(id_comp)>=3 and max(n)=min(n))
)

30.

Коррелированные вложенные
подзапросы
Выдать названия поставщиков ПК по цене
меньше 500.
SELECT
FROM
WHERE
DISTINCT maker
product
500 > any (SELECT
FROM
pc
WHERE
pc.model=product.model)
price

31.

Примеры использования одной и той же таблицы во
внешнем подзапросе и коррелированном вложенном
подзапросе.
Выдать список производителей, выпускающих только
один тип продуктов.
SELECT
DISTINCT x.maker
FROM product X
WHERE
X.maker NOT IN
(
SELECT
Y.maker
FROM product Y
WHERE
Y.type <> X.type )
Результат: C, D

32.

Выдать список производителей, выпускающих более
одного типа продуктов.
SELECT
DISTINCT x.maker
FROM product X
WHERE
X.maker IN
(
SELECT
Y.maker
FROM product Y
WHERE
Y.type <> X.type )
Результат: A, B, E

33.

Запросы, использующие EXISTS
Квантор EXISTS (существует) - понятие,
заимствованное из формальной логики. В языке
SQL предикат с квантором существования
представляется выражением EXISTS (SELECT *
FROM ...).
Такое выражение считается истинным только
тогда, когда результат вычисления "SELECT *
FROM ..." является непустым множеством, т.е.
когда существует какая-либо запись в таблице,
указанной во фразе FROM подзапроса, которая
удовлетворяет условию WHERE подзапроса.

34.

Выдать названия поставщиков ПК по цене
меньше 500.
SELECT
FROM
WHERE
DISTINCT maker
product
EXISTS (SELECT price
FROM
pc
WHERE
pc.model=product.model and price<500)

35.

Использование коррелированного подзапрос в предложении Select
Вывести названия квадратов и сумму использованной для них краски.
1 способ.
Select q_name, sum(b_vol) s
From utq join utb on q_id=b_q_id
Group by q_id, q_name
2 способ
Select (select q_name from utq where q_id=b_q_id), sum(b_vol) s
From utb
Group by b_q_id

36.

• Запросы с коррелированными вложенными
подзапросами обрабатываются системой в обратном
порядке.
• Сначала выбирается первая строка рабочей таблицы,
сформированной основным запросом, и из нее
выбираются значения тех столбцов, которые
используются во вложенном подзапросе (вложенных
подзапросах).
• Если эти значения удовлетворяют условиям вложенного
подзапроса, то выбранная строка включается в результат.
• Затем выбирается вторая строка и т.д., пока в результат
не будут включены все строки, удовлетворяющие
вложенному подзапросу (последовательности
вложенных подзапросов).

37.

Реализация операции деления в SQL
Определить производителей, которые выпускают модели всех типов
(схема "Компьютерная фирма").
Product[maker, type] DIVIDE BY Product[type]
Но увы! Операция деления реляционной алгебры в SQL НЕ РЕАЛИЗОВАНА.

38.

Реализация операции деления в SQL
Определить производителей, которые выпускают модели всех типов (схема
"Компьютерная фирма").
1 способ реализации: группировка.
Идея: если использовать тот факт, что, согласно описанию предметной области,
типов продукции всего три, то мы можем выполнить группировку по
производителю и подсчитать количество уникальных типов.
Затем мы отберем только тех производителей, у которых это число равно трем.
SELECT maker
FROM Product
GROUP BY maker
HAVING COUNT(DISTINCT type) = 3
А если число типов
продукции произвольно?
SELECT maker
FROM Product
GROUP BY maker
HAVING COUNT(DISTINCT type) = (SELECT COUNT(DISTINCT type) FROM Product)

39.

Реализация операции деления в SQL
Определить производителей, которые выпускают модели всех типов (схема
"Компьютерная фирма").
2 способ реализации: разность.
Идея: если взять операцию разности ВСЕХ имеющихся типов моделей и типов у
конкретного производителя, то результирующая выборка не должна содержать строк.
SELECT DISTINCT maker
FROM Product Pr1
WHERE 0 = (SELECT COUNT(*) FROM
(SELECT type FROM Product
EXCEPT
SELECT type FROM Product Pr2
WHERE Pr2.maker = Pr1.maker) X
)
или
SELECT DISTINCT maker
FROM Product Pr1
WHERE type = ALL
(SELECT type FROM Product
EXCEPT
SELECT type FROM Product Pr2
WHERE Pr2.maker = Pr1.maker
)
Истинностное значение предиката ALL есть
TRUE, если подзапрос не возвращает строк
Для искомых производителей список типов в предикате ALL
будет пуст (предикат равен TRUE).
В остальных случаях он будет содержать типы моделей,
отсутствующие у производителя из внешнего запроса,
поэтому операция сравнения (равенство "=") для всех его
моделей даст FALSE.

40.

Реализация операции деления в SQL
Определить производителей, которые выпускают модели всех типов
(схема "Компьютерная фирма").
3 способ реализации: существование.
Идея: не должно существовать такого типа продукции, которого бы не было
у искомого производителя.
SELECT DISTINCT maker
FROM Product Pr1
WHERE NOT EXISTS
(SELECT type
FROM Product
WHERE type NOT IN
(SELECT type
FROM Product Pr2
WHERE Pr1.maker = Pr2.maker
)
)

41.

Реализация операции деления в SQL
Задача 71. Найти тех производителей ПК, все модели ПК которых
имеются в таблице PC.
1 способ: через группировку.
SELECT maker
FROM product p LEFT JOIN pc ON pc.model = p.model
WHERE type = 'PC'
GROUP BY maker
Having COUNT(p.model) =COUNT(pc.model)

42.

Реализация операции деления в SQL
Задача 71. Найти тех производителей ПК, все модели ПК которых
имеются в таблице PC.
2 способ: через разность.
SELECT distinct maker
FROM Product Pr
WHERE type= 'PC' and 0 =
(SELECT COUNT(*)
FROM
(SELECT model FROM Product WHERE Pr.maker = Product.maker and type= 'PC'
except
SELECT model FROM pc) x
)

43.

Реализация операции деления в SQL
Задача 71. Найти тех производителей ПК, все модели ПК которых
имеются в таблице PC.
3 способ: через проверку существования.
SELECT distinct maker
FROM Product Pr
WHERE type= 'PC' and NOT EXISTS
(SELECT model
FROM Product
WHERE Pr.maker = Product.maker and type= 'PC' and model NOT IN
(SELECT model
FROM pc
)
)

44.

Задача 39
Найдите корабли, "сохранившиеся для будущих
сражений"; т.е. выведенные из строя в одной битве
(damaged), они участвовали в другой, произошедшей
позже.
Будем использовать коррелирующие подзапросы, общие
табличные выражения, явные операции соединения,
предикат с использованием EXISTS
Classes (class, type, country, numGuns, bore, displacement)
Ships (name, class, launched)
Battles (name, date)
Outcomes (ship, battle, result)

45.

select ship, date, result
from Outcomes inner join Battles on name =battle
ship
date
result
Bismarck
1941-05-25 00:00:00.000
sunk
California
1942-11-15 00:00:00.000
damaged
CAlifornia
1944-10-25 00:00:00.000
ok
Duke of York
1943-12-26 00:00:00.000
ok
Fuso
1944-10-25 00:00:00.000
sunk
Hood
1941-05-25 00:00:00.000
sunk
King George V
1941-05-25 00:00:00.000
ok
Kirishima
1942-11-15 00:00:00.000
sunk
Prince of Wales
1941-05-25 00:00:00.000
damaged
Rodney
1941-05-25 00:00:00.000
OK
Schamhorst
1943-12-26 00:00:00.000
sunk
South Dakota
1942-11-15 00:00:00.000
damaged
Tennessee
1944-10-25 00:00:00.000
ok
Washington
1942-11-15 00:00:00.000
ok
West Virginia
1944-10-25 00:00:00.000
ok
Yamashiro
1944-10-25 00:00:00.000
sunk

46.

SELECT DISTINCT ship
FROM (select ship, date, result
from Outcomes join Battles on name = battle ) c
WHERE result = 'damaged' AND
EXISTS
(SELECT ship FROM (select ship, date, result
from Outcomes join Battles on name = battle ) cc
WHERE cc.ship = c.ship AND cc.date > c.date)
Коррелирующий подзапрос (как выполняется?)

47.

WITH S AS
(select ship, date, result
from Outcomes join Battles on name = battle )
SELECT DISTINCT ship
FROM S S1
WHERE result = 'damaged' AND
EXISTS (SELECT ship
FROM S
WHERE ship = S1.ship AND date > S1.date)

48.

Задание: 51
Найдите названия кораблей, имеющих
наибольшее число орудий среди всех имеющихся
кораблей такого же водоизмещения (учесть
корабли из таблицы Outcomes).
• Classes (class, type, country, numGuns, bore,
displacement)
• Ships (name, class, launched)
Battles (name, date)
• Outcomes (ship, battle, result)

49.

50.

WITH korabl AS (
SELECT name, class
FROM Ships
UNION
SELECT ship, NULL
FROM Outcomes
),
korabl_klass AS (
SELECT name, c.class, numGuns, displacement
FROM korabl AS t
INNER JOIN Classes AS c
ON c.class IN (t.class, t.name)
)
SELECT DISTINCT name
FROM korabl_klass AS kk
WHERE numGuns > = ALL (SELECT ………..
FROM …………………..
WHERE …..=……………..
)

51.

WITH korabl AS (
SELECT name, class
FROM Ships
UNION
SELECT ship, NULL
FROM Outcomes
),
korabl_klass AS (
SELECT name, c.class, numGuns, displacement
FROM korabl AS t
INNER JOIN Classes AS c
ON c.class IN (t.class, name)
)
SELECT DISTINCT name
FROM korabl_klass AS kk
WHERE numGuns > = ALL (SELECT numGuns
FROM korabl_klass
WHERE …=……
)

52.

WITH korabl AS (
SELECT name, class
FROM Ships
UNION
SELECT ship, NULL
FROM Outcomes
),
korabl_klass AS (
SELECT name, c.class, numGuns, displacement
FROM korabl AS t
INNER JOIN Classes AS c
ON c.class IN (t.class, t.name)
)
SELECT DISTINCT name
FROM korabl_klass AS kk
WHERE numGuns > = ALL (SELECT numGuns
FROM korabl_klass
WHERE displacement = kk.displacement
)

53.

name
Bismarck
California
Iowa
Missouri
Musashi
New Jersey
North Carolina
Ramillies
Revenge
Royal Oak
Royal Sovereign
South Dakota
Tennessee
Washington
Wisconsin
Yamato

English Русский Rules