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

Материал из Кафедра ИУ5 МГТУ им. Н.Э.Баумана - студенческое сообщество
Перейти к: навигация, поиск
м (Билет 1: фэн-шуй)
м (Билет 3: исправленный запрос)
Строка 176: Строка 176:
 
=== Билет 3 ===
 
=== Билет 3 ===
  
Написать запрос SELECT: выдать номера поставщиков, поставляющих детали с номером ‘P1’ для какого-либо изделия в количестве (в поставке) большим, чем средний объём поставок деталей с номером ‘P2’ для этого изделия.
+
Написать запрос SELECT: выдать номера поставщиков, поставляющих детали с номером 'P1' для какого-либо изделия в количестве (в поставке) большим, чем средний объём поставок деталей с номером 'P2' для этого изделия.
  
 
Текст запроса:
 
Текст запроса:
  
 +
<syntaxhighlight lang="sql">
 +
SELECT nomer_postavshika
 +
FROM (spasoi_ekz.spj SPJ JOIN spasoi_ekz.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'
 +
                    );
 +
</syntaxhighlight>
 +
 +
<div class="toccolours mw-collapsible mw-collapsed">
 +
''Этот же запрос, но длиннее и нерациональней''
 +
<div class="mw-collapsible-content">
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
 
SELECT DISTINCT nomer_postavshika
 
SELECT DISTINCT nomer_postavshika
Строка 216: Строка 232:
 
);
 
);
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
</div>
 +
</div>
  
 
Результат:
 
Результат:

Версия 11:15, 4 июня 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 | Мытищи
(9 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            | изделие из болтов     | Челябинск
(7 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
 S9                | P14          | J7            |          16
 S8                | P1           | J7            |           3
(29 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;

Результат:

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

Билет 3

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

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

SELECT nomer_postavshika
FROM (spasoi_ekz.spj SPJ JOIN spasoi_ekz.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.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
------
 Оша
(1 row)

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

Билет 9

Билет 10

Билет 11

Билет 12

Билет 13

Билет 14

Билет 15

Билет 16

Билет 17

Билет 18

Билет 19

Билет 20

Билет 21

Билет 22

Билет 23

Билет 24

Билет 25

Билет 26

Билет 27

Билет 28

Билет 29

Билет 30