     8 

     SQL 

    8.1.  

       ,  SQL      
 .    ,      
       , 
  ,        
   .   ,    . 
     ,  SQL    .    
 [8.1]   600 .   ,   , 
     ;    
     .    ,  
         .  
,     ,     
       .   
(  )     [8.5-8.7]. 
         .    
 ;      ( 
   ).      
 SQL:   (,  ,  
),     .    
   ""  SQL (..   
  SQL     ).  ,  
  . 
    .   SQL,   ,  
,    ..,    
 ,   . 
        .  ,  
    [8.1],    
"SQL/92" ("SQL-92"   "SQL2");    
     SQL (1992) (International Standard 
Database Language SQL).      [8.5],  
        
.     : 
      "#",         ,  
    SQL/92  . 
        ";"    ,   
 SQL/92       SQL   
   . 
           ,  
   ,      
    . 
           SQL/92,  ,   
           
  

     8.  SQL 211 

     SQL/92.  ,    , 
  " "  SQL/92.  , 
  ,     ,   
, ,  . ,  IBM DB2, , 
    SQL/92,  ,    
   ,    . 
      :  SQL   ,  , 
      ,   . 
     SQL  ,  
SQL,          ( ,   
  SQL). 

    8.2.   

             
    (data definition language  DDL)  SQL. 
  DDL: 

     DOMAIN	CREATE TABLE 
    ALTER DCMAIN	ALTER TABLE 
    DROP DCMAIN	DROP TABLE 

           (""  
"dropping") ,       17. 

     

     , ""  SQL     , 
   ;  ,     ,  
      SQL   . 
    SQL       
    (  "S# CHAR(5)"),   
         
.         
   SQL (      
    ). 
        ,     SQL   
. , ,      , 
 . 
         ,      SQL: 
         
  ,  ,   FLOAT  INTEGER 
        -  SQL  " ".  SQL 
        ,  
,      ,  . 
       SQL     .    
 .  ,   " "  
      ,   
       , ..    ,  
   ..       
,      ..     
 ,  ,   . 
       SQL      
,    . 
        SQL         
(..  ,  )     
       . 

    212	 II.   

        SQL    ,   . 
       ,  SQL   , ,   
      ! 

          SQL. 

    .     7,   "["  "]"   
  ,      
  . 

    CREATE DOMAIN domain data-type 
    I default-definition } 
    [ domain-constraint-definition-list ]   ; 

    : 
    1.  domain        
 (data-type).   SQL      
,       . ( 
    , ,  
,  CHAR  CHARACTER.   .) 

    CHARACTER [ VARYING ]	()   INTEGER	DATE 
    BIT [ VARYING ] ()	SMALLINT	TIME 
    NUMERIC (p, g)	FLOAT (p)	TIMESTAMP 
    DECIMAL (p,g)	INTERVAL 

    2.      (default-definition) 
   ,     , 
          
 ,    "DEFAULT default",  default,   
,   ,    niladic- 
(, CURRENT_DATE)  NULL.1 
    . Niladic-  ,    . 
    3.      
(domain-constraint-definition-list)    
,    ,    .  
     ,    
   ,   ,   ( 
 ,   ).   SQL    
     .  
        
   . 

       : 


    CREATE DCMAIN COLOR CHAR(6) DEFAULT '???' CONSTRAINT VALID_COLORS CHECK ( 
VALUE IN 
    ( 'Red', 'Yellow1, 'Blue', 'Green', '???' ) ) ; 

      CREATE TABLE     ( )  
  : 

    CREATE TABLE P   (   . . .   ,   COLOR COLOR,   ...   )   ; 

               
 COLOR   ,        
 "???".      COLOR,     
  
    1      ( )  SQL 
    .        
 . 

     8.  SQL	213 

      ,        
 ,     VALID_COLORS. 
              ALTER 
DCMAIN.  ,        
    (    ,    
)   .       
        . 
     (   )    
 .      [8.1, 8.5]. 
     ,        DROP 
DOMAIN,   : 

    DROP DCMAIN domain option ; 

      option    RESTRICT  CASCADE.  
 :    RESTRICT    ,    
 - ;    CASCADE     
""     (, ,  
      ,    
       ).  
       . 
     [8.1, 8.5]. 

      

        ,     
  SQL . -,   SQL,    
 ,   ,     
 -  . -,   SQL,    
 ,      ;  
   S  S#    ,  SNAME  
  .. 
        .    
   CREATE TABLE ( ,    TABLE 
   ,       ALTER TABLE 
 DROP TABLE,   ).   : 
    CREATE TABLE base-table  ( base-table-element-coimalist )   ; 
         (base-table-element)   
  (column-definition),    
  (base-tabie-constraint-definition).    
(       ),   ,  
 : 

    column representation  [ default-definition ] 

     column   , representation  
    ,      
(default-definition)      ,  
  ,     (  ).  
          , 
   ,   ,  ,   
  NULL, .. NULL   "  ,   ". 
          '. 
        , 
        , 
       " ". 
         . 

    214	 II.   

    .       
"CONSTRAINT constraint",      (  
     ).       . 

      

         . 

    UNIQUE  (  column-commalist ) 

     

    PRIMARY KEY   (  column-commalist ) 

        coiwn-commaiist    .   
       PRIMARY KEY ( 
)     UNIQUE ( ).   
        
  NOT NULL,       . 
    .     ( commaiist) 
    . 

      

         : 

    FOREIGN KEY   (   column-commaiist  ) 
    REFERENCES base-table  [   {  column-commalist )   ] [ ON DELETE option ] 
[  ON UPDATE option  ] 

     option   NO ACTION, CASCADE, SET DEFAULT  SET 
NULL.  

    CASCADE     SET NULL              
CASCADES      NULLIFIES 

    (       ,    
5);   ACTION,            ,    
     RESTRICTED ( . [8.5]),   SET DEFAULT 
     . .    
column-commalist ,      , 
    . 

      

       ( )  : 

    CHECK  (  conditional-egression ) 

              
    ,      
, -   ,   . , 
       ;   
,         ,  
        .  [8.19]  
     .   
     CREATE TABLE: 

    CREATE TABLE SP 
    ( S# S# NOT NULL,   P#  P# NOT NULL,   OTY QTY NOT NULL, PRIMARY KEY   (  
S#,   P#   ), FOREIGN KEY   (  S#   )   REFERENCES S 
    ON DELETE CASCADE 
    ON UPDATE CASCADE, FOREIGN KEY   (   P#   )   REFERENCES P 
    ON DELETE CASCADE 
    ON UPDATE CASCADE, CHECK  ( QTY > 0 AND QTY < 5001  )   )   ; 

     ,   S#, #  QTY  , a S#  # 
       S   .  
     ,   
   

    CHECK  (  column IS NOT NULL  ) 

     8.  SQL	215 

        (column)    
 NOT NULL.  ,       
      NOT NULL. 
               
  ALTER TABLE.   : 
        ; 
                           
 (  ,   ); 
            ; 
        ; 
            ; 
            . 
     : 

    ALTER TABLE S ADD COLUMN DISCOUNT INTEGER DEFAULT -1 ; 

          DISCOUNT ( INTEGER)  
   S.       
     ;       
  -1. 
     ,         
 DROP TABLE,    : 

    DROP TABLE base-table option ; 

      option,      DROP DOMAIN,   
RESTRICT,  CASCADE.    RESTRICT      
  -     , 
 DROP  .    CASCADE,   DROP 
  (      ),   
 ,      ,   
   . 

      

     SQL   ,    ,   
 .      ""  
""   SQL,      SQL . 
 ,   SQL       
,1          ,  
    .  ,   
  ,        . 
 ,       ,  
INFORMATION_SCHEMA, ,    ,  , 
   "". 
     ,       SQL, 
    (  )  
      . , 
       
 " ".      
 ,   , -,     
" " , -,     " 
",   ,   .  
  : 
    '   ,   ,    SQL   
  ,  " "!   ,  
  .       . 

    216	 II.   

    1.          
  .  , ,   
  " ".   " "  
    (     
 ).  ,     
   " ",  . 
    2.       "   ",   
 " ",    ,     
   .  ,  ,  
 ,       
 . ,    ,   
 ,       . 
            . 
         
,           
,     (  ,   TABLES 
()      ,   
 ;   VIEWS ()  , 
 ,   ).     . 

    SCHEMATA 
    () 
    DCMAINS 
    ) 
    TABLES 
    () 
    VIEWS 
    () 
    COLUMNS () TABLE_PRI VI LEGES (  ) 
    COLUMN_PRIVILEGES (  ) USAGE_PRIVILEGES ( ) 
    DCMAIN_CONSTRAINTS (  ) 
    TABLE_CCNSTRAINTS (  ) 
    REFERENTIALJXNSTRAINTS 
    ( ) 
    CHECKJCONSTRAINTS 
    ( ) 
    KEY_COLUMN__USAGE 
    (  ) 
    ASSERTIONS 
    () 
    VIEWJTABLEJJSAGE 
    (  ) 
    VI EW_COLUMN_USAGE 
    (  ) 
    CONSTRAINT_TABLE_USAGE 
    (  ) 
    CONSTRAINT_COLUMN_USAGE 
    (  ) 
    CCNSTRAINT_DCMAIN_USAGE 
    (  ) 


    8.3.  :   

        SQL    (data manipulation 
language DML).   DML  SELECT, INSERT, UPDATE  DELETE. 
    (SELECT),     
 (INSERT, UPDATE, DELETE).    ,   
  ;  ,   
    SQL,     . 
       SQL  ,  ,  ,  
    .    ,   
   ,       . 

     8.  SQL 217 

    (        
,          6  7.) 
           
 . 

    8.3.1.       "  "  ,   


    SELECT P.COLOR, P.CITY FRCM   
    WHERE P.CITY  'Paris' AND   P.WEIGHT > 10 ; 

               
( ).       SQL : =, 
, <, >, <=  >=. 
     (  )  ,     
  (     )    
  ,   ,         
     (Red, London).  SQL    
     SELECT,     
      DISTINCT,   : 

    SELECT DISTINCT P.COLOR, P.CITY 
    FRCM  P 
    WHERE P.CITY <> 'Paris' 
    AND   P.WEIGHT > 10 ; 

          . 
     ,          
 ".".        SQL 
  ,     .   
     ,     
. ( ,     ,   
  \ ,     ORDER BY . .) 
     , ,       
   ,      
 ,    : 

    SELECT DISTINCT P.COLOR, P.CITY 
    FRCM  P 
    WHERE P.CITY <> 'Paris' 
    AND   P.WEIGHT > 10 
    ORDER BY CITY DESC ; 

        ORDER BY  : 

    ORDER BY order-item-commalist 

      commalist        order-item 6 
       ,   ( )  
  ASC  DESC (     
 );    ASC. 

    8.3.2.            

    SELECT P.P#, P.WEIGHT * 454 AS GMWT FRCM  P ; 

     AS GMWT     . 
 ,       #  GMWT 
.    AS GMWT  ,   
    .  ,   SQL   
           
,         . 

    218	 II.   

    8.3.3.       

    SELECT *	  "SELECT S.*"   (..   "*"  ) FRCM S  ; 

         S;      
      ( ),     
  FROM,    ,      
 ().        
(  SQL         ). 
    ,        , 
    .      
SQL (.. SQL   )  ,    
 (*)   (,  ,      
         ALTER TABLE). 
    .   SQL/92  SELECT * ERCM  ( җ 
 )    TABLE . 
    8.3.4.        , 
    
     SQL      .   
 : 

    1.   SELECT S.S#,   S.SNAME,   S.STATUS,   S.CITY, P.P#,   P.PNAME,   
P.COLOR,   P.WEIGHT FRCM      S,   P WHERE    S.CITY = P.CITY ; 
    2.   S JOIN P USING CITY ; 
    3.   S NATURAL JOIN P  ; 

            S   ( ). 
         ;  
     SQL,     
(  JOIN    SQL/92).   
      : 
     -,    FROM    
 S TIMES P. 
     ,   WHERE      , 
    CITY     ( ,  
      ). 
      ,    SELECT    
  ,    SELECT.    
 . 
    ,  ,  FROM  SQL  
 ,  WHERE  , 3   
SELECT-FROM-WHERE    . 

    8.3.5.     ,   ,   
 ,  ,     

    SELECT DISTINCT S.CITY AS SCITY,   P.CITY AS PCITY MM      S JOIN SP USING 
S# JOIN P USING P#   ; 

     ,       , 
    CITY      : 

    SELECT DISTINCT S.CITY AS SCITY,   P.CITY AS PCITY FRCM     S NATURAL JOIN 
SP NATURAL JOIN P  ; 

     8.  SQL	219 

    8.3.6.     ,      
         
          6  7. 

    SELECT FIRST.S# AS SA, SECOND.S# AS SB FRCM  S AS FIRST, S AS SECOND WHERE 
FIRST. CITY = SECOND. CITY AND   FIRST.S# < SECOND.S# ; 

              
FIRST  SECOND.         
 .   ,     SA  SB 
          
   WHERE. 

    8.3.7.     

    SELECT CCUNT(*)   AS N FROM      S  ; 

         ,  N,   , 
  5.  SQL      
(COUNT, SUM, AVG,   MIN),      SQL 
,    ,  : 
              
 DISTINCT, ,      
  .      MIN   DISTINCT 
     . 
        COUNT(*),     
DISTINCT,           
 . 
       null-       
 ,    ,    DISTINCT,  
   COUNT (*),  null-    , 
  . 
           ,  COUNT  
 ;     null- ( 
). ( [8.19] ,     ,  
     SQL.) 

    8.3.8.        2 

    SELECT  ( SP.QTY ) AS MAXQ, MIN ( SP.OTY ) AS MINQ 
    FROM  SP 
    WHERE SP.P# = 'P21 ; 

       FROM  WHERE    
    . ,      
   ,  .   ,  
  ,  .     
      ,  
  -  SQL. ,    
  ,       ,   
  ,  "    ",  
    (  AVG(SUM<QTY) ) 
).          . 
    '   .   , 
           
  .   ,   
  ,     ,    ,  . 

    220	 II.   

    8.3.9.          
  
    .     7. 

    SELECT SP.P#,   SUM   (  SP.QTY  )   AS TOTOTY 
    FROM      SP 
    GROUP    BY SP.P#  ; 

       SQL      : 

    SUMMARIZE SP BY   (  #  )   ADD SUM   (  CTY )   AS TOTQTY 

     ,  ,     GROUP BY,  
   SELECT     .  
    : 

    SELECT P.P#,    (  SELECT SUM   (   SP.OTY  ) 
    FROM      SP 
    WHERE    SP.P# = P.P#   )   AS TOTQTY FROM      P ; 

           
  (,   SELECT,  )   
 SQL/92         
 SQL.        
,     ,   ,  
  (  GROUP BY)   . 
(  TOTQTY    ,  ,   
null-,   .) 

    8.3.10.     ,      

    SELECT SP.P# 
    FROM      SP 
    GROUP    BY SP.P# 
    HAVING COUNT   (   SP.Sft   )   >  1   ; 

     HAVING       ,    WHERE  
;  ,  HAVING     
  ,   WHERE    . 
   HAVING     . 

    8.3.11.   ,   2 

    .     6  7. 

    SELECT DISTINCT S.SNAME 
    FRCM      S 
    WHERE    S.Sft  IN 
    (  SELECT SP.S# FRCM      SP WHERE    SP.P# =   'P2'   )   ; 

    .       .  
,     SELECT-FRCM-WHERE-GROUP BY-HAVING,  
     .     
  ,      
 IN condition ( condition   ),    
.    ,    ( 
  ).      
,   2: {SI, S2, S3, S4}.  , 
     : 

    SELECT DISTINCT S.SNAME 
    FRCM     S 
    WHERE    S.S# IN   (   'SI',    'S2',    'S31,    'S4'   )   ; 

     8.  SQL	221 

     ,    "  , 
  2"        
, , : 

    SELECT DISTINCT S.SNAME FRCM  S, SP WHERE S.S# = SP.S# AND   SP.P# = 'P21 ; 

    8.3.12.   ,      
  

    .     6  7. 

    SELECT DISTINCT S.SNAME FRCM  S WHERE S.S# IN 
    ( SELECT SP.Sft FRCM  SP WHERE SP.P# = IN ( SELECT P.P# FRCM  P WHERE 
P.COLOR = 'Red' ) ) ; 

         . 
    .       
  . 

    8.3.13.   ,     
    S 

    SELECT S.S# 
    FRCM  S 
    WHERE S.STATUS < 
    ( SELECT MAX ( S.STATUS ) FRCM  S ) ; 

             
,       "S"     S. 

    8.3.14.   ,   2 

      ,  8.3.11. 

    SELECT DISTINCT S.SNAME FRCM  S WHERE EXISTS 
    ( SELECT * 
    FRCM  SP 
    WHERE SP.S# = S.S# 
    AND   SP.P# = 'P2' ) ; 

    .  SQL "EXISTS (SELECT. . .FROM. ..)"   
   ,     "SELECT. . .FROM. 
.."  .  ,  SQL  EXISTS  
    (    
   ). 

    8.3.15.   ,     2 

    .     6  7. 

    SELECT DISTINCT S.SNAME FRCM  S 
    WHERE NOT EXISTS ( SELECT * 
    FRCM  SP 
    WHERE SP.Stt = S.S# 
    AND   SP.P# = 'P2' ) ; 

    222	 II.   

      : 

    SELECT DISTINCT S.SNAME FRCM      S 
    WHERE    S.S# NOT IN (  SELECT SP.S# FRCM      SP WHERE    SP.P# =   'P21  
 )   ; 

    8.3.16.   ,    
    .     6  7. 

    SELECT DISTINCT S.SNAME FRCM      S 
    WHERE    NOT EXISTS (  SELECT * FRCM      P 
    WHERE    NOT EXISTS (  SELECT * FRCM      SP 
    WHERE    SP.S# = S.S# AND         SP.P# = P.Pt   )   )   ; 

     SQL   -    
 FORALL; ,   FORALL    
  ,    . 
     ,   ,    ,   
     ,   
,    ,    [7.6]. 
   ,       ,  
  "" ,    
  .    , , : 

    SELECT DISTINCT S.SNAME 
    FRCM      S 
    WHERE     (  SELECT COUNT   {  SP.P#   ) 
    FRCM      SP 
    WHERE SP.S# = S.S# ) = ( SELECT COUNT ( P.P# ) FRCM  P ) ; 

    (" ,       
  ").      . 
     -,    ,    
   NOT EXISTS,       SP, 
     .  ,    
 (   )     
 . 
     -, ,     (  
),     SQL      
SQL/92.        . 
      ,          
 (.      6),    
  : 

    SELECT DISTINCT S.SNAME 
    FRCM      S 
    WHERE   (  SELECT SP.P#   ) 
    FRCM      SP 
    WHERE SP.S# = S.S# ) = ( SELECT P.P# FRCM  P ) ; 

      SQL      
,    ,   
  ( )  (   , 
 ,      ,    
,      ). 

     8.  SQL	223 

    8.3.17.   ,     16 ,  
  S2,      

    .     7. 

    SELECT .# 
    FRCM   
    WHERE P.WEIGHT > 16 
    UNION 
    SELECT SP.P# 
    FRCM  SP 
    WHERE SP.S# = 'S21 ; 

            
 UNION, INTERSECT  EXCEPT ( EXCEPT   SQL  
  MINUS  ).   SQL   
   : UNION ALL, INTERSECT ALL  EXCEPT ALL,  
   (  ) .    
   . 
      ,        
,   ,   SQL   .  SQL 
     ,    
         , 
    ,        
       ,  
   . 

    8.4.  :   

      ,    DML    
: INSERT (), UPDATE ()  DELETE ().  
  , ,   ,   . 

    8.4.1.    

    INSERT 
    INTO   ( #, PNAME, COLOR, WEIGHT, CITY ) 
    VALUES ( 'P81, 'Sprocket1, 'Pink', 14, 'Nice' ) ; 

    8.4.2.    

    INSERT 
    INTO  TEMP ( Stf, CITY ) 
    SELECT S.S#, S.SITY 
    FRCM  S 
    WHERE S.STATUS > 15 ; 
    8.4.3.    
    UPDATE  
    SET   COLOR = 'Yellow1, 
    WEIGHT = P. WEIGHT + 5 WHERE P.CITY = 'Paris' ; 

    8.4.4.    

    UPDATE  
    SET   CITY = ( SELECT S.CITY 
    FRCM  S 
    WHERE    S.S# =   'S51   ) WHERE    P.COLOR =  'Red'   ; 

    8.4.5.    

    DELETE 
    FRCM  SP 
    WHERE 'London' = 
    ( SELECT S.CITY FROM  S WHERE S.S# = SP.S# ) ; 

    224	 II.   

    8.5.   

              
 . ,     ,  . 8.1  
   BNF-    ( 
 ,    ,    
null-).          
 , ,    ,   
 . 
       ,  ,  ,  
  UNION, INTERSECT  EXCEPT ("",  
      ,    
 ).    . 8.1,    
 :  SELECT, FROM, WHERE, GROUP BY  HAVING 
(     ).    
   . 

     SELECT 

     SELECT    

    SELECT  [ ALL  |   DISTINCT ]   select-item-commalist 

    '. 
    1.         select-item-commalist       
.       . 
    2.     ALL,  DISTINCT  ,   ALL. 
    3.    ,     FROM, 
WHERE, GROUP BY  HAVING  .   ,    
 ,   :     
   (, ""   . ),  
       77 (    
  ). 
    4.  72  ,    77   
     77 (. ). 
    5.    ,    2   
      72,    
 DISTINCT,  ,  72,   . 
    6.     . 
         .    
,          , 
   , ..    . 
     I.     : 
    scalar-egression  [   [ AS  ]   column ] 
       (scalar-egression)  (  ) 
      77 (.  ).   
  77   ,   . 
   (      
 SELECT     77)    
  72.        AS,  
         
   72' (   AS  
       - ).     
    1         2,   77, 
 ,   AS,     
 WHERE, GROUP BY  HAVING (  ),  
    TI.        
  ORDER BY,    ""  , 
       . 
    
     8.  SQL	225 8      

      AS ,   :   
,      (, ),   
      72;    
   72    . 
    1 1 
    table-expression 
    ::=       join-table-expression I  nonjoin-table-expression 
    join-table-expression 
    ::=        table-reference [ NATURAL ]  JOIN 
    table-reference [ ON conditional-expression 
    I  USING  (  column-commalist )   ] I   table-reference CROSS JOIN 
table-reference I   ( join-table-expression ) 
    table-reference 
    ::=        table [   [ AS ]  range-variable 
    [   ( column-commalist )   ]   ] I   (  table-expression )   [ AS ]   range-variable 
    [   ( column-comraalist )   ] I  join-table-expression 
    nonjoin-table-expression 
    : '=       nonjoin-table-term 
    I   taile-expression UNION  [ ALL ] 
    [ CORRESPONDING  [ BY   (  column-commalist )   ]   ] 
    table-terra I   table-expression EXCEPT  [ ALL  ] 
    t CORRESPONDING  [ BY  (  column-commalist ) table-term 
    nonjoin- table-term 
    ::=        join-table-primary 
    I   table-term INTERSECT  [ ALL ] 
    [ CORRESPONDING  [ BY  {  column-commalist ) table-primary 
    table-term 
    ::=       nonjoin-table-term I join-table-expression 
    table-primary 
    ::=       join-table-primary I join-tafele-expression 
    nonjoin-tai>le-primary :: =        TABLE table 
    I table-constructor I select-expression I ( nonjoin-table-expression ) 
    table-constructor 
    ::=        VALUES row-constructor-commalist 
    row-constructor 
    ::=   scalar-expression 
    I ( scalar-expression-commalist ) \ ( table-expression ) 
    select-expression 
    ::=   SELECT [ ALL | DISTINCT ] select-item-commalist FROM 
table-reference-comma list 
    [ WHERE conditional-expression ] [ GROUP BY column-commalist ] 
    [ HAVING conditional-expression ] 
    select-item 
    ::=        scalar-expression [ I   [ range-variable .  ] 
    AS ]  column 

    . 8.1. BNF-    SQL 


226  II.   

                
    GROUP BY (. ),      
  SELECT         77, 
 ,      (  ) 
 . 

     2.     : 

    [ range-variable .   }   * 
        (range-variable)  (..   
    "*"),       
     SELECT.     
     77    . 
        (..      "*", 
     R,   ". *" ), 
        , 
    R,    . 
(,           
  .  ,      
 ". *",   ". *".) 

     FROM 

     FROM    

    FRCM table-reference-commalist 

               (table-reference-commalist)        
    . 
           , ,..., . 
    FROM  ,  
   , ,..., . 
    .        
 , .. , ,    FROM,  
   . 

     WHERE 

     WHERE    

    WHERE conditional-expression 

     җ     FRCM.  
  WHERE  ,       
,      (conditional-expression)  
 .   WHERE ,    . 

     GROUP BY 

     GROUP BY    

    GROUP BY column-comnalist 

      (coiumn-coimaiist)    .  җ 
    FROM  WHERE (  
).  ,     GROUP BY, 
   ( )     . 
      , ..   
,         
      ,      
       ,   
 GROUP BY.  ,       . 
  GROUP BY      
 SELECT,       
 ,      ,    
    . 

     8.  SQL	227 

         GROUP BY,    
 ,     SELECT. ,   
   SELECT (  ,    
 "*")     . ,    
      -   ,  
   GROUP BY,        
     COUNT, SUM, AVG,   MiN,   
         
  . 

     HAVING 

     HAVING    

    HAVING conditional-expression 

     G ( )     
 FROM,  WHERE (  )   GROUP 
BY (  
    ).   GROUP BY ,    G   
   FRCM   WHERE   
  ,     ;1  , 
       GROUP BY,  
,     .   HAVING 
  ,   G    ,  
     . 
     /.   HAVING ,   GROUP BY 
,     G.    HAVING  
GROUP BY ,   "", ..  , 
 ,      WHERE  FROM. 
     2.    HAVING     
  (       SELECT,  
  GROUP BY,   ). 
     3.  ,   HAVING  , 
..    ,     , 
    ,     
 (  !). 

      

         ,   
(   )   .  : 
         ,     
    350 (    ,  
    200),   ,   ,  
     . 

    SELECT .#, 
    'Weight in grains = ' AS TEXTl, 
    P.WEIGHT * 454 AS GMWT, 
    P. COLOR, 
    'Max quantity =  '  AS TEXT2, 
    MAX   (  SP.OTY  )   AS MQY FROM      P,   SP WHERE    P.P# = SP.PS 
    AND     (  P.COLOR =  'Red'  OR P.COLOR =  'Blue1   ) AND        SP.OTY > 
200 GROUP    BY P.Ptt,   P.WEIGHT,   P.COLOR HAVING SUM  ( SP.OTY )   > 350 ; 

    1     SQL,         
 ,      FROM  WHERE   
 ,      . 

    228	 II.   

    .  ,  (  )   
     ,    ,  
    SELECT,   
.    ,   : 
    1.   FROM:      ,   
     SP. 
    2.  WHERE:   1     , 
     WHERE.    
 ,     

    .# = SP.P# AND 
    ( P.COLOR = 'Red' OR P.COLOR = 'Blue1 ) AND 
    SP.QTY > 200 

    GROUP BY:   2      , 
   GROUP BY.      .#, 
P.WEIGHT  P.COLOR. 
    .        .#  
 ,  P.WEIGHT  P.COLOR   
  .   SQL    ,     
GROUP BY  P.WEIGHT  P.COLOR, ,     
 SELECT,   . 
    4.  HAVING: ,    

    SUM  (  SP.QTY )   > 350, 

       3 . 
    5.  SELECT:      4    
  . -,     , 
,     . -,    
.  -,    "Weight in grams =" (  
)  "Max quantity =" ( )   
   . , "   " 
,     SQL     . 
     ,       
" ". 
        : 

    #	1				GMWT		COLOR	TEXT2			MQY 

    1	Weight in grams =		5448		Red	Max quantity =	300 
    5	Weight in grains =	5448		Blue	Max quantity =	400 
    	Weight in grains =	7718		Blue	Max quantity =	400 

     ,  ,      
  ,    SELECT. , 
, ,   ,      
.      . ,   
     . ,  
,      ,      
 . ,     SQL  
     ,   
   ,      , 
   (      ). 

    8.6.   

       ,      
  SQL ,  ,    WHERE   
      .  

     8.  SQL 229 

         .    
,   , ,    ;  
,   ,   null-. ( , 
,       , 
   null-   ,   . 
 ,       , 
      null-.) 
      ,   BNF- (. 8.2).  ,  
         
 ,     ;     
         
,   MATCH  "---". 

    condi ti1-express!on 
    ::=        conditional- term 
    I  conditional-egression OR condi tional-terra 
    condi tional- term 
    : :=        conditional-factor 
    I   conditional-term AND conditional-factor 
    conditional-factor 
    ::=        [ NOT ]   condi tional-primary 
    condi tional -primary 
    ::=        simple-condition  |    (  conditional-expression ) 
    simple-condition 
    :: =        comparison-condition I   in-condition I  match-condition I  
all-or-any-condition I  exists-condition 
    comparison-condition 
    '.:=       row-constructor comparison-operator row-constructor 
    compa ri son-opera tor 
    : :=       =  |  <  |  <=  |   >  |  >=  |   
    in-condition 
    : :=       row-constructor [ NOT ]   IN  (  table-expression ) I  
scalar-expression [ NOT ]   IN 
    ( scalar-expression-commalist ) 
    match-conditi on 
    ::=        row-constructor MATCH UNIQUE   (   table-expression ) 
    all-or-any-condition 
    ::=        row-constructor 
    comparison-operator ALL  (  table-expression } |  row-constructor 
    comparison-operator ANY  (  table-expression ) 
    exists-condition 
    ::=   EXISTS ( table-expression ) 

    . 8.2, BNF-    SQL 

    230	 II.   

     MATCH 

     MATCH   : 
    row-constructor MATCH UNIQUE  (  table-expression } 
     rl   ,     row-constructor, 
  җ ,     table-egression.  
  MATCH       ,  
      ,  2, ,   

    rl = 2 

      .   : 

    SELECT SP.* 
    FRCM      SP 
    WHERE    NOT   (   SP.Stt MATCH UNIQUE   (  SELECT S.S# FRCM S   )   )   ; 

    (" ,       
   ").       
   ,     ,   
     . ,      
     . 
    ,   UNIQUE   MATCH UNIQUE  , 
  MATCH      (  ,   null-). 

     "---" 

     "---"    : 

    row-constructor 
    comparison-operator qualifier  ( table-expression ) 

       (comparison-operator)     
(=,   ..),   (qualifier)     ALL ()  ANY 
(-,    ).  ,  
"---"       ,  
    ALL ( ANY)     
 (   -)  () , 
  table-expression. (   ,  
   ALL   ,    ANY  .) 
  ("   ,     
  "): 

    SELECT DISTINCT PX.PNAME 
    FRCM      P AS PX 
    WHERE PX.WEIGHT > ALL ( SELECT PY.WEIGHT 
    FRCM  P AS PY 
    WHERE PY.COLOR = 'Blue' ) ; 


        : 

    .       
 (Blue) ,    (17, 12}.    
SELECT      (   6),  
      .   , , 
      ( ). 

    8.7.   

       SQL,  , .    
   ,      
 ,        

CASE  

     8.  SQL	231 

    CAST,   , ,   . ,   
      ,   
  .  ,  ,   
,       ,     
          .  
 ,  ,     SQL/92, 
     SQL    
 . 
          :  
 (+,-,*,/); BIT_LENGTH; CASE; CAST; CHARACTER_LENGTH; CURRENTJJSER; 
LOWER; OCTET_LENGTH; POSITION; SESSIONJJSER; SUBSTRING; SYSTEMJJSER; TRIM; 
UPPER; USER. 
        CASE  CAST. 

     CASE 

     CASE         
   . : 

    CASE 
    WHEN S.STATUS WHEN S.STATUS WHEN S.STATUS 
    5 THEN '   ' 10 THEN '' 
    < 15 THEN 'He  ' 
    WHEN S.STATUS < 20 THEN '' WHEN S.STATUS < 25 THEN 

'' ELSE	'' END 

     CAST 
     CAST       
   (,  ,  ). : 

    CAST   (   'S8'  AS  S#  ) 

          .    , 
       ,    [8.1]. 

    8.8.  SQL 

      ,  SQL    , 
     (    SQL  
    ,     
).          ( 
  ),    .   
 SQL. 
     ,     SQL,   
   ,   ,    
SQL,    ,      
 . ,       
    SQL,   ,  , 
       ;   
 ,    . (,  ,  , 
..     SQL,    
 ,    .) 
      ,       
 SQL,       .   ,   
  , ,       
 ,    , ,    
  CREATE TABLE,    . 

    232  II.   

           SQL, 
    .    
    . 8.3. (   
  ,     PL/I.  
         .) 

     SQL BEGIN DECLARE SECTION ; 
    DCL SQLSTATE CHAR(5) ; DCL P# CHAR(6) ; DCL WEIGHT FIXED DECIMAL (3) ; 
    EXEC SQL  DECLARE SECTION ; 
    P# = 'P21 ;        /*  */ EXEC SQL SELECT P.WEIGHT 
    INTO  : WEIGHT 
    FROM  P 
    WHERE P.P# = :P# ; IF SQLSTATE = '00000' 
    THEN ... ;         /* WEIGHT () =   */ ELSE ... ;    

     /*  -   */ 

    . 8.3.   PL/1    SQL 

       . 
    1.   SQL   EXEC SQL,    
      ,   
   (   ";"  PL/I). 
    2.   SQL (    ""  
 )     ,     
 .     "":    
 SQL,  SQL   ,  
  ,   . ,  DECLARE 
CURSOR   ,      BEGIN, END, 

    DECLARE SECTION,   WHENEVER (CM. ). 

    3.  SQL       (.. 
 ,  , );     
          SQL. 
           SQL (  
  DML) ,       
 SQL.       INTO  SELECT  
FETCH (. ),     ,   
  " SQL" ( . ). 
    4.     INTO  SELECT  . 8.3. 
   (   )   
 () ,      
; /-  ,    INTO,  
/- ,   ,     SELECT. 
    5.   ,      SQL, 
       SQL,  
  BEGIN  END   (DEOARE SECTICN). 

     8.  SQL	233 

    6.      SQL    , 
 SQLSTAiE1.   -  SQL   
     ;  ,   
00000 ,    ,   02000   
 ,   ,  ,   
.  ,   SQL     
  SQLSTATE,     
,    ,  .  , , 
    . 
    7.           
 ,    .  ,  , 
    (,   FETCH),   
 ,    ,   , 
   ;   ,    
  (,   UPDATE),     
,    SQL  ,    
 .      , 
        .  
   [8.1]  ,    
   . 
    8.      SQL     . 
    9.     ,   SQL    
    SQLSTATE.  WHENEVER 
        : 

     SQL WHENEVER condition action terminator 

     terminator   ,    1,  
condition    SQLERROR ( SQL)  NOT FOUND ( 
),   action    CONTINUE (),  
   ( ).  WHENEVER   ;  
     SQL.     
"WHENEVER condition   label"   ,    
 "IF condition   label"     
  SQL,     "WHENEVER condition CONTINUE" 
    , ,   
    .    : NOT 
FOUND      

    (SQLSTATE = 02000) SQLERROR     

    (.   [8.1]  SQLSTATE) 
      WHENEVER,   SQL   
    (  ), 
  (  ). 
    ,    .    
    .   ,    
    (..   
  ).      
.    ,       
,    (  
    '   SQL  ,  SQLCODE; 
 SQLSTATE    SQL/92,   SQLCODE  
 "",     (   
SQLSTATE)  ,      , 

    234	 II.   

    ),           
       . ,  
       "---" 
 SQL    "---"   ;    
 .       SQL,   
   SQL (    SQL    
).      ,    
     ,      
             
  .         
      ,    
 . 

    ,    

      ,      , : 
      " SELECT", 
        INSERT, 
     UPDATE (  CURRENT), 
        DELETE (   CURRENT). 
           . 

    8.8.1.  SELECT.      ,   
    GIVENS# 

     SQL SELECT STATUS, CITY INTO  :RANK, :CITY FRCM  S WHERE S# = 
:GIVENS# ; 

     " SELECT"     1, 
   ,     .   
,        S,  
 WHERE,   STATUS  CITY       
     RANK  CITY,   SQLSTATE 
  00000.    S    ,  
 WHERE,   SQLSTATE    02000;   
    ,       
SQLSTATE    . 

    8.8.2. INSERT.       ( ,  
      #, PNAME, PWT ; 
   ) 

     SQL INSERT 
    INTO   P ( #, PNAME, COLOR, WEIGHT, CITY ) VALUES  ( :P#, :PNAME, 
DEFAULT, :PWT, DEFAULT ) ; 

    8.8.3. UPDATE.        , 
   RAISE 

     SQL UPDATE S 
    SET   STATUS = STATUS + : RAISE WHERE CITY = 'London' ; 

       ,   WHERE,  
SQLSTATE   02000. 

    1      ,      
,      INTO. 

     8.  SQL 235 

    8.8.4. DELETE.       , 
   CITY 

     SQL DELETE 
    FRCM SP WHERE :CITY = 
    (  SELECT CITY FRCM      S WHERE    S.S# = SP.S#  )   ; 

     ,   ,   WHERE,  
SQLSTATE   02000. 

    ,   

            , ..  
   ,     .   , 
      ,    
  .     (. 
8.4)       (S#, SNAME  STATUS)   
  ,    Y. 

    EXEC SQL	DECLARE	X CURSOR	TOR	   X      */ 
    SELECT	S.S#, S. SNAME, S		STATUS 
    FRCM	S 
    WHERE	S.CITY =	:Y ; 
     SQL	OPEN X;			/*  	*/ 
    DO for all S rows			accessible via X ; /*    S,		*/ 
    /*   X	*/ 
    EXEC	SQL FETCH	X INTO	:S#, :SNAME, :STATUS ; 
    /*   	* / 
    END ; 
    EXEC SQL	CLOSE X	'	/	'*   X    */ 

    . 8.4.    

    .  DECLARE x CURSOR. ..     X, 
   ,   SELECT,  
    DECLARE.      
   ; DECLARE CURSOR   . 
      .    
          
FETCH,         
   INTO. 
    (       ,     
  .  ,    SELECT  
     INTO.)    
   ,  FETCH     
(DO. . .END  PL/1);      ,    
   .      X . 
             .  
    DECLARE CURSOR,    : 

     SQL DECLARE cursor CURSOR 
    TOR table-expression [ ORDER BY order-item-commalist ] ; 

       cursor           .         
table-expression   ozder-item-commaiist,     . 
   . 8.4. .      
  .    [8.1, 8.5]. 

    236  II.   

      ,  DECLARE CURSOR ,   
;          
     .    
    .     
  DECLARE CURSOR,   , ,  
  . 
           : OPEN, FETCH  CLOSE. 
    1.   

     SQL OPEN cursor ; 

       ( )   
(       ).    
      (   , 
   ,   );  , 
   ,    
    .      
 ,      . (  
   ,  ,    
.    ORDER BY,     
,    .) 
    2.   

     SQL FETCH cursor INTO host-variable-coimalist ; 

         cursor (   
)          
    ,    INTO.   
  FETCH   ,      
 SQLSTATE   02000. 
    3.  

     SQL CLOSE cursor ; 

       ()   (  
    ).        
 .        ,  
        ,    
,     ,    , 
   . ,       
      . 
       ,    ,   
-    DEIETE.  ,  X,   
     ,      
"  X", .. ,   "" X. : 

     SQL UPDATE S 
    SET   STATUS = STATUS + : RAISE WHERE CURRENT OF X ; 

     UPDATE. . .WHERE CURRENT  DELETE. . .WHERE CURRENT    
,          
      CREATE VIEW ( 
     ). 

     SQL 

      SQL      SQL,  
    ,  , , 
 . (, ,      , 


     8.  SQL	237 

        ,      
   .) ,     
 .      : 
    1.	  . 
    2.	 . 
    3.	  SQL-   . 
    4.	   ()   . 
      ,    ,  , , 
,        ,  
    SQL-      
 ""  .     2  3    
         
,    SQL.   , 
    ,    
  ""  SQL-    .  
, ,    SQL- 
,        
.   SQL     . 
         PREPARE  EXECUTE.   
   ( ,  ) : 

    DCL SQLSOURCE CHAR VARYING (65000) ; 
    SQLSOURCE = 'DELETE FRCM SP WHERE SP.QTY < 300' ; EXEC SQL PREPARE 
SQLPREPPED FRCM : SQLSOURCE ; EXEC SQL EXECUTE SQLPREPPED ; 

    : 
    \.  SQLSOURCE   PL/1   
 ,    -   
  (..     )  
 SQL,       DELETE. 
    2.   SQLPREPPED, ,   SQL,   
   PL/1,  ()   
    SQL- (   
   SQLSOURCE) . ,  SQLSOURCE  SQLPREPPED 
 . 
    3.     "SQLSOURCE =..."  SQLSOURCE 
   SQL- DELETE. ,   
        
, ,           
 ,       ,  
"  " ,    SQL. 
    4.   PREPARE      ""  
(.. ),   ,     

    SQLPREPPED. 

    5.   ,  EXECUTE    SQLPREPPED,  
    DELETE.  SQLSTATE  
DELETE    ,       . 
     ,     SQLPREPPED   
SQL,   PL/1,     ,     
 PREPARE  EXECUTE.  ,    SQL  
 . 

    238	 II.   

           ,  , 
  SQL  .      
   SQL.       
,    ;     
   ,     ( !) 
 SQL.      SQL  
   SQL,    
,          
      . 
         SQL   [8.1, 8.5]. 

    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.   (      
).       ,  
       ,   
         
  UNION, INTERSECT  EXCEPT.    
 ORDER BY   ,    
   ( ). 
    2.   .  ,    : 
       SELECT,     
DISTINCT,  ,  "SELECT *"    
 . 
      FROM,       
    FROM,  ,    . 

    1  WHERE,      EXISTS. 
      GROUP   HAVING,     , 
    SUM, AVG,   MIN. 

     8.  SQL	239 

     ,     (..  
 )   . 
    3.    INSERT, UPDATE  DELETE. 
         ( 
BNF--),   ( ,  MATCH  
"---")    (   
CASE  CAST).  ,   ,   
         , 
      (,   
SELECT  WHERE). 
     ,       SQL. 
   SQL    ,  
   (  )   SQL, 
 ,       . 
        
 ,       
    "--"  SQL  "--"  
 ,   PL/I. (,   ,   
PL/1,  SQL [8.1]    Ada, , COBOL, Fortran, 
MUMPS  Pascal). 
       (   ) 
 , ,  ,    
 SQLSTATE,   ( SKTKCT, INSERT, UPDATE  
DELETE),     .   ,  
  ,    DECLARE , OPEN, , 
CXOSE  -  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   ? 

    240	 II.   

    8.9.         SQL  
"  ,   2" (.     ). 
    8.10.         , 
   ,    .  ,  
    . ,  QUEL, ,  
 ,     QBE;   ISBL  PRTV [6.8]   
. A SQL?      ? 
    8.11.     SQL  . 6.13-6.48. 
    8.12.    SQL   : 
    )    S10   S   Smith,  New 
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 ) ; 
      PARTJSTRUCTURE ,   (MAJOR_P#)   
 (MINOR_P#)    .     
SQL          . 
         . 
    PART STRUCTURE 
    MAJOR P#	MINOR P#	OTY 
    PI	2	2 
    PI		4 
    2		1 
    2	4	3 
    	5	9 
    4	5	8 
    5	6	3 

      

    8.1.  ISO/IEC 9075:1992. Database Language SQL. (  
  ANSI X3.135-1992.) 

     8.  SQL 241 


       (ISO/ANSI)  SQL,  
  SQL2, SQL-92  SQL/92.  :  SQL  
   "" ,     
   . 
    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. , 


    242                                             II.   


      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.  SQL 243 


        SQL,     ( 
 )    : , 
   . 
    8.20. Date C.J. SQL Dos and Don'ts // C.J. Date. Relational Database 
Writings 1985-1989.  Reading, Mass.: Addison-Wesley, 1990. 
           ,   
SQL ,    ,   SQL, 
  [8.178.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 ^alculus  E3VPC), 
        . 
       E3VPC  
 ; ...,     
SQL,  ."  ,     SQL 
      ("SQL/86"),   SQL/92. 

        

    8.1. CREATE DCMAIN S#    CHAR(5) ; CREATE DOMAIN NAME  CHAR(20) ; CREATE 
DCMAIN STATUS NUMERIC(5) ; CREATE DCMAIN CITY  CHAR (15) ; CREATE DCMAIN P#    
CHAR(6) ; CREATE DCMAIN COLOR CHAR (6) ; CREATE DCMAIN WEIGHT NUMERIC (5) ; 
CREATE DOMAIN J#    CHAR(4) ; CREATE DCMAIN 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# ) ) ; 

    244	 II.   

    CREATE TABLE J 
    ( J#     J#, JNAME  NAME, CITY   CITY, PRIMARY KEY ( J# ) ) ; 
    CREATE TABLE SPJ ( S#     S#, P#     P#, J#     J#, 
    CTY   QTY, 
    PRIMARY KEY ( S#, P#, J# ), TOREIGN 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 DCMAIN DROP DCMAIN DROP DCMAIN DROP DCMAIN DROP DCMAIN DROP DCMAIN 
DROP DCMAIN DROP DCMAIN DROP DCMAIN 
    S# 
    NAME 
    STATUS 
    CITY 
    P# 
    COLOR 
    WEIGHT 
    J# 
    QTY 
    RESTRICT RESTRICT RESTRICT RESTRICT RESTRICT RESTRICT RESTRICT RESTRICT RESTRICT 

    8.5.      SQL   
,    SQL      
 , , ,   ,  , 
    SQL ,   ,   
 SQL. 
      ,   SQL    
""   RENAME    SQL/92 
  "AS column",  column   . 
,    ,       
,    ,    
  (  )      
.  ,       
 :      SQL 
       (  ). 
      SQL,    : 

         			SQL 

    A WHERE      		 [, ,...,   z] 
    A TIMES      		A UNION  
    A MINUS      		SELECT *  FROM A WHERE p 
    					SELECT DISTINCT X,  ,. . . ,z FROM A 
    A CROSS JOIN      		SELECT * FRCM A UNION SELECT * FROM  
    					SELECT * FRCM A EXCEPT SELECT * FRCM  Ib 

     8.  SQL 245 

        . 8.1,  ,       
  SQL   .     , 
        SQL  ,   
    ,    
    SQL.  ,   SQL  
 . 
    8.6.  ""   .   

    EXTEND A ADD exp AS 2 

        SQL  

    SELECT . *,   exp AS Z FRCM   (  A )   AS A 

             
  (    EXTEND);   
   . 
      

    SUMMARIZE A BY   (  Al,   A2, ...,   An)   ADD exp AS  Z 

        SQL  

    SELECT A.AZ, A.A2, ..., A.An, exp AS Z 
    FRCM ( A ) AS A 
    GROUP BY A.AZ, A.A2, ..., A.An 

    8.7.  SQL     . 
  ,       , 
     . ,  
    R := X ; 
    ( R  ,  X   ) 
      SQL: 
    DELETE ERCM R ; INSERT INTO R QX ; 
    ( QX   SQL). 
    8.8.  SQL     . 
    ,       
. ,  
     =  ; 
    (     )      SQL: 
    NOT EXISTS ( SELECT * FRCM A 
    WHERE NOT EXISTS ( SELECT * FRCM  
    WHERE A-row = B-row ) ) 
    ( A-rown -row  ,    
     5). 
    8.9.    . ,    
     .  ,    
 ! 

    SELECT DISTINCT S.SNAME FRCM  S 

    246	 II.   

    WHERE S.S# IN 
    ( SELECT SP.S# FRCM  SP WHERE SP.P# = 'P2' ) ; 
    SELECT DISTINCT T.SNAME FRCM { S NATURAL JOIN SP ) AS  WHERE T.P# = 'P2' ; 
    SELECT DISTINCT T.SNAME 
    FRCM ( S JOIN SP CN S.S# = SP.S# AND SP.P# = 'P2' 
    AS  
    SELECT DISTINCT T.SNAME 
    FRCM ( S JOIN SP USING S# ) AS  
    WHERE T.P# = 'P21 ; 
    SELECT DISTINCT S.SNAME 
    FRCM  S 
    WHERE S.St = ANY 
    ( SELECT SP.S# FRCM  SP WHERE SP.PS = 'P2' ) ; 
    SELECT DISTINCT S.SNAME 
    FRCM  S 
    WHERE EXISTS 
    ( SELECT * FRCM  SP 
    WHERE SP.S# = S.S#    SP.P# = 'P2' ) ; 
    SELECT DISTINCT S.SNAME FRCM  S, SP WHERE S.S# = SP.S# AND   SP.Ptt = 'P2' ; 
    SELECT DISTINCT S.SNAME 
    FRCM  S 
    WHERE 0 < 
    ( SELECT COUNT(*) FRCM  SP 
    WHERE SP.S# = S.S# AND   SP.P# = 'P21 ) ; 

     8.  SQL 247 

    SELECT DISTINCT S.SNAME FRCM  S WHERE  'P2' IN 
    ( SELECT SP.P# FRCM  SP WHERE SP.S# = S.S# ) ; 
    SELECT DISTINCT S.SNAME 
    FRCM  S 
    WHERE  'P2' = ANY 
    ( SELECT SP.P# FRCM  SP WHERE SP.S# = S.S# ) ; 
    SELECT	S.SNAME 
    FRCM	S, SP 
    WHERE	S.S# = SP.S# 
    AND	SP.P# = 'P21 
    GROUP	BY S.SNAME ; 

     :      ? 

    8.10.  SQL       . 
,       EXISTS (),  
 UNION (). ,    SQL  
        (. [8.8]); ,  
 "IN "      , 
   ( .)    . ,  
,  "IN " ()    
        
  .   , ,   , 
  "IN "   SQL     
    -   (  
  ,   "IN "   
 ,   ).     
  [8.17-8.19]. 
    8.11.      8.1 l.n,    
.    6. 
    8.11.13. SELECT * 

    FRCM  J ;  : TABLE J ; 

    8.11.14. SELECT J.* FRCM  J WHERE J.CITY = 'London' ; 
    8.11.15. SELECT DISTINCT SPJ.Stf FRCM  SPJ WHERE SPJ.J# = 'Jl' ; 

    248	 II.   

    8.11.16. SELECT SPJ.* FRCM  SPJ 
    WHERE SPJ.QTY >= 300 AND   SPJ.QTY <= 750 ; 
    8.11.17. SELECT DISTINCT P.COLOR, P.CITY FRCM  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# FRCM  S, P, J 
    WHERE NOT ( S.CITY = P.CITY AND P.CITY = J.CITY ) ; 
    8.11.20. SELECT S.S#, P.P#, J.J# FRCM  S, P, J WHERE S.CITY 0 P.CITY AND   
P.CITY 0 J.CITY AND   J.CITY 0 P.CITY ; 
    8.11.21. SELECT DISTINCT SPJ.P# FRCM  SPJ 
    WHERE  ( SELECT S.CITY FRCM  S WHERE S.S# = SPJ.S* ) = 'London' ; 
    8.11.22. SELECT DISTINCT SPJ.Pjt FRCM  SPJ WHERE  ( SELECT S.CITY 
    FRCM  S 
    WHERE S.St = SPJ.S* ) = 'London1 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 FRCM  S, J WHERE EXISTS 
    ( SELECT * FRCM  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 

     8.  SQL	249 

    WHERE S.S# = SPJ.S# ) = ( SELECT J.CITY FRCM  J WHERE J.J# = SPJ.Jt ) ; 
    8.11.25. SELECT DISTINCT SPJ.J# FRCM  SPJ 
    WHERE  ( SELECT S.CITY FRCM  S 
    WHERE S.S# = SPJ.Sfl ) <> ( SELECT J.CITY FRCM  J WHERE J.J# = SPJ.JS ) ; 
    8.11.26. SELECT DISTINCT SPJX.P* AS PA, SPJY.P# AS PB FRCM  SPJ AS SPJX, 
SPJ AS SPJY WHERE SPJX.SS = SPJY.S* AND   SPJX.P# < SPJY.P* ; 
    8.11.27. SELECT COUNT ( DISTINCT SPJ.JS ) AS N FRCM  SPJ WHERE SPJ.S# = 
'SI1 ; 
    8.11.28. SELECT SUM ( SPJ.CTY ) AS x 
    FRCM  SPJ 
    WHERE SPJ.Sfl = 'SI1 
    AND   SPJ.P# = 'PI' ; 
    8.11.29. SELECT SPJ.P#, SPJ.J#, SUM ( SPJ.QTY ) AS Y FRCM  SPJ GROUP BY 
SPJ.Ptf, SPJ.J* ; 
    8.11.30. SELECT DISTINCT SPJ.Ptf FRCM  SPJ 
    GROUP BY SPJ.Pt, SPJ.Jt HAVING AVG ( SPJ.OTY ) > 320 ; 
    8.11.31. SELECT DISTINCT J.JNAME FRCM  J, SPJ WHERE J.J# = SPJ.Jtf AND   
SPJ.S# = 'SI' ; 
    8.11.32. SELECT DISTINCT P.COLOR FRCM  P, SPJ WHERE P.P# = SPJ.P# AND   
SPJ.S* = 'SI' ; 
    8.11.33. SELECT DISTINCT SPJ.P# FRCM  SPJ, J WHERE SPJ.J# = J.J# AND   
J.CITY = 'London' ; 
    8.11.34. SELECT DISTINCT SPJX.Jt 
    FRCM  SPJ AS SPJX, SPJ AS SPJY 

    250	 .   

    WHERE SPJX.Pjf = SPJY.P# AND   SPJY.Stt = 'SI' ; 
    8.11.35. SELECT DISTINCT SPJX.S# 
    FRCM  SPJ AS SPJX, SPJ AS SPJY, SPJ AS SPJZ WHERE SPJX.P# = SPJY.Ptt AND   
SPJY.S# = SPJZ.S# AND  ( SELECT P.COLOR 
    FRCM  P 
    WHERE P.P# = SPJZ.P# ) = 'Red' ; 
    8.11.36. SELECT S.S# FRCM  S 
    WHERE S.STATUS < ( SELECT S.STATUS FRCM  S WHERE S.S# = 'SI' ) ; 
    8.11.37. SELECT J.J# FRCM  J 
    WHERE J.CITY = ( SELECT MIN ( J.CITY ) FRCM  J ) ; 
    8.11.38. SELECT DISTINCT SPJX.J# FRCM  SPJ AS SPJX WHERE SPJX.Ptt = 'PI' 
AND  ( SELECT AVG ( SPJY.OTY ) 
    FRCM  SPJ AS SPJY 
    WHERE SPJY.J# = SPJX.J# 
    AND   SPJY.P* = 'PI' ) > ( SELECT MAX ( SPJZ.QTY ) 
    FRCM  SPJ AS SPJZ 
    WHERE SPJZ.J# = 'Jl1 ) ; 
    8.11.39. SELECT DISTINCT SPJX.S# 
    FRCM  SPJ AS SPJX 
    WHERE SPJX.P* = 'PI' 
    AND        SPJX.QTY >   (  SELECT AVG   (  SPJY.OTY  ) FRCM      SPJ AS 
SPJY WHERE    SPJY.P# =   'PI' AND         SPJY.Jff  = SPJX.Jtf   ) 
    8.11.40. SELECT J.J# FRCM      J 
    WHERE NOT EXISTS ( SELECT * FRCM  SPJ, P, S WHERE SPJ.J# = J.J# AND   
SPJ.Ptf = P.P# AND   SPJ.S# = S.S# 
    ! 

     8.  SQL 251 

    AND   P.COLOR = 'Red' AND   S.CITY = 'London' ) ; 8.11.41. SELECT J.J# 
FROM  J 
    WHERE NOT EXISTS ( SELECT * FROM  SPJ " WHERE SPJ.J# = J.J# AND   NOT ( 
SPJ.Stt = 'SI' ) ) ; '8.11.42. SELECT P.P# 
    FROM  P 
    WHERE NOT EXISTS ( SELECT * FROM  J 
    WHERE J.CITY = 'London' AND   NOT EXISTS ( SELECT * FROM  SPJ WHERE 
SPJ.Ptt = P.P# AND   SPJ.J* = J.J# ) ) ; 
    8.11.43. SELECT S.S# FROM  S WHERE EXISTS 
    ( SELECT *' FROM  -P 
    WHERE NOT EXISTS ( SELECT * FROM  J 
    WHERE NOT EXISTS ( SELECT * FROM  SPJ WHERE SPJ.S# = S.S# AND   SPJ.PS = 
P.P# AND   SPJ.JS = J.J# ) ) ) ; 
    8.11.44. SELECT J.J# FROM  J 
    WHERE NOT EXISTS ( SELECT * FROM  SPJ AS SPJX WHERE SPJX.S* = 'SI1 AND   
NOT EXISTS ( SELECT * FROM  SPJ AS SPJY WHERE SPJY.P* = SPJX.P# AND   SPJY.J# 
= J.J# ) ) ; 

    252	 II.   

    8.11.45. SELECT S.CITY FRCM S UNION 
    SELECT P.CITY FRCM P UNICN SELECT J.CITY FRCM J ; 
    8.11.46. SELECT DISTINCT SPJ.P* FRCM  SPJ WHERE  ( SELECT S.CITY 
    FRCM  S 
    WHERE S.St = SPJ.S# ) = 'London' OR    ( SELECT J.CITY 
    FRCM  J 
    WHERE J.Jl = SPJ.J# ) = 'London1 ; 
    8.11.47. SELECT S.S#, P.P# 
    FRCM  S CROSS JOIN P 
    EXCEPT 
    SELECT SPJ.Stt,   SPJ.P# 
    FRCM      SPJ ; 
    8.11.48.   . 
    8.12. a) INSERT INTO S ( S#, SNAME, STATUS, CITY ) 
    VALUES ( 'S10', 'Smith', DEFAULT, 'New York' ) ; 6) UPDATE P 
    SET   COLOR = 'Orange' WHERE P.COLOR = 'Red' ; B) DELETE FROM  J WHERE NOT EXISTS 
    ( SELECT * FRCM SPJ WHERE SPJ.J* = J.J# ) ; 

    8.13.  ,    ,   
   ;       
 ( ?).    , cs  : 

     SQL DECLARE CS CURSOR FOR 
    SELECT S.S#, S.SNAME, S.STATUS, S.CITY FRCM  S ORDER BY S# ; EXEC SQL 
DECLARE CJ CURSOR TOR 
    SELECT J.J#, J.JNAME, J.CITY FROM  J WHERE J.J# IN 
    ( SELECT SPJ.Jtt FRCM  SPJ 
    WHERE SPJ.S* = :CS_S# ) ORDER BY J# ; 

     8.  SQL	253 

      GJ ,   cs_s#    
 ,    cs.  ,  
,   (    , 
   "/*"  "*/",      ): 
    
     SQL OPEN CS ; 
    DO for all S rows accessible via CS ; 
    /*     S,    CS */  SQL FETCH 
CS INTO :CS_S#, :CS_SN, :CS_ST, :CS_SC ; print CS_S#, CS_SN, CS_ST, CS_SC ; /* 
  CS_S#, CS_SN, CS_ST, CS_SC */ EXEC SQL OPEN CJ ; 
     for all J rows accessible via CJ ; /*     J, */ /* 
   CJ  */  SQL FETCH CJ INTO :CJ_J#, :CJ_JN, :CJ_JC ; 
print CJ_J#, CJ_JN, CJ_JC ; /*   CJ_J#, CJ_JN, CJ_JC */ END ; 
    EXEC SQL CLOSE CJ ; END ; EXEC SQL CLOSE CS ; 

    8.14.    ,    SQL     
  .   ""       
,       .   
     (    )    
    ,      
  ,   : 

    GET LIST ( GIVENP* ) ; 
    CALL RECURSION ( GIVENP* ) ; 
    RETURN ; 
    RECURSION: PROC ( UPPER_P# ) RECURSIVE ; DCL UPPER_P# ... ; DCL LCWER_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, */ /*    	*/  SQL FETCH  INTO 
:LCWER_Pt ; CALL RECURSION ( LCWER_P# ) ; END ; 
    EXEC SQL   ; END ; /*   */ 

    254	 II.     I      

     ,    "" (" 
")   DECLARE CURSOR    OPEN 
   ,    ,      
        
(     ,    
   ).   ,     
   FETCH ( .)    ""   
  CLOSE       
   "".  , ,  
    ,    
OPEN  CLOSE  ,      "push"  "pop". 
(    "push"  "pop"    Assembler.  
. .) 
     ,       .  
SQL    ,     ( 
       ). 
  .     ,  " 
 "        SQL. 
           ,  
      (   ), 
  . 

    GET LIST ( GIVENP* ) ; 
    CALL RECURSION ( GIVENP* ) ; 
    RETURN ; 
    RECURSION: PRCC ( 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# 
> :LCWER_P# ORDER BY MINOR_P# ; print UPPER_P# ; /*   UPPER_P# 
*/ DO "forever" ; 
    /*    */ EXEC SQL OPEN  ; EXEC SQL FETCH  INTO 
:LCWER_P# ; EXEC SQL CLOSE  ; 
    IF no "lower P#" retrieved THEN RETURN ; 
    IF "lower P#" retrieved THEN CALL RECURSION ( LCWER_P# ) ; /*     ,	
	*/ /*     RECURSION 
	*/ /*   LCWER_P#,                
	*/ /*             
	*/ END ; 
END ; /*   */ 

     8.  SQL 255 

    ,            
 (   UPPER_P#  LOWER_P#    
   ;      .)   
     : 

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

             
 (LOWER_P#)   UPPER_P#,   . 
              [8.14]. 

    256  II.   i 
