SQL-запросы к экзамену по СПАСОИ (10 семестр)

Материал из Кафедра ИУ5 МГТУ им. Н.Э.Баумана - студенческое сообщество
Перейти к: навигация, поиск

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

  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 | Майами
(17 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 | Лондон
(25 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          | Череповец
(17 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
(55 rows)

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

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

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

Результат:

 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 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 SPJ JOIN spasoi_ekz.p P
                                               ON SPJ.nomer_detali = P.nomer_detali
                                                  AND SPJ.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 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 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 = 'Иванов'
                                                 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 = 'Иванов'
        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 = 'Иванов'
                          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
 P3           |   50
 P4           |   10
 P6           |   20
 P7           |    5
 P8           |   25
 P9           |    1
(19 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 SPJ JOIN spasoi_ekz.p P
					       ON SPJ.nomer_detali = P.nomer_detali
					          AND P.nomer_detali = 'P1'
                    );

Результат:

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

Билет 11

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

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

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;

Результат:

   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
(2 rows)

Билет 13

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

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

SELECT COUNT(nomer_postavshika) - 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 |                    15
(1 row)

Билет 14

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

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

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

Результат:

      imya
----------------
 Томми Версетти
(1 row)

Билет 15

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

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

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;

Результат:

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

Билет 17

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

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

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;

Этот же запрос немного попроще

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;

Результат:

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

Билет 18

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


2nd dufficulty.png


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

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


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

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

Результат:

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

Билет 19

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

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

SELECT X.nazvanie
FROM spasoi_ekz.p X JOIN spasoi_ekz.spj SPJ
                      ON SPJ.nomer_detali = X.nomer_detali
                    JOIN spasoi_ekz.j J
                      ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE X.cvet = 'красный'
  AND J.nazvanie = LOWER('Рама 02-03')
  AND kolichestvo < 10
  AND NOT EXISTS (
                  SELECT J.nomer_izdelia
                  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
                    JOIN spasoi_ekz.p P
                      ON P.nomer_detali = SPJ.nomer_detali
WHERE cvet = 'белый'
  AND NOT EXISTS (
                  SELECT nomer_postavshika
                  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 s.imya
FROM s
WHERE s.nomer_postavshika NOT IN 	
       (	SELECT spj.nomer_postavshika		
        FROM spj, p		
        WHERE spj.nomer_detali=p.nomer_detali		
              AND p.cvet!='Белый')
     AND s.nomer_postavshika  IN 	
       (	SELECT spj.nomer_postavshika		
         FROM spj, p		
         WHERE spj.nomer_detali=p.nomer_detali		
         AND p.cvet='Белый')

Результат:

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

Билет 21

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

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

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

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

SELECT nazvanie
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
                          ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE nomer_postavshika = 'S1'
  AND SPJ.nomer_izdelia NOT IN (
                                SELECT nomer_izdelia
                                FROM spasoi_ekz.spj
                                WHERE nomer_postavshika != 'S1'
                               );

Результат:

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

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

Результат:

       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)

и

Второй вариант запроса

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

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

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

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

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

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

Результат:

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

Билет 27

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


1st dufficulty.png


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

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


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

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

Результат:

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

Билет 28

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

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

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

Результат:

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

Билет 29

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

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

SELECT nazvanie
FROM spasoi_ekz.p P, spasoi_ekz.spj SPJ
WHERE P.nomer_detali = SPJ.nomer_detali 
  AND 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
                 );

Результат:

     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)