Связанные подзапросы в HAVING

      

Предложение GROUP BY позволяет группировать выводимые SELECT- запросом записи по значению некоторого поля. Использование предложения HAVING позволяет при выводе осуществлять фильтрацию таких групп. Предикат предложения HAVING оценивается не для каждой строки результата, а для каждой группы выходных записей, сформированной предложением GROUP BY внешнего запроса.

       Пусть, например, необходимо по данным из таблицы ОЦЕНКИ определить сумму полученных студентами оценок (значений поля ОЦЕНКА), сгруппировав значения оценок по датам экзаменов и исключив те дни, когда число студентов, сдававших в течении дня экзамены, было меньше 10.

 

 

SELECT ДАТА, SUM (ОЦЕНКА)

       FROM ОЦЕНКИ A

       GROUP BY ДАТА

       HAVING 10 <

       (SELECT COUNT (ОЦЕНКА)

        FROM ОЦЕНКИ B

       WHERE A. ДАТА = B. ДАТА);

 

       Подзапрос вычисляет количество строк с одной и той же датой, совпадающей с датой, для которой сформирована очередная группа основного запроса.

 

Практическая работа 11

 

Принципы построения и взаимодействия таблиц

Цель работы: Приобрести практический навык по созданию таблиц с помощью SQL.

 

Порядок выполнения работы

 

 

1. Создать таблицы согласно схеме данных с использованием SQL.

2. Заполнить таблицы.

 

Контрольные вопросы:

1. Какие поля приведенных таблиц являются первичными ключами?

2. Типы данных SQL?

3. Дайте определение интерактивного и встроенного SQL?

4. Используемые термины и обозначения?

5. Какая команда создает таблицу?

 

Практическая работа 12

Ввод, изменение и удаление данных с помощью SQL

Цель работы: Приобрести практический навык по использованию команд манипулирование данными

 

Порядок выполнения работы

 

1. Напишите команду, которая вводит в таблицу SUBJECT (ПРЕДМЕТЫ) строку для нового предмета обучения со следующими значениями полей:

SEMESTER = 4; SUBJ_NAME = 'Алгебра'; HOUR = 72; SUBJECT_ID =201.

 

2. Введите запись для нового студента, которого зовут Орлов Николай, обучающегося на первом курсе ВГУ, живущего в Воронеже,
сведения о дате рождения и размере стипендии неизвестны.

 

3. Напишите команду, удаляющую из таблицы EXAM_MARKS (ОЦЕНКИ) записи обо всех оценках студента, идентификатор которого равен 100.

 

4. Напишите команду, которая увеличивает на 5 значение рейтинга
всех имеющихся в базе данных университетов, расположенных
в Санкт- Петербурге.

 

5. Измените в таблице значение города, в котором проживает студент
Иванов, на «Воронеж».

 

 

Контрольные вопросы:

1. Какие команды используются в SQL для манипулирования данными?

2. Назначение предложения SET?

 

Практическая работа 13

Выборка данных

Цель работы: Приобрести практический навык по созданию простейших запросов.

 

Порядок выполнения работы

 

 

1. Напишите запрос, который выводит наименование предмета и количество часов для каждого предмета в 4-м семестре.

2. Напишите запрос, который выводит список фамилий студентов, обучающихся на третьем и последующих курсах.

3. Напишите запрос к таблице STUDENT(СТУДЕНТЫ) для вывода списка фамилий, имен, и номера курса всех студентов со стипендией, большей или равной 100, и живущих в Воронеже.

4. Напишите запрос на вывод находящихся в таблице EXAM_MARKS (ОЦЕНКИ) номеров предметов обучения, экзамены по которым сдавались между 10 и 20 января 1999 года.

5. Напишите запрос, выбирающий сведения о студентах, у которых имена начинаются на буквы «И» или «С».

6. Напишите запрос на вывод из таблицы EXAM_MARKS (ОЦЕНКИ)

записей, имеющих в поле оценка значения оценок.

Контрольные вопросы:

1. С помощью какой команды формируются запросы в SQL?

2. Какой смысл несет ключевое поле DISTINCT?

3. Какие операторы могут использоваться в предложении WHERE?

 

Практическая работа 14

Подведение итогов

Цель работы: Приобрести практический навык применения агрегирующих функций. Упорядочение выходных полей.

 

Порядок выполнения работы

 

 

1. Напишите запрос, который позволяет подсчитать в таблице EXAM_MARKS (ОЦЕНКИ) количество различных предметов обучения.

1. Напишите запрос, выполняющий вывод фамилии первого в алфавитном порядке(по фамилии) студента, фамилия которого начинается на букву»И».

1. Напишите запрос, который выполняет вывод данных для каждого конкретного дня сдачи экзамена о количестве студентов, сдававших экзамен в этот день.

1. Напишите запрос, который по таблице EXAM_MARKS (ОЦЕНКИ) позволяет найти максимальные оценки каждого студента и который выводит их вместе с идентификатором студента.

1. Напишите запрос, который выполняет вывод суммы баллов всех студентов для каждой даты сдачи экзаменов и представляет результаты в порядке убывания сумм.

 

Контрольные вопросы:

1. Какие агрегатные функции вы знаете?

2. Какая команда используется для упорядочения полей?

3. Смысл предложений GROUP BY и HAVING?

 

Практическая работа 15

Составление запросов по нескольким таблицам

 

Цель работы: Приобрести практический навык по созданию вложенных подзапросов. Формирование связанных подзапросов

 

Порядок выполнения работы

 

 

1. Напишите запрос с подзапросом для получения данных обо всех оценках студента с фамилией «Иванов».

1. Напишите запрос, выбирающий данные об именах всех студентов, имеющих по предмету с идентификатором 101 балл выше общего среднего балла.

1.  Напишите запрос, выполняющий вывод количества предметов, по которым экзаменовался каждый студент, сдававший более 20 предметов.

1.  Напишите запрос с EXISTS, позволяющий вывести данные обо всех студентах, обучающихся в вузах, которые имеют рейтинг выше 300.

1. Напишите предыдущий запрос, используя соединения.

 

Контрольные вопросы:

1. Как работает запрос SQL со связанным подзапросом?

2. Связанные подзапросы в HAVING?

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

 

STUDENT(СТУДЕНТЫ)

STUDENT_ID (Код_ студента)	SURNAME (Фамилия)	NAME  (Имя)	STIPEND (Стипендия)	KURS  (Курс)	CITY  (Город)	BIRTHDAY  (Дата_ рождения)	UNIV_ID  (Код_ уч. Зав)
1	Иванов	Иван	150	1	Орел	3.12.1982	10
3	Петров	Петр	200	3	Курск	1.12.1980	10
6	Сидоров	Вадим	150	4	Москва	7.06.1979	22
10	Кузнецов	Борис	0	2	Брянск	8.12.1981	10
12	Зайцева	Ольга	250	2	Липецк	1.05.1981	10
265	Павлов	Андрей	0	3	Воронеж	5.11.1979	10
32	Котов	Павел	150	5	Белгород	Null	14
654	Лукин	Артем	200	3	Воронеж	1.12.1981	10
276	Петров	Антон	200	4	Null	5.08.1981	22

 

LECTURER(ПРЕПОДАВАТЕЛИ)

LECTURER_ID  (Код_ преподавателя)	SURNAME  (Фамилия)	NAME  (Имя)	CITY  (Город)	UNIV_ID (Код_ уч. зав)
24	Колесников	Борис	Воронеж	10
46	Никонов	Иван	Воронеж	10
74	Лагутин	Павел	Москва	22
108	Струков	Николай	Москва	22
276	Николаев	Виктор	Воронеж	10
328	Сорокин	Андрей	Орел	10

 

SUBJECT (ПРЕДМЕТЫ)

SUBJECT_ID (Код_предмета)	SUBJ_NAME (Наименование)	HOUR (часы)	SEMESTER (Семестр)
10	Информатика	56	1
22	Физика	34	1
43	Математика	56	2
56	История	34	4
94	Английский	56	3
73	Физкультура	34	5

 

UNIVERSITY (УЧЕБНЫЕ_ЗАВЕДЕНИЯ)

UNIV_ID (Код_учебного заведения)	UNIV_NAME (Наименование)	RATING (Рейтинг)	CITY (Город)
22	МГУ	606	Москва
10	ВГУ	296	Воронеж
11	НГУ	345	Новосибирск
32	РГУ	416	Ростов
14	БГУ	326	Белгород
15	ТГУ	368	Томск
18	ВГМА	327	Воронеж

EXAM_MARKS (ОЦЕНКИ)

EXAM_ID (Код_экзамена)	STUDENT_ID (Код_студента)	SUBJ_ID (Код_предмета)	MARK (Оценка)	EXAM_DATE (Дата)
145	12	10	5	12.01.2000
34	32	10	4	23.01.2000
75	55	10	5	05.01.2000
238	12	22	3	17.06.2000
639	55	22	NULL	22.06.2000
43	6	22	4	18.01.2000