8.9. Резюме

На этом завершается обзор основных возможностей стандарта SQL ("SQL/92”) начатый с обсуждения основных объектов данных. В этой главе рассматривались следующие ключевые операторы DDL:

CREATE DOMAIN CREATE TABLE

ALTER DOMAIN ALTER TABLE

DROP DOMAIN DROP TABLE

Еще два оператора DDL, create и drop view, обсуждаются далее в этой книге.

1. Относительно доменов было указано, что в SQL они весьма далеки от настоящих реляционных доменов; в сущности, домены SQL — это не более чем просто сокращение. Точнее, домены SQL обеспечивают: спецификации типов данных на уровне доменов; определения по умолчанию на уровне доменов; ограничения целостности на уровне доменов. Были кратко описаны скалярные типы данных SQL, но опущены многие сложные аспекты доменов в стиле SQL.

2. Относительно базовых таблиц отмечалось, что таблицы SQL в общем отличаются от настоящих отношений по крайней мере следующим: в них допустимы одинаковые строки и столбцы упорядочены слева направо. В базовых таблицах должно не менее одного столбца, нуль или более объявленных потенциальных ключей (среди которых, по крайней мере, один может быть объявлен как первичный ключ), нуль или более объявленных внешних ключей, нуль или более объявленных проверяемых ограничений. Поддерживаются следующие правила обновления и удаления внешних ключей:NO ACTION,CASCADE, SET DEFAULT И SET NULL.

Также была кратко описана информационная схема, которая содержит набор предписанных представлений гипотетической "схемы определения".

Далее обсуждались операции обработки данных.

1. Операции выборки (которые в основном предназначены для табличных выражений). Обычно такие операции содержат единственное выражение выборки, но также поддерживаются различные виды явных выражений операции join, а выражения соединения и выборки могут комбинироваться вместе произвольным образом с помощью операторов union, intersect и except. Упоминалось также использование оператора order by для назначения таблицы, которая является результатом вычисления табличного выражения (любого вида).

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

• Базовая инструкция select, включая использование ключевого слова DISTINCT, скалярные выражения, инструкцию "select *" и введение имен результирующих столбцов.

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

• Инструкция where, включая использование подзапросов и функции EXISTS.

• Инструкции group by и having, включая использование итоговых функций COUNT,SUM, AVG, MAX И MIN. Кроме того, описан концептуальный алгоритм вычисления(т.е схема формального определения) для выражений выборки.

3. Операции обновления INSERT, UPDATE, DELETE.

Затем подробно рассматривались табличные выражения, условные выражения и скалярные. Особо отмечалось, что вы­ражение выборки, в результате вычисления которого получается таблица с одной строкой и одним столбцом, может быть использовано как скалярное значение (например, в инст­рукциях SELECT ИЛИ WHERE).

И, наконец, были описаны основные возможности встроенного языка SQL. Глав­ная идея встроенного SQL заключается в двухрежимном принципе, в соответствии с которым (насколько это возможно) любое выражение SQL, используемое интерак­тивно, можно также использовать и в прикладной программе. Исключение из этого принципа возникает при использовании многострочных операций выборки, кото­рые требуют использования курсора для преодоления разрыва между уровнями вы­борки "множество-за-раз" в SQL и "строка-за-раз" в базовом языке, таком как PL/I. (Возможно, здесь стоит упомянуть, что кроме PL/I, стандарт SQL [8.1] также под­держивают языки Ada, С, COBOL, Fortran, MUMPS и Pascal).

После ряда необходимых (хотя большей частью синтаксических) предваритель­ных замечаний, включая, в частности, краткое объяснение назначения переменной sqlstate, обсуждались операции (единичный select, insert, update и delete), для кото­рых не нужен курсор. Затем рассматривались операции, для которых необходимы курсоры, в частности операторы declare cursor, open, fetch, close и current-форму опе­раторов update и delete. И, наконец, было дано краткое описание понятия динамиче­ского SQL (упоминались операторы prepare и execute).

Упражнения

8.1. Дайте определение данных с помощью SQL для базы данных поставщиков, деталей и проектов.

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

8.3. В этой главе мы описали оператор create table, как он определен в стан­дарте SQL [8.1]. Однако многие коммерческие продукты поддерживают дополнительные опции этого оператора, обычно связанные с индексами, размещением на дисковом пространстве и другими вопросами реализации, что противоречит цели физической независимости данных и межсистемной совместимости. Исследуйте доступный вам продукт, поддерживающий SQL. Верны ли предыдущие замечания для этого продукта? В частности, какие дополнительные опции оператора create table поддерживаются в этом продукте?

8.4. И снова исследуйте доступный вам продукт, поддерживающий SQL. Под­держивается ли в нем информационная схема? Если нет, то каким образом поддерживается каталог?

8.5. Покажите, что язык SQL реляционно полный (см. главу 6) в том смысле, что для любого выражения реляционной алгебры существует семантически экви­валентное выражение SQL.

8.6. Есть ли в SQL эквиваленты операций extend и summarize?

8.7. Есть ли в SQL эквивалент реляционной операции присвоения?

8.8. Есть ли в SQL эквиваленты операции сравнения?

8.9. Дайте как можно больше различных формулировок на языке SQL запроса “Получить имена поставщиков , поставляющих деталь P2”.

8.10. Есть два формально эквивалентных подхода к части реляционной модели, связанной с обработкой данных ,- исчисление и алгебра. А, значит, существует два стиля построения языка запросов. Например, язык QUEL бесспорно, основан на исчислении, так же и язык QBE; а язык ISBL системы PRTV [6.8] основан на алгебре. А SQL? Основан на алгебре или на исчислении?

8.11. Дайте решения на языке SQL к упр. 6.13-6.48.

8.12. Сформулируйте на языке SQL следующие задачи обновления:

а) вставить нового поставщика S10 в таблицу S с именем Smith, York; статус еще не известен;

б) изменить цвет всех красных деталей на оранжевый;

в) удалить все проекты, для которых нет поставок.

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

8.14. Даны таблицы

CREATE TABLE PARTS

( Р# . . . , DESCRIPTION ... ,

PRIMARY KEY ( P# ) ) ;

CREATE TABLE PART_STRUCTURE

( MAJOR_P# ... , MINOR_P# ... , QTY ... ,

PRIMARY KEY ( MAJOR_P#, MINOR_P# ),

FOREIGN KEY ( MAJOR_P# ) REFERENCES PARTS, FOREIGN KEY ( MINOR_P# ) REFERENCES PARTS ) ;

В таблице PART_STRUCTURE показано, какая деталь (MAJOR_P#) содержит другую деталь (MINOR_P#) как компонент первого уровня. Напишите программу на языке SQL для получения списка всех компонентов данной детали на все уровнях. Следующие значения для примера могут помочь вам наглядно представить задачу. PART_STRUCTURE

MAJOR_P#	MINOR_P#	QTY
P1 P1 P2 P2 P3 P4 P5	P2 P3 P3 P4 P5 P5 P6	2 4 1 3 9 8 3

Список литературы

8.1. Документ ISO/IEC 9075:1992. Database Language SQL. (Также доступен как документ ANSI ХЗ.135-1992.) Текущая версия официального стандарта SQL, неформально известного как SQL2, SQL-92, SQL/92.

8.2. X/Open. Structured Query Language (SQL): CAE Specification C201. — 1992. Описание стандарта X/Open SQL.

8.3. FIPS PUB 127-2. Database Language SQL. U.S. Department of Commerce, National Institute of Standards and Technology, 1992.

Описание стандарта SQL организации Федеральной обработки информации (Federal Information Processing — FIPS).

8.4. Документ IBM № SC26-4348. Systems Application Architecture Common Programming Interface: Database Reference. IBM Corp.

Описание стандарта IBM SAA SQL.

8.5. Date C.J., Darwen H. A Guide to the SQL Standard. — Reading, Mass.:

Addison-Wesley, 1993.

Некоторые из разделов настоящей главы основаны на материале этой книги, которая является полным руководством по SQL/92. К учебным пособиям по SQL/92 относятся также [8.6, 8.7].

8.6. Cannan S., Otten G. SQL — The Standard Handbook. — Maidenhead, UK:

McGraw-Hill Intern., 1993.

8.7. Melton J., Simon A.R. Understanding The New SQL: A Complete Guide. — San Mateo, Calif: Morgan Kaufmann, 1993.

8.8. Chamberlin D.D., Boyce R.F. SEQUEL: A Structured English Query Language // Proc. ACM SIGMOD Workshop on Data Description, Access, and Control.— Ann Arbor, Mich., 1974.

В статье впервые представлен язык SQL (или SEQUEL, как он назывался вначале; название впоследствии по юридическим причинам было изменено).

8.9. Astrahan M.M., Lorie R.A. SEQUEL-XRM: A Relational System // Proc. ACM Pacific Regional Conference. — San Francisco, Calif, 1975.

Описан первый прототип реализации языка SEQUEL— начальной версии SQL [8.8]. Смотрите также [8.12,8.13], которые выполняют аналогичную функцию для System R.

8.10. Reisner P., Boyce R.F., Chamberlin D.D. Human Factors Evaluation of Two Data Base Query Languages: SQUARE and SEQUEL // Proc. NCC 44. — Anaheim, Calif; Montvale, N.J.: AFIPS Press, 1975.

Язык SEQUEL [8.8], предшественник SQL, был основан на более раннем языке SQUARE. Эти два языка в основном совпадали, но в SQUARE исполь­зовался математический синтаксис, а в SEQUEL — ключевые слова из анг­лийского языка, такие как SELECT, FROM, WHERE и т.д. В статье приведе­ны отчеты по ряду исследований, в которых изучалась практичность двух языков, при этом использовались студенты колледжа. В результате этой ра­боты в SEQUEL были внесены некоторые изменения [8.11].

8.11. Chamberlin D.D. et al. SEQUEL/2: A Unified Approach to Data Definition, Manipulation, and Control // IBM J. R&D. — 1976. —20, № 6; 1977. —21, № 1.

Опыт реализации предыдущего прототипа SEQUEL, описанного в [8.9], и ре­зультаты проверок практичности, отчет о которых содержится в [8.10], при­вели к разработке новой версии языка, названной SEQUEL/2. Язык, поддерживаемый системой System R [8.12,8.13], был в основном похож на SEQUEL/2, плюс некоторые расширения, появившееся в результате опыта пользователей [8.14].

8.12. Astrahan M.M. et al. System R: Relational Approach to Database Management// ACM TODS. — 1976. — 1, № 2.

Система System R была реализацией основного прототипа (ранней версии) языка SQL. В статье описывается архитектура System R в том виде, в каком она была изначально запланирована.

8.13. Blasgen M.W. et al. System R: An Architectural Overview // IBM Sys.J.- 1981.—20,№1.

Описывается архитектура System R на момент, когда система была полностью реализована.

8.14. Chamberlin D.D. A Summary of User Experience with the SQL Data Sublanguage // Proc. Intern. Conf. on Database.— Aberdeen, Scotland, 1980. (Также IBM Research Report RJ2767. — 1980.)

В статье обсуждается ранний опыт использования системы System R и предложены некоторые дополнения языка SQL в свете этого опыта. Некоторые из этих дополнений — операторы EXISTS, LIKE (не обсуждались в этой главе), PREPARE и EXECUTE — действительно были реализованы в окончательной версии System R.

8.15. Chamberlin D.D., Gilbert A.M., Yost R.A. A History of System R and SQL /DATA System // Proc. 7th Intern. Conf. on Very Large Data Bases. — Cannes, France, 1981.

Обсуждаются уроки, извлеченные из прототипа системы System R, а также описана эволюция этого прототипа до первого семейства реляционных продуктов IBM, а именно SQL/DS (переименованного в "DB2 for VM and VSE’)

8.16. Chamberlin D.D. et al. A History and Evaluation of System R // CACM 1981.—24, № 10.

Описываются три основные фазы развития проекта System R (предварительный прототип, многопользовательский прототип и оценка); основное внимание уделено технологиям компиляции и оптимизации, которые впервые использовались в System R. Некоторая часть этой статьи пересекается с [8.15]. Замечание. Интересно сравнить эту статью с [7.14], в которой описана история университетского проекта INGRES.

8.17. Date C.J. A Critique of the SQL Database Language // ACM SIGMOD Record.- 1984.— 14, №3. (Переиздано: C.J. Date. Relational Database: Selected Writings. — Reading, Mass.: Addison-Wesley, 1986.)

Язык SQL далек от совершенства. В статье представлен критический анализ принципиальных недостатков этого языка (в основном исходя из требований к формальному компьютерному языку вообще, а не из требований к языку баз данных). Замечание. Некоторая критика из этой статьи не касается SQL/92

8.18. Date C.J. What's Wrong with SQL? // C.J. Date. Relational Database Writings 1985-1989. — Reading, Mass.: Addison-Wesley, 1990.

Обсуждаются некоторые недостатки языка SQL в дополнение к описанным в [8.17]. Замечание. И опять некоторая критика из этой статьи не касается SQL/92.

8.19. Date C.J. How SQL Missed the Boat // Database Programming & Design.-1993.—6, №9.

8.20. Date CJ. SQL dos and Don 4s // CJ. Date. Relational Database Writings 1985-1989. — Reading, Mass.: Addison-Wesley, 1990.

В статье предложены некоторые практические советы о том, как использо­вать SQL так, чтобы избежать потенциальных ловушек, вызванных недостат­ками SQL, описанными в [8.17-8.19], и получить максимальные преимуще­ства по продуктивности, портативности, связности и т.п.

8.21. Negri M., Pelagatti S., Sbattella L. Formal Semantics of SQL Queries // ACM TODS.—1991.—16, №3.

Немного переработанная цитата из резюме: "Семантика запросов SQL фор­мально определена с помощью набора правил, определяющих преобразова­ние на основе синтаксиса запроса SQL в формальную модель, называемую расширенным исчислением трехзначных предикатов (Extended Three Valued Predicate calculus— E3VPC), которая большей частью основана на хорошо известных математических понятиях. Приведены также правила для преобра­зования общего выражения E3VPC в каноническую форму; ...проблемы, по­добные анализу эквивалентности запросов SQL, полностью решены." Однако заметьте, что рассматриваемый здесь диалект SQL — это только первая вер­сия стандарта ("SQL/86"), а не SQL/92.

Ответы к некоторым упражнениям

8.1. CREATE DOMAIN S# CHAR(5) ;

CREATE DOMAIN NAME CHAR(20) ;

CREATE DOMAIN STATUS NUMERIC(5) ;

CREATE DOMAIN CITY CHAR (15) ;

CREATE DOMAIN P# CHAR(6) ;

CREATE DOMAIN COLOR CHAR(6) ;

CREATE DOMAIN WEIGHT NUMERIC(5) ;

CREATE DOMAIN J# CHAR(4) ;

CREATE DOMAIN QTY NUMERIC(9) ;

CREATE TABLE S

( S# S#,

SNAME NAME,

STATUS STATUS,

CITY CITY,

PRIMARY KEY ( S# ) ) ;

CREATE TABLE P

( P# P#,

PNAME NAME,

COLOR COLOR, WEIGHT WEIGHT,

CITY CITY, PRIMARY KEY ( P# ) )

CREATE TABLE J

( J# J#,

JNAME NAME,

CITY CITY,

PRIMARY KEY ( J# ) ) ;

CREATE TABLE SPJ

( S# S#,

P# P#,

J# J#,

QTY QTY,

PRIMARY KEY ( S#, P#, J# ),

FOREIGN KEY ( S# ) REFERENCES S,

FOREIGN KEY ( P# ) REFERENCES P,

FOREIGN KEY ( J# ) REFERENCES J,

8.2. DROP TABLE SPJ RESTRICT ;

DROP TABLE S RESTRICT ;

DROP TABLE P RESTRICT ;

DROP TABLE J RESTRICT ;

DROP DOMAIN S# RESTRICT;

DROP DOMAIN NAME RESTRICT;

DROP DOMAIN STATUS RESTRICT;

DROP DOMAIN CITY RESTRICT;

DROP DOMAIN P# RESTRICT;

DROP DOMAIN COLOR RESTRICT;

DROP DOMAIN WEIGHT RESTRICT;

DROP DOMAIN J# RESTRICT;

DROP DOMAIN QTY RESTRICT;

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

Начнем с замечания, что язык SQL фактически поддерживает оператор "переименования" реляционной алгебры rename благодаря введению в SQL/92 необязательной спецификации "as column', где column— новое имя столбца. Следовательно, мы можем быть уверены, что все таблицы имеют правильные имена столбцов, в частности операнды произведения, объединения и вычитания удовлетворяют требованиям (в нашей версии) алгебры по отношению к наименованию столбцов. Более того, указанные требования, к наименованию столбцов операндов действительно удовлетворяются: правила наследования имен столбцов в SQL фактически совпадают с такими же в алгебре (в нашей версии).

Вот выражения SQL, соответствующие пяти примитивным операциям:

A WHERE p	SELECT*FROM A WHERE p
A [x, y ,…..z]	SELECT DISTINCT x, y,…, z FROM A
A TIMES B	A CROSS JOIN B
A UNION B	SELECT*FROM A UNION SELECT*FROM B
A MINUS B	SELECT*FROM A EXCEPT SELECT*FROM B

Алгебра SQL

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

8.6. Ответ «да» в обоих случаях. Первое выражение

EXTEND A ADD exp AS Z

может быть представлено в SQL как

SELECT A.*, exp AS Z

FROM ( A ) AS A

Здесь заключенное в скобки А представляет собой табличную ссылку произвольной сложности ( соответствующую операнду А оператора EXTEND ); второе А – переменная области значений.

Второе выражение

SUMMARIZE A BY (A1, A2,…,An ) ADD exp AS Z

Может быть представлено в SQL как

SELECT A.A1, А.А2,…,А.Аn, exp AS Z

FROM (A) AS A

GROUP BY A.А1, А.А2,…,А.Аn

8.7 Язык SQL не поддерживает реляционного присвоения непосредственно. Однако при условии, что мы считаем целевое отношение именованным отношением, можно моделировать такую операцию довольно просто. Например, присвоение

R := X;

(где R – именованное отношение, а Х – произвольное реляционное выражение ) можно успешно воспроизвести такой последовательностью операций SQL:

DELETE FROM R;

INSERT INTO R QX;

(где QХ – эквивалент Х в языке SQL).

8.8. Язык SQL не поддерживает реляционного сравнения непосредственно. Однако ­ такую операцию можно смоделировать , хотя и в виде очень громоздкого выражения. Например, сравнение

А=В;

(где А и В – отношения ) можно успешно смоделировать таким выражением SQL :

NOT EXISTS ( SELECT * FROM A

WHERE NOT EXISTS ( SELECT * FROM B

WHERE A-row = B -row ) ) ;

( где А-row и B-row – конструкции строк, представляющие соответственно целую строку А и целую строку В).

8.9. SELECT DISTINCT S.NAME

FROM S

WHERE S.S# IN

(SELECT SP.S#

FROM SP

WHERE SP.P# = “P2” );

SELECT DISTINCT T.SNAME

FROM ( S NATURAL JOIN SP ) AS T

WHERE T.P# = “P2”;

SELECT DISTINCT T.SNAME

FROM (S JOIN SP ON S.S# = SP.S# AND SP.P#=”P2”)AS T;

SELECT DISTINCT T.NAME

FROM( S JOIN SP USING S# ) AS T

WHERE T.P#=”P2”

SELECT DISTINCT S.NAME

FROM S

WHERE S.S# = ANY

( SELECT SP.S#

FROM SP

WHERE SP.S# = “P2”) ;

SELECT DISTINCT S.NAME

FROM S

WHERE EXISTS

( SELECT*

FROM SP

WHERE SP.S# = S.S#

AND SP.P# = “P2”);

SELECT DISTINCT S.NAME

FROM S, SP

WHERE S.S# = SP.S#

AND SP.P = “P2”;

SELECT DISTINCT S.NAME

FROM S

WHERE 0 <

( SELECT COUNT(*)

FROM SP

WHERE SP.S# = S.S#

AND SP.S# = ”P2”);

SELECT DISTINCT S.NAME

FROM S

WHERE “P2” IN

(SELECT SP.P#

FROM SP

WHERE SP.S# = S.S# );

SELECT DISTINCT S.SNAME

FROM S

WHERE 'P2' = ANY

( SELECT SP.P#

FROM SP

WHERE SP.S# = S.S# ) ;

SELECT S.SNAME

FROM S, SP

WHERE S.S# = SP.S#

AND SP.P# = 'P2'

GROUP BY S.SNAME ;

Дополнительный вопрос: какие выводы можно сделать из этого?

8.10. Язык SQL в действительности является гибридом алгебры и исчисления. На­пример, в нем представлены и квантор существования exists (исчисление), и оператор union (алгебра). Интересно, что первоначально язык SQL разраба­тывался как отличный и от алгебры и от исчисления (см. [8.8]); считалось, что конструкция "IN подзапрос" более дружественная по отношению к поль­зователю, чем явные соединения (и пр.) алгебры и кванторы исчисления. Од­нако, как оказалось, конструкция "IN подзапрос" (очевидно) не отвечала предъявляемым требованиям и поэтому возникла необходимость в расшире­нии первоначального языка различными способами. Сейчас ситуация такова, что, по иронии судьбы, возможности конструкции "IN подзапрос" из языка SQL могли бы быть исключены полностью фактически без потери ка­ких-либо функций языка (хотя можно согласиться с тем, что конструкция "IN подзапрос" иногда воспринимается интуитивно легче, чем альтернатив­ные конструкции). Более подробно этот вопрос изложен в [8.17-8.19].

8.11. Мы перенумеровали решения в виде 8.11-п, что соответствует номерам 6.n в упражнениях главы 6.

8.11.13. SELECT *

FROM J ;

или просто:

TABLE J ;

8.11.14. SELECT J.*

FROM J

WHERE J.CITY = 'London' ;

8.11.15. SELECT DISTINCT SPJ.S#

FROM SPJ

WHERE SPJ.J# = 'J1' ;

8.11.16. SELECT SPJ.

FROM SPJ

WHERE SPJ.QTY >= 300

AND SPJ.QTY <= 750 ;

8.11.17. SELECT DISTINCT P.COLOR, P.CITY

FROM P ;

8.11.18. SELECT S.S#, P.P#, J.J#

FROM S, P, J

WHERE S.CITY = P. CITY

AND P.CITY = J. CITY ;

8.11.19. SELECT S.S#, P.P#, J.J#

FROM S, P, J

WHERE NOT ( S.CITY ” P.CITY AND

P.CITY = J.CITY ) ;

8.11.20. SELECT S.S#, P.P#, J.J#

FROM S, P, J

WHERE S.CITY <> Р.CITY

AND P.CITY <> J.CITY

AND J.CITY <> P.CITY ;

8.11.21. SELECT DISTINCT SPJ.P#

FROM SPJ

WHERE ( SELECT S.CITY

FROM S

WHERE S.S# = SPJ.S# ) = 'London' ;

8.11.22. SELECT DISTINCT SPJ.P#

FROM SPJ

WHERE ( SELECT S.CITY

FROM S

WHERE S.S# = SPJ.S# ) = 'London”

AND ( SELECT J.CITY

FROM J

WHERE J.J# = SPJ.J# ) = 'London' ;

8.11.23. SELECT DISTINCT S.CITY AS SCITY, J.CITY AS JCITY FROM S, J

WHERE EXISTS

( SELECT *

FROM SPJ

WHERE SPJ.S# = S.S#

AND SPJ.J# = J.J# ) ;

8.11.24. SELECT DISTINCT SPJ.P#

FROM SPJ

WHERE ( SELECT S.CITY

FROM S

WHERE S.S# = SPJ.S# )

( SELECT J.CITY

FROM J

WHERE J.J# = SPJ.J# ) ;

8.11.25. SELECT DISTINCT SPJ.J#

FROM SPJ

WHERE ( SELECT S.CITY

FROM S

WHERE S.S# = SPJ.S# ) <>

( SELECT J.CITY

FROM J

WHERE J.J# = SPJ.J# ) ;

8.11.26. SELECT DISTINCT SPJX.P# AS PA, SPJY.P# AS PB

FROM SPJ AS SPJX, SPJ AS SPJY

WHERE SPJX.S# = SPJY.S#

AND SPJX.P# < SPJY.P# ;

8.11.27. SELECT COUNT ( DISTINCT SPJ.J# ) AS N

FROM SPJ

WHERE SPJ.S# = 'Sl' ;

8.11.28. SELECT SUM ( SPJ.QTY ) AS X

FROM SPJ

WHERE SPJ.S# = 'Sl'

AND SPJ.P# = 'PI' ;

8.11.29. SELECT SPJ.P#, SPJ.J#, SUM ( SPJ.QTY ) AS Y

FROM SPJ

GROUP BY SPJ.P#, SPJ.J# ;

8.11.30. SELECT DISTINCT SPJ.P#

FROM SPJ

GROUP BY SPJ.P#, SPJ.J#

HAVING AVG ( SPJ. STY ) > 320 ;

8.11.31. SELECT DISTINCT J.JNAME

FROM J, SPJ

WHERE J.J# = SPJ.J#

AND SPJ.S# = 'Sl' ;

8.11.32. SELECT DISTINCT P. COLOR

FROM P, SPJ

WHERE P.P# = SPJ.P#

AND SPJ.S# = 'Sl' ;

8.11.33. SELECT DISTINCT SPJ.P#

FROM SPJ, J

WHERE SPJ.J# = J.J#

AND J.CITY = 'London' ;

8.11.34. SELECT DISTINCT SPJX.J#

FROM SPJ AS SPJX, SPJ AS SPJY

WHERE SPJX. P# = SPJY.P#

AND SPJY.S# = 'S1' ;

8.11.35. SELECT DISTINCT SPJX.S#

FROM SPJ AS SPJX, SPJ AS SPJY, SPJ AS SPJZ WHERE SPJX.P# = SPJY.P#

AND SPJY.S# = SPJZ.S#

AND ( SELECT P.COLOR

FROM P

WHERE P.P# = SPJZ.P# ) = 'Red' ;

8.11.36. SELECT S.S#

FROM S

WHERE S.STATUS < ( SELECT S.STATUS

FROM S

WHERE S.S# = 'Sl' ) ;

8.11.37. SELECT J.J#

FROM J

WHERE J.CITY = ( SELECT MIN ( J.CITY )

FROM J ) ;

8.11.38. SELECT DISTINCT SPJX.J#

FROM SPJ AS SPJX

WHERE SPJX.P# = 'Р1'

AND ( SELECT AVG ( SPJY.QTY )

FROM SPJ AS SPJY

WHERE SPJY.J# = SPJX.J#

AND SPJY.P# = 'Р1’ ) >

( SELECT MAX ( SPJZ.QTY )

FROM SPJ AS SPJZ

WHERE SPJZ.J# = 'Jl' ) ;

8.11.39. SELECT DISTINCT SPJX.S#

FROM SPJ AS SPJX

WHERE SPJX.P# = 'Р1'

AND SPJX.QTY > (SELECT AVG ( SPJY.QTY )

FROM SPJ AS SPJY

WHERE SPJY.P# = 'Р1’

AND SPJY.J# = SPJX.J# )

8.11.40. SELECT J.J#

FROM J

WHERE NOT EXISTS

( SELECT *

FROM SPJ, P, S

WHERE SPJ.J# = J.J#

AND SPJ.P# = P.P#

AND SPJ.S# = S.S#

8.11.41.-8.11.48.Оставляем для читателей.

8.14. Это хороший пример задачи, которую с помощью SQL в его обычной форме нельзя решить полностью. Нам нужно "разобрать" данную деталь на п уровней при условии, что n во время написания программы неизвестно. Сравнительно простой способ формирования таких п уровней (если бы это было возможно) мог бы быть реализован с помощью рекурсивной программы, в которой каж­дый рекурсивный вызов создает новый курсор, как показано ниже:

GET LIST ( GIVENP# ) ;

CALL RECURSION ( GIVENP# ) ;

RETURN ;

RECURSION: PROC ( UPPER_P# ) RECURSIVE ;

DCL UPPER_P# ... ;

DCL LOWER_P# ... ;

EXEC SQL DECLARE С "reopenable" CURSOR FOR SELECT MINOR_P#

FROM PART_STRUCTURE

WHERE MAJOR_P# = :UPPER_P# ;

print UPPER_P# ;

/* вывести значение UPPER_P# */

EXEC SQL OPEN С ;

DO for all PART_STRUCTURE rows accessible via С ;

/* выполнить для всех строк PART_STRUCTURE, */

/* доступных через курсор С */

EXEC SQL FETCH С INTO :LOWER_P# ;

CALL RECURSION ( LOWER_P# ) ;

END ;

EXEC SQL CLOSE С ;

END ; /* Конец рекурсии */

Здесь подразумевается, что фиктивная спецификация «reopenable» («повторно открываемый » ) к оператору DECLARE CURSOR означает допустимость операции OPEN даже в том случае, если курсор уже открыт , и в результате такой операции создается новый экземпляр курсора для данного табличного выражения (использующего текущие значения любых базовых переменных , на которые есть ссылки в этом выражении). В дальнейшем подразумевается , что ссылки к такому курсору в операторе fetch (и др.) являются ссылками к «текущему » экземпляру и что оператор close уничтожает именно этот экземпляр и восстанавливает предыдущий экземпляр как "текущий". Другими словами подразумевается, что повторно открываемый курсор формирует стек, который обслуживается операторами open и close так же, как стек обрабатывался бы операторами "push" и "pop". (Операторы работы со стеком "push" и "pop" применяются в языке Assembler. —Прим. ред.)

К сожалению, такие допущения на сегодняшний день чисто гипотетические. В SQL пока нет таких средств, как повторно открываемый курсор (в действительности попытка повторно открыть курсор приведет к ошибке ). Приведенный код недопустим. Но этот пример наглядно показывает, что «повторно открываемые курсоры » были бы очень полезным дополнением к современному SQL.

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

GET LIST ( GIVENP# ) ;

CALL RECURSION ( GIVENP# ) ;

RETURN ;

RECURSION: PROC ( UPPER_P# ) RECURSIVE ;

DCL UPPER_P# ... ;

DCL LOWER_P# ... INITIAL ( ' ' ) ;

EXEC SQL DECLARE С CURSOR FOR

SELECT MINOR_P#

FROM PART_STRUCTURE

WHERE MAJOR_P# = :UPPER_P#

AND MINOR_P# > :LOWER_P#

ORDER BY MINOR_P# ;

print UPPER_P# ;

/* вывести значение UPPER P# */

DO "forever" ;

/* выполнить инструкции ниже */

EXEC SQL OPEN С ;

EXEC SQL FETCH С INTO :LOWER_P# ;

EXEC SQL CLOSE С ;

IF no "lower P#" retrieved THEN RETURN ;

IF "lower P#" retrieved THEN CALL RECURSION ( LOWER_P# )

/* если найден еще один уровень, */

/* то вызвать рекурсивную процедуру RECURSION */

/* с параметром LOWER_P#, */

/* иначе — выход из текущей процедуры */

END ;

END ; /* Конец рекурсии */

Заметьте, что в этом решении тот же курсор используется в каждом вызове рекурсии ( но новые экземпляры UPPER_P# и LOWER_P# создаются динамически каждый раз при вызове рекурсии; в конце вызова эти экземпляры уничтожаются.) В связи с этим фактом мы использовали трюк:

... AND MINOR_P# > :LOWER_P# ORDER BY MINOR_P#

Так что на каждом вызове рекурсии просто игнорируются промежуточные компоненты (lcwer_p#) текущего значения upper_p#, которые уже обработаны.

Обсуждение некоторых альтернативных подходов к этой задаче вы найдете в [8.14].

*Это не совсем то выражение выборки, которое определено выше в этой главе, поскольку здесь еще присутствует инструкция INTO.