 ####   ##                   #               ##   ##  ##  ##             
######  ##                  ##               ##   ##  ##  ##               
##      #####  ####  ##### #### #####  ###   ##   ##  ##  ##               
##      ## ##  # ##  ## ##  ##  ##  ## ##    ##   ##  ##  ##              
##  ##  ## ## #####  ## ##  ##  ###### ##    ##   ##  ##  ##               
######  ## ## ## ##  ## ##  ##  ## ### ##     ## ##   ##  ##  ##                 
 #####  ## ## #####  #####  ###  ####  ##      ###    ##  ##  ##           
                     ##                                                  
13.11.2001           ##                                                  

pridajme do tabulky ucitel
INSERT INTO rn_ucitel VALUES(1,'Julius','Fucik'),(2,'Alois','Jirasek');

INSERT INTO rn_predmet VALUES
(1,'MA1a','',1,6),
(2,'MA1b','',1,6),
(3,'GRA1','',2,4),
(4,'PS1','',2,5)

INSERT INTO zapisane VALUES
  (1, 1, 1),
  (1, 2, 2),
  (1, 3, 2),
  (2, 2, NULL),
  (2, 4, 1),
  (3, 1, 1),
  (3, 3, 3),
  (4, 2, NULL),
  (5, 1, 2),
  (5, 2, 3),
  (5, 3, NULL),
  (6, 1, NULL),
  (6, 2, 3),
  (6, 3, 2),
  (6, 4, 1);


alter TABLE rn_predmet add foreign key rn_idu (iducitel) references rn_ucitel
alter TABLE rn_predmet add foreign key rn_idu1 (iducitel) references rn_ucitel
alter TABLE rn_predmet add foreign key rn_idu2 (idpredmet) references rn_student
alter TABLE rn_predmet add foreign key rn_idu3 (iducitel) references rn_ucitel

INSERT INTO rn_zapisane VALUES
  (1, 1, 1),
  (1, 2, 2),
  (1, 3, 2),
  (2, 2, NULL),
  (2, 4, 1),
  (3, 1, 1),
  (3, 3, 3),
  (4, 2, NULL),
  (5, 1, 2),
  (5, 2, 3),
  (5, 3, NULL),
  (6, 1, NULL),
  (6, 2, 3),
  (6, 3, 2),
  (6, 4, 1);

selektujme vsetkych studentov a ucitelov
SELECT s.meno, s.priezvisko FROM rn_student s
UNION
SELECT u.meno, u.priezvisko FROM rn_ucitel u
Ali    |Baba 
Julius |Fucik 
Jan    |Hrasko 
Alois  |Jirasek 
Jan    |Polienko 
Ruzena |Sipova 
Juraj  |Trulo  

UNION ALL - musia mat rovnaky pocet atributov, a musia zodpovedat aj typy

SELECT s.meno, s.priezvisko, CHAR(s.datumnarod) FROM rn_student s

UNION ALL
----------
SELECT u.meno, u.priezvisko, 'neevidujeme' FROM rn_ucitel u
Julius |Fucik    |neevidujeme 
Aloi s |Jirasek  |neevidujeme 
Jan    |Hrasko   |1980-01-01 
Ruzena |Sipova   |1980-02-02 
Ali    |Baba     |1970-12-11 
Jan    |Polienko |1981-07-04 
Juraj  |Trulo    |1982-02-03 
Juraj  |Trulo    |1983-11-07  

R1 R2 UNION UNION ALL
1  1    1       1
1  4    1       1 
2       2       1
2       2       2
3       3       2
        4       3
                4
ak by sa ucitel volal ako student, union all by zobrazil len jedneho

student a zapisane predmety
SELECT s.meno, s.priezvisko, count(*)
FROM rn_student s, 
  rn_predmet p,
  rn_zapisane z
WHERE
  z.idstudent=s.id AND
  z.idpredmet=p.id
Ali    |Baba |MA1a 
Ali    |Baba |GRA1 
Jan    |Hrasko |MA1a 
Jan    |Hrasko |MA1b 
Jan    |Hrasko |GRA1 
Jan    |Polienko |MA1b 
Ruzena |Sipova |MA1b 
Ruzena |Sipova |PS1 
Juraj  |Trulo |MA1a 
Juraj  |Trulo |MA1a 
Juraj  |Trulo |MA1b 
Juraj  |Trulo |MA1b 
Juraj  |Trulo |GRA1 
Juraj  |Trulo |GRA1 
Juraj  |Trulo |PS1  

meno, priezvisko studenta, kolko zapisanych predmetov

SELECT s.meno, s.priezvisko, count(p.kod)
FROM rn_student s, 
  rn_predmet p,
  rn_zapisane z
WHERE
  z.idstudent=s.id AND
  z.idpredmet=p.id
GROUP BY s.priezvisko, s.meno, s.id
???trochu lepsie aj s ORDER BY
Jan    |Hrasko |3 
Ruzena |Sipova |2 
Ali    |Baba |2 
Jan    |Polienko |1 
Juraj  |Trulo |3 
Juraj  |Trulo |4  

insertnime studentovi 4 vsetky predmety, ktore uci Jirasek

hromadne INSERTy, UPDATEy, DELETEy

INSERT INTO rn_zapisane
 (idstudent, idpredmet)  

ID vsetkych predmetov, ktore uci Jirasek
SELECT p.id FROM rn_predmet p
JOIN rn_ucitel u ON p.iducitel = u.id
WHERE u.priezvisko='Jirasek'
3 
4  

INSERT INTO rn_zapisane (idstudent, idpredmet)
  SELECT 4,p.id FROM rn_predmet p 
  JOIN rn_ucitel u ON p.iducitel = u.id 
  WHERE u.priezvisko='Jirasek'

vyselektujme
Alois, Jirasek, 2

select prof.meno, prof.priezvisko, count(sub.id)
from rn_ucitel as prof join rn_predmet as sub on prof.id = sub.iducitel
group by priezvisko, meno

meno, ucitel, predmet, kolko studentov je zapisanych na danom predmete
?group by?