2,3,4,6

--2
create table byvali_studenti
  (
   id_student int,
   cislo_studenta int primary key not null,
   jmeno varchar (30),
   prijmeni varchar (50),
   adresa varchar (50),
   obec varchar (30),
   psc numeric (5),
   telefon numeric (9),
   datum date
  )

create trigger smazano on student
for delete
as
 begin
insert into byvali_studenti (id_student,cislo_studenta,jmeno,prijmeni,adresa,obec,psc,telefon,datum)
 select id_student,cislo_studenta,jmeno,prijmeni,adresa,obec,psc,telefon, GETDATE() from deleted
end

--3
begin tran TZ1
delete from hodnoceni where cislo_studenta=4567
delete from student where cislo_studenta=4567
select * from byvali_studenti
select * from student
--commit tran TZ1
rollback tran TZ1


--4
create procedure kurz
@nazev varchar(50)
as
begin
select k.nazev,t.semestr,t.ucebna,t.den,t.cas,p.cislo_predchozi from kurs k,terminy t,predpoklady p
where @nazev=k.nazev
end

execute kurz 'Databázové systémy'


--5




--6
declare Kurzor cursor for
 select s.jmeno,s.prijmeni,t.den,t.ucebna from student s,terminy t,hodnoceni h
 where s.cislo_studenta=h.cislo_studenta and h.id_terminu=t.id_terminu
 declare
   @jmeno varchar(30),
   @prijmeni varchar(30),
   @den varchar(10),
   @ucebna varchar(5)
  open Kurzor
  fetch Kurzor into @jmeno,@prijmeni,@den,@ucebna
   while @@fetch_status=0
    begin
     print @jmeno+' '+@prijmeni+' - '+@den+' - Učebna: '+@ucebna
     fetch Kurzor into @jmeno,@prijmeni,@den,@ucebna
    end
  close Kurzor
  deallocate Kurzor