Where city in ('indianapolis','whiteland')

GROUP BY CITY

ORDER BY 2,3;

Вывод:

CITY AVG(PAY_RATE) AVG(SALARY)

INDIANAPOLIS 13.5833333 20000

WHITELAND 40000

Значения сортируются так, что значения NULL оказываются в конце. Поэтому запись для города Индианаполис представлена первой. Город Гринвуд не был выбран, но если бы был, то соответствующая ему запись была бы представлена перед записью для Уайтленда, поскольку для Гринвуда средняя зарплата (SALARY) равна 30000, а средняя зарплата является вторым параметром сортировки в выражении ключевого слова ORDER BY.

В завершение раздела рассмотрим использование в выражении ключевого слова ORDER BY итоговых функций МАХ и MIN.

Ввод:

SELECT CITY, MAX(PAY_RATE), MIN(SALARY)

FROM EMP_PAY_TMP

GROUP BY CITY;

Вывод:

CITY MAX(PAY_RATE) MIN(SALARY)

GREENWOOD 30000

INDIANAPOLIS 15 20000

WHITELAND 40000

3 строки выбраны.

Представление имен столбцов числами

В отличие от выражения ключевого слова ORDER BY, в выражении ключевого слова GROUP BY указать порядок столбцов с помощью их номеров нельзя — за исключением того случая, когда используется ключевое слово UNION и имена всех столбцов разные. Вот пример использования номеров вместо имен столбцов.

SELECT EMP_ID, SUM(SALARY)

FROM EMPLOYEE_PAY_TBL

UNION

SELECT EMP_ID, SUM(PAY_RATE)

FROM EMPLOYEE_PAY_TBL

GROUP BY 2, 1;

Этот оператор SQL возвращает табельный номер служащего (EMP_ID) и группирует суммы по значениям зарплаты. При использовании ключевого слова UNION результаты двух операторов SELECT объединяются. Группирование выполняется сначала по столбцу 2Б, представляющем зарплату (SALARY), а затем по столбцу 1, представляющем табельный номер служащего (EMP_ID).

GROUP BY И ORDER BY

Обратите внимание на то, что GROUP BY и ORDER BY работают одинаково в том смысле, что оба эти ключевые слова задают сортировку данных. В выражении ключевого слова ORDER BY задается сортировка данных запроса, а в выражении ключевого слова GROUP BY — сортировка этих данных по группам. Поэтому ключевое слово GROUP BY можно использовать для сортировки точно так же, как и ORDER BY.

Вот несколько особенностей использования ключевого слова GROUP BY для сортировки.

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

Вот пример использования для сортировки данных ключевого слова GROUP BY вместо ключевого слова ORDER BY:

Ввод:

SELECT LAST_NAME, FIRST_NAME, CITY

FROM EMPLOYEE_TBL

GROUP BY LAST_NAME;

Вывод:

SELECT LAST_NAME, FIRST_NAME, CITY

*

ERROR at line 1:

ORA-00979: not a GROUP BY expression

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

В следующем примере проблема предыдущего оператора решена путем добавления в список ключевого слова GROUP BY недостающих имен из списка ключевого слова SELECT.

Ввод:

SELECT IAST_NAME, FIRST_NAME, CITY

FROM EMPLOYEE_TBL

GROUP BY LAST_NAME, FIRST_NAME, CITY;

Вывод:

LAST_NAME FIRST_NAME CITY

GLASS BRANDON WHITELAND

GLASS JACOB INDIANAPOLIS

PLEW LINDA INDIANAPOLIS

SPURGEON TIFFANY INDIANAPOLIS

STEPHENS TINA GREENWOOD

WALLACE MARIAH INDIANAPOLIS

6 строк выбраны.

В этом примере выбираются те же данные из той же таблицы, но уже все столбцы перечислены в выражении ключевого слова GROUP BY в том же порядке, в каком они указаны в списке ключевого слова SELECT. В результате данные показаны отсортированными сначала по столбцу LAST_NAME, затем по столбцу FIRST_NAME и наконец, по столбцу CITY. С помощью ключевого слова ORDER BY то же самое получить легче, но для правильного использования ключевого слова GROUP BY, наверное, будет полезно разобраться, как с его помощью сортируются данные, перед тем, как выполнить группирование результатов.

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

Ввод:

SELECT CITY, LAST_HAME

FROM EMPLOYEEJTBL

GROUP BY CITY, LAST_NAME;

Вывод:

CITY LAST_NAME

GREENWOOD STEPHENS

INDIANAPOLIS GLASS

INDIANAPOLIS PLEW

INDIANAPOLIS SPURGEON

INDIANAPOLIS WALLACE

WHITELAND GLASS

6 строк выбраны.

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

Ввод:

SELECT CITY, COUNT(*)

FROM EMPLOYEEJTBL

GROUP BY CITY

ORDER BY 2,1;

Вывод:

CITY COUNT(*)

GREENWOOD 1

WHITELAND 1

INDIANAPOLIS 4

Обратите внимание на порядок представления полученных данных. Они сначала отсортированы по числу записей для каждого города и только потом по названиям городов. Для первых двух городов число записей равно 1. Поскольку числа совпадают, порядок представления записей определяется вторым параметром сортировки, а именно, названием города. Поэтому Гринвуд идет перед Уайтлендом.

Хотя ключевые слова GROUP BY и ORDER BY и функционируют подобным образом, между ними имеется существенное отличие. Ключевое слово GROUP BY предназначено для группирования одинаковых значений, а задачей ORDER BY является представление данных просто в определенном порядке. Ключевые слова GROUP BY и ORDER BY можно использовать в одном операторе SELECT, но каждое из них должно выполнять свою задачу. В одном операторе SELECT ключевое слово GROUP BY должно предшествовать ключевому слову ORDER BY.

Ключевое слово GROUP BY можно использовать для сортировки данных в операторе CREATE VIEW, а вот ключевое слово ORDER BY использовать в операторе CREATE VIEW нельзя. Оператор CREATE VIEW будет подробно рассматриваться в ходе урока 20, "Создание и использование представлений и синонимов".

Ключевое слово HAVING

Ключевое слово HAVING используется в операторе SELECT вместе с ключевым словом GROUP BY, чтобы указать какие из групп должны быть представлены в выводе. Для GROUP BY ключевое слово HAVING играет ту же роль, что и WHERE для ORDER BY. Другими словами, WHERE задает условия для значений из выбранных столбцов, а HAVING задает условия для групп, создаваемых с помощью GROUP BY.

Ключевое слово HAVING в операторе SELECT должно следовать за выражением ключевого слова GROUP BY и тоже предшествовать ключевому слову ORDER BY, если последнее используется.

Синтаксис оператора SELECT, в котором используется ключевое слово HAVING, следующий.

SELECT столбец1, столбец2

FROM таблица1, таблица2

WHERE условия

GROUP BY столбец1, столбец2

HAVING условия

ORDER BY столбец1, столбец2

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

Ввод:

SELECT CITY, AVG(PAY_RATE), AVG(SALARY)

FROM EMP_PAY_TMP

WHERE CITY <> 'GREENWOOD'

GROUP BY CITY

HAVING AVG(SALARY) > 20000

ORDER BY 3;

Вывод:

CITY AVG(PAY_RATE) AVG(SALARY)

WHITELAND 40000

1 строка выбрана.

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

• Город Гринвуд исключен выражением ключевого слова WHERE.
• Данные по городу Индианаполис не представлены ввиду того, что средняя зарплата для этого города равна 20000, что не превышает необходимые 20000.

Резюме

Итак, вы теперь знаете, как группировать результаты запроса с помощью ключевого слова GROUP BY. Ключевое слово GROUP BY используется, главным образом, с итоговыми функциями SQL типа SUM, AVG, MAX, MIN и COUNT. Ключевое слово GROUP BY подобно ключевому слову ORDER BY в том смысле, что оба они сортируют выводимые данные. Ключевое слово GROUP BY предназначено для сортировки данных по группам, но может использоваться и для обычной сортировки данных, хотя последнее проще сделать с помощью ключевого слова ORDER BY.

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

Лекция 5.7

Использование подзапросов

Литература

• Введение в SQL
• Введение в стандарты языка баз данных SQL
• Структуризированный язык запросов (SQL)
• Реализация языка SQL в СУБД MySQL
• PL/SQL в Oracle
• Двадцать пять заповедей SQL - Сергей Кузнецов
• Лекции по SQL" Карпукова - 227 стр.
• Краткое пособие по языку SQL

Содержание

• Что такое подзапрос?
• Подзапросы в операторе SELECT
• Подзапросы в операторе INSERT
• Подзапросы в операторе UPDATE
• Подзапросы в операторе DELETE
• Подзапросы внутри подзапросов
• Связанные подзапросы
• Вложенные подзапросы

Что такое подзапрос?

Подзапрос — это запрос, содержащийся в выражении ключевого слова WHERE другого запроса с целью дополнительных ограничений на выводимые данные. Подзапросы называют также вложенными запросами. Подзапрос в содержащем его запросе используют для наложения условий на выводимые данные. Подзапросы могут использоваться с операторами SELECT, INSERT, UPDATE или DELETE.

В некоторых случаях подзапрос можно использовать вместо связывания таблиц, тем самым связывая данные таблиц неявно. При использовании в запросе подзапроса сначала выполняется подзапрос, а только потом — содержащий его запрос, причем с учетом условий выполнения подзапроса. Результаты выполнения подзапроса используются при обработке условий в выражении ключевого слова WHERE основного запроса Подзапрос можно использовать либо в выражении ключевого слова WHERE, либо в выражении ключевого слова HAVING главного запроса. Логические операции и операции сравнения типа =, >, <, о, IN, NOT IN, AND, OR и т п. можно использовать как в подзапросе, так и для обработки результатов подзапроса в выражениях ключевых слов WHERE и HAVING.

Все, что применимо к обычному запросу, применимо и к подзапросу Операции связывания, функции, преобразования данных и многое другое можно использовать и в подзапросах

При составлении подзапросов необходимо придерживаться следующих правил.

• Подзапрос необходимо заключить в круглые скобки.
• Подзапрос может ссылаться только на один столбец в выражении своего ключевого слова SELECT, за исключением случаев, когда в главном запросе используется сравнение с несколькими столбцами из подзапроса.
• Ключевое слово ORDER BY использовать в подзапросе нельзя, хотя в главном запросе ORDER BY использоваться может. Вместо ORDER BY в подзапросе можно использовать GROUP BY.
• Подзапрос, возвращающий несколько строк данных, можно использовать только в операторах, допускающих множество значений, например в IN.
• В списке ключевого слова SELECT не допускаются ссылки на значения типа

BLOB, ARRAY, CLOB ИЛИNCLOB.

• Подзапрос нельзя непосредственно использовать как аргумент допускающей множество значений функяии.
• Операцию BETWEEN по отношению к подзапросу использовать нельзя, но ее можно использовать в самом подзапросе. Базовый синтаксис оператора с подзапросом выглядит следующим образом.

SELECT имя_столбиа

FROM таблица

WHERE имя_столбца = (SELECT имя__столбца

FROM таблица

WHERE условия);

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

Рассмотрим примеры правильного и неправильного использования операции BETWEEN в операторе с подзапросом.

Вот пример правильного использования BETWEEN:

SELECT имя_столбца

FROM таблица

WHERE имя_столбца ОПЕРАЦИЯ (SELECT имя_столбца

FROM таблица

WHERE значение BETWEEN значение);

Вот пример неправильного использования BETWEEN:

SELECT имя_столбца FROM таблица

WHERE имя_столбца BETWEEN значение AND (SELECT имя_столбца

FROM таблица);

Подзапросы в операторе SELECT

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

Базовый синтаксис соответствующего оператора должен быть следующим.

SELECT имя_столбца [, имя_столбца ]

FROM таблица1 [, таблица2 ]

WHERE имя_столбца ОПЕРАЦИЯ

(SELECT имя_столбца [, имя_столбца ]

FROM таблица1 [, таблица2 ]

[ WHERE ]);

Например,

SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME EP.PAY__RATE

FROM EMPLOYEEJTBL E, EMPLOYEE_PAY_TBL EP

WHERE E.EMP_ID = EP.EMP_ID

AND E?.PAY_RATE > (SELECT PAY_RATE

FROM EMPLOYEE_PAY_TBL

WHERE E.EMP_ID = '313782439');

Этот оператор SQL возвращает табельный номер служащего, фамилию, имя и норму оплаты труда для всех служащих, у которых эта норма оплаты превышает норму оплаты труда служащего с табельным номером 313782439. В данном случае нет необходимости знать (выяснять), какова норма оплаты того конкретного служащего — норма оплаты его труда нужна только для получения списка тех служащих, которые зарабатывают больше, чем он.

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

Ввод:
SELECT PAY_RATE

FROM EMPLOYEE_PAY_TBL

WHERE E.EXP_IC - ' 220S84332');

Вывод:

· PAY_RATE 11

1 строка выбрана.

Теперь используем этот запрос в качестве подзапроса в следующемзапросе.

SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME EP.PAY_RATE

FROM EMPLOYEEJTBL E, EMPLOYEE_PAY_TBL EP

WHERE E.EMP_ID = EP.EMP_ID

AND EP.PAY_RATE > (SELECT PAY_RATE

FROM EMPLOYEE_PAY_TBL

WHERE E.EMP_ID = '220984332');

EMP_ID LAST_NAM FIRST_NAM PAY_RATE

442346889 PLEW LINDA 14.75

443679012 SPURGEON TIFFANY 15

2 строки выбраны.

Результатом подзапроса будет 11 (это видно из предыдущего примера), поэтому второе из условий в выражении ключевого слова WHERE главного запроса преобразуется в условие

AND EP.PAY_RATE > 11

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

	ЕМР ID	LAST NAM	FIRST NAM	PAY_RATE
	442346889 443679012	PLEW SPURGEON	LINDA TIFFANY	14.75 15

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

Подзапросы в операторе INSERT

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

Базовый синтаксис соответствующего оператора должен быть следующим.

INSERT INTO имя_таблицы [ (столбец! [, столбец2 ]) ]

SELECT [ *| столбец1 [, столбец2 ]]

FROM таблица1 [, таблица2 ]

[ WHERE значение ОПЕРАЦИЯ значение ]

Вот пример использования оператора INSERT с подзапросом.

INSERT INTO RICH_EMPLOYEES

SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME EP.PAY_RATE

FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP

WHERE E.EMP_ID = EP.EMP_ID

AND EP.PAY_RATE > (SELECT PAY_RATE

FROM EMPLOYEE_PAY_TBL

WHERE E.EMP_ID = '220984332');

2 строки созданы.

Этот оператор INSERT вставляет значения EMP_ID, LAST_NAME, FIRST_NAME и PAY_RATE в таблицу RICH_EMPLOYEES для всех служащих, норма оплаты труда которых превышает норму оплаты труда служащего с табельным номером 220984332.

При использовании команд DML типа INSERT не забывайте об использовании команд COMMIT и ROLLBACK

Подзапросы в операторе UPDATE

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

Базовый синтаксис оператора следующий.

UPDATE таблица

SET имя_столбца [, имя_столбца ] =

(SELECT имя_столбца [,имя_столбца ] FROM таблица

[ WHERE ])

Рассмотрим примеры, разъясняющие использование оператора UPDATE с подзапросом. Сначала рассмотрим запрос, возвращающий табельные номера служащих из Индианаполиса. Как видите, таких служащих четыре.

Ввод:

SELECT EMP_ID

FROM EMPLOYEE_TBL

WHERE CITY = 'INDIANAPOLIS';

Вывод:

EMP_ID

4 строки выбраны.

Этот запрос будет использован в качестве подзапроса в следующем операторе UPDATE. Вот этот оператор UPDATE с подзапросом.

UPDATE EMPLOYEE_PAY_TBL

SET PAY_RATE = PAY_RATE * 1.1

WHERE EMP_ID IN (SELECT EMP_ID

FROM EMPLOYEE_TBL

WHERE CITY = 'INDIANAPOLIS');

4 строки обновлены.

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

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

Подзапросы в операторе DELETE

Подзапросы можно использовать в операторе DELETE. Базовый синтаксис оператора следующий.

DELETE FROM имя_таблицы

[ WHERE ОПЕРАЦИЯ [ значение ]

(SELECT имя_столбца

FROM имя_таблицы

[ WHERE ])

В следующем примере из таблицы EMPLOYEE_PAY_TBL удаляется запись с информацией о служащем по имени BRANDON GLASS. Табельный номер этого служащего не известен, но можно создать подзапрос, который найдет этот номер в таблице EMPLOYEEJTBL по значениям столбцов с именами (FIRST_NAME) и фамилиями (LAST_NAME) служащих.

DELETE FROM EMPLOYEE_PAY_TBL WHERE EMP_ID = (SELECT EMP_ID

FROM EMPLOYEE_PAY_TBL

WHERE LAST_NAME a 'GLASS^'

AND FIRST_NAME = 'BRAHDON');

1 строка обновлена.

He забывайте использовать в операторах UPDATE и DELETE ключевое слово WHERE. Если последнее не использовать, будут обновлены или удалены данные всех столбцов За подробностями обратитесь к уроку 5, "Манипуляция данными"

Подзапросы внутри подзапросов

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

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

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

SELECT имя_столбца [, имя_столбца ]

FROM таблица1 [, таблица2 ]

WHERE имя_столбца ОПЕРАЦИЯ (SELECT имя__столбца

FPOM таблица

WHERE имя_столбца ОПЕРАЦИЯ (SELECT имя_столбца FROM таблица

[WHEREимя_столбца ОПЕРАЦИЯ значение]))

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

Ввод:

SELECT CUST_ID, CUST_NAME

FROM CUSTOMER_TBL

WHERE CUST_ID IN (SELECT O.CUST_ID

FROM ORDERS_TBL O, PRODUCTS_TBL P WHERE 0 PROD_ID = P.PROD_ID

AND O.QTY * P.COST > (SELECT STJM(COST)

FROM PRODUCTS_TBL));

Вывод:

CUST_ID CUST_NAME

287 GAVINS PLACE

43 SCHYLERS NOVELTIES

2 строки выбраны.

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

Вот как последовательно выполнялись запросы в данном операторе.

Ввод:

SELECT SUM(COST) FROM PRODUCTS_TBL));

Вывод:

SUM(COST)

138.08 1 строка выбрана.

Ввод:

SELECT O.CUST_ID

FROM ORDERS_TBL O, PRODUCTSJTBL P

WHERE О PROD_ID = P.PROD_ID

AND O.QTY * P.COST > 138.08;

Вывод:

CUST_ID

2 строки выбраны.

После подстановки в главный запрос результатов внутреннего подзапроса главный запрос принимает следующий вид

SELECT CUST_ID, CUST_NAME

FROM CUSTOMER_TBL

WHERE CUST_ID IN (SELECT O.CUST_ID

FROM ORDERS_TBL O, PRODUCTS_TBL P WHERE О PROD_ID = P.PROD_ID

AND O.QTY * P.COST > 138.08);

А вот что получается после выполнения следующего подзапроса

Ввод:

SELECT CUST_ID, CUST_NAME

FROM CUSTOMER_TBL

WHERE CUST_ID IN '287•,'43');

Вывод:

Поэтому в результате имеем

CUST_ID CUST_NAME

287 GAVINS PLACE

43 SCHYLERS NOVELTIES

2 строки выбраны.

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

Связанные подзапросы

Связанные подзапросы допускаются во многих реализациях SQL. Концепция связанного подзапроса определяется стандартом ANSI SQL и поэтому рассматривается здесь. Связанный подзапрос — это подзапрос, зависящий от информации, предоставляемой главным запросом.

В следующем примере в подзапросе определение связи между таблицами CUSTOMER_TBL И ORDERSJTBL использует псевдоним таблицы CUSTOMERJTBL (С), определенный в главном запросе. Этот оператор возвращает имена всех покупателей, заказавших более 10 единиц товара.

Ввод:

SELECT C.CUST_NAME

FROM CUSTOMER_TBL С

WHERE 10 < (SELECT SUM(O.QTY)

FROM ORDERS_TBL О

WHERE O.CUST_ID =C.CUST_ID);

Вывод:

CUST_NAME

SCOTTYS MARKET

SCHYLERS NOVELTIES

MARYS GIFT SHOP

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

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

Ввод:

SELECT C.CUST_NAME, SUM(O.QTY)

FROM CUSTOMER_TBL С,

ORDERS_TBL О GROUP BY CUST_NAME;

Вывод:

CUSTJMAME SUM(O.QTY)

GAVINS PLACE 10

LESLIE GLEASON 1

MARYS GIFT SHOP 100

SCHYLERS NOVELTIES 25

SCOTTYS MARKET 20

WENDY WOLF 2

6 строк выбраны.

Ключевое слово GROUP BY здесь требуется потому, что по отношению ко второму столбцу используется итоговая функция SUM. Это позволяет подсчитать суммы для каждого из заказчиков В предыдущем примере ключевое слово GROUP BY не требовалось, поскольку там функция зим использовалась для суммирования всех результатов запроса, выполняемого для каждого конкретного заказчика.

Резюме

Попросту говоря, подзапрос представляет собой запрос, выполняемый в рамках другого запроса для задания дополнительных условий на выводимые данные. Подзапрос можно использовать в выражениях ключевых слов WHERE и HAVING. Подзапросы обычно используют в других запросах (операторах DQL — языка запросов к данным), но подзапросы можно использовать и в операторах DML (языка манипуляций данными) таких, как INSERT, UPDATE и DELETE. Все основные правила использования операторов языка манипуляций данными применимы и при использовании в них подзапросов.

Синтаксис подзапросов практически не отличается от синтаксиса обычного запроса, имеются лишь небольшие ограничения. Одним из таких ограничений является запрет на использование в подзапросах ключевого слова ORDER BY, однако, вместо него можно использовать ORDER BY, чем достигается практически тот же эффект. Подзапросы используются для размещения в запросах условий, точные данные для которых не известны, тем самым расширяя возможности и гибкость SQL.

Лекция 6

Проектирование реляционных БД на основе принципов нормализации

Литература

• Базы данных: модели реализация / Т. С. Карпова. - СПб.: Питер, 2001. - 304 с.: ил.
• Хомоненко А. Д., Цыганков В. М., Мальцев М. Г. Базы данных: Учебник для высших учебных заведений /Под. ред. проф. А. Д. Хомоненко. - СПб.: КОРОНА принт, 2000. - 416 с.

• Проектирование реляционных БД на основе принципов нормализации
• Системный анализ предметной области
• Пример описания предметной области
• Даталогическое проектирование

Проектирование реляционных БД на основе принципов нормализации

Что такое проект? Это схема — эскиз некоторого устройства, который в дальнейшем будет воплощен в реальность. Что такое проект реляционной базы данных? Это набор взаимосвязанных отношений, в которых определены все атрибуты, заданы первичные ключи отношений и заданы еще некоторые дополнительные свойства отношений, которые относятся к принципам поддержки целостности и будут более подробно рассмотрены в главе 9. Почему именно взаимосвязанных отношений? Потому что при выполнении запросов мы производим объединение отношений и одни и те же значения должны в разных отношениях-таблицах обозначаться одинаково. Действительно, если мы в одной таблице оценки будем обозначать цифрами, а в другой словами «отлично», «хорошо» и т. д., то мы не сможем объединить эти таблицы по столбцу Оценка, хотя по смыслу это для нас одно и то же, но то, что интуитивно понятно человеку, совсем не понятно «умному» компьютеру. Это проблема систем с искусственным интеллектом, которые могут решать весьма сложные интеллектуальные задачи, трудные для рядового инженера, но иногда пасуют перед простейшими интуитивными ассоциациями, понятными любому школьнику. И это необходимо учитывать. Поэтому проект базы данных должен быть очень точен и выверен. Фактически проект базы данных — это фундамент будущего программного комплекса, который будет использоваться достаточно долго и многими пользователями. И как в любом здании, можно достраивать мансарды, переделывать крышу, можно даже менять окна, но заменить фундамент, не разрушив всего здания, невозможно. Этапы жизненного цикла базы данных изображены на рис. 6.1. Они аналогичны, в основном, развитию любой программной системы, однако в них есть определенная специфика, касающаяся только баз данных. Более подробно мы будем рассматривать этапы жизненного цикла БД в следующих разделах учебного пособия, потому что термины, которые мы вынуждены применять при этом описании, пока еще неизвестны нашим читателям.

Рис. 6.1. Этапы жизненного цикла БД

Процесс проектирования БД представляет собой последовательность переходов от неформального словесного описания информационной структуры предметной области к формализованному описанию объектов предметной области в терминах некоторой модели. В общем случае можно выделить следующие этапы проектирования:

1. Системный анализ и словесное описание информационных объектов предметной области.
2. Проектирование инфологической модели предметной области — частично формализованное описание объектов предметной области в терминах некоторой семантической модели, например, в терминах Е-модели.
3. Даталогическое или логическое проектирование БД, то есть описание БД в терминах принятой диалогической модели данных.

Физическое проектирование БД, то есть выбор эффективного размещения БД на внешних носителях для обеспечения наиболее эффективной работы приложения.

Если мы учтем, что между вторым и третьим этапами необходимо принять решение, с использованием какой стандартной СУБД будет реализовываться наш проект, то условно процесс проектирования БД можно представить последовательностью выполнения пяти соответствующих этапов (см. рис. 6.2). Рассмотрим более подробно этапы проектирования БД.

Рис. 6.2. Этапы проектирования БД