Материал: Oracle_SQL_5

4. GROUP BY (группирование по одному или нескольким столбцам)

По каждой дисциплине, читаемой преподавателемпрофессором, вывести следующую информацию под соответствующими именами столбцов:

название дисциплины Дисциплина

количество преподавателей, которые читают эту дисциплину К-во преподавателей

количество кафедр, преподаватели которых эту дисциплину читают К-во кафедр

количество групп, которым эта дисциплина читается К-во групп

COUNT(DISTINCT t.TchPK) AS "К-во преподавателей", COUNT(DISTINCT t.DepFK) AS "К-во кафедр", COUNT(DISTINCT l.GrpFK) AS "К-во групп"

FROM SUBJECT s, LECTURE l, TEACHER t

WHERE s.SbjPK=l.SbjFK AND l.TchFK=t.TchPK AND LOWER(t.Post)= 'профессор'

GROUP BY s.Name;

5. Выражения в GROUP BY

Для каждой группы 3-го курса кафедры ИПО факультета компьютерных наук вывести следующую информацию в соответствующих столбцах:

-номер группы. Если номер равен NULL, вывести константу “Нет номера”- Группа

-день недели. Если день недели равен NULL, вывести константу “Отсутствет”- День недели

-количество пар, которые имеет группа в этот день - К-во занятий

-к-во преподавателей, проводящих занятия в этой группе в этот день

-К-во преподавателей

SELECT NVL(TO_CHAR(g.Num), 'Нет номера') AS "Группа",

FROM FACULTY f, DEPARTMENT d, SGROUP g, LECTURE l

WHERE f.FacPK=d.FacFK AND d.DepPK=g.DepFK AND g.GrpPK=l.GrpFK AND

LOWER(f.Name)='компьютерные науки' AND UPPER(d.Name)='ИПО'AND

g.Course=3

GROUP BY NVL(TO_CHAR(g.Num), 'Нет номера'), NVL(l.Day, 'Отсутствует');

6. Фраза HAVING

Для каждой тройки значений преподаватель-группа- дисциплина таких, что указанный преподаватель преподает указанной группе указанную дисциплину, вывести

-имя преподавателя,

-номер группы,

-название дисциплины,

-количество занятий, которое имеет это этот преподаватель для этой группы по этой дисциплине,

при условии, что:

- указанный преподаватель преподает указанной группе указанную дисциплину не более, чем в 3-х аудиториях

SELECT t.Name, g.Num, s.Name, COUNT(*)

FROM TEACHER t, SGROUP g, SUBJECT s, LECTURE l

WHERE t.TchPK=l.TchFK AND l.GrpFK=g.GrpPK AND l.SbjFK=s.SbjPK

GROUP BY t.Name, g.Num, s.Name HAVING COUNT(DISTINCT l.RomFK) <=3;

7. Фраза ORDER BY

По каждому факультету, деканом которого является ИВАНОВ, вывести:

-его название,

-должность декана,

-количество преподавателей на факультете и

-суммарную зарплату (salary+commission) всех преподавателей факультета

при условии, что:

- разница между фондом финансирования факультета и суммарной зарплатой всех преподавателей факультета меньше 100000.

Результирующую таблицу упорядочить по третьему столбцу по возрастанию, по второму столбцу по убывнию и по четвертому столбцу по убыванию

SELECT f.Name, tdean.Post, COUNT(*), SUM(t.Salary+t.Commission) FROM TEACHER tdean, FACULTY f, DEPARTMENT d, TEACHER t

WHERE tdean.TchPK=f.DeanFK AND f.FacPK=d.FacFK AND d.DepPK=t.DepFK AND

UPPER(tdean.Name)='ИВАНОВ' GROUP BY f.Name, f.Fund, tdean.Post

HAVING f.Fund - SUM(t.Salary+t.Commission) < 100000 ORDER BY 3 ASC, 2 DESC, 4 DESC;