SQL
SQL ( "") (
Structured Query Language). , , , ., , , . , , , , , , , , , . , , , .
, , SQL, , . , , 90- , SQL.
SQL
ANSI (American National Standard Institute ) ISO (International Standard Organization ). 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 Bro
ck, . , , , , . , , , 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 . , , .
(. A .)
1
SQL, , , , , SQL. . , . , . , SQL , . , SQL, 3.
SQL , . , , ,
C. C, . , , , , . , , :1. .
2. , , .
3. , -, .
4. , .
5. , 1.
6. , , 3.
(,
C , , .)SQL , . SQL , , .
, SQL ANSI ( ). SQL ANSI. , , IBM. SQL , , (Oracle), IBM SQL .
, SQL , ANSI , ( ANSI). . ANSI .
ANSI , , ANSI, . , , . ANSI SQL . , ANSI . , , , ANSI. ANSI , .SQL:
(Interactive) (Embedded). , , .SQL , . SQL, , , ( ) .
SQL SQL, , ( ). . , , SQL , SQL. SQL SQL ("
passed off") ., SQL . , , . SQL . , SQL, . , , .
, SQL, , . , , SQL, . , . ANSI , SQL , SQL.
DDL
(Data Definition Language ) ANSI, , (, , , ) .DML
(Data Manipulation Language ) , , .DCL
(Data Control Language ) , , .SQL ANSI. . , SQL, .
, . . . , , .
SQL , , , . . , ,
cname city , snum, cnum . , Highest () None () rating, . , . , , . , ., , SQL . ANSI SQL , . ,
DATA () TIME () ( ). , , , MONEY () BINARY ().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 ( , DATE, , , , ). , , , ., 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. , .
(. A .)
3
, . , . , (), , . , SQL.
, SQL " ". , SQL. , SQL , - - . , SQL , .
, , , . , , , , , ( ), .DML. , , , DML, , .
SQL . , , .
SELECT ()., SELECT , . , , :
SELECT snum, sname, city, comm
FROM Salespeople;
3.1.
=============== SQL Execution Log ============
| SELECT snum, sname, city, 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). |
|
; |
SQL, , . (\) , . |
, . , . , ,
, . , . SQL , . , . , , .( ENTER) . , , , :
SELECT snum, sname, city, comm FROM Salespeople;
SQL , , SQL ( ENTER) .
SQL SQL., , . (*) :
SELECT *
FROM Salespeople;
, .
, 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:
, , , . , .
, , , , . , "
SELECT *" , , , . , (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 30
| 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 SELECT. , DISTINCT , . , , , . DISTINCT, , , ( , , 6), , .
=============== SQL Execution Log ============
| SELECT DISTINCT snum |
| FROM Orders; |
| ============================================= |
| snum |
| ------- |
| 1001 |
| 1002 |
| 1003 |
| 1004 |
| 1007
===============================================
3.5: SELECT
ALL DISTINCT
DISTINCT ALL. , . , DISTINCT, ALL, ALL, , , .
, . , SQL , , .
WHERE
SELECT, , . , . , . :SELECT sname, comm
FROM Salespeople
WHERE city = 'London';
WHERE , , , . , Peel city, , 'London', . Serres , . 3.6.
=============== SQL Execution Log ============
| SELECT sname, comm |
| FROM Salespeople |
| WHERE city = 'London' |
| ============================================= |
| sname comm |
| ------- ---------- |
| Peel 0.12 |
| Motika 0.11 |
=======================
3.6. SELECT 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 1004 |
===============================================
3.7. SELECT .
, , . . , .
, , , , .
, , . , , SQL .
, , , . 4 , , , , .
(. 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.
=============== SQL Execution Log ============
| SELECT * |
| FROM Customers |
| WHERE rating > 200; |
| ============================================= |
| snum cname city rating snum |
| ----- -------- -------- ----- ------ |
| 2004 Cr
| 2008 Cirneros San Jose 300 1007 |
===============================================
4.1. " " (>)
, , 200,
rating >= 200
SQL. , , . / // . , SQL, :
AND, OR, NOT., , ( " "), 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. , .
=============== 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
OR, , San Jose (OR) 200.
SELECT *
FROM Customers
WHERE city = 'San Jose' OR rating > 200;
4.
3. =============== 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
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 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
===============================================
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 = 'San Jose', 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.
=============== 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
. , ( 4.6):
SELECT *
FROM Orders
WHERE NOT ((odate = 10/03/1990 AND snum >1002) OR amt > 2000.00);
=============== 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
| 3007 75.75 10/04/1990 2004 1002 |
| 3010 1309.95 10/06/1990 2004 1002 |
=================================================
4.6. () . Interbase :
SELECT *
FROM Orders
WHERE NOT ((odate = CAST('10/03/1990' AS DATE) AND snum >1002) OR amt > 2000.00);
, , , .
, , , , .
, . 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.
SELECT *
FROM Orders
WHERE (amt < 1000 OR NOT (odate = 10/03/1990 AND cnum > 2003));
SELECT *
FROM Orders
WHERE NOT ((odate = 10/03/1990 OR snum > 1006) AND amt > = 1500);
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 , . , , , Barcelona London, ( 5.1):
SELECT *
FROM Salespeople
WHERE city = 'Barcelona' OR city = 'London';
================ SQL Execution Log ============
| SELECT * |
| FROM Salespeople |
| WHERE city = 'Barcelona' |
| OR city = 'London'; |
| ==============================================|
| snum sname city comm |
| ------ ---------- ----------- ------- |
| 1001 Peel London 0.12 |
| 100
| 1007 Rifkin Barcelona 0.15 |
===============================================
5.1.
:
SELECT *
FROM Salespeople
WHERE city IN ('Barcelona', 'London');
5.2.
================ 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
, IN , . , . , . , , . snum = 1001, 1007, 1004. 5.3:
SELECT *
FROM Customers
WHERE cnum IN (1001, 1007, 1004);
=============== 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 IN. , IN, BETWEEN , , . BETWEEN , AND . IN, BETWEEN , . , , , SQL " (BETWEEN) , " BETWEEN AND . 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
| 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.
=============== 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
, , , , . , IN BETWEEN , , , ( IN) ( BETWEEN).
, , BETWEEN . , BETWEEN, .
, '
A' 'G':SELECT *
FROM Customers
WHERE cname BETWEEN 'A' AND 'G';
5.6.
=============== 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 100
===============================================
5.6. BETWEEN .
, Grass Giovanni . - , BETWEEN . 'G' , 'Giovanni', BETWEEN 'G' . ( ), 'Giovanni' . Grass. , BETWEEN . ( z).
LIKE
CHAR VARCHAR, , . , , , . (wildcards) -. 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 _ _ l% '; |
| ==============================================|
| 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 (//), (%).
, , , , , . SQL ,
NULL () , . NULL, , ( ). , . , NULL , . . , NULL SQL ., , . , , , . NULL snum , .
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 . , 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.
=============== SQL Execution Log ============
| SELECT * |
| FROM Salespeople |
| WHERE city NOT IN ('London', 'San Jose'; |
| ==============================================|
| snum sname city comm |
| ------ ---------- ----------- ------- |
| 1003 Rifkin Barcelona 0.15 |
| 1007 Axelrod New York 0.10 |
=============================
5.9. NOT IN
NOT BETWEEN NOT LIKE.
SQL. (
BETWEEN) (IN), (LIKE)., SQL NULL . NULL ,
IS NULL ( NOT NULL)., , SQL, , , . 6.
(. 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 . , . , DISTINCT, . , , ( 6.3):
SELECT COUNT (DISTINCT snum)
FROM Orders;
, 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 , , , , 6.4:
SELECT COUNT (*)
FROM Customers
=============== SQL Execution Log ============
| SELECT COUNT (*) |
| FROM Customers; |
| ==============================================|
| |
| ------- |
| 7 |
==
6.4.
COUNT NULL, , DISTINCT. DISTINCT , COUNT , , NULL . DISTINCT COUNT (*), , . , , , ( , ). , , - , , ,
COUNT .( )
ALL, , DISTINCT, . ANSI COUNT, .ALL * COUNT:
ALL .
ALL NULL.
* , NULL , COUNT; COUNT NULL . (COUNT) NULL rating ( ):
SELECT COUNT (ALL rating)
FROM Customers;
, . , , . ( , DISTINCT .) , , ( blnc) . , . :
SELECT MAX (blnc + amt)
FROM Orders;
blnc amt , . , , . , . , , .
, SQL, , 7.
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/19
| 1014 10/03/1990 1900.10 |
| 1007 10/03/1990 1098.16 |
=================================================
6.6.
, , , , .
, , , $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. :
SELECT snum, MAX (amt)
FROM Orders
WHERE odate = 10/03/1990
GROUP BY snum;
// Interbase:
WHERE odate = CAST('10/03/1988' AS DATE)
6.8.
=============== 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 IN (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 , odate.
-. , , . , , , . , . , , , .
GROUP BY. , , . , , .
, , . , HAVING.
, , , , 7, , .
(. 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 |
|
| 1003 Axelrod New York % 10.000000 |
===============================================
7.2. .
, . , . , , . , , . (. 7.3) :
SELECT ' For ', odate, ', there are ',
COUNT (DISTINCT onum), 'orders.'
FROM Orders
GROUP BY odate;
=============== 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. , , .
, 5 , , , , ( UNION, 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 |
| 3
| 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 , . , , ( SELECT) , ORDER BY, .
ORDER BY , GROUP BY . , ORDER BY . ORDER BY. , , :
SELECT snum, odate, MAX (amt)
FROM Orders
GROUP BY snum, odate
ORDER 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 , ORDER 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 |
===============================================
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) , , , . , ANSI . .
, , . : , 10 . , , , , .
.
, . , ORDER BY . , .
. , , , ORDER BY.
, , , , , . 8.
For the city (city), the highest rating is: (rating).
(. 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 |
| Clemens Peel London |
| Hoffman Motika London |
| Clemens Motika London |
| Liu Serres San Jose |
| Cisneros Serres San Jose |
=============================================
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.
=============== 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. .
, , , snum . .
, ; snum, . , , .
,
. , , WHERE (=). 'city='London' 'Salespeople.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
=================================================
8.4. .
, , , ( snum ), ( cnum snum ).
. , , . , , . : , . 9.
(. 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. . . Interbase 'SECOND' , , , :
SELECT a.cname, b.cname, a.rating
FROM Customers a, Customers b
WHERE a.rating = b.rating;
, 9.1., , , , , .
, SQL , '' ''. , , , . FROM , .
, SELECT, FROM.
. SQL , , FROM .
, . , , , .
, , , SQL , .
, , . , , () .
,
A B , A B . , Hoffman Clemens, Clemens Hoffman. Cisneros Grass, Liu Giovanni, . , , , Liu Liu., , , , . , , :
SELECT first.cname, second.cname, first.rating
FROM Customers first, Customers second
WHERE first.rating = second.rating
AND first.cname < second.cname;
9.2.
=============== 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 |
| Clemens Pereira 100 |
| Cisneros Grass 300
=================================================
9.2. .
Interbase 'second' - .
Hoffman Periera , . Periera Hoffman . , Hoffman , . ,
<= <., SQL . , , cnum snum .
, , , . :
SELECT first.onum, first.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 , .
, , , , . , (.
Interbase ). , , , 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 ==============
| 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
| 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. . . Interbase 'second' .
, , . .
, ( , ). , , , ., , , . SQL, 10 .
(. A .)
10
9, , . , ( ), , . , SQL, DISTINCT, . , HAVING , .
SQL . , , , , .
sname Motika, snum, . , ( 10.1):
SELECT *
FROM Orders
WHERE snum = (SELECT snum
FROM Salespeople
WHERE sname = 'Motika');
() , SQL ( ) WHERE. , , sname Motika, snum .
, , snum = 1004. SQL , , , 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/
=================================================
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 , , .
NCTDISTINCT, . , , 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
| 3011 9891.88 10/06/1990 2006 1001 |
=================================================
10.2. DISTINCT .
, snum Hoffman 1001, snum ( , Hoffman ). , , cnum snum. , , ( ) snum cnum. DISTINCT . , .
, , . cnum ( . 19) , , . , . , (SQL , , , - ; 22).
, , , , , , , . , .
, , ,
< > <> <>,
<> <> < > ,
<> <> <>
., :
SELECT *
FROM Orders
WHERE (SELECT DISTINCT snum
FROM Orders
WHERE cnum = 2001) = snum;
ANSI , , ( ,
Interbase ). ANSI , ., , , . , GROUP BY, . , , 4- ( 10.3):
SELECT *
FROM Orders
WHERE amt > (SELECT AVG (amt)
FROM Orders
WHERE odate = 10/04/1990);
Interbase :
SELECT *
FROM Orders
WHERE amt > (SELECT AVG (amt)
FROM Orders
WHERE odate = CAST('10/04/1990' AS DATE));
=============== 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 5160,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 HAVING , , . WHERE, . , ,
SELECT AVG (comm)
FROM Salespeople
GROUP BY city
HAVING city = 'London;
! , .
SELECT AVG (comm)
FROM Salespeople
WHERE city = 'London';
, , 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', , . snum=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 |
| ------- |
|
=================================================
10.5: IN
, city , city .
SQL , FROM . , . , "city" WHERE , Customer.city ( city ).
FROM , SQL , . , , . , , .
, . , . , SELECT * . , EXISTS, 12.
, , , SELECT . , IN. , = ( 10.6):
SELECT *
FROM Customers
WHERE cnum = (SELECT snum + 1000
FROM Salespeople
WHERE sname = 'Serres');
=============== 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. .
, cnum 1000, snum Serres. , sname ( UNIQUE INDEX, 17, UNIQUE, 18), . snum num , , .
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) |
| FROM Customers |
| WHERE city = 'San Jose' |
|===============================================|
| rating |
| -------- -------- |
| 300 2
===============================================
10.7. San Jose.
San Jose. 300, , .
. , , . , IN, WHERE, HAVING .
, . 11, , . , 12 13, , , IN, , .
(. A .)
11
, , 10 . , WHERE HAVING. , , , .
SQL, FROM, . , , .
SQL - .
, , , .
, 3- ( 11.1):
SELECT *
FROM Customers outer
WHERE 10/03/1990 IN (SELECT odate
FROM Orders inner
WHERE outer.cnum = inner.cnum);
=============== SQL Execution Log ============
| SELECT * |
| FROM Customers outer |
| WHERE 10/03/1990 IN |
| (SELECT odate |
| FROM Orders inner |
| WHERE outer.cnum = inner.cnum); |
| ============================================= |
| cnum cname city rating snum |
| ----- -------- ---- ------ ----- |
| 2001 Hoffman London 100 1001 |
| 2003 Liu San Jose 200 1002 |
| 2008 Cisneros San Jose 300 1007 |
| 2007 Pereira Rome 100 1004 |
===========
11.1. . . Interbase "INNER" "OUTER" , (JOIN TYPE) , , :
SELECT *
FROM Customers out
WHERE CAST('10/03/1990' AS DATE) IN (SELECT odate
FROM Orders inn
WHERE out.cnum = inn.cnum);
, "" (inner) "" (outer), , 9. ; , . cnum , . , , -. , :
- . -;
- - FROM ;
- . , , , ( ""), -. -
- - , .
, SQL :
- Hoffman ;
- - "";
- . , , cnum , outer.cnum, 2001 cnum Hoffman. odate , , odate;
- odate, cnum = 2001, , , 3 . ( ), Hoffman .
- , Giovanni -, .
, , SQL . , , ( 11.2):
SELECT *
FROM Customers first, Orders second
WHERE first.cnum = second.cnum AND second.odate = 10/03/1990;
, Cisneros , , . , SELECT DISTINCT SELECT. . IN, , , , . , DISTINCT .
========================= SQL Execution Log ========================
|SELECT * |
|FROM Customers first, Orders second |
|WHERE first.cnum = second.cnum AND second.odate = 10/03/1990; |
|======================================================================|
|cnum cname city rating snum onum amt odate cnum1 snum1|
|---- -------- -------- ------ ---- ---- ------- ---------- ----- -----| |2001 Hoffman London 100 1001 3003 767,19 10/03/1990 2001 1001|
|1002 Liu San Jose 200 1002 3005 5160,45 10/03/1990 2003 1002|
|2007 Pereira Rome 100 1004 3002 1900,10 10/03/1990 2007 1004|
|2008 Cisneros San Jose 300 1007 3001 18,69 10/03/1990 2008 1007|
|2008 Cisneros San Jose 300 1007 3006 1098,16 10/03/1990 2008 1007|
======================================================================
11.2. .
, , . ( 11.3):
SELECT snum, sname
FROM Salespeople main
WHERE 1 < (SELECT COUNT (*)
FROM Customers
WHERE snum = main.snum);
=============== SQL Execution Log ==========
|SELECT snum, sname |
|FROM Salespeople main |
|WHERE 1 < (SELECT COUNT (*) |
| FROM Customers |
| WHERE snum = main.snum); | |=============================================|
|snum sname |
|-
|1001 Peel |
|1002 Serres |
=============================================
11.3. , .
, FROM . , SQL , , FROM . ( snum) , SQL . , . , - .
, , . , , , , . . , , snum cnum , , . , , ( cnum, , ).
SELECT *
FROM Orders main
WHERE NOT snum = (SELECT snum
FROM Customers
WHERE cnum = main.cnum);
( 19), . , , , .
, , . . , ( 11.4):
SELECT *
FROM Orders outer
WHERE amt > (SELECT AVG (amt)
FROM Orders inner
WHERE inner.cnum = outer.cnum);
=============== SQL Execution Log ==============
| SELECT * |
| FROM Orders outer |
| WHERE amt > |
| (SELECT AVG (amt) |
| FROM Orders inner |
| WHERE inner.cnum = outer.cnum |
| =============================================== |
| onum amt odate cnum snum |
| ----- -------- ---------- ----- ------ |
|
| 3010 1309.00 10/06/1990 2004 1002 |
| 3011 9891.88 10/06/1990 2006 1001 |
=================================================
11.4. .
, , , , , . ( 11.5):
SELECT *
FROM Orders outer
WHERE amt >= (SELECT AVG (amt)
FROM Orders inner
WHERE inner.cnum = outer.cnum);
=============== SQL Execution Log ==============
| SELECT * |
| FROM Orders outer |
| WHERE amt > = |
| (SELECT AVG (amt) |
| FROM Orders inner |
| WHERE inner.cnum = outer.cnum); |
| =============================================== |
| onum amt odate cnum snum |
| ----- -------- ---------- ----- ------ |
| 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.19 10/03/1990 2008 1007 |
| 3009 1713.23 10/04/1990 2002 1003 |
| 3010 1309.95 10/06/1990 2004 1002 |
| 3011 9891.88 10/06/1990 2006 1001 |
==============
11.5. , >= .
, , , , ( , ).
. , , "inner" "outer", Interbase (, ) , , , .
HAVING , . HAVING, , HAVING. 6, HAVING , SELECT GROUP BY. , . , HAVING , . , , .
, , , , (
SUM) , , 2000.00 (MAX) :Select odate, SUM (amt)
FROM orders a
GROUP BY odate
HAVING SUM (amt) > (SELECT 2000.00 + MAX (amt)
FROM Orders b
WHERE a.odate = b.odate);
MAX , . , , WHERE. GROUP BY HAVING.
, ( ) . , , , .
, DISTINCT . , , . , , , , , .
, , , . , , , , , , , .
SQL . , , , HAVING. , .
SQL. , IN, IN, . , 12,
EXISTS.(. A .)
12
, , , . . .
EXISTS , , . , () . , , , , NULL . , , , , .
EXISTS
, , , (. 4 ). , , AND, OR, NOT. , , , . , . , , , , , San Jose ( 12.1):SELECT cnum, cname, city
FROM Customers
WHERE EXISTS (SELECT *
FROM Customers
WHERE city = 'San Jose');
=============== SQL execution Log ============
| SELECT snum, sname, city |
| FROM Customers |
| WHERE EXISTS |
| (SELECT * |
| FROM Customers |
| WHERE city = 'San Jose'); |
| ============================================= |
| cnum cname city |
| ----- -------- ---- |
| 2001 Hoffman London |
| 2002 Giovanni Rome |
| 2003 Liu San Jose |
| 2004 Grass Berlin |
| 2006 Clemens London |
| 2008
| 2007 Pereira Rome |
===============================================
12.1. EXISTS.
San Jose. EXISTS , , EXISTS , . ( ) , , , . EXISTS, , , .
EXISTS, EXISTS , , , , (SELECT *). , ( 10) .
, EXISTS , , , , .
EXISTS , , , . EXISTS , , . , , , EXISTS . , , ( 12.2):
SELECT DISTINCT snum
FROM Customers outer
WHERE EXISTS (SELECT *
FROM Customers inner
WHERE inner.snum = outer.snum AND
inner.cnum <> outer.cnum);
============= SQL Execution Log ==============
| SELECT DISTINCT cnum |
| FROM Customers outer |
| WHERE EXISTS |
| (SELECT * |
| FROM Customers inner |
| WHERE inner.snum = outer.snum |
| AND inner.cnum <> outer.cnum); |
|===============================================|
| cnum |
| ----- |
| 1001 |
| 1002 |
===============================================
12.2. EXISTS .
- ( , ), , snum ( ), cnum ( ). , , , (.. - ). EXISTS , (snum) , , . DISTINCT ,
, ., . , ( 12.3):
SELECT DISTINCT first.snum, sname, first.city
FROM Salespeople first, Customers second
WHERE EXISTS (SELECT *
FROM Customers third
WHERE second.snum = third.snum AND
second.cnum <> third.cnum) AND
first.snum = second.snum;
============= SQL Execution Log ==============
| SELECT DISTINCT first.snum, sname, first.city |
| FROM Salespeople first, Customers second |
| WHERE EXISTS |
| (SELECT * |
| FROM Customers third |
| WHERE second.snum = third.snum |
| AND second.cnum <> third.cnum) |
| AND first.snum = second.snum; |
|===============================================|
| cnum cname city |
| ----- -------- ---- |
| 1001 Peel London |
| 1002 Serres San Jose |
=====
12.3. EXISTS .
, , , . , , . (
AND first.snum = second.snum) , EXISTS. , snum, . - AND, . , , , . , ., EXISTS . , , EXISTS
NOT. , , , . ( 12.4.)SELECT DISTINCT snum
FROM Customers outer
WHERE NOT EXISTS (SELECT *
FROM Customers inner
WHERE inner.snum = outer.snum AND
inner.cnum <> outer.cnum);
============== SQL Execution Log =============
| SELECT DISTINCT snum |
| FROM Salespeople outer |
| WHERE NOT EXISTS |
| (SELECT * |
| FROM Customers inner |
| WHERE inner.snum = outer.snum |
| AND inner.cnum <> outer.cnum); |
|===============================================|
| cnum |
| ----- |
| 1003 |
| 1004 |
| 1007
===============================================
12.4. NOT EXISTS.
, EXISTS . . , EXISTS , , ; , EXISTS .
EXISTS , , , .
, EXISTS . ( ). , , , . .
, , , . ,
EXISTS (SELECT COUNT (DISTINCT sname) FROM Salespeople) EXISTS (SELECT sname FROM Salespeople) ..
, . , , SQL, .
, , . , , , SQL. :
SELECT *
FROM Salespeople first
WHERE EXISTS (SELECT *
FROM Customers second
WHERE first.snum = second.snum AND
1 < (SELECT COUNT (*)
FROM Orders
WHERE Orders.cnum = second.cnum));
12.5.
============== SQL Execution Log =============
| FROM Salespeople first |
| WHERE EXISTS |
| (SELECT * |
| FROM Customers second |
| WHERE first.snum = second.snum |
| AND 1 < |
| (SELECT CONT (*) |
| FROM Orders |
| WHERE Orders.cnum = second.cnum)); |
|===============================================|
| cnum cname city comm |
| ----- -------- ---- -------- |
| 1001 Peel London 0.17 |
| 1002 Serres San Jose 0.13 |
| 1007 Rifkin Barselona
===============================================
12.5. EXISTS .
:
- ( ) . - ( ). (.. first.snum <> second.snum), . , , , , , . ( ). 1, , . EXISTS , , , , .
, . , . , , . , , SQL, .
, , , . , , , , ( , SQL). , , , , . , , ( , 20).
EXISTS, , SQL. . , EXISTS. , .
, , ANY, ALL, SOME. 13, , , .
(. A .)
13
, EXISTS, . (, , ANY SOME .) , , SQL. , , , , .
ANY
, ALL, SOME EXISTS, ; EXISTS , . , IN, ; , , , . , IN, .SOME ANY ANY, SOME . , , . ; , , .
, ( 13.1):
SELECT *
FROM Salespeople
WHERE city = ANY (SELECT city
FROM Customers);
ANY , ( city ), , (ANY) .
=============== SQL Execution Log ============
| SELECT * |
| FROM Salespeople |
| WHERE city = ANY |
| (SELECT city |
| FROM Customers); |
| ============================================= |
| cnum cname city comm |
| ----- -------- ---- -------- |
| 1001 Peel London 0.12 |
| 1002 Serres San Jose 0.13 |
| 1004 Motika London 0.11 |
=====================
13.1. ANY.
, , , . EXISTS, , , .
IN, , :
SELECT *
from Salespeople
WHERE city IN (SELECT city
FROM Customers);
, 13.2.
=============== SQL Execution Log ============
| SELECT * |
| FROM Salespeople |
| WHERE city IN (SELECT city |
| FROM Customers); |
| ============================================= |
| cnum cname city comm |
| ----- -------- ---- -------- |
| 1001 Peel London 0.12 |
| 1002 Serres San Jose 0.13 |
| 1004 Motika London 0.11 |
===
13.2. IN ANY.
ANY , (=), , , , IN. , , ( 13.3).
SELECT *
FROM Salespeople
WHERE sname < ANY (SELECT cname
FROM Customers);
=============== SQL execution Log ============
| SELECT * |
| FROM Salespeople |
| WHERE sname < ANY |
| (SELECT cname |
| FROM Customers); |
| ============================================= |
| cnum cname city comm |
| ----- -------- ---- -------- |
| 1001 Peel London 0.12 |
| 1003 Axelrod New York 0.10 |
===============================================
13.3. ANY "" (<).
, Serres Rifkin, , . , EXISTS ( 13.4):
SELECT *
FROM Salespeople outer
WHERE EXISTS (SELECT *
FROM Customers inner
WHERE outer.sname < inner.cname);
=============== SQL Execution Log ============
| SELECT * |
| FROM Salespeople outer |
| WHERE EXISTS |
| (SELECT * |
| FROM Customers inner |
| WHERE outer.sname < inner.cname); |
| ============================================= |
| cnum cname city comm |
| ----- -------- ---- -------- |
| 1001 Peel London 0.12 |
| 1004 Motika London 0.11 |
| 1003 Axelrod New York 0.10 |
===============================================
, ANY (, , ALL), EXISTS, . , EXISTS ANY ALL - , (NULL) ( ). , , ANY ALL, EXISTS ( IS NULL).
, , ANY ALL , EXISTS, . , , ANY ALL , , , , EXISTS.
ANY ALL , . EXISTS, , , , . SQL , .
EXISTS ANY ALL , ANY ALL , - , . , , .
, ANY . , , , , , ( 13.5):
SELECT *
FROM Customers
WHERE rating > ANY (SELECT rating
FROM Customers
WHERE city = 'Rome');
, " ( city = 'Rome')", , , , city = 'Rome'. , ANY, SQL. ANY , , .
=============== SQL Execution Log ============
| SELECT * |
| FROM Customers |
| WHERE rating > ANY |
| (SELECT rating |
| FROM Customers |
| WHERE city = 'Rome'); |
| ============================================= |
| cnum cname city rating snum |
| ----- -------- ---- ------ ------ |
| 2002 Giovanni Rome 200 1003 |
| 2003 Liu San Jose 200 1002 |
| 2004 Grass Berlin 300 1002 |
| 2008 Cisneros San Jose 300 1007 |
==============================
13.5. " " (>) ANY.
ANY , , 300 Giovanni, 200. , ANY Periera 100. 200 , , (Giovanni), (, , , ). , , , , .
, , , , , 6- :
SELECT *
FROM Orders
WHERE amt > ANY (SELECT amt
FROM Orders
WHERE odate = 10/06/1990);
13.6.
=============== SQL Execution Log ==============
| SELECT * |
| FROM Orders |
| WHERE amt > ANY |
| (SELECT amt |
| FROM Orders |
| WHERE odate = 10/06/1990); |
| =============================================== |
| onum amt odate cnum snum |
| ----- -------- ---------- ----- ------ |
| 3002 1900.10 10/03/1990 2007 1004 |
| 3005 5160.45 10/03/1990 2003 1002 |
| 3009 1713.23 10/04/1990 2002 1003 |
| 3008 4723.00 10/05/1990 2006 1001 |
| 3011 989
=================================================
13.6. (ANY) 6- .
(9891.88) 6- , , 6- , = 1309.95.
">=" ">", , ., ANY SQL , . , San Jose. ( 13.7):
SELECT *
FROM Orders
WHERE amt < ANY (SELECT amt
FROM Orders A, Customers b
WHERE a.cnum = b.cnum
AND b.city = 'San Jose');
San Jose, ; . ,
< ANY , > ANY . =============== SQL Execution Log ==============
| WHERE amt > ANY |
| (SELECT amt |
| FROM Orders a, Customers b |
| WHERE a.cnum = b.cnum |
| AND b.city = 'San Jose'); |
| =============================================== |
| onum amt odate cnum snum |
| ----- -------- ---------- ----- ------ |
| 3001 18.69 10/03/1990 2008 1007 |
| 3003 767.10 10/03/1990 2001 1001 |
| 3002 1900.10 10/03/1990 2007 1004 |
| 3006 1098.10 10/03/1990 2008 1007 |
| 3009 1713.23 10/04/1990 2002 1003 |
| 3007 75.10 10/04/1990 2004 1002 |
| 3008 4723.00 10/05/1990 2006 1001 |
| 3010 1309.88 10/06/1990 2004 1002 |
================================
13.7. ANY .
, ( 13.8):
SELECT *
FROM Orders
WHERE amt < (SELECT MAX (amt)
FROM Orders a, Customers b
WHERE a.cnum = b.cnum AND b.city = 'San Jose');
=============== SQL Execution Log ==============
| WHERE amt < |
| (SELECT MAX (amt) |
| FROM Orders a, Customers b |
| WHERE a.cnum = b.cnum |
| AND b.city = 'San Jose'); |
| =============================================== |
| onum amt odate cnum snum |
| ----- -------- ---------- ----- ------ |
| 3002 1900.10 10/03/1990 2007 1004 |
| 3005 5160.45 10/03/1990 2003 1002 |
| 3009 1713.23 10/04/1990 2002 1003 |
| 3008 4723.00 10/05/1990 2006 1001 |
| 3011 9891.88 10/06/1990
=================================================
13.8. ANY.
ALL, , . , , , , , , , 1
3.9:SELECT *
FROM Customers
WHERE rating > ALL (SELECT rating
FROM Customers
WHERE city = 'Rome');
=============== SQL Execution Log ============
| SELECT * |
| FROM Customers |
| WHERE rating > ALL |
| (SELECT rating |
| FROM Customers |
| WHERE city = 'Rome'); |
| ============================================= |
| cnum cname city rating snum |
| ----- -------- ---- ------ ------ |
| 2004 Grass Berlin 300 1002 |
| 2008 Cisneros San Jose 300 1007 |
============================
13.9. ALL.
. , . Giovanni (200). , 200.
ANY, EXISTS ( 13.10):
SELECT *
FROM Customers outer
WHERE NOT EXISTS (SELECT *
FROM Customers inner
WHERE outer.rating <= inner.rating
AND inner.city = 'Rome');
=============== SQL Execution Log ============
| SELECT * |
| FROM Customers outer |
| WHERE NOT EXISTS |
| (SELECT * |
| FROM Customers inner |
| WHERE outer rating <= inner.rating |
| AND inner.city = 'Rome'); |
| ============================================= |
| cnum cname city rating snum |
| ----- -------- ---- ------ ------ |
| 2004 Grass Berlin 300 1002 |
| 2008 Cisne
===============================================
13.10. EXISTS ALL.
ALL , , " " , , . :
SELECT *
FROM Customers
WHERE rating = ALL (SELECT rating
FROM Customers
WHERE city = 'San Jose');
, . San Jose . , :
SELECT *
FROM Customers
WHERE rating = (SELECT DISTINCT rating
FROM Customers
WHERE city = 'San Jose');
, , , ALL . , , , . , , .
, ALL , "
<>". , SQL , , , a ., , , . SQL,
<> ALL " " . , , . , ( 13.11):SELECT *
FROM Customers
WHERE rating <> ALL (SELECT rating
FROM Customers
WHERE city = 'San Jose');
=============== SQL Execution Log ============
| SELECT * |
| FROM Customers |
| WHERE rating <> ALL |
| (SELECT rating |
| FROM Customers |
| WHERE city = 'San Jose'); |
| ============================================= |
| cnum cname city rating snum |
| ----- -------- ---- ------ ------ |
| 2001 Hoffman London 100 1001 |
| 2006 Clemens London 100 1001 |
| 2007 Pereira Rome 10
===============================================
13.11. ALL <>.
San Jose. : 200 ( Liu) 300 ( Cisneros). , , , , 100. , NOT IN:
SELECT*
FROM Customers
WHERE rating NOT IN (SELECT rating
FROM Customers
WHERE city = 'San Jose');
ANY:
SELECT *
FROM Customers
WHERE NOT rating = ANY (SELECT rating
FROM Customers
WHERE city = 'San Jose');
.
SQL , ( ) (ANY) , ( ) . , , (ALL) , , , .
, EXISTS , , , NULL. ANY ALL , , , . , .
ALL ANY , . , , , ALL , ANY . ,
SELECT *
FROM Customers
WHERE rating > ANY (SELECT rating
FROM Customers
WHERE city = 'Boston');
,
SELECT *
FROM Customers
WHERE rating > ALL (SELECT rating
FROM Customers
WHERE city = 'Boston');
. Boston, , .
NULL . SQL , (NULL), ( 5). , , , , , ALL ANY EXISTS. :
SELECT *
FROM Customers
WHERE rating > ANY (SELECT rating
FROM Customers
WHERE city = 'Rome');
:
SELECT *
FROM Customers outer
WHERE EXISTS (SELECT *
FROM Customers inner
WHERE outer.rating > inner.rating
AND inner.city = 'Rome');
, . , (NULL) rating :
|
CNUM |
CNAME |
CITY |
RATING |
SNUM |
|
2003 |
Liu |
SanJose |
NULL |
1002 |
ANY, Liu , NULL , Liu . , NOT EXISTS, , NULL , . , , EXISTS . , , NOT EXISTS . , Liu . , , EXISTS , . ANY. , NULL , . , , .
, ANY ALL EXISTS, . , EXISTS NOT EXISTS COUNT (*) SELECT . , EXISTS; NOT EXISTS. ( 13.12).
SELECT *
FROM Customers outer
WHERE NOT EXISTS (SELECT *
FROM customers inner
WHERE outer.rating <= inner.rating
AND inner.city = 'Rome');
=============== SQL Execution Log ============
| SELECT * |
| FROM Customers outer |
| WHERE NOT EXISTS |
| (SELECT * |
| FROM Customers inner |
| WHERE outer.rating <= inner.rating |
| AND inner.city = 'Rome'); |
| ============================================= |
| cnum cname city rating snum |
| ----- -------- -
| 2004 Grass Berlin 300 1002 |
| 2008 Cisneros San Jose 300 1007 |
===============================================
13.12. EXISTS .
SELECT *
FROM Customers outer
WHERE 1 > (SELECT COUNT (*)
FROM Customers inner
WHERE outer.rating <= inner.rating
AND inner.city = 'Rome');
13.13. , SQL. , . , , . , , .
=============== SQL Execution Log ============
| SELECT * |
| FROM Customers outer |
| WHERE 1 > |
| (SELECT COUNT (*) |
| FROM Customers inner |
| WHERE outer.rating <= inner.rating |
| AND inner.city = 'Rome'); |
| ============================================= |
| cnum cname city rating snum |
| ----- -------- ---- ------ ------ |
| 2004 Grass Berlin 300 1002 |
| 2008 Cisneros San Jose 300 10
===============================================
13.13. COUNT EXISTS.
, . , , . , . , , . , , (NULL) .
, , , , , , SQL, .
, , , , UNION.
(. A .)
14
, . .. . UNION SQL.
UNION ( ) . , ., UNION. UNION SQL . , , , , :
SELECT snum, sname
FROM Salespeople
WHERE city = 'London'
UNION
SELECT cnum, cname
FROM Customers
WHERE city = 'London';
, 14.1.
, , , , . , , . , ( 7, ).
, , . SQL, .
=============== SQL Execution Log ============
| |
| SELECT snum, sname |
| FROM Salespeople |
| WHERE city = 'London' |
| UNION |
| SELECT cnum, cname |
| FROM Customers |
| WHERE city = 'London'; |
| ============================================= |
| |
| ----- -------- |
| 1001 Peel |
| 1004 Motika |
| 2001 Hoffman |
| 2006 Clemens |
| |
========================================
14.1. .
( ) , . , , , , , , . . ANSI , , ANSI , ( B ANSI). , .
, SQL , ANSI. , ANSI, DATA BINARY, .
. , UNION. , ( ANSI ), , . .
(NULL) , . (NULL) NOT NULL, 18. , UNION , SELECT . ( .)
UNION . SQL, DISTINCT, . , , 14.2,
SELECT snum, city
FROM Customers;
(snum=1001, city=
'London'), , SQL . , UNION , . 14.3 .SELECT snum, city
FROM Customers
UNION
SELECT snum, city
FROM Salespeople;
=============== SQL Execution Log ============
| SELECT snum, city |
| FROM Customers; |
| ============================================= |
| snum city |
| ----- -------- |
| 1001 London |
| 1003 Rome |
| 1002 San Jose |
| 1002 Berlin |
| 1001 London
| 1004 Rome |
| 1007 San Jose |
===============================================
14.2. .
=============== SQL Execution Log ============
| FROM Customers |
| UNION |
| SELECT snum, city |
| FROM Salespeople; |
| ============================================= |
| |
| ----- -------- |
| 1001 London |
| 1002 Berlin |
| 1007 San Jose |
| 1007 New York |
| 1003 Rome |
| 1001 London |
| 1003 Rome |
| 1002 Barcelona |
| 1007 San Jose |
==============================
14.3. UNION .
SQL, UNION ALL UNION, :
SELECT snum, city
FROM Customers
UNION ALL
SELECT snum, city
FROM Salespeople;
, SELECT, UNION. ANSI, . , , , . , , , , .
, , . , , .
SELECT a.snum, sname, onum, 'Highest on', odate
FROM Salespeople a, Orders b
WHERE a.snum = b.snum AND b.amt = (SELECT MAX (amt)
FROM Orders c
WHERE c.odate = b.odate)
UNION
SELECT a.snum, sname, onum, 'Lowest on ', odate
FROM Salespeople a, Orders b
WHERE a.snum = b.snum AND b.amt = (SELECT MIN (amt)
FROM Orders c
WHERE c.odate = b.odate);
14.4.
'Lowest on', 'Highest on'. , Peel (, ) 5 . , .
=============== SQL Execution Log ============
| AND b.amt = |
| (SELECT min (amt) |
| FROM Orders c |
| WHERE c.odate = b.odate); |
| ============================================= |
| |
| ----- ------- ------ ---------- ----------- |
| 1001 Peel 3008 Highest on 10/05/1990 |
| 1001 Peel 3008 Lowest on 10/05/1990 |
| 1001 Peel 3011 Highest on 10/06/1990 |
| 1002 Serres 3005 Highest on 10/03/1990 |
| 1002 Serr
| 1002 Serres 3010 Lowest on 10/06/1990 |
| 1003 Axelrod 3009 Highest on 10/04/1990 |
| 1007 Rifkin 3001 Lowest on 10/03/1990 |
===============================================
14.4. .
, . , . , , . , . ORDER BY ,
. . , Peel , 14.5.SELECT a.snum, sname, onum, 'Highest on', odate
FROM Salespeople a, Orders b
WHERE a.snum = b.snum AND b.amt = (SELECT MAX (amt)
FROM Orders c
WHERE c.odate = b.odate)
UNION
SELECT a.snum, sname, onum, 'Lowest on', odate
FROM Salespeople a, Orders b
WHERE a.snum = b.snum AND b.amt = (SELECT MIN (amt)
FROM Orders c
WHERE c.odate = b.odate)
ORDER BY 3;
=============== SQL Execution Log ============
| (SELECT min (amt) |
| FROM Orders c |
| WHERE c.odate = b.odate) |
| ORDER BY 3; |
| ============================================= |
| |
| ----- ------- ------ ---------- ----------- |
| 1007 Rifkin 3001 Lowest on 10/03/1990 |
| 1002 Serres 3005 Highest on 10/03/1990 |
| 1002 Serres 3007 Lowest on 10/04/1990 |
| 1001 Peel 3008 Highest on 10/05/1990 |
| 1001 Peel 3008 Lowest on 10/05/1990 |
| 1003 Axelrod 3009 Highest on 10/04/1990 |
| 1002 Serres 3010 Lowest on 10/06/1990 |
| 1001
===============================================
14.5. ORDER BY.
ORDER BY , . , ASC DESC , . , 3 ORDER BY , SELECT . , , , , . ( 7, ).
, , , . , , .
., . , , , . , , , (NULL) snum. , sname .
, , , . , . , . , , , , , , , . , 14.6, :
SELECT Salespeople.snum, sname, cname, comm
FROM Salespeople, Customers
WHERE Salespeople.city = Customers.city
UNION
SELECT snum, sname, 'NO MATCH ', comm
FROM Salespeople
WHERE NOT city = ANY (SELECT city
FROM Customers)
ORDER BY 2 DESC;
=============== SQL Execution Log ============
| FROM Salespeople |
| WHERE NOT city = ANY (SELECT city |
| FROM Customers) |
| ORDER BY 2 DESC; |
| ============================================= |
| |
| ----- ------- --------- ------------ |
| 1002 Serres Cisneros 0.1300 |
| 1002 Serres Liu 0.1300 |
| 1007 Rifkin NO MATCH 0.1500 |
| 1001 Peel Clemens 0.1200 |
| 1001 Peel Hoffman 0.1200 |
| 1004 Motika Clemens 0.1100 |
| 1004 Motika Hoffman 0.1100 |
| 1003 Axelrod NO MATCH 0.1000 |
===============================================
'NO MATCH' , cname ( SQL). , . , . , , . UNION , . , , , :
SELECT a.snum, sname, a.city, 'MATCHED '
FROM Salespeople a, Customers b
WHERE a.city = b.city
UNION
SELECT snum, sname, city, 'NO MATCH'
FROM Salespeople
WHERE NOT city = ANY (SELECT city
FROM Customers)
ORDER BY 2 DESC;
14.7 .
=============== SQL Execution Log ============
| WHERE a.city = b.city |
| union |
| SELECT snum,sname,city, 'NO MATCH' |
| FROM Salespeople |
| WHERE NOT city = (SELECT city |
| FROM Customers) |
| ORDER BY 2 DESC; |
| ============================================= |
| |
| ----- ------- ------------ --------- |
| 1002 Serres San Jose MATCHED |
| 1007 Rifkin Barselona NO MATCH |
| 1001 Peel London MATCHED |
| 1004 Motika London
| 1003 Axelrod New York NO MATCH |
===============================================
14.7. .
, . , . , ( 14.8):
SELECT snum, city, 'SALESPERSON MATCH '
FROM Salespeople
WHERE NOT city = ANY (SELECT city
FROM Customers)
UNION
SELECT snum, city, 'SALESPERSON NO MATCH'
FROM Salespeople
WHERE NOT city = ANY (SELECT city
FROM Customers)
UNION
SELECT cnum, city, 'CUSTOMER MATCHED '
FROM Customers
WHERE city = ANY (SELECT city
FROM Salespeople)
UNION
SELECT cnum, city, 'CUSTOMER NO MATCH '
FROM Customers
WHERE NOT city = ANY (SELECT city
FROM Salespeople)
ORDER BY 2 DESC;
================= SQL Execution Log =============
| FROM Salespeople) |
| ORDER BY 2 DESC; |
| ================================================ |
| |
| ---- -------- ------------------------ |
| 2003 San Jose CUSTOMER MATCHED |
| 2008 San Jose CUSTOMER MATCHED |
| 2002 Rome CUSTOMER NO MATCH |
| 2007 Rome CUSTOMER NO MATCH |
| 1003 New York SALESPERSON MATCHED |
| 1003 New York SALESPERSON NO MATCH |
| 2001 London CUSTOMER MATCHED |
| 2006 London CUSTOMER MATCHED |
| 2004 Berlin CUSTOMER NO MATCH |
| 1007 Barcelona SALESPERSON MATCHED |
| 1007 Barcelona SALESPERSON NO MATCH |
=======================================
14.8. .
, , ANY, .
, , , . , , . , , , . ,
query X UNION query Y UNION query Z;
,
(query X UNION query Y)UNION query Z;
query X UNION (query Y UNION query Z);
, UNION UNION ALL , , .
(query X UNION ALL query Y)UNION query Z;
query X UNION ALL(query Y UNION query Z);
.
, UNION, . (, ), , , . , , , , , .
. SQL. , . , , , .
(. A .)
15
, , . , , , , .
, . , , .
.
, DML ( ):
INSERT (),
UPDATE (),
DELETE ().
, SQL,
.SQL INSERT. , INSERT :
INSERT INTO <table name>
VALUES (<value>, <value> . . .);
, , , :
INSERT INTO Salespeople
VALUES (1001, 'Peel', 'London', .12);
DML , , .
( Salespeople ), CREATE TABLE (. 17), , , . ANSI, , , 3 , 2 + 1 . , , , , 1, 2, .
(NULL), . , city Peel. =NULL , :
INSERT INTO Salespeople
VALUES (1001, 'Peel', NULL, .12);
NULL , , .
, . . , , , :
city, cname, cnum, , :INSERT INTO Customers (city, cnam, cnum)
VALUES ('London', 'Honman', 2001);
, rating snum . , . NULL , . NULL , , INSERT, ( 18 NULL " "
).INSERT, , . , VALUES ( ) :
INSERT INTO Londonstaff
SELECT *
FROM Salespeople
WHERE city = 'London';
, , city = "London" Londonstaff. , Londonstaff :
* CREATE TABLE.
* , ; , , , ( ).
, , , , .
Londonstaff , (Salespeople). , Londonstaff ( (VIEW), 20).
INSERT , , , , , .
, , Daytotals, . , Daytotals .
, , , , INSERT
INSERT INTO Daytotals (date, total)
SELECT odate, SUM (amt)
FROM Orders
GROUP BY odate;
, , Daytotals . , , , - .
DELETE. , , . , :
DELETE FROM Salespeople;
, , DROP TABLE ( 17).
, . , , . , Axelrod ,
DELETE FROM Salespeople
WHERE snum = 1003;
snum sname , , . .
, DELETE , , :
DELETE FROM Salespeople
WHERE city = 'London';
, , , .
UPDATE.UPDATE, , SET, , . , 200,
UPDATE Customers
SET rating = 200;
, , UPDATE, DELETE, . , , , Peel ( snum=1001):
UPDATE Customers
SET rating = 200
WHERE snum = 1001;
UPDATE. SET , . , . , Motika , :
UPDATE Salespeople
SET sname = 'Gibson', city = 'Boston', comm = .10
WHERE snum = 1004;
Gibson, Motika , , Motika snum.
, , , , SET. , "SET Salespeople.sname = Gibson" UPDATE, "SET sname = Gibson".
SET UPDATE, , , . VALUES INSERT, ; . , . :
UPDATE Salespeople
SET comm = comm * 2;
, SET, , - UPDATE. , , , , :
UPDATE Salespeople
SET comm = comm * 2
WHERE city = 'London';
SET . NULL , , - (, , IS NULL). , NULL, :
UPDATE customers
SET rating = NULL
WHERE city = 'London';
.
:
INSERT ;
DELETE ;
UPDATE DELETE, , . , INSERT, INSERT. , INSERT, . .
, , . , NULL. , , UPDATE , INSERT .
, , . , , , DML, 16.
city San Jose,
name Bianco,
comm NULL,
cnum 1100.
(. A .)
16
, , .
, . , SELECT, , . SELECT , , , UPDATE DELETE. , INSERT, , .
, , , FROM , . . , , , , , .
, , .
INSERT . , . , INSERT , .. HAVING.
, SJpeople, . , , , , San Jose:
INSERT INTO SJpeople
SELECT *
FROM Salespeople
WHERE city = 'San Jose';
, SJpeople , San Jose, , :
INSERT INTO Sjpeople
SELECT *
FROM Salespeople
WHERE snum = ANY (SELECT snum
FROM Customers
WHERE city = 'San Jose');
, INSERT. San Jose snum. Salespeople, snum . , Rifkin Serres, San Jose Liu Cisneros, SJpeople.
. Serres San Jose, , , . , San Jose. SJpeople, , ( ). (
c. 18 )., - , , , ( EXISTS, IN, <> ALL, ) .
, , SJpeople
FROM , , , , () .INSERT, , , . , , INSERT, . " " , INSERT .
, INSERT, , , FROM SELECT. , , INSERT, ;
, , . , Samecity, ., :
INSERT INTO (Samecity
SELECT *
FROM (Salespeople outer
WHERE city IN (SELECT city
FROM Customers inner
WHERE inner.snum = outer.snum);
Samecity, INSERT.
, , , . Bonus, snum , odate amt. , , :
INSERT INTO Bonus
SELECT snum, odate, amt
FROM Orders a
WHERE amt = (SELECT MAX (amt)
FROM Orders b
WHERE a.odate = b.odate);
, , , Bonus, . .
, , , . , , , Bonus.
DELETE. , , , , , , .
, , , , :
DELETE
FROM Customers
WHERE snum = ANY (SELECT snum
FROM Salespeople
WHERE city = 'London');
Hoffman Clemens ( Peel), Periera ( Motika).
, , , Peel Motika.
. , , , , , . , , , .
, , city , , .
, , . SQL , ( 19), .
, , FROM , - , , . , . , INSERT, - , , .
DELETE FROM Salespeople
WHERE EXISTS (SELECT *
FROM Customers
WHERE rating = 100 AND
Salespeople.snum = Customers.snum);
, AND . , , . , , 100 .
, :
DELETE FROM Salespeople
WHERE 100 IN (SELECT rating
FROM Customers
WHERE Salespeople.snum = Customers.snum);
,
rating = 100., , ( DELETE) . , , :
DELETE FROM Salespeople
WHERE (snum IN (SELECT snum
FROM Orders a
WHERE amt = (SELECT MIN (amt)
FROM Orders b
WHERE a.odate = b.odate));
DELETE . . , , , , . snum , , DELETE, snum . snum , , snum . , .
snum = 1007, , 3 ; snum = 1002, 4 ; snum = 1001, 5 ( , Peel, 5 , ).
Peel, , :
DELETE FROM Salespeople
WHERE snum IN (SELECT snum
FROM Orders a
WHERE amt = (SELECT MIN (amt)
FROM Orders b
WHERE a.odate = b.odate)
AND 1 < (SELECT COUNT onum
FROM Orders b
WHERE a.odate = b.odate));
, , COUNT = 1 . , snum .
UPDATE , DELETE . DELETE , . , , , :
UPDATE Salespeople
SET comm = comm + .01
WHERE 2 <= (SELECT COUNT (cnum)
FROM Customers
WHERE Customers.snum = Salespeople.snum);
Peel Serres, , .
DELETE. , , :
UPDATE salespeople
SET comm = comm - .01
WHERE snum IN (SELECT snum
FROM Orders a
WHERE amt = (SELECT MIN (amt)
FROM Orders b
WHERE a.odate = b.odate));
, (UPDATE), .
, , . , ( 1.), , , ( 2.), :
1.
SELECT AVG (rating)
FROM Customers;
= 200.
2.
DELETE
FROM Customers
WHERE rating < 200;
, . , , , , , .
: INSERT, . VALUES ( ), ( ). ,
, , , FROM, ( ). ., , , FROM , FROM ( ).
DELETE UPDATE , , , . , , , . , , , . , , , FROM.
, SQL, . , . , . , , .
, , , ( ), ( ), .
(. A .)
17
, , - . , , . , , , .
, . , , . , SQL , . SQL DDL ( ), SQL.
SQL . , , , .
, , - , , , , .
18.
CREATE TABLE. . DML INSERT (. 15). CREATE TABLE , . . , , .
CREATE TABLE:
CREATE TABLE <table-name >
(<column name > <data type>[(<size>)],
<column name > <data type> [(<size>)] ...);
2, . , , , ANSI. B.
SQL, ( , ). (_)
.. , . , , (. 14).
, . , , , , , .
, , ,
CHAR. , , . , ( NULL) . , = 1, , . , , , .. (, Interbase) varchar , NULL, , , : (NULL) '' .
, , , , . , . city . , , ; , Employees, Smith, Smith.Employees - . , Smith (ID), ( ID SQL). 2, 22.
:
CREATE TABLE Saleepeople
(snum integer,
sname char (10),
city char (10),
comm declmal);
, . ( ), .
( ) . , , - , ., , . 18 , . SQL. , .
SQL, , ANSI. ANSI , .
, . , , cnum=2999. , , , cnum 2999. cnum, 2999 , .
, DML INSERT, UPDATE DELETE, , , . , , , , .
. , , , . , , . .
(, ANSI ):
CREATE INDEX <index name> ON <table name>
(<column name> [,<column name>]...);
, , . - ( ). , . SQL , , , .
, , , snum .
CREATE INDEX Clientgroup ON Customers (snum);
, , , .
, , , , . . , ,
UNIQUE INDEX. num, , :CREATE UNIQUE INDEX Custid ON Customers (cnum);
: , cnum. , , , . , , , , .cnum . . 18 19.
, . . SQL , , , . , , . :
DROP INDEX <Index name>;
.
ALTER TABLE ANSI; , , . , . , . , ( 18). , :ALTER TABLE <table name> ADD <column name> <data type> <size>;
NULL . . , , , . . , , () .
, , , . .
, , , . - ALTER TABLE, , .
ALTER TABLE , , . , ! , , ( 20), , , SQL ( 25), . , , .
, , ALTER TABLE .
ALTER TABLE, , , ,
INSERT SELECT * ., (. 22), .
(.. ) , . , SQL , , . , . 15 , . , , :
DROP TABLE <table name>;
, , , . , ( 19), ( 20).
ANSI,
SQL, . , , , , , ALTER TABLE. ANSI ..
SQL- . .. , , . SQL, , ALTER TABLE. DROP TABLE , . ,
, , ., . SQL , , , , . SQL. , , . 18 19.
(. A .)
18
17 , . , .
, , .
, , , , ( CREATE TABLE ALTER TABLE). , . , .
, , INSERT . NULL , , .
(, ), , . , SQL , , . . , , .
. , . CREATE TABLE, :
CREATE TABLE <table name>
(<column name> <data type> <column constraint>,
<column name> <data type> <column constraint> ...
<table constraint> (<column name> [, <column name> ])...);
, . , . , , , , . .
CREATE TABLE,
(NULL) NOT NULL. .,
NULL , . NULL , . , , . , , , . , , , . NOT NULL ( ) , NULL . SQL , NULL ., , NULL snum sname:
CREATE TABLE Salespeople (
Snum integer NOT NULL,
Sname char (10) NOT NULL,
city char (10),
comm decimal);
, NOT NULL INSERT, . NULL, SQL , , , , , .
ALTER TABLE, , , , , NOT NULL, . NOT NULL, .
17, , . , SQL UNIQUE. , , , ().
, . , UNIQUE ; , . , , .
, , , , . , .
UNIQUE , , , . , (NOT NULL), NULL, NULL .
:
CREATE TABLE Salespeople (
Snum integer NOT NULL UNIQUE,
Sname char (10) NOT NULL UNIQUE,
city char (10),
comm decimal);
sname , , Mary Smith , , Mary Smith M. Smith. snum , , Smith .
( ) , -
.UNIQUE. , , , .
, "a", "b" "b", "a" .
, . , (cnum) (snum) . , :
CREATE TABLE Customers (
cnum integer NOT NULL,
cname char (10) NOT NULL,
city char (10),
rating integer,
snum integer NOT NULL,
UNIQUE (cnum, snum));
, UNIQUE NOT NULL. UNIQUE cnum, .
cnum , cnum snum. , snum , , . , UNIQUE, , .
, , , . , . , , , , snum odate . , ,
Salestotal:CREATE TABLE Salestotal (
cnum integer NOT NULL,
odate date NULL,
totamt decimal,
UNIQUE (snum, odate));
, , , :
INSERT INTO Salestotal
SELECT snum, odate, SUM (amt)
FROM Orders
GROUP BY snum, odate;
. , , , "" SQL. UNIQUE , . SQL . SQL
(PRIMARY KEY). .PRIMARY KEY
. , UNIQUE, , ( ) . , 19. UNIQUE.NULL. , UNIQUE, , PRIMARY KEY, NOT NULL.
:
CREATE TABLE Salestotal (
snum integer NOT NULL PRIMARY KEY,
sname char(10) NOT NULL UNIQUE,
city char(10),
comm decimal);
, (UNIQUE) . PRIMARY KEY ( ) , UNIQUE ( sname), .
PRIMARY KEY , . , , ( ). , , , .
PRIMARY KEY :
CREATE TABLE Namefield (
firstname char (10) NOT NULL,
lastname char (10) NOT NULL
city char (10),
PRIMARY KEY (firstname, lastname));
, , , Mary Smith M. Smith. , . , , , UNIQUE .
CHECK), , , , , , , SQL . , SQL
CHECK, , , , . CHECK CHECK , . , .. , ( ) (
$).- , , .
14 .14 , 14.0, , . , CHECK, , 1.CREATE TABLE Salespeople (
snum integer NOT NULL PRIMARY KEY,
sname char(10) NOT NULL UNIQUE,
city char(10),
comm decimal CHECK (comm < 1));
CHECK, , . , , , , , , -, -. , , . , .
CREATE TABLE Salespeople (
snum integer NOT NULL UNIQUE,
sname char(10) NOT NULL UNIQUE,
city char(10)
CHECK (city IN ('London', 'New York', 'San Jose', 'Barselona')),
comm decimal CHECK (comm<1));
, ,
, . ALTER TABLE (. 17) , . , , , ., ,
(CREATE) , . , , .:
CREATE TABLE Orders (
onum integer NOT NULL UNIQUE,
amt decimal,
odate date NOT NULL,
cnum integer NOT NULL,
snum integer NOT NULL);
2, DAT () , ANSI. , , , ANSI, DATE? odate , (/) (-) . ASCII, odate
CHAR. , , odate . , DATE . , odate CHAR. , , CHECK:CREATE TABLE Orders (
onum integer NOT NULL UNIQUE,
amt decimal,
odate char (10) NOT NULL CHECK (odate LIKE '--/--/----'),
cnum NOT NULL,
snum NOT NULL);
, , , , .
CHECK . , . , .15 . CHECK:
CREATE TABLE Salespeople (
snum integer NOT NULL UNIQUE,
sname char (10) NOT NULL UNIQUE,
city char(10),
comm decimal,
CHECK (comm < .15 OR city = 'Barcelona'));
, , , . , . , SQL . , , CHECK, , . , SQL , , . SQL .
, CHECK , , . , :
CHECK ((comm = .15 AND clty = 'London')
OR (comm = .14 AND city = 'Barcelona')
OR (comm = .11 AND city = 'San Jose')..)
. , ,
(VIEW) WITH CHECK OPTION, ( 20 21 WITH CHECK OPTION). . , . WITH CHECK OPTION CHECK, 21., SQL , .
NULL. , NOT NULL .DEFAULT ( ) CREATE TABLE , , , , DEFAULT , , , , .
, -, -. - city, , :
CREATE TABLE Salespeople (
snum integer NOT NULL UNIQUE,
sname char(10) NOT NULL UNIQUE,
city char(10) DEFAULT = 'New York',
comm decimal CHECK (comm < 1));
, - , , , ( )
. , , , , , , ., ( ) , .
NULL. NULL () , , IS NULL, . , - . , , , (NULL). NULL , SQL , .
, . , , , , . , = NULL:
SELECT *
FROM Customers
WHERE rating <= 100;
rating = 0, . .
, , ?
,
NOT NULL., = NULL, .
UNIQUE PRIMARY KEY . , , , . , , , . , UNIQUE PRIMARY KEY ( ) .
, . NOT NULL, NULL, UNIQUE, , PRIMARY KEY, , UNIQUE, , , , CHECK , , , , . , DEFAULT, , INSERT, NULL, DEFAULT NOT NULL.
FOREIGN KEY REFERENCES, 19, , , , , .(. A .)
19
, . snum , , snum . cnum cnum . ; , , .
, , . , , DML. , , , . , , . , , , , , , , , , 8.
, . . , snum, , . , , , , . snum cnum .
, , , ,
. snum , snum, ., cnum snum , . , , , .
, . , , . , , , , . , , .
, . . , , , , .
, , . , , " ( ) ( )". ( ) () . , , ,
.. snum 1001 Hoffman Clemens.
, snum = 1001.
, Hoffman Clemens? , , Hoffman Clemens , !
, , , .
, , : .. . . Hoffman, Clemens Peel, , . , .
, , , 19.1. , .
OREIGN KEYSQL
FOREIGN KEY. FOREIGN KEY SQL, . , , . , , ., , . ( ).
FOREIGN KEY CREATE TABLE ( ALTER TABLE), , . , FOREIGN KEY. , , .
19.1. .
, , .
FOREIGN KEY:
FOREIGN KEY <column list> REFERENCES <pktable> [ <column list> ]
, .
Pktable , . , . , . , ..:- .
- , , , .. , , , , .. . , , .
snum, :
CREATE TABLE Customers (
cnum integer NOT NULL PRIMARY KEY
cname char(10),
city char(10),
snum integer,
FOREIGN KEY (snum) REFERENCES Salespeople (snum));
, ALTER TABLE CREATE TABLE, FOREIGN KEY, , . . ALTER TABLE - , , , , .
FOREIGN KEY (REFERENCES), FOREIGN KEY, REFERENCES, , :
CREATE TABLE Customers (
cnum integer NOT NULL PRIMARY KEY,
cname char(10),
city char(10),
snum integer REFERENCES Salespeople (snum));
Customers.snum , Salespeople.snum. :
FOREIGN KEY (snum) REGERENCES Salespeople (snum)
FOREIGN KEY , , PRIMARY KEY. , , , , , . , PRIMARY KEY snum , ( ) :
CREATE TABLE Customers (
cnum integer NOT NULL PRIMARY KEY,
cname char(10),
city char(10),
snum integer REFERENCES Salespeople);
, .
, , . , , . , () (NULL). , . SQL , (NULL) . , , , , PRIMARY KEY UNIQUE, NOT NULL.
, , . , , ; , . , . , , snum , , snum, , , , , , . ; , , .
snum , , , , - , .
, , . , , . , , , , , .
, , , (NULL). .
NOT NULL, , . , , , , . , NOT NULL, .
, , , :
CREATE TABLE Salespeople (
snum integer NOT NULL PRIMARY KEY,
sname char(10) NOT NULL,
city char(10),
comm decimal);
CREATE TABLE Customers (
cnum integer NOT NULL PRIMARY KEY,
cname char(10) NOT NULL,
city char(10),
rating integer,
snum integer,
FOREIGN KEY (snum) REFERENCES Salespeople,
UNIQUE (cnum, snum));
CREATE TABLE Orders (
cnum integer NOT NULL PRIMARY KEY,
amt decimal,
odate date NOT NULL,
cnum integer NOT NULL
snum integer NOT NULL
FOREIGN KEY (cnum, snum) REFERENCES
CUSTOMERS (cnum, snum));
, . , cnum snum , , , , , , . , UNIQUE , . cnum PRIMARY KEY, , , , cnum - .
, , ,
, ., ( ) , . , snum cnum , .
, snum , , , . cnum, , , snum ( ). , , , .
, , ( ), . , (denormalization), , . , , .
DML? , , : , INSERT UPDATE . (NULL) , , NULL , NOT NULL. (
DELETE) , ., , ANSI, , : , , . , , , . , , , . , , , . , , , . , , (.. ), () ( 22).
, ANSI, . , :
- ( ANSI), ,
, . INSERT, , . , . , , . , , UPDATE DELETE. (update effects) (delete effects), , , UPDATE DELETE . , , :
(RESTRICTED) , (CASCADES) , (NULL) .ANSI ,
, . , . , , , . , , ., , snum , ( , ; , : ). , . , , , . , , - .
UPDATE , DELETE .CREATE TABLE Customers (
cnum integer NOT NULL PRIMARY KEY,
cname char(10) NOT NULL,
city char(10),
rating integer,
snum integer REFERENCES Salespeople,
UPDATE OF Salespeople CASCADES,
DELETE OF Salespeople RESTRICTED);
Peel , , snum Hoffman Clemens . , snum Peel 1009, Hoffman Clemens .
(NULL) . , , . , , . , . , .
CREATE TABLE Orders (
onum integer NOT NULL PRIMARY KEY,
amt decimal,
odate date NOT NULL
cnum integer NOT NULL REFERENCES Customers
snum integer REFERENCES Salespeople,
UPDATE OF Customers CASCADES,
DELETE OF Customers CASCADES,
UPDATE OF Salespeople CASCADES,
DELETE OF Salespeople NULLS);
,
DELETE , NOT NULL snum., FOREIGN KEY , . , . , Employees manager (). , .
, , , . , ( empno), , , , :
CREATE TABLE Employees (
empno integer NOT NULL PRIMARY KEY,
name char(10) NOT NULL UNIOUE,
manager integer REFERENCES Employees);
, . :
|
EMPNO |
NAME |
MANAGER |
|
1003 |
Terrence |
2007 |
|
2007 |
Atali |
NULL |
|
1688 |
McKenna |
1003 |
|
2002 |
Collier |
2007 |
, ( Atali), . Atali, , NULL. . , , NULL. , ?
, , . , , , . , , , , CREATE TABLE:
CREATE TABLE Salespeople (
snum integer NOT NULL PRIMARY KEY,
sname char(10) NOT NULL,
city char(10),
comm declmal,
cnum integer REFERENCES Customers);
CREATE TABLE Customers (
cnum integer NOT NULL PRIMARY KEY,
cname char(10) NOT NULL,
city char(10),
rating integer,
snum integer REFERENCES Salespeople);
.
SQL , . , , , . , SQL , , . , , . , . , , , , , . , , , , ( , , 22 23).
. , . , , , . , , , , ; . (
PRIMARY KEY) (UNIQUE) , , . , , . , , , NULL , UPDATE DELETE.CREATE TABLE. CREATE. 20 , , . , , , .
(. A .)
20
(VIEW) , . , . , , .
, , . , , , , .
, , . , . . , . DML , .
, ( , , ), . , , . .
CREATE VIEW. CREATE VIEW ( ), , , AS (), , :
CREATE VIEW Londonstaff
AS SELECT *
FROM Salespeople
WHERE city = 'London';
,
Londonstaff. , . , , , , , . ( 20.1):Select *
FROM Londonstaff;
=============== SQL Execution Log ============
| SELECT * |
| FROM Londonstaff; |
| ==============================================|
| snum sname city comm |
| ------ ---------- ----------- ------- |
| 1001 Peel London 0.1200 |
| 1004 Motika London 0.1100 |
=========
20.1. Londonstaff
SQL (
SELECT) (*) , , Londonstaff, ., , . , 15, ,
Londonstaff, (, , ; , ). , , , , ., , . , , .
. , . , , , ( 20.2)
CREATE VIEW Salesown
AS SELECT snum, sname, city
FROM Salespeople:
=============== SQL Execution Log ============
| SELECT * |
| FROM Salesown; |
| ==============================================|
| snum sname city |
| ------ ---------- ----------- |
| 1001 Peel London |
| 1002 Serres San Jose |
| 1004 Motika London |
| 1007 Rifkin Barcelona |
| 1003 Axelrod
===============================================
20.2. Salesown
, , , , comm , , , .
DML, . :
UPDATE Salesown
SET city = 'Palo Alto'
WHERE snum = 1004;
. , UPDATE
UPDATE Salesown
SET comm = .20
WHERE snum = 1004;
, comm Salesown. , , .
21.
, , . . , :
- ;
- , .
, , . , . , "" . , - , .
, , . SQL , .
Londonstaff:CREATE VIEW Londonstaff
AS SELECT *
FROM Salespeople
WHERE city = 'London';
SELECT *
FROM Londonstaff
WHERE comm > .12;
, :
SELECT *
FROM Salespeople
WHERE city = 'London' AND comm > .12;
, , . , . , , (
CREATE) :CREATE VIEW Ratingcount (rating, number)
AS SELECT rating, COUNT (*)
FROM Customers
GROUP BY rating;
, rating. , , - , :
SELECT *
FROM Ratingcount
WHERE number = 3;
, , :
SELECT rating, COUNT (*)
FROM Customers
WHERE COUNT (*) = 3
GROUP BY rating;
. , COUNT (), . , , :
SELECT rating, COUNT (*)
FROM Customers
GROUP BY rating;
HAVING COUNT (*) = 3;
SQL . Ratingcount ? , ! SQL, .
, , , . , , SQL .
, Ratingcount , GROUP BY, .
. , , , , , , .
, , :
CREATE VIEW Totalforday
AS SELECT odate, COUNT(DISTINCT cnum), COUNT(DISTINCT snum),
COUNT(onum), AVG(amt), SUM(amt)
FROM Orders
GROUP BY odate;
:
SELECT *
FROM Totalforday;
, SQL , , , .
, .
, . SQL , , .
, , , :
CREATE VIEW Nameorders
AS SELECT onum, amt, a.snum, sname, cname
FROM Orders a, Customers b, Salespeople c
WHERE a.cnum = b.cnum AND a.snum = c.snum;
, .
, Rifkin, ( 20.3 ):
SELECT *
FROM Nameorders
WHERE sname = 'Rifkin';
=============== SQL Execution Log ==============
| SELECT * |
| FROM Nameorders |
| WHERE sname = 'Rifkin'; |
| =============================================== |
| onum amt snum sname cname |
| ------ -------- ----- ------- ------- |
| 3001 18.69 1007 Rifkin Ci
| 3006 1098.16 1007 Rifkin Cisneros |
================================================
20.3. Rifkin Nameorders
, , Axelrod :
SELECT a.sname, cname, amt comm
FROM Nameorders a, Salespeople b
WHERE a.sname = 'Axelrod' AND b.snum = a.snum;
20.4.
, "WHERE a.sname = Axelrod' AND b.sname = Axelrod", , , . , snum , , .
=============== SQL Execution Log ==============
| SELECT a.sname, cname, amt * comm |
| FROM Nameorders a, Salespeople b |
| WHERE a.sname = 'Axelrod' AND b.snum = a.snum; |
| =============================================== |
| onum amt snum sname cname |
| ------ -------- ----- ------- ------- |
| 3001 18.69 1007 Rifkin Cisneros |
| 3006 1098.16 1007 Rifkin Cisneros |
===============================
20.4.
, , Axelrod, . snum, .
, . , , . :
CREATE VIEW Elitesalesforce
AS SELECT b.odate, a.snum, a.sname,
FROM Salespeople a, Orders b
WHERE a.snum = b.snum AND b.amt = (SELECT MAX (amt)
FROM Orders c
WHERE c.odate = b.odate);
, , , , , :
CREATE VIEW Bonus
AS SELECT DISTINCT snum, sname
FROM Elitesalesforce a
WHERE 10 <= (SELECT COUNT (*)
FROM Elitesalestorce b
WHERE a.snum = b.snum);
, , :
SELECT *
FROM Bonus;
SQL. RPG COBOL . SQL , .
, , , , .
( ), . , , . 21.
, . ;
(UNION) (UNION ALL) . ORDER BY . , .:
DROP VIEW <view name>
, , , , . , , , .
, , .
, , , . , , . , , .
, , . , 21. , , , , , .
(. A .)
21
DML
(INSERT), (UPDATE), (DELETE), . , . ., , , . ,
WITH CHECK OPTION, , .18, , , .
DML. , .
. , , . ; , , . . , 21.1:
CREATE VIEW Citymatch (custcity, salescity)
AS SELECT DISTINCT a.city, b.city
FROM Customers a, Salespeople b
WHERE a.snum = b.snum;
, , , ,
., London London , , , , . Hoffman Peel, .
=============== SQL Execution Log ==============
| SELECT * |
| FROM Citymatch; |
| =============================================== |
| custcity salescity |
| --------- --------- |
| Berlin San Jose |
| London London |
| Rome London |
| Rome New York |
| San Jose Barselona |
| San Jose San Jose |
========================
21.1.
, Clemens , , Peel. , .
, , , , - "Lndon London". , , ( , , ORDER BY, ). , , ORDER BY. , . , , . London London ? Hoffman , Clemens , ? Peel ? , . Citymatch " ", , .
, , ; . , " " (updating a view), DML (
INSERT, UPDATE DELETE), ., ? , . : , , , . , , . , , , SQL. , , , SQL, :
- .
- ( ANSI, ).
- , .
- DISTINCT .
- GROUP BY HAVING .
- ( ANSI , ).
- , .
- , , (, comm*100) .
- INSERT, , NOT NULL, .
" "
, , , . -, , . ( ), ( ), .
( , DISTINCT).
" " .
, , . , , , . , , , , . , . ( 22, , , ).
" ", , . , , . , , (, ) .
" " . , , , , .
" ":
CREATE VIEW Dateorders (odate, ocount)
AS SELECT odate, COUNT (*)
FROM Orders
GROUP BY odate;
" " - GROUP BY.
CREATE VIEW Londoncust
AS SELECT *
FROM Customers
WHERE city = 'London';
.
CREATE VIEW SJsales (name, number, percentage)
AS SELECT sname, snum, comm 100
FROM Salespeople
WHERE city = 'SanJose';
" " - "
comm * 100". , , . snum sname.CREATE VIEW Salesonthird
AS SELECT *
FROM Salespeople
WHERE snum IN (SELECT snum
FROM Orders
WHERE odate = 10/03/1990);
" " ANSI - . , .
CREATE VIEW Someorders
AS SELECT snum, onum, cnum
FROM Orders
WHERE odate IN (10/03/1990,10/05/1990);
.
, "" (swallowed) . :
CREATE VIEW Highratings
AS SELECT cnum, rating
FROM Customers
WHERE rating = 300;
. . ,
(INSERT) :INSERT INTO Highratings
VALUES (2018, 200);
INSERT . , Highratings, . , , 300. .
200 , , . , , , .
WITH CHECK OPTION ( ) . WITH CHECK OPTION Highratmgs.
CREATE VIEW Highratings
AS SELECT cnum, rating
FROM Customers
WHERE rating = 300
WITH CHECK OPTION;
.
WITH CHECK OPTION __ (all-or-nothing). , DML, , . , , .
, , , .
, , , . ,
Londonstaff :CREATE VIEW Londonstaff
AS SELECT snum, sname, comm
FROM Salespeople
WHERE city = 'London';
, city, city .
, , ? city , , , NULL, city (NULL , ; . 18 ). city London, . ,
Londonstaff. Londonstaff , ( London, ). , , . WITH CHECK OPTIONCREATE VIEW Londonstate
AS SELECT snum, sname, comm
FROM Salespeople
WHERE city = 'London'
WITH CHECK OPTION;
. , , . , ; , , , , . , , , .
, , . , , . ,
Londonstaff :CREATE VIEW Londonstaff
AS SELECT *
FROM Salespeople
WHERE city = 'London'
WITH CHECK OPTION;
city, , , :
SELECT snum, sname, comm
FROM Londonstaff;
WITH CHECK OPTION ANSI: : , , . ,
CREATE VIEW Highratings
AS SELECT cnum, rating
FROM Customers
WHERE rating = 300
WITH CHECK OPTION;
300 . , ( ) :
CREATE VIEW Myratings
AS SELECT *
FROM Highratings;
, 300:
UPDATE Myratings
SET rating = 200
WHERE cnum = 2004;
, , , . WITH CHECK OPTION , , . , , , WITH CHECK OPTION . , , . , ,
MyratingsCREATE VIEW Myratings
AS SELECT *
FROM Highratings
WITH CHECK OPTION;
. WITH CHECK OPTION Myratings. Myratings, , , WITH CHECK OPTION . , , Myratings , Highratings .
ANSI, . . , .
. , , , , , , , SQL , .
, , , . , . , " ".
, , , .
" ", , SQL; , , , . , , , .
, WITH CHECK OPTION . WITH CHECK OPTION . , , , . , , , . .
20 21 , . , , . 22 SQL.
#1 CREATE VIEW Dailyorders
AS SELECT DISTINCT cnum, snum, onum, odate
FROM Orders;
#2 CREATE VIEW Custotals
AS SELECT cname, SUM (amt)
FROM Orders, Customers
WHERE Orders.cnum = customer.cnum
GROUP BY cname;
#3 CREATE VIEW Thirdorders
AS SELECT *
FROM Dailyorders
WHERE odate = 10/03/1990;
#4 CREATE VIEW Nullcities
AS SELECT snum, sname, city
FROM Salespeople
WHERE city IS NULL OR sname BETWEEN 'A' AND 'MZ';
(. A .)
22
. 2, SQL , . , . , , ,
. , , . , . SQL: GRANT () REVOKE ()., .
SQL . , ( ANSI)
(ID) . , , , , , . SQL , ID , SQL USER, . ( ) ., , . , ID . , ID . , , ID , ID .
SQL, ; ID .
SQL , , ( , ), ID . , SQL ID , ,
USER.SQL . , . , . ANSI SQL, , .
SQL , ANSI, . , , , , , ANSI . , SQL, .
SQL , ANSI . , , . , , , , SQL, .
, . , , . , , ( ), . , .
,
:SELECT
INSERT
INSERT .UPDATE
UPDATE . .DELETE
DELETE .REFERENCES
, , . . ( 19 .), , ,
INDEX, , SYNONYM, , 23, ALTER, ALTER TABLE . SQL GRANT., Diane Adrian . Diane , :
GRANT SELECT ON Customers TO Adrian;
Adrian . , ; , ( , , ).
SQL GRANT, , , , GRANT.
Adrian . SELECT : Diane ( , Diane Adrian SELECT ).
, . Adrian , Diane
GRANT INSERT ON Salespeople TO Diane;
Diane .
GRANT. , , . Stephen SELECT INSERT Adrian
GRANT SELECT, INSERT ON Orders TO Adrian;
Adrian Diane
GRANT SELECT, INSERT ON Orders TO Adrian, Diane;
, . ANSI , , , , , .
, UPDATE RE
FERENCES, . UPDATE :GRANT UPDATE ON Salespeople TO Diane;
Diane . , Adrian Diane ,
GRANT UPDATE (comm) ON Salespeople TO Diane;
, , UPDATE , . , :
GRANT UPDATE (city, comm) ON Salespeople TO Diane;
REFERENCES . REFERENCES , . UPDATE, REFERENCES , . , Diane Stephen , , :
GRANT REFERENCES (cname, cnum) ON Customers TO Stephen;
Stephen cnum cname, . Stephen , . (cname, cnum) , (cnum, cname), , . , , , Diane (. 19).
, , (overlap).
UPDATE, . Adrian Diane :
GRANT REFERENCES ON Salespeople TO Diane;
, , .
SQL GRANT, :
ALL PRIVILEGES ( ) ALL PUBLIC (). ALL GRANT, . , Diane Stephen :GRANT ALL ON Customers TO Stephen;
( UPDATE REFERENCES .)
PUBLIC
(catch-all), .PUBLIC, . , SELECT , . , , , :
GRANT SELECT ON Orders TO PUBLIC;
, , .
, SELECT, (, REFERENCES, ) . ., , , , .
PUBLIC . , , , , , , , , SELECT .
, . , ( ) , , . SQL
WITH GRANT OPTION.Diane , Adrian SELECT , SELECT WITH GRANT OPTION:
GRANT SELECT ON Customers TO Adrian
WITH GRANT OPTION;
Adrian SELECT .
GRANT SELECT ON Diane.Customers TO Stephen;
GRANT SELECT ON Diane.Customers TO Stephen
WITH GRANT OPTION;
GRANT OPTION , , , , GRANT OPTION, . ; ( , , ID , ; ). GRANT OPTION .
ANSI CREATE TABLE, , DROP TABLE , GRANT , , . REVOKE, .
REVOKE GRANT, . INSERT Adrian ,
REVOKE INSERT ON Orders FROM Adrian;
, GRANT, :
REVOKE INSERT, DELETE ON Customers
FROM Adrian, Stephen;
, . ? ? , , ? , , :
- , , , , .
, . , , , UPDATE REFERENCES, .
, , , , , , . GRANT.
, SELECT , . , INSERT, UPDATE DELETE, , . , , , . , REFERENCES . ANSI. ( ) . , , , .
, Claire snum sname . ,
CREATE VIEW Clairesview
AS SELECT snum, sname
FROM Salespeople;
Claire SELECT , :
GRANT SELECT On Clairesview to Claire;
, INSERT , DELETE, . REFERENCES UPDATE
, , , ., , . , ,
, , . Adrian UPDATE , , :CREATE VIEW Londoncust
AS SELECT *
FROM Customers
WHERE city = 'London'
WITH CHECK OPTION;
UPDATE Adrian:
GRANT UPDATE ON Londoncust TO Adrian;
UPDATE , , , city , London, .
WITH CHECK OPTION Adrian city London., , . . , , , :
CREATE VIEW Datetotals
AS SELECT odate, COUNT (*), SUM (amt), AVG (amt)
FROM Orders
GROUP BY odate;
Diane SELECT Datetotals:
GRANT SELECT ON Datetotals TO Diane;
, 18, WITH CHECK OPTION . , , city , . CHECK city, , , , . , , city:
CREATE VIEW Curcities
AS SELECT *
FROM Salespeople
WHERE city IN ('London', 'Rome', 'San Jose', 'Berlin')
WITH CHECK OPTION;
, , , Curcities. , , , , , , . , , ,
., , , . , , . , . , , :
CREATE VIEW Othercities
AS SELECT *
FROM Salespeople
WHERE city NOT IN ('London', 'Rome', 'San Jose', 'Berlin')
WITH CHECK OPTION;
SELECT , , city . , .
, , , . ANSI, . ANSI ; , . , . :
- , , ?
- ?
- , , , , , ?
ANSI , SQL , . .
, , , . , , , , ( ) ( ). , , ANSI ( ).
, , , - . ,
DBA (DataBase Administrator), , , .:
- CONNECT (
),- RESOURCE (),
- DBA ( ).
, ,
CONNECT (. 23), . RESOURCE . DBA , . . , , , SYSADM SYS ( ), ; , DBA . SYSADM, . - . , , , DBA, , . GRANT , . DBA. , DBA Rodriguez :GRANT RESOURCE TO Rodriguez;
, , Rodriguez? ID ? , DBA , CONNECT.
,
IDENTIFIED BY, . , , ID . DBA , ,GRANT CONNECT TO Thelonius IDENTIFIED BY Redwagon;
Thelonius, , Redwagon, .
Thelonious , DBA Redwagon. , . , , . , REVOKE CONNECT, "" . , .
CONNECT , . , DBA . ( ), RESOURCE. .
CONNECT , , , , . , , CONNECT. , , , INSERT, . RESOURSE; CONNECT, .
, . , .
SQL, , SQL. , , , DBA . , .
SQL , SQL . GRANT : . WITH GRANT OPTION , .
, , , . , , SQL, .
23 SQL, , , , , , .
(. A .)
23
SQL, , , , , . , , .
, , , , SQL , . , SQL ( ANSI).
. , , , ( ) , , , . ( .)
Adrian Clients, Diane.Customers, CREATE SYNONYM :
CREATE SYNONYM clients FOR Diane.Customers;
, Adrian Clients , Diane.Customers. Clients , Adrian.
() . , , SQL . , , , - ( SQL). , , , , , . , , CHECK OPTION CHECK ( 22 ). , , . , Adrian Customers, Diane.Customers:CREATE SYNONYM Customers FOR Diane.Customers;
SQL, :
Diane.Customers Adrian.Customers. , Customers, SQL ., , , . , , . , . , Customers,
CREATE PUBLIC SYNONYM Customers FOR Customers;
, , . , , DBA. , , , . , . PUBLIC, .
DROP SYNONYM. , , , DBA. , , Clients, Customers, Adrian
DROP SYNONYM Clients;
, , .
, . , , " ", , , SQL, . , , , , , , , .
, (, , ) . , SQL , , .
SQL , ,
., , ; - , . , , SQL.
, (
DBS Data Base Spaces), SQL .DBS
CREATE DBSPACE ( DBS), ACQUIRE DBSPACE ( DBS) CREATE TABLESPACE ( ), . DBS , DBS. , , DBS. DBS Sampletables, :CREATE DBSPACE Sampletables
(pctindex 10,
pctfree 25);
pctindex , DBS , . Pctfree DBS, , (ALTER TABLE
, ; , ). , , . , DBS, . DBS , .DBS , . , , Diane Sampletables :
GRANT RESOURCE ON Sampletables TO Diane;
. DBS, , .
, DBS, , .
DBS, , . , , , , , , DBS.
, , , . , DBS, , . , DBS.
, , , , . , , , , .
SQL, DML DROP TABLE, . , , , . , ,
., SQL. , ,
COMMIT WORK ROLLBACK WORK. COMMIT , ROLLBACK . COMMIT ROLLBACK. . , , COMMIT ROLLBACKCOMMIT WORK;
-
ROLLBACK WORK;
, ,
AUTOCOMMIT. , . , , "" . , , :SET AUTOCOMMIT ON;
:
SET AUTOCOMMIT OFF;
AUTOCOMMIT, .
, , . , , . ; , .
, , , , . , , , . , , Motika . , , - . ( , , ANSI, , , . .)
, snum NULL, , Peel. :
UPDATE Orders
SET snum = NULL
WHERE snum = 1004;
UPDATE Cudomers
SET snum = 1001
WHERE snum = 1004;
DELETE FROM Salespeople
WHERE snum = 1004;
Motika (, , , ), , , , .
, , . COMMIT, COMMIT ROLLBACK.
SQL , . . , :
UPDATE Salespeople
SET comm = comm * 2
WHERE sname LIKE 'R%';
, Diane :
SELECT city, AVG (comm)
FROM Salespeople
GROUP BY city;
(AVG) Diane , ? , , , , (comm), . , , . .
. , , Diane . Diane , , , , .
, . :
*
. , , , . , , .: "", "", , , "" , "".
*
, . , , Diane .*
. , Diane , . , , . , , Diane , , . , .*
. , . , ., , . , SQL . ANSI , ( COMMIT ROLLBACK).
, . , , . SQL , . , DBA, .
SQL, SQL.
, SQL , . , . ( NOWAIT, , , - ).
. , , . , , CREATE DBSPACE ALTER DBSPACE, - DBS.
, , , , . , .
, DB2 IBM. IBM (, , ), . , , .
:
. ( S-) . , .
( X-) , . , . .
. , .
? , , .
DB2, , , , , . , SQL, , . , , .
, , .
, , , . , , , , .
, , , , . , , , .
, , .
, , . , , (.. ).
, , , , , , . . , , , .
DB2 .
; . , . , , .
, . , , , , . , , , , .
. , .
, 1024 . , , . , , .
. SQL , . , SQL , , , .
, DBS. , , , .
, , .
, :
- , .
- (
, , () , , . , .
DBS
DBS , . , ( , ) DBS.COMMIT
ROLLBACK , , COMMIT ROLLBACK, , ., . , .
(. A .)
24
SQL . , , . , , , . , .
SQL , : , , , , , , . , , , , , . , , , , , . , ANSI, SQL
SQL , . , , . " " , SQL., , , .
SQL : . , , , ; , , . , , , SYSTEM. ; .
, . , , . , - , . , . , .
, , . , , , , SYSTEM DBA. , .
, :
SYSTEMCATALOG ( )
SYSTEMCOLUMNS
SYSTEMTABLES SYSTEMCATALOG
SYSTEMINDEXES
SYSTEMUSERAUTH
SYSTEMTABAUTH
SYSTEMCOLAUTH
SYSTEMSYNONS
, DBA Stephen SYSTEMCATALOG ,
GRANT SELECT ON SYSTEMCATALOG TO Stephen;
Stephen ( DBA, Chris, , Adrian, Londoncust).
SELECT tname, owner, numcolumns, type, CO
FROM SYSTEMCATALOG;
=============== SQL Execution Log ===============
| SELECT tname, owner, numcolumns, type, CO |
| FROM SYSTEMCATALOG; |
| ================================================ |
| tname owner numcolumns type CO |
| ------------- ------- ---------- ---- --- |
| SYSTEMCATALOG SYSTEM 4 B |
| Salespeople Chris 4 B |
| Customers Chris 5 B |
| Londoncust Adrian 5 V Y
| Orders Chris 5 B |
==================================================
24.1. SYSTEMCATALOG
, . ; ; , ; , B ( ) V ( ). (NULL) , V; , .
,
SYSTEMCATALOG ( ) . . SYSTEMCATALOG.SYSTEMCATALOG , . , SYSTEMTABLES.
SYSTEMCATALOG , ; , , SYSTEMCATALOG, SYSTEMTABLES.
, SYSTEM. , , :
CREATE VIEW Datatables
AS SELECT *
FROM SYSTEMCATALOG
WHERE owner <> 'SYSTEM';
, . , , . USER, SQL ID , , .
, :
CREATE VIEW Owntables
AS SELECT *
FROM SYSTEMCATALOG
WHERE Owner = USER;
:
GRANT SELECT ON Owntables TO PUBLIC;
(SELECT) SYSTEMCATALOG, .
SYSTEMCOLUMNS. SYSTEMCOLUMNS . , SYSTEMCOLUMNS, ( , ):
tname
cname datatype cnumber tabownerSalespeople snum integer 1 Diane
Salespeople sname char 2 Diane
Salespeople city char 3 Diane
Salespeople comm decimal 4 Diane
Customers cnum integer 1 Claire
Customers cname char 2 Claire
Customers city char 3 Claire
Customers rating integer 4 Claire
Customers snum integer 5 Claire
Orders onum integer 1 Diane
Orders odate date 2 Diane
Orders amt decimal 3 Diane
Orders cnum integer 4 Diane
Orders snum integer 5 Diane
, . , , , , .
, : tname ( ), tabowner ( ), cname ( ), . datatype ( ) . cnumber ( ) . , , , .
, SYSTFMCATALOG, :
tname
owner numcolumns type COSYSTEMCOLUMNS System 8 B
SQL , , .
, , , SYSTEMCOLUMNS :
CREATE VIEW Owncolumns
AS SELECT *
FROM SYSTEMCOLUMNS
WHERE tabowner = USER;
GRANT SELECT ON Owncolumns TO PUBLIC;
SQL ()
SYSTEMCATALOG SYSTEMCOLUMNS, , . , .COMMENT ON , . TABLE, SYSTEMCATALOG, COLUMN, SYSTEMCOLUMNS.
:
COMMENT ON TABLE Chris.Orders
IS 'Current Customer Orders';
SYSTEMCATALOG. , 254 .
, , tname=Orders, owner=Chris. SYSTEMCATALOG:
SELECT tname, remarks
FROM SYSTEMCATALOG
WHERE tname = 'Orders' AND owner = 'Chris';
24.2.
=============== SQL Execution Log ===============
| SELECT tname, remarks |
| FROM SYSTEMCATALOG |
| WHERE tname = 'Orders' |
| AND owner = 'Chris' |
| ; |
| ================================================ |
| tname remarks |
| ------------- ----------------------- |
| Orders Current Customers Orders |
=
24.2. SYSTEMCATALOG
SYSTEMCOLUMNS . , :
COMMENT ON COLUMN Orders.onum
IS 'Order #';
SYSTEMCOLUMNS:
SELECT cnumber, datatype, cname, remarks
FROM SYSTEMCOLUMNS
WHERE tname = 'Orders' AND tabowner = 'Chris' AND cname = onum;
24.3.
,
COMMENT ON . . , , :COMMENT ON COLUMN Orders.onum
IS ";
.
=============== SQL Execution Log ===============
| SELECT cnumber, datatype, cname, remarks |
| FROM SYSTEMCOLUMNS |
| WHERE tname = 'Orders' |
| AND tabowner = 'Chris' |
| AND cname = 'onum' |
| ; |
| ================================================ |
| cnumber datatype cname remarks |
| ---------- --------- ------ ------------ |
|
==================================================
24.3: SYSTEMCOLUMNS
, :
SYSTEMINDEXES
SYSTEMINDEXES :
iname
( )iowner
tname
cnumber
tabowner
numcolumns
cposition
isunique
(Y N), , snum :
SELECT iname, iowner, tname, cnumber, isunique
FROM SYSTEMINDEXES
WHERE iname = 'salesperson';
24.4.
=============== SQL Execution Log ================
| SELECT iname, iowner, tname, cnumber, isunique |
| FROM SYSTEMINDEXES |
| WHERE iname = 'salespeople' |
| ; |
| ================================================= |
| iname iowner tname cnumber isunique |
| ----------- ------ ---------- ------- -------- |
| salesperson Stephan Customers 5 N |
====================================
24.4: SYSTEMINDEXES
SYSTEMUSERAUTH
SYSTEMUSERAUTH , :
username (ID)
password
resource RESOURCE
dba DBA
, 22, CONNECT (), RESOURCE () DBA.
CONNECT , . resource dba Y (, ) No (, ).
(password) , . , .
, RESOURCE, DBA, :
SELECT username, dba
FROM SYSTEMUSERAUTH
WHERE resource = 'Y';
24.5.
SYSTEMTABAUTH ,
SYSTEMTABAUTH :
COLUMN
username
,grantor
,tname
,owner
tnameselauth
SELECTinsauth
INSERTdelauth
DELETE( auth) Y, N, G. G , . , N ( , - ).
=============== SQL Execution Log ================
| SELECT username, dba |
| FROM SYSTEMUSERAUTH |
| WHERE resource = 'Y' |
| ; |
| ================================================= |
| username dba |
| ----------- ------
| Diane N |
| Adrian Y |
===================================================
24.5. RESOURCE
. tname, - ( , ), (), . ( ), .
UPDATE
REFERENCES , , . , , . .SELECT, INSERT DELETE, Adrian , ( 24.6):
SELECT username, selauth, insauth, delauth
FROM SYSTEMTABAUTH
WHERE grantor = 'Adrian' AND tname = 'Customers';
=============== SQL Execution Log ================
| SELECT username, selauth, insauth, delauth |
| FROM SYSTEMTABAUTH |
| WHERE grantor = 'Adrian' |
| AND tname = 'Customers' |
| ; |
| ================================================= |
| username selauth insauth delauth |
| ----------- ------- -------- -------- |
| Claire G Y N |
| Norman Y Y Y |
==========================================
24.6. Adrian
, Adrian Claire INSERT SELECT , . Norman SELECT, INSERT DELETE, . Claire DELETE - , .
SYSTEMCOLAUTH
username
grantor
tname
cname
owner
tnameupdauth
UPDATErefauth
REFERENCESupdauth refauth Y, N, G; N . , . SYSTEMTABAUTH cname, . .
SYSTEMTABAUTH .
, REFERENCES, ( 24
.7)SELECT owner, tname, cname
FROM SYSTEMCOLAUTH
WHERE refauth IN ('Y', 'G') AND username = USER
ORDER BY 1, 2;
, , , , , (.. ).
=============== SQL Execution Log ================
| SELECT OWNER, TNAME, CNAME |
| FROM SYSTEMCOLAUTH |
| WHERE refaulth IN ('Y' , 'G') |
| AND username = USER |
| ORDER BY 1, 2 |
| ; |
| ================================================= |
| owner tname cname |
| ----------- ----------- ------- |
| Diane Customers cnum |
| Diane Salespeople sname |
| Diane Salespeople sname |
| Gillan Cu
===================================================
24.7. INSERT
SYSTEMSYNONS
SYSTEMSYNONS :
synonym
synowner , ( PUBLIC ())
tname
tabowner
, Adrian Clients , Diane, Customers . ( 24.8):
SELECT *
FROM SYSTEMSYNONS
WHERE tname = 'Customers'
=============== SQL Execution Log ================
| SELECT * |
| FROM SYSTEMSYNONS |
| WHERE tname = 'Customers' |
| ; |
| ================================================= |
| synonym synowner tname tabowner |
| ----------- ----------- ---------- ---------- |
| Clients Adrian Customers
| Customers PUBLIC Customers Diane |
===================================================
24.8:
, . , , . , ( 24.9):
SELECT a.tname, a.cname, iname, cposition
FROM SYSTEMCOLUMNS a, SYSTEMINDEXES b
WHERE a.tabowner = b.tabowner AND a.tname = b.tname AND
a.cnumber = b.cnumber
ORDER BY 3 DESC, 2;
, . salesno snum; - ( ) iname. , custsale, , . snum cnum , snum , cposition.
=============== SQL Execution Log ================
| SELECT a.tname, a.cname, iname, cposition |
| FROM SYSTEMCOLUMNS a, SYSTEMINDEXES b |
| WHERE a.tabowner = b.tabowner |
| AND a.tname = b.tname |
| AND a.cnumber = b.cnumber |
| ORDER BY 3 DESC, 2; |
| ================================================= |
| tname cname iname cposition |
| ----------- ------- -------- ------------ |
| Salespeople sname salesno 1 |
| Customers cnum custsale 2 |
|
===================================================
24.9.
. :
SELECT *
FROM SYSTEMCOLUMNS
WHERE tname IN (SELECT tname
FROM SYSTEMCATALOG);
, , . , , , SYSTEMTABCOLS, SYSTEMTABLES.
, SQL , . . , ( ) SYSTEMCATALOG SYSTEMCOLUMNS. , .
, , SQL . , SQL , , , , SQL, .
(. A .)
25
, SQL . SQL , . , SQL , ( ). , , , , , , ANSI .
SQL , , SQL , , . , , . , SQL , , , (
)., SQL, , , , , , .
, SQL , SQL .
if ... then (" ... "), for ... do (" ... ") while ... repeat (" ... ") , , , , . , SQL , , , - .
, , . , , , , , , , , . , . , (, ).
SQL , , , SQL , , .
SQL ,
EXEC SQL (EXECute SQL). , SQL ., ANSI SQL . , , , SQL, . SQL, , SQL, , ANSI . ANSI ( ), SQL : , , , /1. C , . SQL , , , .
, ( ), SQL , . , .
ANSI, SQL. . , SQL. , , , ( - ). , SQL , . , . , SQL, . SQL , .
, SQL,
ID . ID , , , SQL, . -, SQL , , . , , CONNECT ., SQL . , . ANSI SQL /1, , , ; B. .
, , DATE, ANSI, , ANSI . , , SQL. SQL , . (SQL, , SQL, .)
, , . :
SQL , , , .
, , : id_num, salesperson, loc comm. , . SQL :
EXEC SQL INSERT INTO Salespeople
VALUES (:id_num, :salesperson, :loc, :comm)
. , comm , , .
, . , SQL , .
PL/1 ,
END-EXEC, ., , ( ) , SQL . SQL .
, , , , , :
while not end-ot-file (input) do
begin
readln(id_num, salesperson, loc, comm);
EXEC SOL INSERT INTO Salespeople
VALUES (:id_num, :salesperson, :loc, :comm);
end;
, , , , , , , . , ( ,
readln ). ., , , .12:
while not end-ot-file (input) do
begin
readln (id_num, salesperson, loc, comm);
if comm >= .12 then EXEC SQL INSERT INTO Salespeople
VALUES (:id_num, :salesperson, :loc, :comm);
end;
, comm >= .12, . , , .
, SQL,
SQL DECLARE SECTION ( ), . , - , , . SQL BEGIN DECLARE SECTION ( ) END DECLARE SECTION ( ), , EXEC SQL ()., , :
EXEC SQL BEGIN DECLARE SECTION;
Var
id-num: integer;
Salesperson: packed array (1..10) ot char;
loc: packed array (1..10) ot char;
comm: real;
EXEC SQL END DECLARE SECTION;
,
Var , ( ) , , (, loc loc[3])., , SQL.
, SQL, SQL, . SELECT,
INTO. Peel .EXEC SQL SELECT snum, sname, city, comm
INTO :id_num, :salesperson, :loc, :comm
FROM Salespeople
WHERE snum = 1001;
INTO. , , INTO, , , .
INTO, . INTO . . , . . ,
SELECT INTO :- , , , , , . , , , , , 17 18.
- GROUP BY.
- SELECT DISTINCT , ( ), :
EXEC SQL SELECT DISTINCT snum
INTO :salesnum
FROM Customers
WHERE snum = (SELECT snum
FROM Salespeople
WHERE sname = 'Motika');
, Salespeople.sname
Salespeople.snum , , Customers.snum , Salespeople.snum, , ., , , , , , , . ! , , , , , . , , , SELECT INTO .
, , , .
SQL , -, , . , . , . , . , , ? , ,
., , . SQL , , , .
, . , . , , . DECLARE CURSOR, :
EXEC SQL DECLARE CURSOR Londonsales FOR
SELECT *
FROM Salespeople
WHERE city = 'London';
; . , , , , . , , : , ... ... . , ORDER BY - , .
, , :
EXEC SQL OPEN CURSOR Londonsales;
, , DECLARE FETCH. , FETCH, , .
EXEC SQL FETCH Londonsales INTO :id_num, :salesperson, :loc, :comm;
, .
FETCH . , FETCH , , , , , , , ., , , , ,
Look_at_more:=True;
EXEC SQL OPEN CURSOR Londonsales;
while Look_at_more do
begin
EXEC SQL FETCH Londonsales
INTO :id_num, :Salesperson, :loc, :comm;
writeln (id_num, Salesperson, loc, comm);
writeln ('Do you want to see more data? (Y/N)');
readln (response);
it response = 'N' then Look_at_more:=False
end;
EXEC SQL CLOSE CURSOR Londonsales;
,
:= " ", = "". writeln , .,
Look_at_more "", , . , . , . N (), , .Look_at_more
response boolean char , SQL, SQL., -SQL . ,
CLOSE CURSOR OPEN CURSOR. , , , OPEN CURSOR, ., , .
, SQL , . , , .
, . FETCH , , INTO. , , , , , N.
SQLCODE
, , , . , , , SQL .
SQLCODE ( SQLCOD ) . , SQL, B. SQLCODE , SQL. :1. , . -:
) SELECT .
) FETCH , .
) INSERT (, , ,
).) UPDATE DELETE , , , .
, SQLCODE = 100.
2. , . , SQLCOD
E = 0.3. . , , (. 23).
SQLCODE = , . , . , , , , , , , . , , .
, , , , :
Look_at_more:=True;
EXEC SQL OPEN CURSOR Londonsales;
while Look_at_more and SQLCODE = O do
begin
EXEC SQL FETCH Londonsales
INTO :id_num, :Salesperson, :loc, :comm;
writeln (id_num, Salesperson, loc, comm);
writeln ('Do you want to see more data? (Y/N)');
readln (response);
If response = 'N' then Look_at_more:=False;
end;
EXEC SQL CLOSE CURSOR Londonsales;
. , , , . , SQL
GOTO. , SQL , GOTO , SQLCODE. WHENEVER. :EXEC SQL WHENEVER SQLERROR GOTO Error_handler;
EXEC SQL WHENEVER NOT FOUND CONTINUE;
SQLERROR
, SQLCODE < 0; NOT FOUND , SQLCODE = 100. ( SQLWARNING.)Error_handler , , (GOTO ). , , , ( ). , .
CONTINUE
- SQLCODE. , WHENEVER, SQLCODE. , , , () . , INSERT, , , - , , . , :EXEC SQL WHENEVER NOT FOUND GOTO No_rows;
No_rows
, . , , :EXEC SQL WHENEVER NOT FOUND CONTINUE;
, , , .
, , . , UPDATE DELETE. , , . 16, SQL , :
EXEC SQL DELETE FROM Customers
WHERE rating < (SELECT AVG (rating)
FROM Customers);
, , , , DELETE . :
EXEC SQL DECLARE Belowavg CURSOR FOR
SELECT *
FROM Customers
WHERE rating < (SELECT AVG (rating)
FROM Customers);
, :
EXEC SQL WHENEVER SQLERROR GOTO Error_handler;
EXEC SQL OPEN CURSOR Belowavg;
while not SOLCODE = 100 do
begin
EXEC SOL FETCH Belowavg INTO :a, :b, :c, :d, :e;
EXEC SOL DELETE FROM Customers
WHERE CURRENT OF Belowavg;
end;
EXEC SOL CLOSE CURSOR Belowavg;
WHERE CURRENT OF , DELETE , . , , DELETE , , , .
. , (. 21). , ORDER BY UNION, , , . , ,
. FETCH. UPDATE ., =300, . :
EXEC SOL DECLARE CURSOR High_Cust AS
SELECT *
FROM Salespeople
WHERE snum IN (SELECT snum
FROM Customers
WHERE rating = 300);
:
EXEC SQL OPEN CURSOR High_cust;
while SQLCODE = 0 do
begin
EXEC SOL FETCH High_cust
INTO :id_num, :salesperson, :loc, :comm;
EXEC SQL UPDATE Salespeople
SET comm = comm + .01
WHERE CURRENT OF High_cust;
end;
EXEC SQL CLOSE CURSOR High_cust;
: , , UPDATE . FOR UPDATE <column list>. High_cust , UPDATE comm, :
EXEC SQL DECLARE CURSOR High_Cust AS
SELECT *
FROM Salespeople
WHERE snum IN (SELECT snum
FROM Customers
WHERE rating = 300)
FOR UPDATE OF comm;
, .
(NULLS) SQL. . NULL , NULL SQL, . NULL , , :
SQLCODE , . , . , NULL , . , , NULL . , , indicator ().indicator, SQL . , SQL. , , NULL , indicator, . indicator SQL ,
, - , , , INDICATOR.indicator 0. , NULL, indicator . indicator, , NULL. , city comm, , NOT NULL, SQL , i_a i_b.
, indicator. indicator, indicator.
:
EXEC SQL OPEN CURSOR High_cust;
while SQLCODE = O do
begin
EXEC SQL FETCH High_cust
INTO :id_num, :salesperson, :loc, :i_a, :comm INDlCATOR, :i_b;
If i_a >= 0 and i_b >= 0
then {no NULLs produced}
begin
EXEC SQL UPDATE Salespeople
SET comm = comm + .01
WHERE CURRENT OF Hlgh_cust
end {then}
else {one or both NULL}
begin
If i_a < 0 then writeln('salesperson ', id_num, ' has no city');
If i_b < 0 then writeln('salesperson ', id_num, ' has no commission')
end {else}
end; {while}
EXEC SQL CLOSE CURSOR High_cust;
, , INDICATOR , , , . , UPDATE NULL .
NULL , , , NULL .
: indicator , , WHERE SQL.
, .
, indicator, , NULL SQL.
, , if ... then, indicator, =NULL. , -. , NULL city city, indicator i_city, city . , ; . , i_city . , :
If city = 'London' then comm := comm + .01
else comm := comm - .01
, city,
'London', . , , . SQL -:EXEC SQL UPDATE Salespeople
SET comm = comm + .01
WHERE city = 'London';
EXEC SQL UPDATE Salespeople
SET comm = comm - .01;
WHERE city <> 'London';
, SQL .
city SQL NULL, , comm, , .
indicator,
, , NULL :If i_city >= O then
If city = 'London' then comm := comm + .01
else comm: = comm - .01;
, "", , city = NULL. , .
indicator NULL. UPDATE INSERT SELECT. indicator , NULL . , NULL city comm, , , indicator i_a i_b ; :
EXEC SQL INSERT INTO Salespeople
VALUES (:Id_num, :salesperson, :loc:i_a, :comm:i_b);
indicator , . SQL , . VARCHAR LONG ( C). , , . indicator, , , , , , .
, indicator > 0, < 0.
SQL , . SQL , . SQL , , , , , , . ANSI SQL : , , , PL/I. , . SQL, :
- SQL EXEC SQL , .
- , SQL, SQL , .
- , SQL.
- , INTO, , .
- , . ( ), ( ), ( ). , FETCH , .
- . , , (
: , , . , , ( ). , , . , .
, , , -, , . , , ; , .
- , snum cnum , , . , snum . , (ANSI , , , (.. , cnums , ). , , .
- , ANSI , . ?
- , city , .01 , .01 , -. , , , .02 , , , , , .02. , , , . , city comm NULL , , SQL. : .
(. A .)
A
cnum
rating
. .
, , .
( )
( DML)
SQL
SELECT onum, amt, odate
FROM Orders;
SELECT *
FROM Customers
WHERE snum = 1001;
SELECT city, sname, snum, comm
FROM Salespeople;
SELECT rating, cname
FROM Customers
WHERE city = 'SanJose';
SELECT DISTINCT snum
FROM Orders;
SELECT *
FROM Orders
WHERE amt > 1000;
SELECT sname, city
FROM Salespeople
WHERE city = 'London' AND comm > .10;
SELECT *
FROM Customers
WHERE rating > 100 OR city = 'Rome';
SELECT *
FROM Customers
WHERE NOT rating < = 100 OR city = 'Rome';
SELECT *
FROM Customers
WHERE NOT (rating < = 100 AND city < > 'Rome');
.
onum
amt odate cnum snum3001 18.69 10/03/1990 2008 1007
3003 767.19 10/03/1990 2001 1001
3005 5160.45 10/03/1990 2003 1002
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
onum amt odate cnum snum
3001 18.69 10/03/1990 2008 1007
3003 767.19 10/03/1990 2001 1001
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
SELECT *
FROM Salespeople;
SELECT *
FROM Orders
WHERE odate IN (10/03/1990,10/04/1990);
SELECT *
FROM Orders
WHERE odate BETWEEN 10/03/1990 AND 10/04,1990;
SELECT *
FROM Customers
WHERE snum IN (1001,1004);
SELECT *
FROM Customers
WHERE cname BETWEEN 'A' AND 'H';
SELECT *
FROM Customers
WHERE cname LIKE 'C%';
SELECT *
FROM Orders
WHERE amt <> 0 AND (amt IS NOT NULL);
SELECT *
FROM Orders
WHERE NOT (amt = 0 OR amt IS NULL);
SELECT COUNT(*)
FROM Orders
WHERE odate = 10/03/1990;
SELECT COUNT (DISTINCT city)
FROM Customers;
SELECT cnum, MIN (amt)
FROM Orders
GROUP BY cnum;
SELECT MIN (cname)
FROM Customers
WHERE cname LIKE 'G%';
SELECT city, MAX (rating)
FROM Customers
GROUP BY city;
SELECT odate, count (DISTINCT snum)
FROM Orders
GROUP BY odate;
SELECT onum, snum, amt * .12
FROM Orders;
SELECT 'For the city ', city, ', the highest rating is ', MAX (rating)
FROM Customers
GROUP BY city;
SELECT rating, cname, cnum
FROM Customers
ORDER BY rating DESC;
SELECT odate, SUM (amt)
FROM Orders
GROUP BY odate
ORDER BY 2 DESC;
SELECT onum, cname
FROM Orders, Customers
WHERE Customers.cnum = Orders.cnum;
SELECT onum, cname, sname
FROM Orders, Customers, Salespeople
WHERE Customers.cnum = Orders.cnum AND Salespeople.snum = Orders.snum;
SELECT cname, sname, comm
FROM Salespeople, Customers
WHERE Salespeople.snum = Customers.snum AND comm * .12;
SELECT onum, comm * amt
FROM Salespeople, Orders, Customers
WHERE rating > 100 AND
Orders.cnum = Customers.cnum AND
Orders.snum = Salespeople.snum;
SELECT first.sname, second.sname
FROM Salespeople first, Salespeople second
WHERE first.city = second.city AND first.sname < second.sname;
.
SELECT cname, first.onum, second.onum
FROM Orders first, Orders second, Customers
WHERE first.cnum = second.cnum AND
first.cnum = Customers.cnum AND
first.onum < second.onum;
, .
SELECT a.cname, a.city
FROM Customers a, Customers b
WHERE a.rating = b.rating AND b.cnum = 2001;
SELECT *
FROM Orders
WHERE cnum = (SELECT cnum
FROM Customers
WHERE cname = 'Cisneros');
SELECT *
FROM Orders
WHERE cnum IN (SELECT cnum
FROM Customers
WHERE cname = 'Cisneros');
SELECT DISTINCT cname, rating
FROM Customers, Orders
WHERE amt > (SELECT AVG (amt)
FROM Orders)
AND Orders.cnum = Customers.cnum;
SELECT snum, SUM (amt)
FROM Orders
GROUP BY snum
HAVING SUM (amt) > (SELECT MAX (amt)
FROM Orders);
SELECT cnum, cname
FROM Customers outer
WHERE rating = (SELECT MAX (rating)
FROM Customers inner
WHERE inner.city = outer.city);
:
SELECT snum, sname
FROM Salespeople main
WHERE city IN (SELECT city
FROM Customers inner
WHERE inner.snum <> main.snum);
:
SELECT DISTINCT first.snum, sname
FROM Salespeople first, Customers second
WHERE first.city = second.city AND first.snum <> second.snum;
, , : - . . , city , sn
ums . , , , . , .SELECT *
FROM Salespeople first
WHERE EXISTS (SELECT *
FROM Customers second
WHERE first.snum = second.snum AND rating = 300);
SELECT a.snum, sname, a.city, comm
FROM Salespeople a, Customers b
WHERE a.snum = b.snum AND b.rating = 300;
SELECT *
FROM Salespeople a
WHERE EXISTS (SELECT *
FROM Customers b
WHERE b.city = a.city AND a.snum <> b.snum);
SELECT *
FROM Customers a
WHERE EXISTS (SELECT *
FROM Orders b
WHERE a.snum = b.snum AND a.cnum <> b.cnum)
SELECT *
FROM Customers
WHERE rating >= ANY (SELECT rating
FROM Customers
WHERE snum = 1002);
cnum
cname city rating snum2002 Giovanni Rome 200 1003
2003 Liu San Jose 200 1002
2004 Grass Berlin 300 1002
2008 Cisneros SanJose 300 1007
SELECT *
FROM Salespeople
WHERE city <> ALL (SELECT city
FROM Customers);
SELECT *
FROM Salespeople
WHERE NOT city = ANY (SELECT city
FROM Customers);
SELECT *
FROM Orders
WHERE amt > ALL (SELECT amt
FROM Orders a, Customers b
WHERE a.cnum = b.cnum AND b.city = 'London');
SELECT *
FROM Orders
WHERE amt > (SELECT MAX (amt)
FROM Orders a, Customers b
WHERE a.cnum = b.cnum AND b.city = 'London');
SELECT cname, city, rating, 'High Rating'
FROM Customers
WHERE rating >= 200
UNION
SELECT cname, city, rating, ' Low Ratlng'
FROM Customers
WHERE rating < 200;
SELECT cname, city, rating, 'High Rating'
FROM Customers
WHERE rating >= 200
UNION
SELECT cname, city, rating, ' Low Rating'
FROM Customers
WHERE NOT rating >= 200;
. ,
"Low Rating" , "High Rating" .SELECT cnum, cname
FROM Customers a
WHERE 1 < (SELECT COUNT (*)
FROM Orders b
WHERE a.cnum = b.cnum)
UNION
SELECT snum, sname
FROM Salespeople a
WHERE 1 < (SELECT COUNT (*)
FROM Orders b
WHERE a.snum = b.snum)
ORDER BY 2;
SELECT snum
FROM Salespeople
WHERE city = 'San Jose'
UNION
(SELECT cnum
FROM Customers
WHERE city = 'San Jose'
UNION ALL
SELECT onum
FROM Orders
WHERE odate = 10/03/1990);
INSERT INTO Salespeople (city, cname, comm, cnum)
VALUES ('San Jose', 'Blanco', NULL, 1100);
DELETE FROM Orders WHERE cnum = 2006;
UPDATE Customers
SET rating = rating + 100
WHERE city = 'Rome';
UPDATE Customers
SET snum = 1004
WHERE snum = 1002;
INSERT INTO Multicust
SELECT *
FROM Salespeople
WHERE 1 < (SELECT COUNT (*)
FROM Customers
WHERE Customers.snum = Salespeople.snum);
DELETE FROM Customers
WHERE NOT EXISTS (SELECT *
FROM Orders
WHERE cnum = Customers.cnum);
UPDATE Salespeople
SET comm = comm + (comm * .2)
WHERE 3000 < (SELECT SUM (amt)
FROM Orders
WHERE snum = Salespeople.snum);
, , 1.0 (100%):
UPDATE Salespeople
SET comm = comm + (comm * .2)
WHERE 3000 < (SELECT SUM (amt)
FROM Orders
WHERE snum = Salespeople.snum)
AND comm + (comm * .2) < 1.0;
, .
CREATE TABLE Customers
(cnum integer,
cname char(10),
city char(10),
rating integer,
snum integer);
CREATE INDEX Datesearch ON Orders(odate);
( , .)
CREATE UNIQUE INDEX Onumkey ON Orders(onum);
CREATE INDEX Mydate ON Orders(snum, odate);
CREATE UNIQUE INDEX Combination ON Customers(snum, rating);
18CREATE TABLE Orders
(onum integer NOT NULL PRIMARY KEY,
amt decimal,
odate date NOT NULL,
cnum integer NOT NULL,
snum integer NOT NULL,
UNIOUE (snum, cnum));
CREATE TABLE Orders
(onum integer NOT NULL UNIQUE,
amt decimal,
odate date NOT NULL,
cnum integer NOT NULL,
snum integer NOT NULL,
UNIQUE (snum, cnum));
.
CREATE TABLE Salespeople
(snum integer NOT NULL PRIMARY KEY,
sname char(15) CHECK (sname BETWEEN 'AA' AND 'MZ'),
city char(15),
comm decimal NOT NULL DEFAULT = .10);
CREATE TABLE Orders
(onum integer NOT NULL,
amt decimal,
odate date,
cnum integer NOT NULL,
snum integer NOT NULL,
CHECK ((cnum > snum) AND (onum > cnum)));
CREATE TABLE Cityorders
(onum integer NOT NULL PRIMARY KEY,
amt decimal,
cnum integer,
snum integer,
city char (15),
FOREIGN KEY (onum, amt, snum) REFERENCES Orders (onum, amt, snum),
FOREIGN KEY (cnum, city) REFERENCES Customers (cnum, city));
CREATE TABLE Orders
(onum integer NOT NULL,
amt decimal,
odate date,
cnum integer NOT NULL,
snum integer,
prev integer,
UNIQUE (cnum, onum),
FOREIGN KEY (cnum, prev) REFERENCES Orders (cnum,onum));
CREATE VIEW Highratings
AS SELECT *
FROM Customers
WHERE rating = (SELECT MAX (rating)
FROM Customers);
CREATE VIEW Citynumber
AS SELECT city, COUNT (DISTINCT snum)
FROM Salespeople
GROUP BY city;
CREATE VIEW Nameorders
AS SELECT sname, AVG (amt), SUM (amt)
FROM Salespeople, Orders
WHERE Salespeople.snum = Orders.snum
GROUP BY sname;
CREATE VIEW Multcustomers
AS SELECT *
FROM Salespeople a
WHERE 1 < (SELECT COUNT (*)
FROM Customers b
WHERE a.snum = b.snum);
#1 , DISTINCT.
#2 , , GROUP BY.
#3 , #1,
.CREATE VIEW Commissions
AS SELECT snum, comm
FROM Salespeople
WHERE comm BETWEEN .10 AND .20
WITH CHECK OPTION;
CREATE TABLE Orders
(onum integer NOT NULL PRIMARY KEY,
amt decimal,
odate date DEFAULT VALUE = CURDATE,
snum integer,
cnum integer);
CREATE VIEW Entryorders
AS SELECT onum, amt, snum, cnum
FROM Orders;
GRANT UPDATE (rating) ON Customers TO Janet;
GRANT SELECT ON Orders TO Stephen WITH GRANT OPTION;
REVOKE INSERT ON Salespeople FROM Claire;
1:
CREATE VIEW Jerrysview
AS SELECT *
FROM Customers
WHERE rating BETWEEN 100 AND 500
WITH CHECK OPTION;
2:
GRANT INSERT, UPDATE ON Jerrysview TO Jerry;
1:
CREATE VIEW Janetsview
AS SELECT *
FROM Customers
WHERE rating = (SELECT MIN (rating)
FROM Customers);
2:
GRANT SELECT ON Janetsview TO Janet;
CREATE DBSPACE Myspace
(pctindex 15,
pctfree 40);
CREATE SYNONYM Orders FOR Diane.Orders;
SELECT a.tname, a.owner, b.cname, b.datatype
FROM SYSTEMCATOLOG a, SYSTEMCOLUMNS b
WHERE a.tname = b.tname AND
a.owner = b.owner AND
a.numcolumns > 4;
: - , , a b . .
SELECT tname, synowner, COUNT (ALL synonym)
FROM SYTEMSYNONS
GROUP BY tname, synowner;
SELECT COUNT (*)
FROM SYSTEMCATALOG a
WHERE numcolumns/2 < (SELECT COUNT (DISTINCT cnumber)
FROM SYSTEMINDEXES b
WHERE a.owner = b.tabowner AND a.tname = b.tname);
EXEC SQL BEGIN DECLARE SECTION;
SQLCODE : integer;
{ }
cnum : integer;
snum : integer;
custnum : integer;
salesnum : integer;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE Wrong_Orders AS CURSOR FOR
SELECT cnum, snum
FROM Orders a
WHERE snum <> (SELECT snum
FROM Customers b
WHERE a.cnum = b.cnum);
{ SQL . , . }
EXEC SQL DECLARE Cust_assigns AS CURSOR FOR
SELECT cnum, snum
FROM Customers;
{
begin { }
EXEC SQL OPEN CURSOR Wrong_Orders;
while SQLCODE = 0 do { , Wrong_Orders }
begin
EXEC SQL FETCH Wrong_Orders INTO (:cnum, :snum);
if SQLCODE = 0 then
begin { Wrong_Orders , }
EXEC SQL OPEN CURSOR Cust_Assigns;
repeat
EXEC SQL FETCH Cust_Assigns INTO (:custnum, :salesnum);
until :custnum = :cnum;
{ FETCH ... Cust_Assigns , cnum, Wrong_Orders }
EXEC SQL CLOSE CURSOR Cust_assigns;
{ . salesnum. }
EXEC SQL UPDATE Orders
SET snum = :salesnum
WHERE CURRENT OF Wrong_Orders;
end; { SQLCODE = 0}.
end; { SQLCODE ... }
EXEC SQL CLOSE CURSOR Wrong_Orders;
end; { }
, , , onum , Wrong_Orders. UPDATE WHERE onum =:ordernum ( o
rdernum ), WHERE CURRENT Of Wrong_Orders.( ):
EXEC SQL BEGIN DECLARE SECTION;
SQLCODE : integer;
odernum : integer;
cnum : integer;
snum : integer;
custnum : integer;
salesnum : integer;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE Wrong_Orders AS CURSOR FOR
SELECT onum, cnum, snum
FROM Orders a
WHERE snum <> (SELECT snum
FROM Customers b
WHERE a.cnum = b.cnum);
EXEC SQL DECLARE Cust_assigns AS CURSOR FOR
SELECT cnum, snum
FROM Customer
begin { }
EXEC SQL OPEN CURSOR Wrong_Orders;
while SQLCODE = 0 do { Wrong_Orders }
begin
EXEC SQL FETCH Wrong_Orders INTO (:odernum, :cnum, :snum);
if SQLCODE = 0 then
begin
EXEC SQL OPEN CURSOR Cust_Assigns;
repeat
EXEC SQL FETCH Cust_Assigns INTO (:custnum, :salesnum);
until :custnum = :cnum;
EXEC SQL CLOSE CURSOR Cust_assigns;
EXEC SQL UPDATE Orders
SET snum = :salesnum WHERE CURRENT OF Wrong_Orders;
end; { If SQLCODE = 0 }
end; { While SQLCODE ... do }
EXEC SQL CLOSE CURSOR Wrong_Orders;
end; { }
EXEC SQL BEGIN DECLARE SECTION;
SQLCODE : integer;
newcity : packed array[1..12] of char;
commnull : boolean;
citynull : boolean;
response : char;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE CURSOR Salesperson AS
SELECT * FROM SALESPEOPLE;
begln { main program }
EXEC SQL OPEN CURSOR Salesperson;
EXEC SQL FETCH Salesperson INTO (:snum, :sname, :city, :i_cit, :comm, :i_com);
{ }
while SQLCODE = 0 do { . }
begin
if i_com < 0 then commnull: = true;
if i_cit < 0 then citynull: = true;
{ , NULLS.}
if citynull
then begin
write (' city ', snum,
' ? (Y/N)');
{ city NULL . }
read (response);
{ . }
end { citynull }
else begin { citynull }
if not commnull then
{ -NULL }
begin
if city='London' then comm:=comm*.02*.02
else comm:=comm+.02;
end;
{ - commnull, begin end . }
write (' city ', snum, '', city,
' ? (Y/N)');
{ : , , , . }
read (response);
{ , citynull . }
end; { citynull}
if response = 'Y' then
begin
write (' city:');
read (newcity);
if not commnull then
{ -NULL . }
case newcity of:
begin
'Barcelona' : comm:= comm + .01,
'San Jose' : comm:= comm * .01
end; { commnull}
EXEC SQL UPDATE Salespeople
SET city = :newcity, comm = :comm:i_com
WHERE CURRENT OF Salesperson;
{ NULL comm . }
end; { = 'Y', <> 'Y', . }
EXEC SQL FETCH Salesperson INTO (:snum, :sname, :city, :i_clt, :comm, :l_com);
{ }
end; { SQLCODE = 0}
EXEC SQL CLOSE CURSOR Salesperson;
end; { }
B
,
SQL ANSI, , ., .
( 10) .
, - .
, , .
, .
ANSI ( ):
TEXT
.CHAR
(CHARACTER) , . , . , , 'text'. ('') (').: , , , , .
EXACT NUMERIC
DEC
(DECIMAL) ; , . : . . , . . , , . . = 0 .NUMERIC
DECIMAL , .INT
(INTEGER) . DECIMAL, , , 0. ( , ).SMALLINT
INTEGER, , , , ( ) , INTEGER.APPROXIMATE NUMERIC
FLOAT
10 . , .REAL
, FLOAT, , . .DOUBLE PRECISION
(DOUBLE) , REAL, , , DOUBLE PRECISION REAL.SQL , , SQL, (. 25). , SQL, . , SQL , ANSI SQL : , PL/I, , . , SQL , .
ANSI:
PL/1
SQL PL/1
CHAR CHAR
DECIMAL FIXED DECIMAL
INTEGER FIXED BINARY
FLOAT FLOAT BINARY
SQL
CHAR(<integer>) PIC X (<integer>)
INTEGER PIC S (<nines>) USAGE COMPUTATIONAL
NUMERIC PIC S (<nines with embedded V>) DISPLAY SING LEADING
SEPARATE
SQL
INTEGER INTEGER
REAL REAL
CHAR (<length>) PACKED ARRAY [1..<length>] OF CHAR
SQ
LCHAR CHAR
INTEGER INTEGER
REAL REAL
DOUBLE PRECISION DOUBLE PRECISION
C
SQL, ANSI ISO ( ), , .
. , ,
, ., SQL, B. CHARACTER . , , , , . .
DATE TIME
2, DATE , . , mm/dd/yyyy. IBM . , , ,
, . , , ., :
(ISO) yyyy-mm-dd 1990-10-31
(JIS) yyyy-mm-dd 1990-10-31
IBM (EUR) dd.mm.yyyy 10.31.1990
, , . , , , . ; , A < B , A B .
, , , :
(ISO) hh-mm-ss 21.04.37
(JIS) hh-mm-ss 21.04.37
IBM hh-mm-ss 21.04.37
IBM USA (USA) hh.mm AM/PM 9.04 PM
, . , , (
CURDATE CURTIME) . USER (), .? DATE , TIME.
, ,
TIMESTAMP, .
ANSI , .
CHAR. . , , , , ; , ., . , , UNION.
VARCHAR LONG VARCHAR ( LONG). , CHAR , , VARCHAR , , SQL , .VARCHAR , , . 254 2048 VARCHAR, 16000 LONG. LONG , ; VARCHAR , .
, VARCHAR CHAR. VARCHAR , , , CHAR. , VARCHAR ( ) .
, , , , , CHAR VARCHAR. , LONG . , "" SQL.
.
7, , SQL, . SQL , ,
FORMAT SQL, , . FORMAT :- ( ).
- NULL .
- () .
FORMAT , , , . FORMAT , FORMAT . FORMAT:
FORMAT NULL '_ _ _ _ _ _ _';
FORMAT BTITLE 'Orders Grouped by Salesperson';
FORMAT EXCLUDE (2, 3);
NULL _ _ _ _ _ _ _ ; 'Orders Grouped by Salesperson' ; . , , ORDER BY, . FORMAT , .
, . SET FORMAT; , , . , FORMAT COLUMN :
COLUMN odate FORMAT dd-mon-yy;
10-Oct-90 , .
COMPUTE, , :
SELECT odate, amt
FROM Orders
WHERE snum = 1001
COMPUTE SUM (amt);
Peel, , .
, COMPUTE . ,
BREAK ON odate;
, odate . :
COMPUTE SUM OF amt ON odate;
ON BREAK.
SQL ANSI, , comm * 100. , , , .
SQL, . SELECT , , , . , . , , SELECT:
.
|
ABX(X) |
X ( ) |
|
CEIL(X) |
X , . |
|
FLOOR (X) |
X , . |
|
GREATEST(X,Y) |
. |
|
LEAST(X,Y) |
. |
|
MOD(X,Y) |
X Y. |
|
POWER(X,Y) |
X Y. |
|
ROUND(X,Y) |
X Y. Y , . |
|
SING(X) |
X < 0, X > 0. |
|
SQRT(X) |
X. |
, , , .
|
LEFT(<string>,X) |
() X . |
|
RICHT(<string>,X) |
X |
|
ASCII(<string>) |
ASCII . |
|
CHR(<ASCIIcode>) |
ASCII. |
|
VALUE(<string>) |
. CHAR VARCHAR, . VALUE('3') 3 INTEGER. |
|
UPPER(<string>) |
. |
|
LOWER(<string>) |
. |
|
INlTCAP(<string>) |
. PROPER. |
|
LENGTH(<string>) |
. |
|
<string>||<string> |
, . ( || ). |
|
LPAD(<string>,X,'*') |
'*', , , X. |
|
RPAD(<string>,X, ") |
LPAD, , . |
|
SUBSTR(<string>,X,Y) |
Y X. |
.
|
DAY(<date>) |
. MONTH (), YEAR (), HOUR (), SECOND () . |
|
WEEKDAY(<date>) |
. |
.
|
NVL(<column>,<value>) |
NVL (NULL TO VALUE ) <value> NULL , <column>. <column> =NULL, NVL . |
UNION, 14, , .
INTERSECT () MINUS (). INTERSECT , , MINUS , , . ,SELECT *
FROM Salespeople
WHERE city = 'London'
INTERSECT
SELECT *
FROM Salespeople
WHERE 'London' IN (SELECT city
FROM Customers
WHERE Customers.snum = Salespeople.snum);
, , , , , , . ,
SELECT *
FROM Salespeople
WHERE city = 'London'
MINUS
SELECT *
FROM Salespeople
WHERE 'London' IN (SELECT city
FROM Customers
WHERE Customers.snum = Salespeople.snum);
, , , , . MINUS
DIFFERENCE ().14 , , UNION. . , "
+" , , , . , , NULL , ., , , , ( , ):
SELECT a.snum, sname, cname
FROM Salespeople a, Customers b
WHEREa.snum = b.snum(+);
(UNION):
SELECT a.snum, sname, cname
FROM Salespeople a, Customers b
WHERE a.snum = b.snum
UNION
SELECT snum, sname, '_ _ _ _ _ _ _ _ _ _'
FROM Salespeople
WHERE snum NOT IN (SELECT snum
FROM Customers);
, NULL (. FORMAT , NULL ).
SQL , , , . , :
Journaling
() Auditing ()..
Journaling
. - , , - . . , , , , , , . , , , , . , , :SET JOURNAL ON;
Auditing
. , , , . , , . , . AUDIT:AUDIT INSERT ON Salespeople BY Diane;
ON, BY , , , . AUDIT ALL, AUDIT INSERT, Diane .
D
SQL. , SQL. , SQL; , . ( BNF ):
- .
- SQL (
, (
.,..) , , . , , (**) .: , , ANSI. , .
, ANSI, , -. ,
<predicate> ANSI <predicate> <search condition>.SQL. : .
; SQL , .
<separator> ; <separator> . , SQL ., , . ,
<separator>, SQL. , . , . . , <predicate>, , <predicate> . , <predicate> AND OR <predicate> .<predicate> - . .
|
<separator> |
<comment> | <space> | <newline> |
|
<comment> |
--<string> <newline> |
|
<space> |
|
|
<newline> |
- |
|
<identifier> |
<letter>[{<letter or digit> | <underscore>}... ] : ANSI, , <identifier> 18- . |
|
<underscore> |
- |
|
<percent sign> |
% |
|
<delimiter> |
: , () < > . : = + " - | <> > = < = <string> |
|
<string> |
[ ] : <string>, ('') ('). |
|
<SQL term> |
, . (* *) |
SQL :
|
<query> |
SELECT |
|
<subquery> |
SELECT , , , - . |
|
<value expression> |
<primary> | <primary> <operator> <primary> | <primary> <operator> <value expression> |
|
<operator> |
: + - / * |
|
<primary> |
<column name> | <literal> | <aggregate function> | <built-in constant> | <nonstandard function> |
|
<literal> |
<string> | <mathematical expression> |
|
<built-in constant> |
USER | <implementation-defined constant> |
|
<table name> |
<identifier> |
|
<column spec> |
[<table name> | <alias>]<column n ame> |
|
<grouping column> |
<column spec> | <integer> |
|
<ordering column> |
<column spec> | <integer> |
|
<colconstraint> |
NOT NULL | UNIQUE | CHECK (<predicate>) | PRIMARY KEY | REFERENCES <table name>[(<column name>)] |
|
<tabconstraint> |
UNIQUE (<column list>) | CHECK (<predicate>) | PRIMARY KEY (<column list>) | FOREIGN KEY (<column list>) REFERENCES <table name>[(<column list>)] |
|
<defvalue> |
= <value expression> |
|
<data type> |
(. B ANSI C .) |
|
<size> |
<data type> (. B.) |
|
<cursor name> |
<identifier> |
|
<index name> |
<identifier> |
|
<synonym> |
<identifier> (*nonstandard*) |
|
<owner> |
<Authorization ID> |
|
<column list> |
<column spec> .,.. |
|
<value list> |
<value expression> .,.. |
|
<table reference> |
{ <table name> [<alias>] } .,.. |
<predicate> :
<predicate> ::= [NOT]{ <comparison predicate> | <in predicate> | <null predicate> | <between predicate> | <like predicate> | <quantified predicate> | <exists predicate> } [AND | OR <predicate>]
<predicate>
, , , <exists predicate> <null predicate>, ., NULL . , NULL .
AND, OR NOT <predicate>. NOT = , NOT = , NOT = . AND OR , :
|
AND |
|||
|
OR |
|||
: , , , . AND, , () () , : AND =
.. . NOT , AND OR.
<predicate> .<comparison predicate>
( )<value expresslon> <relational op> <value expresslon> | <subquery> <relatlonal op> ::= = | < | > | < | >= | <>
<value expression> = NULL, <comparison predicate> = ; , .
<relational op>
; , . <value expression> . <subquery> , <value expression> SELECT, <value expression> <comparision predicate>, <subquery> .<between predicate>
<value expression> [NOT] BETWEEN <value expression> AND <value expression>
<between predicate>
A BETWEEN B AND C, <predicate> (A >= B AND <= C). <between predicate> A NOT BETWEEN B AND C, NOT (BETWEEN B AND C). <value expression> <subquery> (*nonstandard*).<in prediicate>
<value expression> [NOT] IN <value list> | <subquery>
<value list> , <value expression> . <subquery>, <value expression> SELECT ( , ANSI). <subquery> , - , , <value list> . , <in predicate> <value expression> <value list>, NOT. A NOT IN (B, C) NOT (A IN (B, C)).
<like predicate>
<charvalue> [NOT] LIKE <pattern> [ESCAPE <escapechar>]
<charvalue>
** <value expression> - . <charvalue> , , <column spec>. <pattern> <string> <charvalue>. <escapechar> - . , :*
<underscore> <pattern> <escapechar>, <charvalue>.*
<percent sign> <pattern> <escapechar>, <charvalue>.*
<escapechar> <pattern> <escapechar>, <charvalue>.*
<pattern>, <charvalue>.,
<like predicate> , NOT. NOT LIKE '' NOT (A LIKE '').<null predicate>
<column spec> IS [NOT] NULL
<column spec>
= IS NULL, NULL . <null predicate> NULL. <column spec> IS NOT NULL , NOT(<column spec> IS NULL).<quantified predicate>
<value expression> <relational op> <quantifier> <subquery> <quantifier> ::= ANY | ALL | SOME
SELECT
<subquery> <value expression>. <subquery> <result set>. <value expression> , <relational operator>, <result set>. :<exists predicate>
:
EXISTS (<subquery>)
<subquery> , <exists predicate> ; , .
SQL. , .
: , EXEC SQL, <SQL term> SQL.
BEGIN DECLARE SECTION
( )
EXEC SQL BEGIN DECLARE SECTION <SQL term>
<host-language variable declarations>
EXEC SQL END DECLARE SECTION<SQL term>
, SQL. SQLCODE .
CLOSE CURSOR
( )
EXEC SQL CLOSE CURSOR <cursor name> <SQL term>;
, .
COMMIT (WORK)
( ())
COMMIT WORK;
, , , , .
CREATE INDEX (*NONSTANDARD*)
( ) (**)
CREATE [UNIQUE] INDEX <Index name> ON <table name> (<column list>);
. UNIQUE , () .
CREATE SYNONYM (*NONSTANDARD*)
( ) (**)
CREATE IPUBLICl SYNONYM <synonym> FOR <owner>.<table name>;
() . , , . , ( ) . PUBLIC , SYSTEM .
CREATE TABLE
( )
CREATE TABLE <table name> ({<column name> <data type>[<size>] [<colconstralnt> ...] [<defvalue>]} .,.. <tabconstraint> .,..);
. . .
<data type>
. <data type> B; <data type>, C. <size> <data type>.<colconstraint> <tabconstraint> , .
<defvalue>
( ) , . (. 17 CREATE TABLE 18 19 <defvalue>).CREATE VIEW
( )
CREATE VIEW <table name> AS <query> [WITH CHECK OPTION];
SQL.
<table name>, <query> , ., , ,
<query>. WITH CHECK OPTION, <predicate> <query>.DECLARE CURSOR
( )
EXEC SQL DECLARE <cursor name> CURSOR FOR <query><SQL term>
<cursor name>, <query>. (. OPEN CURSOR), <query> , ( FETCH) . , <query>, (. 25 ).
DELETE
()
DELETE FROM <table name> {[WHERE <predicate>];} | WHERE CURRENT OF <cursor name><SQL term>
WHERE , . WHERE
<predicate>, , <predicate> . WHERE CURRENT OF () <cursor name>, <table name> <cursor name> . WHERE CURRENT SQL, .EXEC SQL
( SQL)
EXEC SQL <embedded SQL command> <SQL term>
EXEC SQL , SQL, .
FETCH
()
EXEC SQL FETCH <cursorname> INTO <host-varlable llst><SQL term>
FETCH
<query>, <host-variable list>, . <host-variable list> indicator (. 25.)GRANT
( )
()
GRANT ALL [PRIVILEGES] | {SELECT | INSERT | DELETE | UPDATE [(<column llst>)] | REFERENCES [(<column llst>)l } .,.. ON <table name> .,.. TO PUBLIC | <Authorization ID> .,.. [WITH GRANT OPTION];
ALL (), PRIVILEGES (), . PUBLIC () .
.
REFERENCES <column list> . , . UPDATE REFERENCES . GRANT OPTION .()
GRANT DBA | RESOURCE | CONNECT .... TO <Authorization ID> .,.. [IDENTIFIED BY <password>]
CONNECT
.RESOURCE
. DBA .IDENTIFIED BY
CONNECT, .INSERT
()
INSERT INTO <table name> (<column llst>) VALUES (<value llst>) | <query>;
IN
SERT <table name>. VALUES , <table name>. <query> , <table name>. <column list> , <table name>, .OPEN CURSOR
( )
EXEC SQL OPEN CURSOR <cursorname> <SQL term>
OPEN CURSOR ,
<cursor name>. FETCH.REVOKE (*NONSTANDARD*)
( ) ()
REVOKE {ALL [PRIVILEGES] | <privilege> .,..} [ON <table name>] FROM { PUBLIC | <Authorization ID> .,..};
<privelege> GRANT. , REVOKE, , , GRANT. ON , .
ROLLBACK (WORK)
() ()
ROLLBACK WORK;
, . , , .
SELECT
()
SELECT { IDISTINCT | ALL] < value expression > . , . . } / *
[INTO <host variable list> (*embedded only*)]
FROM < table reference > . , . .
[WHERE <predicate>]
[GROUP BY <grouping column> . , . .]
[HAVING <predicate>]
[ORDER BY <ordering column> [ASC | DESC] . , . . ];
(. 3 14). :
- ALL, DISTINCT , ALL.
- SELECT - . - :
- ,
<predicate> WHERE , , . GROUP BY , <value expression> -, , . GROUP BY , - , . <predicate> , <value expression> - . DISTINCT, () .UNION
()
<query> {UNION [ALL] <query> } . . . ;
<query> . <query> <value expression> SELECT 1..n , <data type> <size> 1..n .
UPDATE
()
UPDATE <table name>
SET { <column name> = <value expression> } .,..
{[ WHERE <predlcate>]; } | {[WHERE CURRENT OF <cursorname>] <SQL term>]}
UPDATE
<column name> <value expression>. WHERE <predicate>, <predicate> , . WHERE CURRENT OF, <table name> <cursor name> . WHERE CURRENT OF SQL, . WHERE .WHENEVER
( )
EX
EC SQL WHENEVER <SQLcond> <actlon> <SQL term><SQLcond> ::= SQLERROR | NOT FOUND | SQLWARNING
( )
<action> ::= CONTINUE | GOTO <target> | GOTO <target>
<target> ::=
E
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 |
2:
|
cnum |
cname |
city |
rating |
snum |
|
2001 |
Hoffman |
London |
100 |
1001 |
|
2002 |
Giovanni |
Rome |
200 |
1003 |
|
2003 |
Liu |
San Jose |
200 |
1002 |
|
2004 |
Grass |
Berlin |
300 |
1002 |
|
2006 |
Clemens |
London |
100 |
1001 |
|
2008 |
Cisneros |
San Jose |
300 |
1007 |
|
2007 |
Pereira |
Rome |
100 |
1004 |
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 |
*
?
**
*
( )
**
* *
SQL
*SQL:
*SQL?
*ANSI?
*SQL
*SQL
**
SQL
*?
** *
SQL
*SQL
**
?
*?
*SELECT
**
SELECT
**
*
* DISTINCT *
ALL
DISTINCT ** *
SQL
**
*
* *
SQL
**
IN
*BETWEEN
*LIKE
*(NULL)
*NULL
*NOT
* *SQL
**
?
*?
*COUNT
*DISTINCT
*COUNT ,
**
,
*GROUP BY
*HAVING
** *
SQL
**
*
*
*
*
*
*
*
*
NULL
* *SQL
*,
**
*
*
*
*
* *
SQL
**
?
* **
*
*
* *
SQL
**
?
*,
*DISTINCT
**
*
, IN
**
*
HAVING
* *SQL
**
*
*
*
*
HAVING
** *
SQL
*EXISTS
*EXISTS?
*EXISTS
*EXISTS
*EXISTS
*NOT EXISTS
*EXISTS
** *
SQL
*ANY, ALL SOME
*ANY SOME
*IN EXISTS ANY
*ANY
*ALL
**
ANY ALL
*ANY, ALL, EXIST
**
ANY ALL EXISTS (NULL)
*COUNT EXISTS
* *SQL
*UNION
**
?
*UNIO
N *UNION
*UNION ORDER BY
** *
SQL
*,
*DML
**
(NULL)
*(INSERT)
**
*
*
UPDATE
**
(NULL)
* *SQL
**
INSERT
**
,
*DELETE
*UPDATE
*DML
* *SQL
**
* *
*
*
,
** *
SQL
**
*
*
(NULL)
*,
**
*
*
*
(
CHECK) *CHECK,
*,
** *
SQL
**
*
*
* FOREIGN KEY *
*
*
*
*
*
*
*
,
**
*
,
* *SQL
**
?
*CREATE VIEW
**
*
*
*
*
*
*
* *
SQL
**
*
*
" "
**
,
**
,
* *SQL
**
*
*
GRANT
*,
**
ALL PUBLIC
*WITH GRANT OPTION
**
*
?
*SELECT
**
*
*
*
*
* *
SQL
*SQL
**
*
*
*
?
*?
*SQL
**
* *
SQL
*SQL
**
*
*
()
**
*
* *
SQL
*SQL ( SQL)
*SQL
*SQL?
*SQL
*SQL
**
* *
SQLCODE
*SQLCODE
*WHENEVER
**
INDICATOR
*INDICATOR NULL SQL
*INDICATOR
* *SQL *
*
1
*2
*3
*4
*5
*6
*7
*8
*9
*10
*11
*12
*13
*14
*15
*16
*17
*18
*19
*20
*21
*22
*23
*24
*25
*SQL
*ANSI
**
SQL
**
FORMAT
* *INTERSECT MINUS
**
*
*
SQL
*SQL
*, SQL
* | 10.11.2021 - 12:37: - Personalias -> WHO IS WHO - - _. 10.11.2021 - 12:36: - Conscience -> . ? - _. 10.11.2021 - 12:36: , , - Upbringing, Inlightening, Education -> ... - _. 10.11.2021 - 12:35: - Ecology -> - _. 10.11.2021 - 12:34: , - War, Politics and Science -> - _. 10.11.2021 - 12:34: , - War, Politics and Science -> . - _. 10.11.2021 - 12:34: , , - Upbringing, Inlightening, Education -> , - _. 10.11.2021 - 09:18: - New Technologies -> , 5G- - _. 10.11.2021 - 09:18: - Ecology -> - _. 10.11.2021 - 09:16: - Ecology -> - _. 10.11.2021 - 09:15: , , - Upbringing, Inlightening, Education -> - _. 10.11.2021 - 09:13: , , - Upbringing, Inlightening, Education -> - _. |
|
|
 |