, . , 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 , , , , .
SQL
1. SELECT, , , .
2. , , = 1001.
3. , : city, sname, snum, comm.
4. SELECT, (rating), San Jose.
5. , snum .
(. A .)
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 Crass Berlin 300 1002 |
| 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 100 1004 |
===============================================
4.4. SELECT, NOT
, Grass, . Grass San Jose, , 200, . . , NOT , , . , :
rating NOT > 200
. SQL ?
SELECT *
FROM Customers
WHERE NOT city = 'San Jose' OR rating > 200;
NOT city = '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 1713.23 10/04/1990 2002 1003 |
| 3007 75.75 10/04/1990 2004 1002 |
| 3010 1309.95 10/06/1990 2004 1002 |
=================================================
4.6. ()
. 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.
SQL
1. , $1,000.
2. , sname city .10.
3. =< 100, .
4. ?
SELECT *
FROM Orders
WHERE (amt < 1000 OR NOT (odate = 10/03/1990 AND cnum > 2003));
5. ?
SELECT *
FROM Orders
WHERE NOT ((odate = 10/03/1990 OR snum > 1006) AND amt > = 1500);
6. ?
SELECT snum, sname, city, comm
FROM Salespeople
WHERE (comm > +.12 OR comm <.14);
(. A .)