---

--6.1 Вывести из таблиц «Кафедра», «Специальность» и «Студент» данные о студентах, которые обучаются на данном факультете (например, «ит»).
SELECT 
    С.Рег_номер,
    С.Фамилия AS Студент,
    П.Направление,
    К.Название AS Кафедра,
    К.Факультет
FROM Студент С
INNER JOIN Специальность П ON С.Номер = П.Номер
INNER JOIN Кафедра К ON П.Шифр = К.Шифр
WHERE K.Факультет = 'ит';

--6.2 Вывести из таблиц «Кафедра», «Специальность» и «Сотрудник» данные о выпускающих кафедрах (факультет, шифр, название, фамилию заведующего). Выпускающей считается та кафедра, на которую есть ссылки в таблице «Специальность».
SELECT DISTINCT
    К.Факультет,
    К.Шифр,
    К.Название AS Кафедра,
    СОТ.Фамилия AS Заведующий
FROM Кафедра К
INNER JOIN Специальность П ON К.Шифр = П.Шифр
INNER JOIN Сотрудник СОТ ON К.Шифр = СОТ.Шифр
WHERE СОТ.Должность LIKE '%зав. кафедрой%';

--6.3 Вывести в запросе для каждого сотрудника номер и фамилию его непосредственного руководителя. Для заведующих кафедрами поле руководителя оставить пустым.
SELECT 
    С.Таб_номер AS Таб_номер_Сотрудника,
    С.Фамилия AS Сотрудник,
    IIF(С.Таб_номер = С.Шеф, NULL, С.Шеф) AS Таб_номер_Руководителя,
    IIF(С.Таб_номер = С.Шеф, NULL, Р.Фамилия) AS Руководитель
FROM Сотрудник С
LEFT JOIN Сотрудник Р ON С.Шеф = Р.Таб_номер;

--6.4 Вывести список студентов, сдавших минимум два экзамена.
SELECT 
    С.Рег_номер,
    С.Фамилия,
    COUNT(Э.Код) AS Количество_экзаменов
FROM Студент С
INNER JOIN Экзамен Э ON С.Рег_номер = Э.Рег_номер
GROUP BY С.Рег_номер, С.Фамилия
HAVING COUNT(Э.Код) >= 2;

--6.5 Вывести список инженеров с зарплатой, меньшей 20000 руб.
SELECT 
    С.Таб_номер,
    С.Фамилия,
    С.Должность,
    С.Зарплата,
    ИНЖ.Специализация AS Специализация_Инженера
FROM Сотрудник С
INNER JOIN Инженер ИНЖ ON С.Таб_номер = ИНЖ.Таб_номер
WHERE С.Должность LIKE '%инженер%' AND С.Зарплата < 20000;

--6.6 Вывести список студентов, сдавших экзамены в заданной аудитории (например, 'T505').
SELECT DISTINCT 
    С.Рег_номер,
    С.Фамилия,
    Э.Аудитория
FROM Студент С
INNER JOIN Экзамен Э ON С.Рег_номер = Э.Рег_номер
WHERE Э.Аудитория = 'T505';

--6.7 Вывести из таблиц «Студент» и «Экзамен» учетные номера и фамилии студентов, а также количество сданных экзаменов и средний балл для каждого студента только для тех студентов, у которых средний балл не меньше заданного (например, 4).
SELECT 
    С.Рег_номер,
    С.Фамилия,
    COUNT(Э.Оценка) AS [Количество экзаменов],
    AVG(CAST(Э.Оценка AS FLOAT)) AS [Средний балл]
FROM Студент С
INNER JOIN Экзамен Э ON С.Рег_номер = Э.Рег_номер
GROUP BY С.Рег_номер, С.Фамилия
HAVING AVG(CAST(Э.Оценка AS FLOAT)) >= 4;

--6.8 Вывести список заведующих кафедрами и их зарплаты, и степень.
SELECT 
    С.Фамилия AS Заведующий,
    С.Зарплата,
    П.Степень
FROM Сотрудник С
INNER JOIN Зав_кафедрой З ON С.Таб_номер = З.Таб_номер
INNER JOIN Преподаватель П ON С.Таб_номер = П.Таб_номер
WHERE С.Должность LIKE '%зав. кафедрой%';

--6.9 Вывести список профессоров.
SELECT 
    С.Таб_номер,
    С.Фамилия,
    С.Должность,
    П.Звание,
    П.Степень
FROM Сотрудник С
INNER JOIN Преподаватель П ON С.Таб_номер = П.Таб_номер
WHERE П.Звание = 'профессор';

--6.10 Вывести название дисциплины, фамилию, должность и степень преподавателя, дату и место проведения экзаменов в хронологическом порядке в заданном интервале даты.
SELECT DISTINCT
    Д.Название AS Дисциплина,
    С.Фамилия,
    С.Должность,
    П.Степень,
    Э.Дата,
    Э.Аудитория
FROM Экзамен Э
INNER JOIN Дисциплина Д ON Э.Код = Д.Код
INNER JOIN Сотрудник С ON Э.Таб_номер = С.Таб_номер
INNER JOIN Преподаватель П ON Э.Таб_номер = П.Таб_номер
WHERE Э.Дата BETWEEN '2015-06-05' AND '2015-06-15'
ORDER BY Э.Дата;

--6.11 Вывести фамилию преподавателей, принявших более трех экзаменов.
SELECT 
    С.Фамилия,
    COUNT(Э.Дата) AS [Количество экзаменов]
FROM Экзамен Э
INNER JOIN Сотрудник С ON Э.Таб_номер = С.Таб_номер
GROUP BY С.Фамилия
HAVING COUNT(Э.Дата) > 3;

--6.12 Вывести список студентов, не сдавших ни одного экзамена в указанной дате (например, в дату '05.06.2015').
SELECT 
    С.Рег_номер,
    С.Фамилия
FROM Студент С
WHERE С.Рег_номер NOT IN (
    SELECT Э.Рег_номер 
    FROM Экзамен Э 
    WHERE Э.Дата = '05.06.2015'
);