1-6

create database cvicim3_to
go

use cvicim3_to
go

create schema kontrola authorization dbo
go

-- Tvorba tabulek
create table kontrola.student
(
id_student int identity(1, 1),
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)
)
create table kontrola.lektor
(
id_lektora int identity(1, 1),
cislo_lektora int primary key not null,
jmeno varchar (30),
prijmeni varchar (50),
adresa varchar (50),
obec varchar (30),
psc numeric (5),
telefon numeric (9)
)
create table kontrola.kurs
(
id_kursu int identity(1, 1),
cislo_kursu varchar (4) primary key not null,
nazev varchar (50),
popis varchar (200)
)
create table kontrola.predpoklady
(
id_predpokladu int identity(1, 1) primary key,
cislo_kursu varchar (4) foreign key references kontrola.kurs (cislo_kursu),
cislo_predchozi varchar (4)
)
create table kontrola.aprobace
(
id_aprobace int identity(1, 1) primary key,
cislo_lektora int foreign key references kontrola.lektor (cislo_lektora),
cislo_kursu varchar (4) foreign key references kontrola.kurs (cislo_kursu)
)
create table kontrola.terminy
(
id_terminu int identity(1, 1) primary key,
rok varchar (9),
semestr varchar (5),
cislo_kursu varchar (4) foreign key references kontrola.kurs (cislo_kursu),
ucebna varchar (4),
den varchar (5),
cas varchar (5),
cislo_lektora int foreign key references kontrola.lektor (cislo_lektora)
)
create table kontrola.hodnoceni
(
id_hodnoceni int identity(1, 1) primary key,
cislo_studenta int foreign key references kontrola.student (cislo_studenta),
id_terminu int foreign key references kontrola.terminy (id_terminu),
hodnoceni int
)
-- ukonceni tvorby tabulek
-- vlozeni studentu
insert into kontrola.student (cislo_studenta, jmeno, prijmeni, adresa, obec, psc, telefon)
values (4567, 'Helena','Červená','Poříèí 128','Brno','60200','523698741')
insert into kontrola.student (cislo_studenta, jmeno, prijmeni, adresa, obec, psc, telefon)
values (4965, 'Barbora','Studená','U Pergamenky 26','Praha','12000','258963147')
insert into kontrola.student (cislo_studenta, jmeno, prijmeni, adresa, obec, psc, telefon)
values (6874, 'Jan','Čermák','U dvora 569','Jihlava','58698','365214895')
insert into kontrola.student (cislo_studenta, jmeno, prijmeni, adresa, obec, psc, telefon)
values (7096, 'Karel','Holub','U sokolovny 21','Brno','63500','512963478')
insert into kontrola.student (cislo_studenta, jmeno, prijmeni, adresa, obec, psc, telefon)
values (8513, 'Jiøí','Adamec','Grohova 65','Brno','60200','587452369')
-- vlozeni lektoru
insert into kontrola.lektor (cislo_lektora, jmeno, prijmeni, adresa, obec, psc, telefon)
values (25897, 'Václav','Horník','Hlavní třída 1','Jihlava','58601','214563987')
insert into kontrola.lektor (cislo_lektora, jmeno, prijmeni, adresa, obec, psc, telefon)
values (36521, 'Martin','Dvořák','Křenová 54','Brno','62100','569743215')
insert into kontrola.lektor (cislo_lektora, jmeno, prijmeni, adresa, obec, psc, telefon)
values (87421, 'Ladislav','Pálka','Otakara Ševčíka 63','Brno','60200','539715698')
insert into kontrola.lektor (cislo_lektora, jmeno, prijmeni, adresa, obec, psc, telefon)
values (95471, 'Otakar','Možný','Kolejní 5','Brno','61600','411369852')
-- vlozeni kursu
insert into kontrola.kurs (cislo_kursu, nazev, popis)
values ('X100', 'Základy PC','Úvodní kurz informatiky')
insert into kontrola.kurs (cislo_kursu, nazev, popis)
values ('X201', 'Algoritmizace','Základy programování')
insert into kontrola.kurs (cislo_kursu, nazev, popis)
values ('X202', 'Visual Basic','Programovací tecjniky ve VB')
insert into kontrola.kurs (cislo_kursu, nazev, popis)
values ('X301', 'Základy zpracování dat','Struktura dat, techniky ukládání, práce s datovámi soubory')
insert into kontrola.kurs (cislo_kursu, nazev, popis)
values ('X302', 'Datové modelování','Relační datový model, normalizace, E-R diagramy')
insert into kontrola.kurs (cislo_kursu, nazev, popis)
values ('X401', 'Databázové systémy','Základy databází, SQL jazyk')
-- vlozeni predpokladu
insert into kontrola.predpoklady (cislo_kursu, cislo_predchozi)
values ('X201','X100')
insert into kontrola.predpoklady (cislo_kursu, cislo_predchozi)
values ('X202','X201')
insert into kontrola.predpoklady (cislo_kursu, cislo_predchozi)
values ('X302','X301')
insert into kontrola.predpoklady (cislo_kursu, cislo_predchozi)
values ('X401','X302')
-- vlozeni aprobace
insert into kontrola.aprobace (cislo_lektora, cislo_kursu)
values (25897,'X100')
insert into kontrola.aprobace (cislo_lektora, cislo_kursu)
values (36521,'X201')
insert into kontrola.aprobace (cislo_lektora, cislo_kursu)
values (36521,'X202')
insert into kontrola.aprobace (cislo_lektora, cislo_kursu)
values (95471,'X202')
insert into kontrola.aprobace (cislo_lektora, cislo_kursu)
values (95471,'X401')
insert into kontrola.aprobace (cislo_lektora, cislo_kursu)
values (87421,'X301')
insert into kontrola.aprobace (cislo_lektora, cislo_kursu)
values (87421,'X302')
insert into kontrola.aprobace (cislo_lektora, cislo_kursu)
values (87421,'X401')
-- vlozeni terminu
insert into kontrola.terminy (rok, semestr, cislo_kursu, ucebna, den, cas, cislo_lektora)
values ('2006/2007', 'Letní','X100','P384','Po','7-10',25897)
insert into kontrola.terminy (rok, semestr, cislo_kursu, ucebna, den, cas, cislo_lektora)
values ('2006/2007', 'Letní','X201','P164','St','13-15',36521)
insert into kontrola.terminy (rok, semestr, cislo_kursu, ucebna, den, cas, cislo_lektora)
values ('2006/2007', 'Letní','X401','P381','Po,Pá','9-11',87421)
insert into kontrola.terminy (rok, semestr, cislo_kursu, ucebna, den, cas, cislo_lektora)
values ('2007/2008', 'Zimní','X202','P165','St','10-12',95471)
insert into kontrola.terminy (rok, semestr, cislo_kursu, ucebna, den, cas, cislo_lektora)
values ('2007/2008', 'Zimní','X301','P292','St','17-19',87421)
insert into kontrola.terminy (rok, semestr, cislo_kursu, ucebna, den, cas, cislo_lektora)
values ('2007/2008', 'Zimní','X302','P264','Čt','15-17',87421)
-- vlozeni hodnoceni
insert into kontrola.hodnoceni (cislo_studenta, id_terminu, hodnoceni)
values ('4567',1,2)
insert into kontrola.hodnoceni (cislo_studenta, id_terminu, hodnoceni)
values ('4567',4,2)
insert into kontrola.hodnoceni (cislo_studenta, id_terminu, hodnoceni)
values ('4965',3,1)
insert into kontrola.hodnoceni (cislo_studenta, id_terminu, hodnoceni)
values ('6874',2,1)
insert into kontrola.hodnoceni (cislo_studenta, id_terminu, hodnoceni)
values ('6874',6,1)
insert into kontrola.hodnoceni (cislo_studenta, id_terminu, hodnoceni)
values ('7096',3,2)
insert into kontrola.hodnoceni (cislo_studenta, id_terminu, hodnoceni)
values ('7096',5,3)
insert into kontrola.hodnoceni (cislo_studenta, id_terminu, hodnoceni)
values ('7096',6,2)
insert into kontrola.hodnoceni (cislo_studenta, id_terminu, hodnoceni)
values ('8513',3,3)



--2
create table kontrola.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 kontrola.student
for delete
as
 begin
insert into kontrola.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 kontrola.hodnoceni where cislo_studenta=4567
delete from kontrola.student where cislo_studenta=4567
select * from kontrola.byvali_studenti
select * from kontrola.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 kontrola.kurs k, kontrola.terminy t,kontrola.predpoklady p
where @nazev=k.nazev and k.cislo_kursu=t.cislo_kursu and k.cislo_kursu=p.cislo_kursu
end

execute kurz 'Databázové systémy'

--5
create procedure vnorena
@cislo_lektora int
as
begin
  if (@cislo_lektora is  null)
    begin
     print 'Student nebyl z daneho kurzu hodnocen'
    end
  else
   begin
   select jmeno+' '+prijmeni as 'Jméno učitele',telefon from kontrola.lektor
    where @cislo_lektora=cislo_lektora
   end
end

create procedure ucitel
@cislo_studenta int,   --je to navesene na cislo_studenta a ne id_studenta, cislo_studenta je primarni klic, s id_studenta by to bylo o podminku delsi
@cislo_kurzu varchar(4),
@cislo_lektora int output
as
begin
 set @cislo_lektora= (select t.cislo_lektora from kontrola.terminy t, kontrola.hodnoceni h
                        where @cislo_kurzu=t.cislo_kursu and @cislo_studenta=h.cislo_studenta
                        and h.id_terminu=t.id_terminu)
execute vnorena @cislo_lektora
end

declare @cislo_lektora int
execute ucitel 4965,'X100',@cislo_lektora output

--6
declare Kurzor cursor for
 select s.jmeno,s.prijmeni,t.den,t.ucebna from kontrola.student s,kontrola.terminy t,kontrola.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