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

Операционные системы (5 семестр)

2015-12-18T09:23:12Z

195.19.36.135:

{| class="wikitable"
|-
! Год !! Описание !! Ссылка
|- align="center"
| 2010 || Лекции || [http://yadi.sk/d/9S3UI6BW8Z4LS загрузить]
|}

[[Категория:5 семестр]]

Теория вероятностей и математическая статистика (3 семестр)

2014-10-02T18:24:33Z

195.19.36.135: учебный план

{| class="wikitable"
|-
! Год !! Описание !! Ссылка
|- align="center"
| 2010 || Задачи || [http://yadi.sk/d/iIBbGJlK8Z4q0 загрузить]
|- align="center"
| 2010 || Лекции || [http://yadi.sk/d/w1irW3Fx8Z4mk загрузить]
|- align="center"
| 2010 || К экзамену || [http://yadi.sk/d/-4aVBmMx8Z4oW загрузить]
|}

[[Категория:3 семестр]]

Электрический конспект

2014-06-04T18:06:35Z

195.19.36.135: /* Где набирать */ добавлены проги под мак

Это конспект,  набранный на ноутбуке, планшете (телефоне). От [[Рукописный конспект | рукописного конспекта]] отличается тем,  что с ним гораздо удобнее работать. Гораздо. Электрические конспекты - это очень хорошо. Переходите на них.

Набрал лекцию - спас дерево.

== Ведение ==

* набирать текст на клавиатуре гораздо быстрее, чем писать от руки в тетради;
* конспект получается аккуратным - формулы, таблицы и рисунки радуют глаз;
* легко вносить изменения, исправлять ошибки;
* мгновенно можно создать сколько угодно копий;
* на диске занимает гораздо меньше места, чем фотография [[Рукописный конспект | рукописного конспекта]];
* в реальном мире не занимает места вообще;
* возможность автоматической проверки на орфографию и пунктуацию;
* в конце семестра можно подогнать его на диске преподавателю и получить ништяки.

== Пользование ==

* не нужно разбирать чужой почерк;
* очень просто осуществлять поиск нужной информации;
* низкий расход краски в принтере при печати;
* легко превращается в шпаргалки, как в печатном виде, так и на экране мобильного устройства;
* стильно, модно, молодёжно.

== Однако ==
* преподаватель может допускать к экзамену / зачёту только при наличии [[Рукописный конспект | рукописного конспекта]];
* для работы ноутбука или планшета нужно электричество;
* ноутбук или планшет стоит гораздо дороже тетрадей и ручек-карандашей.

== Где набирать ==

{| class="wikitable"
! Операционная система !! ПО !! Что !! Достоинства !! Недостатки
|- align="center"
| rowspan="4" | '''Microsoft Windows''' || Блокнот || текст || очень простой || ничего не умеет
|- align="center"
| Word || текст <br> формулы <br> таблицы <br> простые рисунки || удобный <br> простой || -
|- align="center"
| Visio || схемы <br> рисунки || векторный || -
|- align="center"
| WinEdt + MiKTeX || текст <br> формулы <br> таблицы <br> || может всё <br> аккуратный <br> логичный <br> простой || сложно научиться
|- align="center"
| rowspan="5" | '''Linux''' || Vim/Emacs/Kate/Gedit/SciTe || текст || очень простой || мало что умеет
|- align="center"
| То же самое + LaTeX ИЛИ Kile/TexMaker || текст <br> формулы <br> таблицы <br> || может всё <br> аккуратный <br> логичный <br> простой || сложно научиться
|- align="center"
| LyX || текст <br> формулы <br> таблицы <br> || Тот же LaTeX, но WYSIWYG || -
|- align="center"
| OpenOffice/LibreOffice Writer || текст <br> формулы <br> таблицы <br> простые рисунки || удобный <br> простой || -
|- align="center"
| Dia/Inkscape || схемы <br> рисунки || векторный || Библиотеки Dia хуже, чем в Visio <br> В Inkscape сложно сделать привязку стрелки к элементу
|- align="center"
| rowspan="3" | '''Mac OS''' || MS Office || Текст, таблицы || Привычно || дорого или незаконно
|- align="center"
| OmniGraffle || картинки || Поход на Visio || Все же не совсем похож <br> нужно привыкать <br> косяки со вставкой формул
|- align="center"
| macTex + texmaker || текст <br> формулы <br> таблицы <br> || см выше || см выше
|- align="center"
| '''Android''' || ? || ? || ? || ?
|- align="center"
| '''?''' || ? || ? || ? || ?
|}

== Примеры ==

<gallery>
Файл:Elkonsp1.png
Файл:Elkonsp2.png
Файл:Elkonsp3.png
</gallery>

[[Категория:Конспекты]]

Категория:Технологии программирования (10 семестр)

2013-06-18T17:52:03Z

195.19.36.135: /* Готовые лабораторные работы */

<p>{{Предмет | name=Технологии программирования | prepod=[[Тарнакин А.О.]] | lections=? | seminars=нет | labs=3 | dzs=? | rks=? | signif=по желанию | what=экзамен}}</p>

Технологии программирования. А конкретно - [http://ru.wikipedia.org/wiki/Java Java].
__TOC__
== Лекции ==

За явкой следить не будут. Вики-конспектов не будет, потому что лекции даются не для конспектирования. Весь материал [[#Литература | тут]].

== Лабораторные работы ==

Всего 3 лабораторных.

Выполняются на языке Java. ПО для разработки можно загрузить [http://www.oracle.com/technetwork/java/javase/downloads/index.html здесь]. Необходимы <code>JRE</code> и <code>JDK</code>. В качестве <code>IDE</code> вполне хватит <code>NetBeans</code>. Сервером приложений лучше выбрать <code>Apache Tomcat</code>, а не <code>GlassFish</code>.

Как можно заметить, описания работ даны в очень общем виде, без подробных требований. По этому поводу прямая речь от преподавателя: <blockquote>''Задания умышленно не детализирую, мне интересно посмотреть на ваше творчество.''</blockquote>

Сдача лабораторных (и курсовой) происходит по принципу "чем раньше - тем проще". В начале вообще по коду ничего отвечать не надо и нет дополнительных заданий. Но начиная где-то с мая сдать станет ощутимо сложнее.

=== Лабораторная работа №1 ===

Работа с базовыми классами стандартных пакетов <code>java.io</code>, <code>java.net</code>, <code>java.util</code>.

Задание:
* необходимо получить содержимое web-странички, проиндексировать слова в нём (в содержимом) и результаты сохранить в файл;
* программа должна быть исполнена в виде консольного приложения, позволяющего выбирать адрес web-страницы и имя файла, в который будут сохранены результаты;
* программу необходимо компилировать и запускать без помощи IDE (но сам код можно писать и в IDE).

Ссылки по теме:
* [http://en.wikibooks.org/wiki/Java_Programming/Understanding_a_Java_Program как устроена Java-программа];
* [http://www.skipy.ru/technics/likbez.html#hello компиляция и запуск из консоли Windows];
* [http://docs.oracle.com/javase/tutorial/essential/io/ основы ввода/вывода];
* [http://docs.oracle.com/javase/6/docs/api/java/net/package-summary.html#package_description работа с сетью];
* [http://docs.oracle.com/javase/tutorial/collections/index.html контейнерные классы].

=== Лабораторная работа №2 ===

Выполняется на базе ЛР №1.

Работа с БД, многопоточность, синхронизация, журналирование.

Задание:
* программа должна быть выполнена как многопоточное приложение, использующее <code>java.util.concurrent</code> и позволяющее ограничивать загрузку процессора;
* индексация каждой странички должна производиться в отдельном потоке, содержимое сохранять в БД тройками вида '''<code>слово - частота упоминания на странице - страница</code>'''. Найденные на страничке ссылки ('''<code><nowiki>href="http://..."</nowiki></code>''') должны обрабатываться отличным от текущего потоком;
* программа должна выполнять поиск по словам и выводить адреса проиндексированных ранее страничек в порядке, пропорциональном частоте появления слова на страничке;
* программа должна писать отладочные сообщения посредством стандартных механизмов [http://ru.wikipedia.org/wiki/Slf4J SLF4J].

Ссылки по теме:
* драйвера для работы с БД:
** [http://jdbc.postgresql.org/ PostgreSQL];
** [http://www.mysql.com/products/connector/ MySQL];
** [http://hsqldb.org/ HyperSQL];
* [http://en.wikipedia.org/wiki/Java_Database_Connectivity работа с БД];
* [http://docs.oracle.com/javase/tutorial/essential/concurrency/index.html многопоточность];
** [http://docs.oracle.com/javase/tutorial/essential/concurrency/highlevel.html синхронизация потоков] (чтобы не писать свой велосипед);
* [http://www.rsdn.ru/forum/java/3622844.1 вопросы для самоконтроля].

=== Лабораторная работа №3 ===

Выполняется на базе ЛР №2.

Создание web-сервисов.

Задание:
* программа должна быть выполнена в виде web-приложения, индексирующего в фоновом режиме содержимое страничек, и обрабатывать поисковые запросы посредством [http://ru.wikipedia.org/wiki/REST REST] web-сервиса.

Ссылки по теме:
* [http://habrahabr.ru/post/38730/ архитектура REST];
* [http://www.devx.com/Java/Article/42873 RESTful веб-сервисы и Java];
* [http://docs.jboss.org/resteasy/docs/1.1.GA/userguide/html_single/index.html руководство по работе с RESTful веб-сервисами на Java] - перечислены все методы и их параметры;
* [http://javadev.ru/Eclipse_Web_Tools_Platform/ пример создания веб-приложения с сервлетом] - очень помогает разобраться, только не надо делать оттуда всё подряд. Всё необходимое находится в итерациях 2 и 3, где про сервлет и класс <code>Database</code>;
* [http://jersey.java.net/use/getting-started.html Jersey] и [http://jersey.java.net/nonav/documentation/latest/user-guide.html документация по Jersey];
* [http://www.blogsoncloud.com/jsp/blogs/restful-web-service-using-jersey.jsp пример создания веб-сервиса с Jersey] - используется <code>Eclipse</code>;
* [http://www.vogella.com/articles/REST/article.html и ещё один пример веб-службы на Jersey] - также используется <code>Eclipse</code>;
* [http://vichargrave.com/restful-web-service-development-with-netbeans-and-tomcat-tutorial/ ещё один пример с Jersey] - используется <code>NetBeans</code> и <code>Tomcat</code>;
* [http://forum.vingrad.ru/forum/topic-242617/kw-restful-jax--maven-jersey.html веб-сервис на Maven].

Если решите делать на [http://ru.wikipedia.org/wiki/SOAP SOAP] - передумайте обратно. [[Тарнакин А.О. | Тарнакин]] и все правильные чуваки не одобряют.

=== Готовые лабораторные работы ===

{| class="wikitable"
! Год !! Вариант исполнения !! Работа !! Описание !! Ссылка
|- align="center"
| 2013 || 1 || ЛР №1 || сбор только русских слов через RegExp<br>Notepad++, командная строка || [http://yadi.sk/d/14F6O7jx2vpNM загрузить]
|- align="center"
| 2013 || 1 || ЛР №2 || СУБД PostgreSQL (схема БД есть), настройки из config.ini<br>NetBeans || [http://yadi.sk/d/GbiQltGJ4CGK5 загрузить]
|- align="center"
| 2013 || 1 || ЛР №3 || СУБД PostgreSQL (схема БД из ЛР №2)<br>NetBeans, Apache Tomcat || [http://yadi.sk/d/HQi0_xfK3nKxa загрузить]
|- align="center"
| 2013 || 2 || все || Описание доступно по ссылке || [https://github.com/zhukovRoman/Java github]
|- align="center"
| 2013 || 3 || все || СУБД PostgreSQL (схема БД прилагается)<br>NetBeans, GlassFish || [http://yadi.sk/d/qAFbbk2M3plxW загрузить]
|}

== Курсовая работа ==

Страница курсовой работы находится [[Курсовая по ТП (10 семестр) | здесь]].

== Экзамен ==

В билете два вопроса.

* [http://yadi.sk/d/ciqV8cTk5Dpci вопросы к экзамену]
* [http://yadi.sk/d/GQnCmFq95nemq ответы на вопросы]

== Литература ==

* Хорстман, Корнел - "Java 2";
* [http://en.wikibooks.org/wiki/Subject:Java_programming_language Википедия].

[[Категория:10 семестр]]
[[Категория:Предметы]]

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

2013-06-16T11:53:41Z

195.19.36.135: /* Билет 21 */

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

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

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

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

== Схема БД ==

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

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

Результат:

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

Результат:

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

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

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

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

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

<div class="toccolours mw-collapsible mw-collapsed">
''Вариант подлиннее для тех, кто не ищет лёгких путей''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT gorod, SUM(sostoyanie)
FROM spasoi_ekz.s
WHERE nomer_postavshika IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
GROUP BY nomer_postavshika HAVING MIN(kolichestvo) > 1000
)
GROUP BY gorod;
</syntaxhighlight>
</div>
</div>


Результат:

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

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

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

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

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

Результат:

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

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

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

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

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

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

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

Результат:

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

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

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

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

<syntaxhighlight lang="sql">

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

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj A JOIN spasoi_ekz.s S
ON A.nomer_postavshika = S.nomer_postavshika
WHERE nomer_izdelia = 'J1'
AND NOT EXISTS (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
WHERE nomer_izdelia != 'J1'
AND nomer_postavshika = A.nomer_postavshika
);
</syntaxhighlight>

Результат:

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

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

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

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

<syntaxhighlight lang="sql">
SELECT DISTINCT J.nazvanie
FROM spasoi_ekz.j J
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj X JOIN spasoi_ekz.s S
ON X.nomer_postavshika = S.nomer_postavshika
WHERE sostoyanie <= 1000000
AND X.nomer_izdelia = J.nomer_izdelia
);
</syntaxhighlight>

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

Результат:

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

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

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

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

Результат:

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

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

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

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

Результат:

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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


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

Результат:

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

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

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

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

Результат:

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

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

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


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

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

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

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

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

Результат:

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

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

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

</syntaxhighlight>

Результат:

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

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

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

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

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

Результат:

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

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

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

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

Результат:

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

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

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

Результат:

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

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

2013-06-16T11:53:19Z

195.19.36.135: /* Билет 21 */ Номер изделия хранится в SPJ так же как и в J

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

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

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

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

== Схема БД ==

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

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

Результат:

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

Результат:

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

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

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

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

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

<div class="toccolours mw-collapsible mw-collapsed">
''Вариант подлиннее для тех, кто не ищет лёгких путей''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT gorod, SUM(sostoyanie)
FROM spasoi_ekz.s
WHERE nomer_postavshika IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
GROUP BY nomer_postavshika HAVING MIN(kolichestvo) > 1000
)
GROUP BY gorod;
</syntaxhighlight>
</div>
</div>


Результат:

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

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

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

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

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

Результат:

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

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

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

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

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

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

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

Результат:

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

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

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

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

<syntaxhighlight lang="sql">

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

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj A JOIN spasoi_ekz.s S
ON A.nomer_postavshika = S.nomer_postavshika
WHERE nomer_izdelia = 'J1'
AND NOT EXISTS (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
WHERE nomer_izdelia != 'J1'
AND nomer_postavshika = A.nomer_postavshika
);
</syntaxhighlight>

Результат:

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

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

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

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

<syntaxhighlight lang="sql">
SELECT DISTINCT J.nazvanie
FROM spasoi_ekz.j J
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj X JOIN spasoi_ekz.s S
ON X.nomer_postavshika = S.nomer_postavshika
WHERE sostoyanie <= 1000000
AND X.nomer_izdelia = J.nomer_izdelia
);
</syntaxhighlight>

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

Результат:

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

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

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

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

Результат:

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

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

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

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

Результат:

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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


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

Результат:

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

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

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

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

Результат:

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

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

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


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

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

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

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

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

Результат:

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

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

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

</syntaxhighlight>

Результат:

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

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

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

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

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

Результат:

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

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

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

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

Результат:

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

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

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

Результат:

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

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

2013-06-16T10:15:27Z

195.19.36.135: /* Билет 5 */ WHERE SPJ.nomer_detali - не нужен был SPJ

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

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

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

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

== Схема БД ==

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

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

Результат:

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

Результат:

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

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

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

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

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

<div class="toccolours mw-collapsible mw-collapsed">
''Вариант подлиннее для тех, кто не ищет лёгких путей''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT gorod, SUM(sostoyanie)
FROM spasoi_ekz.s
WHERE nomer_postavshika IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
GROUP BY nomer_postavshika HAVING MIN(kolichestvo) > 1000
)
GROUP BY gorod;
</syntaxhighlight>
</div>
</div>


Результат:

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

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

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

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

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

Результат:

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

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

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

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

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

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

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

Результат:

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

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

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

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

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj A JOIN spasoi_ekz.s S
ON A.nomer_postavshika = S.nomer_postavshika
WHERE nomer_izdelia = 'J1'
AND NOT EXISTS (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
WHERE nomer_izdelia != 'J1'
AND nomer_postavshika = A.nomer_postavshika
);
</syntaxhighlight>

Результат:

imya
----------------
Томми Версетти
(1 row)

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

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

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

<syntaxhighlight lang="sql">
SELECT DISTINCT j.nazvanie
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj X JOIN spasoi_ekz.s S
ON X.nomer_postavshika = S.nomer_postavshika
WHERE sostoyanie <= 1000000
AND X.nomer_izdelia = SPJ.nomer_izdelia
);
</syntaxhighlight>

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

Результат:

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

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

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

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

Результат:

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

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

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

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

Результат:

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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


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

Результат:

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

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

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

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

Результат:

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

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

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


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

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

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

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

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

Результат:

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

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

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

</syntaxhighlight>

Результат:

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

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

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

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

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

Результат:

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

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

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

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

Результат:

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

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

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

Результат:

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

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

2013-06-12T17:51:22Z

195.19.36.135: /* Билет 28 */

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

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

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

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

== Схема БД ==

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

Результат:

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

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

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

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

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


Результат:

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

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

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

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

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

Результат:

nomer_detali
--------------
P15
P16
(2 rows)

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

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

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

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

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

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

Результат:

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

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

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

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

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE nomer_izdelia = 'J1'
AND SPJ.nomer_postavshika NOT IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
WHERE nomer_izdelia != 'J1'
);
</syntaxhighlight>

Результат:

imya
----------------
Томми Версетти
(1 row)

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

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

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

<syntaxhighlight lang="sql">
SELECT DISTINCT nazvanie
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE sostoyanie > 1000000
AND SPJ.nomer_izdelia NOT IN (
SELECT nomer_izdelia
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE sostoyanie < 1000000
);
</syntaxhighlight>

Результат:

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

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

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

Результат:

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

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

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

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

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

Результат:

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

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

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

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

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

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

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

Результат:

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

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

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

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

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

</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё один вариант запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nazvanie
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE nomer_postavshika = 'S1'
AND SPJ.nomer_izdelia NOT IN (
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_postavshika != 'S1'
);
</syntaxhighlight>
</div>
</div>

Результат:

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

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

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

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


<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('штуцер 01-02')
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND SPJ.nomer_detali = 'P1'
JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE SPJ.nomer_postavshika NOT IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('штуцер 01-02')
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND SPJ.nomer_detali != 'P1'
);
</syntaxhighlight>

Результат:

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

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

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

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

Результат:

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

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

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

С этим запросом возникла неожиданная проблема - задание то ли неполное, то ли неправильное, потому что нельзя однозначно сказать, что по нему требуется сделать.

Так что тут несколько вариантов запросов (кто как понял).

<div class="toccolours mw-collapsible mw-collapsed">
''Первый вариант запроса''
<div class="mw-collapsible-content">
Текст запроса:

<syntaxhighlight lang="sql">
SELECT nazvanie, kol
FROM spasoi_ekz.p A, (
SELECT X.gorod, SUM(kolichestvo) as kol
FROM spasoi_ekz.p X, spasoi_ekz.spj Y
WHERE Y.nomer_detali = X.nomer_detali
GROUP BY X.gorod
) B
WHERE A.gorod = B.gorod;
</syntaxhighlight>

Результат:

nazvanie | kol
----------------------+------
подставка | 9
стойка | 9
абажур | 9
гайка | 139
ось | 60
зубчатое колесо | 60
транзистор | 50
печатная плата | 50
диод | 50
универсальная деталь | 13
уникальная деталь | 14
болт | 9137
рама | 69
колесо | 69
втулка | 69
бумага | 3122
плитка | 14
орнамент | 14
уголок | 14
гайка 01-01 | 27
усиленная рама | 10
винт | 9137
труселя | 1
штуцерная деталь | 10
шуруп | 139
(25 rows)
</div>
</div>
и
<div class="toccolours mw-collapsible mw-collapsed">
''Второй вариант запроса''
<div class="mw-collapsible-content">
В уточнение задания Григорьев сказал следующее: "''Следует учесть, что в качестве наименования города может выступать переменная, в которую в некоторой программе должно быть занесено конкретное значение перед выполнением запроса''".

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

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

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

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

Результат:

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

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

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

</syntaxhighlight>

Результат:

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

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

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

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

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

Результат:

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

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

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

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

Результат:

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

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

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

Результат:

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

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

2013-06-12T13:22:36Z

195.19.36.135: /* Билет 20 */

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

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

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

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

== Схема БД ==

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

<syntaxhighlight lang="sql">
SELECT X.nomer_postavshika
FROM spasoi_ekz.spj X
WHERE X.nomer_detali = 'P1'
AND X.kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_izdelia = X.nomer_izdelia
AND nomer_detali = 'P2'
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос, но длиннее и нерациональней''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT DISTINCT nomer_postavshika
FROM (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_izdelia IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
)
AND nomer_izdelia IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P2'
)
) A
WHERE nomer_detali = 'P1' AND kolichestvo >
(
SELECT AVG(kolichestvo)
FROM (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_izdelia IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
)
AND nomer_izdelia IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P2'
)
) B
WHERE nomer_detali = 'P2'
);
</syntaxhighlight>
</div>
</div>

Результат:

nomer_postavshika
-------------------
S6
(1 row)

=== Билет 4 ===

Написать запрос SELECT: выдать номера изделий, для которых детали поставляет только поставщик с номером ‘S1’.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT DISTINCT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_izdelia IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_postavshika = 'S1'
)
AND nomer_izdelia NOT IN(
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_postavshika != 'S1'
);
</syntaxhighlight>

Результат:

nomer_izdelia
---------------
J5
J16
(2 rows)

=== Билет 5 ===

Написать запрос SELECT: выдать имена поставщиков, поставляющих детали с названием "Болт" в количестве (в поставке) большим, чем средний объём всех поставок деталей с номером 'P1'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND nazvanie = LOWER('Болт')
WHERE kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND SPJ.nomer_detali = 'P1'
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос, но без JOIN''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s
WHERE nomer_postavshika IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
WHERE nomer_detali = (
SELECT nomer_detali
FROM spasoi_ekz.p
WHERE LOWER(nazvanie) = LOWER('Болт')
)
AND kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
)
);
</syntaxhighlight>
</div>
</div>

Результат:

imya
------
Оша
Иванов
(2 rows)

=== Билет 6 ===

Написать запрос SELECT: выдать названия деталей, поставляемых поставщиком, проживающим в том же городе, где изготавливаются эти детали, для изделия с названием ‘Велосипед 01/23’.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT DISTINCT nazvanie
FROM spasoi_ekz.S S, spasoi_ekz.p P, spasoi_ekz.spj SPJ
WHERE S.gorod = P.gorod
AND P.nomer_detali = SPJ.nomer_detali
AND S.nomer_postavshika = SPJ.nomer_postavshika
AND nomer_izdelia = (
SELECT nomer_izdelia
FROM spasoi_ekz.j
WHERE nazvanie = LOWER('Велосипед 01/23')
);
</syntaxhighlight>

Результат:

nazvanie
----------
рама
колесо
(2 rows)

=== Билет 7 ===

Написать запрос SELECT: выдать названия изделий, куда входят детали с названием 'Болт', поставляемых поставщиками с именем 'Иванов', в количестве (в поставке) большим, чем средний объём поставок для этого изделия.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nazvanie
FROM spasoi_ekz.j
WHERE nomer_izdelia IN (
SELECT nomer_izdelia
FROM (spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
AND imya = 'Иванов'
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND nazvanie = LOWER('Болт')) A
WHERE kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = A.nomer_izdelia
)
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос, но длиннее и нерациональней''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nazvanie
FROM (
SELECT J.nazvanie, kolichestvo
FROM spasoi_ekz.s S, spasoi_ekz.p P, spasoi_ekz.j J, spasoi_ekz.spj SPJ
WHERE J.nomer_izdelia = SPJ.nomer_izdelia
AND P.nomer_detali = SPJ.nomer_detali
AND P.nazvanie = LOWER('Болт')
AND S.imya = 'Иванов'
AND S.nomer_postavshika = SPJ.nomer_postavshika
) A
WHERE kolichestvo >
(
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_izdelia IN(
SELECT J.nomer_izdelia
FROM spasoi_ekz.s S, spasoi_ekz.p P, spasoi_ekz.j J, spasoi_ekz.spj SPJ
WHERE J.nomer_izdelia = SPJ.nomer_izdelia
AND P.nomer_detali = SPJ.nomer_detali
AND P.nazvanie = LOWER('Болт')
AND S.imya = 'Иванов'
AND S.nomer_postavshika = SPJ.nomer_postavshika
)
);
</syntaxhighlight>
</div>
</div>

Результат:

nazvanie
-------------------
шкаф
(1 row)

=== Билет 8 ===

Написать запрос SELECT: выдать номера изделий и общее количество деталей для них, поставляемых поставщиками с именем 'Петров'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nomer_izdelia AS "Номер изделия", SUM(kolichestvo) AS "Количество деталей"
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
-- так как "поставщикАМИ" и возможны
-- Иван Петров и Петров Иван, то LIKE %Петров%
AND imya LIKE '%Петров%'
GROUP BY nomer_izdelia;
</syntaxhighlight>

Результат:

Номер изделия | Количество деталей
---------------+--------------------
J4 | 101
J3 | 6
J13 | 9
J15 | 40
J1 | 3
J12 | 15
J14 | 93
(7 rows)

=== Билет 9 ===

Написать запрос SELECT: выдать номера деталей и общее их количество для всех номеров деталей, которые входят только в одно изделие.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nomer_detali, SUM(kolichestvo)
FROM spasoi_ekz.spj
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_izdelia) = 1;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё вариант''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nomer_detali, kol FROM (
SELECT nomer_detali, SUM(kolichestvo) AS kol, COUNT(DISTINCT nomer_izdelia) = 1
FROM spasoi_ekz.spj
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_izdelia) = 1
) A;
</syntaxhighlight>
</div>
</div>

Результат:

nomer_detali | sum
--------------+------
P10 | 3
P11 | 5
P12 | 13
P13 | 14
P17 | 3122
P18 | 9
P19 | 1
P20 | 4
P21 | 27
P22 | 9
P23 | 101
P24 | 1
P25 | 1
P3 | 50
P4 | 10
P6 | 20
P7 | 5
P8 | 25
P9 | 1
(19 rows)

=== Билет 10 ===

Написать запрос SELECT: выдать имена поставщиков, поставляющих детали с названием 'Болт' для изделия с названием 'Рама 02-01' в количестве (в поставке) большим, чем минимальное значение поставки детали с номером 'P1'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Рама 02-01')
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND P.nazvanie = LOWER('Болт')
JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE kolichestvo > (
SELECT MIN(kolichestvo)
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND P.nomer_detali = 'P1'
);
</syntaxhighlight>

Результат:

imya
-------------
Русе Болтон
(1 row)

=== Билет 11 ===

Написать запрос SELECT: выдать названия городов и суммарное состояние проживающих в каждом городе поставщиков, у которых минимальный объём поставки деталей больше 1000.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT gorod, SUM(sostoyanie)
FROM spasoi_ekz.s
WHERE nomer_postavshika IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
GROUP BY nomer_postavshika HAVING MIN(kolichestvo) > 1000
)
GROUP BY gorod;
</syntaxhighlight>


Результат:

gorod | sum
-----------+---------
Прага | 2111110
Стокгольм | 888888
(2 rows)

=== Билет 12 ===

Написать запрос SELECT: выдать номера деталей, поставляемых для какого-либо изделия поставщиком, проживающим в том же городе, где изготавливается это изделие.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nomer_detali
FROM spasoi_ekz.spj SPJ, spasoi_ekz.s S, spasoi_ekz.j J
WHERE SPJ.nomer_postavshika = S.nomer_postavshika
AND S.gorod = J.gorod
AND J.nomer_izdelia = SPJ.nomer_izdelia;
</syntaxhighlight>

Результат:

nomer_detali
--------------
P15
P16
(2 rows)

=== Билет 13 ===

Написать <u>один</u> запрос SELECT: выдать количества строк в таблице S (Поставщик) 1) с пустыми значениями и 2) непустыми значениями в столбце Состояние.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT COUNT(nomer_postavshika) - COUNT(sostoyanie) AS "С пустым состоянием",
COUNT(sostoyanie) AS "С не пустым состоянием"
FROM spasoi_ekz.s;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос, но длиннее и нерациональней''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">

SELECT COUNT(Snull.*) AS "С пустым состоянием", COUNT(Snotnull.sostoyanie) AS "С не пустым состоянием"
FROM spasoi_ekz.s Snull RIGHT OUTER JOIN spasoi_ekz.s Snotnull
ON Snull.nomer_postavshika = Snotnull.nomer_postavshika
AND Snull.sostoyanie IS NULL;
</syntaxhighlight>
</div>
</div>

Результат:

С пустым состоянием | С не пустым состоянием
---------------------+-----------------------
2 | 15
(1 row)

=== Билет 14 ===

Написать запрос SELECT: выдать имена поставщиков, которые поставляют детали только и только для изделия с номером 'J1'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE nomer_izdelia = 'J1'
AND SPJ.nomer_postavshika NOT IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
WHERE nomer_izdelia != 'J1'
);
</syntaxhighlight>

Результат:

imya
----------------
Томми Версетти
(1 row)

=== Билет 15 ===

Написать запрос SELECT: выдать наименования изделий, для которых детали поставляют только те поставщики, у которых состояние больше 1000000 у.е.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT DISTINCT nazvanie
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE sostoyanie > 1000000
AND SPJ.nomer_izdelia NOT IN (
SELECT nomer_izdelia
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE sostoyanie < 1000000
);
</syntaxhighlight>

Результат:

nazvanie
--------------------
кружевное бельё
уникальное изделие
процессор
(3 rows)

=== Билет 16 ===
Написать запрос SELECT: выдать цвета и для каждого цвета общее количество деталей, входящих в изделие с названием 'Панно 01-03'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT cvet AS "Цвет", SUM(kolichestvo) AS "Деталей"
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Панно 01-03')
JOIN spasoi_ekz.P P
ON SPJ.nomer_detali = P.nomer_detali
GROUP BY cvet;
</syntaxhighlight>

Результат:

Цвет | Деталей
-------+---------
белый | 9
серый | 5
(2 rows)

=== Билет 17 ===

Написать запрос SELECT: выдать номера поставщиков и количество сделанных ими поставок при условии, что среднее число деталей во всех этих поставках больше 1000.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nom AS "Номер поставщика", cnt AS "Количество поставок"
FROM (
SELECT S.nomer_postavshika AS nom,
COUNT(SPJ.nomer_postavshika) AS cnt,
AVG(SPJ.kolichestvo) AS kol
FROM spasoi_ekz.s S, spasoi_ekz.spj SPJ
WHERE S.nomer_postavshika = SPJ.nomer_postavshika
GROUP BY S.nomer_postavshika
) A
WHERE kol > 1000;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос немного попроще''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nomer_postavshika AS "Номер поставщика", COUNT(*) AS "Количество поставок"
FROM spasoi_ekz.spj
WHERE nomer_postavshika IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
GROUP BY nomer_postavshika HAVING AVG(kolichestvo) > 1000
)
GROUP BY nomer_postavshika;
</syntaxhighlight>
</div>
</div>

Результат:

Номер поставщика | Количество поставок
------------------+---------------------
S13 | 1
S6 | 7
S14 | 1
S15 | 1
(4 rows)

=== Билет 18 ===

Написать запрос SELECT: выдать имена поставщиков, имеющих состояние больше 1000 и поставляющих деталь с названием 'Гайка 01-01' для изделия с названием 'Велосипед 03-04' в количестве (в поставке) большим, чем средний объём поставки, выполненной поставщиками с именем 'Иванов'.

[[Файл:2nd dufficulty.png|center]]

<p align="center"><font size="5px">'''Второй по сложности запрос экзамена!'''</font></p>

<p align="center"><font size="4px">'''Вот уж не свезло, так не свезло'''</font></p>

Текст запроса:

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
JOIN spasoi_ekz.j J
ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE sostoyanie > 1000
AND P.nazvanie = LOWER('Гайка 01-01')
AND J.nazvanie = LOWER('Велосипед 03-04')
AND kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE S.imya = 'Иванов'
);
</syntaxhighlight>

Результат:

imya
-------------
Петров Пётр
(1 row)

=== Билет 19 ===

Написать запрос SELECT: выдать названия красных деталей, которые входят только в изделие с названием 'Рама 02-03' в количестве, меньшем 10 единиц в поставке.

Текст запроса:
<syntaxhighlight lang="sql">
SELECT X.nazvanie
FROM spasoi_ekz.p X JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_detali = X.nomer_detali
JOIN spasoi_ekz.j J
ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE X.cvet = 'красный'
AND J.nazvanie = LOWER('Рама 02-03')
AND kolichestvo < 10
AND NOT EXISTS (
SELECT J.nomer_izdelia
FROM spasoi_ekz.j J JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE J.nazvanie != LOWER('Рама 02-03')
AND X.nomer_detali = SPJ.nomer_detali
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё вариант этого же запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT P.nazvanie
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND cvet = 'красный'
JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Рама 02-03')
WHERE kolichestvo < 10
AND SPJ.nomer_detali NOT IN (
SELECT nomer_detali
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie != LOWER('Рама 02-03')
);
</syntaxhighlight>
</div>
</div>

Результат:

nazvanie
----------------
усиленная рама
(1 row)

=== Билет 20 ===

Написать запрос SELECT: выдать имена поставщиков, поставляющих только белые детали.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
WHERE cvet = 'белый'
AND NOT EXISTS (
SELECT nomer_postavshika
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
WHERE nomer_postavshika = S.nomer_postavshika
AND P.cvet != 'белый'
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё один вариант этого же запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND cvet = 'белый'
JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE S.nomer_postavshika NOT IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND cvet != 'белый'
);
</syntaxhighlight>
</div>
</div>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё один вариант этого же запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT s.imya
From s
WHERE s.nomer_postavshika NOT IN
( SELECT spj.nomer_postavshika
From spj, p
WHERE spj.nomer_detali=p.nomer_detali
AND p.cvet!='Белый')
AND s.nomer_postavshika IN
( SELECT spj.nomer_postavshika
From spj, p
WHERE spj.nomer_detali=p.nomer_detali
AND p.cvet='Белый')
</syntaxhighlight>
</div>
</div>

Результат:

imya
-----------------
Рендилл Тарли
Сэмвелл Тарли
Мелисса Флорент
Томми Версетти
(4 rows)

=== Билет 21 ===

Написать запрос SELECT: выдать названия изделий, для которых детали поставляет только и только поставщик с номером 'S1'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nazvanie
FROM spasoi_ekz.j J, spasoi_ekz.SPJ SPJ
WHERE J.nomer_izdelia = SPJ.nomer_izdelia
AND NOT EXISTS (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
WHERE nomer_postavshika != 'S1'
AND nomer_izdelia = J.nomer_izdelia
);

</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё один вариант запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nazvanie
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE nomer_postavshika = 'S1'
AND SPJ.nomer_izdelia NOT IN (
SELECT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_postavshika != 'S1'
);
</syntaxhighlight>
</div>
</div>

Результат:

nazvanie
--------------------
уникальное изделие
кружевное бельё
(2 rows)

=== Билет 22 ===

Написать запрос SELECT: выдать имена поставщиков, которые поставляют только детали с номером 'P1' для изделия с именем 'Штуцер 01-02'.

Текст запроса:


<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('штуцер 01-02')
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND SPJ.nomer_detali = 'P1'
JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE SPJ.nomer_postavshika NOT IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('штуцер 01-02')
JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND SPJ.nomer_detali != 'P1'
);
</syntaxhighlight>

Результат:

imya
-------------
Петров Иван
(1 row)

=== Билет 23 ===

Написать запрос SELECT: выдать имена поставщиков, которые поставляют деталь с названием 'Винт' в количестве большим 100 единиц в одной поставке и имеют состояние больше среднего по их родному городу.

Текст запроса:
<syntaxhighlight lang="sql">
SELECT S.imya
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON S.nomer_postavshika = SPJ.nomer_postavshika
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
WHERE P.nazvanie = LOWER('Винт')
AND SPJ.kolichestvo > 100
AND S.sostoyanie > (
SELECT AVG(SS.sostoyanie)
FROM spasoi_ekz.s SS
WHERE SS.gorod = S.gorod
);
</syntaxhighlight>

Результат:

imya
-------------
Петров Пётр
(1 row)

=== Билет 24 ===

Написать запрос SELECT: выдать наименования деталей и общее их количество для всех наименований деталей, изготавливаемых в одном городе.

С этим запросом возникла неожиданная проблема - задание то ли неполное, то ли неправильное, потому что нельзя однозначно сказать, что по нему требуется сделать.

Так что тут несколько вариантов запросов (кто как понял).

<div class="toccolours mw-collapsible mw-collapsed">
''Первый вариант запроса''
<div class="mw-collapsible-content">
Текст запроса:

<syntaxhighlight lang="sql">
SELECT nazvanie, kol
FROM spasoi_ekz.p A, (
SELECT X.gorod, SUM(kolichestvo) as kol
FROM spasoi_ekz.p X, spasoi_ekz.spj Y
WHERE Y.nomer_detali = X.nomer_detali
GROUP BY X.gorod
) B
WHERE A.gorod = B.gorod;
</syntaxhighlight>

Результат:

nazvanie | kol
----------------------+------
подставка | 9
стойка | 9
абажур | 9
гайка | 139
ось | 60
зубчатое колесо | 60
транзистор | 50
печатная плата | 50
диод | 50
универсальная деталь | 13
уникальная деталь | 14
болт | 9137
рама | 69
колесо | 69
втулка | 69
бумага | 3122
плитка | 14
орнамент | 14
уголок | 14
гайка 01-01 | 27
усиленная рама | 10
винт | 9137
труселя | 1
штуцерная деталь | 10
шуруп | 139
(25 rows)
</div>
</div>
и
<div class="toccolours mw-collapsible mw-collapsed">
''Второй вариант запроса''
<div class="mw-collapsible-content">
В уточнение задания Григорьев сказал следующее: "''Следует учесть, что в качестве наименования города может выступать переменная, в которую в некоторой программе должно быть занесено конкретное значение перед выполнением запроса''".

То есть, вообще говоря, задание на запрос неполное, и его можно трактовать так: выдать наименования деталей и общее их количество для всех наименований деталей, изготавливаемых в городе <code>%НАЗВАНИЕГОРОДА%</code>. Вот эта переменная задаётся где-то в программе, а в БД идёт запрос с уже подставленным конкретным названием.

Этот вариант запроса составлен, например, для Лондона.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nazvanie, SUM(kolichestvo)
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p
ON SPJ.nomer_detali = P.nomer_detali
WHERE gorod = 'Лондон'
GROUP BY nazvanie;
</syntaxhighlight>

Результат:

nazvanie | sum
----------+-----
гайка | 71
шуруп | 68
(2 rows)
</div>
</div>

=== Билет 25 ===

Написать запрос SELECT: выдать имена поставщиков, поставляющих хотя бы одну белую деталь для изделия с названием 'Велосипед 01-04' с объёмом поставки большим, чем средний объём поставки этого поставщика.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
JOIN spasoi_ekz.j J
ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE cvet = 'белый'
AND J.nazvanie = LOWER('Велосипед 01-04')
AND kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj SPJ
WHERE SPJ.nomer_postavshika = S.nomer_postavshika
);
</syntaxhighlight>

Результат:

imya
-------------
Петров Пётр
(1 row)

=== Билет 26 ===

Написать запрос SELECT: выдать номера красных деталей и количество поставок этих деталей.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT P.nomer_detali AS "Номер детали", COUNT(kolichestvo) AS "Количество поставок с ней"
FROM spasoi_ekz.p P, spasoi_ekz.spj SPJ
WHERE P.nomer_detali = SPJ.nomer_detali
AND cvet = 'красный'
GROUP BY P.nomer_detali;
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос через JOIN''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT SPJ.nomer_detali AS "Номер детали", COUNT(kolichestvo) AS "Количество поставок с ней"
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON SPJ.nomer_detali = P.nomer_detali
AND cvet = 'красный'
GROUP BY SPJ.nomer_detali;
</syntaxhighlight>
</div>
</div>

Результат:

Номер детали | Количество поставок с ней
--------------+---------------------------
P15 | 3
P22 | 1
P24 | 1
(3 rows)

=== Билет 27 ===

Написать запрос SELECT: выдать наименования городов и среднее состояние поставщиков для каждого города, поставляющих детали с названием 'Гайка 01-01' для изделия с названием 'Велосипед 03-04' в количестве (в поставке) большим, чем минимальный объём поставки, выполненной поставщиком с номером 'S1'.

[[Файл:1st dufficulty.png|center]]

<p align="center"><font size="7px">'''Сложнейший запрос экзамена!'''</font></p>

<p align="center"><font size="5px">'''Сохрани Джа, вытащить такое'''</font></p>

Текст запроса:

<syntaxhighlight lang="sql">
SELECT S.gorod AS "Город", AVG(S.sostoyanie) AS "Среднее состояние"
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
JOIN spasoi_ekz.j J
ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE P.nazvanie = LOWER('Гайка 01-01')
AND J.nazvanie = LOWER('Велосипед 03-04')
AND kolichestvo > (
SELECT MIN(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_postavshika = 'S1'
)
GROUP BY S.gorod;

</syntaxhighlight>

Результат:

Город | Среднее состояние
-----------+-----------------------
Мытищи | 35000.500000000000
Йокогама | 1500000.000000000000
Манчестер | 2571.0000000000000000
(3 rows)

=== Билет 28 ===

Написать запрос SELECT: выдать названия изделий, куда входит хотя бы одна красная деталь весом больше 10 граммов, поставляемая только поставщиком с номером 'S1'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT J.nazvanie
FROM spasoi_ekz.j J JOIN spasoi_ekz.spj SPJ1
ON J.nomer_izdelia = SPJ1.nomer_izdelia
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ1.nomer_detali
WHERE P.ves > 10
AND P.cvet = 'красный'
AND NOT EXISTS (
SELECT SS.nomer_postavshika
FROM spasoi_ekz.s SS JOIN spasoi_ekz.spj SPJ2
ON SS.nomer_postavshika = SPJ2.nomer_postavshika
WHERE SPJ2.nomer_detali = P.nomer_detali
AND SS.nomer_postavshika != 'S1'
);
</syntaxhighlight>

Результат:

nazvanie
-----------------
кружевное бельё
(1 row)

=== Билет 29 ===
Написать запрос SELECT: выдать названия деталей, которые входят только и только в состав изделия с названием 'Штуцер 01-03'.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nazvanie
FROM spasoi_ekz.p P, spasoi_ekz.spj SPJ
WHERE P.nomer_detali = SPJ.nomer_detali
AND NOT EXISTS (
SELECT SPJ.nomer_izdelia
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE J.nazvanie != LOWER('Штуцер 01-03')
AND SPJ.nomer_detali = P.nomer_detali
);
</syntaxhighlight>

Результат:

nazvanie
------------------
штуцерная деталь
(1 row)

=== Билет 30 ===
Написать запрос SELECT: выдать имена поставщиков, которые поставляют, по крайней мере, все те детали, которые поставляет поставщик с номером 'S2'.

Текст запроса:
<syntaxhighlight lang="sql">
SELECT DISTINCT imya
FROM spasoi_ekz.s S
WHERE NOT EXISTS (
SELECT nomer_detali
FROM spasoi_ekz.spj SPJY
WHERE nomer_postavshika = 'S2'
AND NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_postavshika = S.nomer_postavshika
AND nomer_detali = SPJY.nomer_detali
)
);
</syntaxhighlight>

Результат:

imya
------------
Оша
Бран Старк
(2 rows)
[[Категория:Структурное проектирование АСОИ (10 семестр)]]

Курсовая по СПАСОИ (10 семестр)

2013-04-23T21:23:11Z

195.19.36.135:

Курсовая работа по [[:Категория:Структурное проектирование АСОИ (10 семестр) | Структурному проектированию АСОИ]].

== Описание ==

Имеется некая система, например процессинговый центр. Есть БД, транзакции, сервера, банкоматы. Надо промоделировать работу этой системы.

== ПО ==

Выполняется с помощью программного средства КСАМ (КИСП), которое писалось для работы в среде MS Windows 98. Поэтому работать с ней предстоит в виртуальной машине, образ диска (1 ГБ) которой выдадут.

Диск создавался для виртуальной машины [http://www.microsoft.com/en-us/download/details.aspx?id=4580 Microsoft Virtual PC 2007], но работает и в [https://www.virtualbox.org/wiki/Downloads Oracle VirtualBox].

Его также можно просто подмонтировать через <code>Управление дисками</code> в Windows 7 (ну и в других, где это возможно). Внутри можно найти дистрибутив этого КСАМ (который, по ходу, суть есть связка <code>Persоnal Oracle 7</code> и <code>Oracle Forms</code>) и дампы БД.

Запустить где-то кроме виртуальной машины не удалось. Для работы, например, в Windows 7 требует библиотеки:
* <code>msvcrt2x.dll</code>;
* <code>oc30.dll</code>;
* <code>mfcans32.dll</code>.

Все три можно взять из <code>\WINDOWS\SYSTEM</code> в виртуальной машине, тогда требовать перестаёт, но запускаться тоже не начинает, потому что нужна ещё база данных, которая работает с <code>Persоnal Oracle 7</code>, который, как раз, работает только в Windows 98. Можно, правда, попробовать перетащить это всё на новые версии и запустить-таки нормально, но до этого руки, по ходу, ни у кого не дойдут.

== Документы ==

* [http://yadi.sk/d/8T6YvrHI2a-1W методичка по курсовой и руководство по установке пакета];
* [http://yadi.sk/d/jRhi6Yue2a-2g документация по КСАМ (КИСП)];
* [http://yadi.sk/d/5tGPNX613i2e2 дополнительные методические материалы] (в том числе тесты TPC);
* [http://yadi.sk/d/eL6227982a-4I переписка Григорьева с каким-то крутым чуваком из Билайна].

== Графики ==
В курсовой работе в 5 разделе необходимо построить 2 графика. У меня не получилось заставить это сделать excel, а онлайн решения были немного убогими. Поэтому было решено заюзать JS-библиотеку highcharts. Библиотека позволяет сохранить график в одном из 4 форматов - для этого в правом верхнем углу нажмите на кнопку меню.

[http://jsbin.com/ixuhov/31 Для графика 1]

[http://jsbin.com/ixuhov/30 Для графика 2]

[[Категория:10 семестр]]
[[Категория:Курсовые]]

Категория:Технологии программирования (10 семестр)

2013-04-03T22:05:19Z

195.19.36.135: /* Лабораторные работы */

<p>{{Предмет | name=Технологии программирования | prepod=[[Тарнакин А.О.]] | lections=? | seminars=нет | labs=3 | dzs=? | rks=? | signif=по желанию | what=экзамен}}</p>

Технологии программирования. А конкретно - [http://ru.wikipedia.org/wiki/Java Java].
__TOC__
== Лекции ==

За явкой следить не будут. Вики-конспектов не будет, потому что лекции даются не для конспектирования. Весь материал [[#Литература | тут]].

== Лабораторные работы ==

Всего 3 лабораторных.

Выполняются на языке Java. ПО для разработки можно загрузить [http://www.oracle.com/technetwork/java/javase/downloads/index.html здесь]. Необходимы <code>JRE</code> и <code>JDK</code>. В качестве <code>IDE</code> вполне хватит <code>NetBeans</code>. Сервером приложений лучше выбрать <code>Apache Tomcat</code>, а не <code>GlassFish</code>.

Как можно заметить, описания работ даны в очень общем виде, без подробных требований. По этому поводу прямая речь от преподавателя: <blockquote>''Задания умышленно не детализирую, мне интересно посмотреть на ваше творчество.''</blockquote>

Сдача лабораторных (и курсовой) происходит по принципу "чем раньше - тем проще". В начале вообще по коду ничего отвечать не надо и нет дополнительных заданий. Но начиная где-то с мая сдать станет ощутимо сложнее.

[https://github.com/zhukovRoman/Java Альтернативные версии лабораторных для разнообразия]

=== Лабораторная работа №1 ===

Работа с базовыми классами стандартных пакетов <code>java.io</code>, <code>java.net</code>, <code>java.util</code>.

Задание:
* необходимо получить содержимое web-странички, проиндексировать слова в нём (в содержимом) и результаты сохранить в файл;
* программа должна быть исполнена в виде консольного приложения, позволяющего выбирать адрес web-страницы и имя файла, в который будут сохранены результаты;
* программу необходимо компилировать и запускать без помощи IDE (но сам код можно писать и в IDE).

Ссылки по теме:
* [http://en.wikibooks.org/wiki/Java_Programming/Understanding_a_Java_Program как устроена Java-программа];
* [http://www.skipy.ru/technics/likbez.html#hello компиляция и запуск из консоли Windows];
* [http://docs.oracle.com/javase/tutorial/essential/io/ основы ввода/вывода];
* [http://docs.oracle.com/javase/6/docs/api/java/net/package-summary.html#package_description работа с сетью];
* [http://docs.oracle.com/javase/tutorial/collections/index.html контейнерные классы].

{| class="wikitable"
|+ Готовые работы
! Год !! Описание !! Ссылка
|- align="center"
| 2013 || сбор только русских слов через RegExp<br>Notepad++, командная строка || [http://yadi.sk/d/14F6O7jx2vpNM загрузить]
|}

=== Лабораторная работа №2 ===

Выполняется на базе ЛР №1.

Работа с БД, многопоточность, синхронизация, журналирование.

Задание:
* программа должна быть выполнена как многопоточное приложение, использующее <code>java.util.concurrent</code> и позволяющее ограничивать загрузку процессора;
* индексация каждой странички должна производиться в отдельном потоке, содержимое сохранять в БД тройками вида '''<code>слово - частота упоминания на странице - страница</code>'''. Найденные на страничке ссылки ('''<code><nowiki>href="http://..."</nowiki></code>''') должны обрабатываться отличным от текущего потоком;
* программа должна выполнять поиск по словам и выводить адреса проиндексированных ранее страничек в порядке, пропорциональном частоте появления слова на страничке;
* программа должна писать отладочные сообщения посредством стандартных механизмов [http://ru.wikipedia.org/wiki/Slf4J SLF4J].

Ссылки по теме:
* драйвера для работы с БД:
** [http://jdbc.postgresql.org/ PostgreSQL];
** [http://www.mysql.com/products/connector/ MySQL];
** [http://hsqldb.org/ HyperSQL];
* [http://en.wikipedia.org/wiki/Java_Database_Connectivity работа с БД];
* [http://docs.oracle.com/javase/tutorial/essential/concurrency/index.html многопоточность];
** [http://docs.oracle.com/javase/tutorial/essential/concurrency/highlevel.html синхронизация потоков] (чтобы не писать свой велосипед);
* [http://www.rsdn.ru/forum/java/3622844.1 вопросы для самоконтроля].

{| class="wikitable"
|+ Готовые работы
! Год !! Описание !! Ссылка
|- align="center"
| 2013 || СУБД PostgreSQL (схема БД есть), настройки из config.ini<br>NetBeans || [http://yadi.sk/d/IdpS3C923i3UW загрузить]
|}

=== Лабораторная работа №3 ===

Выполняется на базе ЛР №2.

Создание web-сервисов.

Задание:
* программа должна быть выполнена в виде web-приложения, индексирующего в фоновом режиме содержимое страничек, и обрабатывать поисковые запросы посредством [http://ru.wikipedia.org/wiki/REST REST] web-сервиса.

Ссылки по теме:
* [http://habrahabr.ru/post/38730/ архитектура REST];
* [http://www.devx.com/Java/Article/42873 RESTful веб-сервисы и Java];
* [http://docs.jboss.org/resteasy/docs/1.1.GA/userguide/html_single/index.html руководство по работе с RESTful веб-сервисами на Java] - перечислены все методы и их параметры;
* [http://javadev.ru/Eclipse_Web_Tools_Platform/ пример создания веб-приложения с сервлетом] - очень помогает разобраться, только не надо делать оттуда всё подряд. Всё необходимое находится в итерациях 2 и 3, где про сервлет и класс <code>Database</code>;
* [http://jersey.java.net/use/getting-started.html Jersey] и [http://jersey.java.net/nonav/documentation/latest/user-guide.html документация по Jersey];
* [http://www.blogsoncloud.com/jsp/blogs/restful-web-service-using-jersey.jsp пример создания веб-сервиса с Jersey] - используется <code>Eclipse</code>;
* [http://www.vogella.com/articles/REST/article.html и ещё один пример веб-службы на Jersey] - также используется <code>Eclipse</code>;
* [http://vichargrave.com/restful-web-service-development-with-netbeans-and-tomcat-tutorial/ ещё один пример с Jersey] - используется <code>NetBeans</code> и <code>Tomcat</code>;
* [http://forum.vingrad.ru/forum/topic-242617/kw-restful-jax--maven-jersey.html веб-сервис на Maven].

Если решите делать на [http://ru.wikipedia.org/wiki/SOAP SOAP] - передумайте обратно. [[Тарнакин А.О. | Тарнакин]] и все правильные чуваки не одобряют.

{| class="wikitable"
|+ Готовые работы
! Год !! Описание !! Ссылка
|- align="center"
| 2013 || СУБД PostgreSQL (схема БД из ЛР №2)<br>NetBeans, Apache Tomcat || [http://yadi.sk/d/aY4qsjlT2wi6I загрузить]
|}

== Курсовая работа ==

Страница курсовой работы находится [[Курсовая по ТП (10 семестр) | здесь]].

== Экзамен ==

== Литература ==

* Хорстман, Корнел - "Java 2";
* [http://en.wikibooks.org/wiki/Subject:Java_programming_language Википедия].

[[Категория:10 семестр]]
[[Категория:Предметы]]

Аксёнов А.Н.

2013-01-12T13:34:04Z

195.19.36.135: Новая страница: «он няяяшка»

он няяяшка

Категория:Проектирование программных систем (9 семестр)

2012-11-13T20:49:12Z

195.19.36.135: /* Домашнее задание */ Добавил про редакторы UML

<p>{{Предмет | name=Проектирование программных систем | prepod=[[Балдин А.В.]] | lections=? | seminars=нет | labs=нет | dzs=1 | rks=нет | signif=желательно | what=экзамен}}</p>

Язык {{Википедия|UML|UML}}, а также как собрать команду разработчиков и сделать вместе с ними большой проект.

На лекции ходить не обязательно, никого не отмечают, но желательно, иначе будет сложно въезжать в UML.

== Домашнее задание ==

Описать программный проект, любой. Требования: минимум 6 диаграмм на UML и полстраницы описания проекта.

Сдавать или на лекции, или в 369 аудиторию, специальному человеку на входе.

UML диаграммы можно создавать во множестве программ : [http://ru.wikipedia.org/wiki/%D0%98%D0%BD%D1%81%D1%82%D1%80%D1%83%D0%BC%D0%B5%D0%BD%D1%82%D1%8B_UML-%D0%BC%D0%BE%D0%B4%D0%B5%D0%BB%D0%B8%D1%80%D0%BE%D0%B2%D0%B0%D0%BD%D0%B8%D1%8F список].

Многие из них преобразуют некий код в диаграмму. Походу считается самое тру! Есть даже [http://www.codeuml.com/ онлайн редакторы] которые делают из
Actor A
participant B
A -> B: Do Something
activate B
B -> B: Do work
activate B #FFBBBB
deactivate B
alt Success
B ->> C: Success
else failed
B ->> C: Fail
end
B --> A: Return
deactivate B

делают картинку.

Если не хотите париться с выбором редактора - enterprise architect

[http://yadi.sk/d/EaWTtrql0d2s5 сама прога]

Генератор ключей к ней выкладывать не буду - мало ли. Всегда можно скачать [http://nnm-club.ru/forum/viewtopic.php?t=236453 тут]

Прога немного сложная но может все, что связано с UML.

== Экзамен ==

== Литература ==

* Буч Г. - "Объектно-ориентированный анализ и проектирование с примерами приложений на C++";
* Буч Г., Рамбо Д., Джекобсон А. - "UML. Руководство пользователя";
* Леоненков А. - "Самоучитель UML";
* Орлов С.А. - "Технология разработки программного обеспечения";
* Фокс Д. - "Программное обеспечение и его разработка".

[[Категория:9 семестр]]
[[Категория:Предметы]]

Категория:Постреляционные базы данных (9 семестр)

2012-11-13T20:28:11Z

195.19.36.135: /* Лабораторные работы */ Дополнения по первой лабе

<p>{{Предмет | name=Постреляционные базы данных | prepod=[[Виноградова М.В.]] | lections=? | seminars=нет | labs=4 | dzs=1 | rks=2 | signif=по желанию | what=зачёт}}</p>

Магистры проходят этот курс через год, потому они могут либо проходить его через год, либо сейчас, на пятом курсе.

== Домашнее задание ==

Методичку по ДЗ можно взять [http://iu5.bmstu.ru/ebook/dz.zip здесь].

Задания и варианты по годам:
{| class="wikitable"
! Год !! Ссылка
|- align="center"
| 2012 || [http://yadi.sk/d/js6KNXiw0UMPp загрузить]
|}

== Лабораторные работы ==

Четыре лабораторных по две пары каждая (ага, по 2 пары каждая!).

СУБД Caché ({{Википедия|Caché|произносится именно с ударением на последний слог}}), взять можно [http://www.intersystems.ru/cache/devcorner/index.html тут].


Методички:
* [http://iu5.bmstu.ru/ebook/lab.zip по работе с Cashe];
* [http://yadi.sk/d/2bPGTZ1Z0PrSn лабораторная №1];
* [http://yadi.sk/d/MI0ftZAl0iXXv Примерный отчет по лабе №1];

'''Список любимых вопросов по первой лабе:'''

1. Что такое глобал? где их можно посмотреть? почему первый элемент пустой?

2. Как хранятся производные классы? Записи добавляются в какую таблицу (родителя или наследника)? Как хранится глобал производных объектов?

3. Как передавать параметры в SQL-запрос? Как их оттуда забирать?

4. Спрашивает почему вызывается метод одного класса а не другого при наследовании.

''Пример: метод Inf() d классе курсы и иностранные курсы. Выбираем объект класса курсы, но который может быть приведен к производному. Вызывается метод Inf() производного. вопрос: почему? ответ: полиморфизм!''

5. Как проверить существование объекта через терминал ( это про .%ExistsId())

6. В чем отличие метода класса от метода экземпляра класса

7. Как выполняется sql из каше?

set o=##class(%ResultSet).%New("%DynamicQuery:SQL")
do o.Prepare("select * from Lesson where ofcour->name ='"_lang_"'")
do o.Execute()

нужно рассказать про каждую строчку что она делает.

8. Как хранятся массивы? Какой формат у ID элемента массива.

9. ....

* [http://yadi.sk/d/0u0rlgYT0PrUp лабораторная №2] (пока только для 1 и 2 группы, можно брать их варианты).

== РК ==

Два РК. Оба <strike>не обязательные, можно не писать</strike> обязательные, без них не будет зачёта. Если написать хорошо и вовремя, то можно претендовать на зачёт автоматом.

=== РК 1 ===

Билет РК состоит из трёх практических упражнений.

* [http://yadi.sk/d/u12OqHhqr_da примеры заданий на РК].

=== РК 2 ===

== Зачёт ==

Для зачёта автоматом надо хорошо написать оба РК и выполнить дополнительные задания к ЛР.

== Литература ==

* Кузнецов - "Базы данных, языки и модели";
* Гарсиа, Малина, Ульман, Уидоу - "Схемы БД";
* Кирстеп - "Cashe";
* [http://citforum.ru/database/ http://citforum.ru/database/]

[[Категория:9 семестр]]
[[Категория:Предметы]]