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

Пример 1: Вывести максимальную площадь стран: SELECT

MAX(Площадь) AS Макс_площадь

FROM

Страны

Пример 2: Вывести наименьшее население стран в Африке: SELECT

MIN(Население) AS Мин_население

FROM

Страны

WHERE

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

Пример 3: Вывести суммарное население стран Северной и Южной Америки: SELECT

SUM(Население) AS Суммарное_население

FROM

Страны

WHERE

Континент = 'Северная Америка' OR Континент = 'Южная Америка'

Пример 4: Вывести среднее население стран, кроме европейских. Результат округлить до двух знаков:

SELECT

ROUND(AVG(CAST(Население AS FLOAT)), 2) AS Среднее_население

FROM

Страны

WHERE

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

Пример 5: Вывести количество стран, название которых начинается с буквы «С»: SELECT

COUNT(*) AS Количество

FROM

Страны

WHERE

LEFT(Название, 1) = 'С'

Пример 6: Вывести количество континентов, где есть страны: SELECT

COUNT(DISTINCT Континент) AS Количество_Континентов

FROM

Страны

Пример 7: Вывести разницу населения между странами с наибольшим и наименьшим количеством граждан:

SELECT

MAX(Население) - MIN(Население) AS Разница

FROM

Страны

Пример 8: Вывести количество стран на каждом континенте. Результат отсортировать по количеству стран по убыванию:

SELECT

Континент

, COUNT(Название) AS Количество_Стран FROM

Страны GROUP BY

Континент ORDER BY

Количество_Стран DESC

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

SELECT

LEFT(Название, 1) AS Первая_буква

, COUNT(Название) AS Количество_Стран

FROM

Страны

GROUP BY

LEFT(Название, 1) ORDER BY

Первая_буква

Пример 10: Вывести список континентов, где плотность населения больше, чем 100 чел. на кв. км:

SELECT

Континент

, AVG(CAST(Население AS FLOAT) / Площадь) AS Сред_Плотность

FROM

Страны

GROUP BY

Континент HAVING

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

Пример 11: Ожидается, что через 25 лет население Европы и Азии вырастет на 20%, Северной Америки и Африки на 50%, а остальных частей мира - на 70%. Вывести список континентов с прогнозируемым населением:

SELECT

Континент

, CASE

WHEN Континент IN ('Европа', 'Азия') THEN FLOOR(SUM(Население) * 1.2)

ление) * 1.5)

FROM

WHEN Континент IN ('Северная Америка', 'Африка') THEN FLOOR(SUM(Насе-

ELSE FLOOR(SUM(Население) * 1.7) END AS Суммарное_Население

Страны

GROUP BY

Континент

Пример 12: Вывести список континентов, где разница по населению между наиболь- шими и наименьшими странами не более в 1000 раз:

SELECT

Континент

FROM

Страны

GROUP BY

Континент

HAVING MAX(Население) <= 1000 * MIN(Население)

Пример 13: Вывести количество стран, у которых нет столицы (не введена в базу): SELECT

COUNT(*) AS Количество

FROM

Страны

WHERE

Столица IS NULL

Пример 14: Вывести количество символов в самых длинных и коротких названиях стран и столиц:

SELECT

MAX(LEN(Название)) AS Дл_Название

, MAX(LEN(Столица)) AS Дл_Столица

, MIN(LEN(Название)) AS Кр_Название

, MIN(LEN(Столица)) AS Кр_Столица

FROM

Страны

Пример 15: Вывести список континентов, у которых средняя плотность среди стран с площадью более 1 млн. кв. км больше, чем 30 чел. на кв. км. Результат отсортировать по плот- ности по убыванию:

SELECT

Континент

,AVG(CAST(Население AS FLOAT) / Площадь) AS Плотность

FROM

Страны

WHERE

Площадь > 1000000 GROUP BY

Континент HAVING

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

Плотность DESC

Задание

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

Вывести наибольшую по населению страну в Северной и Южной Америке.

Вывести среднее население стран. Результат округлить до одного знака.

Вывести количество стран, у которых название заканчивается на «ан», кроме стран, у которых название заканчивается на «стан».

Вывести количество континентов, где есть страны, название которых начинается с буквы «Р».

Сколько раз страна с наибольшей площадью больше, чем страна с наименьшей пло- щадью?

Вывести количество стран с населением больше, чем 100 млн. чел. на каждом кон- тиненте. Результат отсортировать по количеству стран по возрастанию.

Вывести количество стран по количеству букв в названии. Результат отсортировать по убыванию.

Ожидается, что через 20 лет население мира вырастет на 10%. Вывести список континентов с прогнозируемым населением:

Вывести список континентов, где разница по площади между наибольшими и наименьшими странами не более в 10000 раз:

Вывести среднюю длину названий Африканских стран.

Вывести список континентов, у которых средняя плотность среди стран с населе- нием более 1 млн. чел. больше, чем 30 чел. на кв. км.

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

Соединения таблиц

Цель работы

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

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

Изучить внутреннее соединение таблиц.

Изучить внешнее соединение таблиц.

Изучить соединения таблиц со своей копией.

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

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

Когда выбор данных осуществляется из нескольких таблиц, в конструкции SELECT для каждого поля указывается таблица в виде <таблица>.<поле>. Если название поля уникальное, то можно его указать без таблицы, иначе это обязательно, чтобы избежать коллизий. Чтобы не повторить длинные названия таблиц, можно использовать псевдоним для таблиц. Псевдоним указывается в конструкции как FROM.

При неявном соединении таблиц формат конструкций FROM и WHERE имеет следу- ющий вид:

FROM <таблица1> [псевдоним1], <таблица2> [псевдоним2]… [WHERE <условие_соединения> [AND <условие_поиска>]… ]

Таким образом, если более одной таблицы присутствует в конструкции FROM, то их разделяют запятой.

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

Для явного соединения таблиц используется команда JOIN. У явного соединения есть следующие разновидности:

Внутреннее соединение - осуществляется с помощью команды INNER JOIN. Из двух таблиц берутся только связанные строки. INNER JOIN имеет следующий формат записи:

<таблица1> INNER JOIN <таблица2> ON <таблица1>.<связующее_поле> = <таб- лица2>.<связующее_поле>.

При внутреннем соединении слово INNER можно пропустить.

Внешнее соединение - осуществляется с помощью команды OUTER JOIN. OUTER JOIN имеет следующий формат записи:

<таблица1> LEFT | RIGHT | FULL OUTER JOIN <таблица2> ON <таблица1>.<связую- щее_поле> = <таблица2>.<связующее_поле>.

У внешнего соединения есть три разновидности:

Левое внешнее соединение LEFT OUTER JOIN - из таблицы, название которой явля- ется левым операндом команды JOIN, выбираются все строки, из второй таблицы - только те записи, которые имеют связь с первой таблицей.

Правое внешнее соединение RIGHT OUTER JOIN - из таблицы, название которой является правым операндом команды JOIN, выбираются все строки, из первой таблицы только те записи, которые имеют связь со второй таблицей.

Полное внешнее соединение FULL OUTER JOIN - из обоих таблиц выбираются все строки.

При использовании внешних соединений, слово OUTER можно пропустить.

Перекрестное соединение CROSS JOIN - декартово произведение двух таблиц.

CROSS JOIN имеет следующий формат записи:

<таблица1> CROSS JOIN <таблица2>.

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

Используя соединение, можно связать таблицу с собой. При таком соединении псевдо- ним обязателен.

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

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

Даны следующие таблицы:

Таблица 1. Факультет

Таблица 2. Кафедра

Таблица 3. Сотрудник

Таблица 4. Специальность

Таблица 5. Дисциплина

Таблица 6. Заявка

Таблица 7. Зав_кафедрой

Таблица 8. Инженер

Таблица 9. Преподаватель