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

4.4. Решение упр.2.

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

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

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

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

select distinct n_post,n_det,n_izd from spj order by n_post,n_det,n_izd

Этот запрос возвращает

n_det	n_post	n_izd
S1	P1	J1
S1	P1	J4

S2	P3	J1
S2	P3	J2
S2	P3	J3
S2	P3	J4
S2	P3	J5
S2	P3	J6
S2	P3	J7

S2	P5	J2
S3	P3	J1
S3	P4	J2
S4	P6	J3
S4	P6	J7
S5	P1	J4
S5	P2	J2
S5	P2	J4
S5	P3	J4
S5	P4	J4
S5	P5	J4
S5	P5	J5
S5	P5	J7
S5	P6	J2
S5	P6	J4

Очевидно, что только поставщик S2 поставляет для всех без исключения изделий деталь P3. Следовательно, наш запрос должен будет вернуть номер и фамилию поставщика S2.

Теперь выделим подзадачи.
Подзадача1. Нам нужно выдать номера и фамилии поставщиков, которые…. Как и в упражнении1, отбросим пока условие, которому должны удовлетворять поставщики, и первая подзадача – просто получить номера и фамилии поставщиков

select s.n_post, s.name from s

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

Подзадача2. Получить для каждой пары «номер поставщика- номер детали» количество различных изделий

select a.n_post,a.n_det,count(distinct a.n_izd) as kolj
from spj a
group by a.n_post,a.n_det
order by a.n_post,a.n_det

Обратите внимание на использование distinct в этом запросе. Сравните результат выполнения этого запроса с результатом такого запроса

select distinct a.n_post,a.n_det,count(a.n_izd) as kolj
from spj a
group by a.n_post,a.n_det
order by a.n_post,a.n_det

Подзадача3. Получить общее количество изделий

select count(*) from j

Вопрос. Почему в этом запросе не нужно использовать distinct?

Скомбинировав решения подзадач 2 и 3 получим решение подзадачи 4.

Подзадача 4. Отобрать пары «номер поставщика- номер детали», для которых количество изделий равно общему количеству изделий, т.е. получить номера поставщиков, поставляющих одну и ту же деталь для всех изделий.

select distinct b .n_post
from (select a.n_post, a.n_det, count(distinct a.n_izd) as kolj
              from spj a
            group by a.n_post,a.n_det) b
where   b. kolj =( select count(*) from j)

Решение основной задачи получаем комбинацией решений подзадач 1 и 4

*select* s.n_post, s.name from* s* *where* s.n_post in ( select distinct* b.n_post* *from* (select* a.n_post, a.n_det, count(distinct a.n_izd) as kolj* *from* spj a *group* by a.n_post,a.n_det) b *where* b. kolj = (select* count() from* j) )*	Подзадача 1

	Подзадача 4

А можно сделать и так

select s.n_post, s.name from s
join (select a.n_post, a.n_det, count(distinct a.n_izd) as kolj
         from spj a
        group by a.n_post,a.n_det) b on s.n_post=b.n_post
where   b. kolj = (select count(*) from j)

Нетрудно убедиться, что оба запроса возвращают ожидаемый результат:

Джонс

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

S2	P5	J2
S3	P3	J1
S3	P4	J2
S4	P6	J3
S4	P6	J7
S5	P1	J4
S5	P2	J2
S5	P2	J4
S5	P3	J4
S5	P4	J4
S5	P5	J4
S5	P5	J5
S5	P5	J7
S5	P6	J2
S5	P6	J4

S2	P5	J2
S3	P3	J1
S3	P4	J2
S4	P6	J3
S4	P6	J7
S5	P1	J4
S5	P2	J2
S5	P2	J4
S5	P3	J4
S5	P4	J4
S5	P5	J4
S5	P5	J5
S5	P5	J7
S5	P6	J2
S5	P6	J4

S2	P5	J2
S3	P3	J1
S3	P4	J2
S4	P6	J3
S4	P6	J7
S5	P1	J4
S5	P2	J2
S5	P2	J4
S5	P3	J4
S5	P4	J4
S5	P5	J4
S5	P5	J5
S5	P5	J7
S5	P6	J2
S5	P6	J4