SQL-запросы к экзамену по СПАСОИ (10 семестр): различия между версиями
| Строка 699: | Строка 699: | ||
=== Билет 18 === | === Билет 18 === | ||
Написать запрос SELECT: выдать имена поставщиков, имеющих состояние больше 1000 и поставляющих деталь с названием "Гайка 01-01" для изделия с названием "Велосипед 03-04" в количестве (в поставке) большим, чем средний объём поставки, выполненной поставщиками с именем ‘Иванов’. | Написать запрос SELECT: выдать имена поставщиков, имеющих состояние больше 1000 и поставляющих деталь с названием "Гайка 01-01" для изделия с названием "Велосипед 03-04" в количестве (в поставке) большим, чем средний объём поставки, выполненной поставщиками с именем ‘Иванов’. | ||
Текст запроса: | Текст запроса: | ||
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
SELECT imya FROM S | SELECT imya FROM S | ||
JOIN SPJ ON SPJ.nomer_postavshika = S.nomer_postavshika | JOIN SPJ ON SPJ.nomer_postavshika = S.nomer_postavshika | ||
| Строка 713: | Строка 710: | ||
AND J.nazvanie = 'Велосипед 03-04' | AND J.nazvanie = 'Велосипед 03-04' | ||
AND sostoyanie > ( | AND sostoyanie > ( | ||
SELECT AVG(kolichestvo) | |||
FROM SPJ | FROM SPJ | ||
JOIN S ONSPJ.nomer_postavshika = S.nomer_postavshika | JOIN S ONSPJ.nomer_postavshika = S.nomer_postavshika | ||
Версия от 16:22, 7 июня 2013
Билет экзамена по СПАСОИ состоит из двух частей:
- теория;
- SQL-запрос.
На этой странице собраны все сформированные запросы по билетам.
Схема БД
Схема БД используется всё та же, что была в прошлом семестре и на лекциях.
Для написания запросов и проверки их выполнения создана БД в СУБД PostgreSQL.
Скрипт создания можно загрузить отсюда.
Таблицы БД
Поставщики
SELECT * FROM spasoi_ekz.s;
nomer_postavshika | imya | sostoyanie | gorod -------------------+------------------+------------+------------ S5 | Мелисандра | 65000 | Мадрид S2 | Бран Старк | 60000 | Мурманск S1 | Якен Хгар | 1500000 | Йокогама S7 | Сирио Форель | 1500000 | Йокогама S4 | Джейме Ланнистер | 750000 | Лондон S3 | Серсея Ланнистер | 1200000 | Лондон S8 | Тирион Ланнистер | 2571 | Манчестер S9 | Иванов | 35000 | Мытищи S10 | Русе Болтон | 44444 | Баренцбург S11 | Петров Иван | 35000 | Мытищи S12 | Петров Пётр | 35000 | Мытищи S14 | Рендилл Тарли | 1111111 | Прага S13 | Сэмвелл Тарли | 999999 | Прага S6 | Оша | | Москва S16 | Ходор | | Москва S15 | Мелисса Флорент | 888888 | Стокгольм (16 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 | Манчестер P17 | бумага | белый | 1 | Астрахань (17 rows)
Изделия
SELECT * FROM spasoi_ekz.j;
nomer_izdelia | nazvanie | gorod ---------------+-----------------------+----------------- J1 | автомобиль | Магнитогорск J2 | процессор | Зеленоград J3 | торшер | Нижний Новгород J4 | универсальное изделие | Париж J5 | уникальное изделие | Бостон J6 | велосипед 01/23 | Манчестер J7 | изделие из болтов | Челябинск J8 | шкаф | Ярославль J9 | рама 02-01 | Череповец J10 | книга | Астрахань (10 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 S13 | P17 | J10 | 1001 S14 | P17 | J10 | 1111 S15 | P17 | J10 | 1010 (38 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 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(DISTINCT nomer_izdelia) = 1;
Ещё вариант
Результат:
nomer_detali | kol --------------+----- P10 | 3 P11 | 5 P12 | 13 P13 | 14 P16 | 2 P3 | 50 P4 | 10 P6 | 20 P7 | 5 P8 | 25 P9 | 1 (11 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, SUM(sostoyanie)
FROM spasoi_ekz.s
WHERE nomer_postavshika IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
GROUP BY nomer_postavshika HAVING MIN(kolichestvo) > 1000
)
GROUP BY gorod;
Результат:
gorod | sum -----------+--------- Прага | 2111110 Стокгольм | 888888 (2 rows)
Билет 12
Написать запрос SELECT: выдать номера деталей, поставляемых для какого-либо изделия поставщиком, проживающим в том же городе, где изготавливается это изделие.
Текст запроса:
SELECT nomer_detali
FROM spasoi_ekz.spj SPJ, spasoi_ekz.s S, spasoi_ekz.j J
WHERE SPJ.nomer_postavshika = S.nomer_postavshika
AND S.gorod = J.gorod
AND J.nomer_izdelia = SPJ.nomer_izdelia;
Результат:
nomer_detali -------------- P15 P16 (2 rows)
Билет 13
Написать один запрос SELECT: выдать количества строк в таблице S (Поставщик) 1) с пустыми значениями и 2) непустыми значениями в столбце Состояние.
Текст запроса:
SELECT COUNT(nomer_postavshika) - COUNT(sostoyanie) AS "С пустым состоянием",
COUNT(sostoyanie) AS "С не пустым состоянием"
FROM spasoi_ekz.s;
Этот же запрос, но длиннее и нерациональней
Результат:
С пустым состоянием | С не пустым состоянием
---------------------+-----------------------
2 | 14
(1 row)
Билет 14
Написать запрос SELECT: выдать имена поставщиков, которые поставляют детали только и только для изделия с номером 'J1'.
Несколько смущает формулировка "только и только".
Текст запроса:
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE nomer_izdelia = 'J1'
AND SPJ.nomer_postavshika NOT IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
WHERE nomer_izdelia != 'J1'
);
Результат:
imya ------------- Петров Пётр (1 row)
Билет 15
Написать запрос SELECT: выдать наименования изделий, для которых детали поставляют только те поставщики, у которых состояние больше 1000000 у.е.
Текст запроса:
SELECT DISTINCT nazvanie
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE sostoyanie > 1000000
AND SPJ.nomer_izdelia NOT IN (
SELECT nomer_izdelia
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE sostoyanie < 1000000
);
Результат:
nazvanie -------------------- процессор уникальное изделие (2 rows)
Билет 16
Билет 17
Билет 18
Написать запрос SELECT: выдать имена поставщиков, имеющих состояние больше 1000 и поставляющих деталь с названием "Гайка 01-01" для изделия с названием "Велосипед 03-04" в количестве (в поставке) большим, чем средний объём поставки, выполненной поставщиками с именем ‘Иванов’. Текст запроса:
SELECT imya FROM S
JOIN SPJ ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN P ON P.nomer_detali = SPJ.nomer_detali
JOIN J ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE sostoyanie > 1000
AND P.nazvanie = 'Гайка 01-01'
AND J.nazvanie = 'Велосипед 03-04'
AND sostoyanie > (
SELECT AVG(kolichestvo)
FROM SPJ
JOIN S ONSPJ.nomer_postavshika = S.nomer_postavshika
WHERE S.imya = 'Иванов');
Билет 19
Написать запрос SELECT: выдать названия красных деталей, которые входят только в изделие с названием "Рама 02-03" в количестве меньшем 10 единиц в поставке.
Текст запроса:
SELECT X.nazvanie FROM P X
JOIN SPJ ON SPJ.nomer_detali = X.nomer_detali
JOIN J ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE X.cvet = 'красный'
AND J.nazvanie = 'Рама 02-03'
AND kolichestvo < 10
AND NOT EXISTS (
SELECT J.nomer_izdelia
FROM J
JOIN SPJ ONSPJ.nomer_izdelia = J.nomer_izdelia
WHERE J.nazvanie != 'Рама 02-03'
AND X.nomer_detali =SPJ.nomer_detali);
Билет 20
Написать запрос SELECT: выдать имена поставщиков, поставляющих только белые детали.
Текст запроса:
SELECT imya FROM S X
JOIN SPJ ON SPJ.nomer_postavshika = X.nomer_postavshika
JOIN P ON P.nomer_detali = SPJ.nomer_detali
WHERE P.cvet = 'белый'
AND NOT EXISTS (SELECT nomer_postavshika FROM SPJ
JOIN P ON P.nomer_detali = SPJ.nomer_detali
WHERE nomer_postavshika = X.nomer_postavshika
AND P.cvet != 'белый');