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

Материал из Кафедра ИУ5 МГТУ им. Н.Э.Баумана - студенческое сообщество
Перейти к: навигация, поиск
м (Билет 14: изменённый запрос про "только и только")
(Билет 21)
 
(не показано 119 промежуточных версий 17 участников)
Строка 7: Строка 7:
 
На этой странице собраны все сформированные запросы по билетам.
 
На этой странице собраны все сформированные запросы по билетам.
  
Странно, что ни в одном билете нет запроса с сортировкой результатов. Возможно, они буду в дополнительных заданиях.
+
Странно, что ни в одном билете нет запроса с сортировкой результатов. <s>Возможно, они будут в дополнительных заданиях.</s> Григорьев сказал, что это слишком просто и потому он не стал загромождать запросы дополнительными мелочами.
  
 
== Схема БД ==
 
== Схема БД ==
Строка 15: Строка 15:
 
Для написания запросов и проверки их выполнения создана БД в СУБД [http://www.postgresql.org/ PostgreSQL].
 
Для написания запросов и проверки их выполнения создана БД в СУБД [http://www.postgresql.org/ PostgreSQL].
  
Скрипт создания можно загрузить [http://yadi.sk/d/MmOgbWnr5cATO отсюда].
+
Скрипт создания можно загрузить [http://yadi.sk/d/ArwqOGAy5pPfi отсюда].
  
 
=== Таблицы БД ===
 
=== Таблицы БД ===
Строка 44: Строка 44:
 
   S12              | Петров Пётр      |      35001 | Мытищи
 
   S12              | Петров Пётр      |      35001 | Мытищи
 
   S17              | Томми Версетти  |  123456789 | Майами
 
   S17              | Томми Версетти  |  123456789 | Майами
  (17 rows)
+
  S18              | Безликий        |          1 | Йокогама
 +
  (18 rows)
  
 
==== Детали ====
 
==== Детали ====
Строка 79: Строка 80:
 
   P25          | штуцерная деталь    | коричневый    |  450 | Череповец
 
   P25          | штуцерная деталь    | коричневый    |  450 | Череповец
 
   P2          | шуруп                | чёрный        |    5 | Лондон
 
   P2          | шуруп                | чёрный        |    5 | Лондон
  (25 rows)
+
  P26          | лезвие              | бесцветный    |  120 | Йокогама
 +
  (26 rows)
  
 
==== Изделия ====
 
==== Изделия ====
Строка 106: Строка 108:
 
   J16          | кружевное бельё      | Челябинск
 
   J16          | кружевное бельё      | Челябинск
 
   J17          | штуцер 01-03          | Череповец
 
   J17          | штуцер 01-03          | Череповец
  (17 rows)
+
  J18          | кинжал                | Йокогама
 +
  (18 rows)
  
 
==== Сборки ====
 
==== Сборки ====
Строка 171: Строка 174:
 
   S4                | P25          | J17          |          1
 
   S4                | P25          | J17          |          1
 
   S17              | P16          | J1            |          4
 
   S17              | P16          | J1            |          4
  (55 rows)
+
  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 ===
Строка 199: Строка 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> -->
 
Результат:
 
Результат:
  
Строка 329: Строка 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
Строка 343: Строка 412:
 
                           );
 
                           );
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
</div>
 +
</div>
  
 
Результат:
 
Результат:
Строка 367: Строка 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">
Строка 411: Строка 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
Строка 423: Строка 526:
 
                       );
 
                       );
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
</div>
 +
</div>
  
 
Результат:
 
Результат:
Строка 438: Строка 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
Строка 445: Строка 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
Строка 456: Строка 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">
Строка 468: Строка 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
Строка 481: Строка 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
 
                       )
 
                       )
Строка 568: Строка 695:
 
   P24          |    1
 
   P24          |    1
 
   P25          |    1
 
   P25          |    1
 +
  P26          |    1
 
   P3          |  50
 
   P3          |  50
 
   P4          |  10
 
   P4          |  10
Строка 574: Строка 702:
 
   P8          |  25
 
   P8          |  25
 
   P9          |    1
 
   P9          |    1
  (19 rows)
+
  (20 rows)
  
 
=== Билет 10 ===
 
=== Билет 10 ===
Строка 594: Строка 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>
 
Результат:
 
Результат:
  
Строка 613: Строка 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)
Строка 623: Строка 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>
  
 
Результат:
 
Результат:
Строка 662: Строка 849:
 
   P15
 
   P15
 
   P16
 
   P16
  (2 rows)
+
  P26
 +
  (3 rows)
  
 
=== Билет 13 ===
 
=== Билет 13 ===
Строка 671: Строка 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;
Строка 693: Строка 881:
 
   С пустым состоянием | С не пустым состоянием
 
   С пустым состоянием | С не пустым состоянием
 
  ---------------------+-----------------------
 
  ---------------------+-----------------------
                     2 |                    15
+
                     2 |                    16
 
  (1 row)
 
  (1 row)
  
Строка 699: Строка 887:
  
 
Написать запрос 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
Строка 713: Строка 934:
 
                     AND nomer_postavshika = A.nomer_postavshika
 
                     AND nomer_postavshika = A.nomer_postavshika
 
                 );
 
                 );
</syntaxhighlight>
+
</syntaxhighlight> -->
  
 
Результат:
 
Результат:
Строка 719: Строка 940:
 
       imya
 
       imya
 
  ----------------
 
  ----------------
   Томми Версетти
+
   Безликий
 
  (1 row)
 
  (1 row)
  
Строка 727: Строка 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
Строка 742: Строка 997:
 
                               );
 
                               );
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
</div>
 +
</div>
  
 
Результат:
 
Результат:
Строка 766: Строка 1023:
 
GROUP BY cvet;
 
GROUP BY cvet;
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
 +
<div class="toccolours mw-collapsible mw-collapsed">
 +
''Другой вариант запроса для тонко чувствующих натур''
 +
<div class="mw-collapsible-content">
 +
<syntaxhighlight lang="sql">
 +
SELECT cvet AS "Цвет", SUM(kolichestvo) AS "Деталей"
 +
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
 +
                          ON SPJ.nomer_detali = P.nomer_detali
 +
JOIN spasoi_ekz.j J
 +
  ON SPJ.nomer_izdelia = J.nomer_izdelia
 +
WHERE J.nazvanie = LOWER('Панно 01-03')
 +
GROUP BY cvet;
 +
</syntaxhighlight>
 +
</div>
 +
</div>
 +
 +
<div class="toccolours mw-collapsible mw-collapsed">
 +
''И ещё один вариант запроса от ненавистника JOIN''
 +
<div class="mw-collapsible-content">
 +
<syntaxhighlight lang="sql">
 +
SELECT P.cvet, SUM(SPJ.kolichestvo)
 +
FROM spasoi_ekz.p P,
 +
    spasoi_ekz.spj SPJ,
 +
    spasoi_ekz.j J
 +
WHERE P.nomer_detali = SPJ.nomer_detali
 +
  AND J.nomer_izdelia = SPJ.nomer_izdelia
 +
  AND J.nazvanie = LOWER('Панно 01-03')
 +
GROUP BY P.cvet;
 +
</syntaxhighlight>
 +
</div>
 +
</div>
 +
  
 
Результат:
 
Результат:
Строка 782: Строка 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">
Строка 806: Строка 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>
Строка 850: Строка 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>
  
 
Результат:
 
Результат:
Строка 864: Строка 1209:
  
 
Написать запрос SELECT: выдать названия красных деталей, которые входят только в изделие с названием 'Рама 02-03' в количестве, меньшем 10 единиц в поставке.
 
Написать запрос SELECT: выдать названия красных деталей, которые входят только в изделие с названием 'Рама 02-03' в количестве, меньшем 10 единиц в поставке.
 +
 +
Примечание: Григорьев ругается на X.nomer_detali = SPJ.nomer_detali Лучше во вложенном подзапросе сделать еще один джойн p и приравнивать названия деталей.
  
 
Текст запроса:
 
Текст запроса:
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
SELECT X.nazvanie
+
SELECT DISTINCT X.nazvanie
 
FROM spasoi_ekz.p X JOIN spasoi_ekz.spj SPJ
 
FROM spasoi_ekz.p X JOIN spasoi_ekz.spj SPJ
 
                       ON SPJ.nomer_detali = X.nomer_detali
 
                       ON SPJ.nomer_detali = X.nomer_detali
                    JOIN spasoi_ekz.j J
 
                      ON J.nomer_izdelia = SPJ.nomer_izdelia
 
 
WHERE X.cvet = 'красный'
 
WHERE X.cvet = 'красный'
  AND J.nazvanie = LOWER('Рама 02-03')
 
 
   AND kolichestvo < 10
 
   AND kolichestvo < 10
 
   AND NOT EXISTS (
 
   AND NOT EXISTS (
                   SELECT J.nomer_izdelia
+
                   SELECT *
 
                   FROM spasoi_ekz.j J JOIN spasoi_ekz.spj SPJ
 
                   FROM spasoi_ekz.j J JOIN spasoi_ekz.spj SPJ
 
                                         ON SPJ.nomer_izdelia = J.nomer_izdelia
 
                                         ON SPJ.nomer_izdelia = J.nomer_izdelia
Строка 923: Строка 1267:
 
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
 
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
 
                       ON SPJ.nomer_postavshika = S.nomer_postavshika
 
                       ON SPJ.nomer_postavshika = S.nomer_postavshika
                    JOIN spasoi_ekz.p P
+
WHERE NOT EXISTS (
                      ON P.nomer_detali = SPJ.nomer_detali
+
                   SELECT *
WHERE cvet = 'белый'
+
  AND NOT EXISTS (
+
                   SELECT nomer_postavshika
+
 
                   FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
 
                   FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
 
                                             ON P.nomer_detali = SPJ.nomer_detali
 
                                             ON P.nomer_detali = SPJ.nomer_detali
Строка 954: Строка 1295:
 
</div>
 
</div>
 
</div>
 
</div>
 +
 
<div class="toccolours mw-collapsible mw-collapsed">
 
<div class="toccolours mw-collapsible mw-collapsed">
 
''И ещё один вариант этого же запроса''
 
''И ещё один вариант этого же запроса''
Строка 972: Строка 1314:
 
                               AND p.cvet = 'белый'
 
                               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>
Строка 990: Строка 1350:
 
Написать запрос SELECT: выдать названия изделий, для которых детали поставляет только и только поставщик с номером 'S1'.
 
Написать запрос SELECT: выдать названия изделий, для которых детали поставляет только и только поставщик с номером 'S1'.
  
 +
Чтобы продемонстрировать выполнение запроса наглядно, возьмём поставщика не 'S1', а 'S18', который был создан специально для этого запроса. Если оставить 'S1', то наглядности не получится, так как по нашей схеме БД этот поставщик не попадает под условие "''только и только''", и запрос вернёт пустой результат.
 +
 +
Примечание: Григорьев ругается на nomer_izdelia = J.nomer_izdelia Лучше во вложенном подзапросе сделать еще один джойн j и приравнивать названия изделий, а не номера.
 
Текст запроса:
 
Текст запроса:
  
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
 
SELECT nazvanie
 
SELECT nazvanie
FROM spasoi_ekz.j J, spasoi_ekz.SPJ SPJ
+
FROM spasoi_ekz.j
WHERE J.nomer_izdelia = SPJ.nomer_izdelia
+
WHERE NOT EXISTS (
  AND NOT EXISTS (
+
                   SELECT *
                   SELECT nomer_postavshika
+
 
                   FROM spasoi_ekz.spj
 
                   FROM spasoi_ekz.spj
                   WHERE nomer_postavshika != 'S1'
+
                   WHERE nomer_postavshika != 'S18'
 
                     AND nomer_izdelia = J.nomer_izdelia
 
                     AND nomer_izdelia = J.nomer_izdelia
 +
                )
 +
  AND NOT EXISTS (
 +
                  SELECT *
 +
                  FROM spasoi_ekz.spj
 +
                  WHERE nomer_postavshika = 'S18'
 +
                    AND nomer_izdelia != J.nomer_izdelia
 
                 );
 
                 );
 
 
</syntaxhighlight>
 
</syntaxhighlight>
  
<div class="toccolours mw-collapsible mw-collapsed">
+
Вариант покороче:
''Ещё один вариант запроса''
+
 
<div class="mw-collapsible-content">
+
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
SELECT nazvanie
+
SELECT j1.nazvanie FROM spasoi_ekz.j j1
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
+
WHERE NOT EXISTS(
                          ON SPJ.nomer_izdelia = J.nomer_izdelia
+
  SELECT * FROM spasoi_ekz.spj s1
WHERE nomer_postavshika = 'S1'
+
  JOIN spasoi_ekz.j j2 ON s1.nomer_izdelia = j2.nomer_izdelia
  AND SPJ.nomer_izdelia NOT IN (
+
  WHERE (s1.nomer_postavshika = 'S18'  
                                SELECT nomer_izdelia
+
        AND j2.nomer_izdelia != j1.nomer_izdelia)
                                FROM spasoi_ekz.spj
+
  OR    (s1.nomer_postavshika != 'S18'  
                                WHERE nomer_postavshika != 'S1'
+
        AND j2.nomer_izdelia = j1.nomer_izdelia)
                              );
+
);
 
</syntaxhighlight>
 
</syntaxhighlight>
</div>
 
</div>
 
  
 
Результат:
 
Результат:
Строка 1026: Строка 1390:
 
       nazvanie
 
       nazvanie
 
  --------------------
 
  --------------------
   уникальное изделие
+
   кинжал
  кружевное бельё
+
  (1 row)
  (2 rows)
+
  
 
=== Билет 22 ===
 
=== Билет 22 ===
Строка 1035: Строка 1398:
  
 
Текст запроса:
 
Текст запроса:
 +
<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>
  
 
<!-- неверный запрос - номер изделия, а не название
 
<!-- неверный запрос - номер изделия, а не название
Строка 1043: Строка 1422:
 
                   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">
 
<syntaxhighlight lang="sql">
 
SELECT imya
 
SELECT imya
 
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
 
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
 
                           ON SPJ.nomer_izdelia = J.nomer_izdelia
 
                           ON SPJ.nomer_izdelia = J.nomer_izdelia
                             AND J.nazvanie = LOWER('штуцер 01-02')
+
                             AND J.nazvanie = LOWER('Штуцер 01-02')
 
                         JOIN spasoi_ekz.p P
 
                         JOIN spasoi_ekz.p P
 
                           ON SPJ.nomer_detali = P.nomer_detali
 
                           ON SPJ.nomer_detali = P.nomer_detali
Строка 1057: Строка 1439:
 
                                     FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
 
                                     FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
 
                                                               ON SPJ.nomer_izdelia = J.nomer_izdelia
 
                                                               ON SPJ.nomer_izdelia = J.nomer_izdelia
                                                                 AND J.nazvanie = LOWER('штуцер 01-02')
+
                                                                 AND J.nazvanie = LOWER('Штуцер 01-02')
 
                                                             JOIN spasoi_ekz.p P
 
                                                             JOIN spasoi_ekz.p P
 
                                                               ON SPJ.nomer_detali = P.nomer_detali
 
                                                               ON SPJ.nomer_detali = P.nomer_detali
Строка 1063: Строка 1445:
 
                               );
 
                               );
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
</div>
 +
</div>
  
 
Результат:
 
Результат:
Строка 1102: Строка 1486:
 
Написать запрос SELECT: выдать наименования деталей и общее их количество для всех наименований деталей, изготавливаемых в одном городе.
 
Написать запрос SELECT: выдать наименования деталей и общее их количество для всех наименований деталей, изготавливаемых в одном городе.
  
С этим запросом возникла неожиданная проблема - задание то ли неполное, то ли неправильное, потому что нельзя однозначно сказать, что по нему требуется сделать.
+
<!-- С этим запросом возникла неожиданная проблема - задание то ли неполное, то ли неправильное, потому что нельзя однозначно сказать, что по нему требуется сделать.
  
 
Так что тут несколько вариантов запросов (кто как понял).
 
Так что тут несколько вариантов запросов (кто как понял).
Строка 1154: Строка 1538:
 
</div>
 
</div>
 
</div>
 
</div>
и
+
и -->
<div class="toccolours mw-collapsible mw-collapsed">
+
''Второй вариант запроса''
+
<div class="mw-collapsible-content">
+
 
В уточнение задания Григорьев сказал следующее: "''Следует учесть, что в качестве наименования города может выступать переменная, в которую в некоторой программе должно быть занесено конкретное значение перед выполнением запроса''".
 
В уточнение задания Григорьев сказал следующее: "''Следует учесть, что в качестве наименования города может выступать переменная, в которую в некоторой программе должно быть занесено конкретное значение перед выполнением запроса''".
  
Строка 1181: Строка 1562:
 
   шуруп    |  68
 
   шуруп    |  68
 
  (2 rows)
 
  (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>
 
</div>
 
</div>
Строка 1261: Строка 1652:
 
<p align="center"><font size="7px">'''Сложнейший запрос экзамена!'''</font></p>
 
<p align="center"><font size="7px">'''Сложнейший запрос экзамена!'''</font></p>
  
<p align="center"><font size="5px">'''Сохрани Джа, вытащить такое'''</font></p>
+
<p align="center"><font size="5px">'''Сохрани Джа вытащить такое'''</font></p>
  
  
Строка 1325: Строка 1716:
 
=== Билет 29 ===
 
=== Билет 29 ===
 
Написать запрос SELECT: выдать названия деталей, которые входят только и только в состав изделия с названием 'Штуцер 01-03'.
 
Написать запрос SELECT: выдать названия деталей, которые входят только и только в состав изделия с названием 'Штуцер 01-03'.
 +
 +
Примечание: Григорьев ругается на SPJ.nomer_detali = P.nomer_detali Лучше во вложенном подзапросе сделать еще один джойн p и приравнивать названия деталей.
  
 
Текст запроса:
 
Текст запроса:
  
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
SELECT nazvanie
+
SELECT DISTINCT nazvanie
FROM spasoi_ekz.p P, spasoi_ekz.spj SPJ
+
FROM spasoi_ekz.p
WHERE P.nomer_detali = SPJ.nomer_detali
+
WHERE NOT EXISTS (
  AND NOT EXISTS (
+
 
                   SELECT SPJ.nomer_izdelia
 
                   SELECT SPJ.nomer_izdelia
 
                   FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
 
                   FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
 
                                             ON J.nomer_izdelia = SPJ.nomer_izdelia
 
                                             ON J.nomer_izdelia = SPJ.nomer_izdelia
                   WHERE J.nazvanie != LOWER('Штуцер 01-03')
+
                   WHERE (
                    AND SPJ.nomer_detali = P.nomer_detali
+
                        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>
  
 
Результат:
 
Результат:
Строка 1367: Строка 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' в количестве (в поставке) большим, чем средний объём поставки, выполненной поставщиками с именем 'Иванов'.


2nd dufficulty.png


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

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


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

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

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

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

Результат:

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

Билет 19

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

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

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

SELECT DISTINCT X.nazvanie
FROM spasoi_ekz.p X JOIN spasoi_ekz.spj SPJ
                      ON SPJ.nomer_detali = X.nomer_detali
WHERE X.cvet = 'красный'
  AND kolichestvo < 10
  AND NOT EXISTS (
                  SELECT *
                  FROM spasoi_ekz.j J JOIN spasoi_ekz.spj SPJ
                                        ON SPJ.nomer_izdelia = J.nomer_izdelia
                  WHERE J.nazvanie != LOWER('Рама 02-03')
                    AND X.nomer_detali = SPJ.nomer_detali
                 );

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

SELECT P.nazvanie
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
                          ON SPJ.nomer_detali = P.nomer_detali
                             AND cvet = 'красный'
                        JOIN spasoi_ekz.j J
                          ON SPJ.nomer_izdelia = J.nomer_izdelia
                              AND J.nazvanie = LOWER('Рама 02-03')
WHERE kolichestvo < 10
  AND SPJ.nomer_detali NOT IN (
                               SELECT nomer_detali
                               FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
                                                         ON SPJ.nomer_izdelia = J.nomer_izdelia
                                                            AND J.nazvanie != LOWER('Рама 02-03')
                              );

Результат:

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

Билет 20

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

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

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

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

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

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

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

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

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

Результат:

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

Билет 21

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

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

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

SELECT nazvanie
FROM spasoi_ekz.j
WHERE NOT EXISTS (
                  SELECT *
                  FROM spasoi_ekz.spj
                  WHERE nomer_postavshika != 'S18'
                    AND nomer_izdelia = J.nomer_izdelia
                 )
  AND NOT EXISTS (
                  SELECT *
                  FROM spasoi_ekz.spj
                  WHERE nomer_postavshika = 'S18'
                    AND nomer_izdelia != J.nomer_izdelia
                 );

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

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

Результат:

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

Билет 22

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

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

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

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

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

Результат:

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

Билет 23

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

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

SELECT S.imya
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
                      ON S.nomer_postavshika = SPJ.nomer_postavshika
                    JOIN spasoi_ekz.p P
                      ON P.nomer_detali = SPJ.nomer_detali
WHERE P.nazvanie = LOWER('Винт')
  AND SPJ.kolichestvo > 100
  AND S.sostoyanie > (
                      SELECT AVG(SS.sostoyanie)
                      FROM spasoi_ekz.s SS
                      WHERE SS.gorod = S.gorod
                     );

Результат:

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

Билет 24

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

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

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

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

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

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

Результат:

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

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

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

Билет 25

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

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

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

Результат:

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

Билет 26

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

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

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

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

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

Результат:

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

Билет 27

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


1st dufficulty.png


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

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


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

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

Результат:

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

Билет 28

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

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

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

Результат:

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

Билет 29

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

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

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

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

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

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

Результат:

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

Билет 30

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

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

SELECT DISTINCT imya
FROM spasoi_ekz.s S
WHERE NOT EXISTS (
                  SELECT nomer_detali
                  FROM spasoi_ekz.spj SPJY
                  WHERE nomer_postavshika = 'S2'
                    AND NOT EXISTS (
                                    SELECT *
                                    FROM spasoi_ekz.spj
                                    WHERE nomer_postavshika = S.nomer_postavshika
                                      AND nomer_detali = SPJY.nomer_detali
                                   )
                 );

Результат:

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