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

Список часто используемых функций проверки значений:

Особое место среди встроенных скалярных функций языка SQL занимают функции вы- вода, которые являются разновидностью CASE-выражений. Функция CASE проверяет значе- ние некоторого выражения, и в зависимости от результата проверки может возвращать тот или иной результат.

Выражение CASE имеет два формата:

простое выражение CASE для определения результата сравнивает выражение с набо- ром простых выражений;

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

Оба формата поддерживают дополнительный аргумент ELSE.

Функция IIF(условие, выражение_если_истина, выражение_если_ложь) - возвращает одно из двух значений в зависимости от того, принимает логическое выражение значение true или false.

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

Дана таблица Академики:

Пример 1: Вывести ФИО академиков и длину ФИО: SELECT

ФИО

,LEN(ФИО) AS Количество_символов

FROM

Академики

Пример 2: Вывести список академиков, убрать лишние пробелы в ФИО: SELECT

TRIM(ФИО) AS ФИО

,Дата_рождения

,Специализация

,Год_присвоения_звания

FROM

Академики

Пример 3: Найти позиции буквы «о» в ФИО каждого академика. Вывести ФИО и по- зицию:

SELECT

ФИО

,CHARINDEX('о',ФИО) AS Позиция_о

FROM

Академики

Пример 4: Вывести ФИО и первые три буквы специализации каждого академика: SELECT

ФИО

,LEFT(Специализация, 3) AS Спец_3

FROM

Академики

Пример 5: Вывести ФИО и от второй до пятой буквы специализации каждого академика:

SELECT

ФИО

,SUBSTRING(Специализация, 2, 4) AS Спец_2_5

FROM

Академики

Пример 6: Вывести список академиков, заменить специализацию «лингвист» на «язы- ковед»:

SELECT

ФИО

,Дата_рождения

,REPLACE(Специализация, 'лингвист', 'языковед') AS Спец

,Год_присвоения_звания

FROM

Академики

Пример 7: Вывести список академиков, специализацию на верхнем регистре: SELECT

ФИО

,Дата_рождения

,UPPER(Специализация) AS Спец

,Год_присвоения_звания

FROM

Академики

Пример 8: Вывести ФИО академиков в правильном и обратном виде: SELECT

ФИО

,REVERSE(ФИО) AS ФИО_Обр

FROM

Академики Название

Пример 9: Вывести каждую специализацию 4 раза в одной строке. Убрать дубликаты: SELECT DISTINCT

REPLICATE(Специализация, 4) AS Спец_4

FROM

Академики

Пример 10: Вывести абсолютное значение тригонометрических функций на точке р: SELECT

ABS(COS(PI())) AS Косинус_Пи

,ABS(SIN(PI())) AS Синус_Пи

,ABS(TAN(PI())) AS Тангенс_Пи

,ABS(COT(PI())) AS КоТангенс_Пи

Пример 11: Вывести число 132.456, округленное с точностью от 3 до -3: SELECT

ROUND(123.456, 3) AS Окр3

,ROUND(123.456, 2) AS Окр2

,ROUND(123.456, 1) AS Окр1

,ROUND(123.456, 0) AS Окр0

,ROUND(123.456, -1) AS Окр_1

,ROUND(123.456, -2) AS Окр_2

,ROUND(123.456, -3) AS Окр_3

Пример 12: Вывести наименьшее целое число, которое больше или равно 123.456, и наибольшее целое число, которое меньше или равно 123.456:

SELECT

CEILING(123.456) AS Больше

,FLOOR(123.456) AS Меньше

Пример 13: Вывести квадратный корень, квадрат и куб числа 25: SELECT

SQRT(25) AS Корень

,SQUARE(25) AS Квадрат

,POWER(25, 3) AS Куб

Пример 14: Вывести текущую дату и время:

SELECT

GETDATE() AS Сейчас

Пример 15: Вывести день, месяц, год, час, минуту, секунду, номер квартала, номер не- дели, день года, день недели для текущей даты и времени:

SELECT

DAY(GETDATE()) AS День

,MONTH(GETDATE()) AS Месяц

,YEAR(GETDATE()) AS Год

,DATEPART(HOUR, GETDATE()) AS Час

,DATEPART(MINUTE, GETDATE()) AS Минута

,DATEPART(SECOND, GETDATE()) AS Секунда

,DATEPART(QUARTER, GETDATE()) AS Квартал

,DATEPART(WEEK, GETDATE()) AS Неделя

,DATEPART(DAYOFYEAR, GETDATE()) AS День_года

,DATEPART(WEEKDAY, GETDATE()) AS День_недели

Пример 16: Вывести дату 100 дней назад от текущей:

SELECT

DATEADD(DAY, -100, GETDATE()) AS День_100_Назад

Пример 17: Академик Игорь Евгеньевич Тамм родился 8 июля 1895 года. И.Е. Тамм скончался 12 апреля 1971 года. Вывести количество прожитых дней:

SELECT

DATEDIFF(DAY, '18950708', '19710412') AS Количество_прожитых_дней

Пример 18: Вывести ФИО и время года рождения каждого академика: SELECT

ФИО

, CASE MONTH(Дата_рождения) WHEN 3 THEN 'Весна' WHEN 4 THEN 'Весна' WHEN 5 THEN 'Весна' WHEN 6 THEN 'Лето' WHEN 7 THEN 'Лето' WHEN 8 THEN 'Лето' WHEN 9 THEN 'Осень' WHEN 10 THEN 'Осень' WHEN 11 THEN 'Осень' ELSE 'Зима'

END AS Времени_года FROM Академики

Пример 19: Вывести ФИО, дату рождения и знак зодиака каждого академика: SELECT

ФИО

, Дата_рождения

, CASE

WHEN (MONTH(Дата_рождения)=3 AND DAY(Дата_рождения) >= 21) OR (MONTH(Дата_рождения)=4 AND DAY(Дата_рождения) <= 20) THEN 'Овен'

WHEN (MONTH(Дата_рождения)=4 AND DAY(Дата_рождения) >= 21) OR (MONTH(Дата_рождения)=5 AND DAY(Дата_рождения) <= 21) THEN 'Телец'

WHEN (MONTH(Дата_рождения)=5 AND DAY(Дата_рождения) >= 22) OR (MONTH(Дата_рождения)=6 AND DAY(Дата_рождения) <= 21) THEN 'Близнецы'

WHEN (MONTH(Дата_рождения)=6 AND DAY(Дата_рождения) >= 22) OR (MONTH(Дата_рождения)=7 AND DAY(Дата_рождения) <= 22) THEN 'Рак'

WHEN (MONTH(Дата_рождения)=7 AND DAY(Дата_рождения) >= 23) OR (MONTH(Дата_рождения)=8 AND DAY(Дата_рождения) <= 21) THEN 'Лев'

WHEN (MONTH(Дата_рождения)=8 AND DAY(Дата_рождения) >= 22) OR (MONTH(Дата_рождения)=9 AND DAY(Дата_рождения) <= 23) THEN 'Дева'

WHEN (MONTH(Дата_рождения)=9 AND DAY(Дата_рождения) >= 24) OR (MONTH(Дата_рождения)=10 AND DAY(Дата_рождения) <= 23) THEN 'Весы'

WHEN (MONTH(Дата_рождения)=10 AND DAY(Дата_рождения) >= 24) OR (MONTH(Дата_рождения)=11 AND DAY(Дата_рождения) <= 22) THEN 'Скорпион'

WHEN (MONTH(Дата_рождения)=11 AND DAY(Дата_рождения) >= 23) OR (MONTH(Дата_рождения)=12 AND DAY(Дата_рождения) <= 22) THEN 'Стрелец'

WHEN (MONTH(Дата_рождения)=12 AND DAY(Дата_рождения) >= 23) OR (MONTH(Дата_рождения)=1 AND DAY(Дата_рождения) <= 20) THEN 'Козерог'

WHEN (MONTH(Дата_рождения)=1 AND DAY(Дата_рождения) >= 21) OR (MONTH(Дата_рождения)=2 AND DAY(Дата_рождения) <= 19) THEN 'Водолей'

WHEN (MONTH(Дата_рождения)=2 AND DAY(Дата_рождения) >= 20) OR (MONTH(Дата_рождения)=3 AND DAY(Дата_рождения) <= 20) THEN 'Рыбы'

END AS Знак_зодиака FROM Академики

Пример 20: Вывести список академиков. Для каждого академика, в зависимости от воз- раста, при присвоении звания вывести «молодой» или «старый» в дополнительном столбце:

SELECT

ФИО

,Дата_рождения

,Специализация

,Год_присвоения_звания

,IIF(Год_присвоения_звания - Year(Дата_рождения) <= 45, 'Молодой','Старый') AS Возраст_при_присвоении

FROM Академики

Задание

Вывести список академиков, отсортированный по количеству символов в ФИО.

Вывести список академиков, убрать лишние пробелы в ФИО.

Найти позиции «ов» в ФИО каждого академика. Вывести ФИО и номер позиции.

Вывести ФИО и последние две буквы специализации для каждого академика.

Вывести список академиков, ФИО в формате Фамилия и Инициалы.

Вывести список специализаций в правильном и обратном виде. Убрать дубликаты.

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

Вывести абсолютное значение функций ??????2 (??) ? ?????? (3??) с точностью два знака 2 2 после десятичной запятой.

Вывести количество дней до конца семестра.

Вывести количество месяцев от вашего рождения.

Вывести ФИО и високосность года рождения каждого академика.

Вывести список специализаций без повторений. Для каждой специализации выве- сти «длинный» или «короткий», в зависимости от количества символов.

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

Агрегатные функции

Цель работы

Изучить основы агрегации данных.

Изучить функцию MAX.

Изучить функцию MIN.

Изучить функцию SUM.

Изучить функцию AVG.

Изучить функцию COUNT.

Изучить группировки данных.

Изучить применение фильтрации в группировке данных.

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

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

SUM - вычисляет итог;

MAX - возвращает наибольшее значение;

MIN - возвращает наименьшее значение;

AVG - вычисляет среднее значение;

COUNT - вычисляет количество значений в столбце.

Аргументами функций выступают поля таблицы или результаты выражений над ними.

Вложенность не допускается.

Из агрегатных функций можно составлять любые выражения.

Для функций SUM и AVG столбец должен содержать числовые значения.

Для функций COUNT() можно указать аргумент * для подсчета всех строк без исключения.

По умолчанию вышеперечисленные пять функций учитывают все строки выборки для

вычисления результата. Но выборка может содержать повторяющиеся значения. Если необхо- димо выполнить вычисления только над уникальными значениями, исключив из набора зна- чений повторяющиеся данные, то для этого применяется оператор DISTINCT (кроме COUNT (*)). По умолчанию вместо DISTINCT применяется оператор ALL, который выбирает все строки. Так как этот оператор неявно подразумевается при отсутствии DISTINCT, то его можно не указывать.

Агрегатные функции можно применить не только на всю таблицу, но также на группу значений. Для этого применяется команда GROUP BY, которая пишется после WHERE. После команды GROUP BY перечисляется название столбцов, по которым следует группировать данные. Предложение GROUP BY указывает, что результаты запроса следует разделить на группы, применить агрегатную функцию по отдельности к каждой группе и получить для каж- дой группы одну строку результатов.

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

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

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

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

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