 ####   ##                   #                 ##  ##   ##                   
######  ##                  ##                 ##  ##   ##                   
##      #####  ####  ##### #### #####  ###     ##  ##   ##                   
##      ## ##  # ##  ## ##  ##  ##  ## ##      ##  ##   ##                  
##  ##  ## ## #####  ## ##  ##  ###### ##      ##  ##   ##                   
######  ## ## ## ##  ## ##  ##  ## ### ##      ##   ## ##   ##                 
 #####  ## ## #####  #####  ###  ####  ##      ##    ###    ##               
                     ##                                                  
23.10.2001           ##                                                  

opat dropneme svoje tabulky
(nezabudnut dropnut Primary Key na ID v RN_MESTA:
ALTER TABLE RN_mesto DROP PRIMARY KEY)

zmenime strukturu a zadame nove data

CREATE TABLE RN_student (
  id          integer      not null,
  meno        varchar(10)  not null,
  priezvisko  varchar(20)  not null,
  datumnarod  date         not null,
  idmesto     integer      not null,
  rocnik      integer      not null,
  idskupina   integer      not null,
  pohlavie    char(1)      not null,
  izba        char(5)              ,
  idtypizby   integer              ,
  primary key (id)
);
CREATE TABLE RN_mesto (
  id          integer      not null,
  nazov       varchar(20)  not null,
  primary key (id)
);
CREATE TABLE RN_skupina (
  id          integer      not null,
  kod         char(5)      not null,
  nazov       varchar(30)  not null,
  primary key (id)
);
CREATE TABLE RN_typizby (
  id          integer      not null,
  kod         char(5)      not null,
  kapacita    integer      not null,
  poplatok    decimal(5,2) not null,
  primary key (id)
);
alter TABLE RN_student add foreign key cRN_mesto (idmesto) references RN_mesto;
alter TABLE RN_student add foreign key cRN_skupina (idskupina) references RN_skupina;
alter TABLE RN_student add foreign key cRN_typizby (idtypizby) references RN_typizby;

*****
crN_Mesto - moje meno pre constraint
****
INSERT INTO RN_mesto VALUES
  (1, 'Kosice'),
  (2, 'Bardejov'),
  (3, 'Bagdad'),
  (4, 'Trebisov'),
  (5, 'Presov');
INSERT INTO RN_skupina VALUES
  (1, 'M', 'Matematika'),
  (2, 'MI', 'Matematicka informatika'),
  (3, 'MF', 'Matematika-fyzika');
INSERT INTO RN_typizby VALUES
  (1, 'A', 2, 500.00),
  (2, 'B', 3, 450.00);
INSERT INTO RN_student VALUES
  (1, 'Jan', 'Hrasko', '1.1.1980', 1, 1, 2, 'M', '220A', 1),
  (2, 'Ruzena', 'Sipova', '2.2.1980', 2, 1, 3, 'F', NULL, NULL),
  (3, 'Ali', 'Baba', '11.12.1970', 3, 2, 1, 'M', '112B', 2),
  (4, 'Jan', 'Polienko', '4.7.1981', 1, 4, 1, 'M', '112B', 2),
  (5, 'Juraj', 'Trulo', '3.2.1982', 1, 5, 2, 'M', '112B', 2),
  (6, 'Juraj', 'Trulo', '7.11.1983', 4, 5, 3, 'M', '220A', 1);

vyselektujme si 
meno, priezvisko, mesto na akej izbe byva
SELECT meno, priezvisko, m.nazov, izba FROM RN_STUDENT s, RN_mesto m
WHERE s.idmesto=m.id

Jan |Hrasko |Kosice |220A 
Jan |Polienko |Kosice |112B 
Juraj |Trulo |Kosice |112B 
Ruzena |Sipova |Bardejov | 
Ali |Baba |Bagdad |112B 
Juraj |Trulo |Trebisov |220A  

chceme vediet, kolko za nu platia:
SELECT meno, priezvisko, m.nazov, izba, i.poplatok 
FROM RN_STUDENT s, RN_mesto m, RN_typizby i
WHERE s.idmesto=m.id AND idtypizby=i.id
Jan |Hrasko |Kosice |220A |500.00 
Juraj |Trulo |Trebisov |220A |500.00 
Jan |Polienko |Kosice |112B |450.00 
Juraj |Trulo |Kosice |112B |450.00 
Ali |Baba |Bagdad |112B |450.00  

(Ruzena Sipovu, ma IDTYPIZBY=null)

ak chceme vidiet aj Ruzenu Sipovu:
Spajanie tabuliek inak ako cez WHERE - Alter Join
-----------------------------------
SELECT meno, priezvisko, m.nazov, izba
FROM rn_student s
JOIN rn_mesto m ON s.idmesto=m.id
Jan |Hrasko |Kosice |220A 
Jan |Polienko |Kosice |112B 
Juraj |Trulo |Kosice |112B 
Ruzena |Sipova |Bardejov | 
Ali |Baba |Bagdad |112B 
Juraj |Trulo |Trebisov |220A  

aj poplatok:
SELECT meno, priezvisko, m.nazov, izba
FROM rn_student s
JOIN rn_mesto m ON s.idmesto=m.id
JOIN rn_typizby i ON s.idtypizby=i.id
???????
zatial ako pri selecte
LEFT OUTER JOIN - RIGHT OUTER JOIN

SELECT meno, priezvisko, m.nazov, izba
FROM rn_student s
JOIN rn_mesto m ON s.idmesto=m.id
LEFT OUTER JOIN rn_typizby i ON s.idtypizby=i.id

JOIN zobrazi len tie hodnoty, ktore sa nachadzaju
v oboch tabulkach. Left Outer Join k hodnotam
z A priradi (zobrazi) hodnoty B, ak existuju. 
Ak neexistuju, zobrazi hodnoty A.
Podobne RIGHT OUTER JOIN.

SELECT meno, priezvisko, m.nazov
FROM rn_student s
RIGHT OUTER JOIN rn_mesto m ON s.idmesto=m.id
Jan    |Hrasko   |Kosice 
Jan    |Polienko |Kosice 
Juraj  |Trulo    |Kosice 
Ruzena |Sipova   |Bardejov 
Ali    |Baba     |Bagdad 
Juraj  |Trulo    |Trebisov 
       |         |Presov  

Kosicania
FROM rn_student s
JOIN rn_mesto m ON s.idmesto=m.id
LEFT OUTER JOIN rn_typizby i ON s.idtypizby=i.id
WHERE m.nazov='Kosice'

alebo

FROM rn_student s
JOIN rn_mesto m ON (s.idmesto=m.id AND m.nazov='Kosice')
LEFT OUTER JOIN rn_typizby i ON s.idtypizby=i.id

pohlavie v RN_STUDENT je oznacene F, M
chceme, aby zobrazovalo 'muz', 'zena'

prikaz CASE
-----------
SELECT meno, priezvisko, (CASE pohlavie WHEN 'M' THEN 'muz' WHEN 'F' THEN 'zena' END)
FROM rn_student

Jan |Hrasko |muz 
Ruzena |Sipova |zena 
Ali |Baba |muz 
Jan |Polienko |muz 
Juraj |Trulo |muz 
Juraj |Trulo |muz  

pozor na case sensitivity! 'm' nie je 'M'

vytvorme zoznam studentov. ak byva na intraku, napiste cislo izby, inak napiste 'nebyva'

SELECT meno, priezvisko, (CASE WHEN izba IS NULL THEN 'nebyva' ELSE izba END) 
FROM RN_STUDENT

Jan |Hrasko |220A 
Ruzena |Sipova |nebyva 
Ali |Baba |112B 
Jan |Polienko |112B 
Juraj |Trulo |112B 
Juraj |Trulo |220A  

vsetci, ktori su MI
SELECT st.meno, st.priezvisko, sk.nazov 
FROM rn_student st, rn_skupina sk
WHERE st.idskupina=sk.id AND sk.kod='MI'
Jan |Hrasko |Matematicka informatika 
Juraj |Trulo |Matematicka informatika  

taki, kt. studuju MI alebo matematiku
SELECT st.meno, st.priezvisko, sk.nazov 
FROM rn_student st, rn_skupina sk
WHERE st.idskupina=sk.id AND (sk.kod='MI' OR sk.kod='M')
Ali |Baba |Matematika 
Jan |Polienko |Matematika 
Jan |Hrasko |Matematicka informatika 
Juraj |Trulo |Matematicka informatika  

inak
SELECT st.meno, st.priezvisko, sk.nazov 
FROM rn_student st, rn_skupina sk
WHERE kod IN ('M','MI')

vyber z mnoziny
IN(...mnozina...)

kolko penazi sa vyzbiera za typ izby A a typ izby
SELECT i.kod, sum(poplatok)
FROM rn_student s
JOIN rn_typizby i on s.idtypizby = i.id
GROUP BY i.kod;
A |1000.00 
B |1350.00  
samozrejme, ale preco?

COUNT, SUM, MAX, MIN
mozno aj spajat ( MAX(SUM)) )

Here endeth the lesson 4.