-- a) b)

insert into os_udaje (rod_cislo, meno, priezvisko, ulica, psc, obec)
values ('121212/1212', 'Karol', 'Novy', null, null, null);


insert into os_udaje (rod_cislo, meno, priezvisko, ulica, psc, obec)
values ('232323/2323', 'Karol', 'Novsi', null, null, null);

insert into os_udaje(rod_cislo, meno, priezvisko)
select rod_cislo, meno, priezvisko from valentik.osoba;

--c)

insert into student (rod_cislo, os_cislo, rocnik, st_skupina, st_odbor, st_zameranie)
values ('121212/1212', 
123, 
'1', 
'5ZI012',
(select st_odbor from st_odbory where popis_odboru = 'Informatika' and popis_zamerania is null ),
(select st_zameranie from st_odbory where popis_odboru = 'Informatika' and popis_zamerania is null));



insert into student (rod_cislo, os_cislo, rocnik, st_skupina, st_odbor, st_zameranie)
(select '232323/2323', 
90, 
'2', 
'5ZI012', st_odbor, st_zameranie from st_odbory where popis_odboru = 'Informacne systemy' and popis_zamerania = 'Aplikovana informatika' );


insert into student
(rod_cislo, os_cislo, rocnik, st_skupina,
st_odbor, st_zameranie)
select rod_cislo, os_cislo, rocnik, st_skupina, st_odbor, st_zameranie
from valentik.osoba;

--d)

DECLARE
  cisloPredmetu CHAR(4 BYTE) := 'BI11';
  osobne_cislo NUMBER := 123;
  rokSkolsky NUMBER := 2003;
BEGIN
  INSERT INTO zap_predmety (os_cislo, cis_predm, skrok, prednasajuci, ects) 
    VALUES(osobne_cislo, 
    cisloPredmetu, 
    rokSkolsky, 
    NVL((SELECT prednasajuci FROM zap_predmety WHERE cis_predm = cisloPredmetu AND skrok = rokSkolsky GROUP BY prednasajuci, cis_predm, skrok), (SELECT garant FROM predmet_bod WHERE cis_predm = cisloPredmetu AND skrok = rokSkolsky)),
    (SELECT ects FROM predmet_bod WHERE cis_predm = cisloPredmetu GROUP BY cis_predm, ects));
END;

DECLARE
  cisloPredmetu CHAR(4 BYTE) := 'BI02';
  osobne_cislo NUMBER := 123;
  rokSkolsky NUMBER := 2008;
BEGIN
  INSERT INTO zap_predmety (os_cislo, cis_predm, skrok, prednasajuci, ects) 
    VALUES(osobne_cislo, 
    cisloPredmetu, 
    rokSkolsky, 
    NVL((SELECT prednasajuci FROM zap_predmety WHERE cis_predm = cisloPredmetu AND skrok = rokSkolsky GROUP BY prednasajuci, cis_predm, skrok), (SELECT garant FROM predmet_bod WHERE cis_predm = cisloPredmetu AND skrok = rokSkolsky)),
    (SELECT ects FROM predmet_bod WHERE cis_predm = cisloPredmetu GROUP BY cis_predm, ects));
END;

DECLARE
  cisloPredmetu CHAR(4 BYTE) := 'BE01';
  osobne_cislo NUMBER := 123;
  rokSkolsky NUMBER := 2003;
BEGIN
  INSERT INTO zap_predmety (os_cislo, cis_predm, skrok, prednasajuci, ects) 
    VALUES(osobne_cislo, 
    cisloPredmetu, 
    rokSkolsky, 
    NVL((SELECT prednasajuci FROM zap_predmety WHERE cis_predm = cisloPredmetu AND skrok = rokSkolsky GROUP BY prednasajuci, cis_predm, skrok), (SELECT garant FROM predmet_bod WHERE cis_predm = cisloPredmetu AND skrok = rokSkolsky)),
    (SELECT ects FROM predmet_bod WHERE cis_predm = cisloPredmetu GROUP BY cis_predm, ects));
END;

DECLARE
  cisloPredmetu CHAR(4 BYTE) := 'II08';
  osobne_cislo NUMBER := 90;
  rokSkolsky NUMBER := 2006;
BEGIN
  INSERT INTO zap_predmety (os_cislo, cis_predm, skrok, prednasajuci, ects) 
    VALUES(osobne_cislo, 
    cisloPredmetu, 
    rokSkolsky, 
    NVL((SELECT prednasajuci FROM zap_predmety WHERE cis_predm = cisloPredmetu AND skrok = rokSkolsky GROUP BY prednasajuci, cis_predm, skrok), (SELECT garant FROM predmet_bod WHERE cis_predm = cisloPredmetu AND skrok = rokSkolsky)),
    (SELECT ects FROM predmet_bod WHERE cis_predm = cisloPredmetu GROUP BY cis_predm, ects));
END;

DECLARE
  cisloPredmetu CHAR(4 BYTE) := 'II07';
  osobne_cislo NUMBER := 90;
  rokSkolsky NUMBER := 2007;
BEGIN
  INSERT INTO zap_predmety (os_cislo, cis_predm, skrok, prednasajuci, ects) 
    VALUES(osobne_cislo, 
    cisloPredmetu, 
    rokSkolsky, 
    NVL((SELECT prednasajuci FROM zap_predmety WHERE cis_predm = cisloPredmetu AND skrok = rokSkolsky GROUP BY prednasajuci, cis_predm, skrok), (SELECT garant FROM predmet_bod WHERE cis_predm = cisloPredmetu AND skrok = rokSkolsky)),
    (SELECT ects FROM predmet_bod WHERE cis_predm = cisloPredmetu GROUP BY cis_predm, ects));
END;

insert into zap_predmety
(os_cislo, cis_predm, skrok, prednasajuci, ects)
select os_cislo, cis_predm, skrok, garant, ects
from valentik.skusky sk join predmet_bod using ( cis_predm, skrok );

-- Zadanie Update:

UPDATE os_udaje
SET priezvisko='Stary'
WHERE rod_cislo = '121212/1212';

-- zmena mena na Carlos studentovi s osobnym cislom  8

UPDATE os_udaje
SET meno='Carlos'
WHERE rod_cislo = (select rod_cislo from student where os_cislo = 8);

-- Tento prikaz je este zly. treba tam dorobi pre kazdy riadok.
--UPDATE zap_predmety
--SET cis_predm='BI01'
--WHERE os_cislo = (select os_cislo from student where rocnik = 1) and cis_predm='BI11';

UPDATE student
SET stav='S'
Where stav is null;

-- spytat sa na toto misa

UPDATE student
set rocnik = rocnik + 1, 
st_skupina = (substr (st_skupina, 1,4))||(substr (st_skupina, 1,4)+1)||(substr (st_skupina, 6,1))
where stav='S' and rocnik < 3;




