SQL  

SQL

( .., ..)

SQL

  2. ?
  3. ?
  4. SELECT
  6. SELECT
  10. DISTINCT
  11. ALL DISTINCT
  14. SQL

  19. SQL
  21. IN
  22. BETWEEN
  23. LIKE
  24. (NULL)
  25. NULL
  26. NOT
  28. SQL

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

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

?

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

?

DML. , , , DML, , .

SQL . , , . SELECT ().

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
Salespeople

, SELECT, . . (Salespeople).

;

SQL, , . (\) , .

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

( ENTER) . , , , :

SELECT snum, sname, city, comm FROM Salespeople;

SQL , , SQL ( ENTER) . SQL SQL.

, , . (*) :

SELECT *
FROM Salespeople;

, .

SELECT

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

SELECT . , . , , , SELECT. ,

SELECT sname, comm
FROM Salespeople;

, 3.2.

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

3.2:

, , , . , .

, , , , . , "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 3007 75.75 |
| 10/05/1990 1001 3008 4723.00 |
| 10/06/1990 1002 3010 1309.95 |
| 10/06/1990 1001 3011 9891.88 |
=================================================

3.3:

, SQL.

DISTINCT () , SELECT. , . ( ) , , . , ; (snum). :

SELECT snum
FROM Orders;

, 3.4.

=============== SQL Execution Log ============
| SELECT snum |
| FROM Orders; |
| ============================================= |
| snum |
| ------- |
| 1007 |
| 1001 |
| 1004 |
| 1002 |
| 1007 |
| 1003 |
| 1002 |
| 1001 |
| 1002 |
| 1001 |
===============================================

3.4: SELECT .

, , :

SELECT DISTINCT snum
FROM Orders;

3.5.

, DISTINCT , , , . , , . , DISTINCT, - - . , , . - Clemens , SELECT DISTINCT cname, . Clemens . , DISTINCT.

DISTINCT

DISTINCT SELECT. , 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 , , , , .

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. ?

    5. SELECT *
    FROM Orders
    WHERE (amt < 1000 OR NOT (odate = 10/03/1990 AND cnum > 2003));

  5. ?

    6. 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 .)

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

IN

IN , . , , , Barcelona London, ( 5.1):

SELECT *
FROM Salespeople
WHERE city = 'Barcelona' OR city = 'London';

================ SQL Execution Log ============
| SELECT * |
| FROM Salespeople |
| WHERE city = 'Barcelona' |
| OR city = 'London'; |
| ==============================================|
| snum sname city comm |
| ------ ---------- ----------- ------- |
| 1001 Peel London 0.12 |
| 1004 Motika London 0.11 |
| 1007 Rifkin Barcelona 0.15 |
===============================================

5.1.

:

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

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 0.11 |
| 1003 Axelrod New York 0.10 |
===============================================

5.4: SELECT BETWEEN

SQL BETWEEN. , , - :

SELECT *
FROM Salespeople
WHERE (comm BETWEEN .10, AND .12) AND NOT comm IN (.10, .12);

5.5.

=============== 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 1007 |
===============================================

5.6. BETWEEN .

, Grass Giovanni . - , BETWEEN . 'G' , 'Giovanni', BETWEEN 'G' . ( ), 'Giovanni' . Grass. , BETWEEN . ( z).

LIKE

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 (//), (%).

(NULL)

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

, , . , , , . NULL snum , .

NULL

NULL , , NULL. NULL , NULL, , , . , , , , , NOT () , NOT () . , 'city = NULL' 'city IN (NULL)' , city. "" "" , , NULL . , SQL IS, NULL, NULL. NULL city:

SELECT *
FROM Customers
WHERE city IS NULL;

, NULL . NULL , .

NOT

, , NOT.

, NOT . , NULL , NOT, :

SELECT *
FROM Customers
WHERE city NOT NULL;

NULL ( ), . :

SELECT *
FROM Customers
WHERE NOT city IS NULL;

.

NOT IN:

SELECT *
FROM Salespeople
WHERE city NOT IN ('London', 'San Jose');

:

SELECT *
FROM Salespeople
WHERE NOT city IN ('London', 'San Jose');

5.9.

=============== 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.

SQL

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

(. A .)

    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 -> - _.
Bourabai Research - XXI Bourabai Research Institution