agTTHVH4

1use lab4
2
3-- edit data
4exec sp_rename 'dbo.Лист1$', 'Students'
5exec sp_rename 'dbo.Лист2$', 'Subjects'
6exec sp_rename 'dbo.Лист3$', 'Results'
7
8exec sp_rename 'Students.Номер', 'id', 'COLUMN'
9exec sp_rename 'Students.Участник', 'name', 'COLUMN'
10
11exec sp_rename 'Subjects.Номер', 'id', 'COLUMN'
12exec sp_rename 'Subjects.Дисциплина', 'name', 'COLUMN'
13
14exec sp_rename 'Results.Номер участника', 'student_id', 'COLUMN'
15exec sp_rename 'Results.Дисциплина', 'subject_id', 'COLUMN'
16exec sp_rename 'Results.Баллы', 'points', 'COLUMN'
17
18alter table Subjects drop column F3
19alter table Subjects drop column F4
20alter table Subjects drop column F5
21
22alter table Students alter column id int not null
23alter table Students add constraint pk_students_id primary key (id)
24
25alter table Subjects alter column id int not null
26alter table Subjects add constraint pk_subjects_id primary key (id)
27
28alter table Results alter column student_id int not null
29alter table Results alter column subject_id int not null
30alter table Results add constraint pk_results primary key (student_id, subject_id)
31
32select * from Students;
33select * from Subjects;
34select * from Results;
35
36-- Сколько участников тестирования получили по русскому языку, физике и математике в сумме более 200 баллов?
37
38drop function if exists GetStudentsCount;
39
40go
41create function GetStudentsCount(@subject1 NVARCHAR(255), @subject2 NVARCHAR(255), @subject3 NVARCHAR(255), @sum int)  
42returns int
43as
44begin
45	declare @res int;
46	select @res = count(*) from (
47		select sum(r.points) as s
48		from Results as r
49		join Subjects  as sub on r.subject_id = sub.id 
50		where sub.name = @subject1 or
51			  sub.name = @subject2 or
52			  sub.name = @subject3
53		group by r.student_id
54		having sum(r.points) > @sum
55	) as res;
56	return @res;
57end;
58go
59
60print dbo.GetStudentsCount('русский язык', 'математика', 'физика', 200);
61
62
63
64-- Каков средний балл по физике у участников, которые набрали по математике более 60 баллов?
65
66drop function if exists GetAveragePoints;
67
68go
69create function GetAveragePoints(@subject1 NVARCHAR(255), @subject2 NVARCHAR(255), @point_limit int)  
70returns float
71as
72begin
73	declare @res float;
74	
75	select @res = avg(points) from Results as r 
76	join Subjects  as sub on r.subject_id = sub.id 
77	where sub.name = @subject1 
78		and r.student_id in 
79			(select r.student_id from Results as r 
80			join Subjects  as sub on r.subject_id = sub.id
81			where sub.name = @subject2 
82				and r.points > @point_limit);
83
84	return @res;
85end;
86go
87
88print dbo.GetAveragePoints('физика', 'математика', 60);
89
90select avg(points) from Results as r 
91join Subjects  as sub on r.subject_id = sub.id 
92where sub.name = 'физика' 
93	and r.student_id in 
94		(select r.student_id from Results as r 
95		join Subjects  as sub on r.subject_id = sub.id
96		where sub.name = 'математика' 
97			and r.points > 60);
98
99
100-- Список участников, получивших хотя бы по двум дисциплинам более 70 баллов, с колонками «Участник», «Дисциплины, по которым более 70 баллов».