4_5.sql

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

--insert into Student 
--select (select id_person from Person where st_surname='Mahnitskiy' and st_name='Alexandr' and st_pname='Igorevich'),(select id_group from Grouppa where group_name='KM-08'),(select id_form from Form_edu where form_name='Vechernee'),1

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

declare cursor1 cursor
global
scroll
static
for select  Person.id_person ,student.id_stud
	from Person inner JOIN Student 
   ON Person.id_person=Student.id_person
	ORDER BY 	id_person	


open cursor1
declare 
@kol int,
@idstud int,
@idstud2 int,
@idmax int,
@max int,
@idperson int,
@idperson2 int
	set @kol=0
	set @idperson=0
	set @idperson2=0
		set @max=0
set @idmax=0
while(1=1)
begin

	fetch next from cursor1 into  @idperson,@idstud
	--SELECT @idperson,@idstud
			if(@@fetch_status=-1)
				break;
			if(@@fetch_status<>-2)
			begin
				if (@idperson=@idperson2) 		set @kol=@kol+1;
				
              --if (@kol>@max) 
				--begin
				-- set @max=@kol
				-- set @idmax=@idperson	
				--end


              if (@kol>=1 AND @idperson<>@idperson2) 

				BEGIN
					select st_surname as LName, left(st_name,1) as FName, left(st_pname,1) as Mname, group_name, id_stud 
					from Person t1 
					INNER JOIN Student t2  
					ON t1.id_person=t2.id_person 
					Inner join grouppa t3
					ON t3.id_group=t2.id_group 
                    where t1.id_person=@IDperson2
				END



				if (@idperson<>@idperson2) set @kol=0;

				set @idperson2=@idperson
			end
		
end

close cursor1
deallocate global cursor1

--select @idmax as idmax, @max as kol