.


:

:



,



( , )

:

































 

 

 

 
FAQ

select WORK_TYPE, count(distinct A_2.WORK_NAME) from A_2 group by WORK_TYPE

:

select distinct A_2.WORK_TYPE from A_1,A_2 where A_1.FIO=A_2.FIO and A_1.FIO=''

: ''

select count(distinct A_2.WORK_NAME) from A_2 where A_2.WORK_TYPE =''

,

select distinct A_2.FIO from A_2 where A_2.WORK_TYPE =''

: , 5 .

select FACKULTET from A_1, A_2, A_4 where A_1.FIO=A_2.FIO and A_1.GROUP_NUM=A_4.GROUP_NUM group by FACKULTET having count(distinct WORK_NAME)=5

, 32

SELECT FACKULTET FROM A_4 WHERE A_4.GROUP_NUM='325'

\ , 1 .

select GROUP_NUM from A_1 group by GROUP_NUM having count(FIO)=1

: , 4909

select count(*) from A_1 where A_1.GROUP_NUM ='4909'

:

select A_1.GROUP_NUM from A_1 where A_1.FIO =''

select group num from a1 a2 where a1.fio=a2.fio group by group numhavingcount (distinct work type)=2

: ''

select WORK_NAME from A_2 where FIO<>''

: , .

select distinct GROUP_NUM from A_1,A_2 where A_1.FIO=A_2.FIO and A_2.FIO in (select a.FIO from A_2 a,A_2 b where a.FIO=b.FIO and a.WORK_TYPE<>b.WORK_TYPE)

in a4 not in a1

: , 20

select WORK_TYPE from A_3 where POINTS_QUANTITY>20

, ( '').

SELECT FACKULTET FROM A_4,A_1,A_2 WHERE A_4.GROUP_NUM=A_1.GROUP_NUM AND A_1.FIO=A_2.FIO AND A_2.WORK_TYPE='' GROUP BY FACKULTET

: ,

select GROUP_NUM from A_1 group by GROUP_NUM having count(FIO)<= all(select count(FIO) from A_1 group by GROUP_NUM)

: ,

select distinct a.FIO from A_2 a,A_2 b where a.FIO=b.FIO and a.WORK_NAME<>b.WORK_NAME

: , .

select distinct a.FIO from A_2 a,A_2 b where a.WORK_NAME=b.WORK_NAME and a.FIO<>b.FIO

: , ( ) , -

select distinct a.FIO from A_2 a,A_2 b where a.FIO=b.FIO and a.WORK_NAME<>b.WORK_NAME and a.WORK_TYPE=b.WORK_TYPE and a.WORK_TYPE=''

: , ().

select WORK_NAME from A_2 where WORK_NAME not in (select a.WORK_NAME from A_2 a, A_2 b where a.WORK_NAME=b.WORK_NAME and a.FIO<>b.FIO)

, 4909

select count(*) from A_1, A_2 where A_2.FIO=A_1.FIO and A_1.GROUP_NUM=4909

: , 4909

select distinct WORK_TYPE from A_1, A_2 where A_2.FIO=A_1.FIO and A_1.GROUP_NUM=4909

, ''.

select distinct A_1.GROUP_NUM from A_1, A_2 a, A_2 b where a.WORK_TYPE=b.WORK_TYPE and a.WORK_TYPE='' and a.WORK_NAME<>b.WORK_NAME and a.FIO=A_1.FIO

, () . , , , () .

select distinct A_1.GROUP_NUM from A_1, A_2 where A_2.FIO=A_1.FIO group by GROUP_NUM having count(distinct WORK_TYPE)>1

, 1 , , , , .

select distinct A_1.GROUP_NUM from A_1, A_2 where A_2.FIO=A_1.FIO group by GROUP_NUM having count(distinct WORK_TYPE)=1

,

select distinct a.FIO from B_4 a, B_4 b where a.FIO=b.FIO and a.WORK_TYPE=b.WORK_TYPE and a.WORK_TYPE='' and a.WORK_NAME<>b.WORK_NAME

,

select FACKULTET from A_4 where FACKULTET not in (select FACKULTET from A_2, A_1, A_4 where A_1.GROUP_NUM=A_4.GROUP_NUM and A_1.FIO= A_2.FIO)

,

SELECT GROUP_NUM FROM A_1 GROUP BY GROUP_NUM HAVING COUNT(*)=2

, 1 .

SELECT GROUP_NUM FROM A_1 GROUP BY GROUP_NUM HAVING COUNT(*)=1

,

SELECT GROUP_NUM FROM A_1 WHERE A_1.FIO=""

, ( )

SELECT WORK_NAME FROM A_2 WHERE A_2.FIO="" OR A_2.FIO=""

, 325

SELECT FACKULTET FROM A_4 WHERE A_4.GROUP_NUM='325'

, ( '').

SELECT FACKULTET FROM A_4,A_1,A_2 WHERE A_4.GROUP_NUM=A_1.GROUP_NUM AND A_1.FIO=A_2.FIO AND A_2.WORK_TYPE='' GROUP BY FACKULTET

, 4909

SELECT COUNT(*) FIO FROM A_1 WHERE A_1.GROUP_NUM="4909"

,

SELECT GROUP_NUM FROM A_1,A_2 WHERE A_1.FIO=A_2.FIO GROUP BY GROUP_NUM HAVING COUNT(DISTINCT WORK_TYPE)=2

,

SELECT FIO FROM A_2 GROUP BY FIO HAVING COUNT(*)>1

: (=4)

select count(*) from A_4 where A_4.FACKULTET ='4'

: ,

Select group_num from a4 where group_num not in (select group_num from a1)

: '' ''

select WORK_NAME from A_2 where WORK_TYPE = '' or WORK_TYPE = ''

, ( '').
SELECT FACKULTET FROM A_4,A_1,A_2 WHERE A_4.GROUP_NUM=A_1.GROUP_NUM AND A_1.FIO=A_2.FIO AND A_2.WORK_TYPE='' GROUP BY FACKULTET

( '') .

select GROUP_NUM, COUNT(WORK_NAME) from A_1, A_2 where A_1.FIO = A_2.FIO and WORK_TYPE ='' group by GROUP_NUM

: , ,

select distinct GROUP_NUM from A_1 where FIO not in (select distinct FIO from A_2)

: , 4- .

select distinct WORK_NAME from A_2 where WORK_NAME not in (select WORK_NAME from A_2, A_1, A_4 where A_2.FIO=A_1.FIO and A_1.GROUP_NUM=A_4.GROUP_NUM and FACKULTET<>4)

:

, . , .

select FACKULTET, count(distinct FIO) from A_4, A_1 where A_4.GROUP_NUM=A_1.GROUP_NUM and FIO in (select FIO from A_2) group by FACKULTET

 

, . B_3 . B_2 . : . . . .

B_1
FIO ( )
GROUP_NUM ( )
COURSE ()

B_2
GROUP_NUM ( )
KAFEDRA ()

B_3
KAFEDRA ()
FACKULTET ()

B_4
WORK_NAME ( )
FIO ( )
WORK_TYPE ( )
KAFEDRA ()

B_5
WORK_TYPE ( )
POINTS_QUANTITY ( )

: 3

select distinct B_1.FIO from B_1 where B_1.GROUP_NUM='3'

,

SELECT FIO FROM B_4 WHERE B_4.WORK_TYPE="" GROUP BY FIO

: ,

select GROUP_NUM from B_1, B_4 where B_1.FIO = B_4.FIO and WORK_TYPE = '' group by GROUP_NUM having COUNT(*) > 1

: ,

select FIO from B_4 group by FIO having COUNT(distinct WORK_TYPE) > 1 and COUNT(WORK_NAME) >1

, .

SELECT FIO FROM B_4 GROUP BY FIO HAVING COUNT(*)=1

: , ( ).

select distinct a.FIO from A_2 a,A_2 b where a.FIO=b.FIO and a.WORK_NAME<>b.WORK_NAME

: 4

SELECT KAFEDRA FROM B_2 WHERE GROUP_NUM='4'

,

SELECT FACKULTET FROM B_3,B_4 WHERE B_3.KAFEDRA=B_4.KAFEDRA GROUP BY FACKULTET HAVING COUNT(*)>3

,

SELECT FIO FROM B_4 WHERE B_4.WORK_TYPE="" GROUP BY FIO

SELECT FIO FROM B_4 WHERE B_4.WORK_TYPE="" GROUP BY FIO HAVING COUNT(*)>1

B_2

SELECT * FROM B_2

,

SELECT FACKULTET FROM B_3,B_4 WHERE B_3.KAFEDRA=B_4.KAFEDRA GROUP BY FACKULTET HAVING COUNT(*)>3

, 2 1. , .

SELECT DISTINCT FIO FROM B_4 WHERE B_4.WORK_NAME='2' OR B_4.WORK_NAME='1'

: ,

select distinct B_1.FIO from B_1,B_4 where B_1.FIO =B_4.FIO and B_1.COURSE='1'

B_2

select * from B_2

,

select FIO from B_4 where B_4.WORK_TYPE='' group by FIO having count(distinct WORK_NAME)>1

:

select distinct B_1.GROUP_NUM from B_1 where B_1.COURSE ='1'

select distinct fio from b4 where b4.work name=mars or b4.workname=poisk

, 1

select distinct GROUP_NUM from B_1,B_4 where B_1.FIO=B_4.FIO and B_4.WORK_NAME='1'

: , . , .

select GROUP_NUM from B_1,B_4 where B_1.FIO =B_4.FIO and B_4.WORK_TYPE='' group by GROUP_NUM having count (distinct WORK_NAME)=1

: , , .

select distinct a.FIO from B_4 a, B_4 b where a.FIO=b.FIO and a.WORK_TYPE=b.WORK_TYPE and a.WORK_NAME<>b.WORK_NAME

,

SELECT WORK_NAME FROM B_4 WHERE B_4.FIO=""

: , 1

select WORK_TYPE from B_1,B_4 where B_1.FIO=B_4.FIO and B_1.GROUP_NUM=1

: ,

selecta.FIO from B_4 a,B_4 b where a.WORK_NAME=b.WORK_NAME and a.FIO<>b.FIO

: , . ( )

select FACKULTET from B_3 where B_3.FACKULTET not in (select FACKULTET from B_3,B_4 where B_3.KAFEDRA=B_4.KAFEDRA)

: ,

select KAFEDRA from B_3 where B_3.KAFEDRA not in (select KAFEDRA from B_4)

: , .

select distinct FIO from B_4 where FIO not in (select a.FIO from B_4 a,B_4 b where a.FIO=b.FIO and a.WORK_NAME<>b.WORK_NAME)

, , .

select WORK_NAME from B_4 where B_4.FIO = B_1.FIO group by WORK_NAME having COUNT(FIO) > 1 and COUNT(distinct GROUP_NUM) = 1

: , 1

select distinct GROUP_NUM from B_1,B_4 where B_1.FIO=B_4.FIO and B_4.WORK_NAME='1'

: , 1

select WORK_TYPE from B_1,B_4 where B_1.FIO=B_4.FIO and B_1.GROUP_NUM=1

: , .

select distinct FIO from B_4 where FIO not in (select a.FIO from B_4 a,B_4 b where a.FIO=b.FIO and a.WORK_NAME<>b.WORK_NAME)

: ,

select distinct B_1.FIO from B_1,B_4 where B_1.FIO =B_4.FIO and B_1.COURSE='1'

:

select distinct B_1.GROUP_NUM from B_1 where B_1.COURSE ='1'

 

. C_1 . . C_2 , , . C_3 , . C_4 , .. . , C_4.

C_1
FIO ( )
N_GROUP ( )
N_ZACH ( )

C_2
KOD ()
SPECIALITY ()
N_GROUP ( )

C_3
KOD ()
DISCIPLIN ()

C_4
N_ZACH ( )
DISCIPLINE ()
DATA_EX ( )
MARK ()

: C_1

select * from C_1

,

select distinct N_ZACH from C_4 where MARK=3

:

, .

select FIO, count(distinct DISCIPLINE) from C_1, C_4 where C_1.N_ZACH=C_4.N_ZACH and MARK>=3 group by C_1.N_ZACH, FIO

, c

SELECT DISCIPLINE FROM C_4 WHERE C_4.MARK=2 GROUP BY DISCIPLINE

: , . ( , -)

select C_1.FIO

from C_1

where C_1.N_ZACH NOT IN (select C_4.N_ZACH

from C_4 where Mark > 2)

: , , .

select distinct FIO from C_1, C_4 where C_1.N_ZACH = C_4.N_ZACH and MARK = 5 and C_4.N_ZACH not in (select N_ZACH from C_4 where MARK <> 5)

: , - 3 4, 5

select distinct FIO from C_1, C_4 where C_1.N_ZACH = C_4.N_ZACH and MARK in (3,4) and C_4.N_ZACH not in (select N_ZACH from C_4 where MARK=5)

:

select fio,n/zach,count(*) from c1,c4 where c1.nzach=c4.nzach and c4.mark>2 group by fio,nzach

 

, . ( , , - )

SELECT FIO FROM C_1,C_4 WHERE C_1.N_ZACH=C_4.N_ZACH AND C_4.MARK=5 AND FIO NOT IN (SELECT FIO FROM C_1,C_4 WHERE C_1.N_ZACH=C_4.N_ZACH AND C_4.MARK<>5)

: , .

select distinct C_2.SPECIALITY from C_2

,

SELECT SPECIALITY FROM C_2 GROUP BY SPECIALITY HAVING COUNT(*)=1

, 0012

SELECT FIO FROM C_1 WHERE C_1.N_ZACH='0012'

SELECT FIO FROM C_1 GROUP BY FIO HAVING COUNT(*)>1

: ,

select distinct C_4.DISCIPLINE from C_4

: 4907,

select C_1.FIO from C_1 where C_1.N_GROUP ='4907' and C_1.N_ZACH not in (select C_1.N_ZACH from C_1, C_4 where C_1.N_ZACH=C_4.N_ZACH)

: .

select C_1.N_ZACH, FIO, count(distinct DISCIPLINE) from C_1, C_4 where C_1.N_ZACH=C_4.N_ZACH and MARK>=3 group by C_1.N_ZACH, FIO

: ,

select distinct a.SPECIALITY from C_2 a,C_2 b where a.SPECIALITY=b.SPECIALITY and a.N_GROUP<>b.N_GROUP

: ,

select distinct a.DISCIPLIN from C_3 a, C_3 b where a.KOD<>b.KOD and a.DISCIPLIN =b.DISCIPLIN

3 4 2

select FIO from C_1,C_4 a, C_4 b where C_1.N_ZACH= a.N_ZACH and a.N_ZACH=b.N_ZACH and a.MARK=3 and b.MARK=4

, . ( , 4)

select FIO from C_1,C_4 a, C_4 b where C_1.N_ZACH= a.N_ZACH and a.N_ZACH=b.N_ZACH and a.MARK=3 and b.MARK=4 group by FIO having count(*)=1

, , .

select FIO from C_1,C_4 a, C_4 b where C_1.N_ZACH= a.N_ZACH and a.N_ZACH=b.N_ZACH and a.MARK=5 and b.MARK<>5

: , c

select distinct DISCIPLINE from C_4 where MARK=2

: 4908 8021.

select count(*) from C_2 where C_2.N_GROUP='4908' or C_2.N_GROUP='8021'

: , .

select N_GROUP, count(*) from C_1 group by N_GROUP

: , 4908

select count(*) from C_1 where N_GROUP=4908

: e , .

select distinct a.DISCIPLINE from C_4 a, C_4 b where a.N_ZACH=b.N_ZACH and a.DATA_EX<>b.DATA_EX and b.MARK>=3 and a.DISCIPLINE=b.DISCIPLINE

: ,

select distinct DISCIPLINE from C_4 where DISCIPLINE not in (select DISCIPLINE from C_4 where MARK=2)

: , ''

select C_1.FIO from C_1, C_2, C_3 where C_1.N_GROUP = C_2.N_GROUP and C_2.KOD=C_3.KOD and C_3.DISCIPLIN=''

,

select distinct a.DISCIPLIN from C_3 a, C_3 b where a.KOD<>b.KOD and a.DISCIPLIN =b.DISCIPLIN

: ,

select distinct a.SPECIALITY from C_2 a,C_2 b where a.SPECIALITY=b.SPECIALITY and a.N_GROUP<>b.N_GROUP

: , .

select N_GROUP, count(*) from C_1 group by N_GROUP

: e , .

select distinct a.DISCIPLINE from C_4 a, C_4 b where a.N_ZACH=b.N_ZACH and a.DATA_EX<>b.DATA_EX and b.MARK>=3 and a.DISCIPLINE=b.DISCIPLINE

: ,

select distinct DISCIPLINE from C_4 where DISCIPLINE not in (select DISCIPLINE from C_4 where MARK=2)

: 4907,

select C_1.FIO from C_1 where C_1.N_GROUP ='4907' and C_1.N_ZACH not in (select C_1.N_ZACH from C_1, C_4 where C_1.N_ZACH=C_4.N_ZACH)

: ,

select distinct a.DISCIPLIN from C_3 a, C_3 b where a.KOD<>b.KOD and a.DISCIPLIN =b.DISCIPLIN

: , .

select distinct DISCIPLIN from C_2, C_3 where C_2.KOD=C_3.KOD group by DISCIPLIN having count(distinct SPECIALITY)=4

: , , ( )

select N_GROUP from C_1 where C_1.N_ZACH not in (select C_1.N_ZACH from C_1, C_4 where C_1.N_ZACH=C_4.N_ZACH)

 

. , , D_1. D_2. . . D_3. , , . . 2 '' '' . SUM_Oper. , - 4 .

D_1
N_FILIAL ( )
CITY ()

D_2
ID_CLIENT ( )
NAME_CLIENT ( )
CITY ()

D_3
BILL ( )
ID ( )
FILIAL ( )
DATA_B ( )
DATA_E ( )

D_4
N_OPERATION ( )
OPERATION ()
BILL ( )
DATE_OPER ( )
SUM_OPER ( )

: ,

Select distinct D_2. CITY, count(*) from D_2 group by D_2. CITY

 

: , , .

select CITY from D_1 where CITY not in (select CITY from D_2)

 

: , .

select

BILL from D_4 where OPERATION ='' group by BILL having

sum(SUM_OPER) >= all (select sum(SUM_OPER) from D_4 where OPERATION

='' group by BILL)

:

select bill from d3 where data_b<12.10.2012

:

Select bill from d3 where bill not in(select bill from d4)

 

, , .

select BILL from D_4 where OPERATION='' and BILL not in(select BILL from D_4 where OPERATION='')

group by BILL

,

select CITY from D_1 group by CITY having count(N_FILIAL)=1

: ,

select D_1.N_FILIAL from D_1 where D_1.CITY=''

,

SELECT CITY FROM D_1 GROUP BY CITY HAVING COUNT(*)=1

: , ,

select BILL from D_4 where OPERATION='' group by BILL having count(*)=(select count(*) from D_4 a where OPERATION='' and D_4.BILL=a.BILL group by BILL)

] , .

select BILL from D_4 where OPERATION='' group by BILL having count(*)>(select count (*) from D_4 d44 where OPERATION='' and D_4. BILL=d44. BILL)

: ,

select distinct D_1.CITY from D_1

:

Select bill from d3 where bill not in(select bill from d4)

: ,

select distinct CITY from D_2

:

: 1001

select sum(SUM_OPER) from D_4 where BILL=1001 and OPERATION =''

: . , .

select BILL, count(*) from D_4 where OPERATION ='' group by BILL

: , 2

select BILL from D_4 where OPERATION = '' group by BILL having SUM()= 2 * (select SUM_OPER from D_4 where OPERATION = '')

: , 100000 .

select distinct BILL from D_4 where OPERATION='' and SUM_OPER>=100000

, 20000 .

select distinct BILL from D_4 where OPERATION='' group by BILL having sum(SUM_OPER)>20000

, .

select NAME_CLIENT from D_2 where D_2.CITY=''

: ,

select BILL from D_4 group by BILL having count(OPERATION)>2

. ''

select BILL from D_4 where OPERATION='' group by BILL having count(OPERATION)>0

: , .

select BILL from D_4 where OPERATION ='' group by BILL having sum(SUM_OPER) >= all (select sum(SUM_OPER) from D_4 where OPERATION ='' group by BILL)

.

select BILL from D_3 where DATA_E is null

2005

data_B<= 01.01.2005 and dataB>=31/12/2005

, 5 2004 (.. )

select BILL from D_3 where DATA_E<'05.05.2004'

: , , .

select CITY from D_1 where CITY not in (select CITY from D_2)

, .

Select distinct FILIAL, count(*) from D_3 group by FILIAL

, .

select N_FILIAL from D_1 where N_FILIAL not in D_3

:

, .

select NAME_CLIENT from D_2,D_3 where D_2.ID_CLIENT = D_3.ID group by NAME_CLIENT having COUNT(*) >= ALL(select COUNT(BILL) from D_3 group by ID)

: , .

select BILL from D_4 where OPERATION ='' group by BILL having count(*) >= all (select count(*) from D_4 where OPERATION='' group by BILL)

:

,

select N_OPERATION from D_4 where year(DATE_OPER)=2012

 

 

<== | ==>
|
:

: 2017-01-21; !; : 308 |

:

:

,
==> ...

1898 - | 1729 -


© 2015-2024 lektsii.org - -

: 0.149 .