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

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


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


== Схема БД ==
== Схема БД ==
Строка 221: Строка 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> -->
Результат:
Результат:


Строка 694: Строка 727:
</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>
Результат:
Результат:


Строка 709: Строка 765:
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
SELECT gorod, SUM(sostoyanie)
SELECT gorod, SUM(sostoyanie)
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
FROM spasoi_ekz.s S
                      ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE (
GROUP BY gorod HAVING MIN(kolichestvo) > 1000;
</syntaxhighlight>
 
<div class="toccolours mw-collapsible mw-collapsed">
''Ещё вариант этого запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT gorod, SUM(sostoyanie)
FROM spasoi_ekz.s S
WHERE (
       SELECT MIN(kolichestvo)
       SELECT MIN(kolichestvo)
       FROM spasoi_ekz.spj X
       FROM spasoi_ekz.spj X
Строка 727: Строка 773:
GROUP BY gorod;
GROUP BY gorod;
</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">
Строка 745: Строка 789:
</div>
</div>
</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>
 
Результат:


     gorod  |  sum
     gorod  |  sum
Строка 844: Строка 909:
)
)
                 );
                 );
</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>
<!-- этот запрос не подходит под "только и только", подробности в начале страницы
<!-- этот запрос не подходит под "только и только", подробности в начале страницы
Строка 871: Строка 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">
<syntaxhighlight lang="sql">
-- Неверный запрос, если в таблице SPJ НЕТ поставок с этим изделием,
-- а в таблице J - есть это изделие, то запрос вернет его название, хотя не должен
SELECT DISTINCT nazvanie
SELECT DISTINCT nazvanie
FROM spasoi_ekz.j J
FROM spasoi_ekz.j J
Строка 883: Строка 977:
                 );
                 );
</syntaxhighlight>
</syntaxhighlight>
</div>
</div>


<div class="toccolours mw-collapsible mw-collapsed">
<div class="toccolours mw-collapsible mw-collapsed">
Строка 943: Строка 1039:
</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>
 
 
Результат:
 
   Цвет  | Деталей
   Цвет  | Деталей
  -------+---------
  -------+---------
Строка 1054: Строка 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>


Результат:
Результат:
Строка 1068: Строка 1209:


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


Текст запроса:
Текст запроса:
Строка 1152: Строка 1295:
</div>
</div>
</div>
</div>
<div class="toccolours mw-collapsible mw-collapsed">
<div class="toccolours mw-collapsible mw-collapsed">
''И ещё один вариант этого же запроса''
''И ещё один вариант этого же запроса''
Строка 1174: Строка 1318:
</div>
</div>


Результат:
<div class="toccolours mw-collapsible mw-collapsed">
 
''И ещё один вариант этого запроса от ненавистника JOIN''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s S, spasoi_ekz.spj SPJ2
WHERE SPJ2.nomer_postavshika = S.nomer_postavshika
  AND NOT EXISTS (
                  SELECT *
                  FROM spasoi_ekz.p P, spasoi_ekz.spj SPJ
                  WHERE SPJ.nomer_detali = P.nomer_detali
                    AND SPJ.nomer_postavshika = SPJ2.nomer_postavshika
                    AND P.cvet != 'белый'
                );
</syntaxhighlight>
</div>
</div>
 
Результат:
 
       imya
       imya
  -----------------
  -----------------
Строка 1190: Строка 1352:
Чтобы продемонстрировать выполнение запроса наглядно, возьмём поставщика не 'S1', а 'S18', который был создан специально для этого запроса. Если оставить 'S1', то наглядности не получится, так как по нашей схеме БД этот поставщик не попадает под условие "''только и только''", и запрос вернёт пустой результат.
Чтобы продемонстрировать выполнение запроса наглядно, возьмём поставщика не 'S1', а 'S18', который был создан специально для этого запроса. Если оставить 'S1', то наглядности не получится, так как по нашей схеме БД этот поставщик не попадает под условие "''только и только''", и запрос вернёт пустой результат.


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


Строка 1207: Строка 1370:
                     AND nomer_izdelia != J.nomer_izdelia
                     AND nomer_izdelia != J.nomer_izdelia
                 );
                 );
</syntaxhighlight>
Вариант покороче:
<syntaxhighlight lang="sql">
SELECT j1.nazvanie FROM spasoi_ekz.j j1
WHERE NOT EXISTS(
  SELECT * FROM spasoi_ekz.spj s1
  JOIN spasoi_ekz.j j2 ON s1.nomer_izdelia = j2.nomer_izdelia
  WHERE (s1.nomer_postavshika = 'S18'
        AND j2.nomer_izdelia != j1.nomer_izdelia)
  OR    (s1.nomer_postavshika != 'S18'
        AND j2.nomer_izdelia = j1.nomer_izdelia)
);
</syntaxhighlight>
</syntaxhighlight>


Строка 1233: Строка 1410:
                                           ON X.nomer_izdelia = J.nomer_izdelia
                                           ON X.nomer_izdelia = J.nomer_izdelia
                   WHERE X.nomer_detali != 'P1'  
                   WHERE X.nomer_detali != 'P1'  
                     AND j.nazvanie = LOWER('Штуцер 01-02')
                     AND J.nazvanie = LOWER('Штуцер 01-02')
                     AND S.nomer_postavshika = X.nomer_postavshika
                     AND X.nomer_postavshika = S.nomer_postavshika
                 );
                 );
</syntaxhighlight>
</syntaxhighlight>
Строка 1385: Строка 1562:
   шуруп    |  68
   шуруп    |  68
  (2 rows)
  (2 rows)
<div class="toccolours mw-collapsible mw-collapsed">
''То же, что выше, но для всех городов:''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT gorod, nazvanie, SUM(kolichestvo) FROM spasoi_ekz.spj
JOIN spasoi_ekz.p ON spj.nomer_detali = nomer_detali
GROUP BY gorod, nazvanie
ORDER BY gorod;
</syntaxhighlight>
</div>
</div>


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




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


Текст запроса:
Текст запроса:
Строка 1549: Строка 1740:
                 );
                 );
</syntaxhighlight>
</syntaxhighlight>
<div class="toccolours mw-collapsible mw-collapsed">
''Ещё один вариант этого же запроса''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nazvanie
FROM spasoi_ekz.p P1
WHERE NOT EXISTS (
                  SELECT *
                  FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P2
                                            ON SPJ.nomer_detali = P.nomer_detali
                                          JOIN spasoi_ekz.j J
                                            ON J.nomer_izdelia = SPJ.nomer_izdelia
                  WHERE (
                        P1.nazvanie = P2.nazvanie AND nazvanie!='Штуцер'
                        )
                  OR (
                        P1.nazvanie != P2.nazvanie AND nazvanie='Штуцер'
                );
</syntaxhighlight>
</div>
</div>


Результат:
Результат:
Строка 1576: Строка 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;

Ещё вариант

Результат: 

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

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

Результат: 

 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

Результат: 

 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

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

Результат: 

 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

Результат: 

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

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

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

Результат: 

     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;

Ещё вариант


Результат: 

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

Результат: 

    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;

И ещё вариант

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

Результат: 

   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;

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

Результат: 

 С пустым состоянием | С не пустым состоянием
---------------------+-----------------------
                   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
                 );

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

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

Результат: 

      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;

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

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


Результат: 

 Цвет  | Деталей
-------+---------
 белый |       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;

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

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

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

Результат: 

 Номер поставщика | Количество поставок
------------------+---------------------
 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:

Результат: 

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

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

Результат: 

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

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

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

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

Результат: 

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

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

Результат: 

    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)

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

Билет 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

Результат: 

 Номер детали | Количество поставок с ней
--------------+---------------------------
 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
                        )
                 );

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

Результат: 

     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)
Источник — https://iu5bmstu.ru/index.php?title=SQL-запросы_к_экзамену_по_СПАСОИ_(10_семестр)&oldid=5854