 ####   ##                   #                ##  ##   ##  
######  ##                  ##                ##   ## ##   
##      #####  ####  ##### #### #####  ###    ##    ###    
##      ## ##  # ##  ## ##  ##  ##  ## ##     ##     #    
##  ##  ## ## #####  ## ##  ##  ###### ##     ##    ###    
######  ## ## ## ##  ## ##  ##  ## ### ##     ##   ## ##   ##                 
 #####  ## ## #####  #####  ###  ####  ##     ##  ##   ##  ##         
                     ##                                                  
27.11.2001           ##    

integritne obmedzenia
- domeny
  - ID ma byt typu Integer
  - mozno vytvorit vlastnu domenu - 
    pr tohto roku=mojrok od 1.1. do 31.12
- stlpec
  - not null (povinna polozka)
  - check 
    - znamka: integer
    - overenie zapornych cisiel
    - CONSTRAINT naznamku CHECK (znamka>0)
  - default hodnota
  - unique
- riadok
- tabulka
   - CHECK sa rozsiri, v praxi malo rozsirene
     - pouzivany na SQL prikaz (nevkladat Jiraskove znamky)
- vztah medzi tabulkami
  - najdolezitejsie - hovori o tom referencna integrita
  - zabezpeci konzistenciu dat

referenc. vztahy - nie fyzic.
moze byt aj medzi tou istou tabulkou
kaskadny delete

vyselektujme studenta, pocet kreditov ktore moze ziskat
SELECT s.id, s.meno, s.priezvisko, SUM(p.kredit)
from student s JOIN
student s join
  zapisane z on s.id=z.idstudent
join predmet p on p.id=z.idpredmet
group bz s.id s.meno, s.priezvisko

vyselektujme studenta, pocet ziskanych predmetov
SELECT s.id, s.meno, s.priezvisko, SUM(p.kredit)
from student s JOIN
student s join
  zapisane z on s.id=z.idstudent
join predmet p on p.id=z.idpredmet
where z.znamka is not null
group bz s.id s.meno, s.priezvisko


pocet moznych a ziskanych
WITH 
  rn_mozne(id,kredit) as SELECT cislo 1
  rn_ziskane(id,kredit) as SELECT cislo 2
SELECT s.id, s.meno, s.priezvisko, m.kredit, z.kredit
FROM student s join rn_mozne on s.id=m.id
  left outer join ziskane z on s.id=m.id
order by priezvisko, meno
m - mozne
z - ziskane
with

mozne (id, kredit) as ( 
select std.id, SUM (p.kredit)
from ml_student as std join ml_zapisane as zap on zap.idstudent = std.id join ml_predmet as p on p.id = zap.idpredmet
group by std.id
),

ziskane (id, kredit) as (
select std.id, SUM (p.kredit)
from ml_student as std join ml_zapisane as zap on zap.idstudent = std.id join ml_predmet as p on p.id = zap.idpredmet
where zap.znamka is not null
group by std.id
)

select std.id, std.meno, std.priezvisko, m.kredit, zisk.kredit 
from ml_student std join mozne as m on std.id = m.id left outer join ziskane as zisk on zisk.id = std.id


create table rn_clen(
id integer not null,
idotec integer,
meno VARCHAR(15),
priezvisko VARCHAR (30),
datum_od DATE,
datum_do DATE,
primary key(id))

lepsie
create table rn_clen(
id integer NOT null,
idotec integer default 0,
meno VARCHAR(15),
priezvisko VARCHAR (30),
datum_od DATE,
datum_do DATE,
primary key(id))

na idotec treba constraint sama na seba
id otec integer default 0

nahodme constraint
ALTER TABLE RN_CLEN ADD CONSTRAINT RN_naidotec FOREIGN KEY (idotec) REFERENCES rn_clen
INSERT INTO rn_clen VALUES
(1, 1, 'Jan', 'Novak', '1.1.1901', '1.1.1959'),
(2, 1, 'Juraj', 'Novak', '1.1.1921', '1.1.1963'),
(3, 1, 'Jozef', 'Novak', '1.1.1922', '1.1.1963'),
(4, 1, 'Frantisek', 'Novak', '1.1.1923', '1.1.1963'),
(5, 2, 'Juraj', 'Novak', '1.1.1944', '1.1.1963'),
(6, 2, 'Aladar', 'Novak', '1.1.1945', '1.1.1963'),
(7, 2, 'Arpad', 'Novak', '1.1.1946', '1.1.1963'),
(8, 2, 'Milan', 'Novak', '1.1.1947', '1.1.1963'),
(9, 2, 'Ngu-Bo', 'Novak', '1.1.1948', '1.1.1963'),
(10, 3, 'Alojz', 'Novak', '1.1.1939', null),
(11, 3, 'Mikulas', 'Novak', '1.1.1940', '1.1.1973'),
(12, 3, 'Benedikt', 'Novak', '1.1.1941', null),
(13, 5, 'Juraj', 'Novak', '1.1.1962', null),
(14, 5, 'Jozef', 'Novak', '1.1.1963', '1.1.1983'),
(15, 5, 'Viliam', 'Novak', '1.1.1964', '1.1.1983'),
(16, 5, 'Pavol', 'Novak', '1.1.1965', '1.1.1983'),
(17, 6, 'Bedrich', 'Novak', '1.1.1962', null),
(18, 6, 'Fridrich', 'Novak', '1.1.1963', null),
(19, 7, 'Ondrej', 'Novak', '1.1.1964', null),
(20, 17, 'Andrej', 'Novak', '1.1.1987', null),
(21, 17, 'Peter', 'Novak', '1.1.1988', null),
(22, 17, 'Gejza', 'Novak', '1.1.1989', null);

clovek a jeho synovia
select c1.id, c1.meno, c2.id, c2.meno 
from rn_clen c1, rn_clen c2
where c1.id=c2.idotec
1 |Jan |1 |Jan 
1 |Jan |2 |Juraj 
1 |Jan |3 |Jozef 
1 |Jan |4 |Frantisek 
2 |Juraj |5 |Juraj 
2 |Juraj |6 |Aladar 
2 |Juraj |7 |Arpad 
2 |Juraj |8 |Milan 
2 |Juraj |9 |Ngu-Bo 
3 |Jozef |10 |Alojz 
3 |Jozef |11 |Mikulas 
3 |Jozef |12 |Benedikt 
5 |Juraj |13 |Juraj 
5 |Juraj |14 |Jozef 
5 |Juraj |15 |Viliam 
5 |Juraj |16 |Pavol 
6 |Aladar |17 |Bedrich 
6 |Aladar |18 |Fridrich 
7 |Arpad |19 |Ondrej 
17 |Bedrich |20 |Andrej 
17 |Bedrich |21 |Peter 
17 |Bedrich |22 |Gejza  

clovek a pocet synov
select clen1.id, clen1.meno, count(clen2.id) from ml_clen as clen1 
join ml_clen as clen2 on clen2.idotec = clen1.id group by clen1.id, clen1.meno

clovek a jeho vnuci
select c1.id, c1.meno, c3.id, c3.meno
from rn_clen as c1, rn_clen as c2, rn_clen c3
where (c2.idotec = c1.id) and (c3.idotec=c2.id)
1 |Jan |1 |Jan 
1 |Jan |2 |Juraj 
1 |Jan |3 |Jozef 
1 |Jan |4 |Frantisek 
1 |Jan |5 |Juraj 
1 |Jan |6 |Aladar 
1 |Jan |7 |Arpad 
1 |Jan |8 |Milan 
1 |Jan |9 |Ngu-Bo 
1 |Jan |10 |Alojz 
1 |Jan |11 |Mikulas 
1 |Jan |12 |Benedikt 
2 |Juraj |13 |Juraj 
2 |Juraj |14 |Jozef 
2 |Juraj |15 |Viliam 
2 |Juraj |16 |Pavol 
2 |Juraj |17 |Bedrich 
2 |Juraj |18 |Fridrich 
2 |Juraj |19 |Ondrej 
6 |Aladar |20 |Andrej 
6 |Aladar |21 |Peter 
6 |Aladar |22 |Gejza  

dvojice bratov
select c2.id, c2.meno, c3.id, c3.meno
from rn_clen as c2, rn_clen c3
where (c2.idotec=c3.idotec) and (c2.id<c3.id)
1 |Jan |2 |Juraj 
1 |Jan |3 |Jozef 
2 |Juraj |3 |Jozef 
1 |Jan |4 |Frantisek 
2 |Juraj |4 |Frantisek 
3 |Jozef |4 |Frantisek 
5 |Juraj |6 |Aladar 
5 |Juraj |7 |Arpad 
6 |Aladar |7 |Arpad 
5 |Juraj |8 |Milan 
6 |Aladar |8 |Milan 
7 |Arpad |8 |Milan 
5 |Juraj |9 |Ngu-Bo 
6 |Aladar |9 |Ngu-Bo 
7 |Arpad |9 |Ngu-Bo 
8 |Milan |9 |Ngu-Bo 
10 |Alojz |11 |Mikulas 
10 |Alojz |12 |Benedikt 
11 |Mikulas |12 |Benedikt 
13 |Juraj |14 |Jozef 
13 |Juraj |15 |Viliam 
14 |Jozef |15 |Viliam 
13 |Juraj |16 |Pavol 
14 |Jozef |16 |Pavol 
15 |Viliam |16 |Pavol 
17 |Bedrich |18 |Fridrich 
20 |Andrej |21 |Peter 
20 |Andrej |22 |Gejza 
21 |Peter |22 |Gejza  

vsetkych mrtvych
select c.id,c.meno from pm_clen c where c.datum_do is not null 

a kolko sa dozili
select c.id,c.meno, YEAR(c.datum_do-c.datum_od)
from rn_clen c 
where c.datum_do is not null 
1 |Jan |58 
2 |Juraj |42 
3 |Jozef |41 
4 |Frantisek |40 
5 |Juraj |19 
6 |Aladar |18 
7 |Arpad |17 
8 |Milan |16 
9 |Ngu-Bo |15 
11 |Mikulas |33 
14 |Jozef |20 
15 |Viliam |19 
16 |Pavol |18  

vek kazdeho cloveka
CASE
  WHEN datum_do is null
  THEN 2001-YEAR(datum_od)
  ELSE ...
END;
YEAR(COALESCE(datum_do, 1.1.2001, datum_od));
COALESCE - prva nenulova hodnota vo vymenovanom zozname
