Агрегатные функции COUNT, SUM, AVG, MIN, MAX, EVERY,ANY, SOME. Использование оператора EXISTS

Агрегатныефункции COUNT, SUM, AVG, MIN, MAX, EVERY,ANY, SOME

1. Сколько преподавателей имеется в базе данных?

SELECT  COUNT(*) AS «Кол-во преподавателей» FROM ПРЕПОДАВАТЕЛИ

2. Сколько ассистентов имеется в базе данных?

SELECT  COUNT(*) AS «Кол-во ассистентов» FROM ПРЕПОДАВАТЕЛИ

WHERE Должность = «ассистент»

3. Сколько ассистентов не имеют телефонов?

SELECT  COUNT(*) AS «Кол-во ассистентов без телефона» FROM ПРЕПОДАВАТЕЛИ

WHERE Должность = «ассистент» AND Tel = NULL

4. Сколько кафедр на гидрологическом факультете?

SELECT COUNT(*) AS «Код_кафедры»

FROM ФАКУЛЬТЕТ, КАФЕДРА

WHERE ФАКУЛЬТЕТ.Код_фак-та = КАФЕДРА.Код_фак-та

AND Название = «Гидрологический»

5. Сколько различных должностей занимают преподаватели кафедры «Информатика»?

SELECT COUNT(DISTINCT Должность)

FROM ФАКУЛЬТЕТ, ПРЕПОДАВАТЕЛИ

WHERE КАФЕДРА.Код_кафедры = ПРЕПОДАВАТЕЛЬ.Код_кафедры

AND Название = «Информатика»

6. Суммарный фонд финансирования всех факультетов?

SELECT SUM(Фонд_фин) FROM ФАКУЛЬТЕТ

7. Каково среднее значение ставки в вузе?

SELECT AVG(DISTINCT Оклад) FROM ПРЕПОДАВАТЕЛИ

8. Какова максимальная зарплата преподавателя?

SELECT MAX(Оклад+Премия) FROM ПРЕПОДАВАТЕЛИ

9. Вывести первое по алфавиту название кафедры с фондом финансирования более 150000 руб.

SELECT MIN(Название) FROM КАФЕДРА  WHERE

Фонд_фин > 150000

10. Когда в последний раз принимали на работу преподавателя на кафедру МИТ?

SELECT MAX(Дата_приема) FROM КАФЕДРА К, ПРЕПОДАВАТЕЛЬ T WHERE K.Код_кафедры = T.Код_кафедры AND K.Название  = «МИТ»

Использование оператора EXISTS

Унарный оператор EXISTS проверяет, является ли пустым множество, специфицированное в его аргументе. EXISTS принимает значение TRUE, если множество непустое и FALSE в противном случае.

Пример 1.

Вывести номера и кол-во мест в аудиториях первого корпуса с вместимостью 30 и более мест, если в этом корпусе имеется хотя бы одна аудитория с вместимостью более 50 мест.

SELECT Номер_ауд, Кол-во_мест FROM АУДИТОРИЯ

WHERE Номер_корпуса = 1 AND Кол-во_мест >= 30 AND

EXISTS (SELECT * FROM АУДИТОРИЯ

WHERE Номер_корпуса = 1 AND Кол-во_мест > 50)

Пример 2.

Вывести названия факультетов, на котором работает хотя бы один преподаватель.

SELECT ФИО FROM ФАКУЛЬТЕТ WHERE EXISTS (SELECT * FROM ПРЕПОДАВАТЕЛЬ WHERE ПРЕПОДАВАТЕЛЬ.Код_кафедры IN

SELECT КАФЕДРА.Код_кафедры  FROM КАФЕДРА WHERE КАФЕДРА.Код_факультета = ФАКУЛЬТЕТ. Код_факультета))

Пример 3.

Вывести названия факультетов, на которых работает хотя бы один профессор и которые имеют более двух кафедр, при этом фонд финансирования более 100000 руб.

SELECT ФИО FROM ФАКУЛЬТЕТ WHERE Фонд_фин > 100000 AND

3 <= (SELECT СOUNT(*) FROM КАФЕДРА КАФЕДРА.Код_факультета = ФАКУЛЬТЕТ. Код_факультета) AND

EXISTS (SELECT * FROM ПРЕПОДАВАТЕЛЬ

WHERE ПРЕПОДАВАТЕЛЬ.Должность = ‘Профессор’ AND

ПРЕПОДАВАТЕЛЬ.Код_кафедры IN

(SELECT КАФЕДРА.Код_кафедры  FROM WHERE КАФЕДРА.Код_факультета = ФАКУЛЬТЕТ. Код_факультета))

Функции EVERY, ANY, SOME.

Все ли профессора являются докторами наук:

SELECT EVERY(PhD) FROM ПРЕПОДАВАТЕЛЬ

WHERE ПРЕПОДАВАТЕЛЬ.Должность = ‘Профессор’

Существуют ли доценты – доктора наук:

SELECT SOME(PhD) FROM ПРЕПОДАВАТЕЛЬ

WHERE ПРЕПОДАВАТЕЛЬ.Должность = ‘Доцент’

Для поиска фамилий преподавателей:

SELECT ФИО FROM ПРЕПОДАВАТЕЛЬ

WHERE ПРЕПОДАВАТЕЛЬ.Должность = ‘Доцент’ AND PhD

Пример 4.

Вывести названия факультетов, фонды финансирования которых меньше фонда финансирования хотя бы одной из кафедр гидрологического факультета.

SELECT Название FROM ФАКУЛЬТЕТ

WHERE Фонд_фин < SOME (SELECT ФАКУЛЬТЕТ.Фонд_фин FROM КАФЕДРА, ФАКУЛЬТЕТ = ФАКУЛЬТЕТ. Код_факультета AND

ФАКУЛЬТЕТ. Название = «Гидрологический»)

Пример 5.

Вывести фамилии доцентов, зарплата которых больше, чем ставка хотя бы одного из профессоров.

SELECT ФИО FROM ПРЕПОДАВАТЕЛЬ

WHERE ПРЕПОДАВАТЕЛЬ.Должность = ‘Доцент’ AND

(Оклад + Премия) > SOME (SELECT Оклад FROM ПРЕПОДАВАТЕЛЬ

WHERE ПРЕПОДАВАТЕЛЬ.Должность = ‘Профессор’