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

Материал из Кафедра ИУ5 МГТУ им. Н.Э.Баумана, студенческое сообщество
Перейти к навигации Перейти к поиску
м (→‎Схема БД: изменения в схеме)
м (→‎Схема БД: изменения в схеме)
Строка 11: Строка 11:
Схема БД используется всё [[СПАСОИ_(10)_-_Лекция_№8_-_SQL#Некоторые возможности языка SQL | та же]], что была в прошлом семестре и на лекциях.
Схема БД используется всё [[СПАСОИ_(10)_-_Лекция_№8_-_SQL#Некоторые возможности языка SQL | та же]], что была в прошлом семестре и на лекциях.


Для написания запросов и проверки их выполнения создана БД в СУБД [http://www.postgresql.org/ PostgreSQL]. Скрипт создания можно загрузить [http://yadi.sk/d/GkxQrvaz5Bitz отсюда].
Для написания запросов и проверки их выполнения создана БД в СУБД [http://www.postgresql.org/ PostgreSQL]. Скрипт создания можно загрузить [http://yadi.sk/d/o1fC2VNa5DrwK отсюда].


=== Таблицы БД ===
=== Таблицы БД ===
Строка 20: Строка 20:
SELECT * FROM spasoi_ekz.s;
SELECT * FROM spasoi_ekz.s;
</syntaxhighlight>
</syntaxhighlight>
   nomer_postavshika |      imya      | sostoyznie | gorod
 
  -------------------+------------------+------------+----------
   nomer_postavshika |      imya      | sostoyanie |   gorod
  ------------------+------------------+------------+-----------
   S5                | Мелисандра      |      65000 | Мадрид
   S5                | Мелисандра      |      65000 | Мадрид
   S2                | Бран Старк      |      60000 | Мурманск
   S2                | Бран Старк      |      60000 | Мурманск
Строка 29: Строка 30:
   S4                | Джейме Ланнистер |    750000 | Лондон
   S4                | Джейме Ланнистер |    750000 | Лондон
   S3                | Серсея Ланнистер |    1200000 | Лондон
   S3                | Серсея Ланнистер |    1200000 | Лондон
  (7 rows)
  S8                | Тирион Ланнистер |      2571 | Манчестер
  S9                | Иванов          |      35000 | Мытищи
  (9 rows)


==== Детали ====
==== Детали ====
Строка 46: Строка 49:
   P3          | ось                  | белый        | 5000 | Эдинбург
   P3          | ось                  | белый        | 5000 | Эдинбург
   P4          | зубчатое колесо      | чёрный        |  50 | Эдинбург
   P4          | зубчатое колесо      | чёрный        |  50 | Эдинбург
  P5          | втулка              | серый        |  350 | Лондон
   P6          | транзистор          | коричневый    |    2 | Токио
   P6          | транзистор          | коричневый    |    2 | Токио
   P7          | печатная плата      | зелёный      |  200 | Токио
   P7          | печатная плата      | зелёный      |  200 | Токио
Строка 52: Строка 54:
   P12          | универсальная деталь | универсальный |    1 | Париж
   P12          | универсальная деталь | универсальный |    1 | Париж
   P13          | уникальная деталь    | уникальный    |    1 | Бостон
   P13          | уникальная деталь    | уникальный    |    1 | Бостон
  (12 rows)
  P14          | болт                | серый        |  20 | Ижевск
  P15          | рама                | красный      | 3000 | Манчестер
  P16          | колесо              | белый        | 1500 | Манчестер
  P5          | втулка              | серый        |  350 | Манчестер
  (16 rows)
 


==== Изделия ====
==== Изделия ====
Строка 67: Строка 74:
   J4            | универсальное изделие | Париж
   J4            | универсальное изделие | Париж
   J5            | уникальное изделие    | Бостон
   J5            | уникальное изделие    | Бостон
  (4 rows)
  J6            | велосипед 01/23      | Манчестер
  J7            | изделие из болтов    | Челябинск
  (7 rows)


==== Сборки ====
==== Сборки ====
Строка 97: Строка 106:
   S6                | P2          | J1            |          9
   S6                | P2          | J1            |          9
   S6                | P12          | J4            |          7
   S6                | P12          | J4            |          7
  S1                | P13          | J5            |          14
  S6                | P14          | J1            |        9000
  S2                | P14          | J1            |          3
   S6                | P1          | J1            |          12
   S6                | P1          | J1            |          12
   S1               | P13         | J5           |          14
   S8               | P15         | J6            |          1
  (21 rows)
  S8                | P16          | J6            |          2
  S3                | P5          | J6           |          10
  S9                | P14          | J7            |          16
  S8                | P1          | J7            |          3
  (29 rows)


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

Версия от 17:53, 27 мая 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

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

Билет 6

Билет 7

Билет 8

Билет 9

Билет 10

Билет 11

Билет 12

Билет 13

Билет 14

Билет 15

Билет 16

Билет 17

Билет 18

Билет 19

Билет 20

Билет 21

Билет 22

Билет 23

Билет 24

Билет 25

Билет 26

Билет 27

Билет 28

Билет 29

Билет 30