Главная Для студентов Базы данных 4. Упражнения по SQL

4.5. Решение упр.3.

Основная задача

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

Анализ задачи.

В каждом городе изготавливается некоторое множество деталей. Мы должны включить поставщика в результирующий список, если для него найдется город такой, что:

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

Визуальный анализ данных

Попытаемся проанализировать данные и определить ожидаемый результат. Прежде всего, посмотрим

в каких городах изготавливаются детали
                 select distinct town from p
Получаем
                 Лондон
                 Париж
                 Рим

в каких городах проживают поставщики с минимальным и максимальным рейтингом
             select * from s order by rg
Получаем
S2    Джонс      10   Париж
S1    Смит       20   Лондон
S4    Кларк       20   Лондон
S3    Блейк       30   Париж
S5   Адамс     30    Афины

Поскольку в Париже проживают поставщики с минимальным и максимальным рейтингом, проверить нужно Лондон и Рим. Посмотрим, какие детали изготавливаются

в Лондоне

в Риме

select n_det from p where town='Лондон'

Результат

select n_det from p where town='Рим'

Результат

Теперь нужно проверить есть ли поставщик, который поставляет

все три детали P1, P4, P6 для какого-то одного изделия

или

деталь P3 для какого-нибудь изделия

select distinct n_post,n_det,n_izd from spj
where n_det in ('P1','P4','P6')
order by n_izd,n_post

Результат

S1	P1	J1
S3	P4	J2
S5	P6	J2
S4	P6	J3
S1	P1	J4
S5	P1	J4
S5	P4	J4
S5	P6	J4
S4	P6	J7

Очевидно, что нашим условиям удовлетворяет поставщик S5

select distinct n_post from spj
where n_det='P3'
order by n_post

Результат

Объединив результаты, получаем, что наш ненаписанный запрос должен вернуть поставщиков S2, S3, S5
Теперь выделим подзадачи.

Подзадача 1.

Первая подзадача очевидна - получение списка номеров поставщиков.

Select a.n_post from a s

Теперь реализуем условие отбора.

Подзадача 2.

Определим города, где изготавливаются детали

select distinct town from p

Далее определим города, где проживают поставщики с минимальным и максимальным рейтингом. Перед этим нужно найти собственно минимальный и максимальный рейтинг. Таким образом

Подзадача 3.

Определить максимальный рейтинг

select max(b.rg) from s b

Подзадача 4.

Определить минимальный рейтинг

select min(c.rg) from s c

Подзадача 5.

Получить список городов, где проживают поставщики с минимальным и максимальным рейтингом

select distinct d.town from s d
where d.rg=(select min(b.rg) from s b)
or
d.rg=(select max(c.rg) from s c)

Подзадача 6.

Используя решение подзадач 2 и 5, получим список городов, где производятся детали, но не проживают поставщики с минимальным и максимальным рейтингом. Для этого используем оператор Except (исключение), который возвращает записи входящие в первый select и не входящие во второй.

select distinct town from p
Except
select distinct d.town from s d
                   where d.rg=(select min(b.rg) from s b)
                         or
                         d.rg=(select max(c.rg) from s c)

Подзадача 7.

Получим перечень деталей, производимых в каком-либо городе (например, в Лондоне) и не поставляемых некоторым поставщиком (например, S2) для некоторого изделия (например, J1)

select n_det
from p
where p.n_det in (select p1.n_det
                           from p p1
                           where p1.town='Лондон')
           and
           not p.n_det in (select distinct spj.n_det
                                 from spj
                                 where spj.n_post='S2' and spj.n_izd='J1')

Если таких деталей нет (запрос не возвращает ни одной записи), это значит, что все детали, производимые в Лондоне, поставляются поставщиком S2 для изделия J1.

Подзадача 8.

Получим перечень изделий, для которых запрос из подзадачи 7 будет пустым. Иначе получим перечень изделий, для которых поставщик S2, поставляет все детали изготавливаемые в Лондоне.

Select j.n_izd
from j
where not exists( select n_det
                               from p
                               where p.n_det in (select p1.n_det
                                                        from p p1
                                                        where p1.town='Лондон')
                                          and
                                          not p.n_det in (select distinct spj.n_det
                                                               from spj
                                                               where spj.n_post='S2' and spj.n_izd=j.n_izd))

Ключевой момент: заменяем условие spj.n_izd='J1' на spj.n_izd=j.n_izd.

Подзадача 9.

Получим перечень городов, для которых запрос из подзадачи 8 будет непустым. Иначе получим перечень городов, для которых поставщик S2, поставляет все детали изготавливаемые в городе для некоторого изделия.

Select tw.town
                        from p tw
                         where exists (Select j.n_izd
                                                 from j
                                                 where not exists (Select n_det
                                                                               from p
                                                                               where p.n_det in (select p1.n_det
                                                                                                         from p p1
                                                                                                         where p1.town=tw.town )
                                                                               and
                                                                                not p.n_det in (select distinct spj.n_det
                                                                                                         from spj
                                                                                                         where spj.n_post=='S2' and spj.n_izd=j.n_izd)
                                                                              )
                                                 )

Ключевой момент: заменяем условие p1.town='Лондон' на p1.town=tw.town.

Подзадача 10.

Получим перечень поставщиков, для которых запрос из подзадачи 9 будет непустым. Иначе получим перечень поставщиков, поставивших для одного и того же изделия все детали , изготавливаемые в (пока что) любом городе.

Select s.n_post
from s
where exists (Select tw.town
                        from p tw
                         where exists (Select j.n_izd
                                                 from j
                                                 where not exists (Select n_det
                                                                               from p
                                                                               where p.n_det in (select p1.n_det
                                                                                                         from p p1
                                                                                                         where p1.town=tw.town )
                                                                               and
                                                                                not p.n_det in (select distinct spj.n_det
                                                                                                         from spj
                                                                                                         where spj.n_post=s.n_post and spj.n_izd=j.n_izd)
                                                                              )
                                               )

                       )

Ключевой момент: заменяем условие spj.n_post='S2' на spj.n_post=s.n_post

Решение основной задачи.

Итак, мы подошли вплотную к решению основной задачи. Чтобы получить нужный результат нам достаточно заменить таблицу p (с синонимом tw) на запрос из подзадачи 6.

Полученный запрос выполняется следующим образом :
Для каждой записи из таблицы s будет выполнен подзапрос 1. Так как у нас в таблице s находится 5 строк, подзапрос 1 будет выполнен 5 раз. Если подзапрос 1 вернет хотя бы одну запись, поставщик остается в результирующем списке, иначе будет исключен.
При каждом выполнении подзапроса 1 будут выполнены подзапрос 2 и подзапрос 3. При этом подзапрос 2 выполнится только 1 раз, поскольку он некоррелированный. Подзапрос 3 – коррелированный, поэтому он будет выполнен для каждого города из списка tw. Поскольку в нашем случае в списке tw - два города, подзапрос 3 будет выполнен дважды при каждом выполнении подзапроса 1, то есть всего 10 раз.
Далее, при выполнении подзапроса 2, будут выполнены еще 4 подзапроса, они все некоррелированные и выполнятся 1 раз.
При выполнении подзапроса 3 будет выполнен подзапрос 4, он коррелированный и будет выполнен для каждого изделия из таблицы j, то есть в нашем случае 7 раз при каждом выполнении подзапроса 3. Нетрудно подсчитать, что всего подзапрос 4 будет выполнен 70 раз.
При выполнении подзапроса 4 в свою очередь будут выполнены подзапрос 5 и подзапрос 6. Непосредственно с подзапросом 4 они некоррелированы, но коррелированы с подзапросами более высокого уровня, подзапрос 5 – c подзапросом 1, а подзапрос 6 с подзапросом 3 и основным запросом, а потому подзапрос 5 будет выполнен для каждого города из списка tw (в нашем случае - дважды), а подзапрос 6 – для каждой пары «поставщик – изделие», т.е. 35 раз.