SQL-запросы к экзамену по СПАСОИ (10 семестр): различия между версиями
| Строка 175: | Строка 175: | ||
COUNT(DISTINCT nomer_postavshika) | COUNT(DISTINCT nomer_postavshika) | ||
FROM spasoi_ekz.spj | FROM spasoi_ekz.spj | ||
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_postavshika) > 1) A; | GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_postavshika) > 1 | ||
) A; | |||
</syntaxhighlight> | </syntaxhighlight> | ||
Версия от 18:13, 5 июня 2013
Билет экзамена по СПАСОИ состоит из двух частей:
- теория;
- 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 | Мытищи S10 | Русе Болтон | 44444 | Баренцбург S11 | Петров Иван | 35000 | Мытищи S12 | Петров Пётр | 35000 | Мытищи (12 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 | изделие из болтов | Челябинск J8 | шкаф | Ярославль J9 | рама 02-01 | Череповец (9 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 (35 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 "Номер детали", 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'
);
Этот же запрос, но длиннее и нерациональней
Результат:
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
Результат:
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
)
);
Этот же запрос, но длиннее и нерациональней
Результат:
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(nomer_izdelia) = 1;
Результат:
nomer_detali | sum --------------+----- P9 | 1 P7 | 5 P3 | 50 P16 | 2 P13 | 14 P6 | 20 P8 | 25 (7 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, sost FROM(
SELECT gorod, SUM(sostoyanie) as sost, SUM(SPJ.kolichestvo) from SPJ,S
WHERE S.nomer_postavshika = SPJ.nomer_postavshika
GROUP BY S.gorod HAVING SUM(SPJ.kolichestvo) > 1000
) A;
Результат:
gorod | sost ----------+-------- Москва | 105000 (1 row)