Материал: ответы на задачи к экзамену бд

select gr.groupNumber from studentGroup gr

where faculty = 'ief' and 0 != (select count(*) from ex

inner join student on ex.za4otka = student.za4otka

where predmet = 'теория систем' and ocenka < 4 and groupNumber = gr.groupNumber

)

declare @average as int

set @average = (select AVG(student.godPostupleniya - student.godRozhdeniya) from studentGroup

inner join student on student.groupNumber = studentGroup.groupNumber where student.godPostupleniya > 2013)

select studentGroup.groupNumber from studentGroup

where (

select AVG(student.godPostupleniya) from student

where student.groupNumber = studentGroup.groupNumber AND student.godPostupleniya > 2013 ) > @average

order by (

select AVG(student.godPostupleniya) from student

where student.groupNumber = studentGroup.groupNumber AND student.godPostupleniya > 2013 ) desc

select p.groupNumber from studentGroup p

inner join student on student.groupNumber = p.groupNumber inner join ex on ex.za4otka = student.za4otka

where (p.faculty = 'ief' or p.faculty = 'ФКП' or p.faculty = 'ФИТУ') and 20 <= (

select count(*) from student

inner join ex on student.za4otka = ex.za4otka

inner join studentGroup on student.groupNumber = studentGroup.groupNumber where predmet = 'ff' and p.groupNumber = student.groupNumber and ex.ocenka >= 4 ) and

(

select AVG(ex.ocenka) from student

inner join ex on student.za4otka = ex.za4otka

inner join studentGroup on student.groupNumber = studentGroup.groupNumber where predmet = 'ff' and p.groupNumber = student.groupNumber

) between 7 and 9

select surname, student.za4otka from student

inner join ex on ex.za4otka = student.za4otka

inner join studentGroup on student.groupNumber = studentGroup.groupNumber where (student.groupNumber = 672301 OR student.groupNumber = 672302) and 0 != (

select count(*) from ex

where sem = 3 and za4otka = student.za4otka and ocenka = 4

)

and 0 != ( select count(*) from ex

where sem = 4 and za4otka = student.za4otka and ocenka = 4

)

order by student.za4otka desc

select AVG(gr.groupNumber) , AVG(ex.ocenka) from studentGroup gr

inner join student on student.groupNumber = gr.groupNumber inner join ex on ex.za4otka = student.za4otka

where 20 <= (select avg(ex.ocenka) from student

inner join ex on ex.za4otka = student.za4otka

where ex.predmet = '2' AND ex.ocenka >= 5 AND gr.groupNumber = groupNumber) AND (select avg(ex.ocenka)

from studentGroup

inner join student on student.groupNumber = gr.groupNumber inner join ex on ex.za4otka = student.za4otka

where studentGroup.groupNumber = gr.groupNumber) > 8 AND (faculty = 'ief' OR faculty = 'fcp')

group by gr.groupNumber