По варианту, выданному преподавателем, составить и выполнить запросы к базе данных "Учебный процесс". Вариант: 7712
- Текст задания.
- Реализацию запросов на SQL.
- Выводы по работе.
- SQL
- Соединение таблиц
- Подзапросы
Составить запросы на языке SQL (пункты 1-7).
-
Сделать запрос для получения атрибутов из указанных таблиц, применив фильтры по указанным условиям: Таблицы: НОЦЕНКИ, НВЕДОМОСТИ. Вывести атрибуты: НОЦЕНКИ.ПРИМЕЧАНИЕ, НВЕДОМОСТИ.ДАТА. Фильтры (AND): a) НОЦЕНКИ.КОД = 5. b) НВЕДОМОСТИ.ДАТА = 2022-06-08. c) Н_ВЕДОМОСТИ.ДАТА > 2010-06-18. Вид соединения: RIGHT JOIN.
-
Сделать запрос для получения атрибутов из указанных таблиц, применив фильтры по указанным условиям: Таблицы: НЛЮДИ, НОБУЧЕНИЯ, НУЧЕНИКИ. Вывести атрибуты: НЛЮДИ.ОТЧЕСТВО, НОБУЧЕНИЯ.НЗК, НУЧЕНИКИ.ИД. Фильтры: (AND) a) НЛЮДИ.ИД = 152862. b) НОБУЧЕНИЯ.НЗК > 999080. Вид соединения: INNER JOIN.
-
Вывести число студентов вечерней формы обучения, которые без ИНН. Ответ должен содержать только одно число.
-
В таблице НГРУППЫПЛАНОВ найти номера планов, по которым обучается (обучалось) менее 2 групп на кафедре вычислительной техники.Для реализации использовать соединение таблиц.
-
Выведите таблицу со средним возрастом студентов во всех группах (Группа, Средний возраст), где средний возраст больше минимального возраста в группе 1100.
-
Получить список студентов, отчисленных после первого сентября 2012 года с заочной формы обучения (специальность: 230101). В результат включить: номер группы; номер, фамилию, имя и отчество студента; номер пункта приказа. Для реализации использовать соединение таблиц.
-
Вывести список студентов, имеющих одинаковые фамилии, но не совпадающие даты рождения.
Для начала, выполним запрос, касающийся только отношения Н_ВЕДОМОСТИ:
SELECT Н_ВЕДОМОСТИ.ДАТА, Н_ВЕДОМОСТИ.ОЦЕНКА
FROM Н_ВЕДОМОСТИ
WHERE Н_ВЕДОМОСТИ.ДАТА = '2022-06-08'
AND Н_ВЕДОМОСТИ.ДАТА > '2010-06-18';Результат (таблица 1):
| ДАТА | ОЦЕНКА |
|---|---|
| 2022-06-08 00:00:00 | неявка |
| 2022-06-08 00:00:00 | зачет |
| 2022-06-08 00:00:00 | неявка |
Далее, выполним запрос, касающийся только отношения Н_ОЦЕНКИ:
SELECT Н_ОЦЕНКИ.ПРИМЕЧАНИЕ, Н_ОЦЕНКИ.КОД
FROM Н_ОЦЕНКИ
WHERE Н_ОЦЕНКИ.КОД = '5';Результат (таблица 2):
| ПРИМЕЧАНИЕ | КОД |
|---|---|
| отлично | 5 |
Заметим, что атрибут Н_ОЦЕНКИ.КОД может быть использован для объединения отношений Н_ОЦЕНКИ и Н_ВЕДОМОСТИ. Но результат такой операции нам даст пустое отношение:
Запрос:
SELECT Н_ОЦЕНКИ.ПРИМЕЧАНИЕ, Н_ВЕДОМОСТИ.ДАТА
FROM Н_ОЦЕНКИ
RIGHT JOIN Н_ВЕДОМОСТИ
ON Н_ВЕДОМОСТИ.ОЦЕНКА = Н_ОЦЕНКИ.КОД
WHERE Н_ВЕДОМОСТИ.ДАТА = '2022-06-08'
AND Н_ВЕДОМОСТИ.ДАТА > '2010-06-18'
AND Н_ОЦЕНКИ.КОД = '5';Даст нам пустое отношение.
Выполним запрос касаемо отношения Н_ЛЮДИ:
SELECT Н_ЛЮДИ.ИД, Н_ЛЮДИ.ОТЧЕСТВО
FROM Н_ЛЮДИ
WHERE Н_ЛЮДИ.ИД = 152862;Результат:
| "ИД" | "ОТЧЕСТВО" |
|---|---|
| 152862 | "Юльевна" |
Как видим, случай как и в задании 1, получился тоже довольно вырожденный. Результат всего один кортеж.
Далее, выполним запрос, касаемо отношения Н_ОБУЧЕНИЯ:
SELECT Н_ОБУЧЕНИЯ.НЗК
FROM Н_ОБУЧЕНИЯ
WHERE Н_ОБУЧЕНИЯ.НЗК > '999080';Результат пустой.
Для того, чтобы объединить отношения Н_ОБУЧЕНИЯ и Н_ЛЮДИ, заметим, что атрибуты по которым мы их можем связать следующие: Н_ОБУЧЕНИЯ.ЧЛВК_ИД и Н_ЛЮДИ.ИД.
SELECT *
FROM Н_ЛЮДИ
INNER JOIN Н_ОБУЧЕНИЯ ON Н_ОБУЧЕНИЯ.ЧЛВК_ИД = Н_ЛЮДИ.ИД;Навесим еще отношение Н_УЧЕНИКИ ко всему выше через атрибут Н_УЧЕНИКИ.ИД:
SELECT *
FROM Н_ЛЮДИ
INNER JOIN Н_ОБУЧЕНИЯ ON Н_ОБУЧЕНИЯ.ЧЛВК_ИД = Н_ЛЮДИ.ИД
INNER JOIN Н_УЧЕНИКИ ON Н_УЧЕНИКИ.ИД = Н_ЛЮДИ.ИД;Теперь выведем только требуемые атрибуты и добавим фильтры, предсказуемо получим пустое отношение, запрос:
SELECT Н_ЛЮДИ.ОТЧЕСТВО, Н_ОБУЧЕНИЯ.НЗК, Н_УЧЕНИКИ.ИД
FROM Н_ЛЮДИ
INNER JOIN Н_ОБУЧЕНИЯ
ON Н_ОБУЧЕНИЯ.ЧЛВК_ИД = Н_ЛЮДИ.ИД
INNER JOIN Н_УЧЕНИКИ
ON Н_УЧЕНИКИ.ИД = Н_ЛЮДИ.ИД
WHERE Н_ЛЮДИ.ИД = 152862
AND Н_ОБУЧЕНИЯ.НЗК > '999080';Даст нам пустое отношение.
Для того, чтобы сопоставить ученика с формой обучения, воспользуемся отношением Н_ПЛАНЫ, через него мы можем получить форму обучения, т.к. за планом закреплена форма обучения через Н_ПЛАНЫ.ФО_ИД, а за каждым студентом закреплен план Н_УЧЕНИКИ.ПЛАН_ИД
Выполнив запрос ниже, я выяснил, что ни у кого из людей нет инн, тем не менее, по заданию я учту это в условии:
SELECT Н_ЛЮДИ.ФАМИЛИЯ, Н_ЛЮДИ.ИМЯ, Н_ЛЮДИ.ОТЧЕСТВО, Н_ЛЮДИ.ИНН ,Н_ФОРМЫ_ОБУЧЕНИЯ.НАИМЕНОВАНИЕ
FROM Н_УЧЕНИКИ
JOIN Н_ЛЮДИ ON Н_УЧЕНИКИ.ЧЛВК_ИД=Н_ЛЮДИ.ИД
JOIN Н_ПЛАНЫ ON Н_УЧЕНИКИ.ПЛАН_ИД = Н_ПЛАНЫ.ПЛАН_ИД
JOIN Н_ФОРМЫ_ОБУЧЕНИЯ ON Н_ПЛАНЫ.ФО_ИД = Н_ФОРМЫ_ОБУЧЕНИЯ.ИД;Результат (первые 10 строк):
| ФАМИЛИЯ | ИМЯ | ОТЧЕСТВО | ИНН | НАИМЕНОВАНИЕ |
|---|---|---|---|---|
| Захаров | Антон | Павлович | "NULL" | Очная |
| Краснояров | Станислав | Евгеньевич | "NULL" | Очная |
| Воеводкина | Елена | Сергеевна | "NULL" | Очная |
| Кузнецов | Александр | Александрович | "NULL" | Очная |
| Кучук | Максим | Григорьевич | "NULL" | Очная |
| Новосельский | Вениамин | Борисович | "NULL" | Очная |
| Чугунова | Татьяна | Михайловна | "NULL" | Очная |
| Чумаков | Андрей | Евгеньевич | "NULL" | Очная |
| Улановский | Михаил | Александрович | "NULL" | Очная |
| Гуркин | Дмитрий | Ильич | "NULL" | Очная |
| Галушко | Илья | Сергеевич | "NULL" | Очная |
Теперь просто добавим условие для ИНН, формы обучения и посчитаем:
SELECT COUNT(*)
FROM Н_УЧЕНИКИ
JOIN Н_ЛЮДИ
ON Н_УЧЕНИКИ.ЧЛВК_ИД=Н_ЛЮДИ.ИД
JOIN Н_ПЛАНЫ
ON Н_УЧЕНИКИ.ПЛАН_ИД = Н_ПЛАНЫ.ПЛАН_ИД
JOIN Н_ФОРМЫ_ОБУЧЕНИЯ
ON Н_ПЛАНЫ.ФО_ИД = Н_ФОРМЫ_ОБУЧЕНИЯ.ИД
WHERE Н_ФОРМЫ_ОБУЧЕНИЯ.ИД = 2
AND Н_ЛЮДИ.ИНН IS NULL;Результат: 834
Выполним запрос, который выведет нам все группы на кафедре ВТ
SELECT Н_ГРУППЫ_ПЛАНОВ.ГРУППА, Н_ГРУППЫ_ПЛАНОВ.ПЛАН_ИД
FROM Н_ГРУППЫ_ПЛАНОВ
JOIN Н_ПЛАНЫ ON Н_ГРУППЫ_ПЛАНОВ.ПЛАН_ИД=Н_ПЛАНЫ.ПЛАН_ИД
JOIN Н_ОТДЕЛЫ ON Н_ОТДЕЛЫ.ИД=Н_ПЛАНЫ.ОТД_ИД_ЗАКРЕПЛЕН_ЗА
WHERE Н_ОТДЕЛЫ.КОРОТКОЕ_ИМЯ = 'ВТ';Результат (первые 10 строк):
| "ГРУППА" | "ПЛАН_ИД" |
|---|---|
| "5111" | 5621 |
| "5113" | 6147 |
| "5103" | 5610 |
| "5114" | 6150 |
| "5101" | 5612 |
| "5111" | 4698 |
| "5113" | 5548 |
| "5103" | 4695 |
| "5102" | 4694 |
| "5114" | 5549 |
Теперь добавим условие, которое посчитает для каждого плана число его использований:
SELECT Н_ГРУППЫ_ПЛАНОВ.ПЛАН_ИД, COUNT(*)
FROM Н_ГРУППЫ_ПЛАНОВ
JOIN Н_ПЛАНЫ ON Н_ГРУППЫ_ПЛАНОВ.ПЛАН_ИД=Н_ПЛАНЫ.ПЛАН_ИД
JOIN Н_ОТДЕЛЫ ON Н_ОТДЕЛЫ.ИД=Н_ПЛАНЫ.ОТД_ИД_ЗАКРЕПЛЕН_ЗА
WHERE Н_ОТДЕЛЫ.КОРОТКОЕ_ИМЯ = 'ВТ'
GROUP BY Н_ГРУППЫ_ПЛАНОВ.ПЛАН_ИД;Результат (первые 5 строк): "ПЛАН_ИД"|"count" 4542|1 272|4 773|2 304|1 3721|1
Теперь выведем только те, которые встречаются 1 раз:
SELECT Н_ГРУППЫ_ПЛАНОВ.ПЛАН_ИД
FROM Н_ГРУППЫ_ПЛАНОВ
JOIN Н_ПЛАНЫ ON Н_ГРУППЫ_ПЛАНОВ.ПЛАН_ИД=Н_ПЛАНЫ.ПЛАН_ИД
JOIN Н_ОТДЕЛЫ ON Н_ОТДЕЛЫ.ИД=Н_ПЛАНЫ.ОТД_ИД_ЗАКРЕПЛЕН_ЗА
WHERE Н_ОТДЕЛЫ.КОРОТКОЕ_ИМЯ = 'ВТ'
GROUP BY Н_ГРУППЫ_ПЛАНОВ.ПЛАН_ИД
HAVING COUNT(*) = 1;Выполним запрос, чтобы сопоставить человека, группу и данные о возрасте:
SELECT Н_УЧЕНИКИ.ИД, Н_УЧЕНИКИ.ГРУППА, Н_ЛЮДИ.ИМЯ, Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ, Н_ЛЮДИ.ДАТА_СМЕРТИ
FROM Н_УЧЕНИКИ
JOIN Н_ЛЮДИ ON Н_УЧЕНИКИ.ИД=Н_ЛЮДИ.ИД
LIMIT 10;Результат:
| "ИД" | "ГРУППА" | "ИМЯ" | "ДАТА_РОЖДЕНИЯ" | "ДАТА_СМЕРТИ" |
|---|---|---|---|---|
| 110139 | "1100" | "Алексей" | "2011-11-11 00:00:00" | "9999-09-09 00:00:00" |
| 110141 | "1100" | "Руслан" | "2011-11-11 00:00:00" | "9999-09-09 00:00:00" |
| 116494 | "2539" | "Василий" | "1979-03-13 00:00:00" | "9999-09-09 00:00:00" |
| 111721 | "1508" | "Сергей" | "2011-11-11 00:00:00" | "9999-09-09 00:00:00" |
| 110145 | "6539" | "Игорь" | "2011-11-11 00:00:00" | "9999-09-09 00:00:00" |
| 110146 | "6539" | "Светлана" | "2011-11-11 00:00:00" | "9999-09-09 00:00:00" |
| 110149 | "6539" | "Любовь" | "2011-11-11 00:00:00" | "9999-09-09 00:00:00" |
| 110152 | "6539" | "Татьяна" | "2011-11-11 00:00:00" | "9999-09-09 00:00:00" |
| 110153 | "6539" | "Светлана" | "2011-11-11 00:00:00" | "9999-09-09 00:00:00" |
| 110154 | "6539" | "Александра" | "1973-10-22 00:00:00" | "9999-09-09 00:00:00" |
Давайте посчитаем для каждого возраст:
SELECT
Н_УЧЕНИКИ.ИД,
Н_УЧЕНИКИ.ГРУППА,
Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ,
Н_ЛЮДИ.ДАТА_СМЕРТИ,
CASE WHEN Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ > current_timestamp
THEN NULL
ELSE AGE(current_timestamp, Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ)
END
AS возраст
FROM Н_УЧЕНИКИ
JOIN Н_ЛЮДИ ON Н_УЧЕНИКИ.ИД=Н_ЛЮДИ.ИД;Результат:
| "ИД" | "ГРУППА" | "ДАТА_РОЖДЕНИЯ" | "ДАТА_СМЕРТИ" | "возраст" |
|---|---|---|---|---|
| 110139 | "1100" | "2011-11-11 00:00:00" | "9999-09-09 00:00:00" | "11 years 2 mons 12 days 18:32:05.096208" |
| 110141 | "1100" | "2011-11-11 00:00:00" | "9999-09-09 00:00:00" | "11 years 2 mons 12 days 18:32:05.096208" |
| 116494 | "2539" | "1979-03-13 00:00:00" | "9999-09-09 00:00:00" | "43 years 10 mons 10 days 18:32:05.096208" |
| 111721 | "1508" | "2011-11-11 00:00:00" | "9999-09-09 00:00:00" | "11 years 2 mons 12 days 18:32:05.096208" |
| 110145 | "6539" | "2011-11-11 00:00:00" | "9999-09-09 00:00:00" | "11 years 2 mons 12 days 18:32:05.096208" |
| 110146 | "6539" | "2011-11-11 00:00:00" | "9999-09-09 00:00:00" | "11 years 2 mons 12 days 18:32:05.096208" |
| 110149 | "6539" | "2011-11-11 00:00:00" | "9999-09-09 00:00:00" | "11 years 2 mons 12 days 18:32:05.096208" |
| 110152 | "6539" | "2011-11-11 00:00:00" | "9999-09-09 00:00:00" | "11 years 2 mons 12 days 18:32:05.096208" |
| 110153 | "6539" | "2011-11-11 00:00:00" | "9999-09-09 00:00:00" | "11 years 2 mons 12 days 18:32:05.096208" |
| 110154 | "6539" | "1973-10-22 00:00:00" | "9999-09-09 00:00:00" | "49 years 3 mons 1 day 18:32:05.096208" |
Теперь напишем запрос, который явно получает возраст, минимальный для группы 1100:
SELECT
MIN(AGE(current_timestamp, Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ))
FROM Н_УЧЕНИКИ
JOIN Н_ЛЮДИ ON Н_УЧЕНИКИ.ИД=Н_ЛЮДИ.ИД
WHERE AGE(current_timestamp, Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ) > '0 days'
AND Н_УЧЕНИКИ.ГРУППА = '1100';Результат:
| min |
|---|
| 11 years 2 mons 12 days 18:34:44.95577 |
Соберем из двух запросов выше один, для того чтобы вывести только те записи, которые удовлетворяют условию с помощью HAVING:
SELECT
Н_УЧЕНИКИ.ГРУППА,
AVG(AGE(current_timestamp, Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ)) AS "Средний возраст"
FROM Н_УЧЕНИКИ
JOIN Н_ЛЮДИ ON Н_УЧЕНИКИ.ИД=Н_ЛЮДИ.ИД
WHERE AGE(current_timestamp, Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ) > '0 days'
GROUP BY Н_УЧЕНИКИ.ГРУППА
HAVING AVG(AGE(current_timestamp, Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ)) >
(
SELECT
MIN(AGE(current_timestamp, Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ))
FROM Н_УЧЕНИКИ
JOIN Н_ЛЮДИ ON Н_УЧЕНИКИ.ИД=Н_ЛЮДИ.ИД
WHERE AGE(current_timestamp, Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ) > '0 days'
AND Н_УЧЕНИКИ.ГРУППА = '1100'
);Результат (первые 10 строк):
| "ГРУППА" | "Средний возраст" |
|---|---|
| "2508" | "21 years 10 mons 7 days 30:37:44.718433" |
| "3102" | "41 years 1 mon 29 days 37:49:44.718433" |
| "1105" | "33 years 10 mons 25 days 35:57:44.747233" |
| "4104" | "35 years 4 mons 31 days 24:37:44.718433" |
| "6112" | "42 years 18 days 18:37:44.718433" |
| "5110" | "32 years 9 mons 10 days 18:37:44.718433" |
| "4100" | "40 years 10 mons 5 days 18:37:44.718433" |
| "4106" | "29 years 8 mons 23 days 38:37:44.718433" |
| "6110" | "36 years 4 mons 38 days 34:37:44.718433" |
| "1539" | "33 years 1 day 18:37:44.718433" |
Разделим задание на пункты:
- Получить список студентов
- отчисленных после первого сентября 2012 года
- с заочной формы обучения
- (специальность: 230101)
- В результат включить:
- номер группы
- номер студента
- фамилию студента
- имя студента
- отчество студента
- номер пункта приказа
Каждому пункту выше соответствует атрибут:
- Получить список студентов
- отчисленных после первого сентября 2012 года — Н_УЧЕНИКИ.НАЧАЛО
- с заочной формы обучения — НПЛАНЫ.ФОИД
- (специальность: 230101) – ННАПРСПЕЦ.КОД_НАПРСПЕЦ
- В результат включить:
- номер группы — Н_УЧЕНИКИ.ГРУППА
- номер студента — НУЧЕНИКИ.ЧЛВКИД
- фамилию студента — Н_ЛЮДИ.ФАМИЛИЯ
- имя студента — Н_ЛЮДИ.ИМЯ
- отчество студента — Н_ЛЮДИ.ОТЧЕСТВО
- номер пункта приказа — НУЧЕНИКИ.ППРКОКИД (В комментарии указанно, что это лишь внешний ключ к НПУНКТЫПРИКАЗОВОК, но отношение с таким именем мне найти не удалось)
Запрос:
SELECT
Н_УЧЕНИКИ.ГРУППА AS "Номер группы",
Н_УЧЕНИКИ.ЧЛВК_ИД AS "Номер студента",
Н_ЛЮДИ.ФАМИЛИЯ,
Н_ЛЮДИ.ИМЯ,
Н_ЛЮДИ.ОТЧЕСТВО,
Н_УЧЕНИКИ.П_ПРКОК_ИД AS "ИД пункта приказа"
FROM Н_УЧЕНИКИ
JOIN Н_ПЛАНЫ
ON Н_УЧЕНИКИ.ПЛАН_ИД = Н_ПЛАНЫ.ПЛАН_ИД
JOIN Н_НАПР_СПЕЦ
ON Н_ПЛАНЫ.НАПС_ИД = Н_НАПР_СПЕЦ.ИД
JOIN Н_ЛЮДИ
ON Н_УЧЕНИКИ.ЧЛВК_ИД = Н_ЛЮДИ.ИД
WHERE Н_УЧЕНИКИ.ПРИЗНАК = 'отчисл'
AND Н_УЧЕНИКИ.СОСТОЯНИЕ = 'утвержден'
AND Н_УЧЕНИКИ.НАЧАЛО > '2022-09-01'
AND Н_НАПР_СПЕЦ.КОД_НАПРСПЕЦ = '230101'
AND Н_ПЛАНЫ.ФО_ИД = 3;Результат пуст.
Очень странно, что нельзя создавать представления. Пришлось использовать подзапросы.
Ход мыслей:
-- запрос 1
-- получим всех учеников
SELECT
Н_ЛЮДИ.ИД,
Н_ЛЮДИ.ФАМИЛИЯ,
Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ
FROM
Н_ЛЮДИ
WHERE
Н_ЛЮДИ.ИД IN (
SELECT
Н_УЧЕНИКИ.ЧЛВК_ИД
FROM
Н_УЧЕНИКИ);
-- запрос 2
-- получим все даты рождения, которые встречаются более одного раза
SELECT
q1.ДАТА_РОЖДЕНИЯ,
count(q1.ДАТА_РОЖДЕНИЯ)
FROM (
SELECT
Н_ЛЮДИ.ИД,
Н_ЛЮДИ.ФАМИЛИЯ,
Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ
FROM
Н_ЛЮДИ
WHERE
Н_ЛЮДИ.ИД IN (
SELECT
Н_УЧЕНИКИ.ЧЛВК_ИД
FROM
Н_УЧЕНИКИ)) AS q1
GROUP BY
q1.ДАТА_РОЖДЕНИЯ
HAVING
count(q1.ДАТА_РОЖДЕНИЯ) > 1;
-- запрос 3
-- получим всех учеников, дата рождения которых встречается более одного раза
SELECT
*
FROM (
SELECT
Н_ЛЮДИ.ИД,
Н_ЛЮДИ.ФАМИЛИЯ,
Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ
FROM
Н_ЛЮДИ
WHERE
Н_ЛЮДИ.ИД IN (
SELECT
Н_УЧЕНИКИ.ЧЛВК_ИД
FROM
Н_УЧЕНИКИ)) AS all_students
WHERE
all_students.ДАТА_РОЖДЕНИЯ IN (
SELECT
q1.ДАТА_РОЖДЕНИЯ
FROM (
SELECT
Н_ЛЮДИ.ИД,
Н_ЛЮДИ.ФАМИЛИЯ,
Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ
FROM
Н_ЛЮДИ
WHERE
Н_ЛЮДИ.ИД IN (
SELECT
Н_УЧЕНИКИ.ЧЛВК_ИД
FROM
Н_УЧЕНИКИ)) AS q1
GROUP BY
q1.ДАТА_РОЖДЕНИЯ
HAVING
count(q1.ДАТА_РОЖДЕНИЯ) > 1);
-- запрос 4
-- теперь нужно убрать всех студентов из предыдущего запроса, чья фамилия встречается только 1 раз, сначала найдем такие фамилии
SELECT
ФАМИЛИЯ,
count(*)
FROM (
SELECT
ФАМИЛИЯ
FROM (
SELECT
Н_ЛЮДИ.ИД,
Н_ЛЮДИ.ФАМИЛИЯ,
Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ
FROM
Н_ЛЮДИ
WHERE
Н_ЛЮДИ.ИД IN (
SELECT
Н_УЧЕНИКИ.ЧЛВК_ИД
FROM
Н_УЧЕНИКИ)) AS all_students
WHERE
all_students.ДАТА_РОЖДЕНИЯ IN (
SELECT
q1.ДАТА_РОЖДЕНИЯ
FROM (
SELECT
Н_ЛЮДИ.ИД,
Н_ЛЮДИ.ФАМИЛИЯ,
Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ
FROM
Н_ЛЮДИ
WHERE
Н_ЛЮДИ.ИД IN (
SELECT
Н_УЧЕНИКИ.ЧЛВК_ИД
FROM
Н_УЧЕНИКИ)) AS q1
GROUP BY
q1.ДАТА_РОЖДЕНИЯ
HAVING
count(q1.ДАТА_РОЖДЕНИЯ) > 1)) AS birthday_more_than_once
GROUP BY
ФАМИЛИЯ
HAVING
count(ФАМИЛИЯ) = 1;
-- запрос 5
-- исключим студентов с фамилиями, полученными в запросе 4 из запроса 3, это и будет ответ на вопрос задачи
SELECT
*
FROM (
SELECT
*
FROM (
SELECT
Н_ЛЮДИ.ИД,
Н_ЛЮДИ.ФАМИЛИЯ,
Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ
FROM
Н_ЛЮДИ
WHERE
Н_ЛЮДИ.ИД IN (
SELECT
Н_УЧЕНИКИ.ЧЛВК_ИД
FROM
Н_УЧЕНИКИ)) AS all_students
WHERE
all_students.ДАТА_РОЖДЕНИЯ IN (
SELECT
q1.ДАТА_РОЖДЕНИЯ
FROM (
SELECT
Н_ЛЮДИ.ИД,
Н_ЛЮДИ.ФАМИЛИЯ,
Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ
FROM
Н_ЛЮДИ
WHERE
Н_ЛЮДИ.ИД IN (
SELECT
Н_УЧЕНИКИ.ЧЛВК_ИД
FROM
Н_УЧЕНИКИ)) AS q1
GROUP BY
q1.ДАТА_РОЖДЕНИЯ
HAVING
count(q1.ДАТА_РОЖДЕНИЯ) > 1)) AS query3
WHERE
query3.ФАМИЛИЯ NOT IN (
SELECT
ФАМИЛИЯ
FROM (
SELECT
ФАМИЛИЯ
FROM (
SELECT
Н_ЛЮДИ.ИД,
Н_ЛЮДИ.ФАМИЛИЯ,
Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ
FROM
Н_ЛЮДИ
WHERE
Н_ЛЮДИ.ИД IN (
SELECT
Н_УЧЕНИКИ.ЧЛВК_ИД
FROM
Н_УЧЕНИКИ)) AS all_students
WHERE
all_students.ДАТА_РОЖДЕНИЯ IN (
SELECT
q1.ДАТА_РОЖДЕНИЯ
FROM (
SELECT
Н_ЛЮДИ.ИД,
Н_ЛЮДИ.ФАМИЛИЯ,
Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ
FROM
Н_ЛЮДИ
WHERE
Н_ЛЮДИ.ИД IN (
SELECT
Н_УЧЕНИКИ.ЧЛВК_ИД
FROM
Н_УЧЕНИКИ)) AS q1
GROUP BY
q1.ДАТА_РОЖДЕНИЯ
HAVING
count(q1.ДАТА_РОЖДЕНИЯ) > 1)) AS birthday_more_than_once
GROUP BY
ФАМИЛИЯ
HAVING
count(ФАМИЛИЯ) = 1)
ORDER BY
ФАМИЛИЯ;Запрос:
SELECT
*
FROM (
SELECT
*
FROM (
SELECT
Н_ЛЮДИ.ИД,
Н_ЛЮДИ.ФАМИЛИЯ,
Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ
FROM
Н_ЛЮДИ
WHERE
Н_ЛЮДИ.ИД IN (
SELECT
Н_УЧЕНИКИ.ЧЛВК_ИД
FROM
Н_УЧЕНИКИ)) AS all_students
WHERE
all_students.ДАТА_РОЖДЕНИЯ IN (
SELECT
q1.ДАТА_РОЖДЕНИЯ
FROM (
SELECT
Н_ЛЮДИ.ИД,
Н_ЛЮДИ.ФАМИЛИЯ,
Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ
FROM
Н_ЛЮДИ
WHERE
Н_ЛЮДИ.ИД IN (
SELECT
Н_УЧЕНИКИ.ЧЛВК_ИД
FROM
Н_УЧЕНИКИ)) AS q1
GROUP BY
q1.ДАТА_РОЖДЕНИЯ
HAVING
count(q1.ДАТА_РОЖДЕНИЯ) > 1)) AS query3
WHERE
query3.ФАМИЛИЯ NOT IN (
SELECT
ФАМИЛИЯ
FROM (
SELECT
ФАМИЛИЯ
FROM (
SELECT
Н_ЛЮДИ.ИД,
Н_ЛЮДИ.ФАМИЛИЯ,
Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ
FROM
Н_ЛЮДИ
WHERE
Н_ЛЮДИ.ИД IN (
SELECT
Н_УЧЕНИКИ.ЧЛВК_ИД
FROM
Н_УЧЕНИКИ)) AS all_students
WHERE
all_students.ДАТА_РОЖДЕНИЯ IN (
SELECT
q1.ДАТА_РОЖДЕНИЯ
FROM (
SELECT
Н_ЛЮДИ.ИД,
Н_ЛЮДИ.ФАМИЛИЯ,
Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ
FROM
Н_ЛЮДИ
WHERE
Н_ЛЮДИ.ИД IN (
SELECT
Н_УЧЕНИКИ.ЧЛВК_ИД
FROM
Н_УЧЕНИКИ)) AS q1
GROUP BY
q1.ДАТА_РОЖДЕНИЯ
HAVING
count(q1.ДАТА_РОЖДЕНИЯ) > 1)) AS birthday_more_than_once
GROUP BY
ФАМИЛИЯ
HAVING
count(ФАМИЛИЯ) = 1)
ORDER BY
ФАМИЛИЯ;Результат (первые 10 строк):
| "ИД" | "ФАМИЛИЯ" | "ДАТА_РОЖДЕНИЯ" |
|---|---|---|
| 153319 | "Абрамов" | "1992-03-25 00:00:00" |
| 113854 | "Абрамов" | "2011-11-11 00:00:00" |
| 142342 | "Абрамов" | "1989-01-22 00:00:00" |
| 116670 | "Авдеев" | "2011-11-11 00:00:00" |
| 153320 | "Авдеев" | "1992-01-18 00:00:00" |
| 145409 | "Агапова" | "1990-12-13 00:00:00" |
| 137740 | "Агапова" | "1988-05-18 00:00:00" |
| 111665 | "Аксенов" | "1977-09-17 00:00:00" |
| 120184 | "Аксенов" | "1983-05-23 00:00:00" |
| 110595 | "Аксенов" | "2011-11-11 00:00:00" |
В ходе работы я изучил как можно использовать запросы SQL для выборки необходимых данных из схемы. В работе я использовал соединения таблиц, подзапросы, условия. Также использовал даталогическую модель и комментарии к атрибутам представленные в схеме public БД ucheb.