Prologue
http://rnml.host.sk
-*-*-*-*-*-*-*-*-*-

 ####   ##                   #                 ##                        
######  ##                  ##                 ##                        
##      #####  ####  ##### #### #####  ###     ##                        
##      ## ##  # ##  ## ##  ##  ##  ## ##      ##                        
##  ##  ## ## #####  ## ##  ##  ###### ##      ##                        
######  ## ## ## ##  ## ##  ##  ## ### ##      ##  ##                    
 #####  ## ## #####  #####  ###  ####  ##      ##  ##                    
                     ##                                                  
2.10.2001            ##                                                  
                                                                  
(C) M.N. 2001
!!!!!!!!!!!!!!!!!!!!!!!Do Not Distribute Or Use!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!Usage strictly prohibited!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
======================
//vyuka.science.upjs.sk - klient
-*-*-*-*-*-*-*-*-*-*-*-
SQL - structured query language
data su ulozene v tabulkach - pristup pomocou mena tabulky - nezaujimave poradie
tabulky organizovane v stlpcoch - bez poradia
search - vpravo hore - vyhladavanie tabuliek
pozn> vhodne davat pred nazov tabulky prefix

buttony>
select - SELECT
sql - insert,update prikazy
clear

prikaz SELECT

SELECT co FROM meno_tabulky

pr: SELECT * FROM mn_student
do velkeho txt pola!!!
vysledok>
Jan    |Hrasko 
Ruzena |Sipova 
Ali    |Baba 
Jan    |Polienko 
Juraj  |Trulo  

utriedenie podla priezviska - ORDER BY
SELECT * FROM mn_student ORDER BY priezvisko
Ali    |Baba 
Jan    |Hrasko 
Jan    |Polienko 
Ruzena |Sipova 
Juraj  |Trulo  

SELECT * FROM mn_student ORDER BY priezvisko ASC - ascending - vzostupne
SELECT * FROM mn_student ORDER BY priezvisko DESC - descending - zostupne
Ali    |Baba 
Jan    |Hrasko 
Jan    |Polienko 
Ruzena |Sipova 
Juraj  |Trulo  

ORDER BY 2 - utriedenie podla 2. polozky (priezvisko)

utriedenie len priezvisk podla abecedy:
SELECT priezvisko FROM mn_student ORDER BY priezvisko


SELECT priezvisko,meno FROM mn_student ORDER BY meno,priezvisko
SELECT priezvisko,meno FROM mn_student ORDER BY 1,2

Konkatenacia || (niekde +)
zotriedenie mena a priezviska ako jednej polozky - su to znaky
SELECT meno||' '||priezvisko FROM mn_student

Jan Hrasko 
Ruzena Sipova 
Ali Baba 
Jan Polienko 
Juraj Trulo  

pozn.konkatenovat mozno len rovnake typy (nie cislo a string)
SELECT meno||' '||priezvisko FROM mn_student ORDER BY 1 (uz nefunguje ORDER BY 2)
teda
SELECT meno||' '||priezvisko FROM mn_student ORDER BY priezvisko,meno
Ali Baba 
Jan Hrasko 
Jan Polienko 
Ruzena Sipova 
Juraj Trulo  

vyberme priezvisko,cele meno,usporiadajme podla 1.stlpca
SELECT priezvisko,meno||' '||priezvisko FROM mn_student ORDER BY 1
Baba     |Ali Baba 
Hrasko   |Jan Hrasko 
Polienko |Jan Polienko 
Sipova   |Ruzena Sipova 
Trulo    |Juraj Trulo  

--
vezmime z mena prvy znak - funkcia SUBSTR (pole,pociatoc_index,pocet_znakov)
SELECT priezvisko,SUBSTR(meno,1,1)||'. '||priezvisko FROM mn_student
Hrasko   |J. Hrasko 
Sipova   |R. Sipova 
Baba     |A. Baba 
Polienko |J. Polienko 
Trulo    |J. Trulo  

cv:Hrasko,J.H., usporiadat podla priezviska
SELECT priezvisko || ', ' || SUBSTR(meno,1,1)||'. '||SUBSTR(priezvisko,1,1)||'.' FROM mn_student ORDER BY 1
Baba, A. B. 
Hrasko, J. H. 
Polienko, J. P. 
Sipova, R. S. 
Trulo, J. T.  

--
klauzula WHERE (filter zaznamov)
SELECT meno,priezvisko FROM mn_student WHERE meno='Jan'
Jan |Hrasko 
Jan |Polienko  


vyberie ostatnych:
SELECT meno,priezvisko FROM mn_student WHERE meno<>'Jan'
Ruzena |Sipova 
Ali    |Baba 
Juraj  |Trulo  

vsetci studenti, ktorych priezvisko zacina na 'B' - LIKE
SELECT meno,priezvisko FROM mn_student WHERE priezvisko LIKE 'B%'
Ali |Baba  

% nahradza skupinu (0+) znakov
priezvisko ma koncit na 'a'
SELECT meno,priezvisko FROM mn_student WHERE priezvisko LIKE '%a'

_ nahradza prave jeden znak (niekde ?)
SELECT meno,priezvisko FROM mn_student WHERE priezvisko LIKE '_a%'
alt. WHERE SUBSTR(priezvisko,2,1)='a' - 2.znak ma byt 'a'
Ali |Baba  
--
spajanie obmedzeni - AND
SELECT meno,priezvisko FROM mn_student WHERE priezvisko LIKE 'H%' AND meno='Jan'
Jan |Hrasko  

spojka OR:
SELECT meno,priezvisko FROM mn_student WHERE priezvisko LIKE 'H%' OR meno='Jan'
Jan |Hrasko 
Jan |Polienko  

pozn: mozno aj spravne uzatvorkovat

--
prikaz COUNT - zistovanie poctu
SELECT COUNT(*) FROM mn_student
5

SELECT COUNT(*) FROM mn_student WHERE meno='Jan'
2

--
kolko jednoznacnych mien je v databaze? - prikaz DISTINCT
SELECT DISTINCT meno FROM mn_student
Ali 
Jan 
Juraj 
Ruzena  

pocet roznych krstnych mien
SELECT COUNT(DISTINCT meno) FROM mn_student
4

--
repetitorium
SELECT, ||, COUNT, DISTINCT, FROM, WHERE, AND/OR, LIKE, ORDER BY

----------------------------------------------
vytvorenie tabulky - CREATE
CREATE TABLE meno_tabulky (atribut typ_atributu...)

CREATE TABLE mn_student(
  meno VARCHAR(10), 
  priezvisko VARCHAR(20)
);
; oddeluje viacero prikazov

typy udajov> vid HELP
==============================================================
odporuca sa zaroven pisat prikazy na zrusenie! (upratovanie - cleanup)
zmazanie tabulky - DROP
DROB TABLE meno_tabulky

klikat na tlacidlo SQL nie na SELECT
okno ukaze spravu - uspech/chyba
====================

vkladanie dat do tabuliek - prikaz INSERT


INSERT INTO rn_student VALUES('Jan','Hrasko'),('Juraj','Janosik');

SELECT * FROM rn_student
Jan   |Hrasko
Juraj |Janosik 

editovanie zaznamov - prikaz UPDATE
zmenme priezvisko Jana Hraska na Nemcek
UPDATE rn_student SET priezvisko='Nemcek' WHERE priezvisko='Hrasko'

SELECT * FROM rn_student
Jan   |Nemcek 
Juraj |Janosik  

===
vymazanie dat - prikaz DELETE

vymazme vsetkych Nemcekov z tabulky
DELETE FROM rn_student WHERE priezvisko='Nemcek'

=========================
repetitorium II
SELECT, ||, COUNT, DISTINCT, FROM, WHERE, AND/OR, LIKE, ORDER BY
--
CREATE,DROP
INSERT,UPDATE,DELETE

Here endeth the lesson 1.


 ####   ##                   #                 ##  ##                      
######  ##                  ##                 ##  ##                      
##      #####  ####  ##### #### #####  ###     ##  ##                      
##      ## ##  # ##  ## ##  ##  ##  ## ##      ##  ##                      
##  ##  ## ## #####  ## ##  ##  ###### ##      ##  ##                      
######  ## ## ## ##  ## ##  ##  ## ### ##      ##  ##  ##                   
 #####  ## ## #####  #####  ###  ####  ##      ##  ##  ##                    
                     ##                                                  
9.10.2001            ##         



ak mame Jana Hraska a Jozefa Hraska
DELETE FROM MN_STUDENT WHERE priezvisko='Hrasko'
zmaze oboch Hraskov.

Ak chceme zmazat len Jana Hraska
DELETE FROM MN_STUDENT WHERE priezvisko='Hrasko' AND meno='Jan'
nevyhoda pri velkom mnozstve atributov

dohoda
ako 1. atribut sa dava ID cislo (jednoznacny pristup k zaznamom)
MN_STUDENT (
  id INTEGER
..

rozsirime tabulku o nove polia a vety:

1 |Jan    |Hrasko   |1980-01-01 |Kosice 
2 |Ruzena |Sipova   |1980-02-02 |Bardejov 
3 |Ali    |Baba     |1970-12-11 |Bagdad 
4 |Jan    |Polienko |1981-07-04 |Kosice 
5 |Juraj  |Trulo    |1982-02-03 |KOSICE 
6 |Juraj  |Trulo    |1983-11-07 |Trebisov  

vidime dvoch Jurajov Trulov
zmazeme jedneho
DELETE FROM MN_STUDENT WHERE id=5

pocet ludi, ktory su z Kosic
SELECT COUNT(*) FROM MN_STUDENT_OLD WHERE mesto='Kosice'

2

nenajde cloveka z mesta KOSICE

riesenie:
- unifikacia dat (Kosice su vsade Kosice)
  - nevyhoda: pri opravach tazka udrzba
- dovazanie dat (MESTO bude v samostatnom ciselniku)

tabulky STUDENT (id, meno, pr., namiesto MESTO dame ID mesta)
        MESTO (id, nazov)

id meno pr.     idmesto            id nazov
1  Jan  Hrasko   1      <------>    1 Kosice

SELECT * FROM MN_STUDENT, MN_MESTO
1 |Jan |Hrasko |1980-01-01 |1 |1 |Kosice 
1 |Jan |Hrasko |1980-01-01 |1 |2 |Bardejov 
1 |Jan |Hrasko |1980-01-01 |1 |3 |Bagdad 
1 |Jan |Hrasko |1980-01-01 |1 |4 |Trebisov 
2 |Ruzena |Sipova |1980-02-02 |2 |1 |Kosice 
2 |Ruzena |Sipova |1980-02-02 |2 |2 |Bardejov 
2 |Ruzena |Sipova |1980-02-02 |2 |3 |Bagdad 
2 |Ruzena |Sipova |1980-02-02 |2 |4 |Trebisov 
3 |Ali |Baba |1970-12-11 |3 |1 |Kosice 
3 |Ali |Baba |1970-12-11 |3 |2 |Bardejov 
3 |Ali |Baba |1970-12-11 |3 |3 |Bagdad 
3 |Ali |Baba |1970-12-11 |3 |4 |Trebisov 
4 |Jan |Polienko |1981-07-04 |1 |1 |Kosice 
4 |Jan |Polienko |1981-07-04 |1 |2 |Bardejov 
4 |Jan |Polienko |1981-07-04 |1 |3 |Bagdad 
4 |Jan |Polienko |1981-07-04 |1 |4 |Trebisov 
5 |Juraj |Trulo |1982-02-03 |1 |1 |Kosice 
5 |Juraj |Trulo |1982-02-03 |1 |2 |Bardejov 
5 |Juraj |Trulo |1982-02-03 |1 |3 |Bagdad 
5 |Juraj |Trulo |1982-02-03 |1 |4 |Trebisov 
6 |Juraj |Trulo |1983-11-07 |4 |1 |Kosice 
6 |Juraj |Trulo |1983-11-07 |4 |2 |Bardejov 
6 |Juraj |Trulo |1983-11-07 |4 |3 |Bagdad 
6 |Juraj |Trulo |1983-11-07 |4 |4 |Trebisov  

v 1.tabulke 6 zaznamov, v meste 3 zaznamy => kartezsky sucin (18 zaznamov)

radsej prepojime tabulky>
SELECT * FROM MN_STUDENT, MN_MESTO WHERE MN_STUDENT.ID_MESTO=MN_MESTO.id

(spajanie tabuliek cez stlpce MN_STUDENT.ID_MESTO=MN_MESTO.id)


1 |Jan     |Hrasko   |1980-01-01 |1 |1 |Kosice 
4 |Jan     |Polienko |1981-07-04 |1 |1 |Kosice 
5 |Juraj   |Trulo    |1982-02-03 |1 |1 |Kosice 
2 |Ruzena  |Sipova   |1980-02-02 |2 |2 |Bardejov 
3 |Ali     |Baba     |1970-12-11 |3 |3 |Bagdad 
6 |Juraj   |Trulo    |1983-11-07 |4 |4 |Trebisov  

vyberme SELECT id, meno, priezvisko, nazov FROM MN_STUDENT, MN_MESTO WHERE MN_STUDENT.ID_MESTO=MN_MESTO.id

chyba
A reference to column "ID" is ambiguous
nejednoznacnost stlpcov
oprava
SELECT MN_STUDENT.id, meno, priezvisko, nazov FROM MN_STUDENT, MN_MESTO WHERE MN_STUDENT.ID_MESTO=MN_MESTO.id
1 |Jan    |Hrasko |Kosice 
4 |Jan    |Polienko |Kosice 
5 |Juraj  |Trulo |Kosice 
2 |Ruzena |Sipova |Bardejov 
3 |Ali    |Baba |Bagdad 
6 |Juraj  |Trulo |Trebisov  

AS .. aliasy - jednoduchy pristup k tabulkam
SELECT s.id, s.meno, m.meno FROM MN_student AS s, MN_mesto m WHERE s.id_mesto=m.id
??????

meno, priezvisko, datum_nar, tych ktori su z Kosic

SELECT s.meno, s.priezvisko, s.datum_nar FROM MN_student AS s, MN_mesto m WHERE s.id_mesto=m.id AND m.nazov='Kosice'
Jan   |Hrasko   |1980-01-01 
Jan   |Polienko |1981-07-04 
Juraj |Trulo    |1982-02-03  

vysledkom SELECT-u nech je 
Jan | Hrasko | 01.01.1980

SELECT s.meno || ' ' || s.priezvisko, CHAR(datum_nar,EUR) FROM MN_student AS s, MN_mesto m WHERE s.id_mesto=m.id AND m.nazov='Kosice'
(EUR .. europsky format datumu)

Jan Hrasko   |01.01.1980 
Jan Polienko |04.07.1981 
Juraj Trulo  |03.02.1982  

pocet studentov z Kosic
SELECT COUNT(*) FROM MN_student AS s, MN_mesto m WHERE s.id_mesto=m.id AND m.nazov='Kosice'
3

klauzula GROUP BY .. z ktoreho mesta je kolko studentov?
(pri agregacnych hodnotach)

SELECT m.nazov, COUNT(*) FROM MN_student s, MN_mesto m WHERE s.id_mesto=m.id GROUP BY m.nazov
Bagdad   |1 
Bardejov |1 
Kosice   |3 
Trebisov |1  

len take mesta, kde je viac ako 1 student
SELECT m.nazov, COUNT(*) FROM MN_student s, MN_mesto m WHERE s.id_mesto=m.id GROUP BY m.nazov HAVING count(*) > 1
Kosice |3  

vsetci studenti, ktori sa narodili po roku 1981
SELECT s.meno || ' ' || s.priezvisko, CHAR(datum_nar,EUR) FROM MN_student AS s, MN_mesto m WHERE s.id_mesto=m.id AND s.datum_nar>='1981-01-01'
Jan Polienko |04.07.1981 
Juraj Trul o |03.02.1982 
Juraj Trulo  |07.11.1983  

ohraniceny datum (len v roku 1981)
SELECT s.meno || ' ' || s.priezvisko, CHAR(datum_nar,EUR) FROM MN_student AS s, MN_mesto m WHERE s.id_mesto=m.id AND s.datum_nar>='1981-01-01' AND s.datum_nar<='1982-12-31'
Jan Polienko |04.07.1981
Juraj Trulo  |03.02.1982

alebo (klauzula BETWEEN)
SELECT s.meno || ' ' || s.priezvisko, CHAR(datum_nar,EUR) FROM MN_student AS s, MN_mesto m WHERE s.id_mesto=m.id AND s.datum_nar BETWEEN '1981-01-01' AND '1982-12-31'

-----------------------------
tabulka MN_MESTO
1 |Kosice 
2 |Bardejov 
3 |Bagdad 
4 |Trebisov  

vlozme do tabulky znova zaznam 1 |Kosice
INSERT INTO MN_MESTO VALUES(1,'Kosice);
1 |Kosice 
2 |Bardejov 
3 |Bagdad 
4 |Trebisov 
1 |Kosice  

NOT NULL, primarny kluc
------------
(duplicitny zaznam - casto nevhodna situacia)
ID by mal byt jednoznacny
mal by sa zvolit PRIMARNY KLUC - aby sa dal vlozit zaznam s jednoznacnym ID
1.) ALTER TABLE MN_MESTO ADD PRIMARY KEY (id)    (button SQL)
chyba "ID" cannot be a column of a primary key or unique key because it can contain null values"
- primarny kluc nemoze obsahovat prazdne/null hodnoty
pri vytvarani tabulky treba uviest
CREATE TABLE MN_MESTO (id INTEGER not null, ....)
potom nemozno vlozit zaznam bez zadania ID.
potom uz mozno zvolit primarny kluc
CREATE TABLE MN_MESTO (id INTEGER not null PRIMARY KEY(id))


priprava vlastnej tabulky
------------------------
CREATE TABLE RN_student (
  id          integer      not null,
  meno        varchar(10)  not null,
  priezvisko  varchar(20)  not null,
  datuXXarod  date         not null,
  idmesto     integer      not null,
  primary key (id)
);
CREATE TABLE RN_mesto (
  id          integer      not null,
  nazov       varchar(20)  not null,
  primary key (id)
);

INSERT INTO RN_student VALUES
  (1, 'Jan', 'Hrasko', '1.1.1980', 1),
  (2, 'Ruzena', 'Sipova', '2.2.1980', 2),
  (3, 'Ali', 'Baba', '11.12.1970', 3),
  (4, 'Jan', 'Polienko', '4.7.1981', 1),
  (5, 'Juraj', 'Trulo', '3.2.1982', 1),
  (6, 'Juraj', 'Trulo', '7.11.1983', 4);

INSERT INTO RN_mesto VALUES
  (1, 'Kosice'),
  (2, 'Bardejov'),
  (3, 'Bagdad'),
  (4, 'Trebisov'),
  (5, 'Presov');
----------------------

zoznam poli>
ID         |INTEGER |4  |N 
MENO       |VARCHAR |10 |N 
PRIEZVISKO |VARCHAR |20 |N 
DATUXXAROD |DATE    |4  |N 
IDMESTO    |INTEGER |4  |N  

PRIMARY KEY (ID)  
UNIQUE ()  

Constraints:  
SQL011009155954890 |RN_STUDENT |P |ID  

References:  

------
N .. not null atribut
Constraints - obmedzenie na jednoznacnost, generovane DB2, na aku tabulku, primarny kluc, atribut ID


INSERT INTO RN_STUDENT VALUES(1,'Jan','Hrasko','1.1.1980',1)    
Chyba: "One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because they would produce duplicate rows for a table with a primary key, unique constraint, or unique index."
kedze pod ID=1 je uz student, zafungovalo obmedzenie na primarny kluc

rovnako nemozno vlozit Kosice do mesta

ale
INSERT INTO RN_STUDENT VALUES(7,'Jan','Hrasko','1.1.1980',10)
1 |Jan    |Hrasko   |1980-01-01 |1
2 |Ruzena |Sipova   |1980-02-02 |2
3 |Ali    |Baba     |1970-12-11 |3
4 |Jan    |Polienko |1981-07-04 |1
5 |Juraj  |Trulo    |1982-02-03 |1
6 |Juraj  |Trulo    |1983-11-07 |4
7 |Janko  |Hrasko   |1980-01-01 |10
funguje, hoci ID je neexistujuceho mesta

vyselektujme vsetko teraz
SELECT * FROM RN_STUDENT s, RN_MESTO m WHERE s.ID_MESTO=m.id
vyselektuje 6 studentov, hoci je ich 7
????????

teda to STUDENT chceme vkladat len take udaje aby ID_MESTO bola existujuca hodnota so zaznamom v MESTO
Constraint

ALTER TABLE RN_STUDENT ADD CONSTRAINT RN_idmesta FOREIGN KEY (IDMESTO) REFERENCES RN_MESTO
Chyba "The FOREIGN KEY "RN_IDMESTA" cannot be created because the table contains rows with foreign key values that cannot be found in the parent key of the parent table. "

zmazeme nevhodny zaznam s ID=7
teraz je
ALTER TABLE...
ok

TABLE: RN_STUDENT

ID |INTEGER |4 |N 
MENO |VARCHAR |10 |N 
PRIEZVISKO |VARCHAR |20 |N 
DATUXXAROD |DATE |4 |N 
IDMESTO |INTEGER |4 |N  

PRIMARY KEY (ID)  
UNIQUE ()  

Constraints:  
RN_IDMESTA |RN_STUDENT |F |IDMESTO           .. pribudol novy constraint, F .. cudzi kluc
SQL011009155954890 |RN_STUDENT |P |ID  

References:  
RN_IDMESTA |RN_MESTO |A |IDMESTO |ID         .. nazov referencie, na aku tabulku, ? , atribut

-----
TABLE: RN_MESTO

ID |INTEGER |4 |N 
NAZOV |VARCHAR |20 |N  

PRIMARY KEY (ID)  
UNIQUE ()  

Constraints:  
SQL011009160023120 |RN_MESTO |P |ID  

References:  

--
opat spustime
INSERT INTO RN_STUDENT VALUES(7,'Jan','Hrasko','1.1.1980',10)

chyba:"The insert or update value of the FOREIGN KEY "VYUKA.RN_STUDENT.RN_IDMESTA" is not equal to any value of the parent key of the parent table."
lebo nemozno vlozit zaznam bez referencie na tabulku (idmesta=10 neexistuje)
ochrana databazy zavisi od navrhu

skusme
DELETE FROM RN_MESTO WHERE ID=1
Chyba: "A parent row cannot be deleted because the relationship "VYUKA.RN_STUDENT.RN_IDMESTA" restricts the deletion."
Kedze existuje referencia do RN_STUDENT, nemozno zmazat.

-----
referencna integrita brani vkladat a mazat udaje, ktore porusuju integritu

referencie sa daju zakladat uz pri vytvarani tabuliek, pri modifikacii struktury je lepsie zakladat neskor.

skusme vymazat mesto Presov
DELETE FROM RN_MESTO WHERE NAZOV='Presov'
zaznam ktory nie je nicim spojeny, mozno vymazat

na zaver zhodime referenciu:
ALTER TABLE RN_STUDENT DROP FOREIGN KEY RN_idmesta

Here endeth the lesson 2.
------
 ####   ##                   #                 ##  ##  ##                    
######  ##                  ##                 ##  ##  ##                    
##      #####  ####  ##### #### #####  ###     ##  ##  ##                    
##      ## ##  # ##  ## ##  ##  ##  ## ##      ##  ##  ##                    
##  ##  ## ## #####  ## ##  ##  ###### ##      ##  ##  ##                    
######  ## ## ## ##  ## ##  ##  ## ### ##      ##  ##  ##  ##                   
 #####  ## ## #####  #####  ###  ####  ##      ##  ##  ##  ##                  
                     ##                                                  
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  


 ####   ##                   #                 ##  ##   ##                   
######  ##                  ##                 ##  ##   ##                   
##      #####  ####  ##### #### #####  ###     ##  ##   ##                   
##      ## ##  # ##  ## ##  ##  ##  ## ##      ##  ##   ##                  
##  ##  ## ## #####  ## ##  ##  ###### ##      ##  ##   ##                   
######  ## ## ## ##  ## ##  ##  ## ### ##      ##   ## ##   ##                 
 #####  ## ## #####  #####  ###  ####  ##      ##    ###    ##               
                     ##                                                  
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)) )
