Управление и автоматизация БД Практическая работа №10

Практическое занятие № 10

Создание простых запросов на выборку

Цель работы: получение навыков создания простых запросов в среде MS SQL Server.

Краткие теоретические основания выполнения задания Выборка данных. Команда SELECT

Для получения данных применяется команда SELECT. В упрощенном виде она имеет следующий синтаксис:

SELECT список_столбцов FROM имя_таблицы

Например, пусть ранее была создана таблица Products, и в нее добавлены некоторые начальные данные:

CREATE TABLE Products

(

Id INT IDENTITY PRIMARY KEY,

ProductName NVARCHAR(30) NOT NULL,

Manufacturer NVARCHAR(20) NOT NULL,

ProductCount INT DEFAULT 0, Price MONEY NOT NULL

);

INSERT INTO Products

VALUES

('iPhone 6', 'Apple', 3, 36000),

('iPhone 6S', 'Apple', 2, 41000),

('iPhone 7', 'Apple', 5, 52000),

('Galaxy S8', 'Samsung', 2, 46000),

('Galaxy S8 Plus', 'Samsung', 1, 56000),

('Mi6', 'Xiaomi', 5, 28000),

('OnePlus 5', 'OnePlus', 6, 38000)

Получим все объекты из этой таблицы:

SELECT * FROM Products

Символ звездочка * указывает, что нам надо получить все столбцы.

Получение всех столбцов с помощью символа звездочки * считается не очень хорошей практикой, так как, как правило, не все столбцы бывают нужны. И более оптимальный подход заключается в указании всех необходимых столбцов после слова SELECT. Исключение составляет тот случай, когда надо получить данные по абсолютно всем столбцам таблицы. Также использование

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

SELECT ProductName, Price FROM Products

Спецификация столбца необязательно должна представлять его название. Это может быть любое выражение, например, результат арифметической операции. Так, выполним следующий запрос:

SELECT ProductName + ' (' + Manufacturer + ')', Price, Price *

ProductCount

FROM Products

Здесь при выборке будут создаваться три столбца. Первый столбец представляет результат объединения двух столбцов ProductName и Manufacturer. Второй столбец - стандартный столбец Price. А третий столбец представляет значение столбца Price, умноженное на значение столбца ProductCount.

С помощью оператора AS можно изменить название выходного столбца или определить его псевдоним:

SELECT

ProductName + ' (' + Manufacturer + ')' AS ModelName, Price,

Price * ProductCount AS TotalSum

FROM Products

В данном случае результатом выборки являются данные по 3-м столбцам. Первый столбец ModelName объединяет столбцы ProductName и Manufacturere, второй представляет стандартный столбец Price. Третий столбец TotalSum хранит произведение столбцов ProductCount и Price. При этом, как в случае со столбцом Price, необязательно определять название результирующего столбца с помощью AS.

DISTINCT

Оператор DISTINCT позволяет выбрать уникальные строки. Например, в нашем случае в таблице может быть по несколько товаров от одних и тех же производителей. Выберем всех производителей:

SELECT DISTINCT Manufacturer

FROM Products

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

Выборка с добавлением SELECT INTO

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

SELECT ProductName + ' (' + Manufacturer + ')' AS ModelName, Price

INTO ProductSummary

FROM Products

SELECT * FROM ProductSummary

После выполнения этой команды в базе данных будет создана еще одна таблица ProductSummary, которая будет иметь два столбца ModelName и Price, а данные для этих столбцов будут взяты из таблицы Products:

При выполнении этой команды таблица, в которую идет выборка (в данном случае ProductSummary), не должна существовать в базе данных.

Но, допустим, мы потом решили добавить все данные из таблицы Products в уже существующую таблицу ProductSummary. В этом случае можно опять же использовать команду INSERT:

INSERT INTO ProductSummary

SELECT ProductName + ' (' + Manufacturer + ')' AS ModelName, Price

FROM Products

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

Сортировка. ORDER BY

Оператор ORDER BY позволяет отсортировать извлекаемые значения по определенному столбцу:

SELECT *

FROM Products

ORDER BY ProductName

В данном случае строки сортируются по возрастанию значения столбца ProductName:

Сортировку также можно проводить по псевдониму столбца, который определяется с помощью оператора AS:

SELECT ProductName, ProductCount * Price AS TotalSum

FROM Products

ORDER BY TotalSum

По умолчанию применяется сортировка по возрастанию. С помощью дополнительного оператора DESC можно задать сортировку по убыванию.

SELECT ProductName

FROM Products

ORDER BY ProductName DESC

По умолчанию вместо DESC используется оператор ASC:

SELECT ProductName

FROM Products

ORDER BY ProductName ASC

Если необходимо отсортировать сразу по нескольким столбцам, то все они перечисляются после оператора ORDER BY:

SELECT ProductName, Price, Manufacturer

FROM Products

ORDER BY Manufacturer, ProductName

В этом случае сначала строки сортируются по столбцу Manufacturer по возрастанию. Затем если есть две строки, в которых столбец Manufacturer имеет одинаковое значение, то они сортируются по столбцу ProductName также по возрастанию. Но опять же с помощью ASC и DESC можно отдельно для разных столбцов определить сортировку по возрастанию и убыванию:

SELECT ProductName, Price, Manufacturer

FROM Products

ORDER BY Manufacturer ASC, ProductName DESC

В качестве критерия сортировки также можно использовать сложно выражение на основе столбцов:

SELECT ProductName, Price, ProductCount

FROM Products

ORDER BY ProductCount * Price

Извлечение диапазона строк Оператор TOP

Оператор TOP позволяет выбрать определенное количество строк из таблицы:

SELECT TOP 4 ProductName

FROM Products

Дополнительный оператор PERCENT позволяет выбрать процентное количество строк из таблицы. Например, выберем 75% строк:

SELECT TOP 75 PERCENT ProductName

FROM Products

OFFSET и FETCH

Оператор TOP позволяет извлечь определенное количество строк, начиная с начала таблицы. Для извлечения набора строк из любого места, применяются операторы OFFSET и FETCH. Важно, что эти операторы применяются только в отсортированном наборе данных после выражения ORDER BY.

ORDER BY выражение

OFFSET смещение_относительно_начала {ROW|ROWS}

[FETCH {FIRST|NEXT} количество_извлекаемых_строк {ROW|ROWS} ONLY]

Например, выберем все строки, начиная с третьей:

SELECT * FROM Products

ORDER BY Id

OFFSET 2 ROWS;

Число после ключевого слова OFFSET указывает, сколько строк необходимо пропустить.

Теперь выберем только три строки, начиная с третьей:

SELECT * FROM Products

ORDER BY Id

OFFSET 2 ROWS

FETCH NEXT 3 ROWS ONLY;

После оператора FETCH указывается ключевое слово FIRST или NEXT (какое именно в данном случае не имеет значения) и затем указывается количество строк, которое надо получить.

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

Фильтрация. WHERE

Для фильтрации в команде SELECT применяется оператор WHERE. После этого оператора ставится условие, которому должна соответствовать строка:

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

использовать операции сравнения. Эти операции сравнивают два выражения. В T-SQL можно применять следующие операции сравнения:

= сравнение на равенство (в отличие от си-подобных языков в T-SQL для сравнения на равенство используется один знак равно)

сравнение на неравенство

меньше чем

больше чем

! не меньше чем

! не больше чем

меньше чем или равно

= больше чем или равно

Например, найдем всех товары, производителем которых является компания Samsung:

SELECT * FROM Products

WHERE Manufacturer = 'Samsung'

Стоит отметить, что в данном случае регистр не имеет значение, и мы могли бы использовать для поиска и строку "Samsung", и "SAMSUNG", и "samsung". Все эти варианты давали бы

эквивалентный результат выборки.

Другой пример - найдем все товары, у которых цена больше 45000:

SELECT * FROM Products

WHERE Price 45000

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

SELECT * FROM Products

WHERE Price * ProductCount 200000

Логические операторы

Для объединения нескольких условий в одно могут использоваться логические операторы. В T-SQL имеются следующие логические операторы:

• AND: операция логического И. Она объединяет два выражения:

выражение1 AND выражение2

Только если оба этих выражения одновременно истинны, то и общее условие оператора AND также будет истинно. То есть если и первое условие истинно, и второе.

• OR: операция логического ИЛИ. Она также объединяет два выражения:

выражение1 OR выражение2

Если хотя бы одно из этих выражений истинно, то общее условие оператора OR также будет истинно. То есть если или первое условие истинно, или второе.

• NOT: операция логического отрицания. Если выражение в этой операции ложно, то общее условие истинно.

NO выражение

Если эти операторы встречаются в одном выражении, то сначала выполняется NOT, потом AND и в конце OR.

Например, выберем все товары, у которых производитель Samsung и одновременно цена больше 50000:

SELECT * FROM Products

WHERE Manufacturer = 'Samsung' AND Price 50000

Теперь изменим оператор на OR. То есть выберем все товары, у которых либо производитель Samsung, либо цена больше 50000:

SELECT * FROM Products

WHERE Manufacturer = 'Samsung' OR Price 50000

Применение оператора NOT - выберем все товары, у которых производитель не Samsung:

SELECT * FROM Products

WHERE NOT Manufacturer = 'Samsung'

Но в большинстве случае вполне можно обойтись без оператора NOT. Так, в предыдущий пример мы можем переписать следующим образом:

SELECT * FROM Products

WHERE Manufacturer 'Samsung'

Также в одной команде SELECT можно использовать сразу несколько операторов:

SELECT * FROM Products

WHERE Manufacturer = 'Samsung' OR Price 30000 AND ProductCount 2

Так как оператор AND имеет более высокий приоритет, то сначала будет выполняться

подвыражение Price 30000 AND ProductCount 2, и только потом оператор OR. То есть здесь выбираются товары, которыех на складе больше 2 и у которых одновременно цена больше 30000, либо те товары, производителем которых является Samsung.

С помощью скобок мы также можем переопределить порядок операций:

SELECT * FROM Products

WHERE (Manufacturer = 'Samsung' OR Price 30000) AND ProductCount 2

IS NULL

Ряд столбцов может допускать значение NULL. Это значение не эквивалентно пустой строке ''.

NULL представляет полное отсутствие какого-либо значения. И для проверки на наличие подобного значения применяется оператор IS NULL.

Например, выберем все товары, у которых не установлено поле ProductCount:

SELECT * FROM Products

WHERE ProductCount IS NULL

Если, наоборот, необходимо получить строки, у которых поле ProductCount не равно NULL, то можно использовать оператор NOT:

SELECT * FROM Products

WHERE ProductCount IS NOT NULL

Операторы фильтрации Оператор IN

Оператор IN позволяет определить набор значений, которые должны иметь столбцы:

WHERE выражение [NOT] IN (выражение)

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

Например, выберем товары, у которых производитель либо Samsung, либо Xiaomi, либо Huawei:

SELECT * FROM Products

WHERE Manufacturer IN ('Samsung', 'Xiaomi', 'Huawei')

Мы могли бы все эти значения проверить и через оператор OR:

SELECT * FROM Products

WHERE Manufacturer = 'Samsung' OR Manufacturer = 'Xiaomi' OR

Manufacturer = 'Huawei'

Но использование оператора IN гораздо удобнее, особенно если подобных значений очень много.

С помощью оператора NOT можно найти все строки, которые, наоборот, не соответствуют набору значений:

SELECT * FROM Products

WHERE Manufacturer NOT IN ('Samsung', 'Xiaomi', 'Huawei')

Оператор BETWEEN

Оператор BETWEEN определяет диапазон значений с помощью начального и конечного значения, которому должно соответствовать выражение:

WHERE выражение [NOT] BETWEEN начальное_значение AND конечное_значение

Например, получим все товары, у которых цена от 20 000 до 40 000 (начальное и конечное значения также включаются в диапазон):

SELECT * FROM Products

WHERE Price BETWEEN 20000 AND 40000

Если надо, наоборот, выбрать те строки, которые не попадают в данный диапазон, то применяется оператор NOT:

SELECT * FROM Products

WHERE Price NOT BETWEEN 20000 AND 40000

Также можно использовать более сложные выражения. Например, получим товары, запасы которых на определенную сумму (цена * количество):

SELECT * FROM Products

WHERE Price * ProductCount BETWEEN 100000 AND 200000

Оператор LIKE

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

WHERE выражение [NOT] LIKE шаблон_строки

Для определения шаблона могут применяться ряд специальных символов подстановки:

% соответствует любой подстроке, которая может иметь любое количество символов, при этом подстрока может и не содержать ни одного символа

_ соответствует любому одиночному символу

[ ] соответствует одному символу, который указан в квадратных скобках [ - ] соответствует одному символу из определенного диапазона

[ ^ ] соответствует одному символу, который не указан после символа ^ Некоторые примеры использования подстановок:

WHERE ProductName LIKE 'Galaxy%'

Соответствует таким значениям как "Galaxy Ace 2" или "Galaxy S7"

WHERE ProductName LIKE 'Galaxy S_'

Соответствует таким значениям как "Galaxy S7" или "Galaxy S8"

WHERE ProductName LIKE 'iPhone [78]'

Соответствует таким значениям как "iPhone 7" или "iPhone8"

WHERE ProductName LIKE 'iPhone [6-8]'

Соответствует таким значениям как "iPhone 6", "iPhone 7" или "iPhone8"

WHERE ProductName LIKE 'iPhone [^7]%'

Соответствует таким значениям как "iPhone 6", "iPhone 6S" или "iPhone8". Но не соответствует значениям "iPhone 7" и "iPhone 7S"

WHERE ProductName LIKE 'iPhone [^1-6]%'

Соответствует таким значениям как "iPhone 7", "iPhone 7S" и "iPhone 8". Но не соответствует значениям "iPhone 5", "iPhone 6" и "iPhone 6S"

Применим оператор LIKE:

SELECT * FROM Products

WHERE ProductName LIKE 'iPhone [6-8]%'

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

Агрегатные функции выполняют вычисления над значениями в наборе строк. В T-SQL имеются следующие агрегатные функции:

AVG: находит среднее значение SUM: находит сумму значений

MIN: находит наименьшее значение MAX: находит наибольшее значение

COUNT: находит количество строк в запросе

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

Выражения в функциях AVG и SUM должно представлять числовое значение. Выражение в функциях MIN, MAX и COUNT может представлять числовое или строковое значение или дату.

Все агрегатные функции за исключением COUNT(*) игнорируют значения NULL.

Avg

Функция Avg возвращает среднее значение на диапазоне значений столбца таблицы.

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

CREATE TABLE Products

(

Id INT IDENTITY PRIMARY KEY,

ProductName NVARCHAR(30) NOT NULL,

Manufacturer NVARCHAR(20) NOT NULL,

ProductCount INT DEFAULT 0, Price MONEY NOT NULL

);

INSERT INTO Products

VALUES

('iPhone 6', 'Apple', 3, 36000),

('iPhone 6S', 'Apple', 2, 41000),

('iPhone 7', 'Apple', 5, 52000),

('Galaxy S8', 'Samsung', 2, 46000),

('Galaxy S8 Plus', 'Samsung', 1, 56000),

('Mi6', 'Xiaomi', 5, 28000),

('OnePlus 5', 'OnePlus', 6, 38000)

Найдем среднюю цену товаров из базы данных:

SELECT AVG(Price) AS Average_Price FROM Products

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

Также мы можем применить фильтрацию. Например, найти среднюю цену для товаров какого-то определенного производителя:

SELECT AVG(Price) FROM Products

WHERE Manufacturer='Apple'

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

SELECT AVG(Price * ProductCount) FROM ProductsCount

Функция Count вычисляет количество строк в выборке. Есть две формы этой функции. Первая форма COUNT(*) подсчитывает число строк в выборке:

SELECT COUNT(*) FROM Products

Вторая форма функции вычисляет количество строк по определенному столбцу, при этом строки со значениями NULL игнорируются:

SELECT COUNT(Manufacturer) FROM Products

Min и Max

Функции Min и Max возвращают соответственно минимальное и максимальное значение по столбцу. Например, найдем минимальную цену среди товаров:

SELECT MIN(Price) FROM Products

Поиск максимальной цены:

SELECT MAX(Price) FROM Products

Данные функции также игнорируют значения NULL и не учитывают их при подсчете.

Sum

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

SELECT SUM(ProductCount) FROM Products

Также вместо имени столбца может передаваться вычисляемое выражение. Например, найдем общую стоимость всех имеющихся товаров:

SELECT SUM(ProductCount * Price) FROM Products

All и Distinct

По умолчанию все вышеперечисленных пять функций учитывают все строки выборки для вычисления результата. Но выборка может содержать повторяющие значения. Если необходимо выполнить вычисления только над уникальными значениями, исключив из набора значений повторяющиеся данные, то для этого применяется оператор DISTINCT.

SELECT AVG(DISTINCT ProductCount) AS Average_Price FROM Products

По умолчанию вместо DISTINCT применяется оператор ALL, который выбирает все строки:

SELECT AVG(ALL ProductCount) AS Average_Price FROM Products

Так как этот оператор неявно подразумевается при отсутствии DISTINCT, то его можно не указывать.

Комбинирование функций

Объединим применение нескольких функций:

SELECT COUNT(*) AS ProdCount, SUM(ProductCount) AS TotalCount, MIN(Price) AS MinPrice, MAX(Price) AS MaxPrice, AVG(Price) AS AvgPrice

FROM Products

Операторы GROUP BY и HAVING

Для группировки данных в T-SQL применяются операторы GROUP BY и HAVING, для использования которых применяется следующий формальный синтаксис:

SELECT столбцы

FROM таблица

[WHERE условие_фильтрации_строк] [GROUP BY столбцы_для_группировки] [HAVING условие_фильтрации_групп] [ORDER BY столбцы_для_сортировки]

GROUP BY

Оператор GROUP BY определяет, как строки будут группироваться. Например, сгруппируем товары по производителю

SELECT Manufacturer, COUNT(*) AS ModelsCount

FROM Products

GROUP BY Manufacturer

Первый столбец в выражении SELECT - Manufacturer представляет название группы, а второй столбец - ModelsCount представляет результат функции Count, которая вычисляет количество строк в группе.

Стоит учитывать, что любой столбец, который используется в выражении SELECT (не считая столбцов, которые хранят результат агрегатных функций), должны быть указаны после оператора GROUP BY. Так, например, в случае выше столбец Manufacturer указан и в выражении SELECT, и в выражении GROUP BY.

И если в выражении SELECT производится выборка по одному или нескольким столбцам и также используются агрегатные функции, то необходимо использовать выражение GROUP BY. Так, следующий пример работать не будет, так как он не содержит выражение группировки:

SELECT Manufacturer, COUNT(*) AS ModelsCount

FROM Products

Другой пример, добавим группировку по количеству товаров:

SELECT Manufacturer, ProductCount, COUNT(*) AS ModelsCount

FROM Products

GROUP BY Manufacturer, ProductCount

Оператор GROUP BY может выполнять группировку по множеству столбцов.

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

Следует учитывать, что выражение GROUP BY должно идти после выражения WHERE, но до выражения ORDER BY:

SELECT Manufacturer, COUNT(*) AS ModelsCount

FROM Products

WHERE Price 30000

GROUP BY Manufacturer

ORDER BY ModelsCount DESC

Фильтрация групп. HAVING

Оператор HAVING определяет, какие группы будут включены в выходной результат, то есть выполняет фильтрацию групп.

Применение HAVING во многом аналогично применению WHERE. Только есть WHERE применяется к фильтрации строк, то HAVING используется для фильтрации групп.

Например, найдем все группы товаров по производителям, для которых определено более 1 модели:

SELECT Manufacturer, COUNT(*) AS ModelsCount

FROM Products

GROUP BY Manufacturer

HAVING COUNT(*) 1

При этом в одной команде мы можем использовать выражения WHERE и HAVING:

SELECT Manufacturer, COUNT(*) AS ModelsCount

FROM Products

WHERE Price * ProductCount 80000

GROUP BY Manufacturer

HAVING COUNT(*) 1

То есть в данном случае сначала фильтруются строки: выбираются те товары, общая стоимость которых больше 80000. Затем выбранные товары группируются по производителям. И далее фильтруются сами группы - выбираются те группы, которые содержат больше 1 модели.

Если при этом необходимо провести сортировку, то выражение ORDER BY идет после выражения HAVING:

SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units

FROM Products

WHERE Price * ProductCount 80000

GROUP BY Manufacturer

HAVING SUM(ProductCount) 2

ORDER BY Units DESC

В данном случае группировка идет по производителям, и также выбирается количество моделей для каждого производителя (Models) и общее количество всех товаров по всем этим моделям (Units). В конце группы сортируются по количеству товаров по убыванию.

Расширения SQL Server для группировки

Дополнительно к стандартным операторам GROUP BY и HAVING SQL Server поддерживает еще четыре специальных расширения для группировки данных: ROLLUP, CUBE, GROUPING SETS и OVER.

ROLLUP

Оператор ROLLUP добавляет суммирующую строку в результирующий набор:

SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units

FROM Products

GROUP BY Manufacturer WITH ROLLUP

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

Альтернативный синтаксис запроса, который можно использовать, начиная с версии MS SQL Server 2008:

SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units

FROM Products

GROUP BY ROLLUP(Manufacturer)

При группировке по нескольким критериям ROLLUP будет создавать суммирующую строку для каждой из подгрупп:

SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units

FROM Products

GROUP BY Manufacturer, ProductCount WITH ROLLUP

При сортировке с помощью ORDER BY следует учитывать, что она применяется уже после добавления суммирующей строки.

CUBE

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

SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units

FROM Products

GROUP BY Manufacturer, ProductCount WITH CUBE

GROUPING SETS

Оператор GROUPING SETS аналогично ROLLUP и CUBE добавляет суммирующую строку для групп. Но при этом он не включает сами группам:

SELECT Manufacturer, COUNT(*) AS Models, ProductCount

FROM Products

GROUP BY GROUPING SETS(Manufacturer, ProductCount)

При этом его можно комбинировать с ROLLUP или CUBE. Например, кроме суммирующих строк по каждой из групп добавим суммирующую строку для всех групп:

SELECT Manufacturer, COUNT(*) AS Models, ProductCount, SUM(ProductCount) AS Units

FROM Products

GROUP BY GROUPING SETS(ROLLUP(Manufacturer), ProductCount)

С помощью скобок можно определить более сложные сценарии группировки:

SELECT Manufacturer, COUNT(*) AS Models, ProductCount, SUM(ProductCount) AS Units

FROM Products

GROUP BY GROUPING SETS((Manufacturer, ProductCount), ProductCount)

OVER

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

SELECT ProductName, Manufacturer, ProductCount,

COUNT(*) OVER (PARTITION BY Manufacturer) AS Models, SUM(ProductCount) OVER (PARTITION BY Manufacturer) AS Units

FROM Products

Выражение OVER ставится после агрегатной функции, затем в скобках идет выражение PARTITION BY и столбец, по которому выполняется группировка.

То есть в данном случае мы выбираем название модели, производителя, количество единиц модели и добавляем к этому количество моделей для данного производителя и общее количество единиц всех моделей производителя:

Оборудование, материалы

Персональный компьютер с установленной ОС Windows. Браузер с доступом в интернет. MS SQL Server. SQL Server Management Studio.

Порядок выполнения задания

1. Создайте простой запрос на выборку к таблице books, который выводит максимальную и минимальную цены товарных позиций, присваивая им соответственно псевдонимы maximum и minimum.

1. Создадим простой запрос на выборку к таблице books, который выводит количество записей, соответствующих каждому из уникальных значений b_cat_ID. Для этого

используем функцию count( ) вместе с выражением group by.

1. С помощью простого запроса получите все записи из всех полей таблицы users.

2. С помощью простого запроса отсортируйте таблицу users по полю u_name по возрастанию и получите первые три записи.

3. С помощью простого запроса отсортируйте таблицу users по полю u_name по возрастанию и получите последние три записи.

4. Создайте простой запрос на выборку к таблице books, который содержит поля b_name, b_year, b_price и b_count.

5. Создайте простой запрос на выборку к таблице books, который содержит поля b_name, b_year, b_price и b_count. Запрос должен вывести только книги из категории под номером три.

6. Создайте простой запрос на выборку к таблице books, который содержит поля b_name, b_year, b_price, b_count и поле b_summ, поле b_summ содержит суммарную стоимость всех экземпляров данной книги, его значение является произведением значений полей b_price и b_count.

7. Создайте запрос с использованием агрегатных функций к таблице books, который содержит поля b_cat_ID и b_cat_summ, которое содержит суммарную стоимость всех книг в данной категории.

8. Создайте запрос с использованием агрегатных функций к таблице books, который содержит поля b_cat_ID и b_cat_summ, поле b_cat_summ содержит суммарную стоимость всех книг в данной категории. Выборка должна быть отсортирована по возрастанию по полю b_cat_summ и содержать только значения более 10 000.

Контрольные вопросы:

1. Каково назначение команды SELECT в SQL?

2. Как выбрать все столбцы из таблицы с использованием команды SELECT?