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 | Мытищи
 S12               | Петров Пётр      |      35000 | Мытищи
 S14               | Рендилл Тарли    |    1111111 | Прага
 S13               | Сэмвелл Тарли    |     999999 | Прага
 S6                | Оша              |            | Москва
 S16               | Ходор            |            | Москва
 S15               | Мелисса Флорент  |     888888 | Стокгольм
(16 rows)

Детали

SELECT * FROM spasoi_ekz.p;
 nomer_detali |       nazvanie       |     cvet      | ves  |      gorod
--------------+----------------------+---------------+------+-----------------
 P9           | подставка            | синий         |  400 | Нижний Новгород
 P10          | стойка               | синий         | 2000 | Нижний Новгород
 P11          | абажур               | синий         |  400 | Нижний Новгород
 P1           | гайка                | чёрный        |   20 | Лондон
 P2           | шуруп                | чёрный        |    5 | Лондон
 P3           | ось                  | белый         | 5000 | Эдинбург
 P4           | зубчатое колесо      | чёрный        |   50 | Эдинбург
 P6           | транзистор           | коричневый    |    2 | Токио
 P7           | печатная плата       | зелёный       |  200 | Токио
 P8           | диод                 | коричневый    |    1 | Токио
 P12          | универсальная деталь | универсальный |    1 | Париж
 P13          | уникальная деталь    | уникальный    |    1 | Бостон
 P14          | болт                 | серый         |   20 | Ижевск
 P15          | рама                 | красный       | 3000 | Манчестер
 P16          | колесо               | белый         | 1500 | Манчестер
 P5           | втулка               | серый         |  350 | Манчестер
 P17          | бумага               | белый         |    1 | Астрахань
(17 rows)

Изделия

SELECT * FROM spasoi_ekz.j;
 nomer_izdelia |       nazvanie        |      gorod
---------------+-----------------------+-----------------
 J1            | автомобиль            | Магнитогорск
 J2            | процессор             | Зеленоград
 J3            | торшер                | Нижний Новгород
 J4            | универсальное изделие | Париж
 J5            | уникальное изделие    | Бостон
 J6            | велосипед 01/23       | Манчестер
 J7            | изделие из болтов     | Челябинск
 J8            | шкаф                  | Ярославль
 J9            | рама 02-01            | Череповец
 J10           | книга                 | Астрахань
(10 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
 S9                | P14          | J8            |          25
 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
(38 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           |          15 
 P12          |          7 
 P4           |          10 
(3 rows)

Билет 3

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

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

SELECT nomer_postavshika
FROM (spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
                           ON SPJ.nomer_detali = P.nomer_detali
                              AND P.nomer_detali = 'P1'
     ) A
WHERE kolichestvo > (
                     SELECT AVG(kolichestvo)
                     FROM spasoi_ekz.spj
                     WHERE nomer_izdelia = A.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
(1 row)

Билет 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, SUM(kolichestvo)
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;

Результат:

 nomer_izdelia | sum
---------------+-----
 J3            |   6
 J1            |   3
(2 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 | kol
--------------+-----
 P10          |   3
 P11          |   5
 P12          |  13
 P13          |  14
 P16          |   2
 P3           |  50
 P4           |  10
 P6           |  20
 P7           |   5
 P8           |  25
 P9           |   1
  (11 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 |                    14
(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
--------------------
 процессор
 уникальное изделие
(2 rows)

Билет 16

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

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

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

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

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

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

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

Результат:

      imya
-----------------
 Сэмвелл Тарли
 Рендилл Тарли
 Мелисса Флорент
(3 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
--------------------
 уникальное изделие
(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.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 P A, (
                                SELECT X.gorod, SUM(kolichestvo) as kol FROM P X, SPJ Y
                                WHERE Y.nomer_detali = X.nomer_detali
                                GROUP BY X.gorod
                                )B
WHERE A.gorod = B.gorod

Билет 25

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

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

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

Билет 26

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

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

SELECT P.nomer_detali, COUNT(kolichestvo) FROM P,SPJ
WHERE P.nomer_detali = SPJ.nomer_detali
AND cvet ='красный'
GROUP BY nomer_detali

Билет 27

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

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

SELECT S.gorod, AVG(S.sostoyanie) FROM S 
JOIN SPJ ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN P ON P.nomer_detali = SPJ.nomer_detali
JOIN J ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE P.nazvanie = 'Гайка 01-01'
AND J.nazvanie = 'Велосипед 03-04'
AND kolichestvo > (
                  SELECT MIN(kolichestvo)
                  FROM SPJ
                  WHERE SPJ.nomer_postavshika = 'S1')
GROUP BY S.gorod;

Билет 28

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

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

SELECT X.nazvanie FROM J X
JOIN SPJ Y ON X.nomer_izdelia = Y.nomer_izdelia
JOIN P Z ON Z.nomer_detali = Y.nomer_detali
WHERE Z.ves > 10
AND Z.cvet = 'красный'
AND NOT EXISTS (
                SELECT K.nomer_postavshika FROM S K
                JOIN SPJ L ON K.nomer_postavshika = L.nomer_postavshika
                WHERE L.nomer_detali = Z.nomer_detali
		AND K.nomer_postavshika!='S1');

Билет 29

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

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

SELECT nazvanie FROM P X, SPJ Y
WHERE X.nomer_detali = Y.nomer_detali 
AND NOT EXISTS (SELECT Z.nomer_izdelia FROM SPJ Z
                JOIN J K ON K.nomer_izdelia = Z.nomer_izdelia
                 WHERE K.nazvanie != 'Штуцер 01-03'
                AND Z.nomer_detali=X.nomer_detali);

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