БД. ИТМО. Практикум санктПетербург 2020

• Название: Практикум санктПетербург 2020
• Дата: 27.11.2022
• Размер: 0.94 Mb.
• Имя файла: ИТМО.pdf
• Тип: Практикум; #814747
• страница: 1 из 5

В.В. Повышев
БАЗЫ ДАННЫХ.
ПРАКТИКУМ
Санкт-Петербург
2020

МИНИСТЕРСТВО НАУКИ И ВЫСШЕГО ОБРАЗОВАНИЯ РОССИЙСКОЙ
ФЕДЕРАЦИИ
УНИВЕРСИТЕТ ИТМО
В.В. Повышев
БАЗЫ ДАННЫХ.
ПРАКТИКУМ
УЧЕБНО-МЕТОДИЧЕСКОЕ ПОСОБИЕ
РЕКОМЕНДОВАНО К ИСПОЛЬЗОВАНИЮ В УНИВЕРСИТЕТЕ ИТМО по направлению подготовки 09.03.02 Информационные системы и технологии в качестве учебно-методического пособия для реализации основных профессиональных образовательных программ высшего образования бакалавриата
Санкт-Петербург
2020

Повышев В.В. Базы данных. Практикум – СПб: Университет ИТМО,
2019. – 50 с.
Рецензент(ы):
Береснев Артем Дмитриевич, старший преподаватель факультета инфокоммуникационных технологий, Университета ИТМО.
В пособии приведены описание синтаксиса и использования инструкции
SELECT языка T-SQL, набор практических заданий с решениями, набор заданий для самопроверки.
Университет ИТМО – ведущий вуз России в области информационных и фотонных технологий, один из немногих российских вузов, получивших в
2009 году статус национального исследовательского университета. С 2013 года
Университет
ИТМО
– участник программы повышения конкурентоспособности российских университетов среди ведущих мировых научно-образовательных центров, известной как проект «5 в 100». Цель
Университета ИТМО– становление исследовательского университета мирового уровня, предпринимательского по типу, ориентированного на интернационализацию всех направлений деятельности.
© Университет ИТМО, 2020
©
Повышев В.В., 2020

3
Содержание
Введение ................................................................................................................ 4
Практическая работа 1 ....................................................................................... 5
Практическая работа 2 ....................................................................................... 16
Практическая работа 3 ....................................................................................... 22
Практическая работа 4 ....................................................................................... 32
Практическая работа 5 ....................................................................................... 42
Практическая работа 6 ....................................................................................... 47
Приложения .......................................................................................................... 54

4
Введение
В курсе Базы данных студент знакомятся с принципами организации работы реляционных баз данных. Важнейшей задачей, осуществляемой системами управления базами данных, является обеспечения доступа до данных.
Структурированный язык запросов SQL обеспечивает возможность взаимодействия пользователя с СУБД и получения данных. Наиболее сложной частью языка является написание инструкций SELECT, изучению которой и посвящён данный практикум. В процессе изучения дисциплины студент на практике осваивает следующие умения и навыки: составлять запросы на языке
SQL, выбирать структуру запроса для минимизации времени его выполнения, составления эффективных запросов к реляционной СУБД. Полученные навыки необходимы для успешного освоения таких дисциплин как Программирование,
Технологии программирования, Информационные системы и т.д.
Практикум состоит из шести работ, которые необходимо изучать последовательно. Каждая последующая работа подразумевает использование навыков и умений, полученных в предыдущей работе. Работы состоят из теоретического материала, содержащего описание инструкций запросов SQL, сопровождаются примерами и пояснениями к ним, так же в работах рассматривается примеры выполнения конкретных задач, и даны задачи для самостоятельной работы.
Для выполнения практических работ используется демонстрационное программное обеспечение компании Microsoft: Microsoft SQL Server Express,
Microsoft SQL Server Management Studio и учебная база данных Microsoft
Adventure Works. Программное обеспечение и база данных доступна для свободного использования на официальном сайте поддержки компании
Microsoft - docs.microsoft.com.

5
Практическая работа 1
Цель работы: Изучение базового синтаксиса инструкции SELECT, выражения
WHERE и ORDER BY.
Базовый синтаксис инструкции SELECT
Общий синтаксис инструкции SELECT весьма сложен, однако достаточное представление дает следующее описание:
SELECT лист выборки [INTO новая таблица ]
[ FROM источник данных ] [ WHERE условия поиска ]
[ GROUP BY выражение для группировки ]
[ HAVING условия поиска по группам ]
[ ORDER BY выражение для сортировки [ ASC | DESC ] ]
В данном описании надо учитывать, что курсивом указаны пользовательские, обязательные, параметры синтаксиса конструкции на языке T-SQL, а в квадратных скобках, [], – необязательные элементы синтаксиса. Не надо путать использование квадратных скобок [] в описании синтаксиса и в указании идентификаторов. Фигурные скобки, {}, говорят о том, что пользователь обязан выбрать один из вариантов выражений, перечисленных в скобках через символ
«вертикальная черта», |. Традиционно все ключевые слова T-SQL пишутся в верхнем регистре, т.е. прописными буквами, но это правило не строгое, язык T-
SQL – регистронезависимый.
Инструкция следующего вида вполне допустима:
SELECT ‘мама мыла раму’
В результате выполнения данной инструкции пользователь получит таблицу
(результат выполнения инструкции SELECT всегда является таблицей), состоящую из одной строки и одного столбца, при этом столбец не будет иметь имени.
Следующее изменение инструкции позволит получить таблицу с именованным столбцом:
SELECT ‘мама мыла раму’ AS [просто текст]
В данном случае ключевое слово AS говорит о том, что далее будет следовать псевдоним для столбца. Псевдонимы могут быть у столбцов, таблиц и результатов выполнения запросов. Псевдоним, в данном примере [просто
текст], всегда должен подчиняться правилу идентификаторов (начинаться с буквы, возможно, использовать цифры, все символы без пробелов), если же по каким-то причинам правило идентификаторов нарушается, то необходимо использовать квадратные скобки. Существует общая рекомендация повсеместного использования квадратных скобок, но на практике их опускают ради повышения скорости набора кода.

6
В качестве источника данных в общем случае выступают таблицы базы данных.
Также в качестве источника данных могут выступать отдельные таблицы, результат объединения таблиц, представления, табличные переменные, обобщенные табличные выражения, производные таблицы.
Пример выборки всего содержимого из таблицы:
SELECT *
FROM [Production].[Product]
Необходимо дать несколько пояснений. Символ «звездочка», *, говорит о том, что из таблицы должны быть выбраны все столбцы. Использовать подобную конструкцию надо с крайней осторожностью, так как она создает значительную нагрузку на сервер и сетевую инфраструктуру. В случае если используется выборка из нескольких таблиц использование «звездочки» может привести к ошибке, так как в результате объединения таблиц может возникнуть ситуация наличия двух и более столбцов с одним именем в результирующем множестве
(данный вопрос будет рассмотрен в последующих разделах). Конструкция
[Production].[Product] представляет собой полное имя таблицы, где имя таблицы
– [Product], а [Production] – это название схемы. Схема – это поименованное логическое объединение таблиц, используемое для упрощения понимания архитектуры базы данных.
Для получения данных из столбца необходимо указать соответствующее имя столбца, например, запрос, возвращающий все названия продуктов из таблиц
[Product]:
SELECT [Name]
FROM [Production].[Product]
Рассмотрим еще один вариант написания запроса:
SELECT p.[Name]
FROM [Production].[Product] AS p
В данном случае для таблицы Product из схемы Production введен псевдоним p.
Разумное использование псевдонимов позволяет увеличить скорость написания кода в MS SQL Management Studio.
Следующая инструкция позволяет получить таблицу с двумя столбцами:
SELECT [ProductID], [Name]
FROM [Production].[Product]
Рассмотрим запрос:
SELECT [ProductID], [Name], [ListPrice]-[StandardCost] AS [discount size]
FROM [Production].[Product]

7
В этом примере пользователь получает таблицу из трех столбцов, данные первых двух столбцов взяты непосредственно из таблицы [Product], а третий столбец является результатом выполнения операции вычитания данных одного столбца из данных другого столбца, с использованием псевдонима для упрощения понимания результата.
Еще один вариант:
SELECT [ProductID], [Name], 'empty column', GETDATE()
FROM [Production].[Product]
Пользователь может также получать не только столбцы данных из таблицы, но и столбец с константным значением (третий столбец), а также столбец, содержащий результат выполнения функции. В данном случае в четвертом столбце выводится текущее время сервера, которое возвращает функция
GETDATE():
Обратите внимание на формат даты и времени, которые вернул сервер. Формат зависит от локальных настроек сервера и соответствует одному из нескольких десятков поддерживаемых стандартов.
По устаревшим стандартам требовалось использование ключевого слова ALL:
SELECT ALL [Size]
FROM [Production].[Product]
Ключевое слово ALL говорит о том, что в результат выборки войдут все возможные значения столбца [Size], эта конструкция в текущей версии T-SQL является конструкцией по умолчанию и на практике всегда опускается.
Получение данных без повторений:
SELECT DISTINCT [Size]
FROM [Production].[Product]
Ключевое слово DISTINCT определяет, что в выборке будут данные из столбца
[Size] без повторений.
SELECT DISTINCT [Color], [Size]
FROM [Production].[Product]
Этот запрос вернет все существующие в таблице пары значений [Color] и [Size] без повторений.
Выражение ORDER BY
Учебные примеры могут дать ложную иллюзию того, что данные, получаемые в результате выполнения инструкции SELECT, находятся в каком-то упорядоченном виде. Рассмотренный ранее запрос, SELECT [Name] FROM [Production].[Product],

8 действительно возвращает название всех продуктов в упорядоченном по алфавиту виде, но это всего лишь стечение обстоятельств. В общем случае запрос возвращает данные в том порядке, который определен их физическим расположением в файлах
БД и операциями, которые выполнила СУБД для их получения. Однако есть инструкция, позволяющая получить упорядоченный набор данных.
Упорядоченный вывод:
SELECT [Name], [ListPrice]
FROM [Production].[Product]
ORDER BY [ListPrice] DESC
Инструкция ORDER BY определяет порядок вывода строк, в данном случае будут выведены названия продукта и его цена в виде таблицы, упорядоченной по убыванию цены. Ключевое слово DESC определяет порядок упорядочивания по убыванию, ASC – по возрастанию. Если направление упорядочивания не указано явно, то упорядочивание будет произведено по возрастанию.
Инструкция ORDER BY всегда является завершающей частью запроса.
Упорядочение по нескольким столбцам, с указанием направления для каждого столбца:
SELECT [FirstName], [MiddleName], [LastName]
FROM [Person].[Person]
ORDER BY [FirstName] ASC, [MiddleName] DESC, [LastName] ASC
Упорядочение по позиции в выводе, в данном примере по второму столбцу:
SELECT [Name], [StandardCost] - [ListPrice]
FROM [Production].[Product]
ORDER BY 2
Также возможно использование функции для получения параметра упорядочивания:
SELECT BusINessEntityID, JobTitle, HireDate
FROM HumanResources.Employee
ORDER BY DATEPART(year, HireDate)
Рассмотрим следующий пример:
SELECT TOP 3 [Name], [ListPrice]
FROM [Production].[Product]
ORDER BY [ListPrice] DESC
Инструкция TOP ограничивает количество строк, которые получит пользователь при выполнении запроса, в примере соответственно будут выведены три продукта и их цены в отсортированном наборе. Необходимо сделать замечание:

9 возможно, существует некоторый продукт, условно говоря, четвертый, цена которого равна цене третьего продукта, но он не попадет в выборку для пользователя. К сожалению, мы не можем заранее определить, какой из продуктов, третий или четвертый, с равной ценой, в отсортированном списке попадет в пользовательскую выборку при использовании конструкции TOP 3.
Инструкция WITH TIES позволяет выводить все строки, «соперничающие за последнее место»:
SELECT TOP 3 WITH TIES [Name], [ListPrice]
FROM [Production].[Product]
ORDER BY [ListPrice] DESC
Приведенный пример использования инструкции WITH TIES позволяет вывести на экран не только первые три продукта из набора, отсортированного по убыванию цены, но и все продукты, цена которых равна цене третьего продукта.
Данная инструкция работает исключительно с SELECT и только при использовании оператора упорядоченного вывода ORDER BY.
Вывод доли строк из результирующего набора:
SELECT TOP 3 PERCENT [Name], [ListPrice]
FROM [Production].[Product]
ORDER BY [ListPrice] DESC
В случае использования конструкции TOP число PERCENT пользователь получает некоторый процент строк, равный числу из результирующего набора.
Процент строк округляется до следующего целого.
Использование инструкции INTO позволяет создать новую таблицу и поместить в нее результат выборки:
SELECT TOP 3 PERCENT [Name], [ListPrice] INTO Tmp
FROM [Production].[Product]
ORDER BY [ListPrice] DESC
Выполнение данного запроса будет разбито на два этапа. На первом этапе будет создана таблица Tmp. На втором этапе в нее будет помещен результат выполнения запроса. Необходимо добавить несколько замечаний. Если по каким-то причинам при выполнении запроса произойдет сбой, то будет создана пустая таблица. Использование инструкции упорядоченного выводы выборки,
ORDER BY, не гарантирует сохранения порядка строк в созданной таблице. Если результирующий набор дает набор строк, не обладающий свойством уникальности, то все равно таблица будет создана, данные будут добавлены, но никакие первичные ключи пользователю не будут доступны, хотя на физическом уровне они будут реализованы СУБД. Для использования инструкции INTO необходимо иметь права, аналогичные правам создания обычной таблицы.

10
Выражение WHERE
Инструкция SELECT не позволяет выбрать отдельные строки из источника, но можно выбрать строки, удовлетворяющие определенным условиям – наличию тех или иных значений в столбцах.
Простой пример использования предложения WHERE:
SELECT [Name], [ListPrice]
FROM [Production].[Product]
WHERE [ListPrice]=3578.27
В данном случае будут выведены названия продуктов, у которых цена равна
3578.27, а также их цена. Так как столбец [ListPrice] имеет тип mONey, то использование оператора сравнения на равенство допустимо.
В следующем примере ограничения затрагивают два столбца:
SELECT [Name], [ListPrice]
FROM [Production].[Product]
WHERE [ListPrice]=3578.27 AND [Size]=62
Пользователь получит название таких товаров, у которых цена равна 3578.27 и размер равен 62, и их цену, для этого был использован оператор AND – логическое И. Существует еще два логических оператора, OR – логическое ИЛИ и NOT – логическое отрицание.
Можно написать достаточно сложный запрос:
SELECT [Name] FROM [Production].[Product]
WHERE
([ListPrice]>20 AND [Color]='Red')
OR
([ListPrice]>25 AND [Color]='Black')
В данном случае пользователь получит название таких товаров, у которых цена выше 20 и цвет Red, или цена выше 25 и цвет Black.
При работе с числовыми данными бывают ситуации, когда необходимо использовать некоторый диапазон данных. Например, стоит вопрос – найти названия товаров, цена которых лежит в диапазоне от 20 до 40, включая границы диапазона. Реализовать этот запрос можно с помощью логического оператора
AND.
SELECT [Name] FROM [Production].[Product]
WHERE [ListPrice]>=20 AND [ListPrice]<=40

11
Однако существует специальный оператор BETWEEN, который определяет диапазон для проверки. Общий синтаксис этого оператора выглядит так:
выражение [ NOT ] BETWEEN начало_диапазона AND конец_диапазона
Оператор возвращает значение TRUE, если выражение входит, или не входит, если используется оператор NOT, в диапазон от начало_диапазона до
конец_диапазона включительно.
С помощью этого оператора предыдущий запрос можно реализовать следующим образом:
SELECT [Name] FROM [Production].[Product]
WHERE [ListPrice] BETWEEN 20 AND 40
Если стоит задача сравнения выражения с некоторым набором значений, то могут применяться следующие операторы – IN, ANY (или его синоним – SOME),
ALL.
Оператор IN определяет принадлежность значения одному и значений в списке.
SELECT [Name] FROM [Production].[Product]
WHERE [Color] IN ('Red','Black','Silver')
Приведенный выше запрос возвращает название товаров, у которых цвет либо
Red, либо Black, либо Silver. Стоит обратить внимание, что в скобках может быть не просто набор значений, но и выражение, которое формирует такой набор, например, оператор SELECT.
Операторы ALL и ANY будут рассмотрены позже, в работе с подзапросами.
При работе со строковыми значениями возникает необходимость проверки на соответствие значения не строке, а шаблону, например, требуется найти все продукты, начинающиеся на букву ‘D’. Для этого используется оператор LIKE:
SELECT [Name] FROM [Production].[Product]
WHERE [Name] LIKE 'D%'
При использовании оператора LIKE можно использовать оператор NOT.
Необходимо помнить, что что существуют параметры сравнения, определяемые настройкой СУБД и отдельных таблиц, но в общем случае СУБД не делает различия между строчными и прописными буквами.
В строковых шаблонах допускаются следующие символы:
% – символ-шаблон, заменяющий любую последовательность символов;
_ (подчеркивание) – символ-шаблон, заменяющий любой одиночный символ;
[] – заменяет одиночный символ, указанный в угловых скобках, можно перечислить символы, или диапазон (через дефис) символов;

12
[^] – заменяет одиночный символ, не указанный в угловых скобках, можно перечислить символы, или диапазон (через дефис) символов.
Допускается использование ESCAPE последовательностей:
WHERE СТРОКА LIKE ‘%[a-f][^xyz]_30!%%’ ESCAPE ‘!’
Данный пример является частью запроса, который, в частности, проверяет, совпадают ли значения в столбце СТРОКА со следующим шаблоном – любое количество символов, далее один из символов диапазона от ‘a’ до ‘f’ включительно, далее любой символ, кроме символов ‘x’, ‘y’ или ‘z’, далее еще один любой символ, далее 30%, и опять последовательность любых символов.
Символ ‘!’ является эскейп-символом и говорит о том, что следующий за ним символ, в данном случае ‘%’, не надо рассматривать как управляющий; таким образом, в пример включен символ %, который в общем случае является служебным.
В реляционных базах данных существует особое значение – NULL. Это значение не является нулевым значением в математическом понимании нуля, это неопределённость, которая сообщает пользователям, что в данный момент времени значение атрибута не определено. Для работы с этим значением нельзя использовать оператор сравнения на равенство или любой другой оператор сравнения. Проверка на определенность осуществляется с помощью оператора
IS.
SELECT [Name] FROM [Production].[Product]
WHERE [Color] IS NULL
Данный запрос возвращает название товаров, для которых цвет не определен.
SELECT [Name] FROM [Production].[Product]
WHERE [Size] IS NOT NULL
Этот запрос возвращает название товаров, у которых определен размер, т.е. он не является NULL.