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

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


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


Результат:
Результат:


   Номер детали | Сколько штук поставляется | Сколько у неё поставщиков
   Номер детали | Сколько штук поставляется  
  --------------+---------------------------+--------------------------
  --------------+---------------------------
   P1          |                       15 |                        2
   P1          |         15  
   P12          |                         7 |                        7
   P12          |         7  
   P4          |                       10 |                        2
   P4          |         10  
  (3 rows)
  (3 rows)



Версия от 18:11, 5 июня 2013

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

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

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

Схема БД

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

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

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

Таблицы БД

Поставщики

SELECT * FROM spasoi_ekz.s;
 nomer_postavshika |       imya       | sostoyanie |   gorod
-------------------+------------------+------------+------------
 S5                | Мелисандра       |      65000 | Мадрид
 S2                | Бран Старк       |      60000 | Мурманск
 S1                | Якен Хгар        |    1500000 | Йокогама
 S7                | Сирио Форель     |    1500000 | Йокогама
 S6                | Оша              |      15000 | Москва
 S4                | Джейме Ланнистер |     750000 | Лондон
 S3                | Серсея Ланнистер |    1200000 | Лондон
 S8                | Тирион Ланнистер |       2571 | Манчестер
 S9                | Иванов           |      35000 | Мытищи
 S10               | Русе Болтон      |      44444 | Баренцбург
 S11               | Петров Иван      |      35000 | Мытищи
 S12               | Петров Пётр      |      35000 | Мытищи
(12 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 | Манчестер
(16 rows)

Изделия

SELECT * FROM spasoi_ekz.j;
 nomer_izdelia |       nazvanie        |      gorod
---------------+-----------------------+-----------------
 J1            | автомобиль            | Магнитогорск
 J2            | процессор             | Зеленоград
 J3            | торшер                | Нижний Новгород
 J4            | универсальное изделие | Париж
 J5            | уникальное изделие    | Бостон
 J6            | велосипед 01/23       | Манчестер
 J7            | изделие из болтов     | Челябинск
 J8            | шкаф                  | Ярославль
 J9            | рама 02-01            | Череповец
(9 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
(35 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 "Номер детали", 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(nomer_izdelia) = 1;

Результат:

 nomer_detali | sum
--------------+-----
 P9           |   1
 P7           |   5
 P3           |  50
 P16          |   2
 P13          |  14
 P6           |  20
 P8           |  25
(7 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, sost FROM(
                        SELECT gorod, SUM(sostoyanie) as sost, SUM(SPJ.kolichestvo) from SPJ,S
                        WHERE S.nomer_postavshika = SPJ.nomer_postavshika
                        GROUP BY S.gorod HAVING SUM(SPJ.kolichestvo) > 1000
                       ) A;

Результат:

  gorod   |  sost
----------+--------
 Москва   | 105000
   (1 row)

Билет 12

Билет 13

Билет 14

Билет 15

Билет 16

Билет 17

Билет 18

Билет 19

Билет 20

Билет 21

Билет 22

Билет 23

Билет 24

Билет 25

Билет 26

Билет 27

Билет 28

Билет 29

Билет 30