Примеры написания запросов

Примеры

Пример 1. Создать представление с именем MA_PROJ на таблице PROJECT, содержащее только те строки, в которых номер проекта (PROJNO) начинается буквами 'MA'.

CREATE VIEW MA_PROJ

SELECT * FROM PROJECT

WHERE SUBSTR( ProjNo, 1, 2 ) = ’MA’

Пример 2. Создать представление, как и в примере 1, но включить в него только колонки для номера проекта (ProjNo), имени проекта (ProjName) и сотрудника, ответственного за проект (RespEmp).

CREATE VIEW MA_PROJ

SELECT ProjNo, ProjName, RespEmp

FROM PROJECT

WHERE ProjNo LIKE ’MA%’

Пример 3. Создать представление, как и в примере 2, но в представлении назвать колонку сотрудника, отвечающего за проект, как IN_CHARGE.

CREATE VIEW MA_PROJ ( PROJNO, PROJNAME, IN_CHARGE )

SELECT PROJNO, PROJNAME, RESPEMP

FROM PROJECT

WHERE SUBSTR( PROJNO, 1, 2 ) = ’MA’

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

Это представление может быть создано и следующим образом:

CREATE VIEW MA_PROJ

SELECT PROJNO, PROJNAME, RESPEMP AS IN_CHARGE

FROM PROJECT

WHERE SUBSTR( PROJNO, 1, 2 ) = ’MA’

Пример 4. Создать представление с именем PRJ_LEADER, которое содержит первые четыре колонки (PROJNO, PROJNAME, DEPTNO, RESPEMP) из таблицы PROJECT и фамилию (LASTNAME) сотрудника, отвечающего за проект (RESPEMP). Фамилию сотрудника можно получить из таблицы EMPLOYEE, используя соответствующие значения номера сотрудника EMPNO из таблицы EMPLOYEE и номера сотрудника RESPEMP из таблицы PROJECT.

CREATE VIEW PRJ_LEADER

SELECT PROJNO, PROJNAME, DEPTNO, RESPEMP, LASTNAME

FROM PROJECT, EMPLOYEE

WHERE RESPEMP = EMPNO

Пример 5. Создать представление, как и в примере 4, но добавить к колонкам PROJNO, PROJNAME, DEPTNO, RESPEMP и LASTNAME величину общих выплат (SALARY + BONUS + COMM) для сотрудника, ответственного за проект. Кроме того, выбрать только те проекты, для которых значение штата сотрудников (PRSTAFF) больше единицы.

CREATE VIEW PRJ_LEADER ( PROJNO, PROJNAME, DEPTNO, RESPEMP, LASTNAME,

TOTAL_PAY )

SELECT PROJNO, PROJNAME, DEPTNO, RESPEMP, LASTNAME, SALARY+BONUS+COMM

FROM PROJECT, EMPLOYEE

WHERE RESPEMP = EMPNO AND PRSTAFF > 1

Список имен колонок представления может быть получен из списка имен колонок результирующей таблицы запроса, если в списке вывода SELECT для выражения SALARY+BONUS+COMM указать имя TOTAL_PAY.

CREATE VIEW PRJ_LEADER

SELECT PROJNO, PROJNAME, DEPTNO, RESPEMP, LASTNAME,

SALARY+BONUS+COMM AS TOTAL_PAY

FROM PROJECT, EMPLOYEE

WHERE RESPEMP = EMPNO AND PRSTAFF > 1

Простые запросы

Информация обо всех сотрудниках, работающих на предприятии, хранится в таблице EMPL, имеющей следующую структуру:

Название колонки	Атрибуты колонки	Пояснения
Empl_ID	int not null Primary Key Identity	Табельный номер сотрудника
LastName	varchar(20) not null	Фамилия сотрудника
FirstName	varchar(20) not null	Имя и отчество сотрудника
BDate	date	Дата рождения
HDate	date not null	Дата приема на работу
Sal	decimal(6,0) not null	Оклад сотрудника
Comm	decimal(6,0)	Комиссионные
Bonus	decimal(6,0)	Премия
DeptNo	char(3) not null	Номер отдела, в котором работает сотрудник

Написать следующие запросы.

A. Получение всей информации из таблицы

1. Получить информацию обо всех сотрудниках, работающих на предприятии.

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

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

SELECT * FROM EMPL

SELECT Empl_Id, LastName, FirstName, BDate, HDate,

Sal, Comm, Bonus, DeptNo

FROM EMPL

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

б) Если необходимо, например, для колонок Comm и Bonus вместо пустых значений вывести в отчете 0, можно использовать для этих целей вызов функции Coalesce() и переименование колонок результирующей таблицы:

SELECT Empl_Id, LastName, FirstName, BDate, HDate, Sal,

Coalesce(Comm,0) AS Comm, Coalesce(Bonus,0) AS Bonus, DeptNo

FROM EMPL

1. Получить информацию обо всех сотрудниках предприятия, упорядочив ее сначала по номерам отделов, и в каждом отделе – по фамилиям сотрудников (по алфавиту).

SELECT * FROM EMPL

ORDER BY DeptNo, LastName

3. Получить информацию о первых десяти сотрудниках предприятия, имеющих наибольшие оклады.

SELECT * FROM EMPL

ORDER BY Sal DESC

FETCH FIRST 10 ROWS ONLY

B. Выделение отдельных колонок (реализация операции проекции реляционной алгебры)

1. Получить упорядоченные по алфавиту номера отделов, в которых работают сотрудники.

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

SELECT DeptNo FROM EMPL ORDER BY DeptNo

Однако, поскольку в одном отделе работает много сотрудников, полученный отчет будет содержать много строк-дубликатов. Чтобы удалить их, следует в конструкции SELECT использовать ключевое слово DISTINCT. Кроме того, вместо имени колонки в конструкции ORDER BY можно использовать ее порядковый номер.

SELECT DISTINCT DeptNo FROM EMPL ORDER BY 1

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

SELECT DISTINCT DeptNo AS "Номер отдела" FROM EMPL ORDER BY 1

или

SELECT DISTINCT DeptNo AS "Номер отдела" FROM EMPL ORDER BY "Номер отдела"

2. Получить фамилию, имя и отчество всех сотрудников, работающих на предприятии, и конкретные и суммарные выплаты для каждого сотрудника.

Конкретные выплаты для каждого сотрудника отражаются в колонках Sal, Comm и Bonus. Суммарные выплаты для сотрудника определяются как сумма конкретных выплат: Sal + Comm + Bonus.

SELECT LastName, FirstName, Sal, Comm, Bonus,

Sal + Comm + Bonus AS “Common pay” FROM EMPL

1. В результирующей таблице, полученной в предыдущем примере, несмотря на то, что в колонке Sal для всех сотрудников представлено обязательно какое-то значение, в колонке суммарных выплат для сотрудников появятся неопределенные значения, если для сотрудника не определены значения колонок Comm и/или Bonus. Для того чтобы неопределенные значения колонок Comm и/или Bonus не искажали значения суммарных выплат, следует использовать функцию Coalesce():

SELECT LastName, FirstName, Sal,

Coalesce(Comm,0) AS Comm, Coalesce(Bonus,0) AS Bonus,

Sal + Coalesce(Comm, 0) + Coalesce(Bonus, 0) AS "Common pay"

FROM EMPL

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

SELECT Empl_Id, LastName, FirstName, Sal,

Coalesce(Comm,0) AS Comm, Coalesce(Bonus,0) AS Bonus,

Sal + Coalesce(Comm, 0) + Coalesce(Bonus, 0) AS "Common pay"

FROM EMPL

5. В результирующей таблице, полученной в предыдущем примере, фамилия (LastName) и имя и отчество (FirstName) сотрудника будут находиться в двух колонках. Если нужно эти данные представить в одной колонке, следует использовать операцию конкатенации. При этом следует учесть, что, так как колонки LastName и FirstName объявлены как varchar, никаких лишних пробелов в записи строк не будет, и следует добавить символ пробела между значениями LastName и FirstName.

SELECT Empl_Id, LastName || ' ' || FirstName AS "Фамилия И.О.", Sal,

Coalesce(Comm,0) AS Comm, Coalesce(Bonus,0) AS Bonus,

Sal + Coalesce(Comm, 0) + Coalesce(Bonus, 0) AS "Common pay"

FROM EMPL

6. Получить табельный номер, фамилию, имя, отчество и суммарные выплаты для первых 10 сотрудников, имеющих наибольшие суммарные выплаты. Фамилию, имя и отчество представить одной колонкой результирующей таблицы.

SELECT Empl_Id, LastName || ' ' || FirstName AS "Фамилия И.О.",

Sal + Coalesce(Comm, 0) + Coalesce(Bonus, 0) AS "Common pay"

FROM EMPL

ORDER BY 3 DESC

FETCH FIRST 10 ROWS ONLY

C. Выделение отдельных строк (реализация операции селекции реляционной алгебры)

1. Получить полную информацию обо всех сотрудниках, работающих в отделе с номером A00.

В данном запросе нужно из всех строк таблицы Empl выбрать те, в которых в колонке DeptNo записан текст A00. Такой отбор строк реализуется с помощью конструкции WHERE, в которой записывается основной предикат, использующий операцию сравнения.

SELECT * FROM EMPL WHERE DeptNo = 'A00'

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

В данном запросе в основном предикате нужно использовать функцию, позволяющую из даты приема сотрудника на работу выделить год – Year(HDate).

SELECT Empl_Id AS "Табельный номер",

LastName || ' ' || FirstName AS "Фамилия И.О."

FROM EMPL

WHERE YEAR(HDate) = 2006

3. Получить табельный номер, фамилию и оклад сотрудников, работающих в отделе с номером A00 и принятых на работу в 2006 году.

В данном запросе в условии отбора строк необходимо объединить два условия – по номеру отдела и по году приема на работу. Поскольку требуется одновременное выполнение условий, используется операция И – AND.

SELECT Empl_Id, LastName, Sal

FROM EMPL

WHERE DeptNo = 'A00' AND YEAR(HDate) = 2006

4. Получить табельный номер, фамилию и оклад сотрудников, работающих в отделе с номером A00 или в отделе с номером B01.

В данном запросе в условии отбора строк для объединения условий следует использовать операцию ИЛИ – OR.

SELECT Empl_Id, LastName, Sal

FROM EMPL

WHERE DeptNo = 'A00' OR DeptNo = 'B01'

5. Получить табельный номер, фамилию сотрудников и их оклады для сотрудников, работающих или в отделе A00, или в отделе B01 и принятых на работу в марте или апреле текущего года.

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

• для выборки данных, относящихся к требуемым отделам, следует использовать операцию ИЛИ – OR;

• для выделения месяца используется функция MONTH(HDate);

• для выбора нужного месяца следует также использовать операцию OR;

• для получения текущего года следует использовать функцию YEAR() и специальный регистр CURRENT DATE;

• для выборки данных, имеющих заданную дату (месяц и год) приема на работу и заданный отдел, следует использовать операцию AND;

• для указания правильного порядка вычисления условия следует использовать круглые скобки.

SELECT Empl_ID, LastName, Sal

FROM EMPL

WHERE (DeptNo = 'A00' OR DeptNo = 'B01') -- требуемый отдел

AND (MONTH(HDate) = 3 OR MONTH(HDate) = 4) -- требуемый месяц

AND YEAR(HDate) = YEAR(CURRENT DATE) -- текущий год

6. Использование предиката IN. Предикат IN удобно использовать, если нужно выбрать одно из нескольких значений некоторой колонки. Использование предиката IN эквивалентно использованию нескольких операций сравнения, объединенных операцией OR. Так, пример C4 может быть реализован следующим образом:

SELECT Empl_Id, LastName, Sal

FROM EMPL

WHERE DeptNo IN ('A00', 'B01')

7. Предикат IN удобно использовать, если нужно выбрать одно из многих (обычно, более двух) значений некоторой колонки, например: получить табельный номер, фамилию и оклады сотрудников, принятых на работу в 2001, 2003, 2005 и 2006 году.

SELECT Empl_Id, LastName, Sal

FROM EMPL

WHERE YEAR(HDate) IN (2001, 2003, 2005, 2006)

8. Предикат IN может использоваться в сложных условиях поиска. Например, в примере C5, вместо операции OR, везде можно использовать предикат IN:

SELECT Empl_ID, LastName, Sal

FROM EMPL

WHERE DeptNo IN ('A00', 'B01') AND MONTH(HDate) IN (3, 4)

AND YEAR(HDate) = YEAR(CURRENT DATE)

9. Использование предиката BETWEEN. Получить табельный номер, фамилию, оклад и номер отдела, в котором работает сотрудник, для тех сотрудников, оклад которых лежит в диапазоне от 10000.00 до 30000.00.

В данном запросе в условии поиска необходимо проверить попадание в заданный диапазон значений: 10000.00 ≤ Sal ≤ 30000.00. Условие поиска можно реализовать двумя способами:

а) С помощью основного предиката и операции AND:

SELECT Emp_ID, LastName, Sal, DeptNo

FROM EMPL

WHERE Sal >= 10000.00 AND Sal <= 30000.00

б) С помощью предиката BETWEEN:

SELECT Emp_ID, LastName, Sal, DeptNo

FROM EMPL

WHERE Sal BETWEEN 10000.00 AND 30000.00

10. В примере C8, поскольку номера месяца образуют упорядоченную последовательность чисел (1, 2, 3, 4, …), для выбора нужного месяца приема на работу, вместо предиката IN, можно использовать предикат BETWEEN; запрос будет иметь следующий вид:

SELECT Empl_ID, LastName, Sal

FROM EMPL

WHERE DeptNo IN ('A00', 'B01') AND MONTH(HDate) BETWEEN 3 AND 4

AND YEAR(HDate) = YEAR(CURRENT DATE)

Для выбора нужного отдела в данном примере предикат BETWEEN использовать нельзя, так как в этом случае рассматривается не последовательность упорядоченных значений от 'A00' до 'B01', а именно эти два значения.

11. Получить табельный номер, фамилию и оклад сотрудников, работающих в отделе A00 и оклад которых лежит в диапазоне от 10000.00 до 30000.00.

SELECT Emp_ID, LastName, Sal

FROM EMPL

WHERE Sal BETWEEN 10000.00 AND 30000.00 AND DeptNo = 'A00'

12. Использование предиката IS NULL. Получить полную информацию о сотрудниках, не получавших премии.

Если сотрудник не получает премию, значение колонки Bonus такого сотрудника не определено. Для анализа неопределенного значения следует использовать предикат IS NULL или IS NOT NULL; условия Bonus = NULL или Bonus <> NULL всегда возвращают неопределенное значение, так как один из операндов имеет значение NULL.

SELECT * FROM EMPL WHERE Bonus IS NULL

13. Получить табельный номер, фамилию сотрудников, конкретные и суммарные выплаты для тех сотрудников, которые работают в отделе A00 и получали комиссионные и премии.

Для соответствующих сотрудников колонки Comm и Bonus имеют не пустые значения.

SELECT Emp_ID, LastName, Sal, Comm, Bonus,

Sal + Comm + Bonus AS "Common pays"

FROM EMPL

WHERE DeptNo = 'A00' AND Comm IS NOT NULL AND Bonus IS NOT NULL

Этот запрс можно реализовать и по-другому, если учесть, что при выполнении арифметических вычислений результат получает неопределенное значение (NULL), если хотя бы один из операндов имеет значение NULL:

SELECT Emp_ID, LastName, Sal, Comm, Bonus,

Sal + Comm + Bonus AS "Common pays"

FROM EMPL

WHERE DeptNo = 'A00' AND Comm + Bonus IS NOT NULL

14. Использование предиката LIKE. Получить табельный номер, фамилию и оклады сотрудников, фамилии которых начинаются с буквы А.

SELECT Empl_ID, LastName, Sal

FROM EMPL

WHERE LastName LIKE 'A%'

Этот запрос можно реализовать, используя встроенную функцию substr():

SELECT Empl_ID, LastName, Sal

FROM EMPL

WHERE SUBSTR(LastName, 1, 1) = 'A'

15. Получить табельный номер, фамилию и оклады сотрудников, работающих в проектных отделах и принятых на работу в 2004, 2005 и 2006 годах, при условии, что номера всех проектных отделов начинаются буквой D.

SELECT Empl_ID, LastName, Sal

FROM EMPL

WHERE DeptNo LIKE 'D%' AND YEAR(HDate) BETWEEN 2004 AND 2006

или, используя функцию обработки строк:

SELECT Empl_ID, LastName, Sal

FROM EMPL

WHERE SUBSTR(DeptNo, 1, 1) = 'D' AND YEAR(HDate) BETWEEN 2004 AND 2006

16. Получить табельный номер, фамилию, оклад и номер отдела, в котором работает сотрудник, для сотрудников, родившихся ранее 1950 года.

SELECT Emp_ID, LastName, Sal, DeptNo

FROM EMPL

WHERE BDate IS NOT NULL AND YEAR(BDate) < 1950

Запросы, использующие несколько таблиц

Примеры использования операций соединения

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

Пример 1: В данном примере показаны результаты выполнения различных операций соединения, использующих таблицы J1 и J2. В этих таблицах находятся следующие строки:

SELECT * FROM J1 SELECT * FROM J2

W	X	Y	Z
A		A
B		C
C		D

Следующий запрос выполняет внутреннее соединение таблиц J1 и J2 на основе первой колонки каждой таблицы

SELECT * FROM J1 INNER JOIN J2 ON W = Y

W	X	Y	Z
A		A
C		C

В данном примере внутреннего соединения строка со значением колонки W=’B’ из J1 и строка со значением колонки Y=’D’ из J2 не включаются в результат, поскольку для каждой из этих строк нет соответствующих строк в другой таблице.

Следует отметить, что запрос, использующий внутреннее соединение, эквивалентен следующему запросу, в котором используется конструкция WHERE:

SELECT * FROM J1, J2 WHERE W=Y

Следующий запрос, использующий левое внешнее соединение, возвращает пропущенную в предыдущем запросе строку из J1 с пустыми значениями колонок из J2. Включаются все строки из J1.

SELECT * FROM J1 LEFT OUTER JOIN J2 ON W=Y

W	X	Y	Z
A		A
B		-	-
C		C

Следующий запрос, использующий правое внешнее соединение, возвращает пропущенную строку из J2 с пустыми значениями колонок из J1. Включаются все строки из J2.

SELECT * FROM J1 RIGHT OUTER JOIN J2 ON W=Y

W	X	Y	Z
A		A
C		C
-	-	D

Следующий запрос, использующий полное внешнее соединение, возвращает пропущенные строки из обеих таблиц – J1 и J2 с пустыми значениями колонок из соответствующих таблиц. Включаются все строки из J1 и J2.

SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y

W	X	Y	Z
A		A
C		C
-	-	D
B		-	-

Пример 2: Используя таблицы J1 и J2 из предыдущего примера, проанализируйте, что произойдет, если к условию поиска добавить дополнительный предикат.

SELECT * FROM J1 INNER JOIN J2 ON W=Y AND X=13

W	X	Y	Z
C		C

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

Следует обратить внимание на то, как дополнительное условие влияет на полное внешнее соединение.

SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y AND X=13

W	X	Y	Z
-	-	A
C		C
-	-	D
A		-	-
B		-	-

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

Если включить дополнительный предикат в конструкцию WHERE, будет получен совершенно другой результат.

SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y

WHERE X=13

W	X	Y	Z
C		C

Конструкция WHERE обрабатывается после операции соединения и, следовательно, условие поиска, указанное в конструкции WHERE, будет применяться к промежуточному результату, полученному при выполнении операции полного внешнего соединения. Этот промежуточный результат будет совпадать с результатом полного внешнего соединения из примера B1. Конструкция WHERE применяется к этому промежуточному результату и исключает все строки, кроме строки, имеющей X=13. Выбор места размещения предиката при выполнении внешних соединений может оказать существенное влияние на результат. Рассмотрим, что случится, если будет использован предикат X=12 вместо X=13.

Следующая операция внутреннего соединения не возвращает строки.

SELECT * FROM J1 INNER JOIN J2 ON W=Y AND X=12

Следовательно, полное внешнее соединение вернет 6 строк, 3 строки из J1 с пустыми колонками из J2, и 3 строки из J2 с пустыми колонками из J1.

SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y AND X=12

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

Некоторая организация организует учет поставки товаров на торговые базы. Информация о поставках хранится в базе данных, схема которой приведена ниже (Рис. 4.11).

Рис. 4.11. Концептуальная схема базы данных учета поставок

Описание таблиц.

Таблица SALOR (Поставщик) предназначена для хранения информации о поставщиках и имеет следующую структуру.

Название колонки	Атрибуты колонки	Пояснения
SId	char(3) not null Primary Key	Учетный номер поставщика
SName	varchar(20) not null Unique	Название фирмы, осуществляющей поставки
Status	int	Статус поставщика, отражающий степень важности данного поставщика для организации
SCity	varchar(20) not null	Город размещения поставщика
Mngr	varchar(50) not null	Контактное лицо фирмы-поставщика

Таблица CATEGORY (Категория товара) предназначена для хранения информации о категориях товаров и имеет следующую структуру:

Название колонки	Атрибуты колонки	Пояснения
CId	char(3) not null Primary Key	Код категории товара
CName	varchar(50) not null Unique	Название категории
Descr	varchar(200)	Описание

Таблица ARTICLE (Товар) предназначена для хранения информации о товаре и имеет следующую структуру:

Название колонки	Атрибуты колонки	Пояснения
ACode	char(3) not null Primary Key	Код товара
AName	varchar(50) not null Unique	Название товара
Price	decimal(10,2) not null	Стоимость единицы товара
ACity	varchar(100) not null	Город места производства товара
PrName	varchar(50) not null	Название фирмы – производителя товара
CId	char(3) not null references Category	Код категории товара

Таблица WAREHOUSE (Торговая база) предназначена для хранения информации о торговых базах, на которые осуществляется поставка товаров, и имеет следующую структуру:

Название колонки	Атрибуты колонки	Пояснения
WId	char(3) not null Primary Key	Номер торговой базы
WName	varchar(50) not null Unique	Название торговой базы
WCity	varchar(100) not null	Город размещения торговой базы
Mngr	varchar(50) not null	Управляющий торговой базы

Таблица INVOICE (Накладная на поставленный товар) предназначена для хранения информации о товарах, поставленных поставщиками на торговые базы, и имеет следующую структуру:

Название колонки	Атрибуты колонки	Пояснения
SId	char(3) not null references Salor	Учетный номер поставщика
ACode	char(3) not null references Article	Код товара
WId	char(3) not null references Warehouse	Номер торговой базы
Qty	int not null	Количество поставленного товара

Написать следующие запросы.

D. Использование связанных данных из нескольких таблиц (реализация операции соединения реляционной алгебры)

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

В таблице ARTICLE для товара представлен код категории, к которой относится данный товар. Название категории хранится в таблице CATEGORY. Чтобы получить необходимый отчет, нужно соединить таблицы ARTICLE и CATEGORY по одноименному атрибуту CId. Такое соединение можно реализовать двумя способами:

а) С помощью конструкции WHERE и условия отбора строк:

SELECT AName, Price, CName

FROM ARTICLE, CATEGORY

WHERE ARTICLE.CId = CATEGORY.Cid

ORDER BY CName

Имя колонки CId, используемое в данном запросе, не является уникальным – оно включено в таблицы Article и Category. Поэтому в запросе необходимо уточнить это имя именами таблиц – ARTICLE.CId и

CATEGORY.CId.

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

SELECT AName, Price, CName

FROM ARTICLE INNER JOIN CATEGORY

ON ARTICLE.CId = CATEGORY.Cid

ORDER BY CName

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

SELECT AName, Price, CName

FROM ARTICLE A, CATEGORY C

WHERE A.CId = C.Cid

ORDER BY CName

SELECT AName, Price, CName

FROM ARTICLE A INNER JOIN CATEGORY C

ON A.CId = C.Cid

ORDER BY CName

Если в запросе используется переименование таблиц, исходными именами таблиц пользоваться нельзя; следующий запрос приведет к появлению сообщения об ошибке:

SELECT ARTICLE.AName, A.Price, CName

FROM ARTICLE A, CATEGORY C

WHERE ARTICLE.CId = C.Cid

Следует отметить, что во всех примерах, использующих внутреннее соединение, вместо INNER JOIN можно писать просто JOIN.

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

Стоимость поставленного товара определяется как количество товара, умноженное на стоимость единицы товара (Qty * Price). Поскольку информация о стоимости находится в таблице ARTICLE, а информация о количестве поставленного товара – в таблице INVOICE, здесь опять требуется соединение таблиц.

SELECT Sid, A.ACode, Wid, Qty, Qty * Price AS Pay

FROM INVOICE I INNER JOIN ARTICLE A ON I.ACode = A.ACode

Учитывая, что в данном запросе выводится вся информация из таблицы INVOICE (в этой таблице также присутствует атрибут ACode), этот запрос можно переписать следующим образом:

SELECT I.*, Qty * Price AS Pay

FROM INVOICE I INNER JOIN ARTICLE A ON I.ACode = A.ACode

3. В условиях примера D2 изменить форму отчета, включив в него вместо номеров (кода) поставщика, товара и торговой базы их названия.

В этом случае для получения необходимых данных потребуется выполнить соединение четырех таблиц – INVOICE, SALOR (для выборки названия поставщика), ARTICLE (для выборки названия товара) и WAREHOUSE (для выборки названия торговой базы) по одноименным атрибутам первичных и внешних ключей соответствующих таблиц.

Если используется конструкция WHERE, порядок перечисления таблиц в конструкции FROM произволен:

SELECT SName, AName, WName, Qty, Qty * Price AS Pay

FROM SALOR S, ARTICLE A, WAREHOUSE W, INVOICE I

WHERE S.Sid = I.Sid AND A.ACode = I.ACode AND W.Wid = I.WId

Если же используется операция соединения в конструкции FROM, таблица INVOICE должна быть указана в первой операции соединения (левым или правым операндом), поскольку все остальные используют колонки из этой таблицы:

SELECT SName, AName, WName, Qty, Qty * Price AS Pay

FROM SALOR S INNER JOIN INVOICE I ON S.Sid = I.Sid

INNER JOIN ARTICLE A ON A.ACode = I.ACode

INNER JOIN WAREHOUSE W ON W.Wid = I.WId

или

SELECT SName, AName, WName, Qty, Qty * Price AS Pay

FROM INVOICE I INNER JOIN SALOR S ON S.Sid = I.Sid

INNER JOIN ARTICLE A ON A.ACode = I.ACode

INNER JOIN WAREHOUSE W ON W.Wid = I.WId

4. Получить информацию о поставках товаров, относящихся к категории с номером C01.

Категория товара хранится в таблице ARTICLE, информация о поставках – в таблице INVOICE. Следовательно, здесь также необходимо использовать соединение таблиц, но кроме этого, необходимо выполнить еще и отбор строк. Необходимый результат можно получить с помощью любого из следующих запросов:

– и условие соединения таблиц, и условие отбора строк включены в конструкцию WHERE

SELECT I.*

FROM INVOICE I, ARTICLE A

WHERE A.ACode = I.ACode AND A.Cid = 'C01'

– условие соединения таблиц задается в конструкции FROM, а условие отбора строк – в конструкции WHERE

SELECT I.*

FROM INVOICE I INNER JOIN ARTICLE A ON A.ACode = I.ACode

WHERE A.Cid = 'C01'

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

– наконец, и условие соединения таблиц, и условие отбора строк задаются в конструкции FROM

SELECT I.*

FROM INVOICE I INNER JOIN ARTICLE A ON A.ACode = I.ACode AND A.Cid = 'C01'

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

Название категории находится в таблице CATEGORY, номер категории товара – в таблицах CATEGORY и ARTICLE, информация о поставках – в таблице INVOICE. Следовательно, здесь необходимо использовать соединение трех таблиц – CATEGORY, ARTICLE и INVOICE, и отбор строк. Возможны такие же варианты, как и в примере D4. Включим условие соединения таблиц в конструкцию FROM, а условие отбора строк – в конструкцию WHERE:

SELECT CName, AName, Qty

FROM CATEGORY C INNER JOIN ARTICLE A ON C.Cid = A.Cid

INNER JOIN INVOICE I ON A.ACode = I.ACode

WHERE C.CId LIKE 'C%'

6. Получить отчет в виде: название товара, стоимость единицы товара, номер поставщика, количество поставленного товара для товаров, поставляемых на базу с номером W01.

SELECT AName, Price, Sid, Qty

FROM ARTICLE A INNER JOIN INVOICE I ON A.ACode = I.ACode

WHERE WId = 'W01'

7. Для каждой торговой базы получить номер другой базы, размещенной в том же городе, и название города.

Рассмотрим способ реализации данного запроса на конкретном примере. Поскольку в данном запросе нас интересуют только номера баз и названия городов, в которых они размещены, рассмотрим следующий фрагмент таблицы Warehouse:

WId	WCity
W01	Москва
W02	Орел
W03	Курск
W04	Москва
W05	Орел
W06	Москва

Для реализации такого запроса необходимо выполнить соединение таблицы Warehouse с самой собой по атрибуту WCity с обязательным переименованием (например, … AS W), по крайней мере, одной таблицы; в результате будет получена следующая таблица:

Warehouse.WId	Warehouse.WCity = W.WCity	W.WId
W01	Москва	W01
W01	Москва	W04
W01	Москва	W06
W02	Орел	W02
W02	Орел	W05
W03	Курск	W03
W04	Москва	W01
W04	Москва	W04
W04	Москва	W06
W05	Орел	W02
W05	Орел	W05
W06	Москва	W01
W06	Москва	W04
W06	Москва	W06

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

SELECT Warehouse.Wid, W.Wid, W.WCity

FROM Warehouse, Warehouse W

WHERE Warehouse.WCity = W.WCity AND Warehouse.Wid <> W.Wid

или

SELECT Warehouse.WId, W.WId, W.WCity

FROM Warehouse INNER JOIN Warehouse W ON Warehouse.WCity = W.WCity AND Warehouse.WId <> W.WId

8. Использование операций внешнего соединения. В примере D4 был получен отчет о поставленных товарах, относящихся к категории товаров C01. Однако в данной категории могут находиться товары, которые никем не поставлялись. Если необходимо получить отчет по всем – поставляемым и не поставляемым – товарам данной категории, необходимо использовать операцию внешнего соединения – левого или правого, в зависимости от того, где будет указана таблица ARTICLE.

SELECT I.*

FROM INVOICE I RIGHT OUTER JOIN ARTICLE A ON A.ACode = I.ACode

WHERE A.Cid = 'C01'

или

SELECT I.*

FROM ARTICLE A LEFT OUTER JOIN INVOICE I ON A.ACode = I.ACode

WHERE A.Cid = 'C01'

При использовании внешних соединений перенесение условия отбора строк из конструкции WHERE в конструкцию FROM может привести к получению разных результатов. Рассмотрим конкретный пример.

Пусть в таблице ARTICLE находится следующая информация:

ACode	…	CId
A01	…	C01
A02	…	C01
A03	…	C01
A04	…	C02
A05	…	C03

а в таблице INVOICE – следующая:

SId	ACode	WId	Qty
S01	A01	B01
S02	A01	B01
S01	A02	B02
S03	A05	B02

Запрос, приведенный выше, сначала выполнит левое внешнее соединение, в результате которого будут получены следующие данные:

A.ACode	CId	SId	I.ACode	WId	Qty
A01	C01	S01	A01	B01
A01	C01	S02	A01	B01
A02	C01	S01	A02	B02
A03	C01	-	A03	-	-
A04	C02	-	-	-	-
A05	C03	S03	A05	B02

Затем из полученной таблицы, в соответствии с условием WHERE, будут выбраны нужные строки:

Sid	Acode	WId	Qty
S01	A01	B01
S02	A01	B01
S01	A02	B02
-	A03	-	-

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

SELECT I.*

FROM ARTICLE A LEFT OUTER JOIN INVOICE I ON A.ACode = I.ACode

AND A.Cid = 'C01'

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

A.ACode	A.CId	SId	I.ACode	WId	Qty
A01	C01	S01	A01	B01
A01	C01	S02	A01	B01
A02	C01	S01	A02	B02
A03	C01	-	A03	-	-
A04	C02	-	-	-	-
A05	C03	S03	A05	B02

E. Использование предикатов с подзапросами

В предикатах, используемых в конструкции WHERE, можно использовать подзапросы.

1. Получить названия торговых баз, находящихся в том же городе, что и база с номером W01.

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

SELECT WCity FROM WAREHOUSE WHERE WId = 'W01'

Для получения необходимого результата в конструкции WHERE необходимо сравнивать колонку WCity с результатом приведенного выше запроса. Поскольку данный запрос возвращает единственное значение (колонка WId определена как первичный ключ), он может быть использован в операции сравнения. Запрос, используемый в другом запросе, называется подзапросом. Подзапрос, возвращающий единственное значение, называется скалярным подзапросом. Подзапрос должен быть обязательно заключен в круглые скобки:

SELECT WName FROM WAREHOUSE

WHERE WCity = (SELECT WCity FROM WAREHOUSE WHERE WId = 'W01')-- подзапрос

Данный запрос вернет в качестве результата, в том числе, и название базы с номером W01. Если эту базу необходимо исключить из отчета, требуемый запрос будет иметь следующий вид:

SELECT WName FROM WAREHOUSE

WHERE WCity = (SELECT WCity FROM WAREHOUSE WHERE WId = 'W01') AND

WId <> 'W01'

2. Получить информацию о поставщиках, статус которых меньше статуса поставщика с номером S01.

И в данном случае статус поставщика может быть получен в результате выполнения скалярного подзапроса:

SELECT * FROM SALOR

WHERE Status < (SELECT Status FROM SALOR WHERE Sid = 'S01')

3. Использование предиката IN. Получить информацию о поставщиках, не поставляющих никакие товары.

Вся информация о поставках собрана в таблице INVOICE. Поэтому если поставщик ничего не поставляет, номера данного поставщика в таблице INVOICE нет. Для проверки этого условия можно использовать предикат NOT IN:

SELECT * FROM SALOR

WHERE Sid NOT IN (SELECT DISTINCT Sid FROM INVOICE)

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

Данный запрос реализует операцию вычитания реляционной алгебры.

4. Получить отчет в виде: название товара, стоимость единицы товара, номер поставщика, количество поставленного товара для товаров, поставляемых только на базу с номером W01.

Приведенный выше (в примере D6) запрос возвращает информацию и о тех товарах, которые, наряду с базой W01, поставляются и на другие базы. Информацию о таких товарах следует исключить из отчета.

Коды товаров, поставляемых на другие базы (кроме W01), можно получить в результате выполнения следующего подзапроса:

SELECT ACode FROM INVOICE WHERE WId <> 'W01'

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

SELECT AName, Price, Sid, Qty

FROM ARTICLE A INNER JOIN INVOICE I ON A.ACode = I.ACode

WHERE WId = 'W01' -- информация о товарах, поставляемых, в том числе, и на базу W01

AND A.ACode NOT IN -- исключить

(SELECT ACode FROM INVOICE

WHERE WId <> 'W01') -- товары, поставляемые на другие базы

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

Все пары код товара – номер торговой базы можно получить с помощью следующего запроса, реализующего Декартово произведение отношений (таблиц) ARTICLE и WAREHOUSE:

SELECT ACode, WId FROM ARTICLE, WAREHOUSE

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

SELECT ACode, WId FROM INVOICE

Удаление пар можно также выполнить с помощью предиката IN:

SELECT ACode, WId FROM ARTICLE, WAREHOUSE

WHERE (ACode, WId) NOT IN (SELECT ACode, WId FROM INVOICE)

6. Получить названия товаров, которые поставляются хотя бы на одну базу из тех, на которые поставляет товары поставщик с номером S01.

Номера баз, на которые поставляет товары поставщик с номером S01, можно определить с помощью следующего запроса:

SELECT DISTINCT WId FROM INVOICE WHERE SId = 'S01'

Этот запрос должен быть использован как подзапрос в предикате IN:

SELECT DISTINCT AName

FROM ARTICLE A INNER JOIN INVOICE I ON A.ACode = I.ACode

WHERE WId IN (SELECT DISTINCT WId FROM INVOICE WHERE SId = 'S01')

7. Использование предиката EXISTS. Приведенный выше (в примере E5) запрос может быть реализован и с помощью предиката EXISTS.

SELECT ACode, WId FROM ARTICLE A, WAREHOUSE W

WHERE NOT EXISTS (SELECT * FROM INVOICE I

WHERE A.ACode = I.ACode AND W.WId = I.WId)

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

8. Получить номера поставщиков, поставляющих все товары.

Рассмотрим на примере, как должен выполняться данный запрос.

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

ACode	…
A01	…
A02	…
A03	…

Если, например, поставщик S01 поставляет все товары, это значит, что в таблице INVOICE обязательно будут находиться следующие пары: <S01, A01>, <S01, A02>, <S01, A03>. Это условие можно определить так: для всех кодов товаров из ARTICLE обязательно найдется (или существует) строка в INVOICE, в которой код товара совпадает с кодом товара из ARTICLE и номер поставщика совпадает с искомым номером.

На языке реляционного исчисления данный запрос будет записан следующим образом:

{t(SId) | ∀u(Article)(∃ v(Invoice) (Article(u) ∧ Invoice(v) ∧ v[ACode] = u[ACode] ∧ v[Sid] = t[Sid]))}

В языке SQL квантор существования (∃) реляционного исчисления реализуется предикатом EXISTS. Но в языке нет предиката, реализующего квантор всеобщности (∀). Здесь можно воспользоваться следующей эквивалентностью:

∀x(ψ(x)) эквивалентно ¬∃x(¬ψ(x)). Тогда требуемое условие можно переформулировать так: не существует код товара из ARTICLE, для которого не найдется (или не существует) строка в INVOICE, в которой код товара совпадает с кодом товара из ARTICLE и номер поставщика совпадает с искомым номером.

Выражение реляционного исчисления будет иметь следующий вид:

{t(SId) | ¬∃u(Article)(¬∃ v(Invoice) (Article(u) ∧ Invoice(v) ∧ v[ACode] = u[ACode] ∧ v[Sid] = t[Sid]))}

или

{t(SId) | ¬∃u(Article)(Article(u) ∧ ¬∃ v(Invoice) (Invoice(v) ∧ v[ACode] = u[ACode] ∧ v[Sid] = t[Sid]))}

Соответствующий запрос будет выглядеть следующим образом:

SELECT DISTINCT Sid FROM INVOICE t -- {t(Sid) | …

WHERE NOT EXISTS (SELECT ACode FROM ARTICLE u -- … ¬∃u(Article)(Article(u) ∧ …

WHERE NOT EXISTS (SELECT * FROM INVOICE v -- … ¬∃ v(Invoice) (Invoice(v) ∧ …

WHERE v.ACode = u.ACode AND -- … v[ACode] = u[ACode] ∧ …

v.SId = t.SId)) -- … v[Sid] = t[Sid]))}

9. Использование предиката с кванторами. Получить название и стоимость товаров, стоимость которых больше стоимости любого товара, поставляемого поставщиком с номером S01.

SELECT AName, Price

FROM ARTICLE

WHERE Price > ALL (SELECT Price

FROM ARTICLE A, INVOICE I

WHERE A.ACode = I.ACode AND I.SId = 'S01')

F. Использование агрегатных функций

При выполнении различных запросов часто используются следующие агрегатные функции:

SUM ([ DISTINCT ] имя_колонки) – суммирование всех или только отличающихся друг от друга значений указанной числовой колонки,

AVG ([ DISTINCT ] имя_колонки) – вычисление среднего для всех или только отличающихся друг от друга значений указанной числовой колонки,

MAX ([ DISTINCT ] имя_колонки) – нахождение максимального среди всех или только отличающихся друг от друга значений указанной колонки,

MIN ([ DISTINCT ] имя_колонки) – нахождение минимального среди всех или только отличающихся друг от друга значений указанной колонки,

COUNT ([ DISTINCT ] имя_колонки) – подсчет количества всех или только отличающихся друг от друга значений указанной колонки,

COUNT (*) – подсчет количества строк.

Если в указанной аргументом колонке встречаются пустые значения, все функции, кроме COUNT, возвратят значение NULL; функция COUNT такое значение просто не учитывает, поэтому, если, например, в таблице нет ни одной строки, функция COUNT в любой форме записи вернет значение 0.

1. Получить максимальную стоимость среди всех товаров

SELECT MAX(Price)

FROM ARTICLE

В данном примере вся таблица рассматривается как одна группа, которая используется агрегатной функцией MAX().

2. Получить название и стоимость тех товаров, стоимость которых превышает среднюю стоимость товара.

SELECT AName, Price

FROM ARTICLE

WHERE Price > (SELECT AVG(Price) FROM ARTICLE)

Здесь в подзапросе вся таблица рассматривается как одна группа, которая используется агрегатной функцией AVG(). Подзапрос возвращает единственное значение, поэтому может использоваться в операции сравнения.

3. Получить имена поставщиков, поставляющих товары, имеющие максимальную стоимость.

SELECT DISTINCT SName

FROM SALOR S INNER JOIN INVOICE I ON S.SId = I.SId

INNER JOIN ARTICLE A ON I.ACode = A.ACode

WHERE Price = (SELECT MAX(Price) FROM ARTICLE)

Поскольку стоимость товара хранится в таблице ARTICLE, а имена поставщиков – в таблице SALOR, необходимо выполнить соединение таблиц, включая и связывающую таблицу INVOICE. В конструкции WHERE используется скалярный подзапрос, возвращающий единственное значение (даже если в таблице ARTICLE есть несколько товаров, имеющих одинаковую максимальную стоимость).

4. Для каждой категории товаров получить номер категории и максимальную стоимость среди товаров данной категории.

SELECT CId, MAX(Price) AS "MAX PRICE"

FROM ARTICLE

GROUP BY CId

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

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

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

SELECT CName, MAX(Price) AS "Max price", AVG(Price) AS "Average price"

FROM ARTICLE A INNER JOIN CATEGORY C ON A.CId = C.CId

GROUP BY CName

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

SELECT SName, AName, SUM(Qty) AS "Common quantity",

SUM(Qty * Price) AS "Common pay"

FROM SALOR S INNER JOIN INVOICE I ON S.SId = I.SId

INNER JOIN ARTICLE A ON I.ACode = A.ACode

GROUP BY SName, AName

7. Тот же самый запрос, что и в примере F6, только надо учесть всех поставщиков – и поставляющих товары, и не поставляющих товары; для поставщиков, ничего не поставляющих, в качестве количества и стоимости поставленных товаров вывести 0.

SELECT SName, AName, Coalesce(SUM(Qty), 0) AS "Common quantity",

Coalesce(SUM(Qty * Price), 0) AS "Common pay"

FROM SALOR S LEFT OUTER JOIN INVOICE I ON S.SId = I.SId

LEFT OUTER JOIN ARTICLE A ON I.ACode = A.ACode

GROUP BY SName, AName

В конструкции FROM первая операция левого внешнего соединения добавит в отчет и тех поставщиков, для которых отсутствуют записи в таблице INVOICE; эти строки также должны участвовать в итоговом результате, поэтому для соединения с таблицей ARTICLE нужно использовать также левое внешнее соединение.

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

SELECT AName, Price, SUM(Qty) AS "Common quantity",

SUM(Qty * Price) AS "Common pay",

COUNT(DISTINCT SId) AS "Number of salors"

FROM ARTICLE A LEFT OUTER JOIN INVOICE I ON A.ACode = I.ACode

GROUP BY AName, Price

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

В данном примере, после группирования информации, относящейся к поставщику, из всех групп надо выбрать те, в строках которых упоминается только один номер торговой базы. Отбор групп осуществляется с помощью конструкции HAVING, количество упоминающихся разных торговых баз – с помощью функции COUNT(DISTINCT WId). Запрос будет иметь следующий вид:

SELECT SName, SUM(Qty) AS "Common qty", SUM(Qty * Price) AS "Common pay"

FROM SALOR S INNER JOIN INVOICE I ON S.SId = I.SId

INNER JOIN ARTICLE A ON I.ACode = A.ACode

GROUP BY SName

Понравилась статья? Добавь ее в закладку (CTRL+D) и не забудь поделиться с друзьями: