7.13.18. ( SX.St, PX.Pt, JX.Jt ) WHERE SX.CITY = PX.CITY

AND PX.CITY = JX.CITY AND JX.CITY = SX.CITY

7.13.19.( SX.St, PX.Pt, JX.Jt ) WHERE SX.CITY Ф PX.CITY

OR PX.CITY Ф JX.CITY OR JX.CITY Ф SX.CITY

7.13.20. ( SX.St, PX.Pt, JX.Jt ) WHERE SX.CITY Ф PX.CITY

AND PX.CITY Ф JX.CITY AND JX.CITY Ф SX.CITY

7.13.21.SPJX.Pt WHERE EXISTS SX ( SX.St = SPJX.St AND

SX.CITY = London )

7.13.22.SPJX.Pt WHERE EXISTS SX EXISTS JX

( SX.St = SPJX.St AND SX.CITY = London AND JX.Jt = SPJX.Jt AND JX.CITY = London )

7.13.23. ( SX.CITY AS SCITY, JX.CITY AS JCITY )

WHERE EXISTS SPJX ( SPJX.St = SX.St AND SPJX.Jt = JX.Jt )

7.13.24.SPJX.Pt WHERE EXISTS SX EXISTS JX

( SX.CITY = JX.CITY AND SPJX.St = SX.St AND SPJX.Jt = JX.Jt )

7.13.25.SPJX.Jt WHERE EXISTS SX EXISTS JX

( SX.CITY Ф JX.CITY AND SPJX.St = SX.St AND SPJX.Jt = JX.Jt )

7.13.26. ( SPJX.Pt AS XPt, SPJY.Pt AS YPt )

WHERE SPJX.St = SPJY.St AND SPJX.Pt < SPJY.Pt

7.13.27.COUNT ( SPJX.Jt WHERE SPJX.St = St ( SI ) ) AS N

7.13.28.SUM ( SPJX WHERE SPJX.St = St ( SI )

AND SPJX.Pt = Pt ( PI ), QTY ) AS Q

Замечание. Следующее решение не верно (почему?).

SUM ( SPJX.QTY WHERE SPJX.St = St ( SI )

AND SPJX.Pt = Pt ( PI ), QTY ) AS Q

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

7.13.29. ( SPJX.Pt, SPJX.Jt,

SUM ( SPJY WHERE SPJY.Pt = SPJX.Pt

AND SPJY.Jt = SPJX.Jt, QTY ) AS Q )

7.13.30.SPJX.Pt WHERE

AVG ( SPJY WHERE SPJY.Pt = SPJX.Pt

AND SPJY.Jt = SPJX.Jt, QTY ) > QTY ( 350 )

7.13.3l.JX.JNAME WHERE EXISTS SPJX ( SPJX.Jt = JX.Jl AND

SPJX.SI = S ( SI ) )

7.13.32.PX.COLOR WHERE EXISTS SPJX ( SPJX.Pt = PX.Pt AND

SPJX.St = SI ( SI ) )

7.13.33.SPJX.Pt WHERE EXISTS JX ( JX.CITY = London AND

JX.Jl = SPJX.Jt )

7.13.34.SPJX.Jt WHERE EXISTS SPJY ( SPJX.PI = SPJY.Pl AND

SPJY.St = St ( SI ) )

7.13.35.SPJX.St WHERE EXISTS SPJY EXISTS SPJZ EXISTS PX , ( SPJX.Pt = SPJY.Pl AND SPJY.St = SPJZ.St AND SPJZ.Pt = PX.PI AND PX.COLOR = COLOR ( Red ) )

7.13.36.SX.SI WHERE EXISTS SY ( SY.St = S ( SI ) AND

SX.STATUS < SY.STATUS )

7.13.37.JX.Jl WHERE FORALL JY ( JY.CITY > JX.CITY ) или JX.Jl WHERE JX.CITY = MIN ( JY.CITY )

7.13.38.SPJX.Jt WHERE SPJX.Pt = Pt ( PI ) AND

AVG ( SPJY WHERE SPJY.Pl = Pt ( PI )

AND SPJY.Jt = SPJX.Jt, QTY ) > MAX ( SPJZ.QTY WHERE SPJZ.Jt = Jt ( Jl ) )

7.13.39.SPJX.St WHERE SPJX.Pt = Pt ( PI ) AND SPJX.QTY > AVG ( SPJY

WHERE SPJY.Pl = Pt ( PI ) AND SPJY.Jt = SPJX.Jt, QTY )

7.13.40.JX.Jl WHERE NOT EXISTS SPJX EXISTS SX EXISTS PX ( SX.CITY = London AND PX.COLOR = COLOR ( Red ) AND SPJX.St = SX.SI AND SPJX.Pt = PX.Pt AND SPJX.Jt = JX.Jl )

7.13.41.JX.Jl WHERE FORALL SPJY ( IF SPJY.Jt = JX.Jl

THEN SPJY.St = St ( SI ) END IF )

7.13.42.PX.PI WHERE FORALL JX

( IF JX.CITY = London THEN EXISTS SPJY ( SPJY.Pl = PX.Pt AND SPJY.Jt = JX.Jl )

END IF )

7.13.43.SX.SI WHERE EXISTS PX FORALL JX EXISTS SPJY ( SPJY.St = SX.SI AND

SPJY.P# = PX.P# AND SPJY.J# = JX.J# )

7.13.44.JX.J# WHERE FORALL SPJY ( IF SPJY.S# = S# { SI ) THEN

EXISTS SPJZ ( SPJZ.J# = JX.J# AND SPJZ.P# = SPJY.P# )

END IF )

7.13.45. RANGEVAR VX RANGES OVER

( SX.CITY ), ( PX.CITY ), ( JX.CITY ) ;

VX.CITY

7.13.46.SPJX.P# WHERE EXISTS SX ( SX.S# = SPJX.S# AND

SX.CITY = London ) OR EXISTS JX { JX.J# = SPJX.J* AND JX.CITY = London )

7.13.47. ( SX.S#, PX.P# )

WHERE NOT EXISTS SPJX ( SPJX.S# = SX.S# AND

SPJX.P* = PX.P# )

7.13.48. { SX.S# AS XS#, SY.S# AS YS# )

WHERE FORALL PZ

( ( IF EXISTS SPJX { SPJX.S# = SX.S# AND

SPJX.P* = PZ.P# ) THEN EXISTS SPJY ( SPJY.S# = SY.S# AND SPJY.P* = PZ.P# )

END IF )

( IF EXISTS SPJY ( SPJY.S* = SY.S# AND SPJY.P# = PZ.P# ) THEN EXISTS SPJX ( SPJX.S# = SX.S# AND SPJX.Pt = PZ.Pt )

END IF ) )

7.13.49.{ SPJX.St, SPJX.Pt, ( SPJY.Jt, SPJY.QTY WHERE

SPJY.St = SPJX.St AND SPJY.Pt = SPJX.Pt ) AS JQ )

7.13.50. Пусть R - это результат вычисления выражения из ответа к предыдущему уп ражнению. Тогда имеем следующее.

RANGEVAR RX RANGES OVER R , RANGEVAR RY RANGES OVER RX.JQ ;

(RX.St, RX.Pt, RY.Jt, RY.QTY )

Мы несколько расширили синтаксис и семантику параметра <определени переменной кортежа>. Идея состоит в том, что определение переменной RY зави сит от переменной RX (обратите внимание на то, что оба определения разделен! запятыми, а не точками с запятыми, и поэтому связаны в одну операцию). Даль нейшее обсуждение этого вопроса приводится в [3.3].