SQL-запросы к экзамену по СПАСОИ (10 семестр): различия между версиями
ILobster (обсуждение | вклад) м (→Готовые запросы: про CROSS JOIN) |
ILobster (обсуждение | вклад) м (почему нет сортировки) |
||
| Строка 7: | Строка 7: | ||
На этой странице собраны все сформированные запросы по билетам. | На этой странице собраны все сформированные запросы по билетам. | ||
Странно, что ни в одном билете нет запроса с сортировкой результатов. Возможно, они буду в дополнительных заданиях. | Странно, что ни в одном билете нет запроса с сортировкой результатов. <s>Возможно, они буду в дополнительных заданиях.</s> Григорьев сказал, что это слишком просто и потому он не стал загромождать запросы дополнительными мелочами. | ||
== Схема БД == | == Схема БД == | ||
Версия от 13:28, 20 июня 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 | Мытищи S14 | Рендилл Тарли | 1111111 | Прага S13 | Сэмвелл Тарли | 999999 | Прага S6 | Оша | | Москва S16 | Ходор | | Москва S15 | Мелисса Флорент | 888888 | Стокгольм S12 | Петров Пётр | 35001 | Мытищи S17 | Томми Версетти | 123456789 | Майами S18 | Безликий | 1 | Йокогама (18 rows)
Детали
SELECT * FROM spasoi_ekz.p;
nomer_detali | nazvanie | cvet | ves | gorod --------------+----------------------+---------------+------+----------------- P9 | подставка | синий | 400 | Нижний Новгород P10 | стойка | синий | 2000 | Нижний Новгород P11 | абажур | синий | 400 | Нижний Новгород P1 | гайка | чёрный | 20 | Лондон P3 | ось | белый | 5000 | Эдинбург P4 | зубчатое колесо | чёрный | 50 | Эдинбург P6 | транзистор | коричневый | 2 | Токио P7 | печатная плата | зелёный | 200 | Токио P8 | диод | коричневый | 1 | Токио P12 | универсальная деталь | универсальный | 1 | Париж P13 | уникальная деталь | уникальный | 1 | Бостон P14 | болт | серый | 20 | Ижевск P15 | рама | красный | 3000 | Манчестер P16 | колесо | белый | 1500 | Манчестер P5 | втулка | серый | 350 | Манчестер P17 | бумага | белый | 1 | Астрахань P18 | плитка | белый | 300 | Флоренция P19 | орнамент | серый | 800 | Флоренция P20 | уголок | серый | 100 | Флоренция P21 | гайка 01-01 | серебристый | 20 | Клин P22 | усиленная рама | красный | 3200 | Череповец P23 | винт | розовый | 5 | Ижевск P24 | труселя | красный | 20 | Челябинск P25 | штуцерная деталь | коричневый | 450 | Череповец P2 | шуруп | чёрный | 5 | Лондон P26 | лезвие | бесцветный | 120 | Йокогама (26 rows)
Изделия
SELECT * FROM spasoi_ekz.j;
nomer_izdelia | nazvanie | gorod ---------------+-----------------------+----------------- J1 | автомобиль | Магнитогорск J2 | процессор | Зеленоград J3 | торшер | Нижний Новгород J4 | универсальное изделие | Париж J5 | уникальное изделие | Бостон J6 | велосипед 01/23 | Манчестер J7 | изделие из болтов | Челябинск J8 | шкаф | Ярославль J9 | рама 02-01 | Череповец J10 | книга | Астрахань J11 | панно 01-03 | Флоренция J12 | велосипед 03-04 | Клин J13 | рама 02-03 | Череповец J14 | штуцер 01-02 | Череповец J15 | велосипед 01-04 | Клин J16 | кружевное бельё | Челябинск J17 | штуцер 01-03 | Череповец J18 | кинжал | Йокогама (18 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 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 S5 | P18 | J11 | 9 S5 | P19 | J11 | 1 S5 | P20 | J11 | 4 S9 | P14 | J8 | 25 S12 | P21 | J12 | 15 S11 | P22 | J13 | 9 S11 | P1 | J14 | 26 S12 | P1 | J14 | 13 S12 | P2 | J14 | 54 S12 | P23 | J4 | 101 S12 | P16 | J15 | 40 S7 | P21 | J12 | 3 S8 | P21 | J12 | 4 S9 | P21 | J12 | 5 S1 | P24 | J16 | 1 S1 | P15 | J6 | 3 S4 | P25 | J17 | 1 S17 | P16 | J1 | 4 S18 | P26 | J18 | 1 (56 rows)
Готовые запросы
Некоторое вступление
Если видите, что тот или иной запрос можно составить короче и рациональней - смело вносите правку.
Про только и только
В трёх билетах в задании на запрос встречается формулировка "только и только". Как оказалось, это означает следующее: если холодильники поставляет только и только Уася, то:
- кроме Уаси никто не поставляет холодильники;
- Уася не поставляет ничего, кроме холодильников.
Великий и могучий русский языка, ну что за экономия на бумаге.
Существующие запросы, естественно, оказались неправильными и их пришлось переписать.
Про JOIN
Почти все запросы, где используется соединение таблиц, можно написать с использованием JOIN, а можно просто с перечислением таблиц через запятую.
Но неожиданно оказалось, это почти то же самое и называется CROSS JOIN. Такое соединение таблиц (CROSS JOIN + WHERE) считается устаревшим, поскольку его не рекомендует стандарт SQL ANSI. Таким образом, использование JOIN с условием соединения в ON является более правильным и вообще прогрессивным.
Билет 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 | 71 | 5 P10 | 3 | 2 P11 | 5 | 2 P12 | 13 | 7 P14 | 9036 | 4 P15 | 7 | 3 P16 | 46 | 3 P17 | 3122 | 3 P2 | 68 | 4 P21 | 27 | 4 P4 | 10 | 2 P5 | 16 | 2 (12 rows)
Билет 3
Написать запрос SELECT: выдать номера поставщиков, поставляющих детали с номером 'P1' для какого-либо изделия в количестве (в поставке) большим, чем средний объём поставок деталей с номером 'P2' для этого изделия.
Текст запроса:
SELECT X.nomer_postavshika
FROM spasoi_ekz.spj X
WHERE X.nomer_detali = 'P1'
AND X.kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_izdelia = X.nomer_izdelia
AND nomer_detali = 'P2'
);
Этот же запрос, но длиннее и нерациональней
Результат:
nomer_postavshika ------------------- S6 (1 row)
Билет 4
Написать запрос SELECT: выдать номера изделий, для которых детали поставляет только поставщик с номером ‘S1’.
Текст запроса:
SELECT nomer_izdelia
FROM spasoi_ekz.spj X
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_postavshika != 'S1'
AND X.nomer_izdelia = nomer_izdelia
);
Этот же запрос, но без EXISTS
Результат:
nomer_izdelia --------------- J5 J16 (2 rows)
Билет 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
WHERE nomer_detali = 'P1'
);
Вариант запроса без JOIN
Ещё вариант запроса без JOIN и сложнее
Результат:
imya ------ Оша Иванов (2 rows)
Билет 6
Написать запрос SELECT: выдать названия деталей, поставляемых поставщиком, проживающим в том же городе, где изготавливаются эти детали, для изделия с названием ‘Велосипед 01/23’.
Текст запроса:
SELECT P.nazvanie
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
JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE S.gorod = P.gorod
AND J.nazvanie = LOWER('Велосипед 01/23');
Этот же запрос без JOIN
Результат:
nazvanie ---------- рама колесо (2 rows)
Билет 7
Написать запрос SELECT: выдать названия изделий, куда входят детали с названием 'Болт', поставляемых поставщиками с именем 'Иванов', в количестве (в поставке) большим, чем средний объём поставок для этого изделия.
Текст запроса:
SELECT J.nazvanie
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
JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE imya LIKE '%Иванов%'
AND P.nazvanie = LOWER('Болт')
AND kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj X
WHERE SPJ.nomer_izdelia = X.nomer_izdelia
);
Ещё один вариант запроса
И ещё вариант этого же запроса, но длиннее и нерациональней
Результат:
nazvanie ------------------- шкаф (1 row)
Билет 8
Написать запрос SELECT: выдать номера изделий и общее количество деталей для них, поставляемых поставщиками с именем 'Петров'.
Текст запроса:
SELECT nomer_izdelia AS "Номер изделия", SUM(kolichestvo) AS "Количество деталей"
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;
Результат:
Номер изделия | Количество деталей ---------------+-------------------- J4 | 101 J3 | 6 J13 | 9 J15 | 40 J1 | 3 J12 | 15 J14 | 93 (7 rows)
Билет 9
Написать запрос SELECT: выдать номера деталей и общее их количество для всех номеров деталей, которые входят только в одно изделие.
Текст запроса:
SELECT nomer_detali, SUM(kolichestvo)
FROM spasoi_ekz.spj
GROUP BY nomer_detali HAVING COUNT(DISTINCT nomer_izdelia) = 1;
Ещё вариант
Результат:
nomer_detali | sum --------------+------ P10 | 3 P11 | 5 P12 | 13 P13 | 14 P17 | 3122 P18 | 9 P19 | 1 P20 | 4 P21 | 27 P22 | 9 P23 | 101 P24 | 1 P25 | 1 P26 | 1 P3 | 50 P4 | 10 P6 | 20 P7 | 5 P8 | 25 P9 | 1 (20 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
WHERE nomer_detali = 'P1'
);
Результат:
imya ------------- Русе Болтон (1 row)
Билет 11
Написать запрос SELECT: выдать названия городов и суммарное состояние проживающих в каждом городе поставщиков, у которых минимальный объём поставки деталей больше 1000.
Текст запроса:
SELECT gorod, SUM(sostoyanie)
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
GROUP BY gorod HAVING MIN(kolichestvo) > 1000;
Ещё вариант этого запроса
И ещё вариант подлиннее для тех, кто не ищет лёгких путей
Результат:
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 P26 (3 rows)
Билет 13
Написать один запрос SELECT: выдать количества строк в таблице S (Поставщик) 1) с пустыми значениями и 2) непустыми значениями в столбце Состояние.
Текст запроса:
SELECT COUNT(*) - COUNT(sostoyanie) AS "С пустым состоянием",
COUNT(sostoyanie) AS "С не пустым состоянием"
FROM spasoi_ekz.s;
Этот же запрос, но длиннее и нерациональней
Результат:
С пустым состоянием | С не пустым состоянием
---------------------+-----------------------
2 | 16
(1 row)
Билет 14
Написать запрос SELECT: выдать имена поставщиков, которые поставляют детали только и только для изделия с номером 'J1'.
Чтобы продемонстрировать выполнение запроса наглядно, возьмём изделие не 'J1', а 'J18', которое было создано специально для этого запроса. Если оставить 'J1', то наглядности не получится, так как по нашей схеме БД это изделие не попадает под условие "только и только", и запрос вернёт пустой результат.
Текст запроса:
SELECT DISTINCT imya
FROM spasoi_ekz.s S
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE (
nomer_izdelia != 'J18'
AND
nomer_postavshika = S.nomer_postavshika)
OR
(
nomer_izdelia = 'J18'
AND
nomer_postavshika != S.nomer_postavshika
)
);
Результат:
imya ---------------- Безликий (1 row)
Билет 15
Написать запрос SELECT: выдать наименования изделий, для которых детали поставляют только те поставщики, у которых состояние больше 1000000 у.е.
Текст запроса:
SELECT DISTINCT nazvanie
FROM spasoi_ekz.j J
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj X JOIN spasoi_ekz.s S
ON X.nomer_postavshika = S.nomer_postavshika
WHERE sostoyanie <= 1000000
AND X.nomer_izdelia = J.nomer_izdelia
);
Вариант этого запроса без NOT EXISTS
Результат:
nazvanie -------------------- кружевное бельё уникальное изделие процессор (3 rows)
Билет 16
Написать запрос SELECT: выдать цвета и для каждого цвета общее количество деталей, входящих в изделие с названием 'Панно 01-03'.
Текст запроса:
SELECT cvet AS "Цвет", SUM(kolichestvo) AS "Деталей"
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Панно 01-03')
JOIN spasoi_ekz.P P
ON SPJ.nomer_detali = P.nomer_detali
GROUP BY cvet;
Другой вариант запроса для тонко чувствующих натур
Результат:
Цвет | Деталей -------+--------- белый | 9 серый | 5 (2 rows)
Билет 17
Написать запрос SELECT: выдать номера поставщиков и количество сделанных ими поставок при условии, что среднее число деталей во всех этих поставках больше 1000.
Текст запроса:
SELECT nomer_postavshika AS "Номер поставщика",
COUNT(*) AS "Количество поставок"
FROM spasoi_ekz.spj
GROUP BY nomer_postavshika HAVING AVG(kolichestvo) > 1000;
Ещё вариант запроса
И ещё вариант запроса
И даже ещё вариант запроса
Результат:
Номер поставщика | Количество поставок ------------------+--------------------- S13 | 1 S6 | 7 S14 | 1 S15 | 1 (4 rows)
Билет 18
Написать запрос SELECT: выдать имена поставщиков, имеющих состояние больше 1000 и поставляющих деталь с названием 'Гайка 01-01' для изделия с названием 'Велосипед 03-04' в количестве (в поставке) большим, чем средний объём поставки, выполненной поставщиками с именем 'Иванов'.
Второй по сложности запрос экзамена!
Вот уж не свезло, так не свезло
Текст запроса:
SELECT imya
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
JOIN spasoi_ekz.j J
ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE sostoyanie > 1000
AND P.nazvanie = LOWER('Гайка 01-01')
AND J.nazvanie = LOWER('Велосипед 03-04')
AND kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE S.imya = 'Иванов'
);
Результат:
imya ------------- Петров Пётр (1 row)
Билет 19
Написать запрос SELECT: выдать названия красных деталей, которые входят только в изделие с названием 'Рама 02-03' в количестве, меньшем 10 единиц в поставке.
Текст запроса:
SELECT DISTINCT X.nazvanie
FROM spasoi_ekz.p X JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_detali = X.nomer_detali
WHERE X.cvet = 'красный'
AND kolichestvo < 10
AND NOT EXISTS (
SELECT *
FROM spasoi_ekz.j J JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_izdelia = J.nomer_izdelia
WHERE J.nazvanie != LOWER('Рама 02-03')
AND X.nomer_detali = SPJ.nomer_detali
);
Ещё вариант этого же запроса
Результат:
nazvanie ---------------- усиленная рама (1 row)
Билет 20
Написать запрос SELECT: выдать имена поставщиков, поставляющих только белые детали.
Текст запроса:
SELECT imya
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
WHERE nomer_postavshika = S.nomer_postavshika
AND P.cvet != 'белый'
);
Ещё один вариант этого же запроса
И ещё один вариант этого же запроса
Результат:
imya ----------------- Рендилл Тарли Сэмвелл Тарли Мелисса Флорент Томми Версетти (4 rows)
Билет 21
Написать запрос SELECT: выдать названия изделий, для которых детали поставляет только и только поставщик с номером 'S1'.
Чтобы продемонстрировать выполнение запроса наглядно, возьмём поставщика не 'S1', а 'S18', который был создан специально для этого запроса. Если оставить 'S1', то наглядности не получится, так как по нашей схеме БД этот поставщик не попадает под условие "только и только", и запрос вернёт пустой результат.
Текст запроса:
SELECT nazvanie
FROM spasoi_ekz.j
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_postavshika != 'S18'
AND nomer_izdelia = J.nomer_izdelia
)
AND NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj
WHERE nomer_postavshika = 'S18'
AND nomer_izdelia != J.nomer_izdelia
);
Результат:
nazvanie -------------------- кинжал (1 row)
Билет 22
Написать запрос SELECT: выдать имена поставщиков, которые поставляют только детали с номером 'P1' для изделия с именем 'Штуцер 01-02'.
Текст запроса:
SELECT imya
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON SPJ.nomer_izdelia = J.nomer_izdelia
AND J.nazvanie = LOWER('Штуцер 01-02')
JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE NOT EXISTS (
SELECT *
FROM spasoi_ekz.spj X JOIN spasoi_ekz.j J
ON X.nomer_izdelia = J.nomer_izdelia
WHERE X.nomer_detali != 'P1'
AND j.nazvanie = LOWER('Штуцер 01-02')
AND S.nomer_postavshika = X.nomer_postavshika
);
Этот же запрос, но длиннее и нерациональней
Результат:
imya ------------- Петров Иван (1 row)
Билет 23
Написать запрос SELECT: выдать имена поставщиков, которые поставляют деталь с названием 'Винт' в количестве большим 100 единиц в одной поставке и имеют состояние больше среднего по их родному городу.
Текст запроса:
SELECT S.imya
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON S.nomer_postavshika = SPJ.nomer_postavshika
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
WHERE P.nazvanie = LOWER('Винт')
AND SPJ.kolichestvo > 100
AND S.sostoyanie > (
SELECT AVG(SS.sostoyanie)
FROM spasoi_ekz.s SS
WHERE SS.gorod = S.gorod
);
Результат:
imya ------------- Петров Пётр (1 row)
Билет 24
Написать запрос SELECT: выдать наименования деталей и общее их количество для всех наименований деталей, изготавливаемых в одном городе.
В уточнение задания Григорьев сказал следующее: "Следует учесть, что в качестве наименования города может выступать переменная, в которую в некоторой программе должно быть занесено конкретное значение перед выполнением запроса".
То есть, вообще говоря, задание на запрос неполное, и его можно трактовать так: выдать наименования деталей и общее их количество для всех наименований деталей, изготавливаемых в городе %НАЗВАНИЕГОРОДА%. Вот эта переменная задаётся где-то в программе, а в БД идёт запрос с уже подставленным конкретным названием.
Этот вариант запроса составлен, например, для Лондона.
Текст запроса:
SELECT nazvanie, SUM(kolichestvo)
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.p
ON SPJ.nomer_detali = P.nomer_detali
WHERE gorod = 'Лондон'
GROUP BY nazvanie;
Результат:
nazvanie | sum ----------+----- гайка | 71 шуруп | 68 (2 rows)
Билет 25
Написать запрос SELECT: выдать имена поставщиков, поставляющих хотя бы одну белую деталь для изделия с названием 'Велосипед 01-04' с объёмом поставки большим, чем средний объём поставки этого поставщика.
Текст запроса:
SELECT imya
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
JOIN spasoi_ekz.j J
ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE cvet = 'белый'
AND J.nazvanie = LOWER('Велосипед 01-04')
AND kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj SPJ
WHERE SPJ.nomer_postavshika = S.nomer_postavshika
);
Результат:
imya ------------- Петров Пётр (1 row)
Билет 26
Написать запрос SELECT: выдать номера красных деталей и количество поставок этих деталей.
Текст запроса:
SELECT P.nomer_detali AS "Номер детали", COUNT(kolichestvo) AS "Количество поставок с ней"
FROM spasoi_ekz.p P, spasoi_ekz.spj SPJ
WHERE P.nomer_detali = SPJ.nomer_detali
AND cvet = 'красный'
GROUP BY P.nomer_detali;
Этот же запрос через JOIN
Результат:
Номер детали | Количество поставок с ней --------------+--------------------------- P15 | 3 P22 | 1 P24 | 1 (3 rows)
Билет 27
Написать запрос SELECT: выдать наименования городов и среднее состояние поставщиков для каждого города, поставляющих детали с названием 'Гайка 01-01' для изделия с названием 'Велосипед 03-04' в количестве (в поставке) большим, чем минимальный объём поставки, выполненной поставщиком с номером 'S1'.
Сложнейший запрос экзамена!
Сохрани Джа, вытащить такое
Текст запроса:
SELECT S.gorod AS "Город", AVG(S.sostoyanie) AS "Среднее состояние"
FROM spasoi_ekz.s S JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_postavshika = S.nomer_postavshika
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
JOIN spasoi_ekz.j J
ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE P.nazvanie = LOWER('Гайка 01-01')
AND J.nazvanie = LOWER('Велосипед 03-04')
AND kolichestvo > (
SELECT MIN(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_postavshika = 'S1'
)
GROUP BY S.gorod;
Результат:
Город | Среднее состояние -----------+----------------------- Мытищи | 35000.500000000000 Йокогама | 1500000.000000000000 Манчестер | 2571.0000000000000000 (3 rows)
Билет 28
Написать запрос SELECT: выдать названия изделий, куда входит хотя бы одна красная деталь весом больше 10 граммов, поставляемая только поставщиком с номером 'S1'.
Текст запроса:
SELECT J.nazvanie
FROM spasoi_ekz.j J JOIN spasoi_ekz.spj SPJ1
ON J.nomer_izdelia = SPJ1.nomer_izdelia
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ1.nomer_detali
WHERE P.ves > 10
AND P.cvet = 'красный'
AND NOT EXISTS (
SELECT SPJ2.nomer_postavshika
FROM spasoi_ekz.spj SPJ2
WHERE SPJ2.nomer_detali = P.nomer_detali
AND SPJ2.nomer_postavshika != 'S1'
);
Результат:
nazvanie ----------------- кружевное бельё (1 row)
Билет 29
Написать запрос SELECT: выдать названия деталей, которые входят только и только в состав изделия с названием 'Штуцер 01-03'.
Текст запроса:
SELECT DISTINCT nazvanie
FROM spasoi_ekz.p
WHERE NOT EXISTS (
SELECT SPJ.nomer_izdelia
FROM spasoi_ekz.spj SPJ JOIN spasoi_ekz.j J
ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE (
J.nazvanie != LOWER('Штуцер 01-03')
AND
SPJ.nomer_detali = P.nomer_detali
)
OR (
J.nazvanie = LOWER('Штуцер 01-03')
AND
SPJ.nomer_detali != P.nomer_detali
)
);
Результат:
nazvanie ------------------ штуцерная деталь (1 row)
Билет 30
Написать запрос SELECT: выдать имена поставщиков, которые поставляют, по крайней мере, все те детали, которые поставляет поставщик с номером 'S2'.
Текст запроса:
SELECT DISTINCT imya
FROM spasoi_ekz.s S
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 = S.nomer_postavshika
AND nomer_detali = SPJY.nomer_detali
)
);
Результат:
imya ------------ Оша Бран Старк (2 rows)