Part1.sql - master - erddrsf/Pyhse

Part1.sql
109 строк4 KB
erddrsf
Проект Sql
01 май 2026, 23:16
01 май 2026, 23:16a80bcad
-- 1.	Выбрать всех студентов из одной группы упорядочив по ФИО.
select name 
from students as s
join group_students as g
on s.id = g.student_id
where  group_id =1
order by name;
-- 2.	Упорядочить сданные кем-либо экзамены по числу сдавших.
select exam_id, 
count(student_id) as gg 
from exam_depts 
group by exam_id 
order by gg;
-- 3.	Каково число студентов, не получивших ни одного зачета?
select count (max) 
from 
	(select student_id, max(allow_to_pass::int) 
	from exam_depts
	group by student_id) as gg
where max=0;
-- 4.	Найти самую малочисленную группу
with gg as (select group_id,
			count(student_id) 
			from group_students
			group by group_id
			)

select group_id 
from 
	(select min(count) as count 
	from gg ) as zz 
join gg using(count);
-- 5.	Найти студента, сдавшего больше всех экзаменов
with gg as (select student_id,
	count(passed)
	from exam_depts 
	where passed=true
	group by student_id
	)

select student_id
from 
	(select max (count) as count
	from gg) as zz
join gg using(count);
-- 6.	Найти всех студентов, сдавших все обязательные экзамены с хотя бы одним несданным зачетом
select distinct student_id
from
	(select student_id,
	count (exam_id) 
	filter (where e.optional = false) 
	over (partition by student_id) as c1,
	count (exam_id) 
	filter (where e.optional = false and d.passed = true)
	over (partition by student_id) as c2,
	count (exam_id) 
	filter (where e.optional = true and d.passed = false) 
	over (partition by student_id) as c3
	from exam_depts as d 
	join exams as e on d.exam_id= e.id
	) as zz
where c1 = c2 and c3>0;
-- 7.	Найти группу с самой большой нагрузкой (числом зачетов и экзаменов).
with gg as
	(select group_id ,count(exam_id) from group_exams 
	group by group_id)
	
select group_id
from
	(select max (count) as count 
	from gg
	) as zz
join gg using (count);
-- 8.	Найти, сколько студентов не допущены (т.е., не получили необходимых зачетов) хотя бы к одному обязательному экзамену.
select count(*)
from
	(select distinct student_id,
	count (exam_id) 
	filter (where e.optional = false and d.passed = true)
	over (partition by student_id) as c2
	from exam_depts as d 
	join exams as e on d.exam_id= e.id)
where c2 >0;
-- 9.	Найти самый «сложный» экзамен (с максимальным процентом не сдавших). Полностью необязательные экзамены не рассматривать.
with gg as
	(select distinct exam_id,
	count (student_id) 
	filter (where e.optional = false and d.passed = false)
	over (partition by exam_id) as count
	from exam_depts as d 
	join exams as e on d.exam_id= e.id)
select exam_id, count
from
	(select max(count) as count 
	from gg) as zz
join gg using (count);
-- 10.	Проверить, есть ли в базе студент, не допущенный ни к одному обязательному для его группы экзамену. Можно считать, что каждая группа обязана сдавать хотя бы один экзамен.
select distinct student_id
from
	(select student_id,
	count (exam_id) 
	filter (where e.optional = false and d.allow_to_pass = false) 
	over (partition by student_id) as c1,
	count (exam_id) 
	filter (where e.optional = false) 
	over (partition by student_id) as c2
	from exam_depts as d 
	join exams as e on d.exam_id= e.id) as zz
where c1  = c2;