Кафедра ИУ5 МГТУ им. Н.Э.Баумана, студенческое сообщество - Вклад [ru]

Обсуждение:Заглавная страница

2017-09-11T22:25:08Z

195.91.240.97: /* мб стоит отделить бакалавров от магистров? */

Для обсуждения всего, что касается портала. Пишите любые вопросы, предложения, угрозы.

== приветик от тайного друга ==

спасибо. очень хороший
и полезный сайт.
удачииуспехавам.
: Всё для вас :) [[Участник:ILobster|iLobster]] ([[Обсуждение участника:ILobster|обсуждение]]) 17:40, 18 апреля 2016 (MSK)

== мб стоит отделить бакалавров от магистров? ==

мб, только в чём это будет выражаться? Когда мы создавали ресурс, у нас были только специалисты, и с учебным планом было проще. [[Участник:ILobster|iLobster]] ([[Обсуждение участника:ILobster|обсуждение]]) 23:26, 7 сентября 2017 (MSK)

Не вижу никакого смысла. Вполне достаточно делить предметы по семестрам, пользуясь подходом "9 семестр = 1 семестр магистратуры" [[Служебная:Вклад/195.91.240.97|195.91.240.97]] 01:25, 12 сентября 2017 (MSK)

SQL-запросы к экзамену по СПАСОИ (10 семестр)

2016-06-04T15:37:24Z

195.91.240.97: /* Билет 6 */

<div style="float:right; clear:both; margin-right:1.0em;">__TOC__</div>

Билет экзамена по [[:Категория:Структурное проектирование АСОИ (10 семестр) | СПАСОИ]] состоит из двух частей:
# теория;
# SQL-запрос.

На этой странице собраны все сформированные запросы по билетам.

Странно, что ни в одном билете нет запроса с сортировкой результатов. <s>Возможно, они будут в дополнительных заданиях.</s> Григорьев сказал, что это слишком просто и потому он не стал загромождать запросы дополнительными мелочами.

== Схема БД ==

Схема БД используется всё [[СПАСОИ_(10)_-_Лекция_№8_-_SQL#Некоторые возможности языка SQL | та же]], что была в прошлом семестре и на лекциях.

Для написания запросов и проверки их выполнения создана БД в СУБД [http://www.postgresql.org/ PostgreSQL].

Скрипт создания можно загрузить [http://yadi.sk/d/ArwqOGAy5pPfi отсюда].

=== Таблицы БД ===

==== Поставщики ====

<syntaxhighlight lang="sql">
SELECT * FROM spasoi_ekz.s;
</syntaxhighlight>

nomer_postavshika | imya | sostoyanie | gorod
-------------------+------------------+------------+------------
S5 | Мелисандра | 65000 | Мадрид
S2 | Бран Старк | 60000 | Мурманск
S1 | Якен Хгар | 1500000 | Йокогама
S7 | Сирио Форель | 1500000 | Йокогама
S4 | Джейме Ланнистер | 750000 | Лондон
S3 | Серсея Ланнистер | 1200000 | Лондон
S8 | Тирион Ланнистер | 2571 | Манчестер
S9 | Иванов | 35000 | Мытищи
S10 | Русе Болтон | 44444 | Баренцбург
S11 | Петров Иван | 35000 | Мытищи
S14 | Рендилл Тарли | 1111111 | Прага
S13 | Сэмвелл Тарли | 999999 | Прага
S6 | Оша | | Москва
S16 | Ходор | | Москва
S15 | Мелисса Флорент | 888888 | Стокгольм
S12 | Петров Пётр | 35001 | Мытищи
S17 | Томми Версетти | 123456789 | Майами
S18 | Безликий | 1 | Йокогама
(18 rows)

==== Детали ====

<syntaxhighlight lang="sql">
SELECT * FROM spasoi_ekz.p;
</syntaxhighlight>

nomer_detali | nazvanie | cvet | ves | gorod
--------------+----------------------+---------------+------+-----------------
P9 | подставка | синий | 400 | Нижний Новгород
P10 | стойка | синий | 2000 | Нижний Новгород
P11 | абажур | синий | 400 | Нижний Новгород
P1 | гайка | чёрный | 20 | Лондон
P3 | ось | белый | 5000 | Эдинбург
P4 | зубчатое колесо | чёрный | 50 | Эдинбург
P6 | транзистор | коричневый | 2 | Токио
P7 | печатная плата | зелёный | 200 | Токио
P8 | диод | коричневый | 1 | Токио
P12 | универсальная деталь | универсальный | 1 | Париж
P13 | уникальная деталь | уникальный | 1 | Бостон
P14 | болт | серый | 20 | Ижевск
P15 | рама | красный | 3000 | Манчестер
P16 | колесо | белый | 1500 | Манчестер
P5 | втулка | серый | 350 | Манчестер
P17 | бумага | белый | 1 | Астрахань
P18 | плитка | белый | 300 | Флоренция
P19 | орнамент | серый | 800 | Флоренция
P20 | уголок | серый | 100 | Флоренция
P21 | гайка 01-01 | серебристый | 20 | Клин
P22 | усиленная рама | красный | 3200 | Череповец
P23 | винт | розовый | 5 | Ижевск
P24 | труселя | красный | 20 | Челябинск
P25 | штуцерная деталь | коричневый | 450 | Череповец
P2 | шуруп | чёрный | 5 | Лондон
P26 | лезвие | бесцветный | 120 | Йокогама
(26 rows)

==== Изделия ====

<syntaxhighlight lang="sql">
SELECT * FROM spasoi_ekz.j;
</syntaxhighlight>

nomer_izdelia | nazvanie | gorod
---------------+-----------------------+-----------------
J1 | автомобиль | Магнитогорск
J2 | процессор | Зеленоград
J3 | торшер | Нижний Новгород
J4 | универсальное изделие | Париж
J5 | уникальное изделие | Бостон
J6 | велосипед 01/23 | Манчестер
J7 | изделие из болтов | Челябинск
J8 | шкаф | Ярославль
J9 | рама 02-01 | Череповец
J10 | книга | Астрахань
J11 | панно 01-03 | Флоренция
J12 | велосипед 03-04 | Клин
J13 | рама 02-03 | Череповец
J14 | штуцер 01-02 | Череповец
J15 | велосипед 01-04 | Клин
J16 | кружевное бельё | Челябинск
J17 | штуцер 01-03 | Череповец
J18 | кинжал | Йокогама
(18 rows)

==== Сборки ====

<syntaxhighlight lang="sql">
SELECT * FROM spasoi_ekz.spj;
</syntaxhighlight>

nomer_postavshika | nomer_detali | nomer_izdelia | kolichestvo
-------------------+--------------+---------------+-------------
S1 | P6 | J2 | 20
S1 | P7 | J2 | 5
S2 | P1 | J1 | 4
S6 | P4 | J1 | 2
S6 | P5 | J1 | 6
S3 | P9 | J3 | 1
S4 | P10 | J3 | 1
S5 | P11 | J3 | 1
S2 | P4 | J1 | 8
S6 | P3 | J1 | 50
S7 | P8 | J2 | 25
S1 | P12 | J4 | 1
S2 | P12 | J4 | 1
S3 | P12 | J4 | 1
S4 | P12 | J4 | 1
S5 | P12 | J4 | 1
S7 | P12 | J4 | 1
S7 | P2 | J1 | 1
S6 | P2 | J1 | 9
S6 | P12 | J4 | 7
S1 | P13 | J5 | 14
S6 | P14 | J1 | 9000
S2 | P14 | J1 | 3
S6 | P1 | J1 | 12
S8 | P15 | J6 | 1
S8 | P16 | J6 | 2
S3 | P5 | J6 | 10
S10 | P2 | J8 | 4
S8 | P1 | J7 | 16
S9 | P14 | J7 | 3
S11 | P10 | J3 | 2
S12 | P15 | J1 | 3
S11 | P11 | J3 | 4
S10 | P14 | J9 | 5
S13 | P17 | J10 | 1001
S14 | P17 | J10 | 1111
S15 | P17 | J10 | 1010
S5 | P18 | J11 | 9
S5 | P19 | J11 | 1
S5 | P20 | J11 | 4
S9 | P14 | J8 | 25
S12 | P21 | J12 | 15
S11 | P22 | J13 | 9
S11 | P1 | J14 | 26
S12 | P1 | J14 | 13
S12 | P2 | J14 | 54
S12 | P23 | J4 | 101
S12 | P16 | J15 | 40
S7 | P21 | J12 | 3
S8 | P21 | J12 | 4
S9 | P21 | J12 | 5
S1 | P24 | J16 | 1
S1 | P15 | J6 | 3
S4 | P25 | J17 | 1
S17 | P16 | J1 | 4
S18 | P26 | J18 | 1
(56 rows)

== Готовые запросы ==

=== Некоторое вступление ===

Если видите, что тот или иной запрос можно составить короче и рациональней - смело вносите правку.

==== Про только и только ====

В трёх билетах в задании на запрос встречается формулировка "''только и только''". Как оказалось, это означает следующее: если холодильники поставляет ''только и только'' Уася, то:
# кроме Уаси никто не поставляет холодильники;
# Уася не поставляет ничего, кроме холодильников.

<s>Великий и могучий русский языка, ну что за экономия на бумаге.</s>

Существующие запросы, естественно, оказались неправильными и их пришлось переписать.

==== Про JOIN ====

Почти все запросы, где используется соединение таблиц, можно написать с использованием <code>JOIN</code>, а можно просто с перечислением таблиц через запятую.

Но неожиданно оказалось, это почти то же самое и называется [http://ru.wikipedia.org/wiki/Join_%28SQL%29#CROSS_JOIN CROSS JOIN]. Такое соединение таблиц (<code>CROSS JOIN + WHERE</code>) считается устаревшим, поскольку его не рекомендует стандарт SQL ANSI. Таким образом, использование <code>JOIN</code> с условием соединения в <code>ON</code> является <s>хипстерством в программировании</s> более правильным и вообще прогрессивным.

=== Билет 1 ===

Написать запрос SELECT: выдать номера поставщиков, которые поставляют, по крайней мере, все те детали, которые поставляет поставщик с номером 'S2'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT DISTINCT nomer_postavshika
FROM spasoi_ekz.spj SPJX
WHERE NOT EXISTS (
SELECT nomer_detali
FROM spasoi_ekz.spj SPJY
WHERE nomer_postavshika = 'S2'
AND NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_postavshika = SPJX.nomer_postavshika
AND nomer_detali = SPJY.nomer_detali
)
);
</syntaxhighlight>

Еще один вариант:
<syntaxhighlight lang="sql">
WITH
s_all AS (
SELECT DISTINCT nomer_detali
FROM spasoi_ekz.spj SPJY
WHERE nomer_postavshika = 'S2'
)

SELECT
spj.nomer_postavshika
FROM spasoi_ekz.spj
INNER JOIN s_all ON s_all.nomer_detali = spj.nomer_detali
WHERE
nomer_postavshika != 'S2'
GROUP BY nomer_postavshika
HAVING count(spj.nomer_detali) = (SELECT count(*) FROM s_all);
</syntaxhighlight>

Результат:

nomer_postavshika
-------------------
S6
S2
(2 rows)

=== Билет 2 ===

Написать запрос SELECT: выдать номера деталей и общее их количество для всех номеров деталей, поставляемых более чем одним поставщиком.

Текст запроса, каким его дал Григорьев на лекции, впоследствии исправив его:

<syntaxhighlight lang="sql">
SELECT nomer_detali AS "Номер детали",
SUM(kolichestvo) AS "Сколько штук поставляется",
COUNT(DISTINCT nomer_postavshika) AS "Сколько у неё поставщиков"
FROM spasoi_ekz.spj
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_postavshika) > 1;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё вариант''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nomer_detali AS "Номер детали",
kol AS "Сколько штук поставляется"
FROM (
SELECT nomer_detali,
SUM(kolichestvo) AS kol,
COUNT(DISTINCT nomer_postavshika)
FROM spasoi_ekz.spj
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_postavshika) > 1
) A;
</syntaxhighlight>
</div>
</div>

Результат:

Номер детали | Сколько штук поставляется | Сколько у неё поставщиков
--------------+---------------------------+---------------------------
P1 | 71 | 5
P10 | 3 | 2
P11 | 5 | 2
P12 | 13 | 7
P14 | 9036 | 4
P15 | 7 | 3
P16 | 46 | 3
P17 | 3122 | 3
P2 | 68 | 4
P21 | 27 | 4
P4 | 10 | 2
P5 | 16 | 2
(12 rows)

=== Билет 3 ===

Написать запрос SELECT: выдать номера поставщиков, поставляющих детали с номером 'P1' для какого-либо изделия в количестве (в поставке) большим, чем средний объём поставок деталей с номером 'P2' для этого изделия.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT X.nomer_postavshika
FROM spasoi_ekz.spj X
WHERE X.nomer_detali = 'P1'
AND X.kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_izdelia = X.nomer_izdelia
AND nomer_detali = 'P2'
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос, но длиннее и нерациональней''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT DISTINCT nomer_postavshika
FROM (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_izdelia IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
)
AND nomer_izdelia IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P2'
)
) A
WHERE nomer_detali = 'P1' AND kolichestvo >
(
SELECT AVG(kolichestvo)
FROM (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_izdelia IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
)
AND nomer_izdelia IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P2'
)
) B
WHERE nomer_detali = 'P2'
);
</syntaxhighlight>
</div>
</div>

Результат:

nomer_postavshika
-------------------
S6
(1 row)

=== Билет 4 ===

Написать запрос SELECT: выдать номера изделий, для которых детали поставляет только поставщик с номером ‘S1’.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nomer_izdelia
FROM spasoi_ekz.spj X
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_postavshika != 'S1'
AND X.nomer_izdelia = nomer_izdelia
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос, но без EXISTS''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT DISTINCT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_izdelia IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_postavshika = 'S1'
)
AND nomer_izdelia NOT IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_postavshika != 'S1'
);
</syntaxhighlight>
</div>
</div>

Результат:

nomer_izdelia
---------------
J5
J16
(2 rows)

=== Билет 5 ===

Написать запрос SELECT: выдать имена поставщиков, поставляющих детали с названием "Болт" в количестве (в поставке) большим, чем средний объём всех поставок деталей с номером 'P1'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND nazvanie = LOWER('Болт')
WHERE kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Вариант запроса без JOIN''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S, spasoi_ekz.p P, spasoi_ekz.spj SPJ
WHERE P.nazvanie = LOWER('Болт')
AND P.nomer_detali = SPJ.nomer_detali
AND S.nomer_postavshika = SPJ.nomer_postavshika
AND SPJ.kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
);
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё вариант запроса без JOIN и сложнее''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s
WHERE nomer_postavshika IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
WHERE nomer_detali = (
SELECT nomer_detali
FROM spasoi_ekz.p
WHERE LOWER(nazvanie) = LOWER('Болт')
)
AND kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
)
);
</syntaxhighlight>
</div>
</div>

Результат:

imya
------
Оша
Иванов
(2 rows)

=== Билет 6 ===

Написать запрос SELECT: выдать названия деталей, поставляемых поставщиком, проживающим в том же городе, где изготавливаются эти детали, для изделия с названием ‘Велосипед 01/23’.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT P.nazvanie
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE S.gorod = P.gorod
AND J.nazvanie = LOWER('Велосипед 01/23');
</syntaxhighlight>

Еще один вариант:

<syntaxhighlight lang="sql">
SELECT
p.nazvanie
FROM spj
INNER JOIN j ON spj.nomer_izdelia = j.nomer_izdelia AND j.nazvanie ILIKE 'Велосипед 01/23'
INNER JOIN s ON (
spj.nomer_postavshika = s.nomer_postavshika
AND s.gorod = j.gorod
)
INNER JOIN p ON spj.nomer_detali = p.nomer_detali;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос без JOIN''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT DISTINCT nazvanie
FROM spasoi_ekz.S S, spasoi_ekz.p P, spasoi_ekz.spj SPJ
WHERE S.gorod = P.gorod
AND P.nomer_detali = SPJ.nomer_detali
AND S.nomer_postavshika = SPJ.nomer_postavshika
AND nomer_izdelia = (
SELECT nomer_izdelia
FROM spasoi_ekz.j
WHERE nazvanie = LOWER('Велосипед 01/23')
);
</syntaxhighlight>
</div>
</div>

Результат:

nazvanie
----------
рама
колесо
(2 rows)

=== Билет 7 ===

Написать запрос SELECT: выдать названия изделий, куда входят детали с названием 'Болт', поставляемых поставщиками с именем 'Иванов', в количестве (в поставке) большим, чем средний объём поставок для этого изделия.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT J.nazvanie
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE imya LIKE '%Иванов%'
AND P.nazvanie = LOWER('Болт')
AND kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj X
WHERE SPJ.nomer_izdelia = X.nomer_izdelia
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё один вариант запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nazvanie
FROM spasoi_ekz.j
WHERE nomer_izdelia IN (
SELECT nomer_izdelia
FROM (spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
AND imya LIKE '%Иванов%'
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND nazvanie = LOWER('Болт')) A
WHERE kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = A.nomer_izdelia
)
);
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё вариант этого же запроса, но длиннее и нерациональней''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nazvanie
FROM (
SELECT J.nazvanie, kolichestvo
FROM spasoi_ekz.s S, spasoi_ekz.p P, spasoi_ekz.j J, spasoi_ekz.spj SPJ
WHERE J.nomer_izdelia = SPJ.nomer_izdelia
AND P.nomer_detali = SPJ.nomer_detali
AND P.nazvanie = LOWER('Болт')
AND S.imya LIKE '%Иванов%'
AND S.nomer_postavshika = SPJ.nomer_postavshika
) A
WHERE kolichestvo >
(
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_izdelia IN(
SELECT J.nomer_izdelia
FROM spasoi_ekz.s S, spasoi_ekz.p P, spasoi_ekz.j J, spasoi_ekz.spj SPJ
WHERE J.nomer_izdelia = SPJ.nomer_izdelia
AND P.nomer_detali = SPJ.nomer_detali
AND P.nazvanie = LOWER('Болт')
AND S.imya LIKE '%Иванов%'
AND S.nomer_postavshika = SPJ.nomer_postavshika
)
);
</syntaxhighlight>
</div>
</div>

Результат:

nazvanie
-------------------
шкаф
(1 row)

=== Билет 8 ===

Написать запрос SELECT: выдать номера изделий и общее количество деталей для них, поставляемых поставщиками с именем 'Петров'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nomer_izdelia AS "Номер изделия", SUM(kolichestvo) AS "Количество деталей"
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
-- так как "поставщикАМИ" и возможны
-- Иван Петров и Петров Иван, то LIKE %Петров%
AND imya LIKE '%Петров%'
GROUP BY nomer_izdelia;
</syntaxhighlight>

Результат:

Номер изделия | Количество деталей
---------------+--------------------
J4 | 101
J3 | 6
J13 | 9
J15 | 40
J1 | 3
J12 | 15
J14 | 93
(7 rows)

=== Билет 9 ===

Написать запрос SELECT: выдать номера деталей и общее их количество для всех номеров деталей, которые входят только в одно изделие.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nomer_detali, SUM(kolichestvo)
FROM spasoi_ekz.spj
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_izdelia) = 1;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё вариант''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nomer_detali, kol FROM (
SELECT nomer_detali, SUM(kolichestvo) AS kol, COUNT(DISTINCT nomer_izdelia) = 1
FROM spasoi_ekz.spj
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_izdelia) = 1
) A;
</syntaxhighlight>
</div>
</div>

Результат:

nomer_detali | sum
--------------+------
P10 | 3
P11 | 5
P12 | 13
P13 | 14
P17 | 3122
P18 | 9
P19 | 1
P20 | 4
P21 | 27
P22 | 9
P23 | 101
P24 | 1
P25 | 1
P26 | 1
P3 | 50
P4 | 10
P6 | 20
P7 | 5
P8 | 25
P9 | 1
(20 rows)

=== Билет 10 ===

Написать запрос SELECT: выдать имена поставщиков, поставляющих детали с названием 'Болт' для изделия с названием 'Рама 02-01' в количестве (в поставке) большим, чем минимальное значение поставки детали с номером 'P1'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Рама 02-01')
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND P.nazvanie = LOWER('Болт')
JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE kolichestvo > (
SELECT MIN(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
Этот же запрос без JOIN (точнее, с CROSS JOIN):
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT S.imya
FROM spasoi_ekz.s S,
spasoi_ekz.p P,
spasoi_ekz.j J,
spasoi_ekz.spj SPJ
WHERE SPJ.nomer_postavshika = S.nomer_postavshika
AND SPJ.nomer_detali = P.nomer_detali
AND SPJ.nomer_izdelia = J.nomer_izdelia
AND P.nazvanie = LOWER('Болт')
AND J.nazvanie = LOWER('Рама 02-01')
AND SPJ.kolichestvo > (
SELECT MIN(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
);

</syntaxhighlight>
</div>
</div>
Результат:

imya
-------------
Русе Болтон
(1 row)

=== Билет 11 ===

Написать запрос SELECT: выдать названия городов и суммарное состояние проживающих в каждом городе поставщиков, у которых минимальный объём поставки деталей больше 1000.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT gorod, SUM(sostoyanie)
FROM spasoi_ekz.s S
WHERE (
SELECT MIN(kolichestvo)
FROM spasoi_ekz.spj X
WHERE X.nomer_postavshika = S.nomer_postavshika
) > 1000
GROUP BY gorod;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё вариант''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT gorod, SUM(sostoyanie)
FROM spasoi_ekz.s
WHERE nomer_postavshika IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
GROUP BY nomer_postavshika HAVING MIN(kolichestvo) > 1000
)
GROUP BY gorod;
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё три неправильных варианта ''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
-- здесь сразу отбрасывается весь город,
-- в котором хоть один поставщик имеет миним. объем <=1000,
-- а надо, чтобы был отброшен только этот поставщик.
-- читайте описание конструкции GROUP BY ... HAVING ...
SELECT gorod, SUM(sostoyanie)
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
GROUP BY gorod HAVING MIN(kolichestvo) > 1000;

-- та же фигня
SELECT S.gorod, SUM(S.sostoyanie)--, MIN(SPJ.kolichestvo)
FROM spasoi_ekz.s S, spasoi_ekz.spj SPJ
WHERE S.nomer_postavshika = SPJ.nomer_postavshika
GROUP BY gorod HAVING MIN(kolichestvo) > 1000;

-- и ещё один почти верный
SELECT gorod, sost
FROM (
SELECT gorod, SUM(sostoyanie) AS sost, SUM(SPJ.kolichestvo)
FROM spasoi_ekz.spj, spasoi_ekz.s
WHERE S.nomer_postavshika = SPJ.nomer_postavshika
GROUP BY S.gorod HAVING SUM(SPJ.kolichestvo) > 1000
) A;
</syntaxhighlight>
</div>
</div>

Результат:

gorod | sum
-----------+---------
Прага | 2111110
Стокгольм | 888888
(2 rows)

=== Билет 12 ===

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

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nomer_detali
FROM spasoi_ekz.spj SPJ, spasoi_ekz.s S, spasoi_ekz.j J
WHERE SPJ.nomer_postavshika = S.nomer_postavshika
AND S.gorod = J.gorod
AND J.nomer_izdelia = SPJ.nomer_izdelia;
</syntaxhighlight>

Результат:

nomer_detali
--------------
P15
P16
P26
(3 rows)

=== Билет 13 ===

Написать <u>один</u> запрос SELECT: выдать количества строк в таблице S (Поставщик) 1) с пустыми значениями и 2) непустыми значениями в столбце Состояние.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT COUNT(*) - COUNT(sostoyanie) AS "С пустым состоянием",
COUNT(sostoyanie) AS "С не пустым состоянием"
FROM spasoi_ekz.s;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос, но длиннее и нерациональней''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">

SELECT COUNT(Snull.*) AS "С пустым состоянием", COUNT(Snotnull.sostoyanie) AS "С не пустым состоянием"
FROM spasoi_ekz.s Snull RIGHT OUTER JOIN spasoi_ekz.s Snotnull
ON Snull.nomer_postavshika = Snotnull.nomer_postavshika
AND Snull.sostoyanie IS NULL;
</syntaxhighlight>
</div>
</div>

Результат:

С пустым состоянием | С не пустым состоянием
---------------------+-----------------------
2 | 16
(1 row)

=== Билет 14 ===

Написать запрос SELECT: выдать имена поставщиков, которые поставляют детали только и только для изделия с номером 'J1'.

Чтобы продемонстрировать выполнение запроса наглядно, возьмём изделие не 'J1', а 'J18', которое было создано специально для этого запроса. Если оставить 'J1', то наглядности не получится, так как по нашей схеме БД это изделие не попадает под условие "только и только", и запрос вернёт пустой результат.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT DISTINCT imya
FROM spasoi_ekz.s S
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE (
nomer_izdelia != 'J18'
AND
nomer_postavshika = S.nomer_postavshika)
OR
(
nomer_izdelia = 'J18'
AND
nomer_postavshika != S.nomer_postavshika
)
);
</syntaxhighlight>


Результат:

imya
----------------
Безликий
(1 row)

=== Билет 15 ===

Написать запрос SELECT: выдать наименования изделий, для которых детали поставляют только те поставщики, у которых состояние больше 1000000 у.е.

Текст запроса:
<syntaxhighlight lang="sql">
SELECT DISTINCT nazvanie
FROM spasoi_ekz.j J JOIN spasoi_ekz.spj X
ON J.nomer_izdelia = X.nomer_izdelia
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON S.nomer_postavshika = SPJ.nomer_postavshika
WHERE sostoyanie <= 1000000
AND SPJ.nomer_izdelia = X.nomer_izdelia
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Неправильный вариант''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
-- Неверный запрос, если в таблице SPJ НЕТ поставок с этим изделием,
-- а в таблице J - есть это изделие, то запрос вернет его название, хотя не должен
SELECT DISTINCT nazvanie
FROM spasoi_ekz.j J
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj X JOIN spasoi_ekz.s S
ON X.nomer_postavshika = S.nomer_postavshika
WHERE sostoyanie <= 1000000
AND X.nomer_izdelia = J.nomer_izdelia
);
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''Вариант этого запроса без NOT EXISTS''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT DISTINCT nazvanie
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE sostoyanie > 1000000
AND SPJ.nomer_izdelia NOT IN (
SELECT nomer_izdelia
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE sostoyanie < 1000000
);
</syntaxhighlight>
</div>
</div>

Результат:

nazvanie
--------------------
кружевное бельё
уникальное изделие
процессор
(3 rows)

=== Билет 16 ===
Написать запрос SELECT: выдать цвета и для каждого цвета общее количество деталей, входящих в изделие с названием 'Панно 01-03'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT cvet AS "Цвет", SUM(kolichestvo) AS "Деталей"
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Панно 01-03')
JOIN spasoi_ekz.P P
ON SPJ.nomer_detali = P.nomer_detali
GROUP BY cvet;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Другой вариант запроса для тонко чувствующих натур''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT cvet AS "Цвет", SUM(kolichestvo) AS "Деталей"
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE J.nazvanie = LOWER('Панно 01-03')
GROUP BY cvet;
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё один вариант запроса от ненавистника JOIN''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT P.cvet, SUM(SPJ.kolichestvo)
FROM spasoi_ekz.p P,
spasoi_ekz.spj SPJ,
spasoi_ekz.j J
WHERE P.nomer_detali = SPJ.nomer_detali
AND J.nomer_izdelia = SPJ.nomer_izdelia
AND J.nazvanie = LOWER('Панно 01-03')
GROUP BY P.cvet;
</syntaxhighlight>
</div>
</div>

Результат:

Цвет | Деталей
-------+---------
белый | 9
серый | 5
(2 rows)

=== Билет 17 ===

Написать запрос SELECT: выдать номера поставщиков и количество сделанных ими поставок при условии, что среднее число деталей во всех этих поставках больше 1000.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nomer_postavshika AS "Номер поставщика",
COUNT(*) AS "Количество поставок"
FROM spasoi_ekz.spj
GROUP BY nomer_postavshika HAVING AVG(kolichestvo) > 1000;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё вариант запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nomer_postavshika AS "Номер поставщика", COUNT(*) AS "Количество поставок"
FROM spasoi_ekz.spj
WHERE nomer_postavshika IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
GROUP BY nomer_postavshika HAVING AVG(kolichestvo) > 1000
)
GROUP BY nomer_postavshika;
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё вариант запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nomer_postavshika AS "Номер поставщика", COUNT(*) AS "Количество поставок"
FROM spasoi_ekz.spj
WHERE (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj X
WHERE X.nomer_postavshika = spj.nomer_postavshika
) > 1000
GROUP BY nomer_postavshika;
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''И даже ещё вариант запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nom AS "Номер поставщика", cnt AS "Количество поставок"
FROM (
SELECT S.nomer_postavshika AS nom,
COUNT(SPJ.nomer_postavshika) AS cnt,
AVG(SPJ.kolichestvo) AS kol
FROM spasoi_ekz.s S, spasoi_ekz.spj SPJ
WHERE S.nomer_postavshika = SPJ.nomer_postavshika
GROUP BY S.nomer_postavshika
) A
WHERE kol > 1000;
</syntaxhighlight>
</div>
</div>

Результат:

Номер поставщика | Количество поставок
------------------+---------------------
S13 | 1
S6 | 7
S14 | 1
S15 | 1
(4 rows)

=== Билет 18 ===

Написать запрос SELECT: выдать имена поставщиков, имеющих состояние больше 1000 и поставляющих деталь с названием 'Гайка 01-01' для изделия с названием 'Велосипед 03-04' в количестве (в поставке) большим, чем средний объём поставки, выполненной поставщиками с именем 'Иванов'.

[[Файл:2nd dufficulty.png|center]]

<p align="center"><font size="5px">'''Второй по сложности запрос экзамена!'''</font></p>

<p align="center"><font size="4px">'''Вот уж не свезло, так не свезло'''</font></p>

Текст запроса:

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
JOIN spasoi_ekz.j J
ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE sostoyanie > 1000
AND P.nazvanie = LOWER('Гайка 01-01')
AND J.nazvanie = LOWER('Велосипед 03-04')
AND kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE S.imya = 'Иванов' -- или WHERE S.imya LIKE '%Иванов%',
-- поскольку сказано, что поставка выполнена поставщиками с именем Иванов,
-- а это могут быть разные имена и отчества. Короче, опять великий
-- русский язык в задании - что ещё за "с именем Иванов"
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё один вариант запроса от ненавистника JOIN:''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S,
spasoi_ekz.spj SPJ,
spasoi_ekz.p P,
spasoi_ekz.j J
WHERE SPJ.nomer_postavshika = S.nomer_postavshika
AND SPJ.nomer_detali = P.nomer_detali
AND SPJ.nomer_izdelia=J.nomer_izdelia
AND S.sostoyanie > 1000
AND P.nazvanie = LOWER('Гайка 01-01')
AND J.nazvanie = LOWER('Велосипед 03-04')
AND kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj SPJ2, spasoi_ekz.S S2
WHERE S2.nomer_postavshika = SPJ2.nomer_postavshika
AND S2.imya LIKE '%Иванов%'
);
</syntaxhighlight>
</div>
</div>

Результат:

imya
-------------
Петров Пётр
(1 row)

=== Билет 19 ===

Написать запрос SELECT: выдать названия красных деталей, которые входят только в изделие с названием 'Рама 02-03' в количестве, меньшем 10 единиц в поставке.

Примечание: Григорьев ругается на X.nomer_detali = SPJ.nomer_detali Лучше во вложенном подзапросе сделать еще один джойн p и приравнивать названия деталей.

Текст запроса:
<syntaxhighlight lang="sql">
SELECT DISTINCT X.nazvanie
FROM spasoi_ekz.p X JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_detali = X.nomer_detali
WHERE X.cvet = 'красный'
AND kolichestvo < 10
AND NOT EXISTS (
SELECT *
FROM spasoi_ekz.j J JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE J.nazvanie != LOWER('Рама 02-03')
AND X.nomer_detali = SPJ.nomer_detali
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё вариант этого же запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT P.nazvanie
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND cvet = 'красный'
JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Рама 02-03')
WHERE kolichestvo < 10
AND SPJ.nomer_detali NOT IN (
SELECT nomer_detali
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie != LOWER('Рама 02-03')
);
</syntaxhighlight>
</div>
</div>

Результат:

nazvanie
----------------
усиленная рама
(1 row)

=== Билет 20 ===

Написать запрос SELECT: выдать имена поставщиков, поставляющих только белые детали.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
WHERE nomer_postavshika = S.nomer_postavshika
AND P.cvet != 'белый'
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё один вариант этого же запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND cvet = 'белый'
JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE S.nomer_postavshika NOT IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND cvet != 'белый'
);
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё один вариант этого же запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s
WHERE nomer_postavshika NOT IN (
SELECT spj.nomer_postavshika
FROM spasoi_ekz.spj SPJ, spasoi_ekz.p P
WHERE SPJ.nomer_detali = P.nomer_detali
AND p.cvet != 'белый'
)
AND nomer_postavshika IN (
SELECT spj.nomer_postavshika
FROM spasoi_ekz.spj SPJ, spasoi_ekz.p P
WHERE SPJ.nomer_detali = P.nomer_detali
AND p.cvet = 'белый'
);
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё один вариант этого запроса от ненавистника JOIN''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S, spasoi_ekz.spj SPJ2
WHERE SPJ2.nomer_postavshika = S.nomer_postavshika
AND NOT EXISTS (
SELECT *
FROM spasoi_ekz.p P, spasoi_ekz.spj SPJ
WHERE SPJ.nomer_detali = P.nomer_detali
AND SPJ.nomer_postavshika = SPJ2.nomer_postavshika
AND P.cvet != 'белый'
);
</syntaxhighlight>
</div>
</div>

Результат:

imya
-----------------
Рендилл Тарли
Сэмвелл Тарли
Мелисса Флорент
Томми Версетти
(4 rows)

=== Билет 21 ===

Написать запрос SELECT: выдать названия изделий, для которых детали поставляет только и только поставщик с номером 'S1'.

Чтобы продемонстрировать выполнение запроса наглядно, возьмём поставщика не 'S1', а 'S18', который был создан специально для этого запроса. Если оставить 'S1', то наглядности не получится, так как по нашей схеме БД этот поставщик не попадает под условие "''только и только''", и запрос вернёт пустой результат.

Примечание: Григорьев ругается на nomer_izdelia = J.nomer_izdelia Лучше во вложенном подзапросе сделать еще один джойн j и приравнивать названия изделий, а не номера.
Текст запроса:

<syntaxhighlight lang="sql">
SELECT nazvanie
FROM spasoi_ekz.j
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_postavshika != 'S18'
AND nomer_izdelia = J.nomer_izdelia
)
AND NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_postavshika = 'S18'
AND nomer_izdelia != J.nomer_izdelia
);
</syntaxhighlight>

Результат:

nazvanie
--------------------
кинжал
(1 row)

=== Билет 22 ===

Написать запрос SELECT: выдать имена поставщиков, которые поставляют только детали с номером 'P1' для изделия с именем 'Штуцер 01-02'.

Текст запроса:
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Штуцер 01-02')
JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj X JOIN spasoi_ekz.j J
ON X.nomer_izdelia = J.nomer_izdelia
WHERE X.nomer_detali != 'P1'
AND J.nazvanie = LOWER('Штуцер 01-02')
AND X.nomer_postavshika = S.nomer_postavshika
);
</syntaxhighlight>


<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос, но длиннее и нерациональней''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Штуцер 01-02')
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND SPJ.nomer_detali = 'P1'
JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE SPJ.nomer_postavshika NOT IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Штуцер 01-02')
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND SPJ.nomer_detali != 'P1'
);
</syntaxhighlight>
</div>
</div>

Результат:

imya
-------------
Петров Иван
(1 row)

=== Билет 23 ===

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

Текст запроса:
<syntaxhighlight lang="sql">
SELECT S.imya
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON S.nomer_postavshika = SPJ.nomer_postavshika
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
WHERE P.nazvanie = LOWER('Винт')
AND SPJ.kolichestvo > 100
AND S.sostoyanie > (
SELECT AVG(SS.sostoyanie)
FROM spasoi_ekz.s SS
WHERE SS.gorod = S.gorod
);
</syntaxhighlight>

Результат:

imya
-------------
Петров Пётр
(1 row)

=== Билет 24 ===

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


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

То есть, вообще говоря, задание на запрос неполное, и его можно трактовать так: выдать наименования деталей и общее их количество для всех наименований деталей, изготавливаемых в городе <code>%НАЗВАНИЕГОРОДА%</code>. Вот эта переменная задаётся где-то в программе, а в БД идёт запрос с уже подставленным конкретным названием.

Этот вариант запроса составлен, например, для Лондона.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nazvanie, SUM(kolichestvo)
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p
ON SPJ.nomer_detali = P.nomer_detali
WHERE gorod = 'Лондон'
GROUP BY nazvanie;
</syntaxhighlight>

Результат:

nazvanie | sum
----------+-----
гайка | 71
шуруп | 68
(2 rows)

=== Билет 25 ===

Написать запрос SELECT: выдать имена поставщиков, поставляющих хотя бы одну белую деталь для изделия с названием 'Велосипед 01-04' с объёмом поставки большим, чем средний объём поставки этого поставщика.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
JOIN spasoi_ekz.j J
ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE cvet = 'белый'
AND J.nazvanie = LOWER('Велосипед 01-04')
AND kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj SPJ
WHERE SPJ.nomer_postavshika = S.nomer_postavshika
);
</syntaxhighlight>

Результат:

imya
-------------
Петров Пётр
(1 row)

=== Билет 26 ===

Написать запрос SELECT: выдать номера красных деталей и количество поставок этих деталей.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT P.nomer_detali AS "Номер детали", COUNT(kolichestvo) AS "Количество поставок с ней"
FROM spasoi_ekz.p P, spasoi_ekz.spj SPJ
WHERE P.nomer_detali = SPJ.nomer_detali
AND cvet = 'красный'
GROUP BY P.nomer_detali;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос через JOIN''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT SPJ.nomer_detali AS "Номер детали", COUNT(kolichestvo) AS "Количество поставок с ней"
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND cvet = 'красный'
GROUP BY SPJ.nomer_detali;
</syntaxhighlight>
</div>
</div>

Результат:

Номер детали | Количество поставок с ней
--------------+---------------------------
P15 | 3
P22 | 1
P24 | 1
(3 rows)

=== Билет 27 ===

Написать запрос SELECT: выдать наименования городов и среднее состояние поставщиков для каждого города, поставляющих детали с названием 'Гайка 01-01' для изделия с названием 'Велосипед 03-04' в количестве (в поставке) большим, чем минимальный объём поставки, выполненной поставщиком с номером 'S1'.

[[Файл:1st dufficulty.png|center]]

<p align="center"><font size="7px">'''Сложнейший запрос экзамена!'''</font></p>

<p align="center"><font size="5px">'''Сохрани Джа, вытащить такое'''</font></p>

Текст запроса:

<syntaxhighlight lang="sql">
SELECT S.gorod AS "Город", AVG(S.sostoyanie) AS "Среднее состояние"
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
JOIN spasoi_ekz.j J
ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE P.nazvanie = LOWER('Гайка 01-01')
AND J.nazvanie = LOWER('Велосипед 03-04')
AND kolichestvo > (
SELECT MIN(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_postavshika = 'S1'
)
GROUP BY S.gorod;

</syntaxhighlight>

Результат:

Город | Среднее состояние
-----------+-----------------------
Мытищи | 35000.500000000000
Йокогама | 1500000.000000000000
Манчестер | 2571.0000000000000000
(3 rows)

=== Билет 28 ===

Написать запрос SELECT: выдать названия изделий, куда входит хотя бы одна красная деталь весом больше 10 граммов, поставляемая только поставщиком с номером 'S1'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT J.nazvanie
FROM spasoi_ekz.j J JOIN spasoi_ekz.spj SPJ1
ON J.nomer_izdelia = SPJ1.nomer_izdelia
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ1.nomer_detali
WHERE P.ves > 10
AND P.cvet = 'красный'
AND NOT EXISTS (
SELECT SPJ2.nomer_postavshika
FROM spasoi_ekz.spj SPJ2
WHERE SPJ2.nomer_detali = P.nomer_detali
AND SPJ2.nomer_postavshika != 'S1'
);
</syntaxhighlight>

Результат:

nazvanie
-----------------
кружевное бельё
(1 row)

=== Билет 29 ===
Написать запрос SELECT: выдать названия деталей, которые входят только и только в состав изделия с названием 'Штуцер 01-03'.

Примечание: Григорьев ругается на SPJ.nomer_detali = P.nomer_detali Лучше во вложенном подзапросе сделать еще один джойн p и приравнивать названия деталей.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT DISTINCT nazvanie
FROM spasoi_ekz.p
WHERE NOT EXISTS (
SELECT SPJ.nomer_izdelia
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE (
J.nazvanie != LOWER('Штуцер 01-03')
AND
SPJ.nomer_detali = P.nomer_detali
)
OR (
J.nazvanie = LOWER('Штуцер 01-03')
AND
SPJ.nomer_detali != P.nomer_detali
)
);
</syntaxhighlight>

Результат:

nazvanie
------------------
штуцерная деталь
(1 row)

=== Билет 30 ===
Написать запрос SELECT: выдать имена поставщиков, которые поставляют, по крайней мере, все те детали, которые поставляет поставщик с номером 'S2'.

Текст запроса:
<syntaxhighlight lang="sql">
SELECT DISTINCT imya
FROM spasoi_ekz.s S
WHERE NOT EXISTS (
SELECT nomer_detali
FROM spasoi_ekz.spj SPJY
WHERE nomer_postavshika = 'S2'
AND NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_postavshika = S.nomer_postavshika
AND nomer_detali = SPJY.nomer_detali
)
);
</syntaxhighlight>

Результат:

imya
------------
Оша
Бран Старк
(2 rows)
[[Категория:Структурное проектирование АСОИ (10 семестр)]]

SQL-запросы к экзамену по СПАСОИ (10 семестр)

2016-06-04T15:30:58Z

195.91.240.97: /* Билет 5 */

<div style="float:right; clear:both; margin-right:1.0em;">__TOC__</div>

Билет экзамена по [[:Категория:Структурное проектирование АСОИ (10 семестр) | СПАСОИ]] состоит из двух частей:
# теория;
# SQL-запрос.

На этой странице собраны все сформированные запросы по билетам.

Странно, что ни в одном билете нет запроса с сортировкой результатов. <s>Возможно, они будут в дополнительных заданиях.</s> Григорьев сказал, что это слишком просто и потому он не стал загромождать запросы дополнительными мелочами.

== Схема БД ==

Схема БД используется всё [[СПАСОИ_(10)_-_Лекция_№8_-_SQL#Некоторые возможности языка SQL | та же]], что была в прошлом семестре и на лекциях.

Для написания запросов и проверки их выполнения создана БД в СУБД [http://www.postgresql.org/ PostgreSQL].

Скрипт создания можно загрузить [http://yadi.sk/d/ArwqOGAy5pPfi отсюда].

=== Таблицы БД ===

==== Поставщики ====

<syntaxhighlight lang="sql">
SELECT * FROM spasoi_ekz.s;
</syntaxhighlight>

nomer_postavshika | imya | sostoyanie | gorod
-------------------+------------------+------------+------------
S5 | Мелисандра | 65000 | Мадрид
S2 | Бран Старк | 60000 | Мурманск
S1 | Якен Хгар | 1500000 | Йокогама
S7 | Сирио Форель | 1500000 | Йокогама
S4 | Джейме Ланнистер | 750000 | Лондон
S3 | Серсея Ланнистер | 1200000 | Лондон
S8 | Тирион Ланнистер | 2571 | Манчестер
S9 | Иванов | 35000 | Мытищи
S10 | Русе Болтон | 44444 | Баренцбург
S11 | Петров Иван | 35000 | Мытищи
S14 | Рендилл Тарли | 1111111 | Прага
S13 | Сэмвелл Тарли | 999999 | Прага
S6 | Оша | | Москва
S16 | Ходор | | Москва
S15 | Мелисса Флорент | 888888 | Стокгольм
S12 | Петров Пётр | 35001 | Мытищи
S17 | Томми Версетти | 123456789 | Майами
S18 | Безликий | 1 | Йокогама
(18 rows)

==== Детали ====

<syntaxhighlight lang="sql">
SELECT * FROM spasoi_ekz.p;
</syntaxhighlight>

nomer_detali | nazvanie | cvet | ves | gorod
--------------+----------------------+---------------+------+-----------------
P9 | подставка | синий | 400 | Нижний Новгород
P10 | стойка | синий | 2000 | Нижний Новгород
P11 | абажур | синий | 400 | Нижний Новгород
P1 | гайка | чёрный | 20 | Лондон
P3 | ось | белый | 5000 | Эдинбург
P4 | зубчатое колесо | чёрный | 50 | Эдинбург
P6 | транзистор | коричневый | 2 | Токио
P7 | печатная плата | зелёный | 200 | Токио
P8 | диод | коричневый | 1 | Токио
P12 | универсальная деталь | универсальный | 1 | Париж
P13 | уникальная деталь | уникальный | 1 | Бостон
P14 | болт | серый | 20 | Ижевск
P15 | рама | красный | 3000 | Манчестер
P16 | колесо | белый | 1500 | Манчестер
P5 | втулка | серый | 350 | Манчестер
P17 | бумага | белый | 1 | Астрахань
P18 | плитка | белый | 300 | Флоренция
P19 | орнамент | серый | 800 | Флоренция
P20 | уголок | серый | 100 | Флоренция
P21 | гайка 01-01 | серебристый | 20 | Клин
P22 | усиленная рама | красный | 3200 | Череповец
P23 | винт | розовый | 5 | Ижевск
P24 | труселя | красный | 20 | Челябинск
P25 | штуцерная деталь | коричневый | 450 | Череповец
P2 | шуруп | чёрный | 5 | Лондон
P26 | лезвие | бесцветный | 120 | Йокогама
(26 rows)

==== Изделия ====

<syntaxhighlight lang="sql">
SELECT * FROM spasoi_ekz.j;
</syntaxhighlight>

nomer_izdelia | nazvanie | gorod
---------------+-----------------------+-----------------
J1 | автомобиль | Магнитогорск
J2 | процессор | Зеленоград
J3 | торшер | Нижний Новгород
J4 | универсальное изделие | Париж
J5 | уникальное изделие | Бостон
J6 | велосипед 01/23 | Манчестер
J7 | изделие из болтов | Челябинск
J8 | шкаф | Ярославль
J9 | рама 02-01 | Череповец
J10 | книга | Астрахань
J11 | панно 01-03 | Флоренция
J12 | велосипед 03-04 | Клин
J13 | рама 02-03 | Череповец
J14 | штуцер 01-02 | Череповец
J15 | велосипед 01-04 | Клин
J16 | кружевное бельё | Челябинск
J17 | штуцер 01-03 | Череповец
J18 | кинжал | Йокогама
(18 rows)

==== Сборки ====

<syntaxhighlight lang="sql">
SELECT * FROM spasoi_ekz.spj;
</syntaxhighlight>

nomer_postavshika | nomer_detali | nomer_izdelia | kolichestvo
-------------------+--------------+---------------+-------------
S1 | P6 | J2 | 20
S1 | P7 | J2 | 5
S2 | P1 | J1 | 4
S6 | P4 | J1 | 2
S6 | P5 | J1 | 6
S3 | P9 | J3 | 1
S4 | P10 | J3 | 1
S5 | P11 | J3 | 1
S2 | P4 | J1 | 8
S6 | P3 | J1 | 50
S7 | P8 | J2 | 25
S1 | P12 | J4 | 1
S2 | P12 | J4 | 1
S3 | P12 | J4 | 1
S4 | P12 | J4 | 1
S5 | P12 | J4 | 1
S7 | P12 | J4 | 1
S7 | P2 | J1 | 1
S6 | P2 | J1 | 9
S6 | P12 | J4 | 7
S1 | P13 | J5 | 14
S6 | P14 | J1 | 9000
S2 | P14 | J1 | 3
S6 | P1 | J1 | 12
S8 | P15 | J6 | 1
S8 | P16 | J6 | 2
S3 | P5 | J6 | 10
S10 | P2 | J8 | 4
S8 | P1 | J7 | 16
S9 | P14 | J7 | 3
S11 | P10 | J3 | 2
S12 | P15 | J1 | 3
S11 | P11 | J3 | 4
S10 | P14 | J9 | 5
S13 | P17 | J10 | 1001
S14 | P17 | J10 | 1111
S15 | P17 | J10 | 1010
S5 | P18 | J11 | 9
S5 | P19 | J11 | 1
S5 | P20 | J11 | 4
S9 | P14 | J8 | 25
S12 | P21 | J12 | 15
S11 | P22 | J13 | 9
S11 | P1 | J14 | 26
S12 | P1 | J14 | 13
S12 | P2 | J14 | 54
S12 | P23 | J4 | 101
S12 | P16 | J15 | 40
S7 | P21 | J12 | 3
S8 | P21 | J12 | 4
S9 | P21 | J12 | 5
S1 | P24 | J16 | 1
S1 | P15 | J6 | 3
S4 | P25 | J17 | 1
S17 | P16 | J1 | 4
S18 | P26 | J18 | 1
(56 rows)

== Готовые запросы ==

=== Некоторое вступление ===

Если видите, что тот или иной запрос можно составить короче и рациональней - смело вносите правку.

==== Про только и только ====

В трёх билетах в задании на запрос встречается формулировка "''только и только''". Как оказалось, это означает следующее: если холодильники поставляет ''только и только'' Уася, то:
# кроме Уаси никто не поставляет холодильники;
# Уася не поставляет ничего, кроме холодильников.

<s>Великий и могучий русский языка, ну что за экономия на бумаге.</s>

Существующие запросы, естественно, оказались неправильными и их пришлось переписать.

==== Про JOIN ====

Почти все запросы, где используется соединение таблиц, можно написать с использованием <code>JOIN</code>, а можно просто с перечислением таблиц через запятую.

Но неожиданно оказалось, это почти то же самое и называется [http://ru.wikipedia.org/wiki/Join_%28SQL%29#CROSS_JOIN CROSS JOIN]. Такое соединение таблиц (<code>CROSS JOIN + WHERE</code>) считается устаревшим, поскольку его не рекомендует стандарт SQL ANSI. Таким образом, использование <code>JOIN</code> с условием соединения в <code>ON</code> является <s>хипстерством в программировании</s> более правильным и вообще прогрессивным.

=== Билет 1 ===

Написать запрос SELECT: выдать номера поставщиков, которые поставляют, по крайней мере, все те детали, которые поставляет поставщик с номером 'S2'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT DISTINCT nomer_postavshika
FROM spasoi_ekz.spj SPJX
WHERE NOT EXISTS (
SELECT nomer_detali
FROM spasoi_ekz.spj SPJY
WHERE nomer_postavshika = 'S2'
AND NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_postavshika = SPJX.nomer_postavshika
AND nomer_detali = SPJY.nomer_detali
)
);
</syntaxhighlight>

Еще один вариант:
<syntaxhighlight lang="sql">
WITH
s_all AS (
SELECT DISTINCT nomer_detali
FROM spasoi_ekz.spj SPJY
WHERE nomer_postavshika = 'S2'
)

SELECT
spj.nomer_postavshika
FROM spasoi_ekz.spj
INNER JOIN s_all ON s_all.nomer_detali = spj.nomer_detali
WHERE
nomer_postavshika != 'S2'
GROUP BY nomer_postavshika
HAVING count(spj.nomer_detali) = (SELECT count(*) FROM s_all);
</syntaxhighlight>

Результат:

nomer_postavshika
-------------------
S6
S2
(2 rows)

=== Билет 2 ===

Написать запрос SELECT: выдать номера деталей и общее их количество для всех номеров деталей, поставляемых более чем одним поставщиком.

Текст запроса, каким его дал Григорьев на лекции, впоследствии исправив его:

<syntaxhighlight lang="sql">
SELECT nomer_detali AS "Номер детали",
SUM(kolichestvo) AS "Сколько штук поставляется",
COUNT(DISTINCT nomer_postavshika) AS "Сколько у неё поставщиков"
FROM spasoi_ekz.spj
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_postavshika) > 1;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё вариант''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nomer_detali AS "Номер детали",
kol AS "Сколько штук поставляется"
FROM (
SELECT nomer_detali,
SUM(kolichestvo) AS kol,
COUNT(DISTINCT nomer_postavshika)
FROM spasoi_ekz.spj
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_postavshika) > 1
) A;
</syntaxhighlight>
</div>
</div>

Результат:

Номер детали | Сколько штук поставляется | Сколько у неё поставщиков
--------------+---------------------------+---------------------------
P1 | 71 | 5
P10 | 3 | 2
P11 | 5 | 2
P12 | 13 | 7
P14 | 9036 | 4
P15 | 7 | 3
P16 | 46 | 3
P17 | 3122 | 3
P2 | 68 | 4
P21 | 27 | 4
P4 | 10 | 2
P5 | 16 | 2
(12 rows)

=== Билет 3 ===

Написать запрос SELECT: выдать номера поставщиков, поставляющих детали с номером 'P1' для какого-либо изделия в количестве (в поставке) большим, чем средний объём поставок деталей с номером 'P2' для этого изделия.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT X.nomer_postavshika
FROM spasoi_ekz.spj X
WHERE X.nomer_detali = 'P1'
AND X.kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_izdelia = X.nomer_izdelia
AND nomer_detali = 'P2'
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос, но длиннее и нерациональней''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT DISTINCT nomer_postavshika
FROM (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_izdelia IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
)
AND nomer_izdelia IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P2'
)
) A
WHERE nomer_detali = 'P1' AND kolichestvo >
(
SELECT AVG(kolichestvo)
FROM (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_izdelia IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
)
AND nomer_izdelia IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P2'
)
) B
WHERE nomer_detali = 'P2'
);
</syntaxhighlight>
</div>
</div>

Результат:

nomer_postavshika
-------------------
S6
(1 row)

=== Билет 4 ===

Написать запрос SELECT: выдать номера изделий, для которых детали поставляет только поставщик с номером ‘S1’.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nomer_izdelia
FROM spasoi_ekz.spj X
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_postavshika != 'S1'
AND X.nomer_izdelia = nomer_izdelia
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос, но без EXISTS''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT DISTINCT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_izdelia IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_postavshika = 'S1'
)
AND nomer_izdelia NOT IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_postavshika != 'S1'
);
</syntaxhighlight>
</div>
</div>

Результат:

nomer_izdelia
---------------
J5
J16
(2 rows)

=== Билет 5 ===

Написать запрос SELECT: выдать имена поставщиков, поставляющих детали с названием "Болт" в количестве (в поставке) большим, чем средний объём всех поставок деталей с номером 'P1'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND nazvanie ILIKE 'Болт'
WHERE kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Вариант запроса без JOIN''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S, spasoi_ekz.p P, spasoi_ekz.spj SPJ
WHERE P.nazvanie = LOWER('Болт')
AND P.nomer_detali = SPJ.nomer_detali
AND S.nomer_postavshika = SPJ.nomer_postavshika
AND SPJ.kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
);
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё вариант запроса без JOIN и сложнее''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s
WHERE nomer_postavshika IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
WHERE nomer_detali = (
SELECT nomer_detali
FROM spasoi_ekz.p
WHERE LOWER(nazvanie) = LOWER('Болт')
)
AND kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
)
);
</syntaxhighlight>
</div>
</div>

Результат:

imya
------
Оша
Иванов
(2 rows)

=== Билет 6 ===

Написать запрос SELECT: выдать названия деталей, поставляемых поставщиком, проживающим в том же городе, где изготавливаются эти детали, для изделия с названием ‘Велосипед 01/23’.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT P.nazvanie
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE S.gorod = P.gorod
AND J.nazvanie = LOWER('Велосипед 01/23');
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос без JOIN''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT DISTINCT nazvanie
FROM spasoi_ekz.S S, spasoi_ekz.p P, spasoi_ekz.spj SPJ
WHERE S.gorod = P.gorod
AND P.nomer_detali = SPJ.nomer_detali
AND S.nomer_postavshika = SPJ.nomer_postavshika
AND nomer_izdelia = (
SELECT nomer_izdelia
FROM spasoi_ekz.j
WHERE nazvanie = LOWER('Велосипед 01/23')
);
</syntaxhighlight>
</div>
</div>

Результат:

nazvanie
----------
рама
колесо
(2 rows)

=== Билет 7 ===

Написать запрос SELECT: выдать названия изделий, куда входят детали с названием 'Болт', поставляемых поставщиками с именем 'Иванов', в количестве (в поставке) большим, чем средний объём поставок для этого изделия.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT J.nazvanie
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE imya LIKE '%Иванов%'
AND P.nazvanie = LOWER('Болт')
AND kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj X
WHERE SPJ.nomer_izdelia = X.nomer_izdelia
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё один вариант запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nazvanie
FROM spasoi_ekz.j
WHERE nomer_izdelia IN (
SELECT nomer_izdelia
FROM (spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
AND imya LIKE '%Иванов%'
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND nazvanie = LOWER('Болт')) A
WHERE kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = A.nomer_izdelia
)
);
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё вариант этого же запроса, но длиннее и нерациональней''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nazvanie
FROM (
SELECT J.nazvanie, kolichestvo
FROM spasoi_ekz.s S, spasoi_ekz.p P, spasoi_ekz.j J, spasoi_ekz.spj SPJ
WHERE J.nomer_izdelia = SPJ.nomer_izdelia
AND P.nomer_detali = SPJ.nomer_detali
AND P.nazvanie = LOWER('Болт')
AND S.imya LIKE '%Иванов%'
AND S.nomer_postavshika = SPJ.nomer_postavshika
) A
WHERE kolichestvo >
(
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_izdelia IN(
SELECT J.nomer_izdelia
FROM spasoi_ekz.s S, spasoi_ekz.p P, spasoi_ekz.j J, spasoi_ekz.spj SPJ
WHERE J.nomer_izdelia = SPJ.nomer_izdelia
AND P.nomer_detali = SPJ.nomer_detali
AND P.nazvanie = LOWER('Болт')
AND S.imya LIKE '%Иванов%'
AND S.nomer_postavshika = SPJ.nomer_postavshika
)
);
</syntaxhighlight>
</div>
</div>

Результат:

nazvanie
-------------------
шкаф
(1 row)

=== Билет 8 ===

Написать запрос SELECT: выдать номера изделий и общее количество деталей для них, поставляемых поставщиками с именем 'Петров'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nomer_izdelia AS "Номер изделия", SUM(kolichestvo) AS "Количество деталей"
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
-- так как "поставщикАМИ" и возможны
-- Иван Петров и Петров Иван, то LIKE %Петров%
AND imya LIKE '%Петров%'
GROUP BY nomer_izdelia;
</syntaxhighlight>

Результат:

Номер изделия | Количество деталей
---------------+--------------------
J4 | 101
J3 | 6
J13 | 9
J15 | 40
J1 | 3
J12 | 15
J14 | 93
(7 rows)

=== Билет 9 ===

Написать запрос SELECT: выдать номера деталей и общее их количество для всех номеров деталей, которые входят только в одно изделие.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nomer_detali, SUM(kolichestvo)
FROM spasoi_ekz.spj
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_izdelia) = 1;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё вариант''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nomer_detali, kol FROM (
SELECT nomer_detali, SUM(kolichestvo) AS kol, COUNT(DISTINCT nomer_izdelia) = 1
FROM spasoi_ekz.spj
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_izdelia) = 1
) A;
</syntaxhighlight>
</div>
</div>

Результат:

nomer_detali | sum
--------------+------
P10 | 3
P11 | 5
P12 | 13
P13 | 14
P17 | 3122
P18 | 9
P19 | 1
P20 | 4
P21 | 27
P22 | 9
P23 | 101
P24 | 1
P25 | 1
P26 | 1
P3 | 50
P4 | 10
P6 | 20
P7 | 5
P8 | 25
P9 | 1
(20 rows)

=== Билет 10 ===

Написать запрос SELECT: выдать имена поставщиков, поставляющих детали с названием 'Болт' для изделия с названием 'Рама 02-01' в количестве (в поставке) большим, чем минимальное значение поставки детали с номером 'P1'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Рама 02-01')
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND P.nazvanie = LOWER('Болт')
JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE kolichestvo > (
SELECT MIN(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
Этот же запрос без JOIN (точнее, с CROSS JOIN):
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT S.imya
FROM spasoi_ekz.s S,
spasoi_ekz.p P,
spasoi_ekz.j J,
spasoi_ekz.spj SPJ
WHERE SPJ.nomer_postavshika = S.nomer_postavshika
AND SPJ.nomer_detali = P.nomer_detali
AND SPJ.nomer_izdelia = J.nomer_izdelia
AND P.nazvanie = LOWER('Болт')
AND J.nazvanie = LOWER('Рама 02-01')
AND SPJ.kolichestvo > (
SELECT MIN(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
);

</syntaxhighlight>
</div>
</div>
Результат:

imya
-------------
Русе Болтон
(1 row)

=== Билет 11 ===

Написать запрос SELECT: выдать названия городов и суммарное состояние проживающих в каждом городе поставщиков, у которых минимальный объём поставки деталей больше 1000.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT gorod, SUM(sostoyanie)
FROM spasoi_ekz.s S
WHERE (
SELECT MIN(kolichestvo)
FROM spasoi_ekz.spj X
WHERE X.nomer_postavshika = S.nomer_postavshika
) > 1000
GROUP BY gorod;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё вариант''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT gorod, SUM(sostoyanie)
FROM spasoi_ekz.s
WHERE nomer_postavshika IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
GROUP BY nomer_postavshika HAVING MIN(kolichestvo) > 1000
)
GROUP BY gorod;
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё три неправильных варианта ''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
-- здесь сразу отбрасывается весь город,
-- в котором хоть один поставщик имеет миним. объем <=1000,
-- а надо, чтобы был отброшен только этот поставщик.
-- читайте описание конструкции GROUP BY ... HAVING ...
SELECT gorod, SUM(sostoyanie)
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
GROUP BY gorod HAVING MIN(kolichestvo) > 1000;

-- та же фигня
SELECT S.gorod, SUM(S.sostoyanie)--, MIN(SPJ.kolichestvo)
FROM spasoi_ekz.s S, spasoi_ekz.spj SPJ
WHERE S.nomer_postavshika = SPJ.nomer_postavshika
GROUP BY gorod HAVING MIN(kolichestvo) > 1000;

-- и ещё один почти верный
SELECT gorod, sost
FROM (
SELECT gorod, SUM(sostoyanie) AS sost, SUM(SPJ.kolichestvo)
FROM spasoi_ekz.spj, spasoi_ekz.s
WHERE S.nomer_postavshika = SPJ.nomer_postavshika
GROUP BY S.gorod HAVING SUM(SPJ.kolichestvo) > 1000
) A;
</syntaxhighlight>
</div>
</div>

Результат:

gorod | sum
-----------+---------
Прага | 2111110
Стокгольм | 888888
(2 rows)

=== Билет 12 ===

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

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nomer_detali
FROM spasoi_ekz.spj SPJ, spasoi_ekz.s S, spasoi_ekz.j J
WHERE SPJ.nomer_postavshika = S.nomer_postavshika
AND S.gorod = J.gorod
AND J.nomer_izdelia = SPJ.nomer_izdelia;
</syntaxhighlight>

Результат:

nomer_detali
--------------
P15
P16
P26
(3 rows)

=== Билет 13 ===

Написать <u>один</u> запрос SELECT: выдать количества строк в таблице S (Поставщик) 1) с пустыми значениями и 2) непустыми значениями в столбце Состояние.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT COUNT(*) - COUNT(sostoyanie) AS "С пустым состоянием",
COUNT(sostoyanie) AS "С не пустым состоянием"
FROM spasoi_ekz.s;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос, но длиннее и нерациональней''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">

SELECT COUNT(Snull.*) AS "С пустым состоянием", COUNT(Snotnull.sostoyanie) AS "С не пустым состоянием"
FROM spasoi_ekz.s Snull RIGHT OUTER JOIN spasoi_ekz.s Snotnull
ON Snull.nomer_postavshika = Snotnull.nomer_postavshika
AND Snull.sostoyanie IS NULL;
</syntaxhighlight>
</div>
</div>

Результат:

С пустым состоянием | С не пустым состоянием
---------------------+-----------------------
2 | 16
(1 row)

=== Билет 14 ===

Написать запрос SELECT: выдать имена поставщиков, которые поставляют детали только и только для изделия с номером 'J1'.

Чтобы продемонстрировать выполнение запроса наглядно, возьмём изделие не 'J1', а 'J18', которое было создано специально для этого запроса. Если оставить 'J1', то наглядности не получится, так как по нашей схеме БД это изделие не попадает под условие "только и только", и запрос вернёт пустой результат.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT DISTINCT imya
FROM spasoi_ekz.s S
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE (
nomer_izdelia != 'J18'
AND
nomer_postavshika = S.nomer_postavshika)
OR
(
nomer_izdelia = 'J18'
AND
nomer_postavshika != S.nomer_postavshika
)
);
</syntaxhighlight>


Результат:

imya
----------------
Безликий
(1 row)

=== Билет 15 ===

Написать запрос SELECT: выдать наименования изделий, для которых детали поставляют только те поставщики, у которых состояние больше 1000000 у.е.

Текст запроса:
<syntaxhighlight lang="sql">
SELECT DISTINCT nazvanie
FROM spasoi_ekz.j J JOIN spasoi_ekz.spj X
ON J.nomer_izdelia = X.nomer_izdelia
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON S.nomer_postavshika = SPJ.nomer_postavshika
WHERE sostoyanie <= 1000000
AND SPJ.nomer_izdelia = X.nomer_izdelia
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Неправильный вариант''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
-- Неверный запрос, если в таблице SPJ НЕТ поставок с этим изделием,
-- а в таблице J - есть это изделие, то запрос вернет его название, хотя не должен
SELECT DISTINCT nazvanie
FROM spasoi_ekz.j J
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj X JOIN spasoi_ekz.s S
ON X.nomer_postavshika = S.nomer_postavshika
WHERE sostoyanie <= 1000000
AND X.nomer_izdelia = J.nomer_izdelia
);
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''Вариант этого запроса без NOT EXISTS''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT DISTINCT nazvanie
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE sostoyanie > 1000000
AND SPJ.nomer_izdelia NOT IN (
SELECT nomer_izdelia
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE sostoyanie < 1000000
);
</syntaxhighlight>
</div>
</div>

Результат:

nazvanie
--------------------
кружевное бельё
уникальное изделие
процессор
(3 rows)

=== Билет 16 ===
Написать запрос SELECT: выдать цвета и для каждого цвета общее количество деталей, входящих в изделие с названием 'Панно 01-03'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT cvet AS "Цвет", SUM(kolichestvo) AS "Деталей"
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Панно 01-03')
JOIN spasoi_ekz.P P
ON SPJ.nomer_detali = P.nomer_detali
GROUP BY cvet;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Другой вариант запроса для тонко чувствующих натур''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT cvet AS "Цвет", SUM(kolichestvo) AS "Деталей"
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE J.nazvanie = LOWER('Панно 01-03')
GROUP BY cvet;
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё один вариант запроса от ненавистника JOIN''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT P.cvet, SUM(SPJ.kolichestvo)
FROM spasoi_ekz.p P,
spasoi_ekz.spj SPJ,
spasoi_ekz.j J
WHERE P.nomer_detali = SPJ.nomer_detali
AND J.nomer_izdelia = SPJ.nomer_izdelia
AND J.nazvanie = LOWER('Панно 01-03')
GROUP BY P.cvet;
</syntaxhighlight>
</div>
</div>

Результат:

Цвет | Деталей
-------+---------
белый | 9
серый | 5
(2 rows)

=== Билет 17 ===

Написать запрос SELECT: выдать номера поставщиков и количество сделанных ими поставок при условии, что среднее число деталей во всех этих поставках больше 1000.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nomer_postavshika AS "Номер поставщика",
COUNT(*) AS "Количество поставок"
FROM spasoi_ekz.spj
GROUP BY nomer_postavshika HAVING AVG(kolichestvo) > 1000;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё вариант запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nomer_postavshika AS "Номер поставщика", COUNT(*) AS "Количество поставок"
FROM spasoi_ekz.spj
WHERE nomer_postavshika IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
GROUP BY nomer_postavshika HAVING AVG(kolichestvo) > 1000
)
GROUP BY nomer_postavshika;
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё вариант запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nomer_postavshika AS "Номер поставщика", COUNT(*) AS "Количество поставок"
FROM spasoi_ekz.spj
WHERE (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj X
WHERE X.nomer_postavshika = spj.nomer_postavshika
) > 1000
GROUP BY nomer_postavshika;
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''И даже ещё вариант запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nom AS "Номер поставщика", cnt AS "Количество поставок"
FROM (
SELECT S.nomer_postavshika AS nom,
COUNT(SPJ.nomer_postavshika) AS cnt,
AVG(SPJ.kolichestvo) AS kol
FROM spasoi_ekz.s S, spasoi_ekz.spj SPJ
WHERE S.nomer_postavshika = SPJ.nomer_postavshika
GROUP BY S.nomer_postavshika
) A
WHERE kol > 1000;
</syntaxhighlight>
</div>
</div>

Результат:

Номер поставщика | Количество поставок
------------------+---------------------
S13 | 1
S6 | 7
S14 | 1
S15 | 1
(4 rows)

=== Билет 18 ===

Написать запрос SELECT: выдать имена поставщиков, имеющих состояние больше 1000 и поставляющих деталь с названием 'Гайка 01-01' для изделия с названием 'Велосипед 03-04' в количестве (в поставке) большим, чем средний объём поставки, выполненной поставщиками с именем 'Иванов'.

[[Файл:2nd dufficulty.png|center]]

<p align="center"><font size="5px">'''Второй по сложности запрос экзамена!'''</font></p>

<p align="center"><font size="4px">'''Вот уж не свезло, так не свезло'''</font></p>

Текст запроса:

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
JOIN spasoi_ekz.j J
ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE sostoyanie > 1000
AND P.nazvanie = LOWER('Гайка 01-01')
AND J.nazvanie = LOWER('Велосипед 03-04')
AND kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE S.imya = 'Иванов' -- или WHERE S.imya LIKE '%Иванов%',
-- поскольку сказано, что поставка выполнена поставщиками с именем Иванов,
-- а это могут быть разные имена и отчества. Короче, опять великий
-- русский язык в задании - что ещё за "с именем Иванов"
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё один вариант запроса от ненавистника JOIN:''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S,
spasoi_ekz.spj SPJ,
spasoi_ekz.p P,
spasoi_ekz.j J
WHERE SPJ.nomer_postavshika = S.nomer_postavshika
AND SPJ.nomer_detali = P.nomer_detali
AND SPJ.nomer_izdelia=J.nomer_izdelia
AND S.sostoyanie > 1000
AND P.nazvanie = LOWER('Гайка 01-01')
AND J.nazvanie = LOWER('Велосипед 03-04')
AND kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj SPJ2, spasoi_ekz.S S2
WHERE S2.nomer_postavshika = SPJ2.nomer_postavshika
AND S2.imya LIKE '%Иванов%'
);
</syntaxhighlight>
</div>
</div>

Результат:

imya
-------------
Петров Пётр
(1 row)

=== Билет 19 ===

Написать запрос SELECT: выдать названия красных деталей, которые входят только в изделие с названием 'Рама 02-03' в количестве, меньшем 10 единиц в поставке.

Примечание: Григорьев ругается на X.nomer_detali = SPJ.nomer_detali Лучше во вложенном подзапросе сделать еще один джойн p и приравнивать названия деталей.

Текст запроса:
<syntaxhighlight lang="sql">
SELECT DISTINCT X.nazvanie
FROM spasoi_ekz.p X JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_detali = X.nomer_detali
WHERE X.cvet = 'красный'
AND kolichestvo < 10
AND NOT EXISTS (
SELECT *
FROM spasoi_ekz.j J JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE J.nazvanie != LOWER('Рама 02-03')
AND X.nomer_detali = SPJ.nomer_detali
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё вариант этого же запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT P.nazvanie
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND cvet = 'красный'
JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Рама 02-03')
WHERE kolichestvo < 10
AND SPJ.nomer_detali NOT IN (
SELECT nomer_detali
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie != LOWER('Рама 02-03')
);
</syntaxhighlight>
</div>
</div>

Результат:

nazvanie
----------------
усиленная рама
(1 row)

=== Билет 20 ===

Написать запрос SELECT: выдать имена поставщиков, поставляющих только белые детали.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
WHERE nomer_postavshika = S.nomer_postavshika
AND P.cvet != 'белый'
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё один вариант этого же запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND cvet = 'белый'
JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE S.nomer_postavshika NOT IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND cvet != 'белый'
);
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё один вариант этого же запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s
WHERE nomer_postavshika NOT IN (
SELECT spj.nomer_postavshika
FROM spasoi_ekz.spj SPJ, spasoi_ekz.p P
WHERE SPJ.nomer_detali = P.nomer_detali
AND p.cvet != 'белый'
)
AND nomer_postavshika IN (
SELECT spj.nomer_postavshika
FROM spasoi_ekz.spj SPJ, spasoi_ekz.p P
WHERE SPJ.nomer_detali = P.nomer_detali
AND p.cvet = 'белый'
);
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё один вариант этого запроса от ненавистника JOIN''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S, spasoi_ekz.spj SPJ2
WHERE SPJ2.nomer_postavshika = S.nomer_postavshika
AND NOT EXISTS (
SELECT *
FROM spasoi_ekz.p P, spasoi_ekz.spj SPJ
WHERE SPJ.nomer_detali = P.nomer_detali
AND SPJ.nomer_postavshika = SPJ2.nomer_postavshika
AND P.cvet != 'белый'
);
</syntaxhighlight>
</div>
</div>

Результат:

imya
-----------------
Рендилл Тарли
Сэмвелл Тарли
Мелисса Флорент
Томми Версетти
(4 rows)

=== Билет 21 ===

Написать запрос SELECT: выдать названия изделий, для которых детали поставляет только и только поставщик с номером 'S1'.

Чтобы продемонстрировать выполнение запроса наглядно, возьмём поставщика не 'S1', а 'S18', который был создан специально для этого запроса. Если оставить 'S1', то наглядности не получится, так как по нашей схеме БД этот поставщик не попадает под условие "''только и только''", и запрос вернёт пустой результат.

Примечание: Григорьев ругается на nomer_izdelia = J.nomer_izdelia Лучше во вложенном подзапросе сделать еще один джойн j и приравнивать названия изделий, а не номера.
Текст запроса:

<syntaxhighlight lang="sql">
SELECT nazvanie
FROM spasoi_ekz.j
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_postavshika != 'S18'
AND nomer_izdelia = J.nomer_izdelia
)
AND NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_postavshika = 'S18'
AND nomer_izdelia != J.nomer_izdelia
);
</syntaxhighlight>

Результат:

nazvanie
--------------------
кинжал
(1 row)

=== Билет 22 ===

Написать запрос SELECT: выдать имена поставщиков, которые поставляют только детали с номером 'P1' для изделия с именем 'Штуцер 01-02'.

Текст запроса:
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Штуцер 01-02')
JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj X JOIN spasoi_ekz.j J
ON X.nomer_izdelia = J.nomer_izdelia
WHERE X.nomer_detali != 'P1'
AND J.nazvanie = LOWER('Штуцер 01-02')
AND X.nomer_postavshika = S.nomer_postavshika
);
</syntaxhighlight>


<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос, но длиннее и нерациональней''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Штуцер 01-02')
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND SPJ.nomer_detali = 'P1'
JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE SPJ.nomer_postavshika NOT IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Штуцер 01-02')
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND SPJ.nomer_detali != 'P1'
);
</syntaxhighlight>
</div>
</div>

Результат:

imya
-------------
Петров Иван
(1 row)

=== Билет 23 ===

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

Текст запроса:
<syntaxhighlight lang="sql">
SELECT S.imya
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON S.nomer_postavshika = SPJ.nomer_postavshika
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
WHERE P.nazvanie = LOWER('Винт')
AND SPJ.kolichestvo > 100
AND S.sostoyanie > (
SELECT AVG(SS.sostoyanie)
FROM spasoi_ekz.s SS
WHERE SS.gorod = S.gorod
);
</syntaxhighlight>

Результат:

imya
-------------
Петров Пётр
(1 row)

=== Билет 24 ===

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


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

То есть, вообще говоря, задание на запрос неполное, и его можно трактовать так: выдать наименования деталей и общее их количество для всех наименований деталей, изготавливаемых в городе <code>%НАЗВАНИЕГОРОДА%</code>. Вот эта переменная задаётся где-то в программе, а в БД идёт запрос с уже подставленным конкретным названием.

Этот вариант запроса составлен, например, для Лондона.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nazvanie, SUM(kolichestvo)
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p
ON SPJ.nomer_detali = P.nomer_detali
WHERE gorod = 'Лондон'
GROUP BY nazvanie;
</syntaxhighlight>

Результат:

nazvanie | sum
----------+-----
гайка | 71
шуруп | 68
(2 rows)

=== Билет 25 ===

Написать запрос SELECT: выдать имена поставщиков, поставляющих хотя бы одну белую деталь для изделия с названием 'Велосипед 01-04' с объёмом поставки большим, чем средний объём поставки этого поставщика.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
JOIN spasoi_ekz.j J
ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE cvet = 'белый'
AND J.nazvanie = LOWER('Велосипед 01-04')
AND kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj SPJ
WHERE SPJ.nomer_postavshika = S.nomer_postavshika
);
</syntaxhighlight>

Результат:

imya
-------------
Петров Пётр
(1 row)

=== Билет 26 ===

Написать запрос SELECT: выдать номера красных деталей и количество поставок этих деталей.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT P.nomer_detali AS "Номер детали", COUNT(kolichestvo) AS "Количество поставок с ней"
FROM spasoi_ekz.p P, spasoi_ekz.spj SPJ
WHERE P.nomer_detali = SPJ.nomer_detali
AND cvet = 'красный'
GROUP BY P.nomer_detali;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос через JOIN''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT SPJ.nomer_detali AS "Номер детали", COUNT(kolichestvo) AS "Количество поставок с ней"
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND cvet = 'красный'
GROUP BY SPJ.nomer_detali;
</syntaxhighlight>
</div>
</div>

Результат:

Номер детали | Количество поставок с ней
--------------+---------------------------
P15 | 3
P22 | 1
P24 | 1
(3 rows)

=== Билет 27 ===

Написать запрос SELECT: выдать наименования городов и среднее состояние поставщиков для каждого города, поставляющих детали с названием 'Гайка 01-01' для изделия с названием 'Велосипед 03-04' в количестве (в поставке) большим, чем минимальный объём поставки, выполненной поставщиком с номером 'S1'.

[[Файл:1st dufficulty.png|center]]

<p align="center"><font size="7px">'''Сложнейший запрос экзамена!'''</font></p>

<p align="center"><font size="5px">'''Сохрани Джа, вытащить такое'''</font></p>

Текст запроса:

<syntaxhighlight lang="sql">
SELECT S.gorod AS "Город", AVG(S.sostoyanie) AS "Среднее состояние"
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
JOIN spasoi_ekz.j J
ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE P.nazvanie = LOWER('Гайка 01-01')
AND J.nazvanie = LOWER('Велосипед 03-04')
AND kolichestvo > (
SELECT MIN(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_postavshika = 'S1'
)
GROUP BY S.gorod;

</syntaxhighlight>

Результат:

Город | Среднее состояние
-----------+-----------------------
Мытищи | 35000.500000000000
Йокогама | 1500000.000000000000
Манчестер | 2571.0000000000000000
(3 rows)

=== Билет 28 ===

Написать запрос SELECT: выдать названия изделий, куда входит хотя бы одна красная деталь весом больше 10 граммов, поставляемая только поставщиком с номером 'S1'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT J.nazvanie
FROM spasoi_ekz.j J JOIN spasoi_ekz.spj SPJ1
ON J.nomer_izdelia = SPJ1.nomer_izdelia
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ1.nomer_detali
WHERE P.ves > 10
AND P.cvet = 'красный'
AND NOT EXISTS (
SELECT SPJ2.nomer_postavshika
FROM spasoi_ekz.spj SPJ2
WHERE SPJ2.nomer_detali = P.nomer_detali
AND SPJ2.nomer_postavshika != 'S1'
);
</syntaxhighlight>

Результат:

nazvanie
-----------------
кружевное бельё
(1 row)

=== Билет 29 ===
Написать запрос SELECT: выдать названия деталей, которые входят только и только в состав изделия с названием 'Штуцер 01-03'.

Примечание: Григорьев ругается на SPJ.nomer_detali = P.nomer_detali Лучше во вложенном подзапросе сделать еще один джойн p и приравнивать названия деталей.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT DISTINCT nazvanie
FROM spasoi_ekz.p
WHERE NOT EXISTS (
SELECT SPJ.nomer_izdelia
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE (
J.nazvanie != LOWER('Штуцер 01-03')
AND
SPJ.nomer_detali = P.nomer_detali
)
OR (
J.nazvanie = LOWER('Штуцер 01-03')
AND
SPJ.nomer_detali != P.nomer_detali
)
);
</syntaxhighlight>

Результат:

nazvanie
------------------
штуцерная деталь
(1 row)

=== Билет 30 ===
Написать запрос SELECT: выдать имена поставщиков, которые поставляют, по крайней мере, все те детали, которые поставляет поставщик с номером 'S2'.

Текст запроса:
<syntaxhighlight lang="sql">
SELECT DISTINCT imya
FROM spasoi_ekz.s S
WHERE NOT EXISTS (
SELECT nomer_detali
FROM spasoi_ekz.spj SPJY
WHERE nomer_postavshika = 'S2'
AND NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_postavshika = S.nomer_postavshika
AND nomer_detali = SPJY.nomer_detali
)
);
</syntaxhighlight>

Результат:

imya
------------
Оша
Бран Старк
(2 rows)
[[Категория:Структурное проектирование АСОИ (10 семестр)]]

SQL-запросы к экзамену по СПАСОИ (10 семестр)

2016-06-04T15:08:47Z

195.91.240.97: /* Билет 1 */

<div style="float:right; clear:both; margin-right:1.0em;">__TOC__</div>

Билет экзамена по [[:Категория:Структурное проектирование АСОИ (10 семестр) | СПАСОИ]] состоит из двух частей:
# теория;
# SQL-запрос.

На этой странице собраны все сформированные запросы по билетам.

Странно, что ни в одном билете нет запроса с сортировкой результатов. <s>Возможно, они будут в дополнительных заданиях.</s> Григорьев сказал, что это слишком просто и потому он не стал загромождать запросы дополнительными мелочами.

== Схема БД ==

Схема БД используется всё [[СПАСОИ_(10)_-_Лекция_№8_-_SQL#Некоторые возможности языка SQL | та же]], что была в прошлом семестре и на лекциях.

Для написания запросов и проверки их выполнения создана БД в СУБД [http://www.postgresql.org/ PostgreSQL].

Скрипт создания можно загрузить [http://yadi.sk/d/ArwqOGAy5pPfi отсюда].

=== Таблицы БД ===

==== Поставщики ====

<syntaxhighlight lang="sql">
SELECT * FROM spasoi_ekz.s;
</syntaxhighlight>

nomer_postavshika | imya | sostoyanie | gorod
-------------------+------------------+------------+------------
S5 | Мелисандра | 65000 | Мадрид
S2 | Бран Старк | 60000 | Мурманск
S1 | Якен Хгар | 1500000 | Йокогама
S7 | Сирио Форель | 1500000 | Йокогама
S4 | Джейме Ланнистер | 750000 | Лондон
S3 | Серсея Ланнистер | 1200000 | Лондон
S8 | Тирион Ланнистер | 2571 | Манчестер
S9 | Иванов | 35000 | Мытищи
S10 | Русе Болтон | 44444 | Баренцбург
S11 | Петров Иван | 35000 | Мытищи
S14 | Рендилл Тарли | 1111111 | Прага
S13 | Сэмвелл Тарли | 999999 | Прага
S6 | Оша | | Москва
S16 | Ходор | | Москва
S15 | Мелисса Флорент | 888888 | Стокгольм
S12 | Петров Пётр | 35001 | Мытищи
S17 | Томми Версетти | 123456789 | Майами
S18 | Безликий | 1 | Йокогама
(18 rows)

==== Детали ====

<syntaxhighlight lang="sql">
SELECT * FROM spasoi_ekz.p;
</syntaxhighlight>

nomer_detali | nazvanie | cvet | ves | gorod
--------------+----------------------+---------------+------+-----------------
P9 | подставка | синий | 400 | Нижний Новгород
P10 | стойка | синий | 2000 | Нижний Новгород
P11 | абажур | синий | 400 | Нижний Новгород
P1 | гайка | чёрный | 20 | Лондон
P3 | ось | белый | 5000 | Эдинбург
P4 | зубчатое колесо | чёрный | 50 | Эдинбург
P6 | транзистор | коричневый | 2 | Токио
P7 | печатная плата | зелёный | 200 | Токио
P8 | диод | коричневый | 1 | Токио
P12 | универсальная деталь | универсальный | 1 | Париж
P13 | уникальная деталь | уникальный | 1 | Бостон
P14 | болт | серый | 20 | Ижевск
P15 | рама | красный | 3000 | Манчестер
P16 | колесо | белый | 1500 | Манчестер
P5 | втулка | серый | 350 | Манчестер
P17 | бумага | белый | 1 | Астрахань
P18 | плитка | белый | 300 | Флоренция
P19 | орнамент | серый | 800 | Флоренция
P20 | уголок | серый | 100 | Флоренция
P21 | гайка 01-01 | серебристый | 20 | Клин
P22 | усиленная рама | красный | 3200 | Череповец
P23 | винт | розовый | 5 | Ижевск
P24 | труселя | красный | 20 | Челябинск
P25 | штуцерная деталь | коричневый | 450 | Череповец
P2 | шуруп | чёрный | 5 | Лондон
P26 | лезвие | бесцветный | 120 | Йокогама
(26 rows)

==== Изделия ====

<syntaxhighlight lang="sql">
SELECT * FROM spasoi_ekz.j;
</syntaxhighlight>

nomer_izdelia | nazvanie | gorod
---------------+-----------------------+-----------------
J1 | автомобиль | Магнитогорск
J2 | процессор | Зеленоград
J3 | торшер | Нижний Новгород
J4 | универсальное изделие | Париж
J5 | уникальное изделие | Бостон
J6 | велосипед 01/23 | Манчестер
J7 | изделие из болтов | Челябинск
J8 | шкаф | Ярославль
J9 | рама 02-01 | Череповец
J10 | книга | Астрахань
J11 | панно 01-03 | Флоренция
J12 | велосипед 03-04 | Клин
J13 | рама 02-03 | Череповец
J14 | штуцер 01-02 | Череповец
J15 | велосипед 01-04 | Клин
J16 | кружевное бельё | Челябинск
J17 | штуцер 01-03 | Череповец
J18 | кинжал | Йокогама
(18 rows)

==== Сборки ====

<syntaxhighlight lang="sql">
SELECT * FROM spasoi_ekz.spj;
</syntaxhighlight>

nomer_postavshika | nomer_detali | nomer_izdelia | kolichestvo
-------------------+--------------+---------------+-------------
S1 | P6 | J2 | 20
S1 | P7 | J2 | 5
S2 | P1 | J1 | 4
S6 | P4 | J1 | 2
S6 | P5 | J1 | 6
S3 | P9 | J3 | 1
S4 | P10 | J3 | 1
S5 | P11 | J3 | 1
S2 | P4 | J1 | 8
S6 | P3 | J1 | 50
S7 | P8 | J2 | 25
S1 | P12 | J4 | 1
S2 | P12 | J4 | 1
S3 | P12 | J4 | 1
S4 | P12 | J4 | 1
S5 | P12 | J4 | 1
S7 | P12 | J4 | 1
S7 | P2 | J1 | 1
S6 | P2 | J1 | 9
S6 | P12 | J4 | 7
S1 | P13 | J5 | 14
S6 | P14 | J1 | 9000
S2 | P14 | J1 | 3
S6 | P1 | J1 | 12
S8 | P15 | J6 | 1
S8 | P16 | J6 | 2
S3 | P5 | J6 | 10
S10 | P2 | J8 | 4
S8 | P1 | J7 | 16
S9 | P14 | J7 | 3
S11 | P10 | J3 | 2
S12 | P15 | J1 | 3
S11 | P11 | J3 | 4
S10 | P14 | J9 | 5
S13 | P17 | J10 | 1001
S14 | P17 | J10 | 1111
S15 | P17 | J10 | 1010
S5 | P18 | J11 | 9
S5 | P19 | J11 | 1
S5 | P20 | J11 | 4
S9 | P14 | J8 | 25
S12 | P21 | J12 | 15
S11 | P22 | J13 | 9
S11 | P1 | J14 | 26
S12 | P1 | J14 | 13
S12 | P2 | J14 | 54
S12 | P23 | J4 | 101
S12 | P16 | J15 | 40
S7 | P21 | J12 | 3
S8 | P21 | J12 | 4
S9 | P21 | J12 | 5
S1 | P24 | J16 | 1
S1 | P15 | J6 | 3
S4 | P25 | J17 | 1
S17 | P16 | J1 | 4
S18 | P26 | J18 | 1
(56 rows)

== Готовые запросы ==

=== Некоторое вступление ===

Если видите, что тот или иной запрос можно составить короче и рациональней - смело вносите правку.

==== Про только и только ====

В трёх билетах в задании на запрос встречается формулировка "''только и только''". Как оказалось, это означает следующее: если холодильники поставляет ''только и только'' Уася, то:
# кроме Уаси никто не поставляет холодильники;
# Уася не поставляет ничего, кроме холодильников.

<s>Великий и могучий русский языка, ну что за экономия на бумаге.</s>

Существующие запросы, естественно, оказались неправильными и их пришлось переписать.

==== Про JOIN ====

Почти все запросы, где используется соединение таблиц, можно написать с использованием <code>JOIN</code>, а можно просто с перечислением таблиц через запятую.

Но неожиданно оказалось, это почти то же самое и называется [http://ru.wikipedia.org/wiki/Join_%28SQL%29#CROSS_JOIN CROSS JOIN]. Такое соединение таблиц (<code>CROSS JOIN + WHERE</code>) считается устаревшим, поскольку его не рекомендует стандарт SQL ANSI. Таким образом, использование <code>JOIN</code> с условием соединения в <code>ON</code> является <s>хипстерством в программировании</s> более правильным и вообще прогрессивным.

=== Билет 1 ===

Написать запрос SELECT: выдать номера поставщиков, которые поставляют, по крайней мере, все те детали, которые поставляет поставщик с номером 'S2'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT DISTINCT nomer_postavshika
FROM spasoi_ekz.spj SPJX
WHERE NOT EXISTS (
SELECT nomer_detali
FROM spasoi_ekz.spj SPJY
WHERE nomer_postavshika = 'S2'
AND NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_postavshika = SPJX.nomer_postavshika
AND nomer_detali = SPJY.nomer_detali
)
);
</syntaxhighlight>

Еще один вариант:
<syntaxhighlight lang="sql">
WITH
s_all AS (
SELECT DISTINCT nomer_detali
FROM spasoi_ekz.spj SPJY
WHERE nomer_postavshika = 'S2'
)

SELECT
spj.nomer_postavshika
FROM spasoi_ekz.spj
INNER JOIN s_all ON s_all.nomer_detali = spj.nomer_detali
WHERE
nomer_postavshika != 'S2'
GROUP BY nomer_postavshika
HAVING count(spj.nomer_detali) = (SELECT count(*) FROM s_all);
</syntaxhighlight>

Результат:

nomer_postavshika
-------------------
S6
S2
(2 rows)

=== Билет 2 ===

Написать запрос SELECT: выдать номера деталей и общее их количество для всех номеров деталей, поставляемых более чем одним поставщиком.

Текст запроса, каким его дал Григорьев на лекции, впоследствии исправив его:

<syntaxhighlight lang="sql">
SELECT nomer_detali AS "Номер детали",
SUM(kolichestvo) AS "Сколько штук поставляется",
COUNT(DISTINCT nomer_postavshika) AS "Сколько у неё поставщиков"
FROM spasoi_ekz.spj
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_postavshika) > 1;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё вариант''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nomer_detali AS "Номер детали",
kol AS "Сколько штук поставляется"
FROM (
SELECT nomer_detali,
SUM(kolichestvo) AS kol,
COUNT(DISTINCT nomer_postavshika)
FROM spasoi_ekz.spj
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_postavshika) > 1
) A;
</syntaxhighlight>
</div>
</div>

Результат:

Номер детали | Сколько штук поставляется | Сколько у неё поставщиков
--------------+---------------------------+---------------------------
P1 | 71 | 5
P10 | 3 | 2
P11 | 5 | 2
P12 | 13 | 7
P14 | 9036 | 4
P15 | 7 | 3
P16 | 46 | 3
P17 | 3122 | 3
P2 | 68 | 4
P21 | 27 | 4
P4 | 10 | 2
P5 | 16 | 2
(12 rows)

=== Билет 3 ===

Написать запрос SELECT: выдать номера поставщиков, поставляющих детали с номером 'P1' для какого-либо изделия в количестве (в поставке) большим, чем средний объём поставок деталей с номером 'P2' для этого изделия.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT X.nomer_postavshika
FROM spasoi_ekz.spj X
WHERE X.nomer_detali = 'P1'
AND X.kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_izdelia = X.nomer_izdelia
AND nomer_detali = 'P2'
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос, но длиннее и нерациональней''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT DISTINCT nomer_postavshika
FROM (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_izdelia IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
)
AND nomer_izdelia IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P2'
)
) A
WHERE nomer_detali = 'P1' AND kolichestvo >
(
SELECT AVG(kolichestvo)
FROM (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_izdelia IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
)
AND nomer_izdelia IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P2'
)
) B
WHERE nomer_detali = 'P2'
);
</syntaxhighlight>
</div>
</div>

Результат:

nomer_postavshika
-------------------
S6
(1 row)

=== Билет 4 ===

Написать запрос SELECT: выдать номера изделий, для которых детали поставляет только поставщик с номером ‘S1’.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nomer_izdelia
FROM spasoi_ekz.spj X
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_postavshika != 'S1'
AND X.nomer_izdelia = nomer_izdelia
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос, но без EXISTS''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT DISTINCT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_izdelia IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_postavshika = 'S1'
)
AND nomer_izdelia NOT IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_postavshika != 'S1'
);
</syntaxhighlight>
</div>
</div>

Результат:

nomer_izdelia
---------------
J5
J16
(2 rows)

=== Билет 5 ===

Написать запрос SELECT: выдать имена поставщиков, поставляющих детали с названием "Болт" в количестве (в поставке) большим, чем средний объём всех поставок деталей с номером 'P1'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND nazvanie = LOWER('Болт')
WHERE kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Вариант запроса без JOIN''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S, spasoi_ekz.p P, spasoi_ekz.spj SPJ
WHERE P.nazvanie = LOWER('Болт')
AND P.nomer_detali = SPJ.nomer_detali
AND S.nomer_postavshika = SPJ.nomer_postavshika
AND SPJ.kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
);
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё вариант запроса без JOIN и сложнее''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s
WHERE nomer_postavshika IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
WHERE nomer_detali = (
SELECT nomer_detali
FROM spasoi_ekz.p
WHERE LOWER(nazvanie) = LOWER('Болт')
)
AND kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
)
);
</syntaxhighlight>
</div>
</div>

Результат:

imya
------
Оша
Иванов
(2 rows)

=== Билет 6 ===

Написать запрос SELECT: выдать названия деталей, поставляемых поставщиком, проживающим в том же городе, где изготавливаются эти детали, для изделия с названием ‘Велосипед 01/23’.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT P.nazvanie
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE S.gorod = P.gorod
AND J.nazvanie = LOWER('Велосипед 01/23');
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос без JOIN''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT DISTINCT nazvanie
FROM spasoi_ekz.S S, spasoi_ekz.p P, spasoi_ekz.spj SPJ
WHERE S.gorod = P.gorod
AND P.nomer_detali = SPJ.nomer_detali
AND S.nomer_postavshika = SPJ.nomer_postavshika
AND nomer_izdelia = (
SELECT nomer_izdelia
FROM spasoi_ekz.j
WHERE nazvanie = LOWER('Велосипед 01/23')
);
</syntaxhighlight>
</div>
</div>

Результат:

nazvanie
----------
рама
колесо
(2 rows)

=== Билет 7 ===

Написать запрос SELECT: выдать названия изделий, куда входят детали с названием 'Болт', поставляемых поставщиками с именем 'Иванов', в количестве (в поставке) большим, чем средний объём поставок для этого изделия.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT J.nazvanie
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE imya LIKE '%Иванов%'
AND P.nazvanie = LOWER('Болт')
AND kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj X
WHERE SPJ.nomer_izdelia = X.nomer_izdelia
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё один вариант запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nazvanie
FROM spasoi_ekz.j
WHERE nomer_izdelia IN (
SELECT nomer_izdelia
FROM (spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
AND imya LIKE '%Иванов%'
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND nazvanie = LOWER('Болт')) A
WHERE kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = A.nomer_izdelia
)
);
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё вариант этого же запроса, но длиннее и нерациональней''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nazvanie
FROM (
SELECT J.nazvanie, kolichestvo
FROM spasoi_ekz.s S, spasoi_ekz.p P, spasoi_ekz.j J, spasoi_ekz.spj SPJ
WHERE J.nomer_izdelia = SPJ.nomer_izdelia
AND P.nomer_detali = SPJ.nomer_detali
AND P.nazvanie = LOWER('Болт')
AND S.imya LIKE '%Иванов%'
AND S.nomer_postavshika = SPJ.nomer_postavshika
) A
WHERE kolichestvo >
(
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_izdelia IN(
SELECT J.nomer_izdelia
FROM spasoi_ekz.s S, spasoi_ekz.p P, spasoi_ekz.j J, spasoi_ekz.spj SPJ
WHERE J.nomer_izdelia = SPJ.nomer_izdelia
AND P.nomer_detali = SPJ.nomer_detali
AND P.nazvanie = LOWER('Болт')
AND S.imya LIKE '%Иванов%'
AND S.nomer_postavshika = SPJ.nomer_postavshika
)
);
</syntaxhighlight>
</div>
</div>

Результат:

nazvanie
-------------------
шкаф
(1 row)

=== Билет 8 ===

Написать запрос SELECT: выдать номера изделий и общее количество деталей для них, поставляемых поставщиками с именем 'Петров'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nomer_izdelia AS "Номер изделия", SUM(kolichestvo) AS "Количество деталей"
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
-- так как "поставщикАМИ" и возможны
-- Иван Петров и Петров Иван, то LIKE %Петров%
AND imya LIKE '%Петров%'
GROUP BY nomer_izdelia;
</syntaxhighlight>

Результат:

Номер изделия | Количество деталей
---------------+--------------------
J4 | 101
J3 | 6
J13 | 9
J15 | 40
J1 | 3
J12 | 15
J14 | 93
(7 rows)

=== Билет 9 ===

Написать запрос SELECT: выдать номера деталей и общее их количество для всех номеров деталей, которые входят только в одно изделие.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nomer_detali, SUM(kolichestvo)
FROM spasoi_ekz.spj
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_izdelia) = 1;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё вариант''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nomer_detali, kol FROM (
SELECT nomer_detali, SUM(kolichestvo) AS kol, COUNT(DISTINCT nomer_izdelia) = 1
FROM spasoi_ekz.spj
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_izdelia) = 1
) A;
</syntaxhighlight>
</div>
</div>

Результат:

nomer_detali | sum
--------------+------
P10 | 3
P11 | 5
P12 | 13
P13 | 14
P17 | 3122
P18 | 9
P19 | 1
P20 | 4
P21 | 27
P22 | 9
P23 | 101
P24 | 1
P25 | 1
P26 | 1
P3 | 50
P4 | 10
P6 | 20
P7 | 5
P8 | 25
P9 | 1
(20 rows)

=== Билет 10 ===

Написать запрос SELECT: выдать имена поставщиков, поставляющих детали с названием 'Болт' для изделия с названием 'Рама 02-01' в количестве (в поставке) большим, чем минимальное значение поставки детали с номером 'P1'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Рама 02-01')
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND P.nazvanie = LOWER('Болт')
JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE kolichestvo > (
SELECT MIN(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
Этот же запрос без JOIN (точнее, с CROSS JOIN):
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT S.imya
FROM spasoi_ekz.s S,
spasoi_ekz.p P,
spasoi_ekz.j J,
spasoi_ekz.spj SPJ
WHERE SPJ.nomer_postavshika = S.nomer_postavshika
AND SPJ.nomer_detali = P.nomer_detali
AND SPJ.nomer_izdelia = J.nomer_izdelia
AND P.nazvanie = LOWER('Болт')
AND J.nazvanie = LOWER('Рама 02-01')
AND SPJ.kolichestvo > (
SELECT MIN(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
);

</syntaxhighlight>
</div>
</div>
Результат:

imya
-------------
Русе Болтон
(1 row)

=== Билет 11 ===

Написать запрос SELECT: выдать названия городов и суммарное состояние проживающих в каждом городе поставщиков, у которых минимальный объём поставки деталей больше 1000.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT gorod, SUM(sostoyanie)
FROM spasoi_ekz.s S
WHERE (
SELECT MIN(kolichestvo)
FROM spasoi_ekz.spj X
WHERE X.nomer_postavshika = S.nomer_postavshika
) > 1000
GROUP BY gorod;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё вариант''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT gorod, SUM(sostoyanie)
FROM spasoi_ekz.s
WHERE nomer_postavshika IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
GROUP BY nomer_postavshika HAVING MIN(kolichestvo) > 1000
)
GROUP BY gorod;
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё три неправильных варианта ''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
-- здесь сразу отбрасывается весь город,
-- в котором хоть один поставщик имеет миним. объем <=1000,
-- а надо, чтобы был отброшен только этот поставщик.
-- читайте описание конструкции GROUP BY ... HAVING ...
SELECT gorod, SUM(sostoyanie)
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
GROUP BY gorod HAVING MIN(kolichestvo) > 1000;

-- та же фигня
SELECT S.gorod, SUM(S.sostoyanie)--, MIN(SPJ.kolichestvo)
FROM spasoi_ekz.s S, spasoi_ekz.spj SPJ
WHERE S.nomer_postavshika = SPJ.nomer_postavshika
GROUP BY gorod HAVING MIN(kolichestvo) > 1000;

-- и ещё один почти верный
SELECT gorod, sost
FROM (
SELECT gorod, SUM(sostoyanie) AS sost, SUM(SPJ.kolichestvo)
FROM spasoi_ekz.spj, spasoi_ekz.s
WHERE S.nomer_postavshika = SPJ.nomer_postavshika
GROUP BY S.gorod HAVING SUM(SPJ.kolichestvo) > 1000
) A;
</syntaxhighlight>
</div>
</div>

Результат:

gorod | sum
-----------+---------
Прага | 2111110
Стокгольм | 888888
(2 rows)

=== Билет 12 ===

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

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nomer_detali
FROM spasoi_ekz.spj SPJ, spasoi_ekz.s S, spasoi_ekz.j J
WHERE SPJ.nomer_postavshika = S.nomer_postavshika
AND S.gorod = J.gorod
AND J.nomer_izdelia = SPJ.nomer_izdelia;
</syntaxhighlight>

Результат:

nomer_detali
--------------
P15
P16
P26
(3 rows)

=== Билет 13 ===

Написать <u>один</u> запрос SELECT: выдать количества строк в таблице S (Поставщик) 1) с пустыми значениями и 2) непустыми значениями в столбце Состояние.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT COUNT(*) - COUNT(sostoyanie) AS "С пустым состоянием",
COUNT(sostoyanie) AS "С не пустым состоянием"
FROM spasoi_ekz.s;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос, но длиннее и нерациональней''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">

SELECT COUNT(Snull.*) AS "С пустым состоянием", COUNT(Snotnull.sostoyanie) AS "С не пустым состоянием"
FROM spasoi_ekz.s Snull RIGHT OUTER JOIN spasoi_ekz.s Snotnull
ON Snull.nomer_postavshika = Snotnull.nomer_postavshika
AND Snull.sostoyanie IS NULL;
</syntaxhighlight>
</div>
</div>

Результат:

С пустым состоянием | С не пустым состоянием
---------------------+-----------------------
2 | 16
(1 row)

=== Билет 14 ===

Написать запрос SELECT: выдать имена поставщиков, которые поставляют детали только и только для изделия с номером 'J1'.

Чтобы продемонстрировать выполнение запроса наглядно, возьмём изделие не 'J1', а 'J18', которое было создано специально для этого запроса. Если оставить 'J1', то наглядности не получится, так как по нашей схеме БД это изделие не попадает под условие "только и только", и запрос вернёт пустой результат.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT DISTINCT imya
FROM spasoi_ekz.s S
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE (
nomer_izdelia != 'J18'
AND
nomer_postavshika = S.nomer_postavshika)
OR
(
nomer_izdelia = 'J18'
AND
nomer_postavshika != S.nomer_postavshika
)
);
</syntaxhighlight>


Результат:

imya
----------------
Безликий
(1 row)

=== Билет 15 ===

Написать запрос SELECT: выдать наименования изделий, для которых детали поставляют только те поставщики, у которых состояние больше 1000000 у.е.

Текст запроса:
<syntaxhighlight lang="sql">
SELECT DISTINCT nazvanie
FROM spasoi_ekz.j J JOIN spasoi_ekz.spj X
ON J.nomer_izdelia = X.nomer_izdelia
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON S.nomer_postavshika = SPJ.nomer_postavshika
WHERE sostoyanie <= 1000000
AND SPJ.nomer_izdelia = X.nomer_izdelia
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Неправильный вариант''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
-- Неверный запрос, если в таблице SPJ НЕТ поставок с этим изделием,
-- а в таблице J - есть это изделие, то запрос вернет его название, хотя не должен
SELECT DISTINCT nazvanie
FROM spasoi_ekz.j J
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj X JOIN spasoi_ekz.s S
ON X.nomer_postavshika = S.nomer_postavshika
WHERE sostoyanie <= 1000000
AND X.nomer_izdelia = J.nomer_izdelia
);
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''Вариант этого запроса без NOT EXISTS''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT DISTINCT nazvanie
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE sostoyanie > 1000000
AND SPJ.nomer_izdelia NOT IN (
SELECT nomer_izdelia
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE sostoyanie < 1000000
);
</syntaxhighlight>
</div>
</div>

Результат:

nazvanie
--------------------
кружевное бельё
уникальное изделие
процессор
(3 rows)

=== Билет 16 ===
Написать запрос SELECT: выдать цвета и для каждого цвета общее количество деталей, входящих в изделие с названием 'Панно 01-03'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT cvet AS "Цвет", SUM(kolichestvo) AS "Деталей"
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Панно 01-03')
JOIN spasoi_ekz.P P
ON SPJ.nomer_detali = P.nomer_detali
GROUP BY cvet;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Другой вариант запроса для тонко чувствующих натур''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT cvet AS "Цвет", SUM(kolichestvo) AS "Деталей"
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE J.nazvanie = LOWER('Панно 01-03')
GROUP BY cvet;
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё один вариант запроса от ненавистника JOIN''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT P.cvet, SUM(SPJ.kolichestvo)
FROM spasoi_ekz.p P,
spasoi_ekz.spj SPJ,
spasoi_ekz.j J
WHERE P.nomer_detali = SPJ.nomer_detali
AND J.nomer_izdelia = SPJ.nomer_izdelia
AND J.nazvanie = LOWER('Панно 01-03')
GROUP BY P.cvet;
</syntaxhighlight>
</div>
</div>

Результат:

Цвет | Деталей
-------+---------
белый | 9
серый | 5
(2 rows)

=== Билет 17 ===

Написать запрос SELECT: выдать номера поставщиков и количество сделанных ими поставок при условии, что среднее число деталей во всех этих поставках больше 1000.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nomer_postavshika AS "Номер поставщика",
COUNT(*) AS "Количество поставок"
FROM spasoi_ekz.spj
GROUP BY nomer_postavshika HAVING AVG(kolichestvo) > 1000;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё вариант запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nomer_postavshika AS "Номер поставщика", COUNT(*) AS "Количество поставок"
FROM spasoi_ekz.spj
WHERE nomer_postavshika IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
GROUP BY nomer_postavshika HAVING AVG(kolichestvo) > 1000
)
GROUP BY nomer_postavshika;
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё вариант запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nomer_postavshika AS "Номер поставщика", COUNT(*) AS "Количество поставок"
FROM spasoi_ekz.spj
WHERE (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj X
WHERE X.nomer_postavshika = spj.nomer_postavshika
) > 1000
GROUP BY nomer_postavshika;
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''И даже ещё вариант запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nom AS "Номер поставщика", cnt AS "Количество поставок"
FROM (
SELECT S.nomer_postavshika AS nom,
COUNT(SPJ.nomer_postavshika) AS cnt,
AVG(SPJ.kolichestvo) AS kol
FROM spasoi_ekz.s S, spasoi_ekz.spj SPJ
WHERE S.nomer_postavshika = SPJ.nomer_postavshika
GROUP BY S.nomer_postavshika
) A
WHERE kol > 1000;
</syntaxhighlight>
</div>
</div>

Результат:

Номер поставщика | Количество поставок
------------------+---------------------
S13 | 1
S6 | 7
S14 | 1
S15 | 1
(4 rows)

=== Билет 18 ===

Написать запрос SELECT: выдать имена поставщиков, имеющих состояние больше 1000 и поставляющих деталь с названием 'Гайка 01-01' для изделия с названием 'Велосипед 03-04' в количестве (в поставке) большим, чем средний объём поставки, выполненной поставщиками с именем 'Иванов'.

[[Файл:2nd dufficulty.png|center]]

<p align="center"><font size="5px">'''Второй по сложности запрос экзамена!'''</font></p>

<p align="center"><font size="4px">'''Вот уж не свезло, так не свезло'''</font></p>

Текст запроса:

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
JOIN spasoi_ekz.j J
ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE sostoyanie > 1000
AND P.nazvanie = LOWER('Гайка 01-01')
AND J.nazvanie = LOWER('Велосипед 03-04')
AND kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE S.imya = 'Иванов' -- или WHERE S.imya LIKE '%Иванов%',
-- поскольку сказано, что поставка выполнена поставщиками с именем Иванов,
-- а это могут быть разные имена и отчества. Короче, опять великий
-- русский язык в задании - что ещё за "с именем Иванов"
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё один вариант запроса от ненавистника JOIN:''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S,
spasoi_ekz.spj SPJ,
spasoi_ekz.p P,
spasoi_ekz.j J
WHERE SPJ.nomer_postavshika = S.nomer_postavshika
AND SPJ.nomer_detali = P.nomer_detali
AND SPJ.nomer_izdelia=J.nomer_izdelia
AND S.sostoyanie > 1000
AND P.nazvanie = LOWER('Гайка 01-01')
AND J.nazvanie = LOWER('Велосипед 03-04')
AND kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj SPJ2, spasoi_ekz.S S2
WHERE S2.nomer_postavshika = SPJ2.nomer_postavshika
AND S2.imya LIKE '%Иванов%'
);
</syntaxhighlight>
</div>
</div>

Результат:

imya
-------------
Петров Пётр
(1 row)

=== Билет 19 ===

Написать запрос SELECT: выдать названия красных деталей, которые входят только в изделие с названием 'Рама 02-03' в количестве, меньшем 10 единиц в поставке.

Примечание: Григорьев ругается на X.nomer_detali = SPJ.nomer_detali Лучше во вложенном подзапросе сделать еще один джойн p и приравнивать названия деталей.

Текст запроса:
<syntaxhighlight lang="sql">
SELECT DISTINCT X.nazvanie
FROM spasoi_ekz.p X JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_detali = X.nomer_detali
WHERE X.cvet = 'красный'
AND kolichestvo < 10
AND NOT EXISTS (
SELECT *
FROM spasoi_ekz.j J JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE J.nazvanie != LOWER('Рама 02-03')
AND X.nomer_detali = SPJ.nomer_detali
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё вариант этого же запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT P.nazvanie
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND cvet = 'красный'
JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Рама 02-03')
WHERE kolichestvo < 10
AND SPJ.nomer_detali NOT IN (
SELECT nomer_detali
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie != LOWER('Рама 02-03')
);
</syntaxhighlight>
</div>
</div>

Результат:

nazvanie
----------------
усиленная рама
(1 row)

=== Билет 20 ===

Написать запрос SELECT: выдать имена поставщиков, поставляющих только белые детали.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
WHERE nomer_postavshika = S.nomer_postavshika
AND P.cvet != 'белый'
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё один вариант этого же запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND cvet = 'белый'
JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE S.nomer_postavshika NOT IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND cvet != 'белый'
);
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё один вариант этого же запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s
WHERE nomer_postavshika NOT IN (
SELECT spj.nomer_postavshika
FROM spasoi_ekz.spj SPJ, spasoi_ekz.p P
WHERE SPJ.nomer_detali = P.nomer_detali
AND p.cvet != 'белый'
)
AND nomer_postavshika IN (
SELECT spj.nomer_postavshika
FROM spasoi_ekz.spj SPJ, spasoi_ekz.p P
WHERE SPJ.nomer_detali = P.nomer_detali
AND p.cvet = 'белый'
);
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''И ещё один вариант этого запроса от ненавистника JOIN''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S, spasoi_ekz.spj SPJ2
WHERE SPJ2.nomer_postavshika = S.nomer_postavshika
AND NOT EXISTS (
SELECT *
FROM spasoi_ekz.p P, spasoi_ekz.spj SPJ
WHERE SPJ.nomer_detali = P.nomer_detali
AND SPJ.nomer_postavshika = SPJ2.nomer_postavshika
AND P.cvet != 'белый'
);
</syntaxhighlight>
</div>
</div>

Результат:

imya
-----------------
Рендилл Тарли
Сэмвелл Тарли
Мелисса Флорент
Томми Версетти
(4 rows)

=== Билет 21 ===

Написать запрос SELECT: выдать названия изделий, для которых детали поставляет только и только поставщик с номером 'S1'.

Чтобы продемонстрировать выполнение запроса наглядно, возьмём поставщика не 'S1', а 'S18', который был создан специально для этого запроса. Если оставить 'S1', то наглядности не получится, так как по нашей схеме БД этот поставщик не попадает под условие "''только и только''", и запрос вернёт пустой результат.

Примечание: Григорьев ругается на nomer_izdelia = J.nomer_izdelia Лучше во вложенном подзапросе сделать еще один джойн j и приравнивать названия изделий, а не номера.
Текст запроса:

<syntaxhighlight lang="sql">
SELECT nazvanie
FROM spasoi_ekz.j
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_postavshika != 'S18'
AND nomer_izdelia = J.nomer_izdelia
)
AND NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_postavshika = 'S18'
AND nomer_izdelia != J.nomer_izdelia
);
</syntaxhighlight>

Результат:

nazvanie
--------------------
кинжал
(1 row)

=== Билет 22 ===

Написать запрос SELECT: выдать имена поставщиков, которые поставляют только детали с номером 'P1' для изделия с именем 'Штуцер 01-02'.

Текст запроса:
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Штуцер 01-02')
JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj X JOIN spasoi_ekz.j J
ON X.nomer_izdelia = J.nomer_izdelia
WHERE X.nomer_detali != 'P1'
AND J.nazvanie = LOWER('Штуцер 01-02')
AND X.nomer_postavshika = S.nomer_postavshika
);
</syntaxhighlight>


<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос, но длиннее и нерациональней''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Штуцер 01-02')
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND SPJ.nomer_detali = 'P1'
JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE SPJ.nomer_postavshika NOT IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Штуцер 01-02')
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND SPJ.nomer_detali != 'P1'
);
</syntaxhighlight>
</div>
</div>

Результат:

imya
-------------
Петров Иван
(1 row)

=== Билет 23 ===

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

Текст запроса:
<syntaxhighlight lang="sql">
SELECT S.imya
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON S.nomer_postavshika = SPJ.nomer_postavshika
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
WHERE P.nazvanie = LOWER('Винт')
AND SPJ.kolichestvo > 100
AND S.sostoyanie > (
SELECT AVG(SS.sostoyanie)
FROM spasoi_ekz.s SS
WHERE SS.gorod = S.gorod
);
</syntaxhighlight>

Результат:

imya
-------------
Петров Пётр
(1 row)

=== Билет 24 ===

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


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

То есть, вообще говоря, задание на запрос неполное, и его можно трактовать так: выдать наименования деталей и общее их количество для всех наименований деталей, изготавливаемых в городе <code>%НАЗВАНИЕГОРОДА%</code>. Вот эта переменная задаётся где-то в программе, а в БД идёт запрос с уже подставленным конкретным названием.

Этот вариант запроса составлен, например, для Лондона.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nazvanie, SUM(kolichestvo)
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p
ON SPJ.nomer_detali = P.nomer_detali
WHERE gorod = 'Лондон'
GROUP BY nazvanie;
</syntaxhighlight>

Результат:

nazvanie | sum
----------+-----
гайка | 71
шуруп | 68
(2 rows)

=== Билет 25 ===

Написать запрос SELECT: выдать имена поставщиков, поставляющих хотя бы одну белую деталь для изделия с названием 'Велосипед 01-04' с объёмом поставки большим, чем средний объём поставки этого поставщика.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
JOIN spasoi_ekz.j J
ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE cvet = 'белый'
AND J.nazvanie = LOWER('Велосипед 01-04')
AND kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj SPJ
WHERE SPJ.nomer_postavshika = S.nomer_postavshika
);
</syntaxhighlight>

Результат:

imya
-------------
Петров Пётр
(1 row)

=== Билет 26 ===

Написать запрос SELECT: выдать номера красных деталей и количество поставок этих деталей.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT P.nomer_detali AS "Номер детали", COUNT(kolichestvo) AS "Количество поставок с ней"
FROM spasoi_ekz.p P, spasoi_ekz.spj SPJ
WHERE P.nomer_detali = SPJ.nomer_detali
AND cvet = 'красный'
GROUP BY P.nomer_detali;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос через JOIN''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT SPJ.nomer_detali AS "Номер детали", COUNT(kolichestvo) AS "Количество поставок с ней"
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND cvet = 'красный'
GROUP BY SPJ.nomer_detali;
</syntaxhighlight>
</div>
</div>

Результат:

Номер детали | Количество поставок с ней
--------------+---------------------------
P15 | 3
P22 | 1
P24 | 1
(3 rows)

=== Билет 27 ===

Написать запрос SELECT: выдать наименования городов и среднее состояние поставщиков для каждого города, поставляющих детали с названием 'Гайка 01-01' для изделия с названием 'Велосипед 03-04' в количестве (в поставке) большим, чем минимальный объём поставки, выполненной поставщиком с номером 'S1'.

[[Файл:1st dufficulty.png|center]]

<p align="center"><font size="7px">'''Сложнейший запрос экзамена!'''</font></p>

<p align="center"><font size="5px">'''Сохрани Джа, вытащить такое'''</font></p>

Текст запроса:

<syntaxhighlight lang="sql">
SELECT S.gorod AS "Город", AVG(S.sostoyanie) AS "Среднее состояние"
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
JOIN spasoi_ekz.j J
ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE P.nazvanie = LOWER('Гайка 01-01')
AND J.nazvanie = LOWER('Велосипед 03-04')
AND kolichestvo > (
SELECT MIN(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_postavshika = 'S1'
)
GROUP BY S.gorod;

</syntaxhighlight>

Результат:

Город | Среднее состояние
-----------+-----------------------
Мытищи | 35000.500000000000
Йокогама | 1500000.000000000000
Манчестер | 2571.0000000000000000
(3 rows)

=== Билет 28 ===

Написать запрос SELECT: выдать названия изделий, куда входит хотя бы одна красная деталь весом больше 10 граммов, поставляемая только поставщиком с номером 'S1'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT J.nazvanie
FROM spasoi_ekz.j J JOIN spasoi_ekz.spj SPJ1
ON J.nomer_izdelia = SPJ1.nomer_izdelia
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ1.nomer_detali
WHERE P.ves > 10
AND P.cvet = 'красный'
AND NOT EXISTS (
SELECT SPJ2.nomer_postavshika
FROM spasoi_ekz.spj SPJ2
WHERE SPJ2.nomer_detali = P.nomer_detali
AND SPJ2.nomer_postavshika != 'S1'
);
</syntaxhighlight>

Результат:

nazvanie
-----------------
кружевное бельё
(1 row)

=== Билет 29 ===
Написать запрос SELECT: выдать названия деталей, которые входят только и только в состав изделия с названием 'Штуцер 01-03'.

Примечание: Григорьев ругается на SPJ.nomer_detali = P.nomer_detali Лучше во вложенном подзапросе сделать еще один джойн p и приравнивать названия деталей.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT DISTINCT nazvanie
FROM spasoi_ekz.p
WHERE NOT EXISTS (
SELECT SPJ.nomer_izdelia
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE (
J.nazvanie != LOWER('Штуцер 01-03')
AND
SPJ.nomer_detali = P.nomer_detali
)
OR (
J.nazvanie = LOWER('Штуцер 01-03')
AND
SPJ.nomer_detali != P.nomer_detali
)
);
</syntaxhighlight>

Результат:

nazvanie
------------------
штуцерная деталь
(1 row)

=== Билет 30 ===
Написать запрос SELECT: выдать имена поставщиков, которые поставляют, по крайней мере, все те детали, которые поставляет поставщик с номером 'S2'.

Текст запроса:
<syntaxhighlight lang="sql">
SELECT DISTINCT imya
FROM spasoi_ekz.s S
WHERE NOT EXISTS (
SELECT nomer_detali
FROM spasoi_ekz.spj SPJY
WHERE nomer_postavshika = 'S2'
AND NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_postavshika = S.nomer_postavshika
AND nomer_detali = SPJY.nomer_detali
)
);
</syntaxhighlight>

Результат:

imya
------------
Оша
Бран Старк
(2 rows)
[[Категория:Структурное проектирование АСОИ (10 семестр)]]