Scribd
Upload
16 views1 page

Select

The document contains 11 SQL queries that analyze student, subject, and grade data from various tables. The queries calculate totals, averages, and counts grouped by factors like student group, subject, and major. They return metrics like the total grades, number of students and subjects per group, average grades by group and subject, and minimum, maximum grades for each subject.

Uploaded by

vesela.sobaka
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
16 views1 page

Select

The document contains 11 SQL queries that analyze student, subject, and grade data from various tables. The queries calculate totals, averages, and counts grouped by factors like student group, subject, and major. They return metrics like the total grades, number of students and subjects per group, average grades by group and subject, and minimum, maximum grades for each subject.

Uploaded by

vesela.sobaka
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 1

--1

create view qresult as


select dbo_student.Sname, dbo_student.Kod_group, sum(Reiting.Reiting) as zag_reiting
from Reiting inner join dbo_student on Reiting.Kod_student = dbo_student.Kod_stud
group by Sname, dbo_student.Kod_group

select * from qresult


--2
select count(dbo_student.Kod_stud) as stud_count, dbo_student.Kod_group from dbo_student
group by dbo_student.Kod_group
--3
select count(Rozklad_pids.K_predm_pl) as sub_count, Rozklad_pids.Kod_group from
Rozklad_pids
group by Rozklad_pids.Kod_group
--4
select count(K_zapis) as kilkist_par, Kod_group from Rozklad_pids
group by Kod_group
--5
select dbo_student.Kod_group, avg(QResult.zag_reiting) as avg_group
from dbo_student inner join Qresult on dbo_student.Sname = Qresult.Sname
group by Kod_group
--6
select predmet.Nazva, avg(Reiting.Reiting) as avg_subject from Reiting
inner join Rozklad_pids on Reiting.K_zapis = Rozklad_pids.K_zapis
inner join Predmet_plan on Predmet_plan.K_predm_pl = Rozklad_pids.K_predm_pl
inner join predmet on predmet.K_predmet = Predmet_plan.K_predmet
group by predmet.Nazva
--7
select dbo_student.Sname, predmet.Nazva, Reiting.Reiting from Reiting
inner join dbo_student on Reiting.Kod_student = dbo_student.Kod_stud
inner join Rozklad_pids on Reiting.K_zapis = Rozklad_pids.K_zapis
inner join Predmet_plan on Predmet_plan.K_predm_pl = Rozklad_pids.K_predm_pl
inner join predmet on predmet.K_predmet = Predmet_plan.K_predmet
group by predmet.Nazva
--8
select predmet.Nazva, min(Reiting.Reiting) as min_reiting from Reiting
inner join Rozklad_pids on Reiting.K_zapis = Rozklad_pids.K_zapis
inner join Predmet_plan on Predmet_plan.K_predm_pl = Rozklad_pids.K_predm_pl
inner join predmet on predmet.K_predmet = Predmet_plan.K_predmet
group by predmet.Nazva
--9
select predmet.Nazva, max(Reiting.Reiting) as max_reiting from Reiting
inner join Rozklad_pids on Reiting.K_zapis = Rozklad_pids.K_zapis
inner join Predmet_plan on Predmet_plan.K_predm_pl = Rozklad_pids.K_predm_pl
inner join predmet on predmet.K_predmet = Predmet_plan.K_predmet
group by predmet.Nazva
--10
select predmet.Nazva, count(Rozklad_pids.K_zapis) as kilkist_par from Rozklad_pids
inner join Predmet_plan on Predmet_plan.K_predm_pl = Rozklad_pids.K_predm_pl
inner join predmet on predmet.K_predmet = Predmet_plan.K_predmet
group by predmet.Nazva
--11
select Spetsialnost.Nazva, count(dbo_groups.Kod_group) as kilkist_group from Spetsialnost
inner join Navch_plan on Spetsialnost.K_spets = Navch_plan.K_spets
inner join dbo_groups on Navch_plan.K_navch_plan = dbo_groups.K_navch_plan
group by Spetsialnost.Nazva

You might also like