SQL-запросы к экзамену по СПАСОИ (10 семестр): различия между версиями
| Строка 1307: | Строка 1307: | ||
AND P.cvet = 'красный' | AND P.cvet = 'красный' | ||
AND NOT EXISTS ( | AND NOT EXISTS ( | ||
SELECT | SELECT SPJ2.nomer_postavshika | ||
FROM | FROM spasoi_ekz.spj SPJ2 | ||
WHERE SPJ2.nomer_detali = P.nomer_detali | WHERE SPJ2.nomer_detali = P.nomer_detali | ||
AND | AND SPJ2.nomer_postavshika != 'S1' | ||
); | ); | ||
</syntaxhighlight> | </syntaxhighlight> | ||
Версия от 20:51, 12 июня 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 | Майами (17 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 | Лондон (25 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 | Череповец (17 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 (55 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 | 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 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 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 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 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 P3 | 50 P4 | 10 P6 | 20 P7 | 5 P8 | 25 P9 | 1 (19 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 | 15
(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 -------------------- кружевное бельё уникальное изделие процессор (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 nom AS "Номер поставщика", cnt AS "Количество поставок"
FROM (
SELECT S.nomer_postavshika AS nom,
COUNT(SPJ.nomer_postavshika) AS cnt,
AVG(SPJ.kolichestvo) AS kol
FROM spasoi_ekz.s S, spasoi_ekz.spj SPJ
WHERE S.nomer_postavshika = SPJ.nomer_postavshika
GROUP BY S.nomer_postavshika
) A
WHERE kol > 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 X.nazvanie
FROM spasoi_ekz.p X JOIN spasoi_ekz.spj SPJ
ON SPJ.nomer_detali = X.nomer_detali
JOIN spasoi_ekz.j J
ON J.nomer_izdelia = SPJ.nomer_izdelia
WHERE X.cvet = 'красный'
AND J.nazvanie = LOWER('Рама 02-03')
AND kolichestvo < 10
AND NOT EXISTS (
SELECT J.nomer_izdelia
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
JOIN spasoi_ekz.p P
ON P.nomer_detali = SPJ.nomer_detali
WHERE cvet = 'белый'
AND NOT EXISTS (
SELECT nomer_postavshika
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'.
Текст запроса:
SELECT nazvanie
FROM spasoi_ekz.j J, spasoi_ekz.SPJ SPJ
WHERE J.nomer_izdelia = SPJ.nomer_izdelia
AND NOT EXISTS (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
WHERE nomer_postavshika != 'S1'
AND nomer_izdelia = J.nomer_izdelia
);
Ещё один вариант запроса
Результат:
nazvanie -------------------- уникальное изделие кружевное бельё (2 rows)
Билет 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.p P
ON SPJ.nomer_detali = P.nomer_detali
AND SPJ.nomer_detali = 'P1'
JOIN spasoi_ekz.s S
ON SPJ.nomer_postavshika = S.nomer_postavshika
WHERE SPJ.nomer_postavshika NOT IN (
SELECT nomer_postavshika
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.p P
ON SPJ.nomer_detali = P.nomer_detali
AND SPJ.nomer_detali != 'P1'
);
Результат:
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: выдать наименования деталей и общее их количество для всех наименований деталей, изготавливаемых в одном городе.
С этим запросом возникла неожиданная проблема - задание то ли неполное, то ли неправильное, потому что нельзя однозначно сказать, что по нему требуется сделать.
Так что тут несколько вариантов запросов (кто как понял).
Первый вариант запроса
и
Второй вариант запроса
Билет 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 nazvanie
FROM spasoi_ekz.p P, spasoi_ekz.spj SPJ
WHERE P.nomer_detali = SPJ.nomer_detali
AND 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
);
Результат:
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)