
 ####   ##                   #                 ##  ##  ##                    
######  ##                  ##                 ##  ##  ##                    
##      #####  ####  ##### #### #####  ###     ##  ##  ##                    
##      ## ##  # ##  ## ##  ##  ##  ## ##      ##  ##  ##                    
##  ##  ## ## #####  ## ##  ##  ###### ##      ##  ##  ##                    
######  ## ## ## ##  ## ##  ##  ## ### ##      ##  ##  ##  ##                   
 #####  ## ## #####  #####  ###  ####  ##      ##  ##  ##  ##                  
                     ##                                                  
16.10.2001           ##         

chceme evidovat, ci byva na nejakej izbe
mozme to zvolit ako dalsi atribut
(vo vseob. v DB2 sa neda zmenit typ atributu, da sa vsak pridat novy)
ALTER TABLE rn_student ADD [COLUMN] izba CHAR(5);

Nova struktura:
ID |INTEGER |4 |N 
MENO |VARCHAR |10 |N 
PRIEZVISKO |VARCHAR |20 |N 
DATUXXAROD |DATE |4 |N 
IDMESTO |INTEGER |4 |N 
IZBA |CHAR |5 |Y  

typ CHAR(5) - pre kazdy zaznam je automaticky alokovany priestor 5 znakov
    VARCHAR(20) - realne zaberie v databaze velkost podla dlzky polozky 
                  atributu, nie 20

doplnime data:
UPDATE rn_student SET izba = '120A' WHERE id = 1;
UPDATE rn_student SET izba = '220B' WHERE id = 3;
UPDATE rn_student SET izba = '220B' WHERE id = 4;
UPDATE rn_student SET izba = '220B' WHERE id = 5;
UPDATE rn_student SET izba = '120A' WHERE id = 6;

po SELECTE
1|Jan   |Hrasko  |1980-01-01|1|120A
2|Ruzena|Sipova  |1980-02-02|2|
3|Ali   |Baba    |1970-12-11|3|220B
4|Jan   |Polienko|1981-07-04|1|220B
5|Juraj |Trulo   |1982-02-03|1|220B
6|Juraj |Trulo   |1983-11-07|4|120A

vypisme vsetkych studentov, ktori byvaju na internate
SELECT meno, priezvisko FROM rn_student WHERE izba IS NOT NULL
Jan |Hrasko 
Ali |Baba 
Jan |Polienko 
Juraj |Trulo 
Juraj |Trulo  

chceme vybrat dvojice studentov, ktori byvaju spolu na izbe
SELECT s1.meno, s1.priezvisko, s2.meno, s2.priezvisko
FROM rn_student s1, rn_student s2
WHERE s1.izba=s2.izba
(kartez. sucin z 1 tabulky)
Jan   |Hrasko   |Jan |Hrasko 
Juraj |Trulo    |Jan |Hrasko 
Jan   |Hrasko   |Juraj |Trulo 
Juraj |Trulo    |Juraj |Trulo 
Ali   |Baba     |Ali |Baba 
Jan   |Polienko |Ali |Baba 
Juraj |Trulo    |Ali |Baba 
Ali   |Baba     |Jan |Polienko 
Jan   |Polienko |Jan |Polienko 
Juraj |Trulo    |Jan |Polienko 
Ali   |Baba     |Juraj |Trulo 
Jan   |Polienko |Juraj |Trulo 
Juraj |Trulo    |Juraj |Trulo  

opravime, aby nevypisovalo zbytocne ludi, ktori byvaju sami so sebou...

SELECT s1.meno, s1.priezvisko, s2.meno, s2.priezvisko
FROM rn_student s1, rn_student s2
WHERE s1.izba=s2.izba AND
      s1.id <> s2.id

Juraj |Trulo |Jan |Hrasko 
Jan |Hrasko |Juraj |Trulo 
Jan |Polienko |Ali |Baba 
Juraj |Trulo |Ali |Baba 
Ali |Baba |Jan |Polienko 
Juraj |Trulo |Jan |Polienko 
Ali |Baba |Juraj |Trulo 
Jan |Polienko |Juraj |Trulo  

opravime, aby nevypisovalo zbytocne duplicitne vzajomne byvajucich ludi...
SELECT s1.meno, s1.priezvisko, s2.meno, s2.priezvisko
FROM rn_student s1, rn_student s2
WHERE s1.izba=s2.izba AND
      s1.id < s2.id
Jan |Hrasko |Juraj |Trulo 
Ali |Baba |Jan |Polienko 
Ali |Baba |Juraj |Trulo 
Jan |Polienko |Juraj |Trulo  

vypisme 
izbu, kto, odkial, byva s, jeho mesto
SELECT s1.izba, s1.meno, s1.priezvisko, s1.datuxxarod, 
       m.nazov, s2.meno,  s2.priezvisko, m2.nazov, s2.datuxxarod
FROM rn_student s1, rn_student s2, rn_mesto m, rn_mesto m2
WHERE s1.izba=s2.izba AND
      s1.id < s2.id AND
      s1.idmesto=m.id AND
      s2.idmesto=m2.id

220B |Jan |Polienko |1981-07-04 |Kosice |Juraj |Trulo |Kosice |1982-02-03 
220B |Ali |Baba |1970-12-11 |Bagdad |Jan |Polienko |Kosice |1981-07-04 
220B |Ali |Baba |1970-12-11 |Bagdad |Juraj |Trulo |Kosice |1982-02-03 
120A |Jan |Hrasko |1980-01-01 |Kosice |Juraj |Trulo |Trebisov |1983-11-07  

Here endeth the lesson 3.