lab2.sql

--1
SELECT 
    Н_ОЦЕНКИ.ПРИМЕЧАНИЕ, 
    Н_ВЕДОМОСТИ.ЧЛВК_ИД
FROM 
    Н_ОЦЕНКИ
INNER JOIN 
    Н_ВЕДОМОСТИ 
ON 
    Н_ОЦЕНКИ.КОД = Н_ВЕДОМОСТИ.ОЦЕНКА
WHERE 
    Н_ОЦЕНКИ.КОД < 'зачет' AND 
    Н_ВЕДОМОСТИ.ЧЛВК_ИД > 117219 AND 
    Н_ВЕДОМОСТИ.ЧЛВК_ИД = 117219;
--проверка что таблицы есть 
SELECT * FROM Н_ОЦЕНКИ LIMIT 10;

SELECT * FROM Н_ВЕДОМОСТИ LIMIT 10;

--проверка фильтров
-- Условие 1
SELECT 
    Н_ОЦЕНКИ.ПРИМЕЧАНИЕ, 
    Н_ВЕДОМОСТИ.ЧЛВК_ИД
FROM 
    Н_ОЦЕНКИ
INNER JOIN 
    Н_ВЕДОМОСТИ 
ON 
    Н_ОЦЕНКИ.КОД = Н_ВЕДОМОСТИ.ОЦЕНКА
WHERE 
    Н_ОЦЕНКИ.КОД < 'зачет';

-- Условие 2
SELECT 
    Н_ОЦЕНКИ.ПРИМЕЧАНИЕ, 
    Н_ВЕДОМОСТИ.ЧЛВК_ИД
FROM 
    Н_ОЦЕНКИ
INNER JOIN 
    Н_ВЕДОМОСТИ 
ON 
    Н_ОЦЕНКИ.КОД = Н_ВЕДОМОСТИ.ОЦЕНКА
WHERE 
    Н_ВЕДОМОСТИ.ЧЛВК_ИД > 117219;

-- Условие 3
SELECT 
    Н_ОЦЕНКИ.ПРИМЕЧАНИЕ, 
    Н_ВЕДОМОСТИ.ЧЛВК_ИД
FROM 
    Н_ОЦЕНКИ
INNER JOIN 
    Н_ВЕДОМОСТИ 
ON 
    Н_ОЦЕНКИ.КОД = Н_ВЕДОМОСТИ.ОЦЕНКА
WHERE 
    Н_ВЕДОМОСТИ.ЧЛВК_ИД = 117219;

--2
SELECT 
    Н_ЛЮДИ.ОТЧЕСТВО, 
    Н_ВЕДОМОСТИ.ЧЛВК_ИД, 
    Н_СЕССИЯ.ЧЛВК_ИД
FROM 
    Н_ЛЮДИ
INNER JOIN 
    Н_ВЕДОМОСТИ 
ON 
    Н_ЛЮДИ.ИД = Н_ВЕДОМОСТИ.ЧЛВК_ИД
INNER JOIN 
    Н_СЕССИЯ 
ON 
    Н_ЛЮДИ.ИД = Н_СЕССИЯ.ЧЛВК_ИД
WHERE 
    Н_ЛЮДИ.ФАМИЛИЯ < 'Афанасьев' AND 
    Н_ВЕДОМОСТИ.ДАТА > '2010-06-18';
--проверка 
SELECT * FROM Н_ЛЮДИ WHERE ФАМИЛИЯ < 'Афанасьев';
SELECT * FROM Н_ВЕДОМОСТИ WHERE ДАТА > '2010-06-18';

--3 для каждого студента
SELECT 
    CASE 
        WHEN COUNT(*) > 0 THEN 'Да'
        ELSE 'Нет'
    END AS РЕЗУЛЬТАТ
FROM 
    Н_УЧЕНИКИ
INNER JOIN 
    Н_ЛЮДИ 
ON 
    Н_УЧЕНИКИ.ИД = Н_ЛЮДИ.ИД
WHERE 
    Н_УЧЕНИКИ.ГРУППА = '3102' AND 
    Н_ЛЮДИ.ИНН IS NULL
GROUP BY 
    Н_ЛЮДИ.ИМЯ;
--просто да/нет
SELECT 
    CASE 
        WHEN COUNT(*) > 0 THEN 'Да'
        ELSE 'Нет'
    END AS РЕЗУЛЬТАТ
FROM 
    Н_УЧЕНИКИ
INNER JOIN 
    Н_ЛЮДИ 
ON 
    Н_УЧЕНИКИ.ЧЛВК_ИД = Н_ЛЮДИ.ИД
WHERE 
    Н_УЧЕНИКИ.ГРУППА = '3102' AND 
    Н_ЛЮДИ.ИНН IS NULL;

-- Проверить доступные группы
SELECT DISTINCT ГРУППА FROM Н_УЧЕНИКИ;

-- Проверить учеников в группе 3102
SELECT * FROM Н_УЧЕНИКИ WHERE ГРУППА = '3102';

-- Проверить людей без ИНН
SELECT * FROM Н_ЛЮДИ WHERE ИНН IS NULL;

--4
SELECT 
    Н_ЛЮДИ.ИМЯ, 
    COUNT(*) AS КОЛИЧЕСТВО
FROM 
    Н_УЧЕНИКИ
INNER JOIN 
    Н_ЛЮДИ 
ON 
    Н_УЧЕНИКИ.ЧЛВК_ИД = Н_ЛЮДИ.ИД
INNER JOIN 
    Н_ВИДЫ_ОБУЧЕНИЯ 
ON 
    Н_УЧЕНИКИ.ВИД_ОБУЧ_ИД = Н_ВИДЫ_ОБУЧЕНИЯ.ИД
WHERE 
    Н_ВИДЫ_ОБУЧЕНИЯ.НАИМЕНОВАНИЕ = 'заочная'
GROUP BY 
    Н_ЛЮДИ.ИМЯ
HAVING 
    COUNT(*) < 10;
    );
--модер
SELECT 
    Н_ЛЮДИ.ИМЯ, 
    COUNT(*) AS КОЛИЧЕСТВО
FROM 
    Н_УЧЕНИКИ
INNER JOIN 
    Н_ЛЮДИ 
ON 
    Н_УЧЕНИКИ.ЧЛВК_ИД = Н_ЛЮДИ.ИД
INNER JOIN 
    Н_ВИДЫ_ОБУЧЕНИЯ 
ON 
    Н_УЧЕНИКИ.ВИД_ОБУЧ_ИД = Н_ВИДЫ_ОБУЧЕНИЯ.ИД
WHERE 
    Н_ВИДЫ_ОБУЧЕНИЯ.НАИМЕНОВАНИЕ = 'заочная'
GROUP BY 
    Н_ЛЮДИ.ИМЯ;
----исправл
SELECT 
    Н_ЛЮДИ.ИМЯ, 
    COUNT(*) AS КОЛИЧЕСТВО
FROM 
    Н_УЧЕНИКИ
INNER JOIN 
    Н_ЛЮДИ 
ON 
    Н_УЧЕНИКИ.ЧЛВК_ИД = Н_ЛЮДИ.ИД
GROUP BY 
    Н_ЛЮДИ.ИМЯ;


--проверка
SELECT DISTINCT НАИМЕНОВАНИЕ FROM Н_ВИДЫ_ОБУЧЕНИЯ;
------------------------------
SELECT 
    Н_УЧЕНИКИ.ГРУППА, 
    Н_УЧЕНИКИ.ВИД_ОБУЧ_ИД, 
    Н_ВИДЫ_ОБУЧЕНИЯ.НАИМЕНОВАНИЕ
FROM 
    Н_УЧЕНИКИ
INNER JOIN 
    Н_ВИДЫ_ОБУЧЕНИЯ 
ON 
    Н_УЧЕНИКИ.ВИД_ОБУЧ_ИД = Н_ВИДЫ_ОБУЧЕНИЯ.ИД
LIMIT 10;

--5
SELECT 
    Н_УЧЕНИКИ.ГРУППА, 
    AVG(EXTRACT(YEAR FROM AGE(Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ))) AS СРЕДНИЙ_ВОЗРАСТ
FROM 
    Н_УЧЕНИКИ
INNER JOIN 
    Н_ЛЮДИ 
ON 
    Н_УЧЕНИКИ.ЧЛВК_ИД = Н_ЛЮДИ.ИД
GROUP BY 
    Н_УЧЕНИКИ.ГРУППА
HAVING 
    AVG(EXTRACT(YEAR FROM AGE(Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ))) > (
        SELECT 
            MAX(EXTRACT(YEAR FROM AGE(Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ)))
        FROM 
            Н_УЧЕНИКИ
        INNER JOIN 
            Н_ЛЮДИ 
        ON 
            Н_УЧЕНИКИ.ЧЛВК_ИД = Н_ЛЮДИ.ИД
        WHERE 
            Н_УЧЕНИКИ.ГРУППА = '1100'
    );
--проверка
-- Проверить существующие группы в Н_УЧЕНИКИ
SELECT DISTINCT ГРУППА FROM Н_УЧЕНИКИ;

-- Проверить данные о людях, связанных с Н_УЧЕНИКИ
SELECT Н_УЧЕНИКИ.ГРУППА, Н_ЛЮДИ.ДАТА_РОЖДЕНИЯ
FROM Н_УЧЕНИКИ
INNER JOIN Н_ЛЮДИ 
ON Н_УЧЕНИКИ.ЧЛВК_ИД = Н_ЛЮДИ.ИД
LIMIT 10;


--6
SELECT 
    Н_УЧЕНИКИ.ГРУППА, 
    Н_ЛЮДИ.ИД AS НОМЕР, 
    Н_ЛЮДИ.ФАМИЛИЯ, 
    Н_ЛЮДИ.ИМЯ, 
    Н_ЛЮДИ.ОТЧЕСТВО, 
    Н_УЧЕНИКИ.П_ПРКОК_ИД AS ПУНКТ_ПРИКАЗА
FROM 
    Н_УЧЕНИКИ
INNER JOIN 
    Н_ЛЮДИ 
ON 
    Н_УЧЕНИКИ.ЧЛВК_ИД = Н_ЛЮДИ.ИД
WHERE 
    Н_УЧЕНИКИ.КОНЕЦ_ПО_ПРИКАЗУ > '2012-09-01' AND 
    Н_УЧЕНИКИ.ВИД_ОБУЧ_ИД = (
        SELECT ИД 
        FROM Н_ВИДЫ_ОБУЧЕНИЯ 
        WHERE НАИМЕНОВАНИЕ = 'очная'
    ) AND 
    Н_УЧЕНИКИ.ПЛАН_ИД = (
        SELECT ИД 
        FROM Н_НАПР_СПЕЦ 
        WHERE КОД_НАПРСПЕЦ = '230101'
    );
-----исправл
SELECT 
    Н_УЧЕНИКИ.ГРУППА, 
    Н_ЛЮДИ.ИД AS НОМЕР, 
    Н_ЛЮДИ.ФАМИЛИЯ, 
    Н_ЛЮДИ.ИМЯ, 
    Н_ЛЮДИ.ОТЧЕСТВО, 
    Н_УЧЕНИКИ.П_ПРКОК_ИД AS НОМЕР_ПРИКАЗА
FROM 
    Н_УЧЕНИКИ
INNER JOIN 
    Н_ЛЮДИ 
ON 
    Н_УЧЕНИКИ.ЧЛВК_ИД = Н_ЛЮДИ.ИД
WHERE 
    Н_УЧЕНИКИ.КОНЕЦ_ПО_ПРИКАЗУ > '2012-09-01'; AND 
    Н_УЧЕНИКИ.ВИД_ОБУЧ_ИД = (
        SELECT ИД 
        FROM Н_ВИДЫ_ОБУЧЕНИЯ 
        WHERE НАИМЕНОВАНИЕ = 'очная'
    ) AND 
    Н_УЧЕНИКИ.ПЛАН_ИД = (
        SELECT ИД 
        FROM Н_НАПР_СПЕЦ 
        WHERE КОД_НАПРСПЕЦ = '230101'
    );

--проверка
SELECT * FROM Н_НАПР_СПЕЦ WHERE КОД_НАПРСПЕЦ = '230101';

--7
SELECT 
    A.ФАМИЛИЯ, 
    A.ИМЯ AS ИМЯ_1, 
    A.ДАТА_РОЖДЕНИЯ AS ДАТА_РОЖДЕНИЯ_1, 
    B.ИМЯ AS ИМЯ_2, 
    B.ДАТА_РОЖДЕНИЯ AS ДАТА_РОЖДЕНИЯ_2
FROM 
    Н_ЛЮДИ AS A
INNER JOIN 
    Н_ЛЮДИ AS B 
ON 
    A.ФАМИЛИЯ = B.ФАМИЛИЯ AND 
    (A.ИМЯ <> B.ИМЯ OR A.ДАТА_РОЖДЕНИЯ <> B.ДАТА_РОЖДЕНИЯ)