-- #1
--- Вывести содержимое всех столбцов таблицы Н_СЕССИЯ, поля типа DATE 
--- представить в формате DD.MM.YYYY HH24:MI:SS

SELECT ИД, СЭС_ИД, ЧЛВК_ИД, АУДИТОРИЯ,
      TO_CHAR(ДАТА,  'DD.MM.YYYY HH24:MI:SS') as ДАТА,
      TO_CHAR(ВРЕМЯ,  'DD.MM.YYYY HH24:MI:SS') as ВРЕМЯ,
      TO_CHAR(КОГДА_СОЗДАЛ,  'DD.MM.YYYY HH24:MI:SS') as КОГДА_СОЗДАЛ,
      TO_CHAR(КОГДА_ИЗМЕНИЛ,  'DD.MM.YYYY HH24:MI:SS') as КОГДА_ИЗМЕНИЛ,
      TO_CHAR(ВРЕМЯ_К,  'DD.MM.YYYY HH24:MI:SS') as ВРЕМЯ_К,
      TO_CHAR(ДАТА_К,  'DD.MM.YYYY HH24:MI:SS') as ДАТА_К,
      АУДИТОРИЯ_К, УЧГОД, ГРУППА, СЕМЕСТР, КТО_СОЗДАЛ, КТО_ИЗМЕНИЛ
FROM Н_СЕССИЯ;

-- #2
--- Вывести неповторяющиеся названия дисциплин из таблицы Н_ДИСЦИПЛИНЫ

SELECT DISTINCT НАИМЕНОВАНИЕ FROM Н_ДИСЦИПЛИНЫ;

-- #3
--- Вывести округленное значение разницы между днем рождения произвольной
--- персоны из Н_ЛЮДИ и началом текущего учебного года.

SELECT ROUND(TO_DATE('2014/09/01', 'yyyy/mm/dd') - ДАТА_РОЖДЕНИЯ)
FROM Н_ЛЮДИ
WHERE ИД = (SELECT MIN(ИД) FROM Н_ЛЮДИ);

-- #4
--- Получить фамилии и инициалы людей (в виде «Иванов И.И.), которые 
--- родились в том же месяце, что и произвольная персона из Н_ЛЮДИ.

SELECT ФАМИЛИЯ || ' ' || SUBSTR( ИМЯ, 0, 1) || '.' || 
       SUBSTR( ОТЧЕСТВО, 0, 1) || '.' 
FROM Н_ЛЮДИ 
WHERE TO_CHAR(ДАТА_РОЖДЕНИЯ, 'MM') = (SELECT TO_CHAR(ДАТА_РОЖДЕНИЯ, 'MM') 
                                      FROM Н_ЛЮДИ 
                                      WHERE ИД = 110103 AND 
                                            ФАМИЛИЯ = 'Афанасьев');

-- #5
--- Вывести фамилии, имена, отчества и номера (ИД) людей, фамилии которых
--- начинаются на те же 2 буквы, что и у произвольной персоны из Н_ЛЮДИ,
--- упорядочить по убыванию ФИО, вывести не более 75 строк.

SELECT ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО, ИД 
FROM Н_ЛЮДИ 
WHERE SUBSTR(ФАМИЛИЯ, 0, 2) = (SELECT SUBSTR(ФАМИЛИЯ, 0, 2) 
                               FROM Н_ЛЮДИ 
                               WHERE ИД = 110103 AND 
                                     ФАМИЛИЯ = 'Афанасьев') AND
      ROWNUM <= 75 
ORDER BY Фамилия DESC;

-- #6
--- Вывести список персон (фамилия, имя, отчество, ИД), для которых инициалы не
--- равны «А», «Б», «З» и «К», «У». Представить не менее 2х вариантов запроса.

SELECT ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО, ИД 
FROM Н_ЛЮДИ 
WHERE NOT REGEXP_LIKE (SUBSTR( ИМЯ, 0, 1), '^[АБЗКУ]{1}$') AND 
      NOT REGEXP_LIKE (SUBSTR( ОТЧЕСТВО, 0, 1), '^[АБЗКУ]{1}$');

SELECT ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО, ИД 
FROM Н_ЛЮДИ 
WHERE SUBSTR( ИМЯ, 0, 1) NOT IN ('А', 'Б', 'З', 'К', 'У') AND 
      SUBSTR( ОТЧЕСТВО, 0, 1) NOT IN ('А', 'Б', 'З', 'К', 'У');

-- #7
--- Вычислите количество персон, имя которых такое же, 
--- как у произвольной персоны из Н_ЛЮДИ, учесть возможность 
--- наличия дубликатов.

SELECT COUNT(DISTINCT ИД) 
FROM Н_ЛЮДИ 
WHERE ИМЯ = (SELECT ИМЯ FROM Н_ЛЮДИ WHERE ИД = 110103);

-- #8
--- Вывести, используя таблицу Н_ВЕДОМОСТИ, удвоенные 
--- (значение оценки*2) оценки произвольной персоны из Н_ЛЮДИ. 
--- Использовать NOT IN и регулярные выражения.

SELECT TO_NUMBER(ОЦЕНКА)*2 as УДВОЕННАЯ_ОЦЕНКА FROM Н_ВЕДОМОСТИ 
WHERE ЧЛВК_ИД = (SELECT ИД 
                 FROM Н_ЛЮДИ
                 WHERE NOT ИД NOT IN (110103) AND
                       REGEXP_LIKE(ФАМИЛИЯ, '^Афанасьев$')) AND 
      REGEXP_LIKE(ОЦЕНКА, '^[0-9]{1}$')

-- #9
--- Вывести, используя таблицу Н_ВЕДОМОСТИ, сумму оценок 7и 
--- произвольных персон из Н_ЛЮДИ. 
--- 7 персон задаются условием к таблице Н_ЛЮДИ.

SELECT SUM(TO_NUMBER(ОЦЕНКА)) FROM Н_ВЕДОМОСТИ 
WHERE ЧЛВК_ИД IN (SELECT ИД 
                  FROM Н_ЛЮДИ 
                  WHERE ИД IN (110103, 110105, 110106, 110107, 110108, 
                               110104, 110109)) AND 
      ОЦЕНКА NOT IN ('зачет');
 
-- #10
--- Получить декартово произведение N таблиц, где N равно 3й цифре вашего
--- табельного номера.

SELECT * FROM Н_ЛЮДИ, Н_ВЕДОМОСТИ;

-- #11
--- Вывести, используя таблицу Н_ВЕДОМОСТИ, среднюю оценку 7и 
--- произвольных персон из Н_ЛЮДИ, их фамилии, имена и отчества.

SELECT AVG(TO_NUMBER(ОЦЕНКА)), ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО 
FROM Н_ВЕДОМОСТИ 
JOIN Н_ЛЮДИ ON Н_ВЕДОМОСТИ.ЧЛВК_ИД = Н_ЛЮДИ.ИД 
WHERE ЧЛВК_ИД IN (SELECT ИД 
                  FROM Н_ЛЮДИ 
                  WHERE ИД IN (110103, 110105, 110106, 110107, 110108, 
                               110104, 110109)) AND 
      REGEXP_LIKE(ОЦЕНКА, '^[0-9]{1}$') 
GROUP BY ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО;

-- #12
--- Сформировать SQL-запрос для получения таблицы вида:
--- +====================================================================+
--- |                                     | Средняя оценка | Количество  |
--- |                                     |                | оценок      |
--- +====================================================================+
--- | Оценки 4 и 5 во всем университете   | 4.3            | 98          |
--- +--------------------------------------------------------------------+
--- | Оценки «зачет» в произвольном       | -              | 86          |
--- | учебном году во всем университете   |                |             |
--- +--------------------------------------------------------------------+
--- | Расстояние Левенштайна до вашей     | 2              | -           |
--- | фамилии от фамилии 10 персон,       |                |             |
--- | имеющих оценки 3, 4 и 5             |                |             |
--- +--------------------------------------------------------------------+

SELECT '', 'Средняя оценка', 'Количество оценок' 
FROM DUAL 
WHERE 0=1 

UNION SELECT 'Оценки 4 и 5 во всем университете', 
             REPLACE(TO_CHAR(ROUND(AVG(ОЦЕНКА),1)),',','.'),
                     TO_CHAR(COUNT(ОЦЕНКА)) 
      FROM Н_ВЕДОМОСТИ 
      WHERE ОЦЕНКА IN ('4','5')
      GROUP BY '' 

UNION SELECT 'Оценки «зачет» в произвольном учебном году во всем университете',
             '-', TO_CHAR(COUNT(ОЦЕНКА)) 
      FROM Н_ВЕДОМОСТИ 
      WHERE ОЦЕНКА = 'зачет' AND 
            TO_CHAR(ДАТА, 'YYYY') = '2003' 
      GROUP BY '' 

UNION ALL SELECT 'Расстояние Левенштайна до вашей фамилии от фамилии 10 персон,
имеющих оценки 3, 4 и 5', 
       TO_CHAR(utl_match.edit_distance('Назарьев', ФАМИЛИЯ)), '-' 
FROM (SELECT ФАМИЛИЯ 
      FROM Н_ЛЮДИ 
      WHERE ИД IN (
            SELECT ЧЛВК_ИД 
            FROM Н_ВЕДОМОСТИ  
            WHERE ОЦЕНКА in ('3','4','5') AND 
                  ROWNUM <=10));

-- #13
--- Получить список персон, получивших оценки 3 и 4 с 01.09 прошлого 
--- календарного года по 20.07 текущего календарного года, упорядочить 
--- список по ФИО. Использование объединений таблиц запрещено.

SELECT ФАМИЛИЯ || ' ' || ИМЯ || ' ' || ОТЧЕСТВО as ФИО 
FROM Н_ЛЮДИ 
WHERE ИД IN (
      SELECT DISTINCT ЧЛВК_ИД 
      FROM Н_ВЕДОМОСТИ 
      WHERE ОЦЕНКА IN('3','4') AND 
           ДАТА BETWEEN 
                TO_DATE('01.09.' || (TO_NUMBER(TO_CHAR(CURRENT_DATE, 'YYYY')-5))) 
                AND
                TO_DATE('20.07.' || TO_NUMBER(TO_CHAR(CURRENT_DATE, 'YYYY'))))
ORDER BY 1

-- #14
--- Получить список людей с наиболее частыми сочетаниями фамилии, имени и 
--- отчества, сумма оценок которых не превышает сумму цифр ИД произвольной 
--- персоны из таблицы Н_ЛЮДИ.

SELECT ЧЛВК_ИД FROM 
       (SELECT ЧЛВК_ИД, SUM(ОЦЕНКА) as СУММА 
        FROM Н_ВЕДОМОСТИ 
        WHERE ЧЛВК_ИД in 
              (SELECT ИД 
               FROM Н_ЛЮДИ 
               WHERE ФАМИЛИЯ || ' ' || ИМЯ || ' ' || ОТЧЕСТВО in (
                     SELECT ФИО FROM (
                                SELECT ФАМИЛИЯ || ' ' || ИМЯ || ' ' || ОТЧЕСТВО 
                                       as ФИО, 
                                       COUNT(1) as CNT 
                                FROM Н_ЛЮДИ 
                                GROUP BY 1 
                                ORDER BY 2 DESC) 
                     WHERE CNT = (
                           SELECT MAX(COUNT(ФАМИЛИЯ || ' ' || ИМЯ || ' ' || ОТЧЕСТВО)) 
                           FROM Н_ЛЮДИ 
                           GROUP BY ФАМИЛИЯ || ' ' || ИМЯ || ' ' || ОТЧЕСТВО))) 
               AND REGEXP_LIKE(ОЦЕНКА, '^[0-9]{1}$') 
        GROUP BY ЧЛВК_ИД)
 
WHERE СУММА <= (
      WITH t AS (SELECT MAX(ИД) as a FROM Н_ЛЮДИ)
           SELECT     9 * COALESCE((LENGTH(REGEXP_REPLACE(a, '[^9]', ''))),0)
                    + 8 * COALESCE((LENGTH(REGEXP_REPLACE(a, '[^8]', ''))),0)
                    + 7 * COALESCE((LENGTH(REGEXP_REPLACE(a, '[^7]', ''))),0)
                    + 6 * COALESCE((LENGTH(REGEXP_REPLACE(a, '[^6]', ''))),0)
                    + 5 * COALESCE((LENGTH(REGEXP_REPLACE(a, '[^5]', ''))),0)
                    + 4 * COALESCE((LENGTH(REGEXP_REPLACE(a, '[^4]', ''))),0)
                    + 3 * COALESCE((LENGTH(REGEXP_REPLACE(a, '[^3]', ''))),0)
                    + 2 * COALESCE((LENGTH(REGEXP_REPLACE(a, '[^2]', ''))),0)
                    + 1 * COALESCE((LENGTH(REGEXP_REPLACE(a, '[^1]', ''))),0)
      FROM t);