PyK027Sf

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
90
91-- Список участников, получивших хотя бы по двум дисциплинам более 70 баллов, с колонками «Участник», «Дисциплины, по которым более 70 баллов».
92
93drop table if exists ResultForThirdTask
94
95create table ResultForThirdTask(
96    Участник NVARCHAR(15),
97    Дисциплины NVARCHAR(max)
98)
99
100
101drop procedure if exists GetListOfStudents
102
103go
104create procedure GetListOfStudents(@score int)
105as
106  	declare 
107		@name nvarchar(255) = N'Дисциплин, по которым больше ' + STR(@score) + ' баллов', 
108  		@id int,
109
110		@subjects nvarchar(max),
111		@person nvarchar(15)
112
113  	declare get_person cursor for select id from Students
114	open get_person
115
116  	fetch next from get_person into @id
117
118  	while @@FETCH_STATUS = 0  
119  	begin
120		set @subjects = N''
121
122    	select @person = student, @subjects += subject_ + ', '
123		from (
124			select stud.name as student, s.name as subject_, count(s.id) over(partition by stud.name) as count
125			from Results as res
126				join Subjects as s on res.subject_id = s.id
127				join Students as stud on res.student_id = stud.id
128			where res.student_id = @id and res.points > @score) r
129		where count > 1
130
131		if @subjects != N''
132			insert into ResultForThirdTask values (@person, Substring(@subjects, 1, len(@subjects)-1))
133
134    	fetch next from get_person into @id
135  	end
136	close get_person
137	Deallocate get_person
138
139	exec sp_rename 'dbo.ResultForThirdTask.Дисциплины', @name, 'COLUMN'
140	select * from ResultForThirdTask
141	truncate table ResultForThirdTask
142	set @name = 'dbo.ResultForThirdTask.'+@name
143	exec sp_rename @name, 'Дисциплины', 'COLUMN'
144go
145
146exec GetListOfStudents 96