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

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

2013-06-20T18:23:07Z

195.19.50.70: /* Про JOIN */

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

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

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

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

== Схема БД ==

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

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

Результат:

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

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

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

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

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

Результат:

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

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

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

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

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

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

Результат:

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

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

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

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

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

Результат:

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

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

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

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

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

<div class="toccolours mw-collapsible mw-collapsed">
''Ещё вариант этого запроса''
<div class="mw-collapsible-content">
<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>
</div>

<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'.

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

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

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


Результат:

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

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

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

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

<syntaxhighlight lang="sql">
SELECT DISTINCT nazvanie
FROM spasoi_ekz.j J
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>

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

Результат:

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

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

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

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

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

Результат:

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

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

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

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

Что сдавать в 10 семестре

2013-05-13T14:42:58Z

195.19.50.70: разбил по табличкам

В таблице приведён список всего того, что мешает жить в 10 семестре.

{| class="wikitable"
|- align="center"
! Предмет !! ЛР !! ДЗ !! РК !! Курсовой !! В конце
|- align="center"
| [[:Категория:Надёжность и достоверность (10 семестр) | Надёжность и достоверность]] || - || 4 || - || по плану в 11 семестре, но можно сдать сейчас || <font color="red">'''экзамен'''</font>
|- align="center"
| [[:Категория:Технологии программирования (10 семестр) | Технологии программирования]] || 3 || - || - || да || <font color="red">'''экзамен'''</font>
|- align="center"
| [[:Категория:Технологии разработки ПО (10 семестр) | Технологии разработки ПО]] || 8 || 1 || 2 || - || <font color="red">'''экзамен'''</font>
|- align="center"
| [[:Категория:Структурное проектирование АСОИ (10 семестр) | Структурное проектирование АСОИ]] || 2 || - || - || да || <font color="red">'''экзамен'''</font>

|}

{| class="wikitable"
|- align="center"
! Предмет !! ЛР !! ДЗ !! РК !! Курсовой !! В конце
|- align="center"
| [[:Категория:Распределённые системы (10 семестр) | Распределённые системы]] || - || - || 2 || - || <font color="green">'''зачёт'''</font>
|- align="center"
| [[:Категория:Архитектура корпоративных информационных систем (10 семестр) | Архитектура корпоративных ИС]] || - || 1 || - || - || <font color="green">'''зачёт'''</font>
|- align="center"
| [[Вычислительный практикум (10 семестр) | Вычислительный практикум]] || 8 || - || - || - || <font color="green">'''зачёт'''</font>
|- align="center"
| [[:Категория:Аналитические модели СОИ (10 семестр) | Аналитические модели СОИ]] || - || 6 || - || - || <font color="green">'''зачёт'''</font>
|}

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

Обсуждение участника:ILobster

2013-01-20T14:45:19Z

195.19.50.70: Новая страница: «В К2 ошибка, не L -> PRS, а B -> PRS»

В К2 ошибка, не L -> PRS, а B -> PRS

ТОРА (9) - Семинар №7 - Синтез хорошей БД

2013-01-20T14:35:14Z

195.19.50.70: ошибка в К4

{{Backward|l=ТОРА (9) - Семинар №6 - Синтез хорошей БД}}
__TOC__
== Продолжение решения ДЗ №2 ==

Продолжаем решать [[ТОРА (9) - Семинар №5 - Синтез хорошей БД#Вторая задача | вторую задачу второго ДЗ]]. Начало решения - в предыдущем семинаре.

4) разбиваем на классы эквивалентности:

{|
|-
| style="text-align:right;" | {{Формула|f=A\rightarrow CNO}} ||   -   || {{Формула|f=K_1 = ACNO}}
|-
| style="text-align:right;" | {{Формула|f=B\rightarrow PRS}} ||   -   || {{Формула|f=K_2 = BPRS}}
|-
| style="text-align:right;" | {{Формула|f=AD\rightarrow CNOXVTBPRSE}} ||   -   || {{Формула|f=K_3 = ACDNOXVTPRSE}}
|-
| style="text-align:right;" | {{Формула|f=K\rightarrow DELTVXBPRS}} ||   -   || {{Формула|f=K_4\rightarrow KDELTVXBPRS}}
|-
| style="text-align:right;" | {{Формула|f=L\rightarrow T}} ||   -   || {{Формула|f=K_5 = LT}}
|-
| style="text-align:right;" | {{Формула|f=X\rightarrow VT}} ||   -   || {{Формула|f=K_6 = XVT}}
|-
| style="text-align:right;" | {{Формула|f=ET\rightarrow V}} ||   -   || {{Формула|f=K_7 = EVT}}
|-
| style="text-align:right;" | {{Формула|f=D\rightarrow XEBPRSVT}} ||   -   || {{Формула|f=K_8 = DXEBPRSVT}}
|-
| style="text-align:right;" | {{Формула|f=ABCDEKLNOPRSTVX\rightarrow\varnothing}} ||   -   || {{Формула|f=K_9 = ABCDEKLNOPRSTVX}}
|}

5) граф классов эквивалентности:

[[Файл:9sTORAs7pic1.png|500px|center]]

6) редуцируем:

[[Файл:9sTORAs7pic2.png|500px|center]]

7)

:смотри граф.

8)

:{{Формула|f=AD\rightarrow\varnothing}}

9)

:{{Формула|f=\rho = (ACNO, XVT, BPRS, ETV, LT, DXBE, KDL, AK) = (R_1, R_2, R_3, R_4, R_5, R_6, R_7, R_8}}

10)

:* проверяем свойство сохранения без потерь:

{| class="wikitable" style="margin:auto;"
! !! {{Формула|f=A}} !! {{Формула|f=B}} !! {{Формула|f=C}} !! {{Формула|f=D}} !! {{Формула|f=E}} !! {{Формула|f=K}} !! {{Формула|f=L}} !! {{Формула|f=N}} !! {{Формула|f=O}} !! {{Формула|f=P}} !! {{Формула|f=R}} !! {{Формула|f=S}} !! {{Формула|f=T}} !! {{Формула|f=V}} !! {{Формула|f=X}}
|- align="center"
! {{Формула|f=ACNO}}
| {{Формула|f=a}} || || {{Формула|f=a}} || || || || || {{Формула|f=a}} || {{Формула|f=a}} || || || || || ||
|- align="center"
! {{Формула|f=XVT}}
| || || || || || || || || || || || || {{Формула|f=a}} || {{Формула|f=a}} || {{Формула|f=a}}
|- align="center"
! {{Формула|f=BPRS}}
| || {{Формула|f=a}} || || || || || || || || {{Формула|f=a}} || {{Формула|f=a}} || {{Формула|f=a}} || || ||
|- align="center"
! {{Формула|f=ETV}}
| || || || || {{Формула|f=a}} || || || || || || || || {{Формула|f=a}} || {{Формула|f=a}} ||
|- align="center"
! {{Формула|f=LT}}
| || || || || || || {{Формула|f=a}} || || || || || || {{Формула|f=a}} || ||
|- align="center"
! {{Формула|f=DXBE}}
| || {{Формула|f=a}} || || {{Формула|f=a}} || {{Формула|f=a}} || || || || || bgcolor="lime" | {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}} || {{Формула|f=a}}
|- align="center"
! {{Формула|f=KDL}}
| || bgcolor="lime" | {{Формула|f=a}} || || {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}} || {{Формула|f=a}} || {{Формула|f=a}} || || || bgcolor="lime" | {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}}
|- align="center"
! {{Формула|f=AK}}
| {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}} || {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}} || bgcolor="lime" | {{Формула|f=a}}
|}

:получили строку, сплошь состоящую из {{Формула|f=a}}. Значит, схема <u>обладает свойством соединения без потерь</u>.

:* проверяем свойство сохранения функциональных зависимостей:
::{{Формула|f=УНП = (A\rightarrow CNO, B\rightarrow PRS, AD\rightarrow XCNOVTBPRSE, K\rightarrow DELTVXBPRS,}}
::{{Формула|f=L\rightarrow T, X\rightarrow VT, ET\rightarrow V, D\rightarrow XBEVTPRS)}}
::1-4)
:::{{Формула|f=H\neq\varnothing}}
::5)
:::{{Формула|f=УНП - H = (A\rightarrow CNO, B\rightarrow PRS, K\rightarrow DL, L\rightarrow T, X\rightarrow VT, ET\rightarrow V, D\rightarrow XBE)}}
:::{{Формула|f=H = (AD\rightarrow CNOVTPRSXBE, K\rightarrow EXBTVPRS, D\rightarrow PPRSVT)}}
::6)
:::{{Формула|f=AD\rightarrow^? CNOVTPRSXBE}} <span style="color:green">да</span>
:::{{Формула|f=(AD)^+ = ADCNOXBEPRSVT}}

:::{{Формула|f=K\rightarrow^? XBTEVPRS}} <span style="color:green">да</span>
:::{{Формула|f=(AD)^+ = KDLXBEVTPRS}}

:::{{Формула|f=D\rightarrow^? PRSVT}} <span style="color:green">да</span>
:::{{Формула|f=(AD)^+ = DXBEPRSVT}}

:значит, схема <u>обладает свойством сохранения функциональных зависимостей</u>.

А теперь строим схему в нотации ERwin:

[[Файл:9sTORAs7pic3.png|500px|center]]

переделываем в схему с синтетическими ключами:

[[Файл:9sTORAs7pic4.png|500px|center]]

== ДЗ №3 ==

Пусть на сервер базы данных поступает оператор <code>SELECT</code>, соответствующий следующему запросу: "''Выдать имена поставщиков из Лондона, которые поставляют для изделия с номером {{Формула|f=J1}}, по крайней мере, одну красную деталь''".

Схема базы данных (таблицы {{Формула|f=S}}, {{Формула|f=P}}, {{Формула|f=J}}, {{Формула|f=SPJ}}) в виде ER-диаграммы:

[[Файл:9sTORAs7pic5.png|center|500px]]

Схема базы данных: {{Формула|f=\rho = (S, P, J, SPJ)}}.

здесь:
:{{Формула|f=S}} - таблица "Поставщик", описывающая поставщиков деталей. Имеет следующие поля:
:* <u>номер поставщика</u> - идентификационный номер поставщика (ключевое поле);
:* имя - фамилия, имя и отчество поставщика;
:* состояние - состояние поставщика (состояние счета);
:* город - город, в котором проживает поставщик и в котором находится его центральный офис.

:{{Формула|f=P}} - таблица "Деталь". Описывает поставляемые детали и включает следующие поля:
:* <u>номер детали</u> - идентификационный номер детали (ключевое поле);
:* название - название детали;
:* цвет - цвет детали;
:* вес - все детали в фунтах;
:* город - город, в котором изготавливается деталь.

:{{Формула|f=J}} - таблица "Изделие". Описывает изготавливаемые изделия и включает следующие поля
:* <u>номер изделия</u> - идентификационный номер изделия (ключевое поле);
:* название - название изделия;
:* город - город, где изготавливается изделие.

:{{Формула|f=SPJ}} - таблица "Сборка". Имеет следующие поля:
:* <u>номер поставщика</u> — идентификационный номер поставщика, поставляющего деталь (ключевое поле);
:* <u>номер детали</u> - идентификационный номер детали, поставляемой поставщиком (ключевое поле);
:* <u>номер изделия</u> - идентификационный номер изделия, в которое входит деталь, поставляемая поставщиком (ключевое поле);
:* количество - количество деталей, поставляемых поставщиком для данного изделия (количество деталей в поставке).

<u>Задание</u>:
# Написать соответствующий оператор <code>SELECT</code> и построить логический план выполнения этого оператора.
# Определить оптимальный физический план выполнения оператора <code>SELECT</code> при следующих исходных данных:

::1) Количество записей в таблицах:
:::{{Формула|f=T(S)=10000}}, {{Формула|f=T(P)=100000}}, {{Формула|f=T(SPJ)=1000000}}.

::2) Количество записей в одном блоке таблицы:
:::{{Формула|f=L_s=500}}, {{Формула|f=L_p=500}}, {{Формула|f=L_{SPJ}=1000}}, {{Формула|f=L_{JOIN}=2000}}.

::3) Индексы по атрибутам и число записей таблицы в одном блоке индекса ({{Формула|f=L}}):
::таблица {{Формула|f=S}}:
:::* индекс по атрибуту "номер поставщика", {{Формула|f=L=200}};
::таблица {{Формула|f=Р}}:
:::* индекс по атрибуту "номер детали", {{Формула|f=L=200}};
:: таблица {{Формула|f=SPJ}}:
:::* индекс по атрибуту "номер поставщика", {{Формула|f=L=200}};
:::* индекс по атрибуту "номер детали", {{Формула|f=L=200}};
:::* индекс по атрибуту "номер изделия", {{Формула|f=L=200}}.
::<span style="color:blue">''Примечания:''
:::* ''записи таблиц могут читаться в отсортированном виде по своим индексированным атрибутам'';
:::* ''записи во всех таблицах не сгруппированы (нет кластеризации).''</span>

::4) Мощности атрибутов:
::* {{Формула|f=I(S, город) = 50}}, {{Формула|f=I(S, номер\_ поставщика) = 10000}};
::* {{Формула|f=I(Р, цвет) = 20}}, {{Формула|f=I(Р, номер\_ детали) = 100000}};
::* {{Формула|f=I(SPJ, номер\_ поставщика) = 5000}}, {{Формула|f=I(SPJ, номер\_ детали) = 100000}}, {{Формула|f=I(SPJ, номер\_ изделия) = 10000}}.

::5) Число блоков {{Формула|f=b=10}}, значения {{Формула|f=C_{comp} = C_{move} = C_{filter} = 0.01}} мс, {{Формула|f=C_B = 10}} мс.

::6) Предполагается, что используются левосторонние деревья для поиска оптимального плана и применяются каналы.
::<span style="color:blue">''Примечание: метод хешированного соединения не рассматривать.''</span>

Пример решения можно посмотреть в [[ТОРА (9) - Семинар №8 - Пример решения ДЗ на оптимизацию запроса | последнем семинаре]].

[[Категория:Теоретические основы реляционной алгебры (9 семестр)|С]]
[[Категория:Конспекты лекций и семинаров]]