SQL-запросы к экзамену по СПАСОИ (10 семестр) — различия между версиями

Материал из Кафедра ИУ5 МГТУ им. Н.Э.Баумана - студенческое сообщество
Перейти к: навигация, поиск
м (Схема БД: изменения в схеме)
(Билет 21)
 
(не показана 251 промежуточная версия 19 участников)
Строка 6: Строка 6:
  
 
На этой странице собраны все сформированные запросы по билетам.
 
На этой странице собраны все сформированные запросы по билетам.
 +
 +
Странно, что ни в одном билете нет запроса с сортировкой результатов. <s>Возможно, они будут в дополнительных заданиях.</s> Григорьев сказал, что это слишком просто и потому он не стал загромождать запросы дополнительными мелочами.
  
 
== Схема БД ==
 
== Схема БД ==
Строка 11: Строка 13:
 
Схема БД используется всё [[СПАСОИ_(10)_-_Лекция_№8_-_SQL#Некоторые возможности языка SQL | та же]], что была в прошлом семестре и на лекциях.
 
Схема БД используется всё [[СПАСОИ_(10)_-_Лекция_№8_-_SQL#Некоторые возможности языка SQL | та же]], что была в прошлом семестре и на лекциях.
  
Для написания запросов и проверки их выполнения создана БД в СУБД [http://www.postgresql.org/ PostgreSQL]. Скрипт создания можно загрузить [http://yadi.sk/d/o1fC2VNa5DrwK отсюда].
+
Для написания запросов и проверки их выполнения создана БД в СУБД [http://www.postgresql.org/ PostgreSQL].
 +
 
 +
Скрипт создания можно загрузить [http://yadi.sk/d/ArwqOGAy5pPfi отсюда].
  
 
=== Таблицы БД ===
 
=== Таблицы БД ===
Строка 22: Строка 26:
  
 
   nomer_postavshika |      imya      | sostoyanie |  gorod
 
   nomer_postavshika |      imya      | sostoyanie |  gorod
  ------------------+------------------+------------+-----------
+
  -------------------+------------------+------------+------------
 
   S5                | Мелисандра      |      65000 | Мадрид
 
   S5                | Мелисандра      |      65000 | Мадрид
 
   S2                | Бран Старк      |      60000 | Мурманск
 
   S2                | Бран Старк      |      60000 | Мурманск
 
   S1                | Якен Хгар        |    1500000 | Йокогама
 
   S1                | Якен Хгар        |    1500000 | Йокогама
 
   S7                | Сирио Форель    |    1500000 | Йокогама
 
   S7                | Сирио Форель    |    1500000 | Йокогама
  S6                | Оша              |      15000 | Москва
 
 
   S4                | Джейме Ланнистер |    750000 | Лондон
 
   S4                | Джейме Ланнистер |    750000 | Лондон
 
   S3                | Серсея Ланнистер |    1200000 | Лондон
 
   S3                | Серсея Ланнистер |    1200000 | Лондон
 
   S8                | Тирион Ланнистер |      2571 | Манчестер
 
   S8                | Тирион Ланнистер |      2571 | Манчестер
 
   S9                | Иванов          |      35000 | Мытищи
 
   S9                | Иванов          |      35000 | Мытищи
  (9 rows)
+
  S10              | Русе Болтон      |      44444 | Баренцбург
 +
  S11              | Петров Иван      |      35000 | Мытищи
 +
  S14              | Рендилл Тарли    |    1111111 | Прага
 +
  S13              | Сэмвелл Тарли    |    999999 | Прага
 +
  S6                | Оша              |            | Москва
 +
  S16              | Ходор            |            | Москва
 +
  S15              | Мелисса Флорент  |    888888 | Стокгольм
 +
  S12              | Петров Пётр      |      35001 | Мытищи
 +
  S17              | Томми Версетти  |  123456789 | Майами
 +
  S18              | Безликий        |          1 | Йокогама
 +
  (18 rows)
  
 
==== Детали ====
 
==== Детали ====
Строка 46: Строка 59:
 
   P11          | абажур              | синий        |  400 | Нижний Новгород
 
   P11          | абажур              | синий        |  400 | Нижний Новгород
 
   P1          | гайка                | чёрный        |  20 | Лондон
 
   P1          | гайка                | чёрный        |  20 | Лондон
  P2          | шуруп                | чёрный        |    5 | Лондон
 
 
   P3          | ось                  | белый        | 5000 | Эдинбург
 
   P3          | ось                  | белый        | 5000 | Эдинбург
 
   P4          | зубчатое колесо      | чёрный        |  50 | Эдинбург
 
   P4          | зубчатое колесо      | чёрный        |  50 | Эдинбург
Строка 58: Строка 70:
 
   P16          | колесо              | белый        | 1500 | Манчестер
 
   P16          | колесо              | белый        | 1500 | Манчестер
 
   P5          | втулка              | серый        |  350 | Манчестер
 
   P5          | втулка              | серый        |  350 | Манчестер
  (16 rows)
+
  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)
  
 
==== Изделия ====
 
==== Изделия ====
Строка 76: Строка 98:
 
   J6            | велосипед 01/23      | Манчестер
 
   J6            | велосипед 01/23      | Манчестер
 
   J7            | изделие из болтов    | Челябинск
 
   J7            | изделие из болтов    | Челябинск
  (7 rows)
+
  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)
  
 
==== Сборки ====
 
==== Сборки ====
Строка 113: Строка 146:
 
   S8                | P16          | J6            |          2
 
   S8                | P16          | J6            |          2
 
   S3                | P5          | J6            |          10
 
   S3                | P5          | J6            |          10
   S9                | P14          | J7            |          16
+
  S10              | P2          | J8            |          4
   S8               | P1          | J7           |          3
+
  S8                | P1          | J7            |          16
  (29 rows)
+
   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 ===
 
=== Билет 1 ===
 +
 +
Написать запрос SELECT: выдать номера поставщиков, которые поставляют, по крайней мере, все те детали, которые поставляет поставщик с номером 'S2'.
 +
 +
Текст запроса:
 +
 +
<syntaxhighlight lang="sql">
 +
SELECT DISTINCT nomer_postavshika
 +
FROM spasoi_ekz.spj SPJX
 +
WHERE NOT EXISTS (
 +
                  SELECT nomer_detali
 +
                  FROM spasoi_ekz.spj SPJY
 +
                  WHERE nomer_postavshika = 'S2'
 +
                    AND NOT EXISTS (
 +
                                    SELECT *
 +
                                    FROM spasoi_ekz.spj
 +
                                    WHERE nomer_postavshika = SPJX.nomer_postavshika
 +
                                      AND nomer_detali = SPJY.nomer_detali
 +
                                  )
 +
                );
 +
</syntaxhighlight>
 +
 +
Еще один вариант:
 +
<syntaxhighlight lang="sql">
 +
WITH
 +
  s_all AS (
 +
      SELECT DISTINCT nomer_detali
 +
      FROM spasoi_ekz.spj SPJY
 +
      WHERE nomer_postavshika = 'S2'
 +
  )
 +
 +
SELECT
 +
  spj.nomer_postavshika
 +
FROM spasoi_ekz.spj
 +
  INNER JOIN s_all ON s_all.nomer_detali = spj.nomer_detali
 +
WHERE
 +
  nomer_postavshika != 'S2'
 +
GROUP BY nomer_postavshika
 +
HAVING count(spj.nomer_detali) = (SELECT count(*) FROM s_all);
 +
</syntaxhighlight>
 +
<!-- <div class="toccolours mw-collapsible mw-collapsed">
 +
''Ещё вариант''
 +
<div class="mw-collapsible-content">
 +
<syntaxhighlight lang="sql">
 +
SELECT DISTINCT nomer_postavshika
 +
FROM spasoi_ekz.spj SPJ
 +
WHERE NOT EXISTS (
 +
    (SELECT DISTINCT nomer_detali FROM spasoi_ekz.spj WHERE nomer_postavshika = 'S2')
 +
    EXCEPT
 +
    (SELECT DISTINCT nomer_detali FROM spasoi_ekz.spj WHERE nomer_postavshika = SPJ.nomer_postavshika)
 +
)
 +
AND nomer_postavshika != 'S2'
 +
</syntaxhighlight>
 +
</div>
 +
</div> -->
 +
Результат:
 +
 +
  nomer_postavshika
 +
-------------------
 +
  S6
 +
  S2
 +
(2 rows)
  
 
=== Билет 2 ===
 
=== Билет 2 ===
Строка 125: Строка 266:
 
Написать запрос SELECT: выдать номера деталей и общее их количество для всех номеров деталей, поставляемых более чем одним поставщиком.
 
Написать запрос SELECT: выдать номера деталей и общее их количество для всех номеров деталей, поставляемых более чем одним поставщиком.
  
Текст запроса:
+
Текст запроса, каким его дал Григорьев на лекции, впоследствии исправив его:
  
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
Строка 134: Строка 275:
 
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_postavshika) > 1;
 
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_postavshika) > 1;
 
</syntaxhighlight>
 
</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          |                        15 |                        2
+
   P1          |                        71 |                        5
   P12          |                        7 |                        7
+
  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
 
   P4          |                        10 |                        2
  (3 rows)
+
  P5          |                        16 |                        2
 +
  (12 rows)
  
 
=== Билет 3 ===
 
=== Билет 3 ===
  
Написать запрос SELECT: выдать номера поставщиков, поставляющих детали с номером ‘P1’ для какого-либо изделия в количестве (в поставке) большим, чем средний объём поставок деталей с номером ‘P2’ для этого изделия.
+
Написать запрос 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">
 
<syntaxhighlight lang="sql">
 
SELECT DISTINCT nomer_postavshika
 
SELECT DISTINCT nomer_postavshika
Строка 186: Строка 368:
 
);
 
);
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
</div>
 +
</div>
  
 
Результат:
 
Результат:
Строка 200: Строка 384:
 
Текст запроса:
 
Текст запроса:
  
 +
<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">
 
<syntaxhighlight lang="sql">
 
SELECT DISTINCT nomer_izdelia
 
SELECT DISTINCT nomer_izdelia
Строка 214: Строка 412:
 
                           );
 
                           );
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
</div>
 +
</div>
  
 
Результат:
 
Результат:
Строка 220: Строка 420:
 
  ---------------
 
  ---------------
 
   J5
 
   J5
  (1 row)
+
  J16
 +
  (2 rows)
  
 
=== Билет 5 ===
 
=== Билет 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 ===
 
=== Билет 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 ===
 
=== Билет 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 ===
 
=== Билет 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 ===
 
=== Билет 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 ===
 
=== Билет 10 ===
 +
 +
Написать запрос SELECT: выдать имена поставщиков, поставляющих детали с названием 'Болт' для изделия с названием 'Рама 02-01' в количестве (в поставке) большим, чем минимальное значение поставки детали с номером 'P1'.
 +
 +
Текст запроса:
 +
 +
<syntaxhighlight lang="sql">
 +
SELECT imya
 +
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
 +
  ON SPJ.nomer_izdelia = J.nomer_izdelia
 +
      AND J.nazvanie = LOWER('Рама 02-01')
 +
JOIN spasoi_ekz.p P
 +
  ON SPJ.nomer_detali = P.nomer_detali
 +
      AND P.nazvanie = LOWER('Болт')
 +
JOIN spasoi_ekz.s S
 +
  ON SPJ.nomer_postavshika = S.nomer_postavshika
 +
WHERE kolichestvo > (
 +
    SELECT MIN(kolichestvo)
 +
    FROM spasoi_ekz.spj
 +
    WHERE nomer_detali = 'P1'
 +
                    );
 +
</syntaxhighlight>
 +
 +
<div class="toccolours mw-collapsible mw-collapsed">
 +
Этот же запрос без JOIN (точнее, с CROSS JOIN):
 +
<div class="mw-collapsible-content">
 +
<syntaxhighlight lang="sql">
 +
SELECT S.imya
 +
FROM spasoi_ekz.s S,
 +
    spasoi_ekz.p P,
 +
    spasoi_ekz.j J,
 +
    spasoi_ekz.spj SPJ
 +
WHERE SPJ.nomer_postavshika = S.nomer_postavshika
 +
  AND SPJ.nomer_detali = P.nomer_detali
 +
  AND SPJ.nomer_izdelia = J.nomer_izdelia
 +
  AND P.nazvanie = LOWER('Болт')
 +
  AND J.nazvanie = LOWER('Рама 02-01')
 +
  AND SPJ.kolichestvo > (
 +
                        SELECT MIN(kolichestvo)
 +
                        FROM spasoi_ekz.spj
 +
                        WHERE nomer_detali = 'P1'
 +
                        );
 +
 +
</syntaxhighlight>
 +
</div>
 +
</div>
 +
Результат:
 +
 +
    imya
 +
-------------
 +
  Русе Болтон
 +
(1 row)
  
 
=== Билет 11 ===
 
=== Билет 11 ===
 +
 +
Написать запрос SELECT: выдать названия городов и суммарное состояние проживающих в каждом городе поставщиков, у которых минимальный объём поставки деталей больше 1000.
 +
 +
Текст запроса:
 +
 +
<syntaxhighlight lang="sql">
 +
SELECT gorod, SUM(sostoyanie)
 +
FROM spasoi_ekz.s S
 +
WHERE (
 +
      SELECT MIN(kolichestvo)
 +
      FROM spasoi_ekz.spj X
 +
      WHERE X.nomer_postavshika = S.nomer_postavshika
 +
      ) > 1000
 +
GROUP BY gorod;
 +
</syntaxhighlight>
 +
 +
<div class="toccolours mw-collapsible mw-collapsed">
 +
''И ещё вариант''
 +
<div class="mw-collapsible-content">
 +
<syntaxhighlight lang="sql">
 +
SELECT gorod, SUM(sostoyanie)
 +
FROM spasoi_ekz.s
 +
WHERE nomer_postavshika IN (
 +
                            SELECT nomer_postavshika
 +
                            FROM spasoi_ekz.spj
 +
                            GROUP BY nomer_postavshika HAVING MIN(kolichestvo) > 1000
 +
                          )
 +
GROUP BY gorod;
 +
</syntaxhighlight>
 +
</div>
 +
</div>
 +
 +
<div class="toccolours mw-collapsible mw-collapsed">
 +
''И ещё три неправильных варианта ''
 +
<div class="mw-collapsible-content">
 +
<syntaxhighlight lang="sql">
 +
-- здесь сразу отбрасывается весь город,
 +
-- в котором хоть один поставщик имеет миним. объем <=1000,
 +
-- а надо, чтобы был отброшен только этот поставщик.
 +
-- читайте описание конструкции GROUP BY ... HAVING ...
 +
SELECT gorod, SUM(sostoyanie)
 +
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
 +
                      ON SPJ.nomer_postavshika = S.nomer_postavshika
 +
GROUP BY gorod HAVING MIN(kolichestvo) > 1000;
 +
 +
-- та же фигня
 +
SELECT S.gorod, SUM(S.sostoyanie)--, MIN(SPJ.kolichestvo)
 +
FROM spasoi_ekz.s S, spasoi_ekz.spj SPJ
 +
WHERE S.nomer_postavshika = SPJ.nomer_postavshika
 +
GROUP BY gorod HAVING MIN(kolichestvo) > 1000;
 +
 +
-- и ещё один почти верный
 +
SELECT gorod, sost
 +
FROM (
 +
      SELECT gorod, SUM(sostoyanie) AS sost, SUM(SPJ.kolichestvo)
 +
      FROM spasoi_ekz.spj, spasoi_ekz.s
 +
      WHERE S.nomer_postavshika = SPJ.nomer_postavshika
 +
      GROUP BY S.gorod HAVING SUM(SPJ.kolichestvo) > 1000
 +
    ) A;
 +
</syntaxhighlight>
 +
</div>
 +
</div>
 +
 +
Результат:
 +
 +
    gorod  |  sum
 +
-----------+---------
 +
  Прага    | 2111110
 +
  Стокгольм |  888888
 +
(2 rows)
  
 
=== Билет 12 ===
 
=== Билет 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 ===
 
=== Билет 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 ===
 
=== Билет 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>
 +
 +
Другой вариант:
 +
<syntaxhighlight lang="sql">
 +
SELECT DISTINCT imya
 +
FROM spasoi_ekz.s S
 +
            JOIN spasoi_ekz.spj SPJ ON S.nomer_postavshika = SPJ.nomer_postavshika
 +
WHERE SPJ.nomer_izdelia = 'J18'
 +
AND
 +
S.nomer_postavshika NOT IN (
 +
                  SELECT nomer_postavshika FROM spasoi_ekz.spj
 +
                  WHERE nomer_izdelia !='J18')
 +
</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 ===
 
=== Билет 15 ===
 +
 +
Написать запрос SELECT: выдать наименования изделий, для которых детали поставляют только те поставщики, у которых состояние больше 1000000 у.е.
 +
 +
Текст запроса:
 +
<syntaxhighlight lang="sql">
 +
SELECT DISTINCT nazvanie
 +
FROM spasoi_ekz.j J JOIN spasoi_ekz.spj X
 +
                          ON J.nomer_izdelia = X.nomer_izdelia
 +
WHERE NOT EXISTS (
 +
  SELECT *
 +
  FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
 +
                          ON S.nomer_postavshika = SPJ.nomer_postavshika
 +
  WHERE sostoyanie <= 1000000
 +
  AND SPJ.nomer_izdelia = X.nomer_izdelia
 +
                );
 +
</syntaxhighlight>
 +
 +
<div class="toccolours mw-collapsible mw-collapsed">
 +
''Неправильный вариант''
 +
<div class="mw-collapsible-content">
 +
<syntaxhighlight lang="sql">
 +
-- Неверный запрос, если в таблице SPJ НЕТ поставок с этим изделием,
 +
-- а в таблице J - есть это изделие, то запрос вернет его название, хотя не должен
 +
SELECT DISTINCT nazvanie
 +
FROM spasoi_ekz.j J
 +
WHERE NOT EXISTS (
 +
  SELECT *
 +
  FROM spasoi_ekz.spj X JOIN spasoi_ekz.s S
 +
                          ON X.nomer_postavshika = S.nomer_postavshika
 +
  WHERE sostoyanie <= 1000000
 +
  AND X.nomer_izdelia = J.nomer_izdelia
 +
                );
 +
</syntaxhighlight>
 +
</div>
 +
</div>
 +
 +
<div class="toccolours mw-collapsible mw-collapsed">
 +
''Вариант этого запроса без NOT EXISTS''
 +
<div class="mw-collapsible-content">
 +
<syntaxhighlight lang="sql">
 +
SELECT DISTINCT nazvanie
 +
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
 +
                          ON SPJ.nomer_postavshika = S.nomer_postavshika
 +
                        JOIN spasoi_ekz.j J
 +
                          ON SPJ.nomer_izdelia = J.nomer_izdelia
 +
WHERE sostoyanie > 1000000
 +
  AND SPJ.nomer_izdelia NOT IN (
 +
                                SELECT nomer_izdelia
 +
                                FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
 +
                                                          ON SPJ.nomer_postavshika = S.nomer_postavshika
 +
                                WHERE sostoyanie < 1000000
 +
                              );
 +
</syntaxhighlight>
 +
</div>
 +
</div>
 +
 +
Результат:
 +
 +
      nazvanie
 +
--------------------
 +
  кружевное бельё
 +
  уникальное изделие
 +
  процессор
 +
(3 rows)
  
 
=== Билет 16 ===
 
=== Билет 16 ===
 +
Написать запрос SELECT: выдать цвета и для каждого цвета общее количество деталей, входящих в изделие с названием 'Панно 01-03'.
 +
 +
Текст запроса:
 +
 +
<syntaxhighlight lang="sql">
 +
SELECT cvet AS "Цвет", SUM(kolichestvo) AS "Деталей"
 +
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
 +
                          ON SPJ.nomer_izdelia = J.nomer_izdelia
 +
    AND J.nazvanie = LOWER('Панно 01-03')
 +
JOIN spasoi_ekz.P P
 +
  ON SPJ.nomer_detali = P.nomer_detali
 +
GROUP BY cvet;
 +
</syntaxhighlight>
 +
 +
<div class="toccolours mw-collapsible mw-collapsed">
 +
''Другой вариант запроса для тонко чувствующих натур''
 +
<div class="mw-collapsible-content">
 +
<syntaxhighlight lang="sql">
 +
SELECT cvet AS "Цвет", SUM(kolichestvo) AS "Деталей"
 +
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
 +
                          ON SPJ.nomer_detali = P.nomer_detali
 +
JOIN spasoi_ekz.j J
 +
  ON SPJ.nomer_izdelia = J.nomer_izdelia
 +
WHERE J.nazvanie = LOWER('Панно 01-03')
 +
GROUP BY cvet;
 +
</syntaxhighlight>
 +
</div>
 +
</div>
 +
 +
<div class="toccolours mw-collapsible mw-collapsed">
 +
''И ещё один вариант запроса от ненавистника JOIN''
 +
<div class="mw-collapsible-content">
 +
<syntaxhighlight lang="sql">
 +
SELECT P.cvet, SUM(SPJ.kolichestvo)
 +
FROM spasoi_ekz.p P,
 +
    spasoi_ekz.spj SPJ,
 +
    spasoi_ekz.j J
 +
WHERE P.nomer_detali = SPJ.nomer_detali
 +
  AND J.nomer_izdelia = SPJ.nomer_izdelia
 +
  AND J.nazvanie = LOWER('Панно 01-03')
 +
GROUP BY P.cvet;
 +
</syntaxhighlight>
 +
</div>
 +
</div>
 +
 +
 +
Результат:
 +
 +
  Цвет  | Деталей
 +
-------+---------
 +
  белый |      9
 +
  серый |      5
 +
(2 rows)
  
 
=== Билет 17 ===
 
=== Билет 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 ===
 
=== Билет 18 ===
 +
 +
Написать запрос SELECT: выдать имена поставщиков, имеющих состояние больше 1000 и поставляющих деталь с названием 'Гайка 01-01' для изделия с названием 'Велосипед 03-04' в количестве (в поставке) большим, чем средний объём поставки, выполненной поставщиками с именем 'Иванов'.
 +
 +
 +
[[Файл:2nd dufficulty.png|center]]
 +
 +
 +
<p align="center"><font size="5px">'''Второй по сложности запрос экзамена!'''</font></p>
 +
 +
<p align="center"><font size="4px">'''Вот уж не свезло, так не свезло'''</font></p>
 +
 +
 +
Текст запроса:
 +
 +
<syntaxhighlight lang="sql">
 +
SELECT imya
 +
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
 +
                      ON SPJ.nomer_postavshika = S.nomer_postavshika
 +
                    JOIN spasoi_ekz.p P
 +
                      ON P.nomer_detali = SPJ.nomer_detali
 +
                    JOIN spasoi_ekz.j J
 +
                      ON J.nomer_izdelia = SPJ.nomer_izdelia
 +
WHERE sostoyanie > 1000
 +
  AND P.nazvanie = LOWER('Гайка 01-01')
 +
  AND J.nazvanie = LOWER('Велосипед 03-04')
 +
  AND kolichestvo > (
 +
                    SELECT AVG(kolichestvo)
 +
                    FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
 +
                      ON SPJ.nomer_postavshika = S.nomer_postavshika
 +
                    WHERE S.imya = 'Иванов' -- или WHERE S.imya LIKE '%Иванов%',
 +
                    -- поскольку сказано, что поставка выполнена поставщиками с именем Иванов,
 +
                    -- а это могут быть разные имена и отчества. Короче, опять великий
 +
                    -- русский язык в задании - что ещё за "с именем Иванов"
 +
                    );
 +
</syntaxhighlight>
 +
 +
<div class="toccolours mw-collapsible mw-collapsed">
 +
''И ещё один вариант запроса от ненавистника JOIN:''
 +
<div class="mw-collapsible-content">
 +
<syntaxhighlight lang="sql">
 +
SELECT imya
 +
FROM spasoi_ekz.s S,
 +
    spasoi_ekz.spj SPJ,
 +
    spasoi_ekz.p P,
 +
    spasoi_ekz.j J
 +
WHERE SPJ.nomer_postavshika = S.nomer_postavshika
 +
  AND SPJ.nomer_detali = P.nomer_detali
 +
  AND SPJ.nomer_izdelia=J.nomer_izdelia
 +
  AND S.sostoyanie > 1000
 +
  AND P.nazvanie = LOWER('Гайка 01-01')
 +
  AND J.nazvanie = LOWER('Велосипед 03-04')
 +
  AND kolichestvo > (
 +
                    SELECT AVG(kolichestvo)
 +
                    FROM spasoi_ekz.spj SPJ2, spasoi_ekz.S S2
 +
    WHERE S2.nomer_postavshika = SPJ2.nomer_postavshika
 +
      AND S2.imya LIKE '%Иванов%'
 +
    );
 +
</syntaxhighlight>
 +
</div>
 +
</div>
 +
 +
Результат:
 +
 +
    imya
 +
-------------
 +
  Петров Пётр
 +
(1 row)
  
 
=== Билет 19 ===
 
=== Билет 19 ===
 +
 +
Написать запрос SELECT: выдать названия красных деталей, которые входят только в изделие с названием 'Рама 02-03' в количестве, меньшем 10 единиц в поставке.
 +
 +
Примечание: Григорьев ругается на X.nomer_detali = SPJ.nomer_detali Лучше во вложенном подзапросе сделать еще один джойн p и приравнивать названия деталей.
 +
 +
Текст запроса:
 +
<syntaxhighlight lang="sql">
 +
SELECT DISTINCT X.nazvanie
 +
FROM spasoi_ekz.p X JOIN spasoi_ekz.spj SPJ
 +
                      ON SPJ.nomer_detali = X.nomer_detali
 +
WHERE X.cvet = 'красный'
 +
  AND kolichestvo < 10
 +
  AND NOT EXISTS (
 +
                  SELECT *
 +
                  FROM spasoi_ekz.j J JOIN spasoi_ekz.spj SPJ
 +
                                        ON SPJ.nomer_izdelia = J.nomer_izdelia
 +
                  WHERE J.nazvanie != LOWER('Рама 02-03')
 +
                    AND X.nomer_detali = SPJ.nomer_detali
 +
                );
 +
</syntaxhighlight>
 +
 +
<div class="toccolours mw-collapsible mw-collapsed">
 +
''Ещё вариант этого же запроса''
 +
<div class="mw-collapsible-content">
 +
<syntaxhighlight lang="sql">
 +
SELECT P.nazvanie
 +
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
 +
                          ON SPJ.nomer_detali = P.nomer_detali
 +
                            AND cvet = 'красный'
 +
                        JOIN spasoi_ekz.j J
 +
                          ON SPJ.nomer_izdelia = J.nomer_izdelia
 +
                              AND J.nazvanie = LOWER('Рама 02-03')
 +
WHERE kolichestvo < 10
 +
  AND SPJ.nomer_detali NOT IN (
 +
                              SELECT nomer_detali
 +
                              FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
 +
                                                        ON SPJ.nomer_izdelia = J.nomer_izdelia
 +
                                                            AND J.nazvanie != LOWER('Рама 02-03')
 +
                              );
 +
</syntaxhighlight>
 +
</div>
 +
</div>
 +
 +
Результат:
 +
 +
    nazvanie
 +
----------------
 +
  усиленная рама
 +
(1 row)
  
 
=== Билет 20 ===
 
=== Билет 20 ===
 +
 +
Написать запрос SELECT: выдать имена поставщиков, поставляющих только белые детали.
 +
 +
Текст запроса:
 +
 +
<syntaxhighlight lang="sql">
 +
SELECT imya
 +
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
 +
                      ON SPJ.nomer_postavshika = S.nomer_postavshika
 +
WHERE NOT EXISTS (
 +
                  SELECT *
 +
                  FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
 +
                                            ON P.nomer_detali = SPJ.nomer_detali
 +
                  WHERE nomer_postavshika = S.nomer_postavshika
 +
                    AND P.cvet != 'белый'
 +
                );
 +
</syntaxhighlight>
 +
 +
<div class="toccolours mw-collapsible mw-collapsed">
 +
''Ещё один вариант этого же запроса''
 +
<div class="mw-collapsible-content">
 +
<syntaxhighlight lang="sql">
 +
SELECT imya
 +
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
 +
                          ON SPJ.nomer_detali = P.nomer_detali
 +
                            AND cvet = 'белый'
 +
                        JOIN spasoi_ekz.s S
 +
                          ON SPJ.nomer_postavshika = S.nomer_postavshika
 +
WHERE S.nomer_postavshika NOT IN (
 +
                                  SELECT nomer_postavshika
 +
                                  FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
 +
                                                            ON SPJ.nomer_detali = P.nomer_detali
 +
                                                              AND cvet != 'белый'
 +
                                );
 +
</syntaxhighlight>
 +
</div>
 +
</div>
 +
 +
<div class="toccolours mw-collapsible mw-collapsed">
 +
''И ещё один вариант этого же запроса''
 +
<div class="mw-collapsible-content">
 +
<syntaxhighlight lang="sql">
 +
SELECT imya
 +
FROM spasoi_ekz.s
 +
WHERE nomer_postavshika NOT IN  (
 +
                                SELECT spj.nomer_postavshika
 +
                                FROM spasoi_ekz.spj SPJ, spasoi_ekz.p P
 +
                                WHERE SPJ.nomer_detali = P.nomer_detali
 +
                                  AND p.cvet != 'белый'
 +
                                )
 +
  AND nomer_postavshika  IN (
 +
                            SELECT spj.nomer_postavshika
 +
                            FROM spasoi_ekz.spj SPJ, spasoi_ekz.p P
 +
                            WHERE SPJ.nomer_detali = P.nomer_detali
 +
                              AND p.cvet = 'белый'
 +
                            );
 +
</syntaxhighlight>
 +
</div>
 +
</div>
 +
 +
<div class="toccolours mw-collapsible mw-collapsed">
 +
''И ещё один вариант этого запроса от ненавистника JOIN''
 +
<div class="mw-collapsible-content">
 +
<syntaxhighlight lang="sql">
 +
SELECT imya
 +
FROM spasoi_ekz.s S, spasoi_ekz.spj SPJ2
 +
WHERE SPJ2.nomer_postavshika = S.nomer_postavshika
 +
  AND NOT EXISTS (
 +
                  SELECT *
 +
                  FROM spasoi_ekz.p P, spasoi_ekz.spj SPJ
 +
                  WHERE SPJ.nomer_detali = P.nomer_detali
 +
                    AND SPJ.nomer_postavshika = SPJ2.nomer_postavshika
 +
                    AND P.cvet != 'белый'
 +
                );
 +
</syntaxhighlight>
 +
</div>
 +
</div>
 +
 +
Результат:
 +
 +
      imya
 +
-----------------
 +
  Рендилл Тарли
 +
  Сэмвелл Тарли
 +
  Мелисса Флорент
 +
  Томми Версетти
 +
(4 rows)
  
 
=== Билет 21 ===
 
=== Билет 21 ===
 +
 +
Написать запрос SELECT: выдать названия изделий, для которых детали поставляет только и только поставщик с номером 'S1'.
 +
 +
Чтобы продемонстрировать выполнение запроса наглядно, возьмём поставщика не 'S1', а 'S18', который был создан специально для этого запроса. Если оставить 'S1', то наглядности не получится, так как по нашей схеме БД этот поставщик не попадает под условие "''только и только''", и запрос вернёт пустой результат.
 +
 +
Примечание: Григорьев ругается на nomer_izdelia = J.nomer_izdelia Лучше во вложенном подзапросе сделать еще один джойн j и приравнивать названия изделий, а не номера.
 +
Текст запроса:
 +
 +
<syntaxhighlight lang="sql">
 +
SELECT nazvanie
 +
FROM spasoi_ekz.j
 +
WHERE NOT EXISTS (
 +
                  SELECT *
 +
                  FROM spasoi_ekz.spj
 +
                  WHERE nomer_postavshika != 'S18'
 +
                    AND nomer_izdelia = J.nomer_izdelia
 +
                )
 +
  AND NOT EXISTS (
 +
                  SELECT *
 +
                  FROM spasoi_ekz.spj
 +
                  WHERE nomer_postavshika = 'S18'
 +
                    AND nomer_izdelia != J.nomer_izdelia
 +
                );
 +
</syntaxhighlight>
 +
 +
Вариант покороче:
 +
 +
<syntaxhighlight lang="sql">
 +
SELECT j1.nazvanie FROM spasoi_ekz.j j1
 +
WHERE NOT EXISTS(
 +
  SELECT * FROM spasoi_ekz.spj s1
 +
  JOIN spasoi_ekz.j j2 ON s1.nomer_izdelia = j2.nomer_izdelia
 +
  WHERE (s1.nomer_postavshika = 'S18'
 +
        AND j2.nomer_izdelia != j1.nomer_izdelia)
 +
  OR    (s1.nomer_postavshika != 'S18'
 +
        AND j2.nomer_izdelia = j1.nomer_izdelia)
 +
);
 +
</syntaxhighlight>
 +
 +
Результат:
 +
 +
      nazvanie
 +
--------------------
 +
  кинжал
 +
(1 row)
  
 
=== Билет 22 ===
 
=== Билет 22 ===
 +
 +
Написать запрос SELECT: выдать имена поставщиков, которые поставляют только детали с номером 'P1' для изделия с именем 'Штуцер 01-02'.
 +
 +
Текст запроса:
 +
<syntaxhighlight lang="sql">
 +
SELECT imya
 +
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
 +
                          ON SPJ.nomer_izdelia = J.nomer_izdelia
 +
                            AND J.nazvanie = LOWER('Штуцер 01-02')
 +
                        JOIN spasoi_ekz.s S
 +
                          ON SPJ.nomer_postavshika = S.nomer_postavshika
 +
WHERE NOT EXISTS (
 +
                  SELECT *
 +
                  FROM spasoi_ekz.spj X JOIN spasoi_ekz.j J
 +
                                          ON X.nomer_izdelia = J.nomer_izdelia
 +
                  WHERE X.nomer_detali != 'P1'
 +
                    AND J.nazvanie = LOWER('Штуцер 01-02')
 +
                    AND X.nomer_postavshika = S.nomer_postavshika
 +
                );
 +
</syntaxhighlight>
 +
 +
<!-- неверный запрос - номер изделия, а не название
 +
SELECT imya FROM S X
 +
JOIN SPJ Y ON X.nomer_postavshika=Y.nomer_postavshika
 +
WHERE X.nomer_postavshika NOT IN (
 +
                  SELECT DISTINCT nomer_postavshika FROM SPJ Z
 +
                  WHERE Z.nomer_izdelia != 'Штуцер 01-02'
 +
                  AND Z.nomer_detali!='P1');-->
 +
<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 ===
 
=== Билет 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 ===
 
=== Билет 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>
 +
и -->
 +
В уточнение задания Григорьев сказал следующее: "''Следует учесть, что в качестве наименования города может выступать переменная, в которую в некоторой программе должно быть занесено конкретное значение перед выполнением запроса''".
 +
 +
То есть, вообще говоря, задание на запрос неполное, и его можно трактовать так: выдать наименования деталей и общее их количество для всех наименований деталей, изготавливаемых в городе <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 class="toccolours mw-collapsible mw-collapsed">
 +
''То же, что выше, но для всех городов:''
 +
<div class="mw-collapsible-content">
 +
<syntaxhighlight lang="sql">
 +
SELECT gorod, nazvanie, SUM(kolichestvo) FROM spasoi_ekz.spj
 +
JOIN spasoi_ekz.p ON spj.nomer_detali = nomer_detali
 +
GROUP BY gorod, nazvanie
 +
ORDER BY gorod;
 +
</syntaxhighlight>
 +
</div>
 +
</div>
  
 
=== Билет 25 ===
 
=== Билет 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 ===
 
=== Билет 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 ===
 
=== Билет 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 ===
 
=== Билет 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 ===
 
=== Билет 29 ===
 +
Написать запрос SELECT: выдать названия деталей, которые входят только и только в состав изделия с названием 'Штуцер 01-03'.
 +
 +
Примечание: Григорьев ругается на SPJ.nomer_detali = P.nomer_detali Лучше во вложенном подзапросе сделать еще один джойн p и приравнивать названия деталей.
 +
 +
Текст запроса:
 +
 +
<syntaxhighlight lang="sql">
 +
SELECT DISTINCT nazvanie
 +
FROM spasoi_ekz.p
 +
WHERE NOT EXISTS (
 +
                  SELECT SPJ.nomer_izdelia
 +
                  FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
 +
                                            ON J.nomer_izdelia = SPJ.nomer_izdelia
 +
                  WHERE (
 +
                        J.nazvanie != LOWER('Штуцер 01-03')
 +
                        AND
 +
                        SPJ.nomer_detali = P.nomer_detali
 +
                        )
 +
                    OR (
 +
                        J.nazvanie = LOWER('Штуцер 01-03')
 +
                        AND
 +
                        SPJ.nomer_detali != P.nomer_detali
 +
                        )
 +
                );
 +
</syntaxhighlight>
 +
 +
<div class="toccolours mw-collapsible mw-collapsed">
 +
''Ещё один вариант этого же запроса''
 +
<div class="mw-collapsible-content">
 +
<syntaxhighlight lang="sql">
 +
SELECT nazvanie
 +
FROM spasoi_ekz.p P1
 +
WHERE NOT EXISTS (
 +
                  SELECT *
 +
                  FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P2
 +
                                            ON SPJ.nomer_detali = P.nomer_detali
 +
                                          JOIN spasoi_ekz.j J
 +
                                            ON J.nomer_izdelia = SPJ.nomer_izdelia
 +
                  WHERE (
 +
                        P1.nazvanie = P2.nazvanie AND nazvanie!='Штуцер'
 +
                        )
 +
                  OR (
 +
                        P1.nazvanie != P2.nazvanie AND nazvanie='Штуцер'
 +
                );
 +
</syntaxhighlight>
 +
</div>
 +
</div>
 +
 +
Результат:
 +
 +
      nazvanie
 +
------------------
 +
  штуцерная деталь
 +
(1 row)
  
 
=== Билет 30 ===
 
=== Билет 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>
 +
<!-- <div class="toccolours mw-collapsible mw-collapsed">
 +
''Ещё вариант''
 +
<div class="mw-collapsible-content">
 +
<syntaxhighlight lang="sql">
 +
SELECT DISTINCT imya
 +
FROM spasoi_ekz.spj SPJ
 +
WHERE NOT EXISTS (
 +
    (SELECT DISTINCT nomer_detali FROM spasoi_ekz.spj WHERE nomer_postavshika = 'S2')
 +
    EXCEPT
 +
    (SELECT DISTINCT nomer_detali FROM spasoi_ekz.spj WHERE nomer_postavshika = SPJ.nomer_postavshika)
 +
)
 +
AND nomer_postavshika != 'S2'
 +
</syntaxhighlight>
 +
</div>
 +
</div> -->
 +
Результат:
  
 +
  imya
 +
------------
 +
  Оша
 +
  Бран Старк
 +
(2 rows)
 
[[Категория:Структурное проектирование АСОИ (10 семестр)]]
 
[[Категория:Структурное проектирование АСОИ (10 семестр)]]

Текущая версия на 03:00, 25 июня 2018

Билет экзамена по СПАСОИ состоит из двух частей:

  1. теория;
  2. SQL-запрос.

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

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

Схема БД

Схема БД используется всё та же, что была в прошлом семестре и на лекциях.

Для написания запросов и проверки их выполнения создана БД в СУБД PostgreSQL.

Скрипт создания можно загрузить отсюда.

Таблицы БД

Поставщики

SELECT * FROM spasoi_ekz.s;
 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)

Детали

SELECT * FROM spasoi_ekz.p;
 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)

Изделия

SELECT * FROM spasoi_ekz.j;
 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)

Сборки

SELECT * FROM spasoi_ekz.spj;
 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)

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

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

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

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

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

  1. кроме Уаси никто не поставляет холодильники;
  2. Уася не поставляет ничего, кроме холодильников.

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

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

Про JOIN

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

Но неожиданно оказалось, это почти то же самое и называется CROSS JOIN. Такое соединение таблиц (CROSS JOIN + WHERE) считается устаревшим, поскольку его не рекомендует стандарт SQL ANSI. Таким образом, использование JOIN с условием соединения в ON является хипстерством в программировании более правильным и вообще прогрессивным.

Билет 1

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

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

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
                                   )
                 );

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

WITH
  s_all AS (
      SELECT DISTINCT nomer_detali
      FROM spasoi_ekz.spj SPJY
      WHERE nomer_postavshika = 'S2'
  )
 
SELECT
  spj.nomer_postavshika
FROM spasoi_ekz.spj
  INNER JOIN s_all ON s_all.nomer_detali = spj.nomer_detali
WHERE
  nomer_postavshika != 'S2'
GROUP BY nomer_postavshika
HAVING COUNT(spj.nomer_detali) = (SELECT COUNT(*) FROM s_all);

Результат:

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

Билет 2

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

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

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;

Ещё вариант

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;

Результат:

 Номер детали | Сколько штук поставляется | Сколько у неё поставщиков
--------------+---------------------------+---------------------------
 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' для этого изделия.

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

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'
                      );

Этот же запрос, но длиннее и нерациональней

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'
);

Результат:

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

Билет 4

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

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

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
                 );

Этот же запрос, но без EXISTS

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'
                          );

Результат:

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

Билет 5

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

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

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'
                    );

Вариант запроса без JOIN

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'
                        );

Ещё вариант запроса без JOIN и сложнее

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'
                                                )
                           );

Результат:

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

Билет 6

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

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

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');

Этот же запрос без JOIN

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')
                      );

Результат:

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

Билет 7

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

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

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
                    );

Ещё один вариант запроса

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
                                            )
                       );

И ещё вариант этого же запроса, но длиннее и нерациональней

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
                       )
);

Результат:

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

Билет 8

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

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

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;

Результат:

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

Билет 9

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

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

SELECT nomer_detali, SUM(kolichestvo)
FROM spasoi_ekz.spj
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_izdelia) = 1;

Ещё вариант

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;


Результат:

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

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

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'
                    );

Этот же запрос без JOIN (точнее, с CROSS JOIN):

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

Результат:

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

Билет 11

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

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

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;

И ещё вариант

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;

И ещё три неправильных варианта

-- здесь сразу отбрасывается весь город,
-- в котором хоть один поставщик имеет миним. объем <=1000,
-- а надо, чтобы был отброшен только этот поставщик.
-- читайте описание конструкции GROUP BY ... HAVING ...
SELECT gorod, SUM(sostoyanie)
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ 
                      ON SPJ.nomer_postavshika = S.nomer_postavshika
GROUP BY gorod HAVING MIN(kolichestvo) > 1000;
 
-- та же фигня
SELECT S.gorod, SUM(S.sostoyanie)--, MIN(SPJ.kolichestvo)
FROM spasoi_ekz.s S, spasoi_ekz.spj SPJ
WHERE S.nomer_postavshika = SPJ.nomer_postavshika
GROUP BY gorod HAVING MIN(kolichestvo) > 1000;
 
-- и ещё один почти верный
SELECT gorod, sost
FROM (
      SELECT gorod, SUM(sostoyanie) AS sost, SUM(SPJ.kolichestvo)
      FROM spasoi_ekz.spj, spasoi_ekz.s
      WHERE S.nomer_postavshika = SPJ.nomer_postavshika
      GROUP BY S.gorod HAVING SUM(SPJ.kolichestvo) > 1000
     ) A;

Результат:

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

Билет 12

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

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

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;

Результат:

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

Билет 13

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

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

SELECT COUNT(*) - COUNT(sostoyanie) AS "С пустым состоянием", 
       COUNT(sostoyanie) AS "С не пустым состоянием"
FROM spasoi_ekz.s;

Этот же запрос, но длиннее и нерациональней

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;

Результат:

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

Билет 14

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

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

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

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
			)
                 );

Другой вариант:

SELECT DISTINCT imya
FROM spasoi_ekz.s S
             JOIN spasoi_ekz.spj SPJ ON S.nomer_postavshika = SPJ.nomer_postavshika
WHERE SPJ.nomer_izdelia = 'J18'
AND
S.nomer_postavshika NOT IN (
                  SELECT nomer_postavshika FROM spasoi_ekz.spj
                  WHERE nomer_izdelia !='J18')

Результат:

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

Билет 15

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

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

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

Неправильный вариант

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

Вариант этого запроса без NOT EXISTS

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
                               );

Результат:

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

Билет 16

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

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

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;

Другой вариант запроса для тонко чувствующих натур

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;

И ещё один вариант запроса от ненавистника JOIN

SELECT P.cvet, SUM(SPJ.kolichestvo)
FROM spasoi_ekz.p P,
     spasoi_ekz.spj SPJ,
     spasoi_ekz.j J
WHERE P.nomer_detali = SPJ.nomer_detali
  AND J.nomer_izdelia = SPJ.nomer_izdelia
  AND J.nazvanie = LOWER('Панно 01-03')
GROUP BY P.cvet;


Результат:

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

Билет 17

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

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

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

Ещё вариант запроса

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;

И ещё вариант запроса

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;

И даже ещё вариант запроса

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;

Результат:

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

Билет 18

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


2nd dufficulty.png


Второй по сложности запрос экзамена!

Вот уж не свезло, так не свезло


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

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

И ещё один вариант запроса от ненавистника JOIN:

SELECT imya
FROM spasoi_ekz.s S,
     spasoi_ekz.spj SPJ,
     spasoi_ekz.p P,
     spasoi_ekz.j J
WHERE SPJ.nomer_postavshika = S.nomer_postavshika
  AND SPJ.nomer_detali = P.nomer_detali
  AND SPJ.nomer_izdelia=J.nomer_izdelia
  AND S.sostoyanie > 1000
  AND P.nazvanie = LOWER('Гайка 01-01')
  AND J.nazvanie = LOWER('Велосипед 03-04')
  AND kolichestvo > (
                     SELECT AVG(kolichestvo)
                     FROM spasoi_ekz.spj SPJ2, spasoi_ekz.S S2
		     WHERE S2.nomer_postavshika = SPJ2.nomer_postavshika
		       AND S2.imya LIKE '%Иванов%'
		    );

Результат:

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

Билет 19

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

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

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

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
                 );

Ещё вариант этого же запроса

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')
                              );

Результат:

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

Билет 20

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

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

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 != 'белый'
                 );

Ещё один вариант этого же запроса

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 != 'белый'
                                 );

И ещё один вариант этого же запроса

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 = 'белый'
                            );

И ещё один вариант этого запроса от ненавистника JOIN

SELECT imya
FROM spasoi_ekz.s S, spasoi_ekz.spj SPJ2
WHERE SPJ2.nomer_postavshika = S.nomer_postavshika
  AND NOT EXISTS (
                  SELECT *
                  FROM spasoi_ekz.p P, spasoi_ekz.spj SPJ
                  WHERE SPJ.nomer_detali = P.nomer_detali
                    AND SPJ.nomer_postavshika = SPJ2.nomer_postavshika
                    AND P.cvet != 'белый'
                 );

Результат:

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

Билет 21

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

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

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

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
                 );

Вариант покороче:

SELECT j1.nazvanie FROM spasoi_ekz.j j1
WHERE NOT EXISTS(
  SELECT * FROM spasoi_ekz.spj s1
  JOIN spasoi_ekz.j j2 ON s1.nomer_izdelia = j2.nomer_izdelia
  WHERE (s1.nomer_postavshika = 'S18' 
        AND j2.nomer_izdelia != j1.nomer_izdelia)
  OR    (s1.nomer_postavshika != 'S18' 
        AND j2.nomer_izdelia = j1.nomer_izdelia)
);

Результат:

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

Билет 22

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

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

SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
                          ON SPJ.nomer_izdelia = J.nomer_izdelia
                             AND J.nazvanie = LOWER('Штуцер 01-02')
                        JOIN spasoi_ekz.s S
                          ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE NOT EXISTS (
                  SELECT *
                  FROM spasoi_ekz.spj X JOIN spasoi_ekz.j J
                                          ON X.nomer_izdelia = J.nomer_izdelia
                  WHERE X.nomer_detali != 'P1' 
                    AND J.nazvanie = LOWER('Штуцер 01-02')
                    AND X.nomer_postavshika = S.nomer_postavshika
                 );

Этот же запрос, но длиннее и нерациональней

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'
                              );

Результат:

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

Билет 23

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

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

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
                     );

Результат:

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

Билет 24

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

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

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

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

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

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;

Результат:

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

То же, что выше, но для всех городов:

SELECT gorod, nazvanie, SUM(kolichestvo) FROM spasoi_ekz.spj
JOIN spasoi_ekz.p ON spj.nomer_detali = nomer_detali
GROUP BY gorod, nazvanie
ORDER BY gorod;

Билет 25

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

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

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
                    );

Результат:

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

Билет 26

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

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

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;

Этот же запрос через JOIN

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;

Результат:

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

Билет 27

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


1st dufficulty.png


Сложнейший запрос экзамена!

Сохрани Джа вытащить такое


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

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;

Результат:

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

Билет 28

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

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

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'
                );

Результат:

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

Билет 29

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

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

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

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
                        )
                 );

Ещё один вариант этого же запроса

SELECT nazvanie
FROM spasoi_ekz.p P1
WHERE NOT EXISTS (
                  SELECT *
                  FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P2
                                            ON SPJ.nomer_detali = P.nomer_detali
                                          JOIN spasoi_ekz.j J
                                            ON J.nomer_izdelia = SPJ.nomer_izdelia
                  WHERE (
                         P1.nazvanie = P2.nazvanie AND nazvanie!='Штуцер'
                         )
                  OR (
                         P1.nazvanie != P2.nazvanie AND nazvanie='Штуцер'
                 );

Результат:

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

Билет 30

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

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

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
                                   )
                 );

Результат:

 imya
------------
 Оша
 Бран Старк
(2 rows)