.
, . :
SELECT ( ) FROM ( ) WHERE ( );
:
Select * from abon;
ABON. , ,
Select * from abon where balance < 0;
, :
N | UP | FIRSTNAME | LASTNAME | NOB_NUM | TARIF | BALANCE | FD | TD |
Alexander | Smirnov | -5 | 01.07.1992 | 01.01.9999 | ||||
Marina | Naumova | -89 | 03.02.1990 | 01.01.2009 | ||||
Elena | Zydkina | -9 | 04.08.2008 | 01.01.2013 |
, . : , () AND. , OR:
Select * from abon where balance < 0 and up = 6;
N | UP | FIRSTNAME | LASTNAME | NOB_NUM | TARIF | BALANCE | FD | TD |
Alexander | Smirnov | -5 | 01.07.1992 | 01.01.9999 |
, , , , up = 6.
Select * from abon where balance < 0 or up = 12;
:
N | UP | FIRSTNAME | LASTNAME | NOB_NUM | TARIF | BALANCE | FD | TD |
Ivan | Ivanov | 01.01.2000 | 01.01.9999 | |||||
Alexander | Smirnov | -5 | 01.07.1992 | 01.01.9999 | ||||
Marina | Naumova | -89 | 03.02.1990 | 01.01.2009 | ||||
Elena | Zydkina | -9 | 04.08.2008 | 01.01.2013 |
, , up 12, , up = 12, .
, select * from..., . , :
Select n from abon; - . :
Select n,up,nob_num from abon where balance < 0;
N | UP | NOB_NUM |
, , :
COUNT -
SUM -
AVG -
MIN -
MAX -
Select count(n) from abon where balance < 0; - .
|
|
Select sum(balance) from abon; - .
Select avg(balance) from abon; -
Select min(balance) from abon; -
Select max(balance) from abon; -
, :
To_date .
Select * from abon where fd = to_date (01.01.2000, dd.mm.yyyy);
:
N | UP | FIRSTNAME | LASTNAME | NOB_NUM | TARIF | BALANCE | FD | TD |
Ivan | Ivanov | 01.01.2000 | 01.01.9999 |
Between . , AND:
Select * from abon where balance between 40 and 100;
, 40 100:
N | UP | FIRSTNAME | LASTNAME | NOB_NUM | TARIF | BALANCE | FD | TD |
Ivan | Ivanov | 01.01.2000 | 01.01.9999 | |||||
Elena | Shlyapina | 04.01.2008 | 01.01.2009 | |||||
Stepan | Shishov | 04.12.2000 | 01.01.9999 | |||||
Ivan | Syrkov | 01.01.2001 | 01.04.2010 | |||||
Petr | Legkix | 02.11.2009 | 01.01.9999 | |||||
Marina | Stolova | 03.12.1995 | 01.01.9999 |
-:
Select * from abon where balance > 40 and balance < 100;
:
Select * from abon where sysdate between fd and td;
, :
Select * from abon - abon where sysdate between fd and td;
Sysdate , .
,
Select * from abon where sysdate between fd and td;
, sysdate , , fd td. , abon :
Elena | Shlyapina | 04.01.2008 | 01.01.2009 | |||||
Elena | Shlyapina | 02.01.2009 | 01.01.9999 |
. n up . , , , , . , . , 2 , . ,
Select * from abon where sysdate between fd and td;
. , , not:
Select * from abon where sysdate not between fd and td;
-. J
IN. , . OR:
Select * from abon where up in (1,3,5,9);
, ,
Select * from abon where up = 1 or up = 3 or up = 5 or up = 9;
, . order by :
|
|
Select * from abon where balance between 40 and 100 order by up;
N | UP | FIRSTNAME | LASTNAME | NOB_NUM | TARIF | BALANCE | FD | TD |
Stepan | Shishov | 04.12.2000 | 01.01.9999 | |||||
Elena | Shlyapina | 04.01.2008 | 01.01.2009 | |||||
Ivan | Syrkov | 01.01.2001 | 01.04.2010 | |||||
Ivan | Ivanov | 01.01.2000 | 01.01.9999 | |||||
Petr | Legkix | 02.11.2009 | 01.01.9999 | |||||
Marina | Stolova | 03.12.1995 | 01.01.9999 |
up. :
Select * from abon where balance between 40 and 100 order by up desc;
N | UP | FIRSTNAME | LASTNAME | NOB_NUM | TARIF | BALANCE | FD | TD |
Marina | Stolova | 03.12.1995 | 01.01.9999 | |||||
Petr | Legkix | 02.11.2009 | 01.01.9999 | |||||
Ivan | Ivanov | 01.01.2000 | 01.01.9999 | |||||
Ivan | Syrkov | 01.01.2001 | 01.04.2010 | |||||
Elena | Shlyapina | 04.01.2008 | 01.01.2009 | |||||
Stepan | Shishov | 04.12.2000 | 01.01.9999 |
up.
:
, . , , . %, like:
Select * from abon where lastname like S%;
N | UP | FIRSTNAME | LASTNAME | NOB_NUM | TARIF | BALANCE | FD | TD |
Alexander | Smirnov | -5 | 01.07.1992 | 01.01.9999 | ||||
Elena | Shlyapina | 04.01.2008 | 01.01.2009 | |||||
Elena | Shlyapina | 02.01.2009 | 01.01.9999 | |||||
Stepan | Shishov | 04.12.2000 | 01.01.9999 | |||||
Ivan | Syrkov | 01.01.2001 | 01.04.2010 | |||||
Ivan | Syrkov | 02.04.2010 | 01.01.9999 | |||||
Marina | Stolova | 03.12.1995 | 01.01.9999 |
.
( ):
, . , - :
, 300 .
, TARIF. , , . , > 300 , , :
select * from abon where tarif in (select n from tarif where amount > 300);
, :
1. 300: select n from tarif where amount > 300. 3 8.
2. ( IN). IN , select n from tarif where amount > 300, .. :
select * from abon where tarif in (3,8);
:
N | UP | FIRSTNAME | LASTNAME | NOB_NUM | TARIF | BALANCE | FD | TD |
Anna | Molkina | 04.05.2004 | 01.01.9999 | |||||
Ivan | Syrkov | 01.01.2001 | 01.04.2010 | |||||
Elena | Kolovasheva | 28.01.2005 | 01.01.2007 | |||||
Ivan | Ivanov | 01.01.2000 | 01.01.9999 |
8 , .
|
|
, .
.
:
UPDATE SET = WHERE
,
Update abon set balance = 100500 where up = 12;
balance 100500. , up = 12.
SELECT ( ).
, , .
COMMIT;
ROLLBACK;
.
, . , for update:
Select * from abon where up = 12 for update;
commit/rollback.