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

Материал из Кафедра ИУ5 МГТУ им. Н.Э.Баумана - студенческое сообщество
Перейти к: навигация, поиск
м (Таблицы БД: изменения в схеме)
м (Схема БД: изменения в схеме)
Строка 11: Строка 11:
 
Схема БД используется всё [[СПАСОИ_(10)_-_Лекция_№8_-_SQL#Некоторые возможности языка SQL | та же]], что была в прошлом семестре и на лекциях.
 
Схема БД используется всё [[СПАСОИ_(10)_-_Лекция_№8_-_SQL#Некоторые возможности языка SQL | та же]], что была в прошлом семестре и на лекциях.
  
Для написания запросов и проверки их выполнения создана БД в СУБД [http://www.postgresql.org/ PostgreSQL]. Скрипт создания можно загрузить [http://yadi.sk/d/tRUH-Wc15AysZ отсюда].
+
Для написания запросов и проверки их выполнения создана БД в СУБД [http://www.postgresql.org/ PostgreSQL]. Скрипт создания можно загрузить [http://yadi.sk/d/3LnfpFaA5BaZp отсюда].
  
 
=== Таблицы БД ===
 
=== Таблицы БД ===
Строка 20: Строка 20:
 
SELECT * FROM spasoi_ekz.s;
 
SELECT * FROM spasoi_ekz.s;
 
</syntaxhighlight>
 
</syntaxhighlight>
 
 
   nomer_postavshika |      imya      | sostoyznie |  gorod
 
   nomer_postavshika |      imya      | sostoyznie |  gorod
 
  -------------------+------------------+------------+----------
 
  -------------------+------------------+------------+----------
Строка 53: Строка 52:
 
   P12          | универсальная деталь | универсальный |    1 | Париж
 
   P12          | универсальная деталь | универсальный |    1 | Париж
 
  (12 rows)
 
  (12 rows)
 
  
 
==== Изделия ====
 
==== Изделия ====
Строка 68: Строка 66:
 
   J4            | универсальное изделие | Париж
 
   J4            | универсальное изделие | Париж
 
  (4 rows)
 
  (4 rows)
 
  
 
==== Сборки ====
 
==== Сборки ====
Строка 87: Строка 84:
 
   S5                | P11          | J3            |          1
 
   S5                | P11          | J3            |          1
 
   S2                | P4          | J1            |          8
 
   S2                | P4          | J1            |          8
  S6                | P1          | J1            |          11
 
 
   S6                | P3          | J1            |          50
 
   S6                | P3          | J1            |          50
  S6                | P2          | J1            |          9
 
 
   S7                | P8          | J2            |          25
 
   S7                | P8          | J2            |          25
 
   S1                | P12          | J4            |          1
 
   S1                | P12          | J4            |          1
Строка 96: Строка 91:
 
   S4                | P12          | J4            |          1
 
   S4                | P12          | J4            |          1
 
   S5                | P12          | J4            |          1
 
   S5                | P12          | J4            |          1
  S6                | P12          | J4            |          1
 
 
   S7                | P12          | J4            |          1
 
   S7                | P12          | J4            |          1
  (20 rows)
+
  S7                | P2          | J1            |          1
 +
  S6                | P2          | J1            |          9
 +
  S6                | P12          | J4            |          7
 +
  S6                | P1          | J1            |          12
 +
  (21 rows)
  
 
== Готовые запросы ==
 
== Готовые запросы ==

Версия 18:06, 26 мая 2013

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

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

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

Схема БД

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

Для написания запросов и проверки их выполнения создана БД в СУБД PostgreSQL. Скрипт создания можно загрузить отсюда.

Таблицы БД

Поставщики

SELECT * FROM spasoi_ekz.s;
 nomer_postavshika |       imya       | sostoyznie |  gorod
-------------------+------------------+------------+----------
 S5                | Мелисандра       |      65000 | Мадрид
 S2                | Бран Старк       |      60000 | Мурманск
 S1                | Якен Хгар        |    1500000 | Йокогама
 S7                | Сирио Форель     |    1500000 | Йокогама
 S6                | Оша              |      15000 | Москва
 S4                | Джейме Ланнистер |     750000 | Лондон
 S3                | Серсея Ланнистер |    1200000 | Лондон
(7 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 | Эдинбург
 P5           | втулка               | серый         |  350 | Лондон
 P6           | транзистор           | коричневый    |    2 | Токио
 P7           | печатная плата       | зелёный       |  200 | Токио
 P8           | диод                 | коричневый    |    1 | Токио
 P12          | универсальная деталь | универсальный |    1 | Париж
(12 rows)

Изделия

SELECT * FROM spasoi_ekz.j;
 nomer_izdelia |       nazvanie        |      gorod
---------------+-----------------------+-----------------
 J1            | автомобиль            | Магнитогорск
 J2            | процессор             | Зеленоград
 J3            | торшер                | Нижний Новгород
 J4            | универсальное изделие | Париж
(4 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
 S6                | P1           | J1            |          12
(21 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

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