БД. ИТМО. Практикум санктПетербург 2020
 Скачать 0.94 Mb.
|
|
Примеры запросов с решениями Описание функций и архитектура приведены в приложении. 1. Получить все названия товаров из таблицы Product. SELECT p.Name FROM [Production].[Product] AS p В данном случае для таблицы Product из схемы Production введен псевдоним p. Разумное использование псевдонимов позволяет увеличить скорость написания кода в MS SQL Management Studio. 2. Получить все названия товаров в системе, цена которых (listprice) выше 200. 13 SELECT p.Name FROM [Production].[Product] AS p WHERE p.ListPrice>200 3. Получить все названия товаров в системе цена которых (listprice) выше 200 и у которых первая буква в названии “S”. SELECT p.Name FROM [Production].[Product] AS p WHERE p.ListPrice>200 AND p.Name like 's%' 4. Получить все названия товаров в системе, цена которых (listprice) выше 200 и у которых в названии есть сочетание символов “are”. SELECT p.Name FROM [Production].[Product] AS p WHERE p.ListPrice>200 AND p.Name like '%are%' 5. Получить все названия товаров в системе, в названии которых третий символ – либо буква “s”, либо буква “r”. Решить задачу как минимум двумя способами. SELECT p.Name FROM [Production].[Product] AS p WHERE p.Name like '__s%' or p.Name like '__r%' или SELECT p.Name FROM [Production].[Product] AS p WHERE p.Name like '__[sr]%' 7. Получить все названия товаров в системе, в названии которых ровно 5 символов. SELECT p.Name FROM [Production].[Product] AS p WHERE p.Name like '_____' Или второй вариант SELECT p.Name FROM [Production].[Product] AS p WHERE len(p.Name)=5 Во втором примере используется строковая функция len. Строковые функции даны в приложении. 8. Найти, без повторений, номера товаров, которые были куплены хотя бы один раз. SELECT DISTINCT sod.ProductID FROM [Sales].[SalesORDERDetail] AS sod 9. Найти список всех возможных стилей (style) продукта, без повторений. SELECT DISTINCT p.Style FROM [Production].[Product] AS p WHERE p.Style is NOT null 14 10.Написать запрос, который возвращает названия товаров, которые были произведены между мартом 2011 года и мартом 2012 года включительно (необходимо учитывать формат даты) SELECT p.Name FROM [Production].[Product] AS p WHERE p.SellStartDate>='2011-01-03' AND p.SellStartDate<='2012-31-03' 11. Найти максимальную стоимость товара (отпускная цена ListPrice) из тех, которые были произведены, начиная с марта 2011 года. SELECT max(p.ListPrice) AS [max price] FROM [Production].[Product] AS p WHERE p.SellStartDate>='2011-01-03' 12. Вывести название и цвет продукта, отсортированные по названию продукта по возрастанию. SELECT p.Name, p.Color FROM [Production].[Product] AS p ORDER BY p.Name ASc 13. Вывести названия продукта, цвет и отпускную цену для таких товаров, у которых цвет определен и цена отлична от нуля, и отсортировать полученный список по возрастанию цвета товара и убыванию отпускной цены. SELECT p.Name, p.Color, p.ListPrice FROM [Production].[Product] AS p WHERE p.Color is NOT null AND p.ListPrice!=0 ORDER BY p.Color, p.ListPrice desc 14. Получить название продукта и разницу между отпускной стандартной ценой продукта и стандартной ценой продукта для тех товаров, у которых эти показатели не равны нулю. SELECT p.Name, p.ListPrice-p.StandardCost FROM [Production].[Product] AS p WHERE p.ListPrice!=0 AND p.StandardCost!=0 15. Найти название самого дорогого товара, исходя из предположения, что нет двух товаров с одинаковой ценой. SELECT top 1 with ties p.Name FROM [Production].[Product] AS p ORDER BY p.ListPrice desc 16. Найти список товаров, произведенных в 2005 году. SELECT p.Name FROM [Production].[Product] AS p WHERE datepart(YEAR,p.SellStartDate)=2005 Задания для самостоятельной работы 15 1. Найти и вывести на экран названия продуктов, их цвет и размер. 2. Найти и вывести на экран названия, цвет и размер таких продуктов, у которых цена более 100. 3. Найти и вывести на экран название, цвет и размер таких продуктов, у которых цена менее 100 и цвет Black. 4. Найти и вывести на экран название, цвет и размер таких продуктов, у которых цена менее 100 и цвет Black, упорядочив вывод по возрастанию стоимости продуктов. 5. Найти и вывести на экран название и размер первых трех самых дорогих товаров с цветом Black. 6. Найти и вывести на экран название и цвет таких продуктов, для которых определен и цвет, и размер. 7. Найти и вывести на экран не повторяющиеся цвета продуктов, у которых цена находится в диапазоне от 10 до 50 включительно. 8. Найти и вывести на экран все цвета таких продуктов, у которых в имени первая буква ‘L’ и третья ‘N’. 9. Найти и вывести на экран названия таких продуктов, которых начинаются либо на букву ‘D’, либо на букву ‘M’, и при этом длина имени – более трех символов. 10. Вывести на экран названия продуктов, у которых дата начала продаж – не позднее 2012 года. 11. Найти и вывести на экран названия всех подкатегорий товаров. 12. Найти и вывести на экран названия всех категорий товаров. 13. Найти и вывести на экран имена всех клиентов из таблицы Person, у которых обращение (Title) указано как «Mr.». 14. Найти и вывести на экран имена всех клиентов из таблицы Person, для которых не определено обращение (Title). 16 Практическая работа 2 Цель работы: Изучение выражения GROUP BY – группировка данных Одной из ключевых задач СУБД является выполнение операций, связанных с обработкой данных. Это необходимо для анализа информации, а также для формирования сложных запросов. Выражение GROUP BY позволяет разделить результаты выполнения оператора SELECT на группы по признаку, с целью выполнения статистических операций над группами. Простой запрос: SELECT [Name], [ListPrice], [Color] FROM [Production].[Product] WHERE [Color] IS NOT NULL В результате выполнения этого запроса пользователь получит название, стоимость и цвет таких товаров, у которых цвет определен. Это полезная и важная информация, однако у пользователя может появиться более сложная задача, например, посчитать количество товаров того или иного цвета. Существует набор функций, которые позволяют выполнить операцию над набором значений в столбце. Такие функции называются агрегирующими: SUM ( [ ALL | DISTINCT ] expressiON ) – сумма значений в столбце; MAX( [ ALL | DISTINCT ] expressiON ) – максимальное значение в столбце; MIN ( [ ALL | DISTINCT ] expressiON ) – минимальное значение в столбце; AVG ( [ ALL | DISTINCT ] expressiON ) – среднее значение в столбце; COUNT ( { [ [ ALL | DISTINCT ] expressiON ] | * } ) – количество строк в столбце, с учетом NULL значений. Приведенный общий синтаксис функций рассмотрим на примере: SELECT COUNT(DISTINCT [Color]) FROM [Production].[Product] WHERE [Color] IS NOT NULL В данном случае будет выполнен SELECT, который отберет из таблицы все строки, для которых цвет определен, и будет выполнена агрегирующая функция COUNT, которая посчитает количество различных цветов. Инструкция DISTINCT обеспечивает выбор неповторяющихся значений цвета, а функция COUNT считает количество полученных значений. Запрос следующего вида фактически возвращает количество строк, для которых определен цвет продукта: SELECT COUNT([Color]) FROM [Production].[Product] WHERE [Color] IS NOT NULL Он полностью эквивалентен следующему запросу: 17 SELECT COUNT(*) FROM [Production].[Product] WHERE [Color] IS NOT NULL Рассмотрим работу функции MAX: SELECT MAX([ListPrice]) FROM [Production].[Product] WHERE [Color]='Red' Этот запрос возвращает максимальную цену продукта, у которого цвет ‘Red’. Выражение GROUP BY позволяет разделить результат выполнения запроса на группы и выполнить ту или иную функцию над каждой из групп. SELECT [Color], COUNT(*) AS 'Amount' FROM [Production].[Product] WHERE [Color] IS NOT NULL GROUP BY [Color] В данном запросе все строки, для которых выполнено условие «цвет определен», разделены на группы по цвету, и над каждой группой выполнено функция COUNT. Результатом станет два столбца – цвет и количество строк с данным цветом в общей выборке. Необходимо сделать несколько важных дополнений. При группировке запрос возвращает только те данные, которые имеют одно и то же значение для всей группы. Запрос вида SELECT [Name], [Color], COUNT(*) AS 'Amount' FROM [Production].[Product] WHERE [Color] IS NOT NULL GROUP BY [Color] не будет выполнен, так как в группе может существовать несколько товаров с разными названиями. Даже если фактически в каждой группе с одним и тем же цветом все товары имеют одно и то же название, все равно это будет ошибкой. Так, при группировке нельзя использовать псевдонимы столбцов как признак группировки, нельзя группировать по столбцам с типами данных text, ntext или image, но можно группировать по результату выполнения функции над этими столбцами, например функции приведения типа или строковой функции. Нельзя использовать в качестве признака группировки подзапросы и данные типа XML. Нельзя использовать в качестве признака группировки столбец индексированного представления. Если столбец, по которому осуществляется группировка, содержит значения NULL, то они будут рассмотрены как идентичные и будут образовывать отдельную группу. 18 Допускается группировка по результату выполнения простых математических операций, а также группировка по двум и более столбцам: SELECT Столбец1, Столбец2 FROM Таблица GROUP BY Столбец1, Столбец2; SELECT Столбец1 + Столбец2 FROM Таблица GROUP BY Столбец1, Столбец2; SELECT Столбец1 + Столбец2 FROM Таблица GROUP BY Столбец1 + Столбец2; SELECT Столбец1 + Столбец2 + константное_значение FROM Таблица GROUP BY Столбец1, Столбец2. Рассмотрим следующий пример: SELECT [Color], [Style], COUNT(*) AS 'Amount' FROM [Production].[Product] WHERE [Color] IS NOT NULL AND [Style] IS NOT NULL GROUP BY [Color], [Style] Запрос возвращает количество продуктов с общим цветом и стилем, упомянутых в таблице продукты», с учетом того, что у этих товаров и цвет, и стиль определены. Выражение GROUP BY используется совместно с выражением HAVING, которое определяет условие поиска группы. SELECT [Color], COUNT(*) AS 'Amount' FROM [Production].[Product] WHERE [Color] IS NOT NULL GROUP BY [Color] HAVING COUNT(*)>10 Данный запрос выдаст название цветов, количество товаров данного цвета, но только для тех групп, где это количество больше 10. Естественно, будет выполнено условие, что цвет товаров определен. Условие поиска группы может содержать также логические операторы. Вот пример такой возможности: SELECT [Color], COUNT(*) AS 'Amount' FROM [Production].[Product] WHERE [Color] IS NOT NULL GROUP BY [Color] HAVING COUNT(*)>10 AND MAX([ListPrice])>3000 Стоит отметить, что операция группировки весьма затратна с точки зрения процессорной мощности и времени, неправильная конструкция может существенно увеличить время выполнения запроса. Например, стоит задача, рассмотренная в начале работы: посчитать количество товаров каждого цвета, 19 исключив товары, для которых цвет не определен. Выше эта задача была решена, однако формально допустимо следующее решение: SELECT [Color], COUNT(*) AS 'Amount' FROM [Production].[Product] GROUP BY [Color] HAVING [Color] IS NOT NULL Такой запрос будет выполняться дольше, так как сначала сформируются все группы, и только потом будет применено условие для выбора группы, т.е. будет отброшена группа, для которой цвет не определён. Время на формирование группы с неопределенным цветом фактически будет потрачено впустую. Использование GROUP BY допускает использование ORDER BY, TOP. Существуют специальные конструкции, выполняющие группировку с определёнными правилами. Рассмотрим конструкцию GROUP BY ROLLUP, которая выводить и общие и промежуточные итоги группировки. Частный синтаксис будет такой: SELECT столбец1, столбец2, столбец3, COUNT(*) FROM таблица GROUP BY ROLLUP (столбец1, столбец2, столбец3) В данном случае будут выведены итоги общей группировки по трем столбцам, а также добавлены результаты промежуточной группировки. В данном примере промежуточная группировка это строки полученные в результате группировки по первым двум столбцам, третий столбец будет иметь значение NULL, это строки полученные в результате группировки по первому столбцу, второй и третий будут иметь значение NULL, и одна строка где первый, второй и третий столбцы будут иметь значение NULL. Для понимания ее работы нам понадобится простой пример группировки. SELECT [Color], [Style], [Class], COUNT(*) FROM [Production].[Product] GROUP BY [Color], [Style], [Class] В данном случае пользователь увидит окончательный результат работы группировки по трем столбцам, четвертый столбец это число продуктов, в группе с одинаковым классом, стилем и цветом. Если выполнить запрос с использованием GROUP BY ROLLUP: SELECT [Color], [Style], [Class], COUNT(*) FROM [Production].[Product] GROUP BY ROLLUP ([Color], [Style], [Class]) 20 то пользователь получит все те же строки, как и при обычном GROUP BY, а также дополнительный набор с группировкой по цвету и стилю, дополнительный набор с группировкой только по цвету и строку без группировки. Схожим образом работает операция GROUP BY CUBE ( ), только к базовой группировке GROUP BY добавляются все возможные сочетания параметров группировки. Например, группировка GROUP BY CUBE (столбец1, столбец2), даст все возможные сочетания группировок, базовую группировку (столбец1, столбец2), и возможные сочетания (столбец1,NULL), (NULL,столбец2), (NULL, NULL). Конструкция GROUP BY GROUPING SETS ( ) позволяет объединить несколько операций GROUP BY. SELECT COUNT(*) FROM [Production].[Product] GROUP BY GROUPING SETS (([Color]),([Size])) Данный запрос вернет данные, сгруппированные по цвету, и еще набор строк, где данные сгруппированы по размеру. Примеры запросов с решениями 1. Найти номера первых трех подкатегорий (ProductSubcategoryID) с наибольшим количеством наименований товаров. SELECT TOP WITH TIES 3 [ProductSubcategoryID] FROM [Production].[Product] WHERE [ProductSubcategoryID] IS NOT NULL GROUP BY [ProductSubcategoryID] ORDER BY COUNT(*) DESC 2. Разбить продукты по количеству символов в названии, для каждой группы определить количество продуктов. SELECT LEN([Name]), COUNT(*) FROM [Production].[Product] GROUP BY LEN([Name]) 3. Проверить, есть ли продукты с одинаковым названием, если есть, то вывести эти названия. SELECT [Name] FROM [Production].[Product] GROUP BY [ProductID], [Name] 21 HAVING COUNT(*)>1 Задания для самостоятельной работы 1. Найти и вывести на экран количество товаров каждого цвета, исключив из поиска товары, цена которых меньше 30. 2. Найти и вывести на экран список, состоящий из цветов товаров, таких, что минимальная цена товара данного цвета более 100. 3. Найти и вывести на экран номера подкатегорий товаров и количество товаров в каждой категории. 4. Найти и вывести на экран номера товаров и количество фактов продаж данного товара (используется таблица SalesORDERDetail). 5. Найти и вывести на экран номера товаров, которые были куплены более пяти раз. 6. Найти и вывести на экран номера покупателей, CustomerID, у которых существует более одного чека, SalesORDERID, с одинаковой датой 7. Найти и вывести на экран все номера чеков, на которые приходится более трех продуктов. 8. Найти и вывести на экран все номера продуктов, которые были куплены более трех раз. 9. Найти и вывести на экран все номера продуктов, которые были куплены или три или пять раз. 10. Найти и вывести на экран все номера подкатегорий, в которым относится более десяти товаров. 11. Найти и вывести на экран номера товаров, которые всегда покупались в одном экземпляре за одну покупку. 12 Найти и вывести на экран номер чека, SalesORDERID, на который приходится с наибольшим разнообразием товаров купленных на этот чек. 13 Найти и вывести на экран номер чека, SalesORDERID с наибольшей суммой покупки, исходя из того, что цена товара – это UnitPrice, а количество конкретного товара в чеке – это ORDERQty. 14 Определить количество товаров в каждой подкатегории, исключая товары, для которых подкатегория не определена, и товары, у которых не определен цвет. 15 Получить список цветов товаров в порядке убывания количества товаров данного цвета 16 Вывести на экран ProductID тех товаров, что всегда покупались в количестве более 1 единицы на один чек, при этом таких покупок было более двух. 22 Практическая работа 3 Цель работы: Выборка данных из нескольких таблиц. Структурированный язык запросов позволяет получать данные из нескольких таблиц одновременно. Существует простой и понятный синтаксис подобных запросов, но для полного понимания необходимо рассмотреть несколько общих вопросов. Допустим, пользователь имеет две таблицы. Первая таблица будет называться Корма, и в ней один столбец – название корма. Для примера в таблице будет две строки: сено, мясо. Вторая таблица будет называться Животные и также будет иметь один столбец – название животного, в данном случае будет две строки: собака, лошадь. Вполне допустим следующий запрос: SELECT [название корма], [название животного] FROM Корма, Животные Однако полученный результат вряд ли устроит пользователя: сено собака сено лошадь мясо собака мясо лошадь Чтобы результат соответствовал реальному миру, СУБД должно иметь возможность исключить не существующие в реальном мире варианты сочетания корма и животного. Модифицируем наши таблицы. Добавим в таблицу Корма столбец «номер корма», это будет первичный ключ для этой таблицы. В таблицу Животные необходимо будет добавить два столбца. Столбец «номер животного» станет первичным ключом этой таблицы, а еще один столбец, «номер корма», станет внешним ключом, связанным с первичным ключом таблицы Корма. Естественно, значения в столбце «номер корма» таблицы Животные должен содержать значения, которые удовлетворяют двум требованиям. Во-первых, есть точно такие же значения в столбце «номер корма» таблицы Корма. Во-вторых, эти значения соответствуют реальному миру, т.е. в строке, где содержится информация о лошади, должен стоять число, равное номеру сена, а в строке, где упомянута собака, номер ее корма должен соответствовать номеру мяса. Используемые определения: суперключ, потенциальный ключ, первичный и внешний ключ. Суперключ – атрибут или множество атрибутов, единственным образом идентифицирующие кортеж. Потенциальный ключ – суперключ, который не содержит подмножества, также являющегося суперключом данного отношения. Отношение может иметь 23 несколько потенциальных ключей. Если потенциальный ключ состоит из нескольких атрибутов, он называется составным ключом. Первичный ключ – потенциальный ключ, который выбран для уникальной идентификации кортежей внутри отношения. Внешний ключ – атрибут или множество атрибутов внутри отношения, которое соответствует потенциальному ключу некоторого (может быть, того же самого) отношения. Внешние ключи позволяют описать связь между отношениями. После добавления столбцов и внесения необходимых данных можно выполнить запрос следующего вида: SELECT [название корма], [название животного] FROM Корма, Животные WHERE Корма.[Номер корма]=Животные.[Номер корма] Он даст результат, который соответствует реальному миру: сено лошадь мясо собака В данном случае СУБД получила однозначную инструкцию – не включать в результирующий набор данных, полученный из двух таблиц, те строки, для которых не выполняется равенство внешнего и первичного ключей. Такой способ соединения таблиц допустим. Пользователь может производить соединения двух и более таблиц, используя сравнения на равенство или неравенство значений столбца одной таблицы со значениями столбца другой таблицы. Надо отметить, что подобные соединения не подразумевают, что столбцы обязательно являются первичным и внешним ключом соответственно. У подобного соединения есть один существенный недостаток. Операция сравнения на равенство не применима к значениям NULL. Допустим, у нас появилось информация о животном, например, шимпанзе, для которого корм не определен. В итоговую выборку это животное не попадет. Для разрешения подобной трудности используется операция соединения JOIN. Общий синтаксис, стандартный для всех реляционных СУБД выглядит так: FROM первая_таблица ТИП_СОЕДИНЕНИЯ вторая_таблица [ON (условия_соединения)] Порядок следования таблиц при соединении играет важную роль, от этого зависит результат при использовании некоторых типов соединений. Иногда таблицы называют левая и правая таблицы. Тип соединения определяет принцип соединения таблиц. Условия соединения определяют условия сравнения значений в столбцах соединяемых таблиц – равенство или неравенство. Рассмотрим все типы соединения. Соединение INNER JOIN. 24 Рассмотрим запрос следующего вида: SELECT p.Name, s.Name FROM [Production].[Product] p INNER JOIN [Production].[ProductSubcategory] s ON p.ProductSubcategoryID=s.ProductSubcategoryID Пользователь получит список названий товаров и названий категорий, к которым относится товар, при этом не будут рассмотрены товары, для которых подкатегория не определена. INNER JOIN не учитывает NULL значения как в левой, так и в правой таблице при объединении. Синтаксически допустимо и традиционно используется сокращение названия соединения INNER JOIN до просто JOIN. В рассмотренном выше запросе объединяются таблицы Product и ProductSubcategory, каждая из которых имеет столбец с именем Name. Чтобы различать эти столбцы при объединении, были введены псевдонимы таблиц, и к столбцу Name обращение идет через оператор доступ – точка. Соединение LEFT JOIN. LEFT JOIN учитывает значения NULL из левой таблицы. Таким образом нижеприведенный запрос вернет названия продуктов и название подкатегорий, в том числе и те случаи, когда у продукта не определена подкатегория. Если подкатегория продукта не определена, то ее название будет NULL. SELECT p.Name, s.Name FROM [Production].[Product] p LEFT JOIN [Production].[ProductSubcategory] s ON p.ProductSubcategoryID=s.ProductSubcategoryID Соединение RIGHT JOIN учитывает значения NULL из правой таблицы. Рассмотрим запрос: SELECT p.Name, s.Name FROM [Production].[Product] p RIGHT JOIN [Production].[ProductSubcategory] s ON p.ProductSubcategoryID=s.ProductSubcategoryID В данном случае соединение RIGHT JOIN учитывает ситуацию, в столбце правой таблицы, ProductSubcategory, есть значения NULL. Это правило формально соблюдено СУБД, несмотря на то, что в правой таблице, ProductSubcategory, столбец для соединения, ProductSubcategoryID, является первичным ключом. Соединение FULL OUTER JOIN учитывает значение NULL и в левой, и в правой таблице. На практике синтаксическая единица FULL OUTER JOIN сокращается до FULL JOIN. 25 Рассмотрим пример. Даны две таблицы, tA и tB, со столбцами [tAvalue] и [tBvalue]. Столбец [tAvalue] имеет следующие значения: 1, 2, 3 и NULL. Столбец [tBvalue] имеет значения: 2, 3, 4, и NULL. Выполнив запрос SELECT [tAvalue], [tBvalue] FROM [dbo].[tA] FULL JOIN [dbo].[tB] ON tA.[tAvalue]=tB.[tBvalue] пользователь получит следующие пары значений: (1, NULL), (2,3), (3,3), (NULL, NULL), (NULL,4), (NULL, NULL). В данном случае был рассмотрен пример соединения, в котором условие соединения не связано с первичным или внешним ключом. Также надо учитывать, что запрос вернул две строки со значением NULL в каждом из столбцов. Соединение CROSS JOIN производит полное декартово произведение двух таблиц и не имеет условия соединения. Рассмотрим запрос к таблицам из предыдущего примера SELECT [tAvalue], [tBvalue] FROM [dbo].[tA] CROSS JOIN [dbo].[tB] Этот запрос вернет все возможные сочетания значений [tAvalue] и [tBvalue], т.е. шестнадцать строк. Соединение CROSS JOIN может потребовать значительных ресурсов СУБД и серьезно затруднить работу других пользователей. Соединение таблиц допускает соединение таблицы со своей копией. Стоит задача – найти такие продукты, для которых существуют продукты с таким же названием. Задачу можно решить с использованием выражения GROUP BY, а можно и с использованием самосоединения. SELECT p1.Name FROM [Production].[Product] p1 JOIN [Production].[Product] p2 ON p1.Name=p2.Name AND p1.ProductID!=p2.ProductID Стоит обратить внимание что в данном запросе обязательно использование псевдонимов. Для соединения использованы два условия, одно из которых – на неравенство. Соединение таблиц допускает использование ключевых слов WHERE, GROUP BY, HAVING, ORDER BY и других. Допускается соединение более чем двух таблиц. |