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

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

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

Напишите запрос, который выводит средний балл учеников по школам в столбцы.

Напишите запрос, который выводит средний балл учеников по школам в столбцы и по предметам в строки.

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

Лабораторная работа № 19

Динамический SQL, XML и JSON

Цель работы

Изучить использование динамического SQL.

Изучить вывод данных в формате XML.

Изучить вывод данных в формате JSON.

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

В интерактивных приложениях часто состав SQL-запросов заранее не известен и может изменяться в зависимости от условий.

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

Для выполнения динамического SQL используется команда EXEC | EXECUTE. Упро- щенный синтаксис имеет следующий вид:

EXEC | EXECUTE (<строка динамического SQL>)

Результат выполнения запроса можно получать в формате XML. Для этого в запросе необходимо указать предложение FOR XML.

В предложении FOR XML можно указать один из следующих режимов:

RAW - в этом режиме создается одиночный элемент <row> для каждой строки набора строк, возвращенного инструкцией SELECT. XML-иерархию можно создать с помощью напи- сания вложенных запросов FOR XML.

AUTO - в этом режиме вложенность XML создается эвристически, в зависимости от метода определения инструкции SELECT.

EXPLICIT - этот режим предоставляет больше возможностей по управлению формой XML-структуры. В XML-структуре могут быть использованы смешанные атрибуты и эле- менты. Однако написание запросов в режиме EXPLICIT может быть очень обременительным. PATH - этот режим совместно с вложенным запросом FOR XML обеспечивает гиб-

кость режима EXPLICIT более простым образом.

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

Результат выполнения запроса можно получать в формате JSON. Для этого в запросе необходимо указать предложение FOR JSON. Предложение FOR JSON упрощает клиентские приложения, делегируя форматирование выходных данных JSON из приложения в SQL Server.

В предложении FOR XML можно указать один из следующих режимов:

PATH - этот режим используется для того чтобы сохранить полный контроль над форматом выходных данных JSON. Можно создавать объекты-оболочки и вкладывать сложные свойства друг в друга.

AUTO - этот режим используется для того чтобы отформатировать выходные данные автоматически на основе структуры инструкции SELECT.

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

Пример 1: Создайте и запустите динамический SQL-запрос, выбирающий все данные из заданной таблицы:

DECLARE @DSQL VARCHAR(100) DECLARE @TN VARCHAR(50)

SET @TN = 'Ученики'

SET @DSQL = 'SELECT * FROM ' + @TN EXECUTE (@DSQL)

Пример 2: Создайте и запустите динамический SQL-запрос, выбирающий всех учени- ков из таблицы «Ученики», вторая буква фамилии которых расположена в заданном диапа- зоне:

DECLARE @L1 CHAR(1) DECLARE @L2 CHAR(1)

SET @L1 = 'а' SET @L2 = 'д'

EXECUTE ('SELECT * FROM Ученики WHERE Фамилия LIKE ''_[' + @L1 + '-' + @L2+ ']%''')

Пример 3: Создайте временную таблицу #Temp и добавить в нее название пяти таблиц из базы данных «Учебная». Создайте курсор, который, построчно читая таблицу #Temp, вы- бирает из каждой таблицы все данные:

CREATE TABLE #TEMP (

TABLENAME VARCHAR(50)

)

INSERT INTO

#TEMP

VALUES

('Дисциплина'),

('Заявка'),

('Инженер'),

('Кафедра'),

('Сотрудник')

DECLARE TC CURSOR FOR SELECT TABLENAME FROM #TEMP

OPEN TC

DECLARE @TN VARCHAR(50)

FETCH FROM TC INTO @TN WHILE @@FETCH_STATUS = 0 BEGIN

EXECUTE ('SELECT * FROM ' + @TN) FETCH FROM TC INTO @TN

END

CLOSE TC DEALLOCATE TC

DROP TABLE #TEMP

XML:

Пример 4: Выберите данные из таблицы «Ученики» и экспортируйте в RAW формате

SELECT

ID

,Фамилия

,Предмет

,Школа

,Баллы

FROM

Ученики

FOR XML RAW

XML:

Пример 5: Выберите данные из таблицы «Страны» и экспортируйте в PATH формате

SELECT

Название

,Столица

FROM

,Площадь

,Население

,Континент

Страны

FOR XML PATH

JSON:

Пример 6: Выберите данные из таблицы «Ученики» и экспортируйте в PATH формате

SELECT

ID

,Фамилия

,Предмет

,Школа

,Баллы

FROM

Ученики

FOR JSON PATH

Задание

Создайте и запустите динамический SQL-запрос, выбирающий первые N строк из заданной таблицы.

Создайте и запустите динамический SQL-запрос, выбирающий все страны из таб- лицы «Страны», последняя буква названия которых расположена в заданном диапазоне.

Создайте временную таблицу #Temp и добавьте к ней название столбцов таблицы

«Страны». Создайте курсор, который, построчно читая таблицу #Temp, выбирает каждый раз соответствующий столбец из таблицы «Страны».

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

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

Выберите список европейских стран из таблицы «Страны» и экспортируйте в RAW формате XML.

Выберите список стран с населением больше 100 млн. чел. из таблицы «Страны» и экспортируйте в PATH формате XML.

Выберите список учеников из школы «Лицей» из таблицы «Ученики» и экспорти- руйте в PATH формате JSON

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

Пространственные данные

Цель работы

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

Изучить тип данных GEOMETRY.

Изучить тип данных GEOGRAPHY.

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

Пространственные данные представляют сведения о физическом расположении и форме геометрических объектов. Этими объектами могут быть точки расположения или более сложные объекты, такие как страны, дороги или озера.

SQL Server поддерживает два пространственных типа данных: GEOMETRY и GEOGRAPHY.

Пространственный тип данных GEOMETRY представляет данные в евклидовой (плос- кой) системе координат.

Пространственный тип данных GEOGRAPHY представляет данные в системе коорди- нат для сферической Земли.

Типы данных GEOMETRY и GEOGRAPHY поддерживают шестнадцать объектов пространственных данных или типов экземпляров. Однако только одиннадцать из этих типов экземпляров являются материализуемыми. Такие экземпляры можно создавать в базе данных и работать с ними.

Часто используемые объекты:

POINT(X Y) - для геометрических типов данных представляет собой единое место, где x и y представляют координаты создаваемого экземпляра. Для географических типов данных представляет единичное расположение, где X представляет широту, а Y - долготу. Значения широты и долготы измеряются в градусах. Значения широты находятся в интервале [-90, 90]. Значения долготы находятся в интервале [-180, 180].

LINESTRING(X1 Y1, X2 Y2[, Xn Yn]) - является одномерным объектом, представляющим последовательность точек и соединяющих их линейных сегментов.

CIRCULARSTRING(X1 Y1, X2 Y2, X3 Y3 [, Xn Yn]) - коллекция, состоящая из нуля или большего количества непрерывных круговых сегментов дуги. Сегмент дуги - это сегмент кривой, определяемый тремя точками на двумерной плоскости; первая точка не может совпадать с третьей. Если все три точки сегмента дуги лежат на одной прямой, сегмент дуги считается линейным сегментом.

POLYGON(X1 Y1, X2 Y2, X3 Y3 [, Xn Yn], X1 Y1) - представляет собой двухмерную поверхность, хранимую в виде последовательности точек, содержащую не менее трех уникальных точек, первая и последняя точки должны совпадать.

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

Пример 1: Создайте временную таблицу, которая имеет один столбец типа GEOME- TRY. Добавьте в таблицу пространственный объект LINESTRING с координатами (1 1, 3 3, 2

4, 2 0, 1 1). Создайте SQL-запрос, выбирающий все данные из заданной таблицы:

CREATE TABLE #ST (

D GEOMETRY

)

INSERT

#ST VALUES

('LINESTRING(1 1, 3 3, 2 4, 2 0, 1 1)')

SELECT

D

FROM

#ST

DROP TABLE #ST

Пример 2: Создайте временную таблицу, которая имеет один столбец типа GEOMETRY. Добавьте в таблицу пространственный объект POLYGON с координатами (-3 7, 10 24, 30 16, 13 18, 4 0, 14 -6, 6 -13, 0 -4, -14 -13, -22 -39, -20 -7, -7 3, -11 7, -12 9, -11 10, -9 10,

-3 7). Создайте SQL-запрос, выбирающий все данные из заданной таблицы:

CREATE TABLE #ST (

D GEOMETRY

)

INSERT

#ST VALUES

('POLYGON((-3 7, 10 24, 30 16,

13 18, 4 0, 14 -6,

6 -13, 0 -4, -14 -13,

-22 -39, -20 -7, -7 3,

-11 7, -12 9, -11 10,

-9 10, -3 7))')

SELECT

D

FROM

#ST

DROP TABLE #ST

Задание

Создайте временную таблицу, состоящую из двух полей типа geometry и varchar(20). Заполните таблицу заданными полигонами и их названиями. Выберите все поля из таблицы.

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

Создать таблицы (не менее 5-и) по своему варианту.

Заполнить таблицы (справочные таблицы - не менее 5-и строк, операционные таб- лицы - не менее 20-и).

Создать запросы:

Запрос на выбор всех данных по двум полям таблицы;

Запрос на выбор всех неповторяющихся данных по одному полю таблицы;

Запрос на выбор всех полей и записей таблицы, сгруппированных по значению од- ного поля, с использованием условия на группу (секции GROUP BY, HAVING) и с заголов- ками колонок, заданными в запросе;

Запрос на выбор всех неповторяющихся записей по одному полю таблицы с колон- кой, образованной агрегирующей функцией SUM и озаглавленной в соответствии со смыслом;

Выбор нескольких (не всех) полей таблицы, отсортированных по убыванию;

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

Запрос на выбор всех записей по одному полю таблицы с колонкой, образованной агрегирующей функцией SUM и озаглавленной в соответствии со смыслом; выбор записей с использованием условия диапазона (between);

Запрос на выбор всех записей по произвольному количеству полей таблицы с ис- пользованием агрегирующей функции AVG и условием на отбор записей, заданном в секции WHERE;

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

Запрос на выборку данных таблицы с условием сравнения по шаблону LIKE; Запрос с отбором по условию и сортировкой по убыванию одного из полей, а также добавлением поля, содержащего для всех записей константу, определенную при конструиро- вании запроса;

Запрос с использованием агрегирующих функций MIN и MAX;

Запрос с использованием сложного условия с логическими операторами AND, OR и сортировкой.

ВАРИАНТЫ ЗАДАНИЙ