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

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


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


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


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


=== Таблицы БД ===
=== Таблицы БД ===
Строка 42: Строка 44:
   S12              | Петров Пётр      |      35001 | Мытищи
   S12              | Петров Пётр      |      35001 | Мытищи
   S17              | Томми Версетти  |  123456789 | Майами
   S17              | Томми Версетти  |  123456789 | Майами
  (17 rows)
  S18              | Безликий        |          1 | Йокогама
  (18 rows)


==== Детали ====
==== Детали ====
Строка 77: Строка 80:
   P25          | штуцерная деталь    | коричневый    |  450 | Череповец
   P25          | штуцерная деталь    | коричневый    |  450 | Череповец
   P2          | шуруп                | чёрный        |    5 | Лондон
   P2          | шуруп                | чёрный        |    5 | Лондон
  (25 rows)
  P26          | лезвие              | бесцветный    |  120 | Йокогама
  (26 rows)


==== Изделия ====
==== Изделия ====
Строка 104: Строка 108:
   J16          | кружевное бельё      | Челябинск
   J16          | кружевное бельё      | Челябинск
   J17          | штуцер 01-03          | Череповец
   J17          | штуцер 01-03          | Череповец
  (17 rows)
  J18          | кинжал                | Йокогама
  (18 rows)


==== Сборки ====
==== Сборки ====
Строка 169: Строка 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 ===
Строка 197: Строка 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> -->
Результат:
Результат:


Строка 261: Строка 318:


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


Строка 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
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
FROM spasoi_ekz.spj A JOIN spasoi_ekz.s S
                           ON SPJ.nomer_postavshika = S.nomer_postavshika
                           ON A.nomer_postavshika = S.nomer_postavshika
WHERE nomer_izdelia = 'J1'
WHERE nomer_izdelia = 'J1'
   AND SPJ.nomer_postavshika NOT IN (
   AND NOT EXISTS (
                                    SELECT nomer_postavshika
                  SELECT nomer_postavshika
                                    FROM spasoi_ekz.spj
                  FROM spasoi_ekz.spj
                                    WHERE nomer_izdelia != 'J1'
                  WHERE nomer_izdelia != 'J1'
                                  );
                    AND nomer_postavshika = A.nomer_postavshika
</syntaxhighlight>
                );
</syntaxhighlight> -->


Результат:
Результат:
Строка 718: Строка 940:
       imya
       imya
  ----------------
  ----------------
   Томми Версетти
   Безликий
  (1 row)
  (1 row)


Строка 726: Строка 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
Строка 741: Строка 997:
                               );
                               );
</syntaxhighlight>
</syntaxhighlight>
</div>
</div>


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


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


Результат:
Результат:
Строка 795: Строка 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">
Строка 819: Строка 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>
Строка 863: Строка 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>


Результат:
Результат:
Строка 877: Строка 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
Строка 936: Строка 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
Строка 964: Строка 1292:
                                                               AND cvet != 'белый'
                                                               AND cvet != 'белый'
                                 );
                                 );
</syntaxhighlight>
</div>
</div>
<div class="toccolours mw-collapsible mw-collapsed">
''И ещё один вариант этого же запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s
WHERE nomer_postavshika NOT IN  (
                                SELECT spj.nomer_postavshika
                                FROM spasoi_ekz.spj SPJ, spasoi_ekz.p P
                                WHERE SPJ.nomer_detali = P.nomer_detali
                                  AND p.cvet != 'белый'
                                )
  AND nomer_postavshika  IN (
                            SELECT spj.nomer_postavshika
                            FROM spasoi_ekz.spj SPJ, spasoi_ekz.p P
                            WHERE SPJ.nomer_detali = P.nomer_detali
                              AND p.cvet = 'белый'
                            );
</syntaxhighlight>
</div>
</div>
<div class="toccolours mw-collapsible mw-collapsed">
''И ещё один вариант этого запроса от ненавистника JOIN''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S, spasoi_ekz.spj SPJ2
WHERE SPJ2.nomer_postavshika = S.nomer_postavshika
  AND NOT EXISTS (
                  SELECT *
                  FROM spasoi_ekz.p P, spasoi_ekz.spj SPJ
                  WHERE SPJ.nomer_detali = P.nomer_detali
                    AND SPJ.nomer_postavshika = SPJ2.nomer_postavshika
                    AND P.cvet != 'белый'
                );
</syntaxhighlight>
</syntaxhighlight>
</div>
</div>
Строка 982: Строка 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 (
                  SELECT *
                  FROM spasoi_ekz.spj
                  WHERE nomer_postavshika != 'S18'
                    AND nomer_izdelia = J.nomer_izdelia
                )
   AND NOT EXISTS (
   AND NOT EXISTS (
                   SELECT nomer_postavshika
                   SELECT *
                   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
                 );
                 );
</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>


Результат:
Результат:
Строка 1018: Строка 1390:
       nazvanie
       nazvanie
  --------------------
  --------------------
   уникальное изделие
   кинжал
  кружевное бельё
  (1 row)
  (2 rows)


=== Билет 22 ===
=== Билет 22 ===
Строка 1027: Строка 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>


<!-- неверный запрос - номер изделия, а не название
<!-- неверный запрос - номер изделия, а не название
Строка 1035: Строка 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
Строка 1049: Строка 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
Строка 1055: Строка 1445:
                               );
                               );
</syntaxhighlight>
</syntaxhighlight>
</div>
</div>


Результат:
Результат:
Строка 1092: Строка 1484:
=== Билет 24 ===
=== Билет 24 ===


<!-- убрано до выяснения
Написать запрос SELECT: выдать наименования деталей и общее их количество для всех наименований деталей, изготавливаемых в одном городе.
Написать запрос SELECT: выдать наименования деталей и общее их количество для всех наименований деталей,изготавливаемых в одном городе.
 
<!-- С этим запросом возникла неожиданная проблема - задание то ли неполное, то ли неправильное, потому что нельзя однозначно сказать, что по нему требуется сделать.
 
Так что тут несколько вариантов запросов (кто как понял).
 
<div class="toccolours mw-collapsible mw-collapsed">
''Первый вариант запроса''
<div class="mw-collapsible-content">
Текст запроса:
 
<syntaxhighlight lang="sql">
SELECT nazvanie, kol
FROM spasoi_ekz.p A, (
                      SELECT X.gorod, SUM(kolichestvo) as kol
                      FROM spasoi_ekz.p X, spasoi_ekz.spj Y
                      WHERE Y.nomer_detali = X.nomer_detali
                      GROUP BY X.gorod
                    ) B
WHERE A.gorod = B.gorod;
</syntaxhighlight>
 
Результат:
 
        nazvanie      | kol
----------------------+------
  подставка            |    9
  стойка              |    9
  абажур              |    9
  гайка                |  139
  ось                  |  60
  зубчатое колесо      |  60
  транзистор          |  50
  печатная плата      |  50
  диод                |  50
  универсальная деталь |  13
  уникальная деталь    |  14
  болт                | 9137
  рама                |  69
  колесо              |  69
  втулка              |  69
  бумага              | 3122
  плитка              |  14
  орнамент            |  14
  уголок              |  14
  гайка 01-01          |  27
  усиленная рама      |  10
  винт                | 9137
  труселя              |    1
  штуцерная деталь    |  10
  шуруп                |  139
(25 rows)
</div>
</div>
и -->
В уточнение задания Григорьев сказал следующее: "''Следует учесть, что в качестве наименования города может выступать переменная, в которую в некоторой программе должно быть занесено конкретное значение перед выполнением запроса''".
 
То есть, вообще говоря, задание на запрос неполное, и его можно трактовать так: выдать наименования деталей и общее их количество для всех наименований деталей, изготавливаемых в городе <code>%НАЗВАНИЕГОРОДА%</code>. Вот эта переменная задаётся где-то в программе, а в БД идёт запрос с уже подставленным конкретным названием.  


Формулировка запроса - просто вынос мозга...
Этот вариант запроса составлен, например, для Лондона.  


Текст запроса:
Текст запроса:
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
SELECT nazvanie, kol FROM P A, (
SELECT nazvanie, SUM(kolichestvo)
                                SELECT X.gorod, SUM(kolichestvo) as kol FROM P X, SPJ Y
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p
                                WHERE Y.nomer_detali = X.nomer_detali
                          ON SPJ.nomer_detali = P.nomer_detali
                                GROUP BY X.gorod
WHERE gorod = 'Лондон'
                                )B
GROUP BY nazvanie;
WHERE A.gorod = B.gorod
</syntaxhighlight>
</syntaxhighlight>-->
 
Результат:
 
  nazvanie | sum
----------+-----
  гайка    |  71
  шуруп    |  68
(2 rows)
 
<div class="toccolours mw-collapsible mw-collapsed">
''То же, что выше, но для всех городов:''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT gorod, nazvanie, SUM(kolichestvo) FROM spasoi_ekz.spj
JOIN spasoi_ekz.p ON spj.nomer_detali = nomer_detali
GROUP BY gorod, nazvanie
ORDER BY gorod;
</syntaxhighlight>
</div>
</div>


=== Билет 25 ===
=== Билет 25 ===
Строка 1184: Строка 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>




Строка 1232: Строка 1700:
  AND P.cvet = 'красный'
  AND P.cvet = 'красный'
  AND NOT EXISTS (
  AND NOT EXISTS (
                 SELECT SS.nomer_postavshika
                 SELECT SPJ2.nomer_postavshika
                 FROM spasoi_ekz.s SS JOIN spasoi_ekz.spj SPJ2
                 FROM spasoi_ekz.spj SPJ2        
                            ON SS.nomer_postavshika = SPJ2.nomer_postavshika
                 WHERE SPJ2.nomer_detali = P.nomer_detali
                 WHERE SPJ2.nomer_detali = P.nomer_detali
  AND SS.nomer_postavshika != 'S1'
  AND SPJ2.nomer_postavshika != 'S1'
                 );                     
                 );                     
</syntaxhighlight>
</syntaxhighlight>
Строка 1249: Строка 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>
 
<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>
</syntaxhighlight>
</div>
</div>


Результат:
Результат:
Строка 1291: Строка 1789:
                 );
                 );
</syntaxhighlight>
</syntaxhighlight>
 
<!-- <div class="toccolours mw-collapsible mw-collapsed">
''Ещё вариант''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT DISTINCT imya
FROM spasoi_ekz.spj SPJ
WHERE NOT EXISTS (
    (SELECT DISTINCT nomer_detali FROM spasoi_ekz.spj WHERE nomer_postavshika = 'S2')
    EXCEPT
    (SELECT DISTINCT nomer_detali FROM spasoi_ekz.spj WHERE nomer_postavshika = SPJ.nomer_postavshika)
)
AND nomer_postavshika != 'S2'
</syntaxhighlight>
</div>
</div> -->
Результат:
Результат:



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

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

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

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

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

Схема БД

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

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

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

Таблицы БД

Поставщики

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

Детали

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

Изделия

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

Сборки

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

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

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

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

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

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

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

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

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

Про JOIN

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

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

Билет 1

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

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

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

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

WITH
  s_all AS (
      SELECT DISTINCT nomer_detali
      FROM spasoi_ekz.spj SPJY
      WHERE nomer_postavshika = 'S2'
  )

SELECT
  spj.nomer_postavshika
FROM spasoi_ekz.spj
  INNER JOIN s_all ON s_all.nomer_detali = spj.nomer_detali
WHERE
  nomer_postavshika != 'S2'
GROUP BY nomer_postavshika
HAVING count(spj.nomer_detali) = (SELECT count(*) FROM s_all);

Результат:

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

Билет 2

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

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

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

Ещё вариант

SELECT nomer_detali AS "Номер детали",
       kol AS "Сколько штук поставляется"
FROM (
      SELECT nomer_detali,
             SUM(kolichestvo) AS kol,
             COUNT(DISTINCT nomer_postavshika)
      FROM spasoi_ekz.spj
      GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_postavshika) > 1
     ) A;

Результат:

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

Билет 3

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

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

SELECT X.nomer_postavshika 
FROM spasoi_ekz.spj X
WHERE X.nomer_detali = 'P1'
  AND X.kolichestvo > (
                       SELECT AVG(kolichestvo)
                       FROM spasoi_ekz.spj
                       WHERE nomer_izdelia = X.nomer_izdelia
                         AND nomer_detali = 'P2'
                      );

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

SELECT DISTINCT nomer_postavshika
FROM (
      SELECT *
      FROM spasoi_ekz.spj
      WHERE nomer_izdelia IN(
                             SELECT nomer_izdelia
                             FROM spasoi_ekz.spj
                             WHERE nomer_detali = 'P1'
                            )
        AND nomer_izdelia IN(
	                     SELECT nomer_izdelia
	                     FROM spasoi_ekz.spj
	                     WHERE nomer_detali = 'P2'
	                    )
     ) A
WHERE nomer_detali = 'P1' AND kolichestvo >
(
 SELECT AVG(kolichestvo)
 FROM (
       SELECT *
       FROM spasoi_ekz.spj
       WHERE nomer_izdelia IN(
                              SELECT nomer_izdelia
                              FROM spasoi_ekz.spj
                              WHERE nomer_detali = 'P1'
                             )
         AND nomer_izdelia IN(
                              SELECT nomer_izdelia
                              FROM spasoi_ekz.spj
                              WHERE nomer_detali = 'P2'
                             )
      ) B
 WHERE nomer_detali = 'P2'
);

Результат:

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

Билет 4

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

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

SELECT nomer_izdelia
FROM spasoi_ekz.spj X
WHERE NOT EXISTS (
                  SELECT *
                  FROM spasoi_ekz.spj
                  WHERE nomer_postavshika != 'S1'
                    AND X.nomer_izdelia = nomer_izdelia
                 );

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

SELECT DISTINCT nomer_izdelia
FROM spasoi_ekz.spj
WHERE nomer_izdelia IN(
                       SELECT nomer_izdelia
                       FROM spasoi_ekz.spj
                       WHERE nomer_postavshika = 'S1'
                      )
  AND nomer_izdelia NOT IN(
                           SELECT nomer_izdelia
                           FROM spasoi_ekz.spj
                           WHERE nomer_postavshika != 'S1'
                          );

Результат:

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

Билет 5

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

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

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

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

SELECT imya
FROM spasoi_ekz.s S, spasoi_ekz.p P, spasoi_ekz.spj SPJ
WHERE P.nazvanie = LOWER('Болт')
  AND P.nomer_detali = SPJ.nomer_detali
  AND S.nomer_postavshika = SPJ.nomer_postavshika
  AND SPJ.kolichestvo > (
                         SELECT AVG(kolichestvo)
                         FROM spasoi_ekz.spj
                         WHERE nomer_detali = 'P1'
                        );

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

SELECT imya
FROM spasoi_ekz.s
WHERE nomer_postavshika IN (
                            SELECT nomer_postavshika
                            FROM spasoi_ekz.spj
                            WHERE nomer_detali = (
                                                  SELECT nomer_detali
                                                  FROM spasoi_ekz.p
                                                  WHERE LOWER(nazvanie) = LOWER('Болт')
                                                 )
                              AND kolichestvo > (
                                                 SELECT AVG(kolichestvo)
                                                 FROM spasoi_ekz.spj
                                                 WHERE nomer_detali = 'P1'
                                                )
                           );

Результат:

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

Билет 6

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

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

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

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

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

Результат:

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

Билет 7

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

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

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

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

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

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

SELECT nazvanie
FROM (
      SELECT J.nazvanie, kolichestvo
      FROM spasoi_ekz.s S, spasoi_ekz.p P, spasoi_ekz.j J, spasoi_ekz.spj SPJ
      WHERE J.nomer_izdelia = SPJ.nomer_izdelia
        AND P.nomer_detali = SPJ.nomer_detali
        AND P.nazvanie = LOWER('Болт')
        AND S.imya LIKE '%Иванов%'
        AND S.nomer_postavshika = SPJ.nomer_postavshika
     ) A
WHERE kolichestvo >
(
 SELECT AVG(kolichestvo)
 FROM spasoi_ekz.spj
 WHERE nomer_izdelia IN(
                        SELECT J.nomer_izdelia
                        FROM spasoi_ekz.s S, spasoi_ekz.p P, spasoi_ekz.j J, spasoi_ekz.spj SPJ
                        WHERE J.nomer_izdelia = SPJ.nomer_izdelia
                          AND P.nomer_detali = SPJ.nomer_detali
                          AND P.nazvanie = LOWER('Болт')
                          AND S.imya LIKE '%Иванов%'
                          AND S.nomer_postavshika = SPJ.nomer_postavshika
                       )
);

Результат:

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

Билет 8

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

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

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

Результат:

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

Билет 9

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

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

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

Ещё вариант

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


Результат:

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

Билет 10

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

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

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

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

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

Результат:

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

Билет 11

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

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

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

И ещё вариант

SELECT gorod, SUM(sostoyanie)
FROM spasoi_ekz.s
WHERE nomer_postavshika IN (
                            SELECT nomer_postavshika
                            FROM spasoi_ekz.spj
                            GROUP BY nomer_postavshika HAVING MIN(kolichestvo) > 1000
                           )
GROUP BY gorod;

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

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

-- та же фигня
SELECT S.gorod, SUM(S.sostoyanie)--, MIN(SPJ.kolichestvo)
FROM spasoi_ekz.s S, spasoi_ekz.spj SPJ
WHERE S.nomer_postavshika = SPJ.nomer_postavshika
GROUP BY gorod HAVING MIN(kolichestvo) > 1000;

-- и ещё один почти верный
SELECT gorod, sost
FROM (
      SELECT gorod, SUM(sostoyanie) AS sost, SUM(SPJ.kolichestvo)
      FROM spasoi_ekz.spj, spasoi_ekz.s
      WHERE S.nomer_postavshika = SPJ.nomer_postavshika
      GROUP BY S.gorod HAVING SUM(SPJ.kolichestvo) > 1000
     ) A;

Результат:

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

Билет 12

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

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

SELECT nomer_detali
FROM spasoi_ekz.spj SPJ, spasoi_ekz.s S, spasoi_ekz.j J
WHERE SPJ.nomer_postavshika = S.nomer_postavshika
  AND S.gorod = J.gorod
  AND J.nomer_izdelia = SPJ.nomer_izdelia;

Результат:

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

Билет 13

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

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

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

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

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

Результат:

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

Билет 14

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

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

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

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

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

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

Результат:

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

Билет 15

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

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

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

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

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

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

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

Результат:

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

Билет 16

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

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

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

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

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

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

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


Результат:

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

Билет 17

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

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

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

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

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

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

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

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

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

Результат:

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

Билет 18

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



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

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


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

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

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

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

Результат:

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

Билет 19

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

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

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

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

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

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

Результат:

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

Билет 20

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

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

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

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

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

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

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

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

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

Результат:

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

Билет 21

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

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

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

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

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

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

Результат:

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

Билет 22

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

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

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

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

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

Результат:

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

Билет 23

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

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

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

Результат:

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

Билет 24

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

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

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

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

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

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

Результат:

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

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

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

Билет 25

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

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

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

Результат:

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

Билет 26

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

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

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

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

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

Результат:

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

Билет 27

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



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

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


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

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

Результат:

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

Билет 28

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

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

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

Результат:

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

Билет 29

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

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

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

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

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

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

Результат:

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

Билет 30

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

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

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

Результат:

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