https://iu5bmstu.ru/api.php?action=feedcontributions&user=37.190.38.24&feedformat=atom
Кафедра ИУ5 МГТУ им. Н.Э.Баумана, студенческое сообщество - Вклад [ru]
2024-03-28T21:53:53Z
Вклад
MediaWiki 1.41.0
https://iu5bmstu.ru/index.php?title=%D0%9E%D0%B1%D1%81%D1%83%D0%B6%D0%B4%D0%B5%D0%BD%D0%B8%D0%B5:SQL-%D0%B7%D0%B0%D0%BF%D1%80%D0%BE%D1%81%D1%8B_%D0%BA_%D1%8D%D0%BA%D0%B7%D0%B0%D0%BC%D0%B5%D0%BD%D1%83_%D0%BF%D0%BE_%D0%A1%D0%9F%D0%90%D0%A1%D0%9E%D0%98_(10_%D1%81%D0%B5%D0%BC%D0%B5%D1%81%D1%82%D1%80)&diff=3914
Обсуждение:SQL-запросы к экзамену по СПАСОИ (10 семестр)
2013-06-20T18:59:33Z
<p>37.190.38.24: </p>
<hr />
<div>== Билет №11 ==<br />
<br />
Сначала был такой запрос:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT gorod, sost<br />
FROM (<br />
SELECT gorod, SUM(sostoyanie) AS sost, SUM(SPJ.kolichestvo)<br />
FROM spasoi_ekz.spj, spasoi_ekz.s<br />
WHERE S.nomer_postavshika = SPJ.nomer_postavshika<br />
GROUP BY S.gorod HAVING SUM(SPJ.kolichestvo) > 1000<br />
) A;<br />
</syntaxhighlight><br />
<br />
Но он выполняется не совсем по заданию - там требуется искать тех поставщиков, у которых среди всех их поставок минимальное значение любой из них больше 1000, а тут считается сумма всех поставок поставщика и только потом сравнивается с 1000.<br />
<br />
Я убрал его в комментарии и написал новый.<br />
<br />
== Хипстерство в программировании ==<br />
<br />
Поясните за "хипстерство в программировании": каким образом <code>JOIN ... ON</code> под него попал? [[Участник:ILobster|iLobster]] ([[Обсуждение участника:ILobster|обсуждение]]) 22:26, 20 июня 2013 (MSK)<br />
<br />
== Билеты №№ 1 и 30 ==<br />
<br />
Мне кажется, код будет выдавать номер (и имя) поставщика S2. Не уверен, что это не нужно, но, по-моему, это довольно бесполезно.</div>
37.190.38.24
https://iu5bmstu.ru/index.php?title=SQL-%D0%B7%D0%B0%D0%BF%D1%80%D0%BE%D1%81%D1%8B_%D0%BA_%D1%8D%D0%BA%D0%B7%D0%B0%D0%BC%D0%B5%D0%BD%D1%83_%D0%BF%D0%BE_%D0%A1%D0%9F%D0%90%D0%A1%D0%9E%D0%98_(10_%D1%81%D0%B5%D0%BC%D0%B5%D1%81%D1%82%D1%80)&diff=3913
SQL-запросы к экзамену по СПАСОИ (10 семестр)
2013-06-20T18:56:31Z
<p>37.190.38.24: /* Билет 30 */</p>
<hr />
<div><div style="float:right; clear:both; margin-right:1.0em;">__TOC__</div><br />
<br />
Билет экзамена по [[:Категория:Структурное проектирование АСОИ (10 семестр) | СПАСОИ]] состоит из двух частей:<br />
# теория;<br />
# SQL-запрос.<br />
<br />
На этой странице собраны все сформированные запросы по билетам.<br />
<br />
Странно, что ни в одном билете нет запроса с сортировкой результатов. <s>Возможно, они буду в дополнительных заданиях.</s> Григорьев сказал, что это слишком просто и потому он не стал загромождать запросы дополнительными мелочами.<br />
<br />
== Схема БД ==<br />
<br />
Схема БД используется всё [[СПАСОИ_(10)_-_Лекция_№8_-_SQL#Некоторые возможности языка SQL | та же]], что была в прошлом семестре и на лекциях.<br />
<br />
Для написания запросов и проверки их выполнения создана БД в СУБД [http://www.postgresql.org/ PostgreSQL].<br />
<br />
Скрипт создания можно загрузить [http://yadi.sk/d/ArwqOGAy5pPfi отсюда].<br />
<br />
=== Таблицы БД ===<br />
<br />
==== Поставщики ====<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT * FROM spasoi_ekz.s;<br />
</syntaxhighlight><br />
<br />
nomer_postavshika | imya | sostoyanie | gorod<br />
-------------------+------------------+------------+------------<br />
S5 | Мелисандра | 65000 | Мадрид<br />
S2 | Бран Старк | 60000 | Мурманск<br />
S1 | Якен Хгар | 1500000 | Йокогама<br />
S7 | Сирио Форель | 1500000 | Йокогама<br />
S4 | Джейме Ланнистер | 750000 | Лондон<br />
S3 | Серсея Ланнистер | 1200000 | Лондон<br />
S8 | Тирион Ланнистер | 2571 | Манчестер<br />
S9 | Иванов | 35000 | Мытищи<br />
S10 | Русе Болтон | 44444 | Баренцбург<br />
S11 | Петров Иван | 35000 | Мытищи<br />
S14 | Рендилл Тарли | 1111111 | Прага<br />
S13 | Сэмвелл Тарли | 999999 | Прага<br />
S6 | Оша | | Москва<br />
S16 | Ходор | | Москва<br />
S15 | Мелисса Флорент | 888888 | Стокгольм<br />
S12 | Петров Пётр | 35001 | Мытищи<br />
S17 | Томми Версетти | 123456789 | Майами<br />
S18 | Безликий | 1 | Йокогама<br />
(18 rows)<br />
<br />
==== Детали ====<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT * FROM spasoi_ekz.p;<br />
</syntaxhighlight><br />
<br />
nomer_detali | nazvanie | cvet | ves | gorod<br />
--------------+----------------------+---------------+------+-----------------<br />
P9 | подставка | синий | 400 | Нижний Новгород<br />
P10 | стойка | синий | 2000 | Нижний Новгород<br />
P11 | абажур | синий | 400 | Нижний Новгород<br />
P1 | гайка | чёрный | 20 | Лондон<br />
P3 | ось | белый | 5000 | Эдинбург<br />
P4 | зубчатое колесо | чёрный | 50 | Эдинбург<br />
P6 | транзистор | коричневый | 2 | Токио<br />
P7 | печатная плата | зелёный | 200 | Токио<br />
P8 | диод | коричневый | 1 | Токио<br />
P12 | универсальная деталь | универсальный | 1 | Париж<br />
P13 | уникальная деталь | уникальный | 1 | Бостон<br />
P14 | болт | серый | 20 | Ижевск<br />
P15 | рама | красный | 3000 | Манчестер<br />
P16 | колесо | белый | 1500 | Манчестер<br />
P5 | втулка | серый | 350 | Манчестер<br />
P17 | бумага | белый | 1 | Астрахань<br />
P18 | плитка | белый | 300 | Флоренция<br />
P19 | орнамент | серый | 800 | Флоренция<br />
P20 | уголок | серый | 100 | Флоренция<br />
P21 | гайка 01-01 | серебристый | 20 | Клин<br />
P22 | усиленная рама | красный | 3200 | Череповец<br />
P23 | винт | розовый | 5 | Ижевск<br />
P24 | труселя | красный | 20 | Челябинск<br />
P25 | штуцерная деталь | коричневый | 450 | Череповец<br />
P2 | шуруп | чёрный | 5 | Лондон<br />
P26 | лезвие | бесцветный | 120 | Йокогама<br />
(26 rows)<br />
<br />
==== Изделия ====<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT * FROM spasoi_ekz.j;<br />
</syntaxhighlight><br />
<br />
nomer_izdelia | nazvanie | gorod<br />
---------------+-----------------------+-----------------<br />
J1 | автомобиль | Магнитогорск<br />
J2 | процессор | Зеленоград<br />
J3 | торшер | Нижний Новгород<br />
J4 | универсальное изделие | Париж<br />
J5 | уникальное изделие | Бостон<br />
J6 | велосипед 01/23 | Манчестер<br />
J7 | изделие из болтов | Челябинск<br />
J8 | шкаф | Ярославль<br />
J9 | рама 02-01 | Череповец<br />
J10 | книга | Астрахань<br />
J11 | панно 01-03 | Флоренция<br />
J12 | велосипед 03-04 | Клин<br />
J13 | рама 02-03 | Череповец<br />
J14 | штуцер 01-02 | Череповец<br />
J15 | велосипед 01-04 | Клин<br />
J16 | кружевное бельё | Челябинск<br />
J17 | штуцер 01-03 | Череповец<br />
J18 | кинжал | Йокогама<br />
(18 rows)<br />
<br />
==== Сборки ====<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT * FROM spasoi_ekz.spj;<br />
</syntaxhighlight><br />
<br />
nomer_postavshika | nomer_detali | nomer_izdelia | kolichestvo<br />
-------------------+--------------+---------------+-------------<br />
S1 | P6 | J2 | 20<br />
S1 | P7 | J2 | 5<br />
S2 | P1 | J1 | 4<br />
S6 | P4 | J1 | 2<br />
S6 | P5 | J1 | 6<br />
S3 | P9 | J3 | 1<br />
S4 | P10 | J3 | 1<br />
S5 | P11 | J3 | 1<br />
S2 | P4 | J1 | 8<br />
S6 | P3 | J1 | 50<br />
S7 | P8 | J2 | 25<br />
S1 | P12 | J4 | 1<br />
S2 | P12 | J4 | 1<br />
S3 | P12 | J4 | 1<br />
S4 | P12 | J4 | 1<br />
S5 | P12 | J4 | 1<br />
S7 | P12 | J4 | 1<br />
S7 | P2 | J1 | 1<br />
S6 | P2 | J1 | 9<br />
S6 | P12 | J4 | 7<br />
S1 | P13 | J5 | 14<br />
S6 | P14 | J1 | 9000<br />
S2 | P14 | J1 | 3<br />
S6 | P1 | J1 | 12<br />
S8 | P15 | J6 | 1<br />
S8 | P16 | J6 | 2<br />
S3 | P5 | J6 | 10<br />
S10 | P2 | J8 | 4<br />
S8 | P1 | J7 | 16<br />
S9 | P14 | J7 | 3<br />
S11 | P10 | J3 | 2<br />
S12 | P15 | J1 | 3<br />
S11 | P11 | J3 | 4<br />
S10 | P14 | J9 | 5<br />
S13 | P17 | J10 | 1001<br />
S14 | P17 | J10 | 1111<br />
S15 | P17 | J10 | 1010<br />
S5 | P18 | J11 | 9<br />
S5 | P19 | J11 | 1<br />
S5 | P20 | J11 | 4<br />
S9 | P14 | J8 | 25<br />
S12 | P21 | J12 | 15<br />
S11 | P22 | J13 | 9<br />
S11 | P1 | J14 | 26<br />
S12 | P1 | J14 | 13<br />
S12 | P2 | J14 | 54<br />
S12 | P23 | J4 | 101<br />
S12 | P16 | J15 | 40<br />
S7 | P21 | J12 | 3<br />
S8 | P21 | J12 | 4<br />
S9 | P21 | J12 | 5<br />
S1 | P24 | J16 | 1<br />
S1 | P15 | J6 | 3<br />
S4 | P25 | J17 | 1<br />
S17 | P16 | J1 | 4<br />
S18 | P26 | J18 | 1<br />
(56 rows)<br />
<br />
== Готовые запросы ==<br />
<br />
=== Некоторое вступление ===<br />
<br />
Если видите, что тот или иной запрос можно составить короче и рациональней - смело вносите правку.<br />
<br />
==== Про только и только ====<br />
<br />
В трёх билетах в задании на запрос встречается формулировка "''только и только''". Как оказалось, это означает следующее: если холодильники поставляет ''только и только'' Уася, то:<br />
# кроме Уаси никто не поставляет холодильники;<br />
# Уася не поставляет ничего, кроме холодильников.<br />
<br />
<s>Великий и могучий русский языка, ну что за экономия на бумаге.</s><br />
<br />
Существующие запросы, естественно, оказались неправильными и их пришлось переписать.<br />
<br />
==== Про JOIN ====<br />
<br />
Почти все запросы, где используется соединение таблиц, можно написать с использованием <code>JOIN</code>, а можно просто с перечислением таблиц через запятую.<br />
<br />
Но неожиданно оказалось, это почти то же самое и называется [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> более правильным и вообще прогрессивным.<br />
<br />
=== Билет 1 ===<br />
<br />
Написать запрос SELECT: выдать номера поставщиков, которые поставляют, по крайней мере, все те детали, которые поставляет поставщик с номером 'S2'.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT nomer_postavshika<br />
FROM spasoi_ekz.spj SPJX<br />
WHERE NOT EXISTS (<br />
SELECT nomer_detali<br />
FROM spasoi_ekz.spj SPJY<br />
WHERE nomer_postavshika = 'S2'<br />
AND NOT EXISTS (<br />
SELECT *<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_postavshika = SPJX.nomer_postavshika<br />
AND nomer_detali = SPJY.nomer_detali<br />
)<br />
);<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Ещё вариант''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT nomer_postavshika<br />
FROM spasoi_ekz.spj SPJ<br />
WHERE NOT EXISTS (<br />
(SELECT DISTINCT nomer_detali FROM spasoi_ekz.spj WHERE nomer_postavshika = 'S2')<br />
EXCEPT<br />
(SELECT DISTINCT nomer_detali FROM spasoi_ekz.spj WHERE nomer_postavshika = SPJ.nomer_postavshika)<br />
)<br />
AND nomer_postavshika != 'S2'<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
nomer_postavshika<br />
-------------------<br />
S6<br />
S2<br />
(2 rows)<br />
<br />
=== Билет 2 ===<br />
<br />
Написать запрос SELECT: выдать номера деталей и общее их количество для всех номеров деталей, поставляемых более чем одним поставщиком.<br />
<br />
Текст запроса, каким его дал Григорьев на лекции, впоследствии исправив его:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT nomer_detali AS "Номер детали",<br />
SUM(kolichestvo) AS "Сколько штук поставляется",<br />
COUNT(DISTINCT nomer_postavshika) AS "Сколько у неё поставщиков"<br />
FROM spasoi_ekz.spj<br />
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_postavshika) > 1;<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Ещё вариант''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT nomer_detali AS "Номер детали",<br />
kol AS "Сколько штук поставляется"<br />
FROM (<br />
SELECT nomer_detali,<br />
SUM(kolichestvo) AS kol,<br />
COUNT(DISTINCT nomer_postavshika)<br />
FROM spasoi_ekz.spj<br />
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_postavshika) > 1<br />
) A;<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
Номер детали | Сколько штук поставляется | Сколько у неё поставщиков<br />
--------------+---------------------------+---------------------------<br />
P1 | 71 | 5<br />
P10 | 3 | 2<br />
P11 | 5 | 2<br />
P12 | 13 | 7<br />
P14 | 9036 | 4<br />
P15 | 7 | 3<br />
P16 | 46 | 3<br />
P17 | 3122 | 3<br />
P2 | 68 | 4<br />
P21 | 27 | 4<br />
P4 | 10 | 2<br />
P5 | 16 | 2<br />
(12 rows)<br />
<br />
=== Билет 3 ===<br />
<br />
Написать запрос SELECT: выдать номера поставщиков, поставляющих детали с номером 'P1' для какого-либо изделия в количестве (в поставке) большим, чем средний объём поставок деталей с номером 'P2' для этого изделия.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT X.nomer_postavshika <br />
FROM spasoi_ekz.spj X<br />
WHERE X.nomer_detali = 'P1'<br />
AND X.kolichestvo > (<br />
SELECT AVG(kolichestvo)<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_izdelia = X.nomer_izdelia<br />
AND nomer_detali = 'P2'<br />
);<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Этот же запрос, но длиннее и нерациональней''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT nomer_postavshika<br />
FROM (<br />
SELECT *<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_izdelia IN(<br />
SELECT nomer_izdelia<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_detali = 'P1'<br />
)<br />
AND nomer_izdelia IN(<br />
SELECT nomer_izdelia<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_detali = 'P2'<br />
)<br />
) A<br />
WHERE nomer_detali = 'P1' AND kolichestvo ><br />
(<br />
SELECT AVG(kolichestvo)<br />
FROM (<br />
SELECT *<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_izdelia IN(<br />
SELECT nomer_izdelia<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_detali = 'P1'<br />
)<br />
AND nomer_izdelia IN(<br />
SELECT nomer_izdelia<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_detali = 'P2'<br />
)<br />
) B<br />
WHERE nomer_detali = 'P2'<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
nomer_postavshika<br />
-------------------<br />
S6<br />
(1 row)<br />
<br />
=== Билет 4 ===<br />
<br />
Написать запрос SELECT: выдать номера изделий, для которых детали поставляет только поставщик с номером ‘S1’.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT nomer_izdelia<br />
FROM spasoi_ekz.spj X<br />
WHERE NOT EXISTS (<br />
SELECT *<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_postavshika != 'S1'<br />
AND X.nomer_izdelia = nomer_izdelia<br />
);<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Этот же запрос, но без EXISTS''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT nomer_izdelia<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_izdelia IN(<br />
SELECT nomer_izdelia<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_postavshika = 'S1'<br />
)<br />
AND nomer_izdelia NOT IN(<br />
SELECT nomer_izdelia<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_postavshika != 'S1'<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
nomer_izdelia<br />
---------------<br />
J5<br />
J16<br />
(2 rows)<br />
<br />
=== Билет 5 ===<br />
<br />
Написать запрос SELECT: выдать имена поставщиков, поставляющих детали с названием "Болт" в количестве (в поставке) большим, чем средний объём всех поставок деталей с номером 'P1'.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
AND nazvanie = LOWER('Болт')<br />
WHERE kolichestvo > (<br />
SELECT AVG(kolichestvo)<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_detali = 'P1'<br />
);<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Вариант запроса без JOIN''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.s S, spasoi_ekz.p P, spasoi_ekz.spj SPJ<br />
WHERE P.nazvanie = LOWER('Болт')<br />
AND P.nomer_detali = SPJ.nomer_detali<br />
AND S.nomer_postavshika = SPJ.nomer_postavshika<br />
AND SPJ.kolichestvo > (<br />
SELECT AVG(kolichestvo)<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_detali = 'P1'<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Ещё вариант запроса без JOIN и сложнее''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.s<br />
WHERE nomer_postavshika IN (<br />
SELECT nomer_postavshika<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_detali = (<br />
SELECT nomer_detali<br />
FROM spasoi_ekz.p<br />
WHERE LOWER(nazvanie) = LOWER('Болт')<br />
)<br />
AND kolichestvo > (<br />
SELECT AVG(kolichestvo)<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_detali = 'P1'<br />
)<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
imya<br />
------<br />
Оша<br />
Иванов<br />
(2 rows)<br />
<br />
=== Билет 6 ===<br />
<br />
Написать запрос SELECT: выдать названия деталей, поставляемых поставщиком, проживающим в том же городе, где изготавливаются эти детали, для изделия с названием ‘Велосипед 01/23’.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT P.nazvanie<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
WHERE S.gorod = P.gorod<br />
AND J.nazvanie = LOWER('Велосипед 01/23');<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Этот же запрос без JOIN''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT nazvanie<br />
FROM spasoi_ekz.S S, spasoi_ekz.p P, spasoi_ekz.spj SPJ<br />
WHERE S.gorod = P.gorod<br />
AND P.nomer_detali = SPJ.nomer_detali<br />
AND S.nomer_postavshika = SPJ.nomer_postavshika<br />
AND nomer_izdelia = (<br />
SELECT nomer_izdelia<br />
FROM spasoi_ekz.j<br />
WHERE nazvanie = LOWER('Велосипед 01/23')<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
nazvanie<br />
----------<br />
рама<br />
колесо<br />
(2 rows)<br />
<br />
=== Билет 7 ===<br />
<br />
Написать запрос SELECT: выдать названия изделий, куда входят детали с названием 'Болт', поставляемых поставщиками с именем 'Иванов', в количестве (в поставке) большим, чем средний объём поставок для этого изделия.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT J.nazvanie<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
WHERE imya LIKE '%Иванов%'<br />
AND P.nazvanie = LOWER('Болт')<br />
AND kolichestvo > (<br />
SELECT AVG(kolichestvo)<br />
FROM spasoi_ekz.spj X<br />
WHERE SPJ.nomer_izdelia = X.nomer_izdelia<br />
);<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Ещё один вариант запроса''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT nazvanie<br />
FROM spasoi_ekz.j<br />
WHERE nomer_izdelia IN (<br />
SELECT nomer_izdelia<br />
FROM (spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
AND imya LIKE '%Иванов%'<br />
JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
AND nazvanie = LOWER('Болт')) A<br />
WHERE kolichestvo > (<br />
SELECT AVG(kolichestvo)<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = A.nomer_izdelia<br />
)<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''И ещё вариант этого же запроса, но длиннее и нерациональней''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT nazvanie<br />
FROM (<br />
SELECT J.nazvanie, kolichestvo<br />
FROM spasoi_ekz.s S, spasoi_ekz.p P, spasoi_ekz.j J, spasoi_ekz.spj SPJ<br />
WHERE J.nomer_izdelia = SPJ.nomer_izdelia<br />
AND P.nomer_detali = SPJ.nomer_detali<br />
AND P.nazvanie = LOWER('Болт')<br />
AND S.imya LIKE '%Иванов%'<br />
AND S.nomer_postavshika = SPJ.nomer_postavshika<br />
) A<br />
WHERE kolichestvo ><br />
(<br />
SELECT AVG(kolichestvo)<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_izdelia IN(<br />
SELECT J.nomer_izdelia<br />
FROM spasoi_ekz.s S, spasoi_ekz.p P, spasoi_ekz.j J, spasoi_ekz.spj SPJ<br />
WHERE J.nomer_izdelia = SPJ.nomer_izdelia<br />
AND P.nomer_detali = SPJ.nomer_detali<br />
AND P.nazvanie = LOWER('Болт')<br />
AND S.imya LIKE '%Иванов%'<br />
AND S.nomer_postavshika = SPJ.nomer_postavshika<br />
)<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
nazvanie<br />
-------------------<br />
шкаф<br />
(1 row)<br />
<br />
=== Билет 8 ===<br />
<br />
Написать запрос SELECT: выдать номера изделий и общее количество деталей для них, поставляемых поставщиками с именем 'Петров'.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT nomer_izdelia AS "Номер изделия", SUM(kolichestvo) AS "Количество деталей" <br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
-- так как "поставщикАМИ" и возможны<br />
-- Иван Петров и Петров Иван, то LIKE %Петров%<br />
AND imya LIKE '%Петров%'<br />
GROUP BY nomer_izdelia;<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
Номер изделия | Количество деталей<br />
---------------+--------------------<br />
J4 | 101<br />
J3 | 6<br />
J13 | 9<br />
J15 | 40<br />
J1 | 3<br />
J12 | 15<br />
J14 | 93<br />
(7 rows)<br />
<br />
=== Билет 9 ===<br />
<br />
Написать запрос SELECT: выдать номера деталей и общее их количество для всех номеров деталей, которые входят только в одно изделие.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT nomer_detali, SUM(kolichestvo)<br />
FROM spasoi_ekz.spj<br />
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_izdelia) = 1;<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Ещё вариант''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT nomer_detali, kol FROM (<br />
SELECT nomer_detali, SUM(kolichestvo) AS kol, COUNT(DISTINCT nomer_izdelia) = 1<br />
FROM spasoi_ekz.spj<br />
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_izdelia) = 1<br />
) A;<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
<br />
Результат:<br />
<br />
nomer_detali | sum<br />
--------------+------<br />
P10 | 3<br />
P11 | 5<br />
P12 | 13<br />
P13 | 14<br />
P17 | 3122<br />
P18 | 9<br />
P19 | 1<br />
P20 | 4<br />
P21 | 27<br />
P22 | 9<br />
P23 | 101<br />
P24 | 1<br />
P25 | 1<br />
P26 | 1<br />
P3 | 50<br />
P4 | 10<br />
P6 | 20<br />
P7 | 5<br />
P8 | 25<br />
P9 | 1<br />
(20 rows)<br />
<br />
=== Билет 10 ===<br />
<br />
Написать запрос SELECT: выдать имена поставщиков, поставляющих детали с названием 'Болт' для изделия с названием 'Рама 02-01' в количестве (в поставке) большим, чем минимальное значение поставки детали с номером 'P1'.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
AND J.nazvanie = LOWER('Рама 02-01')<br />
JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
AND P.nazvanie = LOWER('Болт')<br />
JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
WHERE kolichestvo > (<br />
SELECT MIN(kolichestvo)<br />
FROM spasoi_ekz.spj <br />
WHERE nomer_detali = 'P1'<br />
);<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
imya<br />
-------------<br />
Русе Болтон<br />
(1 row)<br />
<br />
=== Билет 11 ===<br />
<br />
Написать запрос SELECT: выдать названия городов и суммарное состояние проживающих в каждом городе поставщиков, у которых минимальный объём поставки деталей больше 1000.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT gorod, SUM(sostoyanie)<br />
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ <br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
GROUP BY gorod HAVING MIN(kolichestvo) > 1000;<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Ещё вариант этого запроса''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT gorod, SUM(sostoyanie)<br />
FROM spasoi_ekz.s S<br />
WHERE ( <br />
SELECT MIN(kolichestvo) <br />
FROM spasoi_ekz.spj X <br />
WHERE X.nomer_postavshika = S.nomer_postavshika <br />
) > 1000<br />
GROUP BY gorod;<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''И ещё вариант подлиннее для тех, кто не ищет лёгких путей''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT gorod, SUM(sostoyanie)<br />
FROM spasoi_ekz.s<br />
WHERE nomer_postavshika IN (<br />
SELECT nomer_postavshika<br />
FROM spasoi_ekz.spj<br />
GROUP BY nomer_postavshika HAVING MIN(kolichestvo) > 1000<br />
)<br />
GROUP BY gorod;<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<!-- а этот запрос выполняет не совсем то и не правильно<br />
<syntaxhighlight lang="sql"><br />
SELECT gorod, sost<br />
FROM (<br />
SELECT gorod, SUM(sostoyanie) AS sost, SUM(SPJ.kolichestvo)<br />
FROM spasoi_ekz.spj, spasoi_ekz.s<br />
WHERE S.nomer_postavshika = SPJ.nomer_postavshika<br />
GROUP BY S.gorod HAVING SUM(SPJ.kolichestvo) > 1000<br />
) A;<br />
</syntaxhighlight> --> <br />
<br />
Результат:<br />
<br />
gorod | sum<br />
-----------+---------<br />
Прага | 2111110<br />
Стокгольм | 888888<br />
(2 rows)<br />
<br />
=== Билет 12 ===<br />
<br />
Написать запрос SELECT: выдать номера деталей, поставляемых для какого-либо изделия поставщиком, проживающим в том же городе, где изготавливается это изделие.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT nomer_detali<br />
FROM spasoi_ekz.spj SPJ, spasoi_ekz.s S, spasoi_ekz.j J<br />
WHERE SPJ.nomer_postavshika = S.nomer_postavshika<br />
AND S.gorod = J.gorod<br />
AND J.nomer_izdelia = SPJ.nomer_izdelia;<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
nomer_detali<br />
--------------<br />
P15<br />
P16<br />
P26<br />
(3 rows)<br />
<br />
=== Билет 13 ===<br />
<br />
Написать <u>один</u> запрос SELECT: выдать количества строк в таблице S (Поставщик) 1) с пустыми значениями и 2) непустыми значениями в столбце Состояние.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT COUNT(*) - COUNT(sostoyanie) AS "С пустым состоянием", <br />
COUNT(sostoyanie) AS "С не пустым состоянием"<br />
FROM spasoi_ekz.s;<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Этот же запрос, но длиннее и нерациональней''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
<br />
SELECT COUNT(Snull.*) AS "С пустым состоянием", COUNT(Snotnull.sostoyanie) AS "С не пустым состоянием"<br />
FROM spasoi_ekz.s Snull RIGHT OUTER JOIN spasoi_ekz.s Snotnull<br />
ON Snull.nomer_postavshika = Snotnull.nomer_postavshika<br />
AND Snull.sostoyanie IS NULL;<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
С пустым состоянием | С не пустым состоянием<br />
---------------------+-----------------------<br />
2 | 16<br />
(1 row)<br />
<br />
=== Билет 14 ===<br />
<br />
Написать запрос SELECT: выдать имена поставщиков, которые поставляют детали только и только для изделия с номером 'J1'.<br />
<br />
Чтобы продемонстрировать выполнение запроса наглядно, возьмём изделие не 'J1', а 'J18', которое было создано специально для этого запроса. Если оставить 'J1', то наглядности не получится, так как по нашей схеме БД это изделие не попадает под условие "только и только", и запрос вернёт пустой результат.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT imya<br />
FROM spasoi_ekz.s S<br />
WHERE NOT EXISTS (<br />
SELECT *<br />
FROM spasoi_ekz.spj<br />
WHERE (<br />
nomer_izdelia != 'J18'<br />
AND<br />
nomer_postavshika = S.nomer_postavshika)<br />
OR<br />
(<br />
nomer_izdelia = 'J18'<br />
AND<br />
nomer_postavshika != S.nomer_postavshika<br />
)<br />
);<br />
</syntaxhighlight><br />
<!-- этот запрос не подходит под "только и только", подробности в начале страницы<br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.spj A JOIN spasoi_ekz.s S<br />
ON A.nomer_postavshika = S.nomer_postavshika<br />
WHERE nomer_izdelia = 'J1'<br />
AND NOT EXISTS (<br />
SELECT nomer_postavshika<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_izdelia != 'J1'<br />
AND nomer_postavshika = A.nomer_postavshika<br />
);<br />
</syntaxhighlight> --><br />
<br />
Результат:<br />
<br />
imya<br />
----------------<br />
Безликий<br />
(1 row)<br />
<br />
=== Билет 15 ===<br />
<br />
Написать запрос SELECT: выдать наименования изделий, для которых детали поставляют только те поставщики, у которых состояние больше 1000000 у.е.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT nazvanie<br />
FROM spasoi_ekz.j J<br />
WHERE NOT EXISTS (<br />
SELECT *<br />
FROM spasoi_ekz.spj X JOIN spasoi_ekz.s S<br />
ON X.nomer_postavshika = S.nomer_postavshika<br />
WHERE sostoyanie <= 1000000<br />
AND X.nomer_izdelia = J.nomer_izdelia<br />
);<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Вариант этого запроса без NOT EXISTS''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT nazvanie<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
WHERE sostoyanie > 1000000<br />
AND SPJ.nomer_izdelia NOT IN (<br />
SELECT nomer_izdelia<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
WHERE sostoyanie < 1000000<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
nazvanie<br />
--------------------<br />
кружевное бельё<br />
уникальное изделие<br />
процессор<br />
(3 rows)<br />
<br />
=== Билет 16 ===<br />
Написать запрос SELECT: выдать цвета и для каждого цвета общее количество деталей, входящих в изделие с названием 'Панно 01-03'.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT cvet AS "Цвет", SUM(kolichestvo) AS "Деталей"<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
AND J.nazvanie = LOWER('Панно 01-03')<br />
JOIN spasoi_ekz.P P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
GROUP BY cvet;<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Другой вариант запроса для тонко чувствующих натур''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT cvet AS "Цвет", SUM(kolichestvo) AS "Деталей"<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
WHERE J.nazvanie = LOWER('Панно 01-03')<br />
GROUP BY cvet;<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
Цвет | Деталей<br />
-------+---------<br />
белый | 9<br />
серый | 5<br />
(2 rows)<br />
<br />
=== Билет 17 ===<br />
<br />
Написать запрос SELECT: выдать номера поставщиков и количество сделанных ими поставок при условии, что среднее число деталей во всех этих поставках больше 1000.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT nomer_postavshika AS "Номер поставщика",<br />
COUNT(*) AS "Количество поставок"<br />
FROM spasoi_ekz.spj<br />
GROUP BY nomer_postavshika HAVING AVG(kolichestvo) > 1000;<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Ещё вариант запроса''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT nomer_postavshika AS "Номер поставщика", COUNT(*) AS "Количество поставок"<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_postavshika IN (<br />
SELECT nomer_postavshika<br />
FROM spasoi_ekz.spj<br />
GROUP BY nomer_postavshika HAVING AVG(kolichestvo) > 1000<br />
)<br />
GROUP BY nomer_postavshika;<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''И ещё вариант запроса''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT nomer_postavshika AS "Номер поставщика", COUNT(*) AS "Количество поставок"<br />
FROM spasoi_ekz.spj<br />
WHERE (<br />
SELECT AVG(kolichestvo)<br />
FROM spasoi_ekz.spj X<br />
WHERE X.nomer_postavshika = spj.nomer_postavshika<br />
) > 1000<br />
GROUP BY nomer_postavshika;<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''И даже ещё вариант запроса''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT nom AS "Номер поставщика", cnt AS "Количество поставок"<br />
FROM (<br />
SELECT S.nomer_postavshika AS nom,<br />
COUNT(SPJ.nomer_postavshika) AS cnt,<br />
AVG(SPJ.kolichestvo) AS kol<br />
FROM spasoi_ekz.s S, spasoi_ekz.spj SPJ<br />
WHERE S.nomer_postavshika = SPJ.nomer_postavshika<br />
GROUP BY S.nomer_postavshika<br />
) A<br />
WHERE kol > 1000;<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
Номер поставщика | Количество поставок<br />
------------------+---------------------<br />
S13 | 1<br />
S6 | 7<br />
S14 | 1<br />
S15 | 1<br />
(4 rows)<br />
<br />
=== Билет 18 ===<br />
<br />
Написать запрос SELECT: выдать имена поставщиков, имеющих состояние больше 1000 и поставляющих деталь с названием 'Гайка 01-01' для изделия с названием 'Велосипед 03-04' в количестве (в поставке) большим, чем средний объём поставки, выполненной поставщиками с именем 'Иванов'.<br />
<br />
<br />
[[Файл:2nd dufficulty.png|center]]<br />
<br />
<br />
<p align="center"><font size="5px">'''Второй по сложности запрос экзамена!'''</font></p><br />
<br />
<p align="center"><font size="4px">'''Вот уж не свезло, так не свезло'''</font></p><br />
<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
JOIN spasoi_ekz.p P<br />
ON P.nomer_detali = SPJ.nomer_detali<br />
JOIN spasoi_ekz.j J<br />
ON J.nomer_izdelia = SPJ.nomer_izdelia<br />
WHERE sostoyanie > 1000<br />
AND P.nazvanie = LOWER('Гайка 01-01')<br />
AND J.nazvanie = LOWER('Велосипед 03-04')<br />
AND kolichestvo > (<br />
SELECT AVG(kolichestvo)<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika <br />
WHERE S.imya = 'Иванов'<br />
);<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
imya<br />
-------------<br />
Петров Пётр<br />
(1 row)<br />
<br />
=== Билет 19 ===<br />
<br />
Написать запрос SELECT: выдать названия красных деталей, которые входят только в изделие с названием 'Рама 02-03' в количестве, меньшем 10 единиц в поставке.<br />
<br />
Текст запроса:<br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT X.nazvanie<br />
FROM spasoi_ekz.p X JOIN spasoi_ekz.spj SPJ<br />
ON SPJ.nomer_detali = X.nomer_detali<br />
WHERE X.cvet = 'красный'<br />
AND kolichestvo < 10<br />
AND NOT EXISTS (<br />
SELECT *<br />
FROM spasoi_ekz.j J JOIN spasoi_ekz.spj SPJ<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
WHERE J.nazvanie != LOWER('Рама 02-03')<br />
AND X.nomer_detali = SPJ.nomer_detali<br />
);<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Ещё вариант этого же запроса''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT P.nazvanie<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
AND cvet = 'красный'<br />
JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
AND J.nazvanie = LOWER('Рама 02-03')<br />
WHERE kolichestvo < 10<br />
AND SPJ.nomer_detali NOT IN (<br />
SELECT nomer_detali<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
AND J.nazvanie != LOWER('Рама 02-03')<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
nazvanie<br />
----------------<br />
усиленная рама<br />
(1 row)<br />
<br />
=== Билет 20 ===<br />
<br />
Написать запрос SELECT: выдать имена поставщиков, поставляющих только белые детали.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
WHERE NOT EXISTS (<br />
SELECT *<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P<br />
ON P.nomer_detali = SPJ.nomer_detali<br />
WHERE nomer_postavshika = S.nomer_postavshika<br />
AND P.cvet != 'белый'<br />
);<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Ещё один вариант этого же запроса''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
AND cvet = 'белый'<br />
JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
WHERE S.nomer_postavshika NOT IN (<br />
SELECT nomer_postavshika<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
AND cvet != 'белый'<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''И ещё один вариант этого же запроса''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.s<br />
WHERE nomer_postavshika NOT IN (<br />
SELECT spj.nomer_postavshika<br />
FROM spasoi_ekz.spj SPJ, spasoi_ekz.p P<br />
WHERE SPJ.nomer_detali = P.nomer_detali<br />
AND p.cvet != 'белый'<br />
)<br />
AND nomer_postavshika IN (<br />
SELECT spj.nomer_postavshika<br />
FROM spasoi_ekz.spj SPJ, spasoi_ekz.p P<br />
WHERE SPJ.nomer_detali = P.nomer_detali<br />
AND p.cvet = 'белый'<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
imya<br />
-----------------<br />
Рендилл Тарли<br />
Сэмвелл Тарли<br />
Мелисса Флорент<br />
Томми Версетти<br />
(4 rows)<br />
<br />
=== Билет 21 ===<br />
<br />
Написать запрос SELECT: выдать названия изделий, для которых детали поставляет только и только поставщик с номером 'S1'.<br />
<br />
Чтобы продемонстрировать выполнение запроса наглядно, возьмём поставщика не 'S1', а 'S18', который был создан специально для этого запроса. Если оставить 'S1', то наглядности не получится, так как по нашей схеме БД этот поставщик не попадает под условие "''только и только''", и запрос вернёт пустой результат.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT nazvanie<br />
FROM spasoi_ekz.j<br />
WHERE NOT EXISTS (<br />
SELECT *<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_postavshika != 'S18'<br />
AND nomer_izdelia = J.nomer_izdelia<br />
)<br />
AND NOT EXISTS (<br />
SELECT *<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_postavshika = 'S18'<br />
AND nomer_izdelia != J.nomer_izdelia<br />
);<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
nazvanie<br />
--------------------<br />
кинжал<br />
(1 row)<br />
<br />
=== Билет 22 ===<br />
<br />
Написать запрос SELECT: выдать имена поставщиков, которые поставляют только детали с номером 'P1' для изделия с именем 'Штуцер 01-02'.<br />
<br />
Текст запроса:<br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
AND J.nazvanie = LOWER('Штуцер 01-02')<br />
JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
WHERE NOT EXISTS (<br />
SELECT *<br />
FROM spasoi_ekz.spj X JOIN spasoi_ekz.j J<br />
ON X.nomer_izdelia = J.nomer_izdelia<br />
WHERE X.nomer_detali != 'P1' <br />
AND j.nazvanie = LOWER('Штуцер 01-02')<br />
AND S.nomer_postavshika = X.nomer_postavshika<br />
);<br />
</syntaxhighlight><br />
<br />
<!-- неверный запрос - номер изделия, а не название<br />
SELECT imya FROM S X<br />
JOIN SPJ Y ON X.nomer_postavshika=Y.nomer_postavshika<br />
WHERE X.nomer_postavshika NOT IN (<br />
SELECT DISTINCT nomer_postavshika FROM SPJ Z<br />
WHERE Z.nomer_izdelia != 'Штуцер 01-02'<br />
AND Z.nomer_detali!='P1');--><br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Этот же запрос, но длиннее и нерациональней''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
AND J.nazvanie = LOWER('Штуцер 01-02')<br />
JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
AND SPJ.nomer_detali = 'P1'<br />
JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
WHERE SPJ.nomer_postavshika NOT IN (<br />
SELECT nomer_postavshika<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
AND J.nazvanie = LOWER('Штуцер 01-02')<br />
JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
AND SPJ.nomer_detali != 'P1'<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
imya<br />
-------------<br />
Петров Иван<br />
(1 row)<br />
<br />
=== Билет 23 ===<br />
<br />
Написать запрос SELECT: выдать имена поставщиков, которые поставляют деталь с названием 'Винт' в количестве большим 100 единиц в одной поставке и имеют состояние больше среднего по их родному городу.<br />
<br />
Текст запроса:<br />
<syntaxhighlight lang="sql"><br />
SELECT S.imya<br />
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ<br />
ON S.nomer_postavshika = SPJ.nomer_postavshika<br />
JOIN spasoi_ekz.p P<br />
ON P.nomer_detali = SPJ.nomer_detali<br />
WHERE P.nazvanie = LOWER('Винт')<br />
AND SPJ.kolichestvo > 100<br />
AND S.sostoyanie > (<br />
SELECT AVG(SS.sostoyanie)<br />
FROM spasoi_ekz.s SS<br />
WHERE SS.gorod = S.gorod<br />
);<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
imya<br />
-------------<br />
Петров Пётр<br />
(1 row)<br />
<br />
=== Билет 24 ===<br />
<br />
Написать запрос SELECT: выдать наименования деталей и общее их количество для всех наименований деталей, изготавливаемых в одном городе.<br />
<br />
<!-- С этим запросом возникла неожиданная проблема - задание то ли неполное, то ли неправильное, потому что нельзя однозначно сказать, что по нему требуется сделать.<br />
<br />
Так что тут несколько вариантов запросов (кто как понял).<br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Первый вариант запроса''<br />
<div class="mw-collapsible-content"><br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT nazvanie, kol<br />
FROM spasoi_ekz.p A, (<br />
SELECT X.gorod, SUM(kolichestvo) as kol<br />
FROM spasoi_ekz.p X, spasoi_ekz.spj Y<br />
WHERE Y.nomer_detali = X.nomer_detali<br />
GROUP BY X.gorod<br />
) B<br />
WHERE A.gorod = B.gorod;<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
nazvanie | kol<br />
----------------------+------<br />
подставка | 9<br />
стойка | 9<br />
абажур | 9<br />
гайка | 139<br />
ось | 60<br />
зубчатое колесо | 60<br />
транзистор | 50<br />
печатная плата | 50<br />
диод | 50<br />
универсальная деталь | 13<br />
уникальная деталь | 14<br />
болт | 9137<br />
рама | 69<br />
колесо | 69<br />
втулка | 69<br />
бумага | 3122<br />
плитка | 14<br />
орнамент | 14<br />
уголок | 14<br />
гайка 01-01 | 27<br />
усиленная рама | 10<br />
винт | 9137<br />
труселя | 1<br />
штуцерная деталь | 10<br />
шуруп | 139<br />
(25 rows)<br />
</div><br />
</div><br />
и --><br />
В уточнение задания Григорьев сказал следующее: "''Следует учесть, что в качестве наименования города может выступать переменная, в которую в некоторой программе должно быть занесено конкретное значение перед выполнением запроса''".<br />
<br />
То есть, вообще говоря, задание на запрос неполное, и его можно трактовать так: выдать наименования деталей и общее их количество для всех наименований деталей, изготавливаемых в городе <code>%НАЗВАНИЕГОРОДА%</code>. Вот эта переменная задаётся где-то в программе, а в БД идёт запрос с уже подставленным конкретным названием. <br />
<br />
Этот вариант запроса составлен, например, для Лондона. <br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT nazvanie, SUM(kolichestvo)<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
WHERE gorod = 'Лондон'<br />
GROUP BY nazvanie;<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
nazvanie | sum<br />
----------+-----<br />
гайка | 71<br />
шуруп | 68<br />
(2 rows)<br />
<br />
=== Билет 25 ===<br />
<br />
<br />
Написать запрос SELECT: выдать имена поставщиков, поставляющих хотя бы одну белую деталь для изделия с названием 'Велосипед 01-04' с объёмом поставки большим, чем средний объём поставки этого поставщика.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
JOIN spasoi_ekz.p P<br />
ON P.nomer_detali = SPJ.nomer_detali<br />
JOIN spasoi_ekz.j J<br />
ON J.nomer_izdelia = SPJ.nomer_izdelia<br />
WHERE cvet = 'белый'<br />
AND J.nazvanie = LOWER('Велосипед 01-04')<br />
AND kolichestvo > (<br />
SELECT AVG(kolichestvo)<br />
FROM spasoi_ekz.spj SPJ<br />
WHERE SPJ.nomer_postavshika = S.nomer_postavshika<br />
);<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
imya<br />
-------------<br />
Петров Пётр<br />
(1 row)<br />
<br />
=== Билет 26 ===<br />
<br />
Написать запрос SELECT: выдать номера красных деталей и количество поставок этих деталей.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT P.nomer_detali AS "Номер детали", COUNT(kolichestvo) AS "Количество поставок с ней"<br />
FROM spasoi_ekz.p P, spasoi_ekz.spj SPJ<br />
WHERE P.nomer_detali = SPJ.nomer_detali<br />
AND cvet = 'красный'<br />
GROUP BY P.nomer_detali; <br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Этот же запрос через JOIN''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT SPJ.nomer_detali AS "Номер детали", COUNT(kolichestvo) AS "Количество поставок с ней"<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
AND cvet = 'красный'<br />
GROUP BY SPJ.nomer_detali;<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
Номер детали | Количество поставок с ней<br />
--------------+---------------------------<br />
P15 | 3<br />
P22 | 1<br />
P24 | 1<br />
(3 rows)<br />
<br />
=== Билет 27 ===<br />
<br />
Написать запрос SELECT: выдать наименования городов и среднее состояние поставщиков для каждого города, поставляющих детали с названием 'Гайка 01-01' для изделия с названием 'Велосипед 03-04' в количестве (в поставке) большим, чем минимальный объём поставки, выполненной поставщиком с номером 'S1'.<br />
<br />
<br />
[[Файл:1st dufficulty.png|center]]<br />
<br />
<br />
<p align="center"><font size="7px">'''Сложнейший запрос экзамена!'''</font></p><br />
<br />
<p align="center"><font size="5px">'''Сохрани Джа, вытащить такое'''</font></p><br />
<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT S.gorod AS "Город", AVG(S.sostoyanie) AS "Среднее состояние"<br />
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
JOIN spasoi_ekz.p P<br />
ON P.nomer_detali = SPJ.nomer_detali<br />
JOIN spasoi_ekz.j J<br />
ON J.nomer_izdelia = SPJ.nomer_izdelia<br />
WHERE P.nazvanie = LOWER('Гайка 01-01')<br />
AND J.nazvanie = LOWER('Велосипед 03-04')<br />
AND kolichestvo > (<br />
SELECT MIN(kolichestvo)<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_postavshika = 'S1'<br />
)<br />
GROUP BY S.gorod;<br />
<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
Город | Среднее состояние<br />
-----------+-----------------------<br />
Мытищи | 35000.500000000000<br />
Йокогама | 1500000.000000000000<br />
Манчестер | 2571.0000000000000000<br />
(3 rows)<br />
<br />
=== Билет 28 ===<br />
<br />
Написать запрос SELECT: выдать названия изделий, куда входит хотя бы одна красная деталь весом больше 10 граммов, поставляемая только поставщиком с номером 'S1'.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT J.nazvanie<br />
FROM spasoi_ekz.j J JOIN spasoi_ekz.spj SPJ1<br />
ON J.nomer_izdelia = SPJ1.nomer_izdelia<br />
JOIN spasoi_ekz.p P<br />
ON P.nomer_detali = SPJ1.nomer_detali<br />
WHERE P.ves > 10<br />
AND P.cvet = 'красный'<br />
AND NOT EXISTS (<br />
SELECT SPJ2.nomer_postavshika<br />
FROM spasoi_ekz.spj SPJ2 <br />
WHERE SPJ2.nomer_detali = P.nomer_detali<br />
AND SPJ2.nomer_postavshika != 'S1'<br />
); <br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
nazvanie<br />
-----------------<br />
кружевное бельё<br />
(1 row)<br />
<br />
=== Билет 29 ===<br />
Написать запрос SELECT: выдать названия деталей, которые входят только и только в состав изделия с названием 'Штуцер 01-03'.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT nazvanie<br />
FROM spasoi_ekz.p<br />
WHERE NOT EXISTS (<br />
SELECT SPJ.nomer_izdelia<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J<br />
ON J.nomer_izdelia = SPJ.nomer_izdelia<br />
WHERE (<br />
J.nazvanie != LOWER('Штуцер 01-03')<br />
AND<br />
SPJ.nomer_detali = P.nomer_detali<br />
)<br />
OR (<br />
J.nazvanie = LOWER('Штуцер 01-03')<br />
AND<br />
SPJ.nomer_detali != P.nomer_detali<br />
)<br />
);<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
nazvanie<br />
------------------<br />
штуцерная деталь<br />
(1 row)<br />
<br />
=== Билет 30 ===<br />
Написать запрос SELECT: выдать имена поставщиков, которые поставляют, по крайней мере, все те детали, которые поставляет поставщик с номером 'S2'.<br />
<br />
Текст запроса:<br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT imya<br />
FROM spasoi_ekz.s S<br />
WHERE NOT EXISTS (<br />
SELECT nomer_detali<br />
FROM spasoi_ekz.spj SPJY<br />
WHERE nomer_postavshika = 'S2'<br />
AND NOT EXISTS (<br />
SELECT *<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_postavshika = S.nomer_postavshika<br />
AND nomer_detali = SPJY.nomer_detali<br />
)<br />
);<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Ещё вариант''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT imya<br />
FROM spasoi_ekz.spj SPJ<br />
WHERE NOT EXISTS (<br />
(SELECT DISTINCT nomer_detali FROM spasoi_ekz.spj WHERE nomer_postavshika = 'S2')<br />
EXCEPT<br />
(SELECT DISTINCT nomer_detali FROM spasoi_ekz.spj WHERE nomer_postavshika = SPJ.nomer_postavshika)<br />
)<br />
AND nomer_postavshika != 'S2'<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
imya<br />
------------<br />
Оша<br />
Бран Старк<br />
(2 rows)<br />
[[Категория:Структурное проектирование АСОИ (10 семестр)]]</div>
37.190.38.24
https://iu5bmstu.ru/index.php?title=SQL-%D0%B7%D0%B0%D0%BF%D1%80%D0%BE%D1%81%D1%8B_%D0%BA_%D1%8D%D0%BA%D0%B7%D0%B0%D0%BC%D0%B5%D0%BD%D1%83_%D0%BF%D0%BE_%D0%A1%D0%9F%D0%90%D0%A1%D0%9E%D0%98_(10_%D1%81%D0%B5%D0%BC%D0%B5%D1%81%D1%82%D1%80)&diff=3912
SQL-запросы к экзамену по СПАСОИ (10 семестр)
2013-06-20T18:49:18Z
<p>37.190.38.24: /* Билет 1 */</p>
<hr />
<div><div style="float:right; clear:both; margin-right:1.0em;">__TOC__</div><br />
<br />
Билет экзамена по [[:Категория:Структурное проектирование АСОИ (10 семестр) | СПАСОИ]] состоит из двух частей:<br />
# теория;<br />
# SQL-запрос.<br />
<br />
На этой странице собраны все сформированные запросы по билетам.<br />
<br />
Странно, что ни в одном билете нет запроса с сортировкой результатов. <s>Возможно, они буду в дополнительных заданиях.</s> Григорьев сказал, что это слишком просто и потому он не стал загромождать запросы дополнительными мелочами.<br />
<br />
== Схема БД ==<br />
<br />
Схема БД используется всё [[СПАСОИ_(10)_-_Лекция_№8_-_SQL#Некоторые возможности языка SQL | та же]], что была в прошлом семестре и на лекциях.<br />
<br />
Для написания запросов и проверки их выполнения создана БД в СУБД [http://www.postgresql.org/ PostgreSQL].<br />
<br />
Скрипт создания можно загрузить [http://yadi.sk/d/ArwqOGAy5pPfi отсюда].<br />
<br />
=== Таблицы БД ===<br />
<br />
==== Поставщики ====<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT * FROM spasoi_ekz.s;<br />
</syntaxhighlight><br />
<br />
nomer_postavshika | imya | sostoyanie | gorod<br />
-------------------+------------------+------------+------------<br />
S5 | Мелисандра | 65000 | Мадрид<br />
S2 | Бран Старк | 60000 | Мурманск<br />
S1 | Якен Хгар | 1500000 | Йокогама<br />
S7 | Сирио Форель | 1500000 | Йокогама<br />
S4 | Джейме Ланнистер | 750000 | Лондон<br />
S3 | Серсея Ланнистер | 1200000 | Лондон<br />
S8 | Тирион Ланнистер | 2571 | Манчестер<br />
S9 | Иванов | 35000 | Мытищи<br />
S10 | Русе Болтон | 44444 | Баренцбург<br />
S11 | Петров Иван | 35000 | Мытищи<br />
S14 | Рендилл Тарли | 1111111 | Прага<br />
S13 | Сэмвелл Тарли | 999999 | Прага<br />
S6 | Оша | | Москва<br />
S16 | Ходор | | Москва<br />
S15 | Мелисса Флорент | 888888 | Стокгольм<br />
S12 | Петров Пётр | 35001 | Мытищи<br />
S17 | Томми Версетти | 123456789 | Майами<br />
S18 | Безликий | 1 | Йокогама<br />
(18 rows)<br />
<br />
==== Детали ====<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT * FROM spasoi_ekz.p;<br />
</syntaxhighlight><br />
<br />
nomer_detali | nazvanie | cvet | ves | gorod<br />
--------------+----------------------+---------------+------+-----------------<br />
P9 | подставка | синий | 400 | Нижний Новгород<br />
P10 | стойка | синий | 2000 | Нижний Новгород<br />
P11 | абажур | синий | 400 | Нижний Новгород<br />
P1 | гайка | чёрный | 20 | Лондон<br />
P3 | ось | белый | 5000 | Эдинбург<br />
P4 | зубчатое колесо | чёрный | 50 | Эдинбург<br />
P6 | транзистор | коричневый | 2 | Токио<br />
P7 | печатная плата | зелёный | 200 | Токио<br />
P8 | диод | коричневый | 1 | Токио<br />
P12 | универсальная деталь | универсальный | 1 | Париж<br />
P13 | уникальная деталь | уникальный | 1 | Бостон<br />
P14 | болт | серый | 20 | Ижевск<br />
P15 | рама | красный | 3000 | Манчестер<br />
P16 | колесо | белый | 1500 | Манчестер<br />
P5 | втулка | серый | 350 | Манчестер<br />
P17 | бумага | белый | 1 | Астрахань<br />
P18 | плитка | белый | 300 | Флоренция<br />
P19 | орнамент | серый | 800 | Флоренция<br />
P20 | уголок | серый | 100 | Флоренция<br />
P21 | гайка 01-01 | серебристый | 20 | Клин<br />
P22 | усиленная рама | красный | 3200 | Череповец<br />
P23 | винт | розовый | 5 | Ижевск<br />
P24 | труселя | красный | 20 | Челябинск<br />
P25 | штуцерная деталь | коричневый | 450 | Череповец<br />
P2 | шуруп | чёрный | 5 | Лондон<br />
P26 | лезвие | бесцветный | 120 | Йокогама<br />
(26 rows)<br />
<br />
==== Изделия ====<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT * FROM spasoi_ekz.j;<br />
</syntaxhighlight><br />
<br />
nomer_izdelia | nazvanie | gorod<br />
---------------+-----------------------+-----------------<br />
J1 | автомобиль | Магнитогорск<br />
J2 | процессор | Зеленоград<br />
J3 | торшер | Нижний Новгород<br />
J4 | универсальное изделие | Париж<br />
J5 | уникальное изделие | Бостон<br />
J6 | велосипед 01/23 | Манчестер<br />
J7 | изделие из болтов | Челябинск<br />
J8 | шкаф | Ярославль<br />
J9 | рама 02-01 | Череповец<br />
J10 | книга | Астрахань<br />
J11 | панно 01-03 | Флоренция<br />
J12 | велосипед 03-04 | Клин<br />
J13 | рама 02-03 | Череповец<br />
J14 | штуцер 01-02 | Череповец<br />
J15 | велосипед 01-04 | Клин<br />
J16 | кружевное бельё | Челябинск<br />
J17 | штуцер 01-03 | Череповец<br />
J18 | кинжал | Йокогама<br />
(18 rows)<br />
<br />
==== Сборки ====<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT * FROM spasoi_ekz.spj;<br />
</syntaxhighlight><br />
<br />
nomer_postavshika | nomer_detali | nomer_izdelia | kolichestvo<br />
-------------------+--------------+---------------+-------------<br />
S1 | P6 | J2 | 20<br />
S1 | P7 | J2 | 5<br />
S2 | P1 | J1 | 4<br />
S6 | P4 | J1 | 2<br />
S6 | P5 | J1 | 6<br />
S3 | P9 | J3 | 1<br />
S4 | P10 | J3 | 1<br />
S5 | P11 | J3 | 1<br />
S2 | P4 | J1 | 8<br />
S6 | P3 | J1 | 50<br />
S7 | P8 | J2 | 25<br />
S1 | P12 | J4 | 1<br />
S2 | P12 | J4 | 1<br />
S3 | P12 | J4 | 1<br />
S4 | P12 | J4 | 1<br />
S5 | P12 | J4 | 1<br />
S7 | P12 | J4 | 1<br />
S7 | P2 | J1 | 1<br />
S6 | P2 | J1 | 9<br />
S6 | P12 | J4 | 7<br />
S1 | P13 | J5 | 14<br />
S6 | P14 | J1 | 9000<br />
S2 | P14 | J1 | 3<br />
S6 | P1 | J1 | 12<br />
S8 | P15 | J6 | 1<br />
S8 | P16 | J6 | 2<br />
S3 | P5 | J6 | 10<br />
S10 | P2 | J8 | 4<br />
S8 | P1 | J7 | 16<br />
S9 | P14 | J7 | 3<br />
S11 | P10 | J3 | 2<br />
S12 | P15 | J1 | 3<br />
S11 | P11 | J3 | 4<br />
S10 | P14 | J9 | 5<br />
S13 | P17 | J10 | 1001<br />
S14 | P17 | J10 | 1111<br />
S15 | P17 | J10 | 1010<br />
S5 | P18 | J11 | 9<br />
S5 | P19 | J11 | 1<br />
S5 | P20 | J11 | 4<br />
S9 | P14 | J8 | 25<br />
S12 | P21 | J12 | 15<br />
S11 | P22 | J13 | 9<br />
S11 | P1 | J14 | 26<br />
S12 | P1 | J14 | 13<br />
S12 | P2 | J14 | 54<br />
S12 | P23 | J4 | 101<br />
S12 | P16 | J15 | 40<br />
S7 | P21 | J12 | 3<br />
S8 | P21 | J12 | 4<br />
S9 | P21 | J12 | 5<br />
S1 | P24 | J16 | 1<br />
S1 | P15 | J6 | 3<br />
S4 | P25 | J17 | 1<br />
S17 | P16 | J1 | 4<br />
S18 | P26 | J18 | 1<br />
(56 rows)<br />
<br />
== Готовые запросы ==<br />
<br />
=== Некоторое вступление ===<br />
<br />
Если видите, что тот или иной запрос можно составить короче и рациональней - смело вносите правку.<br />
<br />
==== Про только и только ====<br />
<br />
В трёх билетах в задании на запрос встречается формулировка "''только и только''". Как оказалось, это означает следующее: если холодильники поставляет ''только и только'' Уася, то:<br />
# кроме Уаси никто не поставляет холодильники;<br />
# Уася не поставляет ничего, кроме холодильников.<br />
<br />
<s>Великий и могучий русский языка, ну что за экономия на бумаге.</s><br />
<br />
Существующие запросы, естественно, оказались неправильными и их пришлось переписать.<br />
<br />
==== Про JOIN ====<br />
<br />
Почти все запросы, где используется соединение таблиц, можно написать с использованием <code>JOIN</code>, а можно просто с перечислением таблиц через запятую.<br />
<br />
Но неожиданно оказалось, это почти то же самое и называется [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> более правильным и вообще прогрессивным.<br />
<br />
=== Билет 1 ===<br />
<br />
Написать запрос SELECT: выдать номера поставщиков, которые поставляют, по крайней мере, все те детали, которые поставляет поставщик с номером 'S2'.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT nomer_postavshika<br />
FROM spasoi_ekz.spj SPJX<br />
WHERE NOT EXISTS (<br />
SELECT nomer_detali<br />
FROM spasoi_ekz.spj SPJY<br />
WHERE nomer_postavshika = 'S2'<br />
AND NOT EXISTS (<br />
SELECT *<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_postavshika = SPJX.nomer_postavshika<br />
AND nomer_detali = SPJY.nomer_detali<br />
)<br />
);<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Ещё вариант''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT nomer_postavshika<br />
FROM spasoi_ekz.spj SPJ<br />
WHERE NOT EXISTS (<br />
(SELECT DISTINCT nomer_detali FROM spasoi_ekz.spj WHERE nomer_postavshika = 'S2')<br />
EXCEPT<br />
(SELECT DISTINCT nomer_detali FROM spasoi_ekz.spj WHERE nomer_postavshika = SPJ.nomer_postavshika)<br />
)<br />
AND nomer_postavshika != 'S2'<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
nomer_postavshika<br />
-------------------<br />
S6<br />
S2<br />
(2 rows)<br />
<br />
=== Билет 2 ===<br />
<br />
Написать запрос SELECT: выдать номера деталей и общее их количество для всех номеров деталей, поставляемых более чем одним поставщиком.<br />
<br />
Текст запроса, каким его дал Григорьев на лекции, впоследствии исправив его:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT nomer_detali AS "Номер детали",<br />
SUM(kolichestvo) AS "Сколько штук поставляется",<br />
COUNT(DISTINCT nomer_postavshika) AS "Сколько у неё поставщиков"<br />
FROM spasoi_ekz.spj<br />
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_postavshika) > 1;<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Ещё вариант''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT nomer_detali AS "Номер детали",<br />
kol AS "Сколько штук поставляется"<br />
FROM (<br />
SELECT nomer_detali,<br />
SUM(kolichestvo) AS kol,<br />
COUNT(DISTINCT nomer_postavshika)<br />
FROM spasoi_ekz.spj<br />
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_postavshika) > 1<br />
) A;<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
Номер детали | Сколько штук поставляется | Сколько у неё поставщиков<br />
--------------+---------------------------+---------------------------<br />
P1 | 71 | 5<br />
P10 | 3 | 2<br />
P11 | 5 | 2<br />
P12 | 13 | 7<br />
P14 | 9036 | 4<br />
P15 | 7 | 3<br />
P16 | 46 | 3<br />
P17 | 3122 | 3<br />
P2 | 68 | 4<br />
P21 | 27 | 4<br />
P4 | 10 | 2<br />
P5 | 16 | 2<br />
(12 rows)<br />
<br />
=== Билет 3 ===<br />
<br />
Написать запрос SELECT: выдать номера поставщиков, поставляющих детали с номером 'P1' для какого-либо изделия в количестве (в поставке) большим, чем средний объём поставок деталей с номером 'P2' для этого изделия.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT X.nomer_postavshika <br />
FROM spasoi_ekz.spj X<br />
WHERE X.nomer_detali = 'P1'<br />
AND X.kolichestvo > (<br />
SELECT AVG(kolichestvo)<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_izdelia = X.nomer_izdelia<br />
AND nomer_detali = 'P2'<br />
);<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Этот же запрос, но длиннее и нерациональней''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT nomer_postavshika<br />
FROM (<br />
SELECT *<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_izdelia IN(<br />
SELECT nomer_izdelia<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_detali = 'P1'<br />
)<br />
AND nomer_izdelia IN(<br />
SELECT nomer_izdelia<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_detali = 'P2'<br />
)<br />
) A<br />
WHERE nomer_detali = 'P1' AND kolichestvo ><br />
(<br />
SELECT AVG(kolichestvo)<br />
FROM (<br />
SELECT *<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_izdelia IN(<br />
SELECT nomer_izdelia<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_detali = 'P1'<br />
)<br />
AND nomer_izdelia IN(<br />
SELECT nomer_izdelia<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_detali = 'P2'<br />
)<br />
) B<br />
WHERE nomer_detali = 'P2'<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
nomer_postavshika<br />
-------------------<br />
S6<br />
(1 row)<br />
<br />
=== Билет 4 ===<br />
<br />
Написать запрос SELECT: выдать номера изделий, для которых детали поставляет только поставщик с номером ‘S1’.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT nomer_izdelia<br />
FROM spasoi_ekz.spj X<br />
WHERE NOT EXISTS (<br />
SELECT *<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_postavshika != 'S1'<br />
AND X.nomer_izdelia = nomer_izdelia<br />
);<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Этот же запрос, но без EXISTS''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT nomer_izdelia<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_izdelia IN(<br />
SELECT nomer_izdelia<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_postavshika = 'S1'<br />
)<br />
AND nomer_izdelia NOT IN(<br />
SELECT nomer_izdelia<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_postavshika != 'S1'<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
nomer_izdelia<br />
---------------<br />
J5<br />
J16<br />
(2 rows)<br />
<br />
=== Билет 5 ===<br />
<br />
Написать запрос SELECT: выдать имена поставщиков, поставляющих детали с названием "Болт" в количестве (в поставке) большим, чем средний объём всех поставок деталей с номером 'P1'.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
AND nazvanie = LOWER('Болт')<br />
WHERE kolichestvo > (<br />
SELECT AVG(kolichestvo)<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_detali = 'P1'<br />
);<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Вариант запроса без JOIN''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.s S, spasoi_ekz.p P, spasoi_ekz.spj SPJ<br />
WHERE P.nazvanie = LOWER('Болт')<br />
AND P.nomer_detali = SPJ.nomer_detali<br />
AND S.nomer_postavshika = SPJ.nomer_postavshika<br />
AND SPJ.kolichestvo > (<br />
SELECT AVG(kolichestvo)<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_detali = 'P1'<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Ещё вариант запроса без JOIN и сложнее''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.s<br />
WHERE nomer_postavshika IN (<br />
SELECT nomer_postavshika<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_detali = (<br />
SELECT nomer_detali<br />
FROM spasoi_ekz.p<br />
WHERE LOWER(nazvanie) = LOWER('Болт')<br />
)<br />
AND kolichestvo > (<br />
SELECT AVG(kolichestvo)<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_detali = 'P1'<br />
)<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
imya<br />
------<br />
Оша<br />
Иванов<br />
(2 rows)<br />
<br />
=== Билет 6 ===<br />
<br />
Написать запрос SELECT: выдать названия деталей, поставляемых поставщиком, проживающим в том же городе, где изготавливаются эти детали, для изделия с названием ‘Велосипед 01/23’.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT P.nazvanie<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
WHERE S.gorod = P.gorod<br />
AND J.nazvanie = LOWER('Велосипед 01/23');<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Этот же запрос без JOIN''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT nazvanie<br />
FROM spasoi_ekz.S S, spasoi_ekz.p P, spasoi_ekz.spj SPJ<br />
WHERE S.gorod = P.gorod<br />
AND P.nomer_detali = SPJ.nomer_detali<br />
AND S.nomer_postavshika = SPJ.nomer_postavshika<br />
AND nomer_izdelia = (<br />
SELECT nomer_izdelia<br />
FROM spasoi_ekz.j<br />
WHERE nazvanie = LOWER('Велосипед 01/23')<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
nazvanie<br />
----------<br />
рама<br />
колесо<br />
(2 rows)<br />
<br />
=== Билет 7 ===<br />
<br />
Написать запрос SELECT: выдать названия изделий, куда входят детали с названием 'Болт', поставляемых поставщиками с именем 'Иванов', в количестве (в поставке) большим, чем средний объём поставок для этого изделия.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT J.nazvanie<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
WHERE imya LIKE '%Иванов%'<br />
AND P.nazvanie = LOWER('Болт')<br />
AND kolichestvo > (<br />
SELECT AVG(kolichestvo)<br />
FROM spasoi_ekz.spj X<br />
WHERE SPJ.nomer_izdelia = X.nomer_izdelia<br />
);<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Ещё один вариант запроса''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT nazvanie<br />
FROM spasoi_ekz.j<br />
WHERE nomer_izdelia IN (<br />
SELECT nomer_izdelia<br />
FROM (spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
AND imya LIKE '%Иванов%'<br />
JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
AND nazvanie = LOWER('Болт')) A<br />
WHERE kolichestvo > (<br />
SELECT AVG(kolichestvo)<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = A.nomer_izdelia<br />
)<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''И ещё вариант этого же запроса, но длиннее и нерациональней''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT nazvanie<br />
FROM (<br />
SELECT J.nazvanie, kolichestvo<br />
FROM spasoi_ekz.s S, spasoi_ekz.p P, spasoi_ekz.j J, spasoi_ekz.spj SPJ<br />
WHERE J.nomer_izdelia = SPJ.nomer_izdelia<br />
AND P.nomer_detali = SPJ.nomer_detali<br />
AND P.nazvanie = LOWER('Болт')<br />
AND S.imya LIKE '%Иванов%'<br />
AND S.nomer_postavshika = SPJ.nomer_postavshika<br />
) A<br />
WHERE kolichestvo ><br />
(<br />
SELECT AVG(kolichestvo)<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_izdelia IN(<br />
SELECT J.nomer_izdelia<br />
FROM spasoi_ekz.s S, spasoi_ekz.p P, spasoi_ekz.j J, spasoi_ekz.spj SPJ<br />
WHERE J.nomer_izdelia = SPJ.nomer_izdelia<br />
AND P.nomer_detali = SPJ.nomer_detali<br />
AND P.nazvanie = LOWER('Болт')<br />
AND S.imya LIKE '%Иванов%'<br />
AND S.nomer_postavshika = SPJ.nomer_postavshika<br />
)<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
nazvanie<br />
-------------------<br />
шкаф<br />
(1 row)<br />
<br />
=== Билет 8 ===<br />
<br />
Написать запрос SELECT: выдать номера изделий и общее количество деталей для них, поставляемых поставщиками с именем 'Петров'.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT nomer_izdelia AS "Номер изделия", SUM(kolichestvo) AS "Количество деталей" <br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
-- так как "поставщикАМИ" и возможны<br />
-- Иван Петров и Петров Иван, то LIKE %Петров%<br />
AND imya LIKE '%Петров%'<br />
GROUP BY nomer_izdelia;<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
Номер изделия | Количество деталей<br />
---------------+--------------------<br />
J4 | 101<br />
J3 | 6<br />
J13 | 9<br />
J15 | 40<br />
J1 | 3<br />
J12 | 15<br />
J14 | 93<br />
(7 rows)<br />
<br />
=== Билет 9 ===<br />
<br />
Написать запрос SELECT: выдать номера деталей и общее их количество для всех номеров деталей, которые входят только в одно изделие.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT nomer_detali, SUM(kolichestvo)<br />
FROM spasoi_ekz.spj<br />
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_izdelia) = 1;<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Ещё вариант''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT nomer_detali, kol FROM (<br />
SELECT nomer_detali, SUM(kolichestvo) AS kol, COUNT(DISTINCT nomer_izdelia) = 1<br />
FROM spasoi_ekz.spj<br />
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_izdelia) = 1<br />
) A;<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
<br />
Результат:<br />
<br />
nomer_detali | sum<br />
--------------+------<br />
P10 | 3<br />
P11 | 5<br />
P12 | 13<br />
P13 | 14<br />
P17 | 3122<br />
P18 | 9<br />
P19 | 1<br />
P20 | 4<br />
P21 | 27<br />
P22 | 9<br />
P23 | 101<br />
P24 | 1<br />
P25 | 1<br />
P26 | 1<br />
P3 | 50<br />
P4 | 10<br />
P6 | 20<br />
P7 | 5<br />
P8 | 25<br />
P9 | 1<br />
(20 rows)<br />
<br />
=== Билет 10 ===<br />
<br />
Написать запрос SELECT: выдать имена поставщиков, поставляющих детали с названием 'Болт' для изделия с названием 'Рама 02-01' в количестве (в поставке) большим, чем минимальное значение поставки детали с номером 'P1'.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
AND J.nazvanie = LOWER('Рама 02-01')<br />
JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
AND P.nazvanie = LOWER('Болт')<br />
JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
WHERE kolichestvo > (<br />
SELECT MIN(kolichestvo)<br />
FROM spasoi_ekz.spj <br />
WHERE nomer_detali = 'P1'<br />
);<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
imya<br />
-------------<br />
Русе Болтон<br />
(1 row)<br />
<br />
=== Билет 11 ===<br />
<br />
Написать запрос SELECT: выдать названия городов и суммарное состояние проживающих в каждом городе поставщиков, у которых минимальный объём поставки деталей больше 1000.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT gorod, SUM(sostoyanie)<br />
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ <br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
GROUP BY gorod HAVING MIN(kolichestvo) > 1000;<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Ещё вариант этого запроса''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT gorod, SUM(sostoyanie)<br />
FROM spasoi_ekz.s S<br />
WHERE ( <br />
SELECT MIN(kolichestvo) <br />
FROM spasoi_ekz.spj X <br />
WHERE X.nomer_postavshika = S.nomer_postavshika <br />
) > 1000<br />
GROUP BY gorod;<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''И ещё вариант подлиннее для тех, кто не ищет лёгких путей''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT gorod, SUM(sostoyanie)<br />
FROM spasoi_ekz.s<br />
WHERE nomer_postavshika IN (<br />
SELECT nomer_postavshika<br />
FROM spasoi_ekz.spj<br />
GROUP BY nomer_postavshika HAVING MIN(kolichestvo) > 1000<br />
)<br />
GROUP BY gorod;<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<!-- а этот запрос выполняет не совсем то и не правильно<br />
<syntaxhighlight lang="sql"><br />
SELECT gorod, sost<br />
FROM (<br />
SELECT gorod, SUM(sostoyanie) AS sost, SUM(SPJ.kolichestvo)<br />
FROM spasoi_ekz.spj, spasoi_ekz.s<br />
WHERE S.nomer_postavshika = SPJ.nomer_postavshika<br />
GROUP BY S.gorod HAVING SUM(SPJ.kolichestvo) > 1000<br />
) A;<br />
</syntaxhighlight> --> <br />
<br />
Результат:<br />
<br />
gorod | sum<br />
-----------+---------<br />
Прага | 2111110<br />
Стокгольм | 888888<br />
(2 rows)<br />
<br />
=== Билет 12 ===<br />
<br />
Написать запрос SELECT: выдать номера деталей, поставляемых для какого-либо изделия поставщиком, проживающим в том же городе, где изготавливается это изделие.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT nomer_detali<br />
FROM spasoi_ekz.spj SPJ, spasoi_ekz.s S, spasoi_ekz.j J<br />
WHERE SPJ.nomer_postavshika = S.nomer_postavshika<br />
AND S.gorod = J.gorod<br />
AND J.nomer_izdelia = SPJ.nomer_izdelia;<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
nomer_detali<br />
--------------<br />
P15<br />
P16<br />
P26<br />
(3 rows)<br />
<br />
=== Билет 13 ===<br />
<br />
Написать <u>один</u> запрос SELECT: выдать количества строк в таблице S (Поставщик) 1) с пустыми значениями и 2) непустыми значениями в столбце Состояние.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT COUNT(*) - COUNT(sostoyanie) AS "С пустым состоянием", <br />
COUNT(sostoyanie) AS "С не пустым состоянием"<br />
FROM spasoi_ekz.s;<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Этот же запрос, но длиннее и нерациональней''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
<br />
SELECT COUNT(Snull.*) AS "С пустым состоянием", COUNT(Snotnull.sostoyanie) AS "С не пустым состоянием"<br />
FROM spasoi_ekz.s Snull RIGHT OUTER JOIN spasoi_ekz.s Snotnull<br />
ON Snull.nomer_postavshika = Snotnull.nomer_postavshika<br />
AND Snull.sostoyanie IS NULL;<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
С пустым состоянием | С не пустым состоянием<br />
---------------------+-----------------------<br />
2 | 16<br />
(1 row)<br />
<br />
=== Билет 14 ===<br />
<br />
Написать запрос SELECT: выдать имена поставщиков, которые поставляют детали только и только для изделия с номером 'J1'.<br />
<br />
Чтобы продемонстрировать выполнение запроса наглядно, возьмём изделие не 'J1', а 'J18', которое было создано специально для этого запроса. Если оставить 'J1', то наглядности не получится, так как по нашей схеме БД это изделие не попадает под условие "только и только", и запрос вернёт пустой результат.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT imya<br />
FROM spasoi_ekz.s S<br />
WHERE NOT EXISTS (<br />
SELECT *<br />
FROM spasoi_ekz.spj<br />
WHERE (<br />
nomer_izdelia != 'J18'<br />
AND<br />
nomer_postavshika = S.nomer_postavshika)<br />
OR<br />
(<br />
nomer_izdelia = 'J18'<br />
AND<br />
nomer_postavshika != S.nomer_postavshika<br />
)<br />
);<br />
</syntaxhighlight><br />
<!-- этот запрос не подходит под "только и только", подробности в начале страницы<br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.spj A JOIN spasoi_ekz.s S<br />
ON A.nomer_postavshika = S.nomer_postavshika<br />
WHERE nomer_izdelia = 'J1'<br />
AND NOT EXISTS (<br />
SELECT nomer_postavshika<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_izdelia != 'J1'<br />
AND nomer_postavshika = A.nomer_postavshika<br />
);<br />
</syntaxhighlight> --><br />
<br />
Результат:<br />
<br />
imya<br />
----------------<br />
Безликий<br />
(1 row)<br />
<br />
=== Билет 15 ===<br />
<br />
Написать запрос SELECT: выдать наименования изделий, для которых детали поставляют только те поставщики, у которых состояние больше 1000000 у.е.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT nazvanie<br />
FROM spasoi_ekz.j J<br />
WHERE NOT EXISTS (<br />
SELECT *<br />
FROM spasoi_ekz.spj X JOIN spasoi_ekz.s S<br />
ON X.nomer_postavshika = S.nomer_postavshika<br />
WHERE sostoyanie <= 1000000<br />
AND X.nomer_izdelia = J.nomer_izdelia<br />
);<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Вариант этого запроса без NOT EXISTS''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT nazvanie<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
WHERE sostoyanie > 1000000<br />
AND SPJ.nomer_izdelia NOT IN (<br />
SELECT nomer_izdelia<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
WHERE sostoyanie < 1000000<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
nazvanie<br />
--------------------<br />
кружевное бельё<br />
уникальное изделие<br />
процессор<br />
(3 rows)<br />
<br />
=== Билет 16 ===<br />
Написать запрос SELECT: выдать цвета и для каждого цвета общее количество деталей, входящих в изделие с названием 'Панно 01-03'.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT cvet AS "Цвет", SUM(kolichestvo) AS "Деталей"<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
AND J.nazvanie = LOWER('Панно 01-03')<br />
JOIN spasoi_ekz.P P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
GROUP BY cvet;<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Другой вариант запроса для тонко чувствующих натур''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT cvet AS "Цвет", SUM(kolichestvo) AS "Деталей"<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
WHERE J.nazvanie = LOWER('Панно 01-03')<br />
GROUP BY cvet;<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
Цвет | Деталей<br />
-------+---------<br />
белый | 9<br />
серый | 5<br />
(2 rows)<br />
<br />
=== Билет 17 ===<br />
<br />
Написать запрос SELECT: выдать номера поставщиков и количество сделанных ими поставок при условии, что среднее число деталей во всех этих поставках больше 1000.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT nomer_postavshika AS "Номер поставщика",<br />
COUNT(*) AS "Количество поставок"<br />
FROM spasoi_ekz.spj<br />
GROUP BY nomer_postavshika HAVING AVG(kolichestvo) > 1000;<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Ещё вариант запроса''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT nomer_postavshika AS "Номер поставщика", COUNT(*) AS "Количество поставок"<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_postavshika IN (<br />
SELECT nomer_postavshika<br />
FROM spasoi_ekz.spj<br />
GROUP BY nomer_postavshika HAVING AVG(kolichestvo) > 1000<br />
)<br />
GROUP BY nomer_postavshika;<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''И ещё вариант запроса''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT nomer_postavshika AS "Номер поставщика", COUNT(*) AS "Количество поставок"<br />
FROM spasoi_ekz.spj<br />
WHERE (<br />
SELECT AVG(kolichestvo)<br />
FROM spasoi_ekz.spj X<br />
WHERE X.nomer_postavshika = spj.nomer_postavshika<br />
) > 1000<br />
GROUP BY nomer_postavshika;<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''И даже ещё вариант запроса''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT nom AS "Номер поставщика", cnt AS "Количество поставок"<br />
FROM (<br />
SELECT S.nomer_postavshika AS nom,<br />
COUNT(SPJ.nomer_postavshika) AS cnt,<br />
AVG(SPJ.kolichestvo) AS kol<br />
FROM spasoi_ekz.s S, spasoi_ekz.spj SPJ<br />
WHERE S.nomer_postavshika = SPJ.nomer_postavshika<br />
GROUP BY S.nomer_postavshika<br />
) A<br />
WHERE kol > 1000;<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
Номер поставщика | Количество поставок<br />
------------------+---------------------<br />
S13 | 1<br />
S6 | 7<br />
S14 | 1<br />
S15 | 1<br />
(4 rows)<br />
<br />
=== Билет 18 ===<br />
<br />
Написать запрос SELECT: выдать имена поставщиков, имеющих состояние больше 1000 и поставляющих деталь с названием 'Гайка 01-01' для изделия с названием 'Велосипед 03-04' в количестве (в поставке) большим, чем средний объём поставки, выполненной поставщиками с именем 'Иванов'.<br />
<br />
<br />
[[Файл:2nd dufficulty.png|center]]<br />
<br />
<br />
<p align="center"><font size="5px">'''Второй по сложности запрос экзамена!'''</font></p><br />
<br />
<p align="center"><font size="4px">'''Вот уж не свезло, так не свезло'''</font></p><br />
<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
JOIN spasoi_ekz.p P<br />
ON P.nomer_detali = SPJ.nomer_detali<br />
JOIN spasoi_ekz.j J<br />
ON J.nomer_izdelia = SPJ.nomer_izdelia<br />
WHERE sostoyanie > 1000<br />
AND P.nazvanie = LOWER('Гайка 01-01')<br />
AND J.nazvanie = LOWER('Велосипед 03-04')<br />
AND kolichestvo > (<br />
SELECT AVG(kolichestvo)<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika <br />
WHERE S.imya = 'Иванов'<br />
);<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
imya<br />
-------------<br />
Петров Пётр<br />
(1 row)<br />
<br />
=== Билет 19 ===<br />
<br />
Написать запрос SELECT: выдать названия красных деталей, которые входят только в изделие с названием 'Рама 02-03' в количестве, меньшем 10 единиц в поставке.<br />
<br />
Текст запроса:<br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT X.nazvanie<br />
FROM spasoi_ekz.p X JOIN spasoi_ekz.spj SPJ<br />
ON SPJ.nomer_detali = X.nomer_detali<br />
WHERE X.cvet = 'красный'<br />
AND kolichestvo < 10<br />
AND NOT EXISTS (<br />
SELECT *<br />
FROM spasoi_ekz.j J JOIN spasoi_ekz.spj SPJ<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
WHERE J.nazvanie != LOWER('Рама 02-03')<br />
AND X.nomer_detali = SPJ.nomer_detali<br />
);<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Ещё вариант этого же запроса''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT P.nazvanie<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
AND cvet = 'красный'<br />
JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
AND J.nazvanie = LOWER('Рама 02-03')<br />
WHERE kolichestvo < 10<br />
AND SPJ.nomer_detali NOT IN (<br />
SELECT nomer_detali<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
AND J.nazvanie != LOWER('Рама 02-03')<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
nazvanie<br />
----------------<br />
усиленная рама<br />
(1 row)<br />
<br />
=== Билет 20 ===<br />
<br />
Написать запрос SELECT: выдать имена поставщиков, поставляющих только белые детали.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
WHERE NOT EXISTS (<br />
SELECT *<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P<br />
ON P.nomer_detali = SPJ.nomer_detali<br />
WHERE nomer_postavshika = S.nomer_postavshika<br />
AND P.cvet != 'белый'<br />
);<br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Ещё один вариант этого же запроса''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
AND cvet = 'белый'<br />
JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
WHERE S.nomer_postavshika NOT IN (<br />
SELECT nomer_postavshika<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
AND cvet != 'белый'<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''И ещё один вариант этого же запроса''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.s<br />
WHERE nomer_postavshika NOT IN (<br />
SELECT spj.nomer_postavshika<br />
FROM spasoi_ekz.spj SPJ, spasoi_ekz.p P<br />
WHERE SPJ.nomer_detali = P.nomer_detali<br />
AND p.cvet != 'белый'<br />
)<br />
AND nomer_postavshika IN (<br />
SELECT spj.nomer_postavshika<br />
FROM spasoi_ekz.spj SPJ, spasoi_ekz.p P<br />
WHERE SPJ.nomer_detali = P.nomer_detali<br />
AND p.cvet = 'белый'<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
imya<br />
-----------------<br />
Рендилл Тарли<br />
Сэмвелл Тарли<br />
Мелисса Флорент<br />
Томми Версетти<br />
(4 rows)<br />
<br />
=== Билет 21 ===<br />
<br />
Написать запрос SELECT: выдать названия изделий, для которых детали поставляет только и только поставщик с номером 'S1'.<br />
<br />
Чтобы продемонстрировать выполнение запроса наглядно, возьмём поставщика не 'S1', а 'S18', который был создан специально для этого запроса. Если оставить 'S1', то наглядности не получится, так как по нашей схеме БД этот поставщик не попадает под условие "''только и только''", и запрос вернёт пустой результат.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT nazvanie<br />
FROM spasoi_ekz.j<br />
WHERE NOT EXISTS (<br />
SELECT *<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_postavshika != 'S18'<br />
AND nomer_izdelia = J.nomer_izdelia<br />
)<br />
AND NOT EXISTS (<br />
SELECT *<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_postavshika = 'S18'<br />
AND nomer_izdelia != J.nomer_izdelia<br />
);<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
nazvanie<br />
--------------------<br />
кинжал<br />
(1 row)<br />
<br />
=== Билет 22 ===<br />
<br />
Написать запрос SELECT: выдать имена поставщиков, которые поставляют только детали с номером 'P1' для изделия с именем 'Штуцер 01-02'.<br />
<br />
Текст запроса:<br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
AND J.nazvanie = LOWER('Штуцер 01-02')<br />
JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
WHERE NOT EXISTS (<br />
SELECT *<br />
FROM spasoi_ekz.spj X JOIN spasoi_ekz.j J<br />
ON X.nomer_izdelia = J.nomer_izdelia<br />
WHERE X.nomer_detali != 'P1' <br />
AND j.nazvanie = LOWER('Штуцер 01-02')<br />
AND S.nomer_postavshika = X.nomer_postavshika<br />
);<br />
</syntaxhighlight><br />
<br />
<!-- неверный запрос - номер изделия, а не название<br />
SELECT imya FROM S X<br />
JOIN SPJ Y ON X.nomer_postavshika=Y.nomer_postavshika<br />
WHERE X.nomer_postavshika NOT IN (<br />
SELECT DISTINCT nomer_postavshika FROM SPJ Z<br />
WHERE Z.nomer_izdelia != 'Штуцер 01-02'<br />
AND Z.nomer_detali!='P1');--><br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Этот же запрос, но длиннее и нерациональней''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
AND J.nazvanie = LOWER('Штуцер 01-02')<br />
JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
AND SPJ.nomer_detali = 'P1'<br />
JOIN spasoi_ekz.s S<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
WHERE SPJ.nomer_postavshika NOT IN (<br />
SELECT nomer_postavshika<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J<br />
ON SPJ.nomer_izdelia = J.nomer_izdelia<br />
AND J.nazvanie = LOWER('Штуцер 01-02')<br />
JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
AND SPJ.nomer_detali != 'P1'<br />
);<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
imya<br />
-------------<br />
Петров Иван<br />
(1 row)<br />
<br />
=== Билет 23 ===<br />
<br />
Написать запрос SELECT: выдать имена поставщиков, которые поставляют деталь с названием 'Винт' в количестве большим 100 единиц в одной поставке и имеют состояние больше среднего по их родному городу.<br />
<br />
Текст запроса:<br />
<syntaxhighlight lang="sql"><br />
SELECT S.imya<br />
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ<br />
ON S.nomer_postavshika = SPJ.nomer_postavshika<br />
JOIN spasoi_ekz.p P<br />
ON P.nomer_detali = SPJ.nomer_detali<br />
WHERE P.nazvanie = LOWER('Винт')<br />
AND SPJ.kolichestvo > 100<br />
AND S.sostoyanie > (<br />
SELECT AVG(SS.sostoyanie)<br />
FROM spasoi_ekz.s SS<br />
WHERE SS.gorod = S.gorod<br />
);<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
imya<br />
-------------<br />
Петров Пётр<br />
(1 row)<br />
<br />
=== Билет 24 ===<br />
<br />
Написать запрос SELECT: выдать наименования деталей и общее их количество для всех наименований деталей, изготавливаемых в одном городе.<br />
<br />
<!-- С этим запросом возникла неожиданная проблема - задание то ли неполное, то ли неправильное, потому что нельзя однозначно сказать, что по нему требуется сделать.<br />
<br />
Так что тут несколько вариантов запросов (кто как понял).<br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Первый вариант запроса''<br />
<div class="mw-collapsible-content"><br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT nazvanie, kol<br />
FROM spasoi_ekz.p A, (<br />
SELECT X.gorod, SUM(kolichestvo) as kol<br />
FROM spasoi_ekz.p X, spasoi_ekz.spj Y<br />
WHERE Y.nomer_detali = X.nomer_detali<br />
GROUP BY X.gorod<br />
) B<br />
WHERE A.gorod = B.gorod;<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
nazvanie | kol<br />
----------------------+------<br />
подставка | 9<br />
стойка | 9<br />
абажур | 9<br />
гайка | 139<br />
ось | 60<br />
зубчатое колесо | 60<br />
транзистор | 50<br />
печатная плата | 50<br />
диод | 50<br />
универсальная деталь | 13<br />
уникальная деталь | 14<br />
болт | 9137<br />
рама | 69<br />
колесо | 69<br />
втулка | 69<br />
бумага | 3122<br />
плитка | 14<br />
орнамент | 14<br />
уголок | 14<br />
гайка 01-01 | 27<br />
усиленная рама | 10<br />
винт | 9137<br />
труселя | 1<br />
штуцерная деталь | 10<br />
шуруп | 139<br />
(25 rows)<br />
</div><br />
</div><br />
и --><br />
В уточнение задания Григорьев сказал следующее: "''Следует учесть, что в качестве наименования города может выступать переменная, в которую в некоторой программе должно быть занесено конкретное значение перед выполнением запроса''".<br />
<br />
То есть, вообще говоря, задание на запрос неполное, и его можно трактовать так: выдать наименования деталей и общее их количество для всех наименований деталей, изготавливаемых в городе <code>%НАЗВАНИЕГОРОДА%</code>. Вот эта переменная задаётся где-то в программе, а в БД идёт запрос с уже подставленным конкретным названием. <br />
<br />
Этот вариант запроса составлен, например, для Лондона. <br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT nazvanie, SUM(kolichestvo)<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
WHERE gorod = 'Лондон'<br />
GROUP BY nazvanie;<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
nazvanie | sum<br />
----------+-----<br />
гайка | 71<br />
шуруп | 68<br />
(2 rows)<br />
<br />
=== Билет 25 ===<br />
<br />
<br />
Написать запрос SELECT: выдать имена поставщиков, поставляющих хотя бы одну белую деталь для изделия с названием 'Велосипед 01-04' с объёмом поставки большим, чем средний объём поставки этого поставщика.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT imya<br />
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
JOIN spasoi_ekz.p P<br />
ON P.nomer_detali = SPJ.nomer_detali<br />
JOIN spasoi_ekz.j J<br />
ON J.nomer_izdelia = SPJ.nomer_izdelia<br />
WHERE cvet = 'белый'<br />
AND J.nazvanie = LOWER('Велосипед 01-04')<br />
AND kolichestvo > (<br />
SELECT AVG(kolichestvo)<br />
FROM spasoi_ekz.spj SPJ<br />
WHERE SPJ.nomer_postavshika = S.nomer_postavshika<br />
);<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
imya<br />
-------------<br />
Петров Пётр<br />
(1 row)<br />
<br />
=== Билет 26 ===<br />
<br />
Написать запрос SELECT: выдать номера красных деталей и количество поставок этих деталей.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT P.nomer_detali AS "Номер детали", COUNT(kolichestvo) AS "Количество поставок с ней"<br />
FROM spasoi_ekz.p P, spasoi_ekz.spj SPJ<br />
WHERE P.nomer_detali = SPJ.nomer_detali<br />
AND cvet = 'красный'<br />
GROUP BY P.nomer_detali; <br />
</syntaxhighlight><br />
<br />
<div class="toccolours mw-collapsible mw-collapsed"><br />
''Этот же запрос через JOIN''<br />
<div class="mw-collapsible-content"><br />
<syntaxhighlight lang="sql"><br />
SELECT SPJ.nomer_detali AS "Номер детали", COUNT(kolichestvo) AS "Количество поставок с ней"<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P<br />
ON SPJ.nomer_detali = P.nomer_detali<br />
AND cvet = 'красный'<br />
GROUP BY SPJ.nomer_detali;<br />
</syntaxhighlight><br />
</div><br />
</div><br />
<br />
Результат:<br />
<br />
Номер детали | Количество поставок с ней<br />
--------------+---------------------------<br />
P15 | 3<br />
P22 | 1<br />
P24 | 1<br />
(3 rows)<br />
<br />
=== Билет 27 ===<br />
<br />
Написать запрос SELECT: выдать наименования городов и среднее состояние поставщиков для каждого города, поставляющих детали с названием 'Гайка 01-01' для изделия с названием 'Велосипед 03-04' в количестве (в поставке) большим, чем минимальный объём поставки, выполненной поставщиком с номером 'S1'.<br />
<br />
<br />
[[Файл:1st dufficulty.png|center]]<br />
<br />
<br />
<p align="center"><font size="7px">'''Сложнейший запрос экзамена!'''</font></p><br />
<br />
<p align="center"><font size="5px">'''Сохрани Джа, вытащить такое'''</font></p><br />
<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT S.gorod AS "Город", AVG(S.sostoyanie) AS "Среднее состояние"<br />
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ<br />
ON SPJ.nomer_postavshika = S.nomer_postavshika<br />
JOIN spasoi_ekz.p P<br />
ON P.nomer_detali = SPJ.nomer_detali<br />
JOIN spasoi_ekz.j J<br />
ON J.nomer_izdelia = SPJ.nomer_izdelia<br />
WHERE P.nazvanie = LOWER('Гайка 01-01')<br />
AND J.nazvanie = LOWER('Велосипед 03-04')<br />
AND kolichestvo > (<br />
SELECT MIN(kolichestvo)<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_postavshika = 'S1'<br />
)<br />
GROUP BY S.gorod;<br />
<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
Город | Среднее состояние<br />
-----------+-----------------------<br />
Мытищи | 35000.500000000000<br />
Йокогама | 1500000.000000000000<br />
Манчестер | 2571.0000000000000000<br />
(3 rows)<br />
<br />
=== Билет 28 ===<br />
<br />
Написать запрос SELECT: выдать названия изделий, куда входит хотя бы одна красная деталь весом больше 10 граммов, поставляемая только поставщиком с номером 'S1'.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT J.nazvanie<br />
FROM spasoi_ekz.j J JOIN spasoi_ekz.spj SPJ1<br />
ON J.nomer_izdelia = SPJ1.nomer_izdelia<br />
JOIN spasoi_ekz.p P<br />
ON P.nomer_detali = SPJ1.nomer_detali<br />
WHERE P.ves > 10<br />
AND P.cvet = 'красный'<br />
AND NOT EXISTS (<br />
SELECT SPJ2.nomer_postavshika<br />
FROM spasoi_ekz.spj SPJ2 <br />
WHERE SPJ2.nomer_detali = P.nomer_detali<br />
AND SPJ2.nomer_postavshika != 'S1'<br />
); <br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
nazvanie<br />
-----------------<br />
кружевное бельё<br />
(1 row)<br />
<br />
=== Билет 29 ===<br />
Написать запрос SELECT: выдать названия деталей, которые входят только и только в состав изделия с названием 'Штуцер 01-03'.<br />
<br />
Текст запроса:<br />
<br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT nazvanie<br />
FROM spasoi_ekz.p<br />
WHERE NOT EXISTS (<br />
SELECT SPJ.nomer_izdelia<br />
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J<br />
ON J.nomer_izdelia = SPJ.nomer_izdelia<br />
WHERE (<br />
J.nazvanie != LOWER('Штуцер 01-03')<br />
AND<br />
SPJ.nomer_detali = P.nomer_detali<br />
)<br />
OR (<br />
J.nazvanie = LOWER('Штуцер 01-03')<br />
AND<br />
SPJ.nomer_detali != P.nomer_detali<br />
)<br />
);<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
nazvanie<br />
------------------<br />
штуцерная деталь<br />
(1 row)<br />
<br />
=== Билет 30 ===<br />
Написать запрос SELECT: выдать имена поставщиков, которые поставляют, по крайней мере, все те детали, которые поставляет поставщик с номером 'S2'.<br />
<br />
Текст запроса:<br />
<syntaxhighlight lang="sql"><br />
SELECT DISTINCT imya<br />
FROM spasoi_ekz.s S<br />
WHERE NOT EXISTS (<br />
SELECT nomer_detali<br />
FROM spasoi_ekz.spj SPJY<br />
WHERE nomer_postavshika = 'S2'<br />
AND NOT EXISTS (<br />
SELECT *<br />
FROM spasoi_ekz.spj<br />
WHERE nomer_postavshika = S.nomer_postavshika<br />
AND nomer_detali = SPJY.nomer_detali<br />
)<br />
);<br />
</syntaxhighlight><br />
<br />
Результат:<br />
<br />
imya<br />
------------<br />
Оша<br />
Бран Старк<br />
(2 rows)<br />
[[Категория:Структурное проектирование АСОИ (10 семестр)]]</div>
37.190.38.24