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

Материал из Кафедра ИУ5 МГТУ им. Н.Э.Баумана, студенческое сообщество
Перейти к навигации Перейти к поиску
м (→‎Билет 18: ещё фэн-шуй)
 
(не показано 165 промежуточных версий 18 участников)
Строка 6: Строка 6:


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


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


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


=== Таблицы БД ===
=== Таблицы БД ===
Строка 35: Строка 37:
   S10              | Русе Болтон      |      44444 | Баренцбург
   S10              | Русе Болтон      |      44444 | Баренцбург
   S11              | Петров Иван      |      35000 | Мытищи
   S11              | Петров Иван      |      35000 | Мытищи
  S12              | Петров Пётр      |      35000 | Мытищи
   S14              | Рендилл Тарли    |    1111111 | Прага
   S14              | Рендилл Тарли    |    1111111 | Прага
   S13              | Сэмвелл Тарли    |    999999 | Прага
   S13              | Сэмвелл Тарли    |    999999 | Прага
Строка 41: Строка 42:
   S16              | Ходор            |            | Москва
   S16              | Ходор            |            | Москва
   S15              | Мелисса Флорент  |    888888 | Стокгольм
   S15              | Мелисса Флорент  |    888888 | Стокгольм
  (16 rows)
  S12              | Петров Пётр      |      35001 | Мытищи
  S17              | Томми Версетти  |  123456789 | Майами
  S18              | Безликий        |          1 | Йокогама
  (18 rows)


==== Детали ====
==== Детали ====
Строка 55: Строка 59:
   P11          | абажур              | синий        |  400 | Нижний Новгород
   P11          | абажур              | синий        |  400 | Нижний Новгород
   P1          | гайка                | чёрный        |  20 | Лондон
   P1          | гайка                | чёрный        |  20 | Лондон
  P2          | шуруп                | чёрный        |    5 | Лондон
   P3          | ось                  | белый        | 5000 | Эдинбург
   P3          | ось                  | белый        | 5000 | Эдинбург
   P4          | зубчатое колесо      | чёрный        |  50 | Эдинбург
   P4          | зубчатое колесо      | чёрный        |  50 | Эдинбург
Строка 68: Строка 71:
   P5          | втулка              | серый        |  350 | Манчестер
   P5          | втулка              | серый        |  350 | Манчестер
   P17          | бумага              | белый        |    1 | Астрахань
   P17          | бумага              | белый        |    1 | Астрахань
  (17 rows)
  P18          | плитка              | белый        |  300 | Флоренция
  P19          | орнамент            | серый        |  800 | Флоренция
  P20          | уголок              | серый        |  100 | Флоренция
  P21          | гайка 01-01          | серебристый  |  20 | Клин
  P22          | усиленная рама      | красный      | 3200 | Череповец
  P23          | винт                | розовый      |    5 | Ижевск
  P24          | труселя              | красный      |  20 | Челябинск
  P25          | штуцерная деталь    | коричневый    |  450 | Череповец
  P2          | шуруп                | чёрный        |    5 | Лондон
  P26          | лезвие              | бесцветный    |  120 | Йокогама
  (26 rows)


==== Изделия ====
==== Изделия ====
Строка 88: Строка 101:
   J9            | рама 02-01            | Череповец
   J9            | рама 02-01            | Череповец
   J10          | книга                | Астрахань
   J10          | книга                | Астрахань
  (10 rows)
  J11          | панно 01-03          | Флоренция
  J12          | велосипед 03-04      | Клин
  J13          | рама 02-03            | Череповец
  J14          | штуцер 01-02          | Череповец
  J15          | велосипед 01-04      | Клин
  J16          | кружевное бельё      | Челябинск
  J17          | штуцер 01-03          | Череповец
  J18          | кинжал                | Йокогама
  (18 rows)


==== Сборки ====
==== Сборки ====
Строка 126: Строка 147:
   S3                | P5          | J6            |          10
   S3                | P5          | J6            |          10
   S10              | P2          | J8            |          4
   S10              | P2          | J8            |          4
  S9                | P14          | J8            |          25
   S8                | P1          | J7            |          16
   S8                | P1          | J7            |          16
   S9                | P14          | J7            |          3
   S9                | P14          | J7            |          3
Строка 136: Строка 156:
   S14              | P17          | J10          |        1111
   S14              | P17          | J10          |        1111
   S15              | P17          | J10          |        1010
   S15              | P17          | J10          |        1010
  (38 rows)
  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 ===
Строка 164: Строка 221:
</syntaxhighlight>
</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> -->
Результат:
Результат:


Строка 190: Строка 280:
<div class="mw-collapsible-content">
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
SELECT nomer_detali AS "Номер детали", kol AS "Сколько штук поставляется" FROM (
SELECT nomer_detali AS "Номер детали",
      SELECT nomer_detali,
      kol AS "Сколько штук поставляется"
              SUM(kolichestvo) AS kol,
FROM (
              COUNT(DISTINCT nomer_postavshika)
      SELECT nomer_detali,
      FROM spasoi_ekz.spj
            SUM(kolichestvo) AS kol,
      GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_postavshika) > 1
            COUNT(DISTINCT nomer_postavshika)
    ) A;
      FROM spasoi_ekz.spj
      GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_postavshika) > 1
    ) A;
</syntaxhighlight>
</syntaxhighlight>
</div>
</div>
Строка 203: Строка 295:
Результат:
Результат:


   Номер детали | Сколько штук поставляется  
   Номер детали | Сколько штук поставляется | Сколько у неё поставщиков
  --------------+---------------------------
  --------------+---------------------------+---------------------------
   P1          |          15
   P1          |                       71 |                        5
   P12          |          7  
  P10          |                        3 |                        2
   P4          |         10  
  P11         |                        5 |                        2
  (3 rows)
   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 ===
=== Билет 3 ===
Строка 217: Строка 318:


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


Строка 285: Строка 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
Строка 299: Строка 412:
                           );
                           );
</syntaxhighlight>
</syntaxhighlight>
</div>
</div>


Результат:
Результат:
Строка 305: Строка 420:
  ---------------
  ---------------
   J5
   J5
  (1 row)
  J16
  (2 rows)


=== Билет 5 ===
=== Билет 5 ===
Строка 322: Строка 438:
WHERE kolichestvo > (
WHERE kolichestvo > (
                     SELECT AVG(kolichestvo)
                     SELECT AVG(kolichestvo)
                     FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
                     FROM spasoi_ekz.spj
                                              ON SPJ.nomer_detali = P.nomer_detali
                    WHERE nomer_detali = 'P1'
                                                  AND SPJ.nomer_detali = 'P1'
                     );
                     );
</syntaxhighlight>
</syntaxhighlight>


<div class="toccolours mw-collapsible mw-collapsed">
<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос, но без JOIN''
''Вариант запроса без 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">
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
Строка 366: Строка 499:
Текст запроса:
Текст запроса:


<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">
<syntaxhighlight lang="sql">
SELECT DISTINCT nazvanie
SELECT DISTINCT nazvanie
Строка 378: Строка 526:
                       );
                       );
</syntaxhighlight>
</syntaxhighlight>
</div>
</div>


Результат:
Результат:
Строка 393: Строка 543:
Текст запроса:
Текст запроса:


<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">
<syntaxhighlight lang="sql">
SELECT nazvanie
SELECT nazvanie
Строка 400: Строка 570:
                         FROM (spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
                         FROM (spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
                                                   ON SPJ.nomer_postavshika = S.nomer_postavshika
                                                   ON SPJ.nomer_postavshika = S.nomer_postavshika
                                                       AND imya = 'Иванов'
                                                       AND imya LIKE '%Иванов%'
                                                 JOIN spasoi_ekz.p P
                                                 JOIN spasoi_ekz.p P
                                                   ON SPJ.nomer_detali = P.nomer_detali
                                                   ON SPJ.nomer_detali = P.nomer_detali
Строка 411: Строка 581:
                       );
                       );
</syntaxhighlight>
</syntaxhighlight>
</div>
</div>


<div class="toccolours mw-collapsible mw-collapsed">
<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос, но длиннее и нерациональней''
''И ещё вариант этого же запроса, но длиннее и нерациональней''
<div class="mw-collapsible-content">
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
Строка 423: Строка 595:
         AND P.nomer_detali = SPJ.nomer_detali
         AND P.nomer_detali = SPJ.nomer_detali
         AND P.nazvanie = LOWER('Болт')
         AND P.nazvanie = LOWER('Болт')
         AND S.imya = 'Иванов'
         AND S.imya LIKE '%Иванов%'
         AND S.nomer_postavshika = SPJ.nomer_postavshika
         AND S.nomer_postavshika = SPJ.nomer_postavshika
     ) A
     ) A
Строка 436: Строка 608:
                           AND P.nomer_detali = SPJ.nomer_detali
                           AND P.nomer_detali = SPJ.nomer_detali
                           AND P.nazvanie = LOWER('Болт')
                           AND P.nazvanie = LOWER('Болт')
                           AND S.imya = 'Иванов'
                           AND S.imya LIKE '%Иванов%'
                           AND S.nomer_postavshika = SPJ.nomer_postavshika
                           AND S.nomer_postavshika = SPJ.nomer_postavshika
                       )
                       )
Строка 458: Строка 630:


<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
SELECT nomer_izdelia, SUM(kolichestvo)
SELECT nomer_izdelia AS "Номер изделия", SUM(kolichestvo) AS "Количество деталей"
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
                           ON SPJ.nomer_postavshika = S.nomer_postavshika
                           ON SPJ.nomer_postavshika = S.nomer_postavshika
Строка 469: Строка 641:
Результат:
Результат:


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


=== Билет 9 ===
=== Билет 9 ===
Строка 503: Строка 680:
Результат:
Результат:


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


=== Билет 10 ===
=== Билет 10 ===
Строка 536: Строка 722:
WHERE kolichestvo > (
WHERE kolichestvo > (
    SELECT MIN(kolichestvo)
    SELECT MIN(kolichestvo)
    FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
    FROM spasoi_ekz.spj  
      ON SPJ.nomer_detali = P.nomer_detali
    WHERE nomer_detali = 'P1'
          AND P.nomer_detali = 'P1'
                     );
                     );
</syntaxhighlight>
</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>
Результат:
Результат:


Строка 555: Строка 763:
Текст запроса:
Текст запроса:


<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">
<syntaxhighlight lang="sql">
SELECT gorod, SUM(sostoyanie)
SELECT gorod, SUM(sostoyanie)
Строка 565: Строка 787:
GROUP BY gorod;
GROUP BY gorod;
</syntaxhighlight>
</syntaxhighlight>
<!-- этот запрос выполняет не совсем то и не правильно
</div>
</div>
 
<div class="toccolours mw-collapsible mw-collapsed">
''И ещё три неправильных варианта ''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
<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
SELECT gorod, sost
FROM (
FROM (
SELECT gorod, SUM(sostoyanie) AS sost, SUM(SPJ.kolichestvo)
      SELECT gorod, SUM(sostoyanie) AS sost, SUM(SPJ.kolichestvo)
FROM spasoi_ekz.spj, spasoi_ekz.s
      FROM spasoi_ekz.spj, spasoi_ekz.s
WHERE S.nomer_postavshika = SPJ.nomer_postavshika
      WHERE S.nomer_postavshika = SPJ.nomer_postavshika
GROUP BY S.gorod HAVING SUM(SPJ.kolichestvo) > 1000
      GROUP BY S.gorod HAVING SUM(SPJ.kolichestvo) > 1000
) A;
    ) A;
</syntaxhighlight> -->  
</syntaxhighlight>
</div>
</div>


Результат:
Результат:
Строка 604: Строка 849:
   P15
   P15
   P16
   P16
  (2 rows)
  P26
  (3 rows)


=== Билет 13 ===
=== Билет 13 ===
Строка 613: Строка 859:


<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
SELECT COUNT(nomer_postavshika) - COUNT(sostoyanie) AS "С пустым состоянием",  
SELECT COUNT(*) - COUNT(sostoyanie) AS "С пустым состоянием",  
       COUNT(sostoyanie) AS "С не пустым состоянием"
       COUNT(sostoyanie) AS "С не пустым состоянием"
FROM spasoi_ekz.s;
FROM spasoi_ekz.s;
Строка 635: Строка 881:
   С пустым состоянием | С не пустым состоянием
   С пустым состоянием | С не пустым состоянием
  ---------------------+-----------------------
  ---------------------+-----------------------
                     2 |                    14
                     2 |                    16
  (1 row)
  (1 row)


Строка 642: Строка 888:
Написать запрос SELECT: выдать имена поставщиков, которые поставляют детали только и только для изделия с номером 'J1'.
Написать запрос 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">
<syntaxhighlight lang="sql">
SELECT imya
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
FROM spasoi_ekz.spj A JOIN spasoi_ekz.s S
                           ON SPJ.nomer_postavshika = S.nomer_postavshika
                           ON A.nomer_postavshika = S.nomer_postavshika
WHERE nomer_izdelia = 'J1'
WHERE nomer_izdelia = 'J1'
   AND SPJ.nomer_postavshika NOT IN (
   AND NOT EXISTS (
                                    SELECT nomer_postavshika
                  SELECT nomer_postavshika
                                    FROM spasoi_ekz.spj
                  FROM spasoi_ekz.spj
                                    WHERE nomer_izdelia != 'J1'
                  WHERE nomer_izdelia != 'J1'
                                  );
                    AND nomer_postavshika = A.nomer_postavshika
</syntaxhighlight>
                );
</syntaxhighlight> -->


Результат:
Результат:


    imya
      imya
  -------------
  ----------------
   Петров Пётр
   Безликий
  (1 row)
  (1 row)


Строка 670: Строка 948:


Текст запроса:
Текст запроса:
<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">
<syntaxhighlight lang="sql">
SELECT DISTINCT nazvanie
SELECT DISTINCT nazvanie
Строка 685: Строка 997:
                               );
                               );
</syntaxhighlight>
</syntaxhighlight>
</div>
</div>


Результат:
Результат:
Строка 690: Строка 1004:
       nazvanie
       nazvanie
  --------------------
  --------------------
  кружевное бельё
  уникальное изделие
   процессор
   процессор
  уникальное изделие
  (3 rows)
  (2 rows)


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


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


Результат:
Результат:
Строка 738: Строка 1071:


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


<div class="toccolours mw-collapsible mw-collapsed">
<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос немного попроще''
''Ещё вариант запроса''
<div class="mw-collapsible-content">
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
Строка 762: Строка 1089:
                           )
                           )
GROUP BY nomer_postavshika;
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>
</syntaxhighlight>
</div>
</div>
Строка 779: Строка 1140:


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


Текст запроса:
Текст запроса:
Строка 797: Строка 1167:
                     FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
                     FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
                       ON SPJ.nomer_postavshika = S.nomer_postavshika  
                       ON SPJ.nomer_postavshika = S.nomer_postavshika  
                     WHERE S.imya = 'Иванов'
                     WHERE S.imya = 'Иванов' -- или WHERE S.imya LIKE '%Иванов%',
                    -- поскольку сказано, что поставка выполнена поставщиками с именем Иванов,
                    -- а это могут быть разные имена и отчества. Короче, опять великий
                    -- русский язык в задании - что ещё за "с именем Иванов"
                     );
                     );
</syntaxhighlight>
</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>


Результат:
Результат:
Строка 809: Строка 1207:


=== Билет 19 ===
=== Билет 19 ===
Написать запрос SELECT: выдать названия красных деталей, которые входят только в изделие с названием "Рама 02-03" в количестве меньшем 10 единиц в поставке.
 
Написать запрос SELECT: выдать названия красных деталей, которые входят только в изделие с названием 'Рама 02-03' в количестве, меньшем 10 единиц в поставке.
 
Примечание: Григорьев ругается на X.nomer_detali = SPJ.nomer_detali Лучше во вложенном подзапросе сделать еще один джойн p и приравнивать названия деталей.  


Текст запроса:
Текст запроса:
<syntaxhighlight lang="sql">
<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>


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


=== Билет 20 ===
=== Билет 20 ===
Написать запрос SELECT: выдать имена поставщиков, поставляющих только белые детали.
Написать запрос 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">
<syntaxhighlight lang="sql">
SELECT imya FROM S X
SELECT imya
JOIN SPJ ON SPJ.nomer_postavshika = X.nomer_postavshika
FROM spasoi_ekz.s
JOIN P ON P.nomer_detali = SPJ.nomer_detali
WHERE nomer_postavshika NOT IN  (
WHERE P.cvet = 'белый'
                                SELECT spj.nomer_postavshika
AND NOT EXISTS (SELECT nomer_postavshika FROM SPJ
                                FROM spasoi_ekz.spj SPJ, spasoi_ekz.p P
                JOIN P ON P.nomer_detali = SPJ.nomer_detali
                                WHERE SPJ.nomer_detali = P.nomer_detali
                WHERE nomer_postavshika = X.nomer_postavshika
                                  AND p.cvet != 'белый'
              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>
</syntaxhighlight>
</div>
</div>
Результат:
      imya
-----------------
  Рендилл Тарли
  Сэмвелл Тарли
  Мелисса Флорент
  Томми Версетти
(4 rows)


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


Написать запрос SELECT: выдать названия изделий, для которых детали поставляет только и только поставщик с номером ‘S1’.
Написать запрос SELECT: выдать названия изделий, для которых детали поставляет только и только поставщик с номером 'S1'.
 
Чтобы продемонстрировать выполнение запроса наглядно, возьмём поставщика не 'S1', а 'S18', который был создан специально для этого запроса. Если оставить 'S1', то наглядности не получится, так как по нашей схеме БД этот поставщик не попадает под условие "''только и только''", и запрос вернёт пустой результат.
 
Примечание: Григорьев ругается на nomer_izdelia = J.nomer_izdelia Лучше во вложенном подзапросе сделать еще один джойн j и приравнивать названия изделий, а не номера.
Текст запроса:
Текст запроса:
<syntaxhighlight lang="sql">
<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>


SELECT nazvanie FROM J X, SPJ
Вариант покороче:
WHERE X.nomer_izdelia = SPJ.nomer_izdelia  
 
AND NOT EXISTS (SELECT nomer_postavshika FROM SPJ
<syntaxhighlight lang="sql">
                WHERE nomer_postavshika != 'S1'
SELECT j1.nazvanie FROM spasoi_ekz.j j1
                AND nomer_izdelia=X.nomer_izdelia);
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>
</syntaxhighlight>
Результат:
      nazvanie
--------------------
  кинжал
(1 row)


=== Билет 22 ===
=== Билет 22 ===
Написать запрос SELECT: выдать имена поставщиков, которые поставляют только детали с номером 'P1' для изделия с именем ‘Штуцер 01-02‘
 
Написать запрос SELECT: выдать имена поставщиков, которые поставляют только детали с номером 'P1' для изделия с именем 'Штуцер 01-02'.
 
Текст запроса:
Текст запроса:
<syntaxhighlight lang="sql">
<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
SELECT imya FROM S X
JOIN SPJ Y ON X.nomer_postavshika=Y.nomer_postavshika
JOIN SPJ Y ON X.nomer_postavshika=Y.nomer_postavshika
Строка 865: Строка 1421:
                   SELECT DISTINCT nomer_postavshika FROM SPJ Z
                   SELECT DISTINCT nomer_postavshika FROM SPJ Z
                   WHERE Z.nomer_izdelia != 'Штуцер 01-02'
                   WHERE Z.nomer_izdelia != 'Штуцер 01-02'
                   AND Z.nomer_detali!='P1');
                   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>
</syntaxhighlight>
</div>
</div>
Результат:
    imya
-------------
  Петров Иван
(1 row)


=== Билет 23 ===
=== Билет 23 ===
Написать запрос SELECT: выдать имена поставщиков, которые поставляют деталь с названием "Винт" в количестве большим 100 единиц в одной поставке и имеют состояние больше среднего по их родному городу.
 
Написать запрос SELECT: выдать имена поставщиков, которые поставляют деталь с названием 'Винт' в количестве большим 100 единиц в одной поставке и имеют состояние больше среднего по их родному городу.
 
Текст запроса:
Текст запроса:
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
SELECT X.imya FROM S X
SELECT S.imya
JOIN SPJ Y ON X.nomer_postavshika=Y.nomer_postavshika
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
JOIN P Z ON Z.nomer_detali = Y.nomer_detali
                      ON S.nomer_postavshika = SPJ.nomer_postavshika
WHERE Z.nazvanie = 'Винт'
                    JOIN spasoi_ekz.p P
AND Y.kolichestvo > 100
                      ON P.nomer_detali = SPJ.nomer_detali
AND X.sostoyanie > (SELECT AVG(K.sostoyanie) FROM S K
WHERE P.nazvanie = LOWER('Винт')
                    WHERE K.gorod = X.gorod);
  AND SPJ.kolichestvo > 100
  AND S.sostoyanie > (
                      SELECT AVG(SS.sostoyanie)
                      FROM spasoi_ekz.s SS
                      WHERE SS.gorod = S.gorod
                    );
</syntaxhighlight>
</syntaxhighlight>
Результат:
    imya
-------------
  Петров Пётр
(1 row)


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


Написать запрос SELECT: выдать наименования деталей и общее их количество для всех наименований деталей,изготавливаемых в одном городе.
Написать запрос 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">
<syntaxhighlight lang="sql">
SELECT nazvanie, kol FROM P A, (
SELECT nazvanie, SUM(kolichestvo)
                                SELECT X.gorod, SUM(kolichestvo) as kol FROM P X, SPJ Y
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p
                                WHERE Y.nomer_detali = X.nomer_detali
                          ON SPJ.nomer_detali = P.nomer_detali
                                GROUP BY X.gorod
WHERE gorod = 'Лондон'
                                )B
GROUP BY nazvanie;
WHERE A.gorod = B.gorod
</syntaxhighlight>
</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’ с объёмом поставки большим, чем средний объём поставки этого поставщика.
Написать запрос SELECT: выдать имена поставщиков, поставляющих хотя бы одну белую деталь для изделия с названием 'Велосипед 01-04' с объёмом поставки большим, чем средний объём поставки этого поставщика.


Текст запроса:
Текст запроса:
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
SELECT imya FROM S X
SELECT imya
JOIN SPJ ON SPJ.nomer_postavshika = X.nomer_postavshika
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
JOIN P ON P.nomer_detali = SPJ.nomer_detali
                      ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN J ON J.nomer_izdelia = SPJ.nomer_izdelia
                    JOIN spasoi_ekz.p P
WHERE P.cvet = 'белый'
                      ON P.nomer_detali = SPJ.nomer_detali
AND J.nazvanie = 'Велосипед 01-04'
                    JOIN spasoi_ekz.j J
AND kolichestvo > (
                      ON J.nomer_izdelia = SPJ.nomer_izdelia
                  SELECT AVG(kolichestvo)
WHERE cvet = 'белый'
                  FROM SPJ
  AND J.nazvanie = LOWER('Велосипед 01-04')
                  WHERE SPJ.nomer_postavshika = X.nomer_postavshika);
  AND kolichestvo > (
                    SELECT AVG(kolichestvo)
                    FROM spasoi_ekz.spj SPJ
                    WHERE SPJ.nomer_postavshika = S.nomer_postavshika
                    );
</syntaxhighlight>
</syntaxhighlight>
Результат:
    imya
-------------
  Петров Пётр
(1 row)


=== Билет 26 ===
=== Билет 26 ===
Строка 921: Строка 1611:


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


SELECT S.gorod, AVG(S.sostoyanie) FROM S
JOIN SPJ ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN P ON P.nomer_detali = SPJ.nomer_detali
JOIN J ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE P.nazvanie = 'Гайка 01-01'
AND J.nazvanie = 'Велосипед 03-04'
AND kolichestvo > (
                  SELECT MIN(kolichestvo)
                  FROM SPJ
                  WHERE SPJ.nomer_postavshika = 'S1')
GROUP BY S.gorod;
</syntaxhighlight>
</syntaxhighlight>
Результат:
    Город  |  Среднее состояние
-----------+-----------------------
  Мытищи    |    35000.500000000000
  Йокогама  |  1500000.000000000000
  Манчестер | 2571.0000000000000000
(3 rows)


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


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


Текст запроса:
Текст запроса:
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
SELECT X.nazvanie FROM J X
SELECT J.nazvanie
JOIN SPJ Y ON X.nomer_izdelia = Y.nomer_izdelia
FROM spasoi_ekz.j J JOIN spasoi_ekz.spj SPJ1
JOIN P Z ON Z.nomer_detali = Y.nomer_detali
                      ON J.nomer_izdelia = SPJ1.nomer_izdelia
WHERE Z.ves > 10
                    JOIN spasoi_ekz.p P
AND Z.cvet = 'красный'
                      ON P.nomer_detali = SPJ1.nomer_detali
AND NOT EXISTS (
WHERE P.ves > 10
                SELECT K.nomer_postavshika FROM S K
AND P.cvet = 'красный'
                JOIN SPJ L ON K.nomer_postavshika = L.nomer_postavshika
AND NOT EXISTS (
                WHERE L.nomer_detali = Z.nomer_detali
                SELECT SPJ2.nomer_postavshika
AND K.nomer_postavshika!='S1');                     
                FROM spasoi_ekz.spj SPJ2       
                WHERE SPJ2.nomer_detali = P.nomer_detali
  AND SPJ2.nomer_postavshika != 'S1'
                );                     
</syntaxhighlight>
</syntaxhighlight>
Результат:
    nazvanie
-----------------
  кружевное бельё
(1 row)


=== Билет 29 ===
=== Билет 29 ===
Написать запрос SELECT: выдать названия деталей, которые входят только и только в состав изделия с названием ‘Штуцер 01-03’.
Написать запрос SELECT: выдать названия деталей, которые входят только и только в состав изделия с названием 'Штуцер 01-03'.
 
Примечание: Григорьев ругается на SPJ.nomer_detali = P.nomer_detali Лучше во вложенном подзапросе сделать еще один джойн p и приравнивать названия деталей.  


Текст запроса:
Текст запроса:
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
SELECT nazvanie FROM P X, SPJ Y
SELECT DISTINCT nazvanie
WHERE X.nomer_detali = Y.nomer_detali
FROM spasoi_ekz.p
AND NOT EXISTS (SELECT Z.nomer_izdelia FROM SPJ Z
WHERE NOT EXISTS (
                JOIN J K ON K.nomer_izdelia = Z.nomer_izdelia
                  SELECT SPJ.nomer_izdelia
                WHERE K.nazvanie != 'Штуцер 01-03'
                  FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
                AND Z.nomer_detali=X.nomer_detali);                    
                                            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>
</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 ===
Строка 997: Строка 1789:
                 );
                 );
</syntaxhighlight>
</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> -->
Результат:
Результат:



Текущая версия от 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' в количестве (в поставке) большим, чем средний объём поставки, выполненной поставщиками с именем 'Иванов'.



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

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


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

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



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

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


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

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)