© 2025, Ахматов Максим Сергеевич 25 0
СДЕЛАЙТЕ СВОИ УРОКИ ЕЩЁ ЭФФЕКТИВНЕЕ, А ЖИЗНЬ СВОБОДНЕЕ
Благодаря готовым учебным материалам для работы в классе и дистанционно
Скидки до 50 % на комплекты
только до
Готовые ключевые этапы урока всегда будут у вас под рукой
Организационный момент
Проверка знаний
Объяснение материала
Закрепление изученного
Итоги урока
Был в сети 01.07.2025 15:42
Ахматов Максим Сергеевич
Преподаватель профессиональных дисциплин
3 247
18 475 
Управление и автоматизация БД Практическая работа №12
Категория:
Информатика
29.06.2025 19:05
Просмотр содержимого документа
«Управление и автоматизация БД Практическая работа №12»
Практическое занятие № 12
Создание сложных запросов на выборку
Цель работы: получение навыков создания сложных запросов в среде MS SQL Server.
Краткие теоретические основания выполнения задания Выполнение подзапросовT-SQL поддерживает функциональность подзапросов (subquery), то есть таких запросов, которые могут встроены в другие запросы.
Например, создадим таблицы для товаров, покупателей и заказов:
USE productsdb; 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
);
CREATE TABLE Customers
(
Id INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(30) NOT NULL
);
CREATE TABLE Orders
(
Id INT IDENTITY PRIMARY KEY,
ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE,
CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE,
CreatedAt DATE NOT NULL, ProductCount INT DEFAULT 1, Price MONEY NOT NULL
);
Таблица Orders содержит ссылки на две другие таблицы через поля ProductId и CustomerId. Добавим в таблицы некоторые данные:
INSERT INTO Products
VALUES ('iPhone 6', 'Apple', 2, 36000),
('iPhone 6S', 'Apple', 2, 41000),
('iPhone 7', 'Apple', 5, 52000),
('Galaxy S8', 'Samsung', 2, 46000),
('Galaxy S8 Plus', 'Samsung', 1, 56000),
('Mi 5X', 'Xiaomi', 2, 26000),
('OnePlus 5', 'OnePlus', 6, 38000)
INSERT INTO Customers VALUES ('Tom'), ('Bob'),('Sam')
INSERT INTO Orders
VALUES (
(SELECT Id FROM Products WHERE ProductName='Galaxy S8'), (SELECT Id FROM Customers WHERE FirstName='Tom'),
'2017-07-11', 2,
(SELECT Price FROM Products WHERE ProductName='Galaxy S8')
), (
(SELECT Id FROM Products WHERE ProductName='iPhone 6S'), (SELECT Id FROM Customers WHERE FirstName='Tom'),
'2017-07-13', 1,
(SELECT Price FROM Products WHERE ProductName='iPhone 6S')
), (
(SELECT Id FROM Products WHERE ProductName='iPhone 6S'), (SELECT Id FROM Customers WHERE FirstName='Bob'),
'2017-07-11', 1,
(SELECT Price FROM Products WHERE ProductName='iPhone 6S')
)
Здесь интерес представляет добавление элементов в таблицу Orders. Например, первый заказ был сделан покупателем Tom на товар Galaxy S8. Соответственно в таблицу Orders нам надо сохранить информацию о заказе, где поле ProductId указывает на Id товара Galaxy S8, поле Price - на его цену, а поле CustomerId - на Id покупателя Tom. Но на момент написания запроса нам может быть неизвестен ни Id покупателя, ни Id товара, ни цена товара. В этом случае можно выполнить подзапрос.
Подзапрос выполняет команду SELECT и заключается в скобки. В данном же случае при добавлении одного товара выполняется три подзапроса. Каждый подзапрос возвращает одного скалярное значение, например, числовой идентификатор.
В данном случае подзапросы выполнялись к другой таблице, но могут выполняться и к той же, к которой вызывается основной запрос. Например, найдем товары из таблицы Products, которые имеют минимальную цену:
SELECT *
FROM Products
WHERE Price = (SELECT MIN(Price) FROM Products)
Или найдем товары, цена которых выше средней:
SELECT *
FROM Products
WHERE Price (SELECT AVG(Price) FROM Products)
Подзапросы бывают коррелирующими и некоррелирующими. В примерах выше команды SELECT выполняли фактически один подзапрос для всей команды, например, подзапрос возвращает минимальную или среднюю цену, которая не изменится, сколько бы мы строк не выбирали в
основном запросе. То есть результат подзапроса не зависел от строк, которые выбираются в основном запросе. И такой подзапрос выполняется один раз для всего внешнего запроса.
Но также существуют коррелирующие подзапросы (correlated subquery), результаты которых зависят от строк, которые выбираются в основном запросе.
Например, выберем все заказы из таблицы Orders, добавив к ним информацию о товаре:
SELECT CreatedAt,
Price,
(SELECT ProductName FROM Products
WHERE Products.Id = Orders.ProductId) AS Product
FROM Orders
Здесь для каждой строки из таблицы Orders будет выполняться подзапрос, результат которого зависит от столбца ProductId. И каждый подзапрос может возвращать различные данные.
Коррелирующий подзапрос может выполняться и для той же таблицы, к которой выполняется основной запрос. Например, выберем из таблицы Products те товары, стоимость которых выше средней цены товаров для данного производителя:
SELECT ProductName,
Manufacturer, Price,
(SELECT AVG(Price) FROM Products AS SubProds
WHERE SubProds.Manufacturer=Prods.Manufacturer) AS AvgPrice
FROM Products AS Prods
WHERE Price
(SELECT AVG(Price) FROM Products AS SubProds
WHERE SubProds.Manufacturer=Prods.Manufacturer)
В данном случае определено два коррелирующих подзапроса. Первый подзапрос определяет спецификацию столбца AvgPrice. Он будет выполняться для каждой строки, извлекаемой из таблицы Products. В подзапрос передается производитель товара и на его основе выбирается средняя цена для товаров именно этого производителя. И так как производитель у товаров может отличаться, то и результат подзапроса в каждом случае также может отличаться.
Второй подзапрос аналогичен, только он используется для фильтрации извлекаемых из таблицы Products. И также он будет выполняться для каждой строки.
Чтобы избежать двойственности при фильтрации в подзапросе при сравнении производителей (SubProds.Manufacturer=Prods.Manufacturer) для внешней выборки установлен псевдоним Prods, а для выборки из подзапросов определен псевдоним SubProds.
Следует учитывать, что коррелирующие подзапросы выполняются для каждой отдельной строки выборки, то выполнение таких подзапросов может замедлять выполнение всего запроса в целом.
Подзапросы в основных командах SQL Подзапросы в SELECTВ выражении SELECT мы можем вводить подзапросы четырьмя способами:
Использовать в условии в выражении WHERE
Использовать в условии в выражении HAVING
Использовать в качестве таблицы для выборки в выражении FROM
Использовать в качестве спецификации столбца в выражении SELECT
Рассмотрим некоторые из этих случаев. Например, получим все товары, у которых цена выше средней:
SELECT *
FROM Products
WHERE Price (SELECT AVG(Price) FROM Products)
Чтобы получить нужные товары, нам вначале надо выполнить подзапрос на получение средней цены товара: SELECT AVG(Price) FROM Products.
Или выберем всех покупателей из таблицы Customers, у которых нет заказов в таблице Orders:
SELECT * FROM CUSTOMERS
WHERE Id NOT IN (SELECT CustomerId FROM Orders)
Хотя в данном случае подзапросы прекрасно справляются со своей задачей, стоит отметить, что это не самый эффективный способ для извлечения данных из других таблиц, так как в рамках T- SQL для сведения данных из разных таблиц можно использовать оператор JOIN, который рассматривается в следующей теме.
Получение набора значенийПри использовании в операторах сравнения подзапросы должны возвращать одно скалярное значение. Но иногда возникает необходимость получить набор значений. Чтобы при использовании в операторах сравнения подзапрос мог возвращать набор значений, перед ним необходимо использовать один из операторов: ALL, SOME или ANY.
При использовании ключевого слова ALL условие в операции сравнения должно быть верно для всех значений, которые возвращаются подзапросом. Например, найдем все товары, цена которых меньше чем у любого товара фирмы Apple:
SELECT * FROM Products
WHERE Price ALL(SELECT Price FROM Products WHERE Manufacturer='Apple')
Если бы мы в данном случае опустили бы ключевое слово ALL, то мы бы столкнулись с ошибкой.
Допустим, если подзапрос возвращает значения vl1, val2 и val3, то условие фильтрации фактически было бы аналогично объединению этих значений через оператор AND:
WHERE Price val1 AND Price val2 AND Price val3
В тоже время подобный запрос гораздо проще переписать другим образом:
SELECT * FROM Products
WHERE Price (SELECT MIN(Price) FROM Products WHERE Manufacturer='Apple')
При применении ключевых слов ANY и SOME условие в операции сравнения должно быть истинным для хотя бы одного из значений, возвращаемых подзапросом. По действию оба этих оператора аналогичны, поэтому можно применять любое из них. Например, в следующем случае получим товары, которые стоят меньше самого дорого товара компании Apple:
SELECT * FROM Products
WHERE Price ANY(SELECT Price FROM Products WHERE Manufacturer='Apple')
И также стоит отметить, что данный запрос можно сделать проще, переписав следующим образом:
SELECT * FROM Products
WHERE Price (SELECT MAX(Price) FROM Products WHERE Manufacturer='Apple')
Подзапрос как спецификация столбцаРезультат подзапроса может представлять отдельный столбец в выборке. Например, выберем все заказы и добавим к ним информацию о названии товара:
SELECT *,
(SELECT ProductName FROM Products WHERE Id=Orders.ProductId) AS Product
FROM Orders
В команде INSERT подзапросы могут применяться для определения значения, которое вставляется в один из столбцов:
INSERT INTO Orders (ProductId, CustomerId, CreatedAt, ProductCount, Price) VALUES
(
(SELECT Id FROM Products WHERE ProductName='Galaxy S8'), (SELECT Id FROM Customers WHERE FirstName='Tom'),
'2017-07-11',
2,
(SELECT Price FROM Products WHERE ProductName='Galaxy S8')
)
Подзапросы в команде UPDATEВ команде UPDATE подзапросы могут применяться:
В качестве устанавливаемого значения после оператора SET
Как часть условия в выражении WHERE
Так, увеличим количество купленных товаров на 2 в тех заказах, где покупатель Тоm:
UPDATE Orders
SET ProductCount = ProductCount + 2
WHERE CustomerId=(SELECT Id FROM Customers WHERE FirstName='Tom')
Или установим для заказа цену товара, полученную в результате подзапроса:
UPDATE Orders
SET ProductCount = ProductCount + 2
WHERE CustomerId=(SELECT Id FROM Customers WHERE FirstName='Tom')
Подзапросы в команде DELETEВ команде DELETE подзапросы также применяются как часть условия. Так, удалим все заказы на Galaxy S8, которые сделал Bob:
DELETE FROM Orders
WHERE ProductId=(SELECT Id FROM Products WHERE ProductName='Galaxy S8') AND CustomerId=(SELECT Id FROM Customers WHERE FirstName='Bob')
Оператор EXISTSОператор EXISTS позволяет проверить, возвращает ли подзапрос какое-либо значение. Как правило, этот оператор используется для индикации того, что какая-либо строка удовлетворяет условию. То есть фактически оператор EXISTS не возвращает строки, а лишь указывает, что в базе данных есть как минимум одна строка, которые соответствует данному запросу. Поскольку возвращения набора строк не происходит, то подзапросы с подобным оператором выполняются довольно быстро.
Применение оператора имеет следующий формальный синтаксис:
WHERE [NOT] EXISTS (подзапрос)
Например, найдем всех покупателей из таблицы Customer, которые делали заказы:
SELECT *
FROM Customers
WHERE EXISTS (SELECT * FROM Orders
WHERE Orders.CustomerId = Customers.Id)
Другой пример - найдем все товары из таблицы Products, на которые не было заказов в таблице Orders:
SELECT *
FROM Products
WHERE NOT EXISTS (SELECT * FROM Orders WHERE Products.Id = Orders.ProductId)
Стоит отметить, что для получения подобного результата ы могли бы использовать и оператор IN:
SELECT *
FROM Products
WHERE Id NOT IN (SELECT ProductId FROM Orders)
Но поскольку при применении EXISTS не происходит выборка строк, то его использование более оптимально и эффективно, чем использование оператора IN.
UNIONОператор UNION подобно inner join или outer join позволяет соединить две таблицы. Но в отличие от inner/outer join объединения соединяют не столбцы разных таблиц, а два однотипных набора в один. Формальный синтаксис объединения:
SELECT_выражение1
UNION [ALL] SELECT_выражение2
[UNION [ALL] SELECT_выражениеN]
Например, пусть в базе данных будут две отдельные таблицы для клиентов банка (таблица Customers) и для сотрудников банка (таблица Employees):
USE usersdb;
CREATE TABLE Customers
(
Id INT IDENTITY PRIMARY KEY, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL,
AccountSum MONEY
);
CREATE TABLE Employees
(
Id INT IDENTITY PRIMARY KEY, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL,
);
INSERT INTO Customers VALUES ('Tom', 'Smith', 2000),
('Sam', 'Brown', 3000),
('Mark', 'Adams', 2500),
('Paul', 'Ins', 4200),
('John', 'Smith', 2800),
('Tim', 'Cook', 2800)
INSERT INTO Employees VALUES ('Homer', 'Simpson'),
('Tom', 'Smith'),
('Mark', 'Adams'),
('Nick', 'Svensson')
Здесь мы можем заметить, что обе таблицы, несмотря на наличие различных данных, могут характеризоваться двумя общими атрибутами - именем (FirstName) и фамилией (LastName). Выберем сразу всех клиентов банка и его сотрудников из обеих таблиц:
SELECT FirstName, LastName
FROM Customers
UNION SELECT FirstName, LastName FROM Employees
В данном случае из первой таблицы выбираются два значения - имя и фамилия клиента. Из второй таблицы Employees также выбираются два значения - имя и фамилия сотрудников. То есть при объединении количество выбираемых столбцов и их тип совпадают для обеих выборок.
При этом названия столбцов объединенной выборки будут совпадать с названия столбцов первой выборки. И если мы захотим при этом еще произвести сортировку, то в выражениях ORDER BY необходимо ориентироваться именно на названия столбцов первой выборки:
SELECT FirstName + ' ' +LastName AS FullName
FROM Customers
UNION SELECT FirstName + ' ' + LastName AS EmployeeName
FROM Employees
ORDER BY FullName DESC
В данном случае каждая выборка имеет по одному столбцу, который представляет объединение имени и фамилии клиента или сотрудника. Но в случае с клиентами столбец будет называться FullName, а в случае с сотрудниками - EmployeeName. Тем не менее для сортировки применяется название столбца из первой выборки, и он же будет в результирующей выборке:
Если же в одной выборке больше столбцов, чем в другой, то они не смогут быть объединены. Например, в следующем случае объединение завершится с ошибкой:
SELECT FirstName, LastName, AccountSum
FROM Customers
UNION SELECT FirstName, LastName
FROM Employees
Также соответствующие столбцы должны соответствовать по типу. Так, следующий пример завершится с ошибкой из-за не соответствия по типу данных:
SELECT FirstName, LastName
FROM Customers
UNION SELECT Id, LastName
FROM Employees
В данном случае первый столбец первой выборки имеет тип NVARCHAR, то есть хранит строку. Первый столбец второй выборки - Id имеет тип INT, то есть хранит число.
Если оба объединяемых набора содержат в строках идентичные значения, то при объединении повторяющиеся строки удаляются. Например, в случае с таблицами Customers и Employees сотрудники банка могут быть одновременно его клиентами и содержаться в обеих таблицах. При объединении в примерах выше всех дублирующиеся строки удалялись. Если же необходимо при объединении сохранить все, в том числе повторяющиеся строки, то для этого необходимо использовать оператор ALL:
SELECT FirstName, LastName
FROM Customers
UNION ALL SELECT FirstName, LastName
FROM Employees
Объединять выборки можно и из одной и той же таблицы. Например, в зависимости от суммы на счете клиента нам надо начислять ему определенные проценты:
SELECT FirstName, LastName, AccountSum + AccountSum * 0.1 AS TotalSum
FROM Customers WHERE AccountSum 3000
UNION SELECT FirstName, LastName, AccountSum + AccountSum * 0.3 AS TotalSum
FROM Customers WHERE AccountSum = 3000
В данном случае если сумма меньше 3000, то начисляются проценты в размере 10% от суммы на счете. Если на счете больше 3000, то проценты увеличиваются до 30%.
Оператор EXCEPT позволяет найти разность двух выборок, то есть те строки, которые есть в первой выборке, но которых нет во второй. Для его использования применяется следующий формальный синтаксис:
SELECT_выражение1
EXCEPT SELECT_выражение2
Для примера возьмем таблицы из прошлой темы:
USE usersdb;
CREATE TABLE Customers
(
Id INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
AccountSum MONEY
);
CREATE TABLE Employees
(
Id INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
);
INSERT INTO Customers VALUES ('Tom', 'Smith', 2000),
('Sam', 'Brown', 3000),
('Mark', 'Adams', 2500),
('Paul', 'Ins', 4200),
('John', 'Smith', 2800),
('Tim', 'Cook', 2800)
INSERT INTO Employees VALUES ('Homer', 'Simpson'),
('Tom', 'Smith'),
('Mark', 'Adams'),
('Nick', 'Svensson')
Таблица Employees содержит данные обо всех сотрудниках банка, а таблица Customers - обо всех клиентах. Но сотрудники банка могут также быть его клиентами. И допустим, нам надо найти всех клиентов банка, которые не являются его сотрудниками:
SELECT FirstName, LastName
FROM Customers
EXCEPT SELECT FirstName, LastName
FROM Employees
Подобным образом можно получить всех сотрудников банка, которые не являются его клиентами:
SELECT FirstName, LastName
FROM Employees
EXCEPT SELECT FirstName, LastName
FROM Customers
Оператор INTERSECT позволяет найти общие строки для двух выборок, то есть данный оператор выполняет операцию пересечения множеств. Для его использования применяется следующий формальный синтаксис:
SELECT выражение1
INTERSECT SELECT выражение2
Для примера возьмем таблицы из прошлой темы:
USE usersdb;
CREATE TABLE Customers (
Id INT IDENTITY PRIMARY KEY, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL,
AccountSum MONEY
);
CREATE TABLE Employees (
Id INT IDENTITY PRIMARY KEY, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL,
);
INSERT INTO Customers VALUES ('Tom', 'Smith', 2000),
('Sam', 'Brown', 3000),
('Mark', 'Adams', 2500),
('Paul', 'Ins', 4200),
('John', 'Smith', 2800),
('Tim', 'Cook', 2800)
INSERT INTO Employees VALUES ('Homer', 'Simpson'),
('Tom', 'Smith'),
('Mark', 'Adams'),
('Nick', 'Svensson')
В таблице Customers хранятся все клиенты банка, а в таблице Employees - все его сотрудники. Но сотрудники могут быть одновременно и клиентами банка, поэтому их данные могут храниться сразу в двух таблицах. Найдем всех сотрудников банка, которые одновременно являются его клиентами. То есть нам надо найти общие элементы двух выборок:
SELECT FirstName, LastName FROM Employees
INTERSECT SELECT FirstName, LastName FROM Customers
Персональный компьютер с установленной ОС Windows. Браузер с доступом в интернет. MS SQL Server. SQL Server Management Studio.
Порядок выполнения заданияСоздайте запрос на выборку с вложенным запросом, выводящим перечень книг, которые не заказывались покупателями:
Создайте запрос на выборку с вложенным запросом, выводящим список клиентов, которые не заказали ни одной книги.
С использованием подзапроса, выберите все заказы и добавим к ним информацию о названии каталога.
Контрольные вопросы:
1. Что такое подзапрос? Где он может быть использован в SQL-запросе?
2. Какие типы подзапросов существуют?
Вебинар для учителей
Свидетельство об участии БЕСПЛАТНО!
