Лабораторная работа: Знакомства с MS SQL Server

Таблица 10. Студент

Таблица 11. Экзамен

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

SELECT

Ф.Название AS Факультет

, К.Название AS Кафедра

FROM

Факультет Ф, Кафедра К

WHERE

Ф.Аббревиатура = К.Факультет ORDER BY

Факультет, Кафедра

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

SELECT

Ф.Название AS Факультет

, К.Название AS Кафедра

FROM

Факультет Ф

INNER JOIN Кафедра К ON Ф.Аббревиатура = К.Факультет

ORDER BY

Факультет, Кафедра

Пример 3: Выбрать все факультеты и их кафедры, если существуют. Результат отсор- тировать по алфавиту:

SELECT

Ф.Название AS Факультет

, К.Название AS Кафедра

FROM

Факультет Ф

LEFT OUTER JOIN Кафедра К ON Ф.Аббревиатура = К.Факультет

ORDER BY

Факультет, Кафедра

Пример 4: Вывести из таблиц «Кафедра», «Специальность» и «Студент» данные о сту- дентах:

SELECT

С.Фамилия

, П.Направление

, К.Название AS Кафедра

FROM

Студент С

INNER JOIN Специальность П ON С.Номер = П.Номер INNER JOIN Кафедра К ON П.Шифр = К.Шифр

Пример 5: Вывести для каждого сотрудника фамилию, должность, зарплату и фамилию его непосредственного руководителя:

SELECT

С.Фамилия

, С.Должность

, С.Зарплата

, П.Фамилия AS Руководитель

FROM

Сотрудник С

INNER JOIN Сотрудник П ON С.Шеф = П.Таб_номер

Пример 6: Вывести список студентов, сдавших хотя бы один экзамен. По правилам соединения, студенты, не сдававшие экзамены, в выборке представлены не будут:

SELECT

С.Фамилия

FROM

Студент С

INNER JOIN Экзамен Э ON С.Рег_номер = Э.Рег_номер

GROUP BY

С.Фамилия

Пример 7: Вывести из таблиц «Студент» и «Экзамен» учетные номера и фамилии сту- дентов, а также количество сданных экзаменов и средний балл для каждого студента:

SELECT

С.Фамилия

, COUNT(Э.Оценка) AS [Количество экзаменов]

, AVG(Э.Оценка) AS [Средний балл]

FROM

Студент С

INNER JOIN Экзамен Э ON С.Рег_номер = Э.Рег_номер

GROUP BY

С.Фамилия

Пример 8: Вывести список заведующих кафедрами и их зарплаты, и стаж работы: SELECT

С.Фамилия

, С.Зарплата

, З.Стаж

FROM

Сотрудник С

INNER JOIN Зав_кафедрой З ON С.Таб_номер = З.Таб_номер

Пример 9: Вывести список кандидатов и докторов физико-математических наук: SELECT

С.Фамилия

, П.Степень

FROM

Сотрудник С

INNER JOIN Преподаватель П ON С.Таб_номер = П.Таб_номер

WHERE

П.Степень IN ('к.ф.-м.н.', 'д.ф.-м.н.')

Пример 10: Вывести название дисциплины, фамилию, должность и степень препода- вателя, дату и место проведения экзаменов в хронологическом порядке:

SELECT DISTINCT

Д.Название AS Дисциплина

, С.Фамилия, С.Должность

, П.Степень, Э.Дата, Э.Аудитория

FROM

Экзамен Э

INNER JOIN Дисциплина Д ON Э.Код = Д.Код

INNER JOIN Сотрудник С ON Э.Таб_номер = С.Таб_номер INNER JOIN Преподаватель П ON Э.Таб_номер = П.Таб_номер

ORDER BY

Э.Дата

Пример 11: Вывести фамилию преподавателей и количество их экзаменов: SELECT

С.Фамилия, COUNT(Э.Дата) AS [Количество экзаменов]

FROM

Экзамен Э

INNER JOIN Сотрудник С ON Э.Таб_номер = С.Таб_номер

GROUP BY

С.Фамилия

Пример 12: Вывести список студентов, не сдавших ни одного экзамена: SELECT

С.Фамилия

FROM

Студент С

LEFT OUTER JOIN Экзамен Э ON С.Рег_номер = Э.Рег_номер

WHERE

Э.Рег_номер IS NULL

Задание

Вывести из таблиц «Кафедра», «Специальность» и «Студент» данные о студентах, которые обучаются на данном факультете (например, «ит»).

Вывести из таблиц «Кафедра», «Специальность» и «Сотрудник» данные о выпус- кающих кафедрах (факультет, шифр, название, фамилию заведующего). Выпускающей счита- ется та кафедра, на которую есть ссылки в таблице «Специальность».

Вывести в запросе для каждого сотрудника номер и фамилию его непосредствен- ного руководителя. Для заведующих кафедрами поле руководителя оставить пустым.

Вывести список студентов, сдавших минимум два экзамена.

Вывести список инженеров с зарплатой, меньшей 20000 руб.

Вывести список студентов, сдавших экзамены в заданной аудитории.

Вывести из таблиц «Студент» и «Экзамен» учетные номера и фамилии студентов, а также количество сданных экзаменов и средний балл для каждого студента только для тех студентов, у которых средний балл не меньше заданного (например, 4).

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

Вывести список профессоров.

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

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

Вывести список студентов, не сдавших ни одного экзамена в указанной дате.

Лабораторная работа 7

Объединение результатов нескольких запросов

Цель работы

Изучить основы объединения результатов запросов.

Изучить UNION.

Изучить EXCEPT.

Изучить INTERSECT.

Теоретическая часть

Для объединения результатов двух или более запросов в одну таблицу используется команда UNION. Команда UNION объединяет вывод двух или более запросов в единый набор строк и столбцов и имеет вид:

Первый запрос UNION [ALL]

Второй запрос

…

Для объединения результатов нескольких запросов с помощью UNION, они должны соответствовать следующим требованиям:

? содержать одинаковое количество столбцов;

? типы данных столбцов должны совпадать во всех запросах;

? в промежуточных запросах нельзя использовать сортировку ORDER BY.

Чтобы отсортировать результат объединения, в конце запроса добавляется ORDER BY. Названия столбцов в запросах могут отличаться. Поэтому в команде ORDER BY указывается название столбцов с первого запроса.

Если объединяемые наборы содержат в строках идентичные значения, то при объеди- нении повторяющиеся строки удаляются.

Если необходимо при объединении сохранить повторяющиеся строки, то для этого ис- пользуется параметр ALL.

Все запросы выполняются независимо друг от друга, а уже их вывод объединяется. В объединяемых запросах можно использовать одну и ту же таблицу.

Чтобы найти разность двух выборок, то есть те строки, которые есть в первой выборке, но которых нет во второй, используется команда EXCEPT. Команда EXCEPT имеет следую- щий вид:

Первый запрос EXCEPT

Второй запрос.

Возвращаются все различные значения, указанные слева от оператора EXCEPT. Эти значения возвращаются, если они отсутствуют в результатах выполнения правого запроса.

Требования к использованию команды EXCEPT такие же, как к команде UNION.

Если сравниваются значения столбцов с целью определения различных строк, два значения NULL считаются равными.

Команда INTERSECT позволяет найти общие строки в результатах двух запросов, то есть данный оператор выполняет операцию пересечения множеств. Команда INTERSECT имеет следующий вид:

Первый запрос INTERSECT

Второй запрос.

Требования к использованию команды INTERSECT такие же, как к командам UNION и EXCEPT.

Практическая часть

Дана таблица Страны:

Пример 1: Вывести объединенный результат выполнения запросов, которые выбирают страны с площадью больше 1 млн. кв. км и с населением больше 100 млн. чел.:

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Площадь > 1000000 UNION

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Население > 100000000

Пример 2: Вывести объединенный результат выполнения запросов, которые выбирают страны с площадью больше 1 млн. кв. км и с населением больше 100 млн. чел., при этом остав- ляет дубликаты:

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Площадь > 1000000 UNION ALL

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Население > 100000000

Пример 3: Вывести объединенный результат выполнения запросов, которые выбирают европейские страны с плотностью более 300 чел. на кв. км, азиатские страны с плотностью более 200 чел. на кв. км. и африканские страны с плотностью более 150 чел. на кв. км. Резуль- тат отсортировать по континентам:

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Континент = 'Европа' AND

CAST(Население AS FLOAT) / Площадь > 400 UNION

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Континент = 'Азия' AND

CAST(Население AS FLOAT) / Площадь > 300

UNION SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Континент = 'Африка' AND

CAST(Население AS FLOAT) / Площадь > 200 ORDER BY

Континент

Пример 4: Вывести список стран с площадью больше 1 млн. кв. км, исключить страны с населением больше 10 млн. чел.:

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Площадь > 1000000 EXCEPT

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Население > 10000000

Пример 5: Вывести список стран с площадью больше 1 млн. кв. км и с населением больше 100 млн. чел.:

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Площадь > 1000000 INTERSECT

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Население > 100000000

Задание

Вывести объединенный результат выполнения запросов, которые выбирают страны с площадью меньше 500 кв. км и с площадью больше 5 млн. кв. км: интерфейс база данный фильтрация