MainPage/DataBase/Lab 3

Университет ИТМО

Факультет ФПИ и КТ

Отчёт по лабораторной работе 3

«Информационная система и база данных»

Вариант: 3313110

Студент: Чжоу Хунсян

Группа: P33131

Преподаватель:

1 Текст задания.
2 Реализацию запросов на SQL

1 Текст задания.

Задание.

По варианту, выданному преподавателем, составить и выполнить запросы к базе данных “Учебный процесс”.
根据教师给出的选项，编写并执行对“教育过程（Учебный процесс）”数据库的查询。

Команда для подключения к базе данных ucheb:
连接 ucheb 数据库的命令：

psql -h pg -d ucheb

Отчёт по лабораторной работе должен содержать:
实验室报告必须包含：

Текст задания. 任务文本
Реализацию запросов на SQL. 在 SQL 中执行查询
Выводы по работе. 对工作的结论。

Темы для подготовки к защите лабораторной работы:
为实验室工作答辩准备的主题：

SQL
Соединение таблиц 连接表
Подзапросы 子查询

Составить запросы на языке SQL (пункты 1-7).

Сделать запрос для получения атрибутов из указанных таблиц, применив фильтры по указанным условиям:
发出请求从指定表中获取属性，根据指定条件应用过滤器：
Н_ТИПЫ_ВЕДОМОСТЕЙ, Н_ВЕДОМОСТИ.
Вывести атрибуты: Н_ТИПЫ_ВЕДОМОСТЕЙ.НАИМЕНОВАНИЕ, Н_ВЕДОМОСТИ.ДАТА.
显示属性：Н_ТИПЫ_ВЕДОМОСТЕЙ.НАИМЕНОВАНИЕ, Н_ВЕДОМОСТИ.ДАТА。
Фильтры (AND):
过滤器（与）： a) Н_ТИПЫ_ВЕДОМОСТЕЙ.ИД > 3.
b) Н_ВЕДОМОСТИ.ИД < 1426978.
Вид соединения: RIGHT JOIN. 连接类型：RIGHT JOIN
Сделать запрос для получения атрибутов из указанных таблиц, применив фильтры по указанным условиям:
发出请求从指定表中获取属性，并根据指定条件应用过滤器：
Таблицы: Н_ЛЮДИ, Н_ОБУЧЕНИЯ, Н_УЧЕНИКИ.
Вывести атрибуты: Н_ЛЮДИ.ФАМИЛИЯ, Н_ОБУЧЕНИЯ.ЧЛВК_ИД, Н_УЧЕНИКИ.ИД.
显示属性：Н_ЛЮДИ.ФАМИЛИЯ, Н_ОБУЧЕНИЯ.ЧЛВК_ИД, Н_УЧЕНИКИ.ИД
Фильтры: (AND)
过滤器（与）：
a) Н_ЛЮДИ.ОТЧЕСТВО > Сергеевич.
b) Н_ОБУЧЕНИЯ.ЧЛВК_ИД < 112514.
c) Н_УЧЕНИКИ.ГРУППА > 1101.
Вид соединения: RIGHT JOIN.
连接类型：RIGHT JOIN
Вывести число студентов группы 3102, которые младше 20 лет.
打印 3102 组中 20 岁以下学生的人数
Ответ должен содержать только одно число.
答案必须仅包含一个数字。
Найти группы, в которых в 2011 году было ровно 10 обучающихся студентов на ФКТИУ.
查找 2011 年恰好有 10 名学生在 FKTIU 学习的小组。
Для реализации использовать подзапрос.
要实现此目的，请使用子查询。
Выведите таблицу со средним возрастом студентов во всех группах (Группа, Средний возраст), где средний возраст меньше максимального возраста в группе 1100.
显示所有组中学生的平均年龄（组、平均年龄）的表格，其中平均年龄小于组 1100 中的最大年龄。
Получить список студентов, отчисленных до первого сентября 2012 года с заочной формы обучения (специальность: Программная инженерия). В результат включить:
获取 2012 年 9 月 1 日之前被函授课程（专业：软件工程）开除的学生名单。结果包括： номер группы; 组号；
номер, фамилию, имя и отчество студента; 学生的编号、姓氏、名字和父名；
номер пункта приказа; 订单商品编号；
Для реализации использовать соединение таблиц.
要实现此目的，请使用表连接。
Вывести список студентов, имеющих одинаковые фамилии, но не совпадающие даты рождения.
显示具有相同姓氏但出生日期不同的学生列表。

2 Реализацию запросов на SQL

-- 1
SELECT "Н_ТИПЫ_ВЕДОМОСТЕЙ"."НАИМЕНОВАНИЕ", "Н_ВЕДОМОСТИ"."ЧЛВК_ИД" FROM "Н_ТИПЫ_ВЕДОМОСТЕЙ"
RIGHT JOIN "Н_ВЕДОМОСТИ" ON "Н_ТИПЫ_ВЕДОМОСТЕЙ"."ИД" = "Н_ВЕДОМОСТИ"."ТВ_ИД"
WHERE
    "Н_ТИПЫ_ВЕДОМОСТЕЙ"."ИД" > 3 AND
    "Н_ВЕДОМОСТИ"."ИД" = 1426978;

-- 2
SELECT "Н_ЛЮДИ"."ФАМИЛИЯ", "Н_ОБУЧЕНИЯ"."ЧЛВК_ИД", "Н_УЧЕНИКИ"."ИД" FROM "Н_ЛЮДИ"
INNER JOIN "Н_ОБУЧЕНИЯ" ON "Н_ЛЮДИ"."ИД" = "Н_ОБУЧЕНИЯ"."ЧЛВК_ИД"
INNER JOIN "Н_УЧЕНИКИ" ON "Н_ОБУЧЕНИЯ"."ЧЛВК_ИД" = "Н_УЧЕНИКИ"."ЧЛВК_ИД"
WHERE
    "Н_ЛЮДИ"."ОТЧЕСТВО" > 'Сергеевич' AND
    "Н_ОБУЧЕНИЯ"."ЧЛВК_ИД" < 112514 AND
    CAST("Н_УЧЕНИКИ"."ГРУППА" AS integer) > 1101;

-- SELECT "Н_ЛЮДИ"."ОТЧЕСТВО", "Н_ОБУЧЕНИЯ"."ЧЛВК_ИД", "Н_УЧЕНИКИ"."ГРУППА",
--        "Н_ЛЮДИ"."ФАМИЛИЯ", "Н_ОБУЧЕНИЯ"."ЧЛВК_ИД", "Н_УЧЕНИКИ"."ИД" FROM "Н_ЛЮДИ"
-- INNER JOIN "Н_ОБУЧЕНИЯ" ON "Н_ЛЮДИ"."ИД" = "Н_ОБУЧЕНИЯ"."ЧЛВК_ИД"
-- INNER JOIN "Н_УЧЕНИКИ" ON "Н_ОБУЧЕНИЯ"."ЧЛВК_ИД" = "Н_УЧЕНИКИ"."ЧЛВК_ИД"
-- WHERE
--     "Н_ЛЮДИ"."ОТЧЕСТВО" > 'Сергеевич' AND
--     "Н_ОБУЧЕНИЯ"."ЧЛВК_ИД" < 112514 AND
--     CAST("Н_УЧЕНИКИ"."ГРУППА" AS integer) > 1101;

-- 3

SELECT count(*) FROM "Н_УЧЕНИКИ"
INNER JOIN "Н_ЛЮДИ" ON "Н_УЧЕНИКИ"."ЧЛВК_ИД" = "Н_ЛЮДИ"."ИД"
WHERE
    "Н_УЧЕНИКИ"."ГРУППА" = '3102' AND
    age("Н_ЛЮДИ"."ДАТА_РОЖДЕНИЯ") < CAST('20 years' AS interval);

SELECT age("Н_ЛЮДИ"."ДАТА_РОЖДЕНИЯ") AS age, * FROM "Н_УЧЕНИКИ"
INNER JOIN "Н_ЛЮДИ" ON "Н_УЧЕНИКИ"."ЧЛВК_ИД" = "Н_ЛЮДИ"."ИД"
WHERE
    "Н_УЧЕНИКИ"."ГРУППА" = '3102' AND
    age("Н_ЛЮДИ"."ДАТА_РОЖДЕНИЯ") < CAST('35 years' AS interval);

-- 4
SELECT "Н_УЧЕНИКИ"."ГРУППА" FROM "Н_УЧЕНИКИ"
WHERE
    "Н_УЧЕНИКИ"."ПЛАН_ИД" IN (
        SELECT "Н_ПЛАНЫ"."ИД" FROM "Н_ПЛАНЫ"
        INNER JOIN "Н_ОТДЕЛЫ" ON "Н_ПЛАНЫ"."ОТД_ИД" = "Н_ОТДЕЛЫ"."ИД"
        WHERE
            "Н_ОТДЕЛЫ"."КОРОТКОЕ_ИМЯ" = 'КТиУ' AND
            ("Н_ПЛАНЫ"."УЧЕБНЫЙ_ГОД" LIKE '%2011' OR
            "Н_ПЛАНЫ"."УЧЕБНЫЙ_ГОД" LIKE '2011%')
    )
GROUP BY "Н_УЧЕНИКИ"."ГРУППА" HAVING count("Н_УЧЕНИКИ"."ГРУППА") = 20;

SELECT "Н_УЧЕНИКИ"."ГРУППА", count("Н_УЧЕНИКИ"."ГРУППА") AS "КОЛИЧЕСТВО" FROM "Н_УЧЕНИКИ"
WHERE
        "Н_УЧЕНИКИ"."ПЛАН_ИД" IN (
        SELECT "Н_ПЛАНЫ"."ИД" FROM "Н_ПЛАНЫ"
        WHERE
                "Н_ПЛАНЫ"."ОТД_ИД" = 703 AND
            (SUBSTRING("Н_ПЛАНЫ"."УЧЕБНЫЙ_ГОД",1,4) = '2011' OR
             SUBSTRING("Н_ПЛАНЫ"."УЧЕБНЫЙ_ГОД",6,9)='2011')
    )
GROUP BY "Н_УЧЕНИКИ"."ГРУППА" HAVING count("Н_УЧЕНИКИ"."ГРУППА") = 20;

SELECT "Н_ОТДЕЛЫ"."ИД" FROM "Н_ОТДЕЛЫ" WHERE "КОРОТКОЕ_ИМЯ" = 'КТиУ';


-- select "Н_ГРУППЫ_ПЛАНОВ"."ГРУППА" from "Н_ЛЮДИ"
-- inner join "Н_УЧЕНИКИ" on "Н_УЧЕНИКИ"."ЧЛВК_ИД" = "Н_ЛЮДИ"."ИД"
-- inner join "Н_ГРУППЫ_ПЛАНОВ" on "Н_ГРУППЫ_ПЛАНОВ"."ГРУППА" = "Н_УЧЕНИКИ"."ГРУППА"
-- inner join "Н_ПЛАНЫ" ON "Н_ГРУППЫ_ПЛАНОВ"."ПЛАН_ИД" = "Н_ПЛАНЫ"."ИД"
-- INNER JOIN "Н_ОТДЕЛЫ" ON "Н_ОТДЕЛЫ"."ИД" = "Н_ПЛАНЫ"."ОТД_ИД"
-- where "Н_ОТДЕЛЫ"."КОРОТКОЕ_ИМЯ" = 'КТиУ' and
--       (SUBSTRING("Н_ПЛАНЫ"."УЧЕБНЫЙ_ГОД",1,4) = '2011' OR
--        SUBSTRING("Н_ПЛАНЫ"."УЧЕБНЫЙ_ГОД",6,9)='2011')
-- group by "Н_ГРУППЫ_ПЛАНОВ"."ГРУППА" having count("Н_ЛЮДИ"."ИД") = 20;

-- 5
SELECT STUDENT_AGE."ГРУППА" AS group_id, avg(STUDENT_AGE.age) AS average_age FROM (
    SELECT "Н_УЧЕНИКИ"."ГРУППА", cast(to_char(age("Н_ЛЮДИ"."ДАТА_РОЖДЕНИЯ"), 'yy') AS int) AS age FROM "Н_УЧЕНИКИ"
    INNER JOIN "Н_ЛЮДИ" ON "Н_УЧЕНИКИ"."ЧЛВК_ИД" = "Н_ЛЮДИ"."ИД"
) AS STUDENT_AGE
GROUP BY STUDENT_AGE."ГРУППА" having avg(STUDENT_AGE.age) < (
    SELECT max(cast(to_char(age("Н_ЛЮДИ"."ДАТА_РОЖДЕНИЯ"), 'yy') AS int)) FROM "Н_УЧЕНИКИ"
    INNER JOIN "Н_ЛЮДИ" ON "Н_УЧЕНИКИ"."ЧЛВК_ИД" = "Н_ЛЮДИ"."ИД"
    WHERE STUDENT_AGE."ГРУППА" = '1100'
);

-- 6
SELECT "Н_УЧЕНИКИ"."ГРУППА", "Н_ЛЮДИ"."ИД", "Н_ЛЮДИ"."ФАМИЛИЯ", "Н_ЛЮДИ"."ИМЯ", "Н_ЛЮДИ"."ОТЧЕСТВО", "Н_УЧЕНИКИ"."П_ПРКОК_ИД" FROM "Н_УЧЕНИКИ"
INNER JOIN "Н_ЛЮДИ" ON "Н_УЧЕНИКИ"."ЧЛВК_ИД" = "Н_ЛЮДИ"."ИД"
INNER JOIN "Н_ПЛАНЫ" ON "Н_УЧЕНИКИ"."ПЛАН_ИД" = "Н_ПЛАНЫ"."ИД"
INNER JOIN "Н_ФОРМЫ_ОБУЧЕНИЯ" ON "Н_ПЛАНЫ"."ФО_ИД" = "Н_ФОРМЫ_ОБУЧЕНИЯ"."ИД"
INNER JOIN "Н_НАПРАВЛЕНИЯ_СПЕЦИАЛ" ON "Н_ПЛАНЫ"."НАПС_ИД" = "Н_НАПРАВЛЕНИЯ_СПЕЦИАЛ"."ИД"
INNER JOIN "Н_НАПР_СПЕЦ" ON "Н_НАПРАВЛЕНИЯ_СПЕЦИАЛ"."НС_ИД" = "Н_НАПР_СПЕЦ"."ИД"
WHERE
    "Н_УЧЕНИКИ"."ПРИЗНАК" = 'отчисл' AND
    "Н_ФОРМЫ_ОБУЧЕНИЯ"."НАИМЕНОВАНИЕ" = 'Заочная' AND
    "Н_НАПР_СПЕЦ"."НАИМЕНОВАНИЕ" = 'Программная инженерия' AND
    "Н_УЧЕНИКИ"."КОНЕЦ" < '2012-09-01';

-- 7
SELECT "Н_ЛЮДИ"."ФАМИЛИЯ", "Н_ЛЮДИ"."ДАТА_РОЖДЕНИЯ" FROM "Н_УЧЕНИКИ"
INNER JOIN "Н_ЛЮДИ" ON "Н_УЧЕНИКИ"."ЧЛВК_ИД" = "Н_ЛЮДИ"."ИД"
WHERE
    "Н_ЛЮДИ"."ФАМИЛИЯ" IN (
        SELECT "Н_ЛЮДИ"."ФАМИЛИЯ" FROM "Н_УЧЕНИКИ"
        INNER JOIN "Н_ЛЮДИ" ON "Н_УЧЕНИКИ"."ЧЛВК_ИД" = "Н_ЛЮДИ"."ИД"
        GROUP BY "Н_ЛЮДИ"."ФАМИЛИЯ"
        HAVING
            count("Н_ЛЮДИ"."ФАМИЛИЯ") > 1
    )
;


SELECT DISTINCT "ФАМИЛИЯ", "ДАТА_РОЖДЕНИЯ" FROM (
SELECT "Н_ЛЮДИ"."ФАМИЛИЯ", "Н_ЛЮДИ"."ДАТА_РОЖДЕНИЯ" FROM "Н_УЧЕНИКИ"
INNER JOIN "Н_ЛЮДИ" ON "Н_УЧЕНИКИ"."ЧЛВК_ИД" = "Н_ЛЮДИ"."ИД"
WHERE
    "Н_ЛЮДИ"."ФАМИЛИЯ" IN (
        SELECT "Н_ЛЮДИ"."ФАМИЛИЯ" FROM "Н_УЧЕНИКИ"
        INNER JOIN "Н_ЛЮДИ" ON "Н_УЧЕНИКИ"."ЧЛВК_ИД" = "Н_ЛЮДИ"."ИД"
        GROUP BY "Н_ЛЮДИ"."ФАМИЛИЯ"
        HAVING
        count("Н_ЛЮДИ"."ФАМИЛИЯ") > 1
    )
) AS T;

SELECT * FROM (
    SELECT "Н_ЛЮДИ"."ФАМИЛИЯ", "Н_ЛЮДИ"."ДАТА_РОЖДЕНИЯ" FROM "Н_УЧЕНИКИ"
    INNER JOIN "Н_ЛЮДИ" ON "Н_УЧЕНИКИ"."ЧЛВК_ИД" = "Н_ЛЮДИ"."ИД"
    WHERE
        "Н_ЛЮДИ"."ФАМИЛИЯ" IN (
            SELECT "Н_ЛЮДИ"."ФАМИЛИЯ" FROM "Н_УЧЕНИКИ"
            INNER JOIN "Н_ЛЮДИ" ON "Н_УЧЕНИКИ"."ЧЛВК_ИД" = "Н_ЛЮДИ"."ИД"
            GROUP BY "Н_ЛЮДИ"."ФАМИЛИЯ"
            HAVING
            count("Н_ЛЮДИ"."ФАМИЛИЯ") > 1
            -- count("Н_ЛЮДИ"."ФАМИЛИЯ" || to_char("Н_ЛЮДИ"."ДАТА_РОЖДЕНИЯ", 'yyyy-mm-dd')) = 1
      )
) AS t1
WHERE "ФАМИЛИЯ" IN (
    SELECT "ФАМИЛИЯ" FROM (
        SELECT "Н_ЛЮДИ"."ФАМИЛИЯ", "Н_ЛЮДИ"."ДАТА_РОЖДЕНИЯ" FROM "Н_УЧЕНИКИ"
                                                                   INNER JOIN "Н_ЛЮДИ" ON "Н_УЧЕНИКИ"."ЧЛВК_ИД" = "Н_ЛЮДИ"."ИД"
        WHERE
              "Н_ЛЮДИ"."ФАМИЛИЯ" IN (
              SELECT "Н_ЛЮДИ"."ФАМИЛИЯ" FROM "Н_УЧЕНИКИ"
                INNER JOIN "Н_ЛЮДИ" ON "Н_УЧЕНИКИ"."ЧЛВК_ИД" = "Н_ЛЮДИ"."ИД"
              GROUP BY "Н_ЛЮДИ"."ФАМИЛИЯ"
              HAVING
                      count("Н_ЛЮДИ"."ФАМИЛИЯ") > 1
              -- count("Н_ЛЮДИ"."ФАМИЛИЯ" || to_char("Н_ЛЮДИ"."ДАТА_РОЖДЕНИЯ", 'yyyy-mm-dd')) = 1
          )
) AS T2
    GROUP BY "ФАМИЛИЯ"
    HAVING count(DISTINCT "ДАТА_РОЖДЕНИЯ") > 1
);

-- dop
SELECT "ФАМИЛИЯ", "ИМЯ", "ОТЧЕСТВО", AGE FROM(
    SELECT "Н_ЛЮДИ"."ФАМИЛИЯ", "Н_ЛЮДИ"."ИМЯ", "Н_ЛЮДИ"."ОТЧЕСТВО", cast(to_char(age("Н_ЛЮДИ"."ДАТА_РОЖДЕНИЯ"), 'yy') AS int) AS AGE FROM "Н_ЛЮДИ"
        INNER JOIN "Н_СЕССИЯ" ON "Н_ЛЮДИ"."ИД" = "Н_СЕССИЯ"."ЧЛВК_ИД"
        INNER JOIN "Н_СОДЕРЖАНИЯ_ЭЛЕМЕНТОВ_СТРОК" ON "Н_СЕССИЯ"."СЭС_ИД" = "Н_СОДЕРЖАНИЯ_ЭЛЕМЕНТОВ_СТРОК"."ИД"
        INNER JOIN "Н_ЭЛЕМЕНТЫ_СТРОК" ON "Н_СОДЕРЖАНИЯ_ЭЛЕМЕНТОВ_СТРОК"."ЭСТ_ИД" = "Н_ЭЛЕМЕНТЫ_СТРОК"."ИД"
        INNER JOIN "Н_ОТДЕЛЫ" ON "Н_ЭЛЕМЕНТЫ_СТРОК"."ОТД_ИД" = "Н_ОТДЕЛЫ"."ИД"
    WHERE "Н_ОТДЕЛЫ"."КОРОТКОЕ_ИМЯ" = 'ВТ'
) AS RES
WHERE AGE = (
    SELECT min(AGE) FROM (
        SELECT cast(to_char(age("Н_ЛЮДИ"."ДАТА_РОЖДЕНИЯ"), 'yy') AS int) AS AGE FROM "Н_ЛЮДИ"
            INNER JOIN "Н_СЕССИЯ" ON "Н_ЛЮДИ"."ИД" = "Н_СЕССИЯ"."ЧЛВК_ИД"
            INNER JOIN "Н_СОДЕРЖАНИЯ_ЭЛЕМЕНТОВ_СТРОК" ON "Н_СЕССИЯ"."СЭС_ИД" = "Н_СОДЕРЖАНИЯ_ЭЛЕМЕНТОВ_СТРОК"."ИД"
            INNER JOIN "Н_ЭЛЕМЕНТЫ_СТРОК" ON "Н_СОДЕРЖАНИЯ_ЭЛЕМЕНТОВ_СТРОК"."ЭСТ_ИД" = "Н_ЭЛЕМЕНТЫ_СТРОК"."ИД"
            INNER JOIN "Н_ОТДЕЛЫ" ON "Н_ЭЛЕМЕНТЫ_СТРОК"."ОТД_ИД" = "Н_ОТДЕЛЫ"."ИД"
            WHERE "Н_ОТДЕЛЫ"."КОРОТКОЕ_ИМЯ" = 'ВТ' AND
                cast(to_char(age("Н_ЛЮДИ"."ДАТА_РОЖДЕНИЯ"), 'yy') AS int) > 0
    ) AS RES
);


SELECT "Н_ЛЮДИ"."ФАМИЛИЯ", "Н_ЛЮДИ"."ИМЯ", "Н_ЛЮДИ"."ОТЧЕСТВО" FROM "Н_ЛЮДИ"
INNER JOIN "Н_СЕССИЯ" ON "Н_ЛЮДИ"."ИД" = "Н_СЕССИЯ"."ЧЛВК_ИД"
INNER JOIN "Н_СОДЕРЖАНИЯ_ЭЛЕМЕНТОВ_СТРОК" ON "Н_СЕССИЯ"."СЭС_ИД" = "Н_СОДЕРЖАНИЯ_ЭЛЕМЕНТОВ_СТРОК"."ИД"
INNER JOIN "Н_ЭЛЕМЕНТЫ_СТРОК" ON "Н_СОДЕРЖАНИЯ_ЭЛЕМЕНТОВ_СТРОК"."ЭСТ_ИД" = "Н_ЭЛЕМЕНТЫ_СТРОК"."ИД"
INNER JOIN "Н_ОТДЕЛЫ" ON "Н_ЭЛЕМЕНТЫ_СТРОК"."ОТД_ИД" = "Н_ОТДЕЛЫ"."ИД"
WHERE "Н_ОТДЕЛЫ"."КОРОТКОЕ_ИМЯ" = 'КТ'
ORDER BY cast(to_char(age("Н_ЛЮДИ"."ДАТА_РОЖДЕНИЯ"), 'yy') AS int)
LIMIT 1;

ITMO-PE