4_1.sql

to become acquainted with a concept &#171 cursor&#187 language of SQL, by the types of cursors, to

if (cursor_status('global', 'cursor_2')<>-3)
deallocate  global cursor_2

if (cursor_status('global', 'cursor_1')<>-3)
deallocate  global cursor_1


declare cursor_1 cursor--vse ozenki vseh
global
scroll
keyset
for
select id_stud as ID, ball_name as BALL
from Mark t1
Inner join Mark_names t2
ON t1.id_mark=t2.id_mark

declare cursor_2 cursor--vse id s ozenkami
global
scroll
keyset
for
select distinct t2.id_stud as ID
 from Mark  t1
INNER JOIN Student t2
ON t1.id_stud=t2.id_stud
INNER JOIN Grouppa t3
ON t2.id_group=t3.id_group 
where kurs=4 
						

declare 
@id int,
@id2 int,
@sum float,
@ozenka int,
@kol int,
@featch_res2 int,
@featch_res1 int

--open cursor_2
--while(1=1)
--begin
--	fetch next from cursor_2 
--	set @featch_res=@@FETCH_STATUS	
  ---  if (@featch_res=-1)
--	    break;
--end
--close cursor_2





open cursor_2
while(1=1)
begin
	fetch next from cursor_2 into @id
		set @featch_res2=@@FETCH_STATUS
    if (@featch_res2=-1)
	    break;
	--select (@id)
	set @sum=0
	set @kol=0
					

					open cursor_1
					while(1=1)
					begin
						fetch next from cursor_1 into @id2, @ozenka 
						
						set @featch_res1=@@FETCH_STATUS	
						if (@featch_res1=-1)
						break;


						if (@id2=@id)
						 begin
							set @sum=@sum+@ozenka
							set @kol=@kol+1	
						end


					end
					close cursor_1


					set @sum=@sum/@kol
					if (@sum<=4.56 and @sum>3 and @id2 is not null)
					begin
						
						select st_surname as LName, left(st_name,1) as FName, left(st_pname,1) as Mname, kurs as Kurs, group_name as Grup, @sum as Mark
						from Person t1 
						INNER JOIN Student t2
						ON t1.id_person=t2.id_person 
						INNER JOIN Grouppa t3
						ON t2.id_group=t3.id_group 
						where kurs=4 and id_stud=@id 
						
						--select @id
					end
					
					





end

close cursor_2