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

Материал из Кафедра ИУ5 МГТУ им. Н.Э.Баумана - студенческое сообщество
Перейти к: навигация, поиск
м (Билет 13: фэн-шуй)
м (Билет 13: орфография)
Строка 614: Строка 614:
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
 
SELECT COUNT(nomer_postavshika) - COUNT(sostoyanie) AS "С пустым состоянием",  
 
SELECT COUNT(nomer_postavshika) - COUNT(sostoyanie) AS "С пустым состоянием",  
       COUNT(sostoyanie) AS "С не пустым состоянием"
+
       COUNT(sostoyanie) AS "С непустым состоянием"
 
FROM spasoi_ekz.s;
 
FROM spasoi_ekz.s;
 
</syntaxhighlight>
 
</syntaxhighlight>

Версия 14:48, 7 июня 2013

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

  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

Билет 17

Билет 18

Билет 19

Билет 20

Билет 21

Билет 22

Билет 23

Билет 24

Билет 25

Билет 26

Билет 27

Билет 28

Билет 29

Билет 30