SQL-запросы к экзамену по СПАСОИ (10 семестр)
Билет экзамена по СПАСОИ состоит из двух частей:
- теория;
- 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 | Париж P13 | уникальная деталь | уникальный | 1 | Бостон (12 rows)
Изделия
SELECT * FROM spasoi_ekz.j;
nomer_izdelia | nazvanie | gorod ---------------+-----------------------+----------------- J1 | автомобиль | Магнитогорск J2 | процессор | Зеленоград J3 | торшер | Нижний Новгород J4 | универсальное изделие | Париж J5 | уникальное изделие | Бостон (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 S1 | P13 | J5 | 14 (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
Написать запрос 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)