SQL

1
2 SQL:
3 SQL
4
5
6
7
8
9
10
11
12 EXISTS
13 ANY, ALL, SOME
14 UNION
15 , ,
16
17
18
19
20 :
21
22
23 SQL
24 SQL
25 SQL ( SQL )

A
B SQL
C SQL
D
E

1.

 

  SQL (   "SEEQUEL")     .  -            ,        .
          ,                    .        ,    , , ,            ,   ,    .
          ,    ,                .
       , 
          ,  SQL,        ,   .   ,        90-    SQL.
   SQL  ANSI (   )       ISO (   ). ,       SQL   ANSI,       , ,   ,   .      ,           ""        .   ,  ,  ,   ANSI,             .
             ,        . ,     SQL,        .   ,   ,       .     SQL  ,         ,      .   ,            ;        .       E,          .

- ?

 
    -    ,    .     .      ,      .    ,      ,  ,  ,
 . ,              .   (    )    ;          - ,  ,      .       :
 
     
 Gerry Farish ( 415)365-8775  127 Primrose Ave.,SF
 Celia Brock  ( 707)874-3553  246 #3rd St.,Sonoma
 Yves Grillet  ( 762)976-3665  778 Modernas,Barcelona
 
                  -  ,  (   )  . ,        .      .     ,           .             ,      .

 
                    . ,       (   ) -   .   ,         .         , , ,  .
 
       
 Farish  Drume  B.C./B.S.  $272.99
 Grillet  Halben  None  $44. 76
 Brock  Halben  Health,Inc.  $9077.47
 
 
              ,                . ,  - .   Halben       .    ,          (    )     ,    - . ,    ,              Grillet  Brock,         .                ,         .            -        -   SQL     Halben       .

 
     ,  ,  ,        .    ,          .         .      ,       -          . 
   .            ,      ,       - .            ,      .   ,     :"      . "          ,  ,   ,     .    ,    -  .

( )

 
      ,             . ,     - ,     . ,      ,      Mary Smiths;    ,             . 
     .   (     ),          ,  -   .
          .  -      ;         ,      . 
,   ,      .       ,                 .              19.

 
    ,   (  )   .  ,     ,    "   "   "  3 ". ,              .         .     ,        ,  cname   ,  odate   .             .           .

   1.1, 1.2,  1.3            ,   ,        SQL.            E.          SQL    ,      ,     .
                   .     ,  -   .          .     .
             ,          .
 
 1.1: 
 -----------------------------------------------------------
 SNUM SNAME  CITY COMM
 -----------------------------------------------------------
 1001 Peel  London .12
 1002 Serres  San Jose .13
 1004 Motika  London .11
 1007 Rifkin  Barcelona .15
 1003 Axelrod  New York .10
 -----------------------------------------------------------
 
 1.2: 
 
 CNUM CNAME  CITY  RATING  SNUM
 2001 | Hoffman  | London | 100  | 1001
 2002 | Giovanni  | Rome  | 200  | 1003
 2003 | Liu  | SanJose  | 200  | 1002
 2004 | Grass  | Berlin  | 300  | 1002
 2006 | Clemens  | London  | 100  | 1001
 2008 | Cisneros  | SanJose  | 300  | 1007
 2007 | Pereira  | Rome  | 100  | 1004
 
 
 
 
 1.3: 
 
 ONUM AMT ODATE CNUM SNUM
 3001 | 18.69 | 10/03/1990 | 2008 | 1007
 3003 | 767.19 | 10/03/1990 | 2001 | 1001
 3002 | 1900.10 | 10/03/1990 | 2007 | 1004
 3005 | 5160.45 | 10/03/1990 | 2003 | 1002
 3006 | 1098.16 | 10/03/1990 | 2008 | 1007
 3009 | 1713.23 | 10/04/1990 | 2002 | 1003
 3007 | 75.75 | 10/04/1990 | 2004 | 1002
 3008 | 4723.00 | 10/05/1990 | 2006 | 1001
 3010 | 1309.95 | 10/06/1990 | 2004 | 1002
 3011 | 9891.88 | 10/06/1990 | 2006 | 1001
 
 
  ,  snum    ,     .   snum    ,      . ,         -  ,  snum          .   ,  ,  "       ".
            19.
 
 
  :          - ,   ,    
   .          -           .     ,  , ,       ,     .
           ,     SQL    ,  ,   .         .
 
    1.1
 
  
 --------- ----------------------------------------------
 snum     
 ( "   " ).
 sname  .
 city  (  ).
 comm     .
 
 
 
 
 1.2   :
 
  
 -------- ---------------------------------------------------
 cnum     .
 cname  .
 city  (  ).
 rating      
  .      
 (  ).
 snum     
 (    )
 
      1.3:
 
  
 --------- ---------------------------------------------------
 onum     .
 amt   .
 odate  .
 cnum    
 (    ).
 snum    
 (   ).

 
         , ,        .        ,         ,       ,          .
   ,     ,      .      SQL,           .
       .    ,      ,       ,                     .
       SQL .       ,    ,        .

 

SQL

1.       ?
2.    4    
3.     ? ?
4.          
 ?
 
( .  A  . )

2.SQL:

 
 
        SQL       ,             ,    SQL.
           .          .         ,                .             SQL                         .              SQL,    3.
 

SQL?

 
  SQL        .                 ,  C.       C,        .                  ,              .        ,        ,   :
 
 1.   .
 2.   -        
  .
 3.   ,   -      
.
 4.       .
 5.  ,    1.
 6.    ,       3.
 
  (,     C ,             .) SQL    .   SQL                     ,    .
 

ANSI?

 
       ,  SQL     ANSI (    ). SQL   ANSI.     IBM.     SQL  ,      Oracle   IBM     SQL .
         SQL  , ANSI         (      ANSI ).
    ,   .      ANSI    .     ANSI      ,      ANSI       . ,   ,    .      ANSI SQL           .
  ,   ANSI   .          ,                  ANSI.
  ANSI -            ,            .

SQL

 
    SQL:   .  ,    ,   .  SQL              .    SQL,    ,          (   ) - .
   SQL    SQL   ,        (   ).
         . ,   ,      SQL           SQL.  SQL    SQL   ("passed off")           .
    ,    SQL   .                      .  SQL -     .       SQL       .             .
 

SQL

 
         SQL,   ,  .           SQL,    . 
  ,        .   ANSI     ,   SQL      ,          SQL.
  DDL (    ) -       ANSI,       ( , , ,    )   . 
 DML (  ) -              . 
 DCD (  )     ,        .
      DDL  ANSI.    .    ,    SQL    .

 
             .    -    .              .             ,         ,           .
 
   SQL,        -            .         .   , , cname  city -     , snum,  cnum -   .   ,      Highest()   None()   rating,     .   ,         .           ,             .              .
 
   ,                  SQL,   . ANSI SQL  
    ,           .  , DATA()  TIME() -    (      ).      ,   MONEY()  BINARY (). (MONEY -      . 
            ,         .)
  ANSI      ,    -      .  ANSI      B.
     ANSI ,    ,     SQL,     .
     ANSI , INTEGER( )  DECIMAL ( ) (     INT  DEC,  ),     ,          . ,                 .
     - CHAR (   ),     .   CHAR   ,            .         VARCHAR(  ),              ( 254  ). CHARACTER  VARCHAR       "".   CHAR  VARCHAR  ,  CHAR         ,  VARCHAR      .
 
        ,  . ,  1      "1".  "1" -     ,        1.  1 + 1 = 2,  "1" + "1"   "2".        ,      .         .          (  )    :  ASCII  (        )  EBCDIC  ( -   ) (   ).  ,        ,     .         4.
 
      ,   ANSI,     DATE(). (  ,     ,          ,       . )            ,   ,     .
 

SQL

 
       ,        SQL. SQL        ,       ANSI.  , ANSI     ,      ANSI       . ANSI -    -        ,                  .
 

- ?

 
  SQL           ,        (   PC     ,            ). ,   ,              (  ).     ,     (),     ,     ID().        ID ,   ;  ,        (    ),    .
 
 
  SQL   .     SQL          .      ,      .            . 
  ,  ,          ,           22.
 
  - USER()      .      ,  .
 

 
    -        SQL.    ,       .         .           . 
 SQL         .   -    , ,  ,            -   SQL. 
 
  ,  ,       SQL  .           .         ,       .        -  " FROM Salespeope "  " WHERE city = "London".      .   , Salespeople - ,  FROM -    FROM. , " city = "London" " -   WHERE.  -           .      ,  (  ),  .
       ,     
. , ,         .        ,                 SQL .   ( [ ] )       ,   ( ... )          .      (<>) -        .     SQL ,     .

 
        .      -     SQL,         .        ,     .    -  SQL -   ,   ,    ,     (       ),          .   -     ;          ,         .   3,   ,  ,       .     SQL      ,         SQL.    ,              .

SQL

 
 1.         SQL ?
 2.  ANSI   DATA ?
 3.   SQL     
  ?
 4.   -   ?
 

( . A . )


3. SQL

 
            .                .  ,     (  )            .   ,                 SQL.

     , SQL     .  -      SQL. ,   SQL ,   -     - .   ,      SQL          .

?

 
   -        ,             .             , ,   ,     ,    (      ),          .
 

?

 
        DML. ,  
     ,     ,          DML   ,       .
     SQL    .     ,               .    - SELECT().

SELECT

 
     ,  SELECT         . ,    
   :
 
 SELECT snum, sname, sity, comm
 FROM Salespeople;
 
 
 
        3.1.
 =============== SQL Execution Log ============
 | |
 | SELECT snum, sname, sity, comm |
 | FROM Salespeople; |
 | |
 | ==============================================|
 | snum sname city comm |
 | ------ ---------- ----------- ------- |
 | 1001 Peel London 0.12 |
 | 1002 Serres San Jose 0.13 |
 | 1004 Motika London 0.11 |
 | 1007 Rifkin Barcelona 0.15 |
 | 1003 Axelrod New York 0.10 |
 ===============================================
 
 3.1:  SELECT
 
   ,        .         ,      ,     .
       :
 
SELECT          - .     ,  .
 
 
snum, sname  -       .           . , ,             ,         ;    .
 
 
FROM  ,  SELECT,   Salespeople     .            .    -   (Salespeople).
 
;         SQL          .       (\)  ,    .
 
 
  ,           .                     . ,           ,        -   .            .      SQL :    . ,     .  ,  ,     ,       .
 
 
     ( ENTER)  .        ,       ,  :
 
 SELECT snum, sname, city, comm FROM Salespeople;
 
      SQL        ,   SQL   (     ENTER )  .  -            ,           .

 

 
        ,       .  (*)         :
 
 SELECT *
 FROM Salespeople;
 
           .

SELECT

 
    ,  SELECT     SELECT,  .           ,  .       ,       (*).   FROM  ,         .  ,    ( ; )            .

 
   SELECT       . ,         .   ,        ,    SELECT. , 
 
 
 SELECT sname, comm
 FROM Salespeople;
 
 
 
      3.2.
 
 
 
 =============== SQL Execution Log ============
 | |
 | SELECT snum, comm |
 | FROM Salespeople; |
 | |
 | ==============================================|
 | sname comm |
 | ------------- --------- |
 | Peel 0.12 |
 | Serres 0.13 |
 | Motika 0.11 |
 | Rifkin 0.15 |
 | Axelrod 0.10 |
 ===============================================
 
 
 3.2:   
 
 
           ,         . ,            .

     ,  , ,            . ,  (*)       ,      ,         .    ,   (odate),  (snum),  (onum),   (amt):
 
 SELECT odate, snum, onum, amt
 FROM Orders;
 
       3.3.
 
 ============= SQL Execution Log ===============
 | |
 | SELECT odate, snum, onum, amt |
 | FROM Orders; |
 | |
 | ------------------------------------------------|
 | odate snum onum amt |
 | ----------- ------- ------ --------- |
 | 10/03/1990 1007 3001 18.69 |
 | 10/03/1990 1001 3003 767.19 |
 | 10/03/1990 1004 3002 1900.10 |
 | 10/03/1990 1002 3005 5160.45 |
 | 10/03/1990 1007 3006 1098.16 |
 | 10/04/1990 1003 3009 1713.23 |
 | 10/04/1990 1002 3007 75.75 |
 | 10/05/1990 1001 3008 4723.00 |
 | 10/06/1990 1002 3010 1309.95 |
 | 10/06/1990 1001 3011 9891.88 |
 ===============================================
 3.3:  
 
     ,     -         SQL.

 
  DISTINCT () -            SELECT.                .   (  )      ,              ).    ,    ;       (snum).    :
 
 SELECT snum
 FROM Orders;
 
      3.4
 
 =============== SQL Execution Log ============
 | |
 | SELECT snum |
 | FROM Orders; |
 | |
 | ============================================= |
 | snum |
 | ------- |
 | 1007 |
 | 1001 |
 | 1004 |
 | 1002 |
 | 1007 |
 | 1003 |
 | 1002 |
 | 1001 |
 | 1002 |
 | 1001 |
 =============================================
 3. 4: SELECT    .
 
 
      ,  ,  
 :
 
 SELECT DISTINCT snum
 FROM Orders;
 
        3.5.
 
 
   , DISTINCT   ,    ,        .  -     ,           .       ,      DISTINCT,      -   -  . ,          .  -   Clemens   ,    SELECT DISTINCT cname,        .      Clemens      .      ,        DISTINCT.

DISTINCT

 
  DISTINCT         SELECT.     ,
 
 =============== SQL Execution Log ============
 | |
 | SELECT DISTINCT snum |
 | FROM Orders; |
 | |
 | ============================================= |
 | snum |
 | ------- |
 | 1001 |
 | 1002 |
 | 1003 |
 | 1004 |
 | 1007 |
 =============================================
 3.5: SELECT  
 
  DISTINCT       .  
       -  . DISTINCT, ,      ,    (        ,     6 ),        .

DISTINCT ALL

 
   DISTINCT,    - ALL.     ,    .              DISTINCT  ALL,  ALL     ,    .

 
       ,    ,         .            , SQL             .
 
  WHERE -   SELECT,     ,            .            . ,           .     :
 
 SELECT sname, city
 FROM Salespeople;
 WHERE city = "LONDON";
 
    WHERE ,                  . ,   Peel,      city,     "London",      .   Serres   ,   .        3.6.
 
 =============== SQL Execution Log ============
 | |
 | SELECT sname, city |
 | FROM Salespeople |
 | WHERE city = 'London' |
 | ============================================= |
 | sname city |
 | ------- ---------- |
 | Peel London |
 | Motika London |
 =============================================
 
 3.6: SELECT c  WHERE
 
 
          WHERE.  rating       
             .   -          .                .        100,
 :
 
 SELECT *
 FROM Customers
 WHERE rating = 100;
 
       ,   -   .      3. 7.
 
   WHERE       .  ,     ,  ,      SELECT   WHERE. ,           SELECT,     WHERE.
 
 
 
 
 
 =============== SQL Execution Log ============
 | |
 | SELECT * |
 | FROM Customers |
 | WHERE rating = 100; |
 | ============================================= |
 | num cname city rating snum |
 | ------ -------- ------ ---- ------ |
 | 2001 Hoffman London 100 1001 |
 | 2006 Clemens London 100 1001 |
 | 2007 Pereira Rome 100 1001 |
 =============================================
 
 3.7: SELECT     

 
               ,        .          .   ,       .
    ,                    ,      .
      ,      ,       .     ,    ,   SQL  .     ,   ,     .   4,            ,         .
 
 

SQL

 
 1.   SELECT     , , 
       .
 2.          
     = 1001.
 3.          
 : city, sname, snum, comm.
 4.   SELECT    (rating), -
      San Jose.
 5.       snum   
            -
 .
 
 ( .  A  . )

4.

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

 
    -           .      ,   2 + 3 = 5  city = "London".      .             .      " " - (>).     SQL :
 
 =  
 >  
 <  
 >=    
 <=    
 <>  
 
         .   ,      , ASCII  EBCDIC,   . SQL            .   ,   "1",   ,       .          - , "a" < "n"   a     -         .
 
 
    ASCII   EBCDIC,  -  :    
          (    ).  ASCII,     -      ,  "Z" < "a",    -    ,  "1" < "Z".      EBCDIC.     ,         ASCII.                .
      -  .     ; 1 + 2 -        3.       ,         ,   +()  *().
       , 
      SQL      .   SQL    5.         (rating)  200.   200 -   ,      ,        .
 
 SELECT *
 FROM Customers
 WHERE rating > 200;
 
        4.1.
 
  ,           -
 200,     
 
 rating > = 200

 
        SQL.   -     ,  .       /     // .      SQL :
 
 AND, OR,  NOT.
 
 =============== SQL Execution Log ============
 | |
 | SELECT * |
 | FROM Customers |
 | WHERE rating > 200; |
 | ============================================= |
 | snum cname city rating snum |
 | ----- -------- -------- ----- ------ |
 | 2004 Crass Berlin 300 1002 |
 | 2008 Cirneros San Jose 300 1007 |
 =============================================
 
 4.1:    (>)
 
 
   ,  ,   (  "   " ),           - AND, OR, NOT.
     ,   /   -     ;  ,  SQL(     )       .

 

:

 
* AND    (   A AND B)     
    ,    .
* OR    (   A OR B)     
 ,     .
* NOT    (   NOT A)    
          .
 
      ,      .        San Jose   ()  200:
 
 SELECT *
 FROM Customers
 WHERE city = " San Jose'
 AND rating > 200;
 
 
         4.2.        .
      OR        San Jose (OR)     200.
 
 
 =============== SQL Execution Log ============
 | |
 | SELECT * |
 | FROM Customers |
 | WHERE city = 'San Jose' |
 | AND rating > 200; |
 | ============================================= |
 | num cname city rating snum |
 | ------ -------- -------- ---- ----- |
 | 2008 Cirneros San Jose 300 1007 |
 =============================================
 
 4.2: SELECT  AND
 
 SELECT *
 FROM Customers
 WHERE city = " San Jose'
 OR rating > 200;
 
         4.3.
NOT      .     NOT:
 
 
 SELECT *
 FROM Customers
 WHERE city = " San Jose'
 OR NOT rating > 200;
 
 
       4.4.
 
 
 =============== SQL Execution Log ============
 | |
 | SELECT * |
 | FROM Customers |
 | WHERE city = 'San Jose' |
 | OR rating > 200; |
 | ============================================= |
 | num cname city rating snum |
 | ----- ------- -------- ----- ------ |
 | 2003 Liu San Jose 200 1002 |
 | 2004 Grass Berlin 300 1002 |
 | 2008 Cirneros San Jose 300 1007 |
 =============================================
 
 4.:3: SELECT  OR
 
 =============== SQL Execution Log ============
 | |
 | SELECT * |
 | FROM Customers |
 | WHERE city = 'San Jose' |
 | OR NOT rating > 200; |
 | ============================================= |
 | cnum cname city rating snum |
 | ------ -------- ------ ----- ----- |
 | 2001 Hoffman London 100 1001 |
 | 2002 Giovanni Rome 200 1003 |
 | 2003 Liu San Jose 200 1002 |
 | 2006 Clemens London 100 1001 |
 | 2008 Cirneros San Jose 300 1007 |
 | 2007 Pereira Rome 100 1004 |
 =============================================
 
 4.4: SELECT  NOT
 
      Grass  . Grass    San Jose,       200,        .            .     NOT    ,    ,       .      :
 
 rating NOT > 200
 
    .   SQL  ?
 
 SELECT *
 FROM Customers
 WHERE NOT city = " San Jose'
 OR rating > 200;
 
 
  NOT      city = 'SanJose',    rating > 200  ?   ,    . SQL   NOT       . 
 
      :
 
 SELECT *
 FROM Customers
 WHERE NOT( city = " San Jose'
 OR rating > 200 );
 
 
   SQL     ,    
              (       ).  , SQL     ,     city = " San Jose'   rating > 200.    ,      . ,       ,     ,   NOT      .
      -    4.5. 
    .        (    4.6 ):
 
 SELECT *
 FROM Orders
 WHERE NOT ((odate = 10/03/1990 AND snum >1002)
 OR amt > 2000.00);
 
 =============== SQL Execution Log ============
 | |
 | SELECT * |
 | FROM Customers |
 | WHERE NOT (city = 'San Jose' |
 | OR rating > 200); |
 | ============================================= |
 | cnum cname city rating snum |
 | ----- -------- ------- ----- ------ |
 | 2001 Hoffman London 100 1001 |
 | 2002 Giovanni Rome 200 1003 |
 | 2006 Clemens London 100 1001 |
 | 2007 Pereira Rome 100 1004 |
 =============================================
 4.5: SELECT  NOT   
 
 =============== SQL Execution Log ==============
 | |
 | SELECT * |
 | FROM Orders |
 | WHERE NOT ((odate = 10/03/1990 AND snum > 1002) |
 | OR amt > 2000.00); |
 | =============================================== |
 | onum amt odate cnum snum |
 | ------ -------- ---------- ----- ----- |
 | 3003 767.19 10/03/1990 2001 1001 |
 | 3009 1713.23 10/04/1990 2002 1003 |
 | 3007 75.75 10/04/1990 2004 1002 |
 | 3010 1309.95 10/06/1990 2004 1002 |
 =================================================
 4.6:  () 
 
         ,         .
 
        ,   
      ,      ,       .
          .        -  odate = 10/03/1990  snum > 1002     AND,                  .     (      1,  B1  )    (amt) > 2000.00 (B2)   OR,    (B3),      ,   B1  B2 -    . 
 B3        NOT,    (B4),    .
    B4,  , -    ,  B3 . B3 -  ,  B1  B2 -  . B1         10/03/1990,    snum    1002. B2    ,       2000.00.      2000.00  B2 - ;   B3  ,  B4 . ,       .
   ,    3   snum > 1002 (     onum 3001  3   snum = 1007 ),  B1 ,    B3    .      .      .

 

 
    ,       .             -    .
        AND  OR   ,      ,    .   NOT,    ,         . 
           ,   ,     .                   .
  ,        ,          SQL.      5.
 
 

SQL

 
1.          
    $1,000.
2.        sname  city  
       .10 .
3.          
    =< 100,      .
4.         ?
 
 SELECT *
 FROM Orders
 WHERE (amt < 1000 OR
 NOT (odate = 10/03/1990
 AND cnum > 2003 ));
 
5.         ?
 
 SELECT *
 FROM Orders
 WHERE NOT ((odate = 10/03/1990 OR snum > 1006)
 AND amt > = 1500 );
 
6.       ?
 
 SELECT snum, sname, city, comm
 FROM Salespeople
 WHERE ( comm > + .12 OR
 comm < .14 );
 
( .  A  . )

5.

 
            4, SQL    IN, BETWEEN, LIKE,  IS NULL.   ,                .   IS NULL       NULL,    :   .        NOT    .

IN

   IN             .              ,      ,    Barcelona   London,      (     5.1 ):
 
 SELECT *
 FROM Salespeople
 WHERE city = 'Barcelona'
 OR city = 'London';
 
         :
 
 SELECT *
 FROM Salespeople
 WHERE city IN ( 'Barcelona', 'London' );
 
         5.2.
 
     , IN               .              .   ,   .       ,   .          snum = 1001, 1007,  1004. 
       5.3:
 SELECT *
 FROM Customers
 WHERE cnum IN ( 1001, 1007, 1004 );
 
 =============== SQL Execution Log ============
 | SELECT * |
 | FROM Salespeople |
 | WHERE city = 'Barcelona' |
 | OR city = 'London'; |
 | ==============================================|
 | snum sname city comm |
 | ------ ---------- ----------- ------- |
 | 1001 Peel London 0.12 |
 | 1004 Motika London 0.11 |
 | 1007 Rifkin Barcelona 0.15 |
 ===============================================
 
 5.1      
 
 =============== SQL Execution Log ============
 | |
 | SELECT * |
 | FROM Salespeople |
 | WHERE city IN ('Barcelona', 'London'; |
 | ==============================================|
 | snum sname city comm |
 | ------ ---------- ----------- ------- |
 | 1001 Peel London 0.12 |
 | 1004 Motika London 0.11 |
 | 1007 Rifkin Barcelona 0.15 |
 | |
 ===============================================
 
 5.2 SELECT  IN
 
 =============== SQL Execution Log ============
 | SELECT * |
 | FROM Customers |
 | WHERE snum IN ( 1001, 1007, 1004 ); |
 | ============================================= |
 | snum cname city rating snum |
 | ------ -------- ------ ---- ------ |
 | 2001 Hoffman London 100 1001 |
 | 2006 Clemens London 100 1001 |
 | 2008 Cisneros San Jose 300 1007 |
 | 2007 Pereira Rome 100 1004 |
 =============================================
 
 5.3: SELECT  IN  
 

BETWEEN

 
   BETWEEN    IN.     
  ,    IN, BETWEEN  ,        .      BETWEEN   ,  AND   .    IN, BETWEEN   ,             . (   ,     , SQL    "  ()BETWEEN   |,   " BETWEEN  |.      LIKE).             .10  .12 (    5.4):
 
 SELECT *
 FROM Salespeople
 WHERE comm BETWEEN .10 AND .12;
 
     BETWEEN,         (   , .10  .12 )    .
 
 =============== SQL Execution Log ============
 | SELECT * |
 | FROM Salespeople |
 | WHERE comm BETWEEN .10 AND .12; |
 | ==============================================|
 | snum sname city comm |
 | ------ ---------- ----------- ------- |
 | 1001 Peel London 0.12 |
 | 1004 Motika London 0.11 |
 | 1003 Axelrod New York 0.10 |
 ===============================================
 
  5.4: SELECT  BETWEEN
 
  SQL      BETWEEN.        ,     ,   -  :
 
 SELECT *
 FROM Salespeople
 WHERE ( comm BETWEEN .10, AND .12 )
 AND NOT comm IN ( .10, .12 );
 
         5.5.
  
   ,   ,                 .  ,   IN  BETWEEN         ,      (  IN )    (  BETWEEN ).
  ,   , BETWEEN         ASCII.       BETWEEN         .
 
 =============== SQL Execution Log ============
 | |
 | SELECT * |
 | FROM Salespeople |
 | WHERE ( comm BETWEEN .10 AND .12 |
 | AND NOT comm IN ( .10 .12; |
 | ==============================================|
 | snum sname city comm |
 | ------ ---------- ----------- ------- |
 | 1004 Motika London 0.11 |
 | |
 ===============================================
 
 5.5:  BETWEEN - 
 
           
 :
 
 
 SELECT *
 FROM Customers
 WHERE cname BETWEEN 'A' AND 'G';
 
 
         5.6.
     Grass  Giovanni ,    BETWEEN.   -   BETWEEN    .  'G'     Giovanni,  BETWEEN  'G'  .       (    ),  Giovanni  .      Grass.       BETWEEN      .            (      z ).
 
 
 =============== SQL Execution Log ============
 | |
 | SELECT * |
 | FROM Customers |
 | WHERE cname BETWEEN 'A' AND 'G'; |
 | ============================================= |
 | cnum cname city rating snum |
 | ------ -------- ------ ---- ------ |
 | 2006 Clemens London 100 1001 |
 | 2008 Cisneros San Jose 300 1007 |
 | |
 =============================================
 
 5. 6:  BETWEEN   

LIKE

 
  LIKE      CHAR  VARCHAR,       . ..      ,       .       (wildkards) -      -.
         LIKE:
 
*   ( _ )    . , 'b_t'    'bat'  'bit',     'brat'.
 
*   (%)      (  ).  '%p%t'    'put', 'posit',  'opt',   'spite'.
 
        G (     5.7 ):
 
 
 
 SELECT
 FROM Customers
 WHERE cname LIKE 'G%';
 
 =============== SQL Execution Log ============
 | |
 | SELECT * |
 | FROM Customers |
 | WHERE cname LIKE 'G'; |
 | ============================================= |
 | cnum cname city rating snum |
 | ------ -------- ------ ---- ------ |
 | 2002 Giovanni Rome 200 1003 |
 | 2004 Grass Berlin 300 1002 |
 | |
 =============================================
 
 5. 7: SELECT  LIKE  %
 
  LIKE          ,         .               Peal  Peel.                  (       5.8):
 
 SELECT *
 FROM Salespeople
 WHERE sname LIKE 'P _ _ l %';
 
    ,      ,        'P'  'l' ,    Prettel    .   ' % ' -           sname       Peel (      sname -     ).   ,   sname ,     Peel,   . ,  'l'     .   ' % ' -    .  ,   sname   - VARCHAR.
 
 =============== SQL Execution Log ============
 | |
 | SELECT * |
 | FROM Salespeople |
 | WHERE sname LIKE ' P 1% '; |
 | ==============================================|
 | snum sname city comm |
 | ------ ---------- ----------- ------- |
 | 1001 Peel London 0.12 |
 | |
 ===============================================
 
 5.8: SELECT  LIKE   (_)
 
 
                  ?  LIKE ,         ESC.  ESC        ,               . ,      sname    ,  :
 
 SELECT *
 FROM Salespeople
 WHERE sname LIKE '%/_%'ESCAPE'/';
 
 
        ,     
     .  ESCAPE  '/ '   ESC.  ESC   LIKE ,   ,  ,   ESCAPE,     ,      .  ESC             .
 
    ,       
   ;     .
    ,  ESC    .  ,         ESC,     . -      ESC "     ",  -   ESC .
           '_/'  sname :
 
 SELECT *
 FROM Salespeople
 WHERE sname LIKE ' % /_ / / %'ESCAPE'/';
 
         .  
     (%),    ( /_ ),  ESC ( // ),        ( % ).

(NULL)

 
  ,          
   ,      ,        . SQL   ,     NULL()  ,  .     NULL,  ,                 ( ).      ,    ,           .  ,  NULL    ,      .       .   , NULL  SQL    .
  ,           .        ,         ,       .
          NULL   snum      ,    .
 

NULL

 
    NULL    ,            NULL.  NULL    ,      NULL,      ,  - .  ,       ,                   NOT() -  , NOT () -  .
  ,   'city = NULL'  'city IN (NULL)'  ,    city.
           -              NULL  .   , SQL    IS,      NULL,    NULL.
          NULL   city :
 SELECT *
 FROM Customers
 WHERE city IS NULL;
 
      ,        NULL    .  NULL -  ,      .

NOT

 
              NOT.
     ,     NOT -  . ,     NULL   ,    NOT      :
 
 SELECT *
 FROM Customers
 WHERE city NOT NULL;
 
     NULL(     ),     .    
 
 SELECT *
 FROM Customers
 WHERE NOT city IS NULL;
 -   .
 
     NOT  IN:
 SELECT *
 FROM Salespeople
 WHERE city NOT IN ( 'London', 'San Jose' );
 
   -     
 
 SELECT *
 FROM Salespeople
 WHERE NOT city IN ( 'London', ' San Jose' );
 
        5.9.
       NOT BETWEEN  NOT LIKE.
 
 =============== SQL Execution Log ============
 | |
 | SELECT * |
 | FROM Salespeople |
 | WHERE sity NOT IN ('London', 'San Jose'; |
 | ==============================================|
 | snum sname city comm |
 | ------ ---------- ----------- ------- |
 | 1003 Rifkin Barcelona 0.15 |
 | 1007 Axelrod New York 0.10 |
 | |
 ===============================================
 
 5. 9:  NOT  IN

 
            SQL.        (BETWEEN)     (IN),            (LIKE).
          SQL     -        NULL   .       NULL      IS NULL. ,             ,       SQL      ,      ,  .      6.

SQL

 
 
1.           3 
 4  1990
2.        -
  Peel  Motika. ( :    , 
 snum      )
3.  ,       
        A  G.
4.         -
    C.
5.         -
   NULL   amt().
 
( .  A  . )

6.

 
 
    ,              ,           .                    .      ,         ,        .                   .

?

 
               .      .         .    :
 
* COUNT     -NULL   
  .
* SUM       
 .
* AVG       .
* MAX        .
* MIN       
 .

?

 
          SELECT ,    ,      .       SUM  AVG.  COUNT, MAX,  MIN,       .      , MAX  MIN      ASCII,   ,  MIN   ,  MAX     (         4 ).
   SUM      ,  
  ,      6.1:
 
 
 SELECT SUM ((amt))
 FROM Orders;
 
 =============== SQL Execution Log ============
 | SELECT SUM (amt) |
 | FROM Orders; |
 | ==============================================|
 | ------- |
 | 26658.4 |
 ===============================================
 6.1:  
 
 
  ,        -
 ,        .
- ,        -
,   GROUP BY ( )   .
   -    (  
    6.2 ):
 
 
 SELECT AVG (amt)
 FROM Orders;
 
 
 =============== SQL Execution Log ============
 | |
 | SELECT AVG (amt) |
 | FROM Orders; |
 | ==============================================|
 | |
 | ------- |
 | 2665.84 |
 | |
 ===============================================
 
 6.2:  
 

COUNT

 
  COUNT    .    -
   ,     .   
 ,    DISTINCT   
     .     ,
,        
   (     6.3 ):
 
 
 SELECT COUNT ( DISTINCT snum )
 FROM Orders;
 
 
 
 

DISTINCT

 
 
 
 
     ,  DISTINCT, -
      ,    ,
    SELECT,  .
   DISTINCT  COUNT   
,   ANSI,     
    .
 
 
 =============== SQL Execution Log ============
 | |
 | SELECT COUNT (DISTINCT snum) |
 | FROM Orders; |
 | ==============================================|
 | |
 | ------- |
 | 5 |
 | |
 | |
 ===============================================
 
 
 6.3:   
 
 
     ( COUNT )    
DISTINCT    ,      3,  -
       DISTINCT. DISTINCT 
  ,    ,  
    COUNT.  MAX  MIN,     
 ,  SUM  AVG,      -
 ,        
  .
 
 

COUNT ,

 
 
 
       ,  
COUNT     ,     -
,       6.4:
 
 SELECT COUNT (*)
 FROM Customers
 
 
 COUNT     NULL  ,   
DISTINCT    . DISTINCT   
   COUNT  ,   
 
 
 =============== SQL Execution Log ============
 | |
 | SELECT COUNT (*) |
 | FROM Customers; |
 | ==============================================|
 | |
 | ------- |
 | 7 |
 | |
 | |
 ===============================================
 
 6. 4:     ,    NULL    .
 DISTINCT   c COUNT (*), ,     
       .  
 ,      ,    -
 ,   (     , 
   ). ,   ,   -
     ,    
 COUNT     .
 
 

 
 
 
     (    ) -
  ALL,     , 
DISTINCT,   : -  . ANSI -
     COUNT,    
 .
   ALL  *     COUNT -
 
 
* ALL  _  .
* ALL     NULL.
 
 
  *      NULL -
,      COUNT;    COUNT -
  NULL   .   (CO-
UNT)  -NULL    rating    ( -
  ):
 
 
 SELECT COUNT ( ALL rating )
 FROM Customers;
 
 

,

 
 
 
  ,        
.        -
         -
 . (    , DISTINCT  . ) -
,          -
   ( blnc)   .  
   ,     -
 .       
:
 
 
 SELECT MAX ( blnc + (amt) )
 FROM Orders;
 
 
    ,     blnc  amt
          -
. ,      ,  -
      . ,
         
. ,         .
 ,      SQL   
       ,  
    7.
 
 

GROUP BY

 
 
 
  GROUP BY     
      ,     
.        
    SELECT. ,    -
       .
        ,  MAX
(amt)        snum. GROUP BY,
,        :
 
 
 SELECT snum, MAX (amt)
 FROM Orders
 GROUP BY snum;
 
 
        6.5.
 
 
 =============== SQL Execution Log ==============
 | |
 | SELECT snum, MAX (amt) |
 | FROM Orders |
 | GROUP BY snum; |
 | =============================================== |
 | snum |
 | ------ -------- |
 | 1001 767.19 |
 | 1002 1713.23 |
 | 1003 75.75 |
 | 1014 1309.95 |
 | 1007 1098.16 |
 | |
 ================================================
 
 
 6.5:       
 
 
 GROUP BY        -
       .   ,
           
snum,  MAX       . 
 ,    GROUP BY, ,  ,
     ,     
.      -
     .
     GROUP BY   . -
   ,    
       -
 .   ,     
  ,    MAX    , -
 :
 
 
 SELECT snum, odate, MAX ((amt))
 FROM Orders
 GROUP BY snum, odate;
 
 
        6.6.
 
 
 =============== SQL Execution Log ==============
 | |
 | SELECT snum, odate, MAX (amt) |
 | FROM Orders |
 | GROUP BY snum, odate; |
 | =============================================== |
 | snum odate |
 | ------ ---------- -------- |
 | 1001 10/03/1990 767.19 |
 | 1001 10/05/1990 4723.00 |
 | 1001 10/06/1990 9891.88 |
 | 1002 10/03/1990 5160.45 |
 | 1002 10/04/1990 75.75 |
 | 1002 10/06/1990 1309.95 |
 | 1003 10/04/1990 1713.23 |
 | 1014 10/03/1990 1900.10 |
 | 1007 10/03/1990 1098.16 |
 | |
 ================================================
 
 
 6.6:       
 
 
  ,  ,        -
,     .
 
 

HAVING

 
 
 ,    ,     
      $3000.00.  
      WHERE (  
  ,   ),    -
    ,    
   .        --
  :
 
 
 SELECT snum, odate, MAX (amt)
 FROM Oreders
 WHERE MAX ((amt)) > 3000.00
 GROUP BY snum, odate;
 
 
       ANSI.  
    $3000.00,   -
  HAVING.
  HAVING     
   ,     WHERE -
    .
    :
 
 
 SELECT snum, odate, MAX ((amt))
 FROM Orders
 GROUP BY snum, odate
 HAVING MAX ((amt)) > 3000.00;
 
 
        6. 7.
 
 
 =============== SQL Execution Log ==============
 | |
 | SELECT snum, odate, MAX (amt) |
 | FROM Orders |
 | GROUP BY snum, odate |
 | HAVING MAX (amt) > 3000.00; |
 | =============================================== |
 | snum odate |
 | ------ ---------- -------- |
 | 1001 10/05/1990 4723.00 |
 | 1001 10/06/1990 9891.88 |
 | 1002 10/03/1990 5160.45 |
 | |
 ================================================
 
 
 6. 7:    
 
 
 
    HAVING        
 SELECT,     GROUP BY. 
      .   
:
 
 
 SELECT snum, MAX (amt)
 FROM Orders
 GROUP BY snum
 HAVING odate = 10/03/1988;
 
 
  date      HAVING,   
  (    )     
 .    ,  HAVING -
        GROUP BY. 
    (   
 6.8 ):
 
 
 SELECT snum, MAX (amt)
 FROM Orders
 WHEREodate = 10/03/1990
 GROUP BY snum;
 
 
 =============== SQL Execution Log ==============
 | |
 | SELECT snum, odate, MAX (amt) |
 | FROM Orders |
 | GROUP BY snum, odate; |
 | =============================================== |
 | snum |
 | ------ -------- |
 | 1001 767.19 |
 | 1002 5160.45 |
 | 1014 1900.10 |
 | 1007 1098.16 |
 | |
 ================================================
 
 
 6.8:      
   3 
 
 
   odate ,      , 
       .   -
  -    - "  -   -
  3 ."   7,       
.
    , HAVING    
      . ,  
  -  ,      
GROUP BY  . ,     -
  Serres  Rifkin:
 
 
 SELECT snum, MAX (amt)
 FROM Orders
 GROUP BY snum
 HAVING snum B (1002,1007);
 
 
        6.9.
 
 
 
 
 
 =============== SQL Execution Log ==============
 | |
 | SELECT snum, MAX (amt) |
 | FROM Orders |
 | GROUP BY snum |
 | HAVING snum IN ( 1002, 1007 ); |
 | =============================================== |
 | snum |
 | ------ -------- |
 | 1002 5160.45 |
 | 1007 1098.16 |
 | |
 ================================================
 
 
 6. 9:  HAVING  GROUP BY 
 
 

 
 
 
    ANSI SQL,     
.     ,     -
  .     ,  
 
 
 SELECT odate, MAX ( SUM (amt) )
 FROM Orders
 GROUP BY odate;
 
 
   . (    -
  ,   ,   -
     ,      -
.)   , , SUM  
    odate,  MAX   ,  -
    .   GROUP BY -
           oda-
te.
 
 

 
 
 
 
 
      -.  -
,     ,  .   
         
      .   -
  - ,       
         -
.      ,    
       -
  .
         
 GROUP BY.       ,  
         
.    ,      -
    .
  ,    , 
       
.         -
     HAVING.
  ,         
 ,   ,   7,   
       .
 
 

SQL

 
 
 
1.          3 -
 .
2.        -NULL -
   city   .
3.          -
 .
4.         ,
      G.
5.          .
6.        
    . (     
   ,      .)
 
 
( .  A  . )

7.

 
 
 
           -
 .         -
 ,       -
,     ,     -
     .   -
 ,       , 
   ,   .
 
 

 

 
 
 
    SQL    
      . -
,        ,
        , ,  
    SQL    
        .
 
 

 

 
 
 
         -
          -
. SQL       
  .       
  SELECT,         -
 . ,   ,   -
         . 
:
 
 
 SELECT snum, sname, city, comm * 100
 FROM Salespeople;
 
 
        7.1.
 
 
 
 

 

 
 
 
     ( ..  -
),    -  .   -  -
    ,     
 .     ,    
 
 
 =============== SQL Execution Log ============
 | |
 | SELECT snum, sname, city, comm * 100 |
 | FROM Salespeople; |
 | ==============================================|
 | snum sname city |
 | ------ --------- ----------- --------- |
 | 1001 Peel London 12.000000 |
 | 1002 Serres San Jose 13.000000 |
 | 1004 Motika London 11.000000 |
 | 1007 Rifkin Barcelona 15.000000 |
 | 1003 Axelrod New York 10.000000 |
 | |
 ===============================================
 
 
 7.1:     
 
 
, ,     SELECT . 
   -    ,   
      .   ,
         
,    .
 
 

 
  'A',       , -  -
,     1.      -
 SELECT ,   .   ,
    ,     .
    1 + 2    SELECT,   
    'A' + 'B';    -
      'A'  'B'   ,    
 .
   ,       
  .
       -
      (%).     -
        ,   
  (     7.2 )
 
 
 
 
 
 SELECT snum, sname, city, ' % ', comm * 100
 FROM Salespeople;
 
 
 =============== SQL Execution Log ============
 | |
 | SELECT snum, sname, city, '%' comm * 100 |
 | FROM Salespeople; |
 | ==============================================|
 | snum sname city |
 | ------ -------- ----------- ---- --------- |
 | 1001 Peel London % 12.000000 |
 | 1002 Serres San Jose % 13.000000 |
 | 1004 Motika London % 11.000000 |
 | 1007 Rifkin Barcelona % 15.000000 |
 | 1003 Axelrod New York % 10.000000 |
 | |
 ===============================================
 
 
 7.2:     
 
 
         
.        -
     .   , 
         ,  
     .     
         
 .      ( .  7.3 )
   :
 
 
 SELECT ' For ', odate, ', there are ',
 COUNT ( DISTINCT onum ), 'orders.'
 FROM Orders
 GROUP BY odate;
 
 
   ,  5 ,  -
   ,     . (   -
     UNION, 
 
 =============== SQL Execution Log ==============
 | |
 | SELECT 'For', odate, ', ' there are ' , |
 | COUNT (DISTINCT onum), ' orders ' |
 | FROM Orders |
 | GROUP BY odate; |
 | =============================================== |
 | odate |
 | ------ ---------- --------- ------ ------- |
 | For 10/03/1990 , there are 5 orders. |
 | For 10/04/1990 , there are 2 orders. |
 | For 10/05/1990 , there are 1 orders. |
 | For 10/06/1990 , there are 2 orders. |
 ================================================
 7.3:  ,  ,  
 
 
     14. )    ,  -
         ,
  .    ,   -
     ,    
   .
    SQL   
   (  Report Writer), 
     . 
SQL         
. SQL          -
. ,  ,  ,    SQL
          -
 . , ,    SQL.
 
 

 
   ,  -    , 
    ,     - -
 . SQL   ORDER BY 
    .    
         -
.      , 
  GROUP BY,      ( ASC )  -
 ( DESC )   .    - -
.         
   (      cnum -
):
 SELECT *
 FROM Orders
 ORDER BY cnum DESC;
 
    7.4.
 
 =============== SQL Execution Log ==============
 | SELECT * |
 | FROM Orders |
 | ORDER BY cnum DESC; |
 | =============================================== |
 | onum amt odate cnum snum |
 | ------ -------- ---------- ----- ----- |
 | 3001 18.69 10/03/1990 2008 1007 |
 | 3006 1098.16 10/03/1990 2008 1007 |
 | 3002 1900.10 10/03/1990 2007 1004 |
 | 3008 4723.00 10/05/1990 2006 1001 |
 | 3011 9891.88 10/06/1990 2006 1001 |
 | 3007 75.75 10/04/1990 2004 1002 |
 | 3010 1309.95 10/06/1990 2004 1002 |
 | 3005 5160.45 10/03/1990 2003 1002 |
 | 3009 1713.23 10/04/1990 2002 1003 |
 | 3003 767.19 10/03/1990 2001 1001 |
 
 7. 4:      
 
 

 
 
 
         , -
    amt,    cnum. (  -
   7.5 ):
 
 
 SELECT *
 FROM Orders
 ORDER BY cnum DESC, amt DESC;
 
 
 =============== SQL Execution Log ==============
 | |
 | SELECT * |
 | FROM Orders |
 | ORDER BY cnum DESC, amt DESC; |
 | =============================================== |
 | onum amt odate cnum snum |
 | ------ -------- ---------- ----- ----- |
 | 3006 1098.16 10/03/1990 2008 1007 |
 | 3001 18.69 10/03/1990 2008 1007 |
 | 3002 1900.10 10/03/1990 2007 1004 |
 | 3011 9891.88 10/06/1990 2006 1001 |
 | 3008 4723.00 10/05/1990 2006 1001 |
 | 3010 1309.95 10/06/1990 2004 1002 |
 | 3007 75.75 10/04/1990 2004 1002 |
 | 3005 5160.45 10/03/1990 2003 1002 |
 | 3009 1713.23 10/04/1990 2002 1003 |
 | 3003 767.19 10/03/1990 2001 1001 |
 | |
 ================================================
 
 
 
 
 7.5:      
 
 
    ORDER BY       -
 .   ,   ,  
      SELECT.  - 
ANSI   ,   ,  . -
 , ,  :
 
 
 SELECT cname, city
 FROM Customers
 GROUP BY cnum;
 
 
    cnum    , GROUP BY  c 
     .    
 ,       ,  
 (  SELECT)  ,   -
 ORDER BY,   .
 
 

 
 
 
 ORDER BY   ,   GROUP BY  -
 .   ,  ORDER BY   .  -
       ORDER BY. 
 ,    ;  , ,
    :
 
 
 SELECT snum, odate, MAX (amt)
 FROM Orders
 GROUP BY snum, odate
 GROUP BY snum;
 
 
    7.6.
 
 
 =============== SQL Execution Log ==============
 | |
 | SELECT snum, odate, MAX (amt) |
 | FROM Orders |
 | GROUP BY snum, odate |
 | ORDER BY snum ; |
 | =============================================== |
 | snum odate amt |
 | ----- ---------- -------- |
 | 1001 10/06/1990 767.19 |
 | 1001 10/05/1990 4723.00 |
 | 1001 10/05/1990 9891.88 |
 | 1002 10/06/1990 5160.45 |
 | 1002 10/04/1990 75.75 |
 | 1002 10/03/1990 1309.95 |
 | 1003 10/04/1990 1713.23 |
 | 1004 10/03/1990 1900.10 |
 | 1007 10/03/1990 1098.16 |
 | |
 ================================================
 
 
 7. 6:    
 
 
          , -
   .
 
 

 
 
 
   ,       
     .   
      ,      .
 ,     SELECT ,  OR-
DER BY -   1,         
. ,       -
    ,   
     (   -
 7.7 ):
 
 
 SELECT sname, comm
 FROM Salespeople
 GROUP BY 2 DESC;
 
 
 
 
 =============== SQL Execution Log ============
 | |
 | (SELECT sname, comm |
 | FROM Salespeople |
 | ORDER BY 2 DESC; |
 | ============================================= |
 | sname comm |
 | -------- -------- |
 | Peel 0.17 |
 | Serres 0.13 |
 | Rifkin 0.15 |
 | |
 ===============================================
 
 
 
 
P 7. 7:   
 
 
       ORDER BY -   -
  GROUP BY        -
 .    , , 
   SELECT ,   -
  GROUP BY,        . -
,       ,  
   ,     7.8:
 
 
 SELECT snum, COUNT ( DISTINCT onum )
 FROM Orders
 GROUP BY snum
 ORDER BY 2 DESC;
 
 =============== SQL Execution Log ==============
 | |
 | SELECT snum, odate, MAX (amt) |
 | FROM Orders |
 | GROUP BY snum |
 | ORDER BY 2 DESC; |
 | =============================================== |
 | snum |
 | ----- ---------- |
 | 1001 3 |
 | 1002 3 |
 | 1007 2 |
 | 1003 1 |
 | 1004 1 |
 | |
 ================================================
 
 
 7.8:     
 
 
   ,     ,   
   ;       
.     ANSI SQL,    -
,       :
 
 
 SELECT snum, COUNT ( DISTINCT onum )
 FROM Orders
 GROUP BY snum
 GROUP BY COUNT ( DISTINCT onum ) DESC;
 
 
     !
 
 

NULL

 
 
 
     (NULL)     
   ,      -
     .  -  
ANSI    .   
   .
 
 

 
 
 
   ,        ,
        -
 .      : ,  -
     10       -
 .  ,    ,   -
,   ,       
         .
          
.
       .    
   ,  ORDER BY   -
      .  -
       ,    
   .
       .   -
        
,    -  ,    -
      ORDER BY.
 ,          -
   ,      
          
 ,         .
    8.
 
 

SQL

 
 
 
1.      12% . 
          , -
  ,       .
2.          
    .      :
 For the city (city), the highest rating is: (rating).
3.         
 .   ( rating )   
    .
4.           
     .
 
 
( .  A  . )

8.

 
 
 
  ,        -
 .   ,      
     .  -   -
          ,
     .     -
    ,     
     .
 
 

 

 
 
 
       SQL -   -
        -
      ,    . 
   - ,     -
     .     1,
         
   .  ,  
     ,    
     .  ,
     FROM , 
.         -
  , ,     
. ,       
 ,   WHERE  .
 
 

 

 
 
         , -
     .   
 :
 Salespeople.snum
 Salespeople.city
 Orders.odate
 
 
  ,         
   ,  SQL   
  ,  .    -
   ,      ,
      .      -
. ,        
city.
      (  ),   -
     Salespeople.city  Customers.city,  SQL
  .
 
 

 

 
 
 
         
       ,    
      .   
         
   .     ,   -
 (     8.1 ):
 
 
 SELECT Customers.cname, Salespeople.sname,
 Salespeople.city
 FROM Salespeople, Customers
 WHERE Salespeople.city = Customers.city;
 
 
 =============== SQL Execution Log ============
 | SELECT Customers.cname, Salespeople.sname, |
 | Salespeople.city |
 | FROM Salespeople, Customers |
 | WHERE Salespeople.city = Customers.city |
 | ============================================= |
 | cname cname city |
 | ------- -------- ---- |
 | Hoffman Peel London |
 | Hoffman Peel London |
 | Liu Serres San Jose |
 | Cisneros Serres San Jose |
 | Hoffman Motika London |
 | Clemens Motika London |
 =============================================
 
 
 8.1:   
 
 
     city        -
,      .   -
            ,  -
          
   .   ,  , 
  ,      -
,    ,      .
  SQL      -    
      ,    -
   .   ,  
 Peel         
 ,      .  -
      ,   
city      London,  Peel -   -
      .    -
         (  -
        ).
 

 

 
 
 
        
   .   ,   
    .  .   ,
    snum .    
 ,       1.  -
       . , 
       
,     :
 
 
 SELECT Customers.cname, Salespeople.sname
 FROM Customers, Salespeople
 WHERE Salespeople.snum = Customers.snum;
 
 
       8.2.
 
 
  -  ,      -
  ,    , snum    -
,   .   .    -
   ;   snum  -
  - .       , 
           -
     .
 
 
 =============== SQL Execution Log ============
 | SELECT Customers.cname, Salespeople.sname, |
 | FROM Salespeople, Customers |
 | WHERE Salespeople.snum = Customers.snum |
 | ============================================= |
 | cname sname |
 | ------- -------- |
 | Hoffman Peel |
 | Giovanni Axelrod |
 | Liu Serres |
 | Grass Serres |
 | Clemens Peel |
 | Cisneros Rifkin |
 | Pereira Motika |
 =============================================
 
 
 8.2:     
 
 

 

 
 
 
       
 -   .      
  ,     ,  
    WHERE    -
    ( = ).  'city = 'London'  'Sa-
lespeople.snum = Orders.snum ' -     -
  .    -    -
  ,    .  , , -
      .  -
     (   
8.3):
 
 
 SELECT sname, cname
 FROM Salespeople, Customers
 WHERE sname < cname
 AND rating < 200;
 
 
 =============== SQL Execution Log ============
 | SELECT sname, cname |
 | FROM Salespeople, Customers |
 | WHERE sname < cname |
 | AND rating < 200; |
 | ============================================= |
 | sname cname |
 | -------- ------- |
 | Peel Pereira |
 | Motika Pereira |
 | Axelrod Hoffman |
 | Axelrod Clemens |
 | Axelrod Pereira |
 | |
 =============================================
 
 
 8.3:    
 
 
      .    -
      ,    -
   ,       200.
,       , ,   -
,         -
,        .
 
 

 

 
 
 
         .
          
     .    
    (     8.4 ):
 
 
 SELECT onum, cname, Orders.cnum, Orders.snum
 FROM Salespeople, Customers,Orders
 WHERE Customers.city < > Salespeople.city
 AND Orders.cnum = Customers.cnum
 AND Orders.snum = Salespeople.snum;
 
 
 =============== SQL Execution Log ==============
 | |
 | SELECT onum, cname, Orders.cnum, Orders.snum |
 | FROM Salespeople, Customers, Orders |
 | WHERE Customers.city < > Salespeople.city |
 | AND Orders.cnum = Customers.cnum |
 | AND Orders.snum = Salespeople.snum; |
 | =============================================== |
 | onum cname cnum snum |
 | ------ ------- ----- ----- |
 | 3001 Cisneros 2008 1007 |
 | 3002 Pereira 2007 1004 |
 | 3006 Cisneros 2008 1007 |
 | 3009 Giovanni 2002 1003 |
 | 3007 Grass 2004 1002 |
 | 3010 Grass 2004 1002 |
 ===============================================
 
 
 8. 4:   
 
 
       ,   -
  ,   -      
     (   snum  ), 
   -     ( -
    cnum  snum    ).
 
 
 
 
 
 
 
 
 

 

 
 
 
          -
  .  ,     
         -
        . -
,       ,   -
       .  
:      ,    
.
     9.
 
 

 

SQL

 
 
 
1.         -
       .
2.          
     .
3.        
     12% .   , 
 ,    .
4.         
       100.
 
 
( .  A  . )

9.

 
 
 
 
 
   8,          -
.
   ,       -
       .  
,     .   , 
   ,    ,     -
       
   .
 
 

 

 
 
 
     ,     
, ,         
  .       -
 ,     .   
       
   -       -
 , .
       ,  
      .      -
,  SQL   ,      . -
 ,   -  ,     
  ,   ,     
 .
 
 

 
 
 
       ,     
  ,   .   -
   ,    ,  -
  .       ,
        .
          -
  ,     - -
.      FROM .   -
:    ,  ,   
  .       
       (   
9.1):
 
 
 SELECT first.cname, second.cname, first.rating
 FROM Customers first, Customers second
 WHERE first.rating = second.rating;
 
 
 =============== SQL Execution Log ==============
 | |
 | Giovanni Giovanni 200 |
 | Giovanni Liu 200 |
 | Liu Giovanni 200 |
 | Liu Liu 200 |
 | Grass Grass 300 |
 | Grass Cisneros 300 |
 | Clemens Hoffman 100 |
 | Clemens Clemens 100 |
 | Clemens Pereira 100 |
 | Cisneros Grass 300 |
 | Cisneros Cisneros 300 |
 | Pereira Hoffman 100 |
 | Pereira Clemens 100 |
 | Pereira Pereira 100 |
 | |
 ===============================================
 
 
 9.1:    
 
 
(      9.1,     
,        ,  -
  . )
 
 
   , SQL   ,     -
    ''  ''.   - ,
 ,       -
.         FROM
,     .    -
     SELECT,     -
   FROM.
  -  . SQL      -
  ,         
  FROM .
   -     !  -
 ,        -
 .
 ,      ,   
, SQL          -
  -        -
       .
 
 
 
 
 
 
 
 

 
 
 
           -
,      .  ,  
      ,   ( -
)  . ,  A   
     B   ,  
 A         B
  .   , Hoffman    Cle-
mens,   Clemens    Hoffman.     
Cisneros  Grass, Liu  Giovanni,   .    -
     ,      - Liu 
Liu.     o  ,   -
   ,         
    .    -
,           -
 , :
 
 
 SELECT tirst.cname, second.cname, first.rating
 FROM Customers first, Customers second
 WHERE first.rating = second.rating
 AND first.cname < second.cname;
 
 
       9.2.
 
 
 Hoffman  Periera   ,  -
        . -
         -  Perie-
ra       Hoffman   -
  -    .  Hoffman 
            
       .    -
 
 
 =============== SQL Execution Log ==============
 | |
 | SELECT first.cname, second.cname, first.rating |
 | FROM Customers first, Customers second |
 | WHERE first.rating = second.rating |
 | AND first.cname < second.cname |
 | =============================================== |
 | cname cname rating |
 | ------- --------- ------- |
 | Hoffman Pereira 100 |
 | Giovanni Liu 200 |
 | Clemens Hoffman 100 |
 | Pereira Pereira 100 |
 | Gisneros Grass 300 |
 =================================================
 
 
 9.2:       .
 
 
          , 
    < =  <.
 
 
 
 

 
 
 
        SQL  
  .    ,  
   cnum  snum    .  
          -
,         
,        . 
       :
 
 
 SELECT first.onum, tirst.cnum, first.snum,
 second.onum, second.cnum,second.snum
 FROM Orders first, Orders second
 WHERE first.cnum = second.cnum
 AND first.snum < > second.snum;
 
 
    ,     . 
     ,    
,          -
   ,   .   
 ,    .    -
    ,     cnum=2008
  snum=1007,         
   cnum.      -   
     snum,   ,  -
      .    snum
   cnum    ,    -
  .
 
 

 
 
 
      -     
  ,        
       .   -
         -
      . ,   
     ,     
 ,  a  b,     
   SELECT  .    -
   (   11).
 
 

 
 
 
 
          
,        SELECT *
 .        -
   .      -
  ,      -
.         -
,         
     . (  -
   9.3 ):
 
 
 SELECT a.cnum, b.cnum, c.cnum
 FROM Customers a, Customers b, Customers c
 WHERE a.rating = 100
 AND b.rating = 200
 AND c.rating = 300;
 
 
 =============== SQL Execution Log ==============
 | |
 | AND c.rating = 300; |
 | =============================================== |
 | cnum cnum cnum |
 | ----- ------ ------ |
 | 2001 2002 2004 |
 | 2001 2002 2008 |
 | 2001 2003 2004 |
 | 2001 2003 2008 |
 | 2006 2002 2004 |
 | 2006 2002 2008 |
 | 2006 2003 2004 |
 | 2006 2003 2008 |
 | 2007 2002 2004 |
 | 2007 2002 2008 |
 | 2007 2003 2004 |
 | 2007 2003 2008 |
 =================================================
 
 
  9.3     
 
 
 
    ,      
   ,      -
   100,   200,     300.  -
    .  -   -
      GROUP BY  ORDER BY,  
      .
    ,     
        FROM -
,   SELECT. ,    
        -
 . ,      -
     Serres ( snum 1002 )   (
    9.4 ):
 
 
 SELECT b.cnum, b.cname
 FROM Customers a, Customers b
 WHERE a.snum = 1002
 AND b.city = a.city;
 
 
 =============== SQL Execution Log ============
 | |
 | SELECT b.cnum, b.cname |
 | FROM Customers a, Customers b |
 | WHERE a.snum = 1002 |
 | AND b.city = a.city; |
 | ==============================================|
 | cnum cname |
 | ------ --------- |
 | 2003 Liu |
 | 2008 Cisneros |
 | 2004 Grass |
 =============================================
 
 
 
 
 9.4     
  Serres.
 
 
  a       
    snum = 1002.    -
 ,    Serres.  b  
            
  a;   ,   b    
     a.    -
 b -    a,     
 .    ,   Serres -
            , 
    a .  ,  -
   Serres, Liu  Grass.  b  
       ( San Jose  Berlin -
 ) , ,  - Liu  Grass.
 
 
         
    .   
   :      -
  .     ,   
        (  
  9.5 ):
 
 
 SELECT sname, Salespeople.snum, first.cname
 second.cname
 FROM Customers first, Customers second, Salespeople
 WHERE first.snum = second.snum
 AND Salespeople.snum = first.snum
 AND first.cnum < second.cnum;
 
 
 =============== SQL Execution Log ==================
 | |
 | SELECT cname, Salespeople.snum, first.cname |
 | second.cname |
 | FROM Customers first, Customers second, Salespeople |
 | WHERE first.snum = second.snum |
 | AND Salespeople.snum = first.snum |
 | AND first.cnum < second.cnum; |
 | ====================================================|
 | cname snum cname cname |
 | ------ ------ -------- -------- |
 | Serres 1002 Liu Grass |
 | Peel 1001 Hoffman Clemens |
 =====================================================
 
 
 9.5:        
 
 

 
 
 
         
    ,   ,  
 .       
  .     
 ,     (
      ,    -
       ).    ,
,     .
        -
     ,   
,         
   .     SQL, 
     10      -
.
 
 

SQL

 
 
 
1.           
    .      ,  
      .
2.           -
 ,   ,     , 
   .
3.      (cname)  (city) 
     (rating)   Hoffman.  -
    cnum Hoffman    ,   
        .
 
 
( .  A  . )

10.

 
 
 
 
    9,       
.   ,      (  
),      ,   
       .  -
         -
      SQL ,   DIS-
TINCT,      .   
    HAVING   
    .
 
 

 

?

 
 
 
   SQL       . -
,        -
  ,     . ,
     : Motika,    
  snum,        . -
     (     10.1 ):
 
 
 SELECT *
 FROM Orders
 WHERE snum =
 ( SELECT snum
 FROM Salespeople
 WHERE sname = 'Motika');
 
 
   (  ) , SQL   
  (   )   WHERE.  -
           -
     ,   sname  -
 Motika,      snum  .
 
 
      snum = 1004. 
SQL,     ,      -
    ,     
 
 
 WHERE snum = 1004
 
 
 
 
 
 =============== SQL Execution Log ==============
 | |
 | SELECT * |
 | FROM Orders |
 | WHERE snum = |
 | (SELECT snum |
 | FROM Salespeople |
 | WHERE sname = 'Motika'); |
 |=================================================|
 | onum amt odate cnum snum |
 | ----- ------- ---------- ----- ----- |
 | 3002 1900.10 10/03/1990 2007 1004 |
 | |
 =================================================
 
 
 10.1:  
 
 
         -
.  ,       
,           
     . ,   ,
        (  
, snum ),   .
 ,        Motika,   
 
 
 
 WHERE snum = 1004
 
 
      ,       -
.        Motika -
, ,       ,   -
    .
 
 
 

,

 
 
 
     ,      -
      .
    snum " WHERE city = "London"  "WHERE sname
= 'Motika",     .  
        
  ,    .
        
 (   ,     4 ),
        
     .     -
     ,    ;
      .   
   (  )  
      ,   . , -
         : 
     (   5  
    ).
   ,   -  :
 
 
 SELECT *
 FROM Orders
 WHERE snum =
 ( SELECT snum
 FROM Salespeople
 WHERE city = Barcelona );
 
 
        Barcelona - Rifkin, 
     snum   
.   -    .  SQL  
  ,     
   Barcelona  ,    -
,     .
 

DISTINCT

 
  ,   ,  DISTINCT  
   .    
        
Hoffman ( cnum = 2001 ).
       (    
10.2 ):
 
 SELECT *
 FROM Orders
 WHERE snum =
 ( SELECT DISTINCT snum
 FROM Orders
 WHERE cnum = 2001 );
 
 =============== SQL Execution Log ==============
 | SELECT * |
 | FROM Orders |
 | WHERE snum = |
 | (SELECT DISTINCT snum |
 | FROM Orders |
 | Where cnum = 2001); |
 | =============================================== |
 | onum amt odate cnum snum |
 | ----- --------- --------- ------ ------- |
 | 3003 767.19 10/03/1990 2001 1001 |
 | 3008 4723.00 10/05/1990 2006 1001 |
 | 3011 9891.88 10/06/1990 2006 1001 |
 ================================================
 
 
 10.2:  DISTINCT    
   
 
 
      snum   Hoffman - 1001,
          snum 
 (  ,    Hoffman  ). 
         ,  -
          cnum 
    snum.        -
  ,      (    )
 snum    cnum.  DISTINCT 
.       ,  
      -      .
        
      .    cnum -  -
   ,     
  .       
         .  
,        . ( SQL
    -     
-   .      22.) -
 ,      
    ,      
  ,    .    
    ,     -
.
 
 
 

 
 
 
 
        , -
 
 
 
 <   > <  > <  >,  
 <  > <  > <   > ,
 <  > <  > <  >.
 
 
  ,       :
 
 
 SELECT *
 FROM Orders
 WHERE ( SELECT DISTINCT snum
 FROM Orders
 WHERE cnum = 2001 )
 = snum;
 
 
   ANSI ,    ,  
     . ANSI   
     ,     -
 .
 
 
 

 
 
 
 
   ,     
    ,  , -  .
        
GROUP BY        -
 . ,       
    4-  (    
10.3 ):
 
 
 SELECT *
 FROM Orders
 WHERE amt >
 ( SELECT AVG (amt)
 FROM Orders
 WHERE odate = 10/04/1990 );
 
 
 =============== SQL Execution Log ==============
 | |
 | SELECT * |
 | FROM Orders |
 | WHERE amt > |
 | (SELECT AVG (amt) |
 | FROM Orders |
 | WHERE odate = 01/04/1990 ); |
 | =============================================== |
 | onum amt odate cnum snum |
 | ----- -------- ---------- ----- ----- |
 | 3002 1900.10 10/03/1990 2007 1004 |
 | 3005 2345.45 10/03/1990 2003 1002 |
 | 3006 1098.19 10/03/1990 2008 1007 |
 | 3009 1713.23 10/04/1990 2002 1003 |
 | 3008 4723.00 10/05/1990 2006 1001 |
 | 3010 1309.95 10/06/1990 2004 1002 |
 | 3011 9891.88 10/06/1990 2006 1001 |
 ================================================
 
 
 10.3:         10/04/1990
 
 
     4  - 1788.98 ( 1713.23 + 75.75)
 ,     = 894.49.    -
   amt   -  .
      ,  -
       GROUP
BY,    . , , 
    .   GROUP BY  HA-
VING   ,      
 ,     .   -
      WHERE  -
  . ,    
       -
 
 
 SELECT AVG (comm)
 FROM Salespeople
 GROUP BY city
 HAVlNG city = "London";
 
 
    !       -
  .     -
 
 
 SELECT AVG (comm)
 FROM Salespeople
 WHERE city = "London";
 
 
 
 

,

IN

 
 
 
        
      IN (  BETWEEN,
LIKE,  IS NULL      ).   -
, IN   ,      
     ,    -
.    IN  , SQL   
   .  , ,  IN
          -
 ,         
 (     10.4 ):
 
 
 SELECT *
 FROM Orders
 WHERE snum IN
 ( SELECT snum
 FROM Salespeople
 WHERE city = "LONDON" );
 
 
 =============== SQL Execution Log ==============
 | |
 | SELECT * |
 | FROM Orders |
 | WHERE snum IN |
 | (SELECT snum |
 | FROM Salespeople |
 | WHERE city = 'London'); |
 | =============================================== |
 | onum amt odate cnum snum |
 | ----- -------- ---------- ----- ------ |
 | 3003 767.19 10/03/1990 2001 1001 |
 | 3002 1900.10 10/03/1990 2007 1004 |
 | 3006 1098.19 10/03/1990 2008 1007 |
 | 3008 4723.00 10/05/1990 2006 1001 |
 | 3011 9891.88 10/06/1990 2006 1001 |
 ================================================
 
 
 10. 4:    IN
 
 
    ,  -    
          ,
     :
 
 
 SELECT onum, amt, odate, cnum, Orders.snum
 FROM Orders, Salespeople
 WHERE Orders.snum = Salespeople.snum
 AND Salespeople.city = "London";
 
 
              -
, SQL        
        .   -
       snum  city =
"London",        ,   -
    .     snums=1001
 snum=1004.  , ,      -
    snum .
  ,      , -
    -      .
     - ,   -
     .
        
 ,         
   .        -
   .
       IN,    -
     .    
      (=),  
 IN.     , IN   -
         
.       .  
    ;    
     ,   
.         -
. ,  ,    :
 
 
 SELECT onum, amt, odate
 FROM Orders
 WHERE snum =
 ( SELECT snum
 FROM Orders
 WHERE cnum = 2001 );
 
 
      DISTINCT  IN  (=),
 :
 
 
 SELECT onum, amt, odate
 FROM Orders
 WHERE snum IN
 ( SELECT snum
 FROM Orders
 WHERE cnum = 2001 );
 
 
            
 ?   IN     -
   .     
 ,        
      .   (
= ) ,   .
 ,   ,       -
.      ,   -
       .
  ,      (  ) 
  ,    = . IN  -
,         -
,        . ,  
       :
 
 SELECT comm
 FROM Salespeople
 WHERE snum IN
 ( SELECT snum
 FROM Customers
 WHERE city = "London" );
 
    ,    10.5, 
   Peel ( snum = 1001 ),  
   .  -    .  -
          -
  - . , IN -    
    .
 
 =============== SQL Execution Log ==============
 | SELECT comm |
 | FROM Salespeople |
 | WHERE snum IN |
 | (SELECT snum |
 | FROM Customers |
 | WHERE city = 'London'); |
 | =============================================== |
 | comm |
 | ------- |
 | 0.12 |
 ================================================
 10.5  IN      
 
 
  ,     city   -
 ,       city
    .
 SQL         
FROM  .        ,
  .   , "city"  -
 WHERE      Customer.city(  city
 ).       
FROM  , SQL    - .  -
        
,          -
 .   ,  ,  -
  .
 
 
 

 
 
 
 
        ,    -
  .  ,   
   .   ,  SE-
LECT *     .   
,      EXISTS,  
    12.
 
 
 

 
 
 
       ,   
 ,   SELECT .    
       IN. , 
    = (    -
 10.6 ):
 
 
 SELECT *
 FROM Customers
 WHERE cnum =
 ( SELECT snum + 1000
 FROM Salespeople
 WHERE sname = Serres );
 
 
        cnum  1000, 
 snum Serres.     sname   
  (      UNIQUE INDEX, -
   17,   UNIQUE,    18 );
 
 =============== SQL Execution Log ============
 | |
 | SELECT * |
 | FROM Customers |
 | WHERE cnum = |
 | (SELECT snum + 1000 |
 | WHERE Salespeople |
 | WHERE sname = 'Serres' |
 | ============================================= |
 | cnum cname city rating snum |
 | ----- -------- ---- ------ ----- |
 | 2002 Giovanni Rome 200 1003 |
 =============================================
 
 
 10.6:    
 
 
    .   snum 
num        
  ,    ,    -
 .
 
 
 
 

HAVING

 
 
 
 
        HAVING.
       
        GROUP
BY  HAVING.      (  -
   10.7 ):
 
 
 SELECT rating, COUNT ( DISTINCT cnum )
 FROM Customers
 GROUP BY rating
 HAVING rating >
 ( SELECT AVG (rating)
 FROM Customers
 WHERE city = " San Jose';
 
 
 =============== SQL Execution Log ============
 | |
 | SELECT rating,count (DISTINCT cnum) |
 | FROM Customers |
 | GROUP BY rating |
 | HAVING rating > |
 | (SELECT AVG (rating)snum + 1000 |
 | FROM Custimers |
 | WHERE city = 'San Jose' |
 
 |============================================ |
 | rating |
 | -------- -------- |
 | 200 2 |
 ================================================
 
 
 10.7:        San Jose
 
 
          San
Jose.        300,   
        .
 
 
 

 
 
 
 
       .  , 
      , -
      . 
       -
       IN,    WHERE
   HAVING  .
   ,    .  
 11,     ,   -
        . ,
  12  13,      
    ,    IN,  -
        -
.