( .., ..)
, . , . , (), , . , 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
, , , . , .
, , , , . , "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
, 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
, , :
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
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 |
=======================
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;
, , . . , .
, , , , .
, , . , , SQL .
, , , . 4 , , , , .
(. 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 Cr
, , 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; |
| ======================
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; |
| ==
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
, 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.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
. 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 .)
, 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
:
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 |
=================
, 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 |
=========
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
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 |
==========
, , , , . , 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
, 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 |
======================================
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 |
=============================
, ? 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 |
=============================
NOT BETWEEN NOT LIKE.
SQL. (BETWEEN) (IN), (LIKE).
, SQL NULL . NULL , IS NULL ( NOT NULL).
, , SQL, , , . 6.
(. A .)
![]() | 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 -> - _. |
|
![]() |