-
Union Для объединения запросов используется служебное слово UNION:
Найти номера моделей и цены ПК и портативных компьютеров:
SELECT model, price
FROM PC
UNION
SELECT model, price
FROM Laptop
ORDER BY price DESC;
Предложение UNION приводит к появлению в результирующем наборе всех строк каждого из запросов. При этом, если определен параметр ALL, то сохраняются все дубликаты выходных строк, в противном случае в результирующем наборе присутствуют только уникальные строки. Заметим, что можно связывать вместе любое число запросов. Кроме того, с помощью скобок можно задавать порядок объединения.
Операция объединения может быть выполнена только при выполнении следующих условий:
-
количество выходных столбцов каждого из запросов должно быть одинаковым;
-
выходные столбцы каждого из запросов должны быть совместимы между собой (в порядке их следования) по типам данных;
-
в результирующем наборе используются имена столбцов, заданные в первом запросе;
-
предложение ORDER BY применяется к результату соединения, поэтому оно может быть указано только в конце всего составного запроса.
-
INTERSECT В результирующий набор попадают только те строки, которые присутствуют в обоих запросах
Select distinct maker from Product inner join PC on Product.model=PC.model
where PC.speed>=750
INTERSECT
Select distinct maker from Product inner join Laptop on Product.model=Laptop.model
where Laptop.speed>=750
-
EXCEPT В результирующий набор попадают только те строки первого запроса, которые отсутствуют во втором
Задание: 8 Найдите производителя, выпускающего ПК, но не ПК-блокноты.
Select Distinct maker
from Product
where type='PC'
Except
Select distinct maker
from Product
where type='Laptop'
Select distinct maker
from Product
where type='PC' and not exists
(select maker from Product Pr
where Pr.type='Laptop' and Pr.maker=Product.maker)
Следует сказать, что не все СУБД поддерживают эти предложения в операторе SELECT. Нет поддержки INTERSECT/EXCEPT, например, в MySQL, а в MS SQL Server она появилась, лишь начиная с версии 2005, и то без ключевого слова ALL. ALL с INTERSECT/EXCEPT также еще не реализована в Oracle.
Поэтому для выполнения операций пересечения и разности могут быть использованы другие средства. Здесь уместно заметить, что один и тот же результат можно получить с помощью различных формулировок оператора SELECT. В случае пересечения и разности можно воспользоваться предикатом существования EXISTS.
10 Работа Запросы с подзапросами
SQL позволяет использовать одни подзапросы внутри других подзапросов, то есть вкладывать запросы друг в друга.
Модернизируем запрос 93.
93) Найдите среднюю скорость пк, выпущенных производителем а
SELECT AVG(speed) AS AVG_SPEED_from_A
FROM PC INNER JOIN Product ON Product.model=PC.model
WHERE Product.maker='A';
|
|
|
|
||||||||||||||||||||||||||||||
93_105) Select avg(speed) as avg_speed
FROM PC
WHERE model IN (SELECT model FROM Product
WHERE maker='A');
|
|
|
|
||||||||||||||||||||||||||||||
107) Задание: 9 Найдите производителей ПК с процессором не менее 450 Мгц. Вывести: Maker
Без подзапроса
SELECT DISTINCT maker
FROM Product INNER JOIN PC ON Product.model=PC.model
WHERE PC.speed>450;
С подзапросом
SELECT DISTINCT Maker FROM PRODUCT WHERE TYPE = 'PC' AND MODEL IN
(SELECT MODEL FROM PC WHERE SPEED > = 450)
Рассмотрим заранее неправильный запрос:
Найти модели и цены ПК стоимость которых совпадает со стоимость ноутбука.
SELECT DISTINCT model, price
FROM PC
WHERE price=(select price from Laptop);
Ошибка. Подзапрос возвращает больше одного значения, либо ни одного и с ними идёт сравнение.
110) Найти модели и цены ПК¸ стоимость которых больше чем минимальная стоимость ноутбуков.
SELECT DISTINCT model, price
FROM PC
WHERE PC.price>(select min(price) from Laptop);
Корректен так как скалярное значение price сравнивается с подзапросом, который возвращает единственное значение.
106) Задание: 10 Найдите принтеры, имеющие самую высокую цену. Вывести: model, price
Select model, price
from printer
where price= (select max(price) from printer)
108) Найти ноутбуки, скорость которых меньше скорости любого из ПК. Вывести model, speed
SELECT Laptop.model, Laptop.speed
FROM Laptop
WHERE laptop.speed <(select min(speed)from PC);
109) Найти имеющееся в наличии количество компьютеров, выпущенных производителем А.
SELECT COUNT(*) AS Qty
FROM PC
WHERE model IN(SELECT model
FROM Product
WHERE maker = 'A' );
Задание: 18 Найти производителей самых дешёвых принтеров: Вывести: maker, price
Select distinct Product.maker, Printer.price
from Product inner join Printer
on Product.model=printer.model where Printer.color='y' and Printer.Price=(select min(price) from printer where Printer.color='y')
Подзапросы, возвращающие множество строк и содержащие несколько столбцов, вполне естественно могут использоваться в предложении From. Это позволяет ограничить набор столбцов и/или строк при выполнении операции соединения таблиц.
SELECT DISTINCT PC.model, maker
FROM PC inner join (SELECT maker, model FROM Product ) AS Prod
On PC.model = Prod.model WHERE PC.price < 600;
111) Вывести производителя(maker), тип (Type)¸модель (model) и частоту процессора(speed) для ноутбуков, частота которых превышает 600 МГц.
SELECT prod.maker, lap.*
FROM (select 'Laptop' as type, model, speed
from Laptop
where Laptop.speed>600) AS lap INNER JOIN (select maker, model from Product) AS prod ON lap.model=prod.model;