SQL-запросы к экзамену по СПАСОИ (10 семестр)

2013-06-05T15:04:40Z

195.19.34.135: /* Билет 11 */

<div style="float:right; clear:both; margin-right:1.0em;">__TOC__</div>

Билет экзамена по [[:Категория:Структурное проектирование АСОИ (10 семестр) | СПАСОИ]] состоит из двух частей:
# теория;
# SQL-запрос.

На этой странице собраны все сформированные запросы по билетам.

== Схема БД ==

Схема БД используется всё [[СПАСОИ_(10)_-_Лекция_№8_-_SQL#Некоторые возможности языка SQL | та же]], что была в прошлом семестре и на лекциях.

Для написания запросов и проверки их выполнения создана БД в СУБД [http://www.postgresql.org/ PostgreSQL].

Скрипт создания можно загрузить [http://yadi.sk/d/91d_8vGH5UmwP отсюда].

=== Таблицы БД ===

==== Поставщики ====

<syntaxhighlight lang="sql">
SELECT * FROM spasoi_ekz.s;
</syntaxhighlight>

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)

==== Детали ====

<syntaxhighlight lang="sql">
SELECT * FROM spasoi_ekz.p;
</syntaxhighlight>

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)

==== Изделия ====

<syntaxhighlight lang="sql">
SELECT * FROM spasoi_ekz.j;
</syntaxhighlight>

nomer_izdelia | nazvanie | gorod
---------------+-----------------------+-----------------
J1 | автомобиль | Магнитогорск
J2 | процессор | Зеленоград
J3 | торшер | Нижний Новгород
J4 | универсальное изделие | Париж
J5 | уникальное изделие | Бостон
J6 | велосипед 01/23 | Манчестер
J7 | изделие из болтов | Челябинск
J8 | шкаф | Ярославль
J9 | рама 02-01 | Череповец
(9 rows)

==== Сборки ====

<syntaxhighlight lang="sql">
SELECT * FROM spasoi_ekz.spj;
</syntaxhighlight>

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'.

Текст запроса:

<syntaxhighlight lang="sql">
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
)
);
</syntaxhighlight>

Результат:

nomer_postavshika
-------------------
S6
S2
(2 rows)

=== Билет 2 ===

Написать запрос SELECT: выдать номера деталей и общее их количество для всех номеров деталей, поставляемых более чем одним поставщиком.

Текст запроса:

<syntaxhighlight lang="sql">
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;
</syntaxhighlight>

Результат:

Номер детали | Сколько штук поставляется | Сколько у неё поставщиков
--------------+---------------------------+--------------------------
P1 | 15 | 2
P12 | 7 | 7
P4 | 10 | 2
(3 rows)

=== Билет 3 ===

Написать запрос SELECT: выдать номера поставщиков, поставляющих детали с номером 'P1' для какого-либо изделия в количестве (в поставке) большим, чем средний объём поставок деталей с номером 'P2' для этого изделия.

Текст запроса:

<syntaxhighlight lang="sql">
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'
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос, но длиннее и нерациональней''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
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'
);
</syntaxhighlight>
</div>
</div>

Результат:

nomer_postavshika
-------------------
S6
(1 row)

=== Билет 4 ===

Написать запрос SELECT: выдать номера изделий, для которых детали поставляет только поставщик с номером ‘S1’.

Текст запроса:

<syntaxhighlight lang="sql">
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'
);
</syntaxhighlight>

Результат:

nomer_izdelia
---------------
J5
(1 row)

=== Билет 5 ===

Написать запрос SELECT: выдать имена поставщиков, поставляющих детали с названием "Болт" в количестве (в поставке) большим, чем средний объём всех поставок деталей с номером 'P1'.

Текст запроса:

<syntaxhighlight lang="sql">
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'
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос, но без JOIN''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT imya
FROM spasoi_ekz.s
WHERE nomer_postavshika IN (
SELECT nomer_postavshika
FROM spasoi_ekz.spj
WHERE nomer_detali = (
SELECT nomer_detali
FROM spasoi_ekz.p
WHERE LOWER(nazvanie) = LOWER('Болт')
)
AND kolichestvo > (
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_detali = 'P1'
)
);
</syntaxhighlight>
</div>
</div>

Результат:

imya
------
Оша
Иванов
(2 rows)

=== Билет 6 ===

Написать запрос SELECT: выдать названия деталей, поставляемых поставщиком, проживающим в том же городе, где изготавливаются эти детали, для изделия с названием ‘Велосипед 01/23’.

Текст запроса:

<syntaxhighlight lang="sql">
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')
);
</syntaxhighlight>

Результат:

nazvanie
----------
рама
колесо
(2 rows)

=== Билет 7 ===

Написать запрос SELECT: выдать названия изделий, куда входят детали с названием 'Болт', поставляемых поставщиками с именем 'Иванов', в количестве (в поставке) большим, чем средний объём поставок для этого изделия.

Текст запроса:

<syntaxhighlight lang="sql">
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
)
);
</syntaxhighlight>

<div class="toccolours mw-collapsible mw-collapsed">
''Этот же запрос, но длиннее и нерациональней''
<div class="mw-collapsible-content">
<syntaxhighlight lang="sql">
SELECT nazvanie
FROM (
SELECT J.nazvanie, kolichestvo
FROM spasoi_ekz.s S, spasoi_ekz.p P, spasoi_ekz.j J, spasoi_ekz.spj SPJ
WHERE J.nomer_izdelia = SPJ.nomer_izdelia
AND P.nomer_detali = SPJ.nomer_detali
AND P.nazvanie = LOWER('Болт')
AND S.imya = 'Иванов'
AND S.nomer_postavshika = SPJ.nomer_postavshika
) A
WHERE kolichestvo >
(
SELECT AVG(kolichestvo)
FROM spasoi_ekz.spj
WHERE nomer_izdelia IN(
SELECT J.nomer_izdelia
FROM spasoi_ekz.s S, spasoi_ekz.p P, spasoi_ekz.j J, spasoi_ekz.spj SPJ
WHERE J.nomer_izdelia = SPJ.nomer_izdelia
AND P.nomer_detali = SPJ.nomer_detali
AND P.nazvanie = LOWER('Болт')
AND S.imya = 'Иванов'
AND S.nomer_postavshika = SPJ.nomer_postavshika
)
);
</syntaxhighlight>
</div>
</div>

Результат:

nazvanie
-------------------
шкаф
(1 row)

=== Билет 8 ===

Написать запрос SELECT: выдать номера изделий и общее количество деталей для них, поставляемых поставщиками с именем 'Петров'.

Текст запроса:

<syntaxhighlight lang="sql">
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;
</syntaxhighlight>

Результат:

nomer_izdelia | sum
---------------+-----
J3 | 6
J1 | 3
(2 rows)

=== Билет 9 ===

Написать запрос SELECT: выдать номера деталей и общее их количество для всех номеров деталей, которые входят только в одно изделие.

Текст запроса:

<syntaxhighlight lang="sql">
SELECT nomer_detali, SUM(kolichestvo)
FROM spasoi_ekz.spj
GROUP BY nomer_detali HAVING COUNT(nomer_izdelia) = 1;
</syntaxhighlight>

Результат:

nomer_detali | sum
--------------+-----
P9 | 1
P7 | 5
P3 | 50
P16 | 2
P13 | 14
P6 | 20
P8 | 25
(7 rows)

=== Билет 10 ===

Написать запрос SELECT: выдать имена поставщиков, поставляющих детали с названием 'Болт' для изделия с названием 'Рама 02-01' в количестве (в поставке) большим, чем минимальное значение поставки детали с номером 'P1'.

Текст запроса:

<syntaxhighlight lang="sql">
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'
);
</syntaxhighlight>

Результат:

imya
-------------
Русе Болтон
(1 row)

=== Билет 11 ===

Написать запрос SELECT: выдать названия городов и суммарное состояние проживающих в каждом городе поставщиков, у которых минимальный объём поставки деталей больше 1000.

Текст запроса:

<syntaxhighlight lang="sql">

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;
</syntaxhighlight>

Результат:

gorod | sost
----------+--------
Москва | 105000
(1 row)

=== Билет 12 ===

=== Билет 13 ===

=== Билет 14 ===

=== Билет 15 ===

=== Билет 16 ===

=== Билет 17 ===

=== Билет 18 ===

=== Билет 19 ===

=== Билет 20 ===

=== Билет 21 ===

=== Билет 22 ===

=== Билет 23 ===

=== Билет 24 ===

=== Билет 25 ===

=== Билет 26 ===

=== Билет 27 ===

=== Билет 28 ===

=== Билет 29 ===

=== Билет 30 ===

[[Категория:Структурное проектирование АСОИ (10 семестр)]]

Кафедра ИУ5 МГТУ им. Н.Э.Баумана, студенческое сообщество - Вклад [ru]

SQL-запросы к экзамену по СПАСОИ (10 семестр)