<pre>
             ################       #################        #############      
             ##################     #######    ########    ######    #######    
             #######     #######    #######     #######    ########             
             #######      #######   ###############         ###############     
             #######      #######   #######    ########          ############   
             #######     #######    #######      #######  #######      ######   
             ##################     ###################    #################    
             ###############        #################        #############                                                                                 
             
                 D  A  T  A  B    Z  O  V      S  Y  S  T    M  Y
             
             

Zopar uzitocnych URL                                   

SQL - structured query language

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

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

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

maximalny poplatok za izbu
SELECT max(poplatok)
FROM rn_typizby i;

studenti ktori platia maximalnu sumu
SELECT *
FROM rn_typizby i,rn_student s
WHERE s.idtypizby=i.id AND i.poplatok=500;

ale co ked chceme dynamicky select poplatku
SELECT *
FROM rn_typizby i,rn_student s
WHERE s.idtypizby=i.id AND 
i.poplatok=(SELECT max(poplatok) FROM rn_typizby i);
1 |A |2 |500.00 |1 |Jan |Hrasko |1980-01-01 |1 |1 |2 |M |220A |1 
1 |A |2 |500.00 |6 |Juraj |Trulo |1983-11-07 |4 |5 |3 |M |220A |1  

najstarsi student - meno, priezvisko, dat. nar
SELECT meno,priezvisko,datumnarod FROM rn_student
WHERE datumnarod=(SELECT min(datumnarod) FROM rn_student)
Ali |Baba |1970-12-11  

meno priezvisko odbor
SELECT st.meno, st.priezvisko, sk.nazov 
FROM rn_student st, rn_skupina sk
WHERE st.idskupina=sk.id 
Ali |Baba |Matematika 
Jan |Polienko |Matematika 
Jan |Hrasko |Matematicka informatika 
Juraj |Trulo |Matematicka informatika 
Ruzena |Sipova |Matematika-fyzika 
Juraj |Trulo |Matematika-fyzika  

zmenme vsetkym MF na MI
?????????
select odbor.kod, std.meno, std.priezvisko, std.datumnarod
from rn_student as s, rn_skupina as odbor
where std.idskupina = odbor.id

zmenme select aby vypisal vsetky odbory a studujucich na nich
select odbor.kod, std.meno, std.priezvisko, std.datumnarod 
from ml_student as std 
RIGHT OUTER JOIN ml_skupina AS odbor ON std.idskupina = odbor.id;

na ktorom odbore nie je ziaden student
SELECT sk.kod
FROM rn_skupina sk
WHERE sk.id in (SELECT DISTINCT st.idskupina FROM rn_student st)
M 
MI 
MF  

alebo:
SELECT sk.kod, sk.id
FROM rn_skupina sk
WHERE NOT EXISTS (SELECT * FROM rn_student st WHERE st.idskupina=sk.id)

vyselektujme studentov, usporiadajme podla datumu narodenia
SELECT st.meno, st.priezvisko 
FROM rn_student st
ORDER BY st.datumnarod DESC
Juraj |Trulo 
Juraj |Trulo 
Jan |Polienko 
Ruzena |Sipova 
Jan |Hrasko 
Ali |Baba  

ocislujme ich
(v kazdom kroku vieme spocitat mladsich..)
SELECT (SELECT COUNT(*) FROM rn_student s2 WHERE s1.datumnarod<=s2.datumnarod), s1.meno, s1.priezvisko
FROM rn_student s1
ORDER BY s1.datumnarod DESC
1 |Juraj |Trulo 
2 |Juraj |Trulo 
3 |Jan |Polienko 
4 |Ruzena |Sipova 
5 |Jan |Hrasko 
6 |Ali |Baba  

3 najstarsi ludia
SELECT s1.meno, s1.priezvisko
FROM rn_student s1
WHERE (SELECT COUNT(*) FROM rn_student s2 WHERE s1.datumnarod<=s2.datumnarod)<4
ORDER BY s1.datumnarod DESC
Juraj |Trulo 
Juraj |Trulo 
Jan |Polienko  

WITH
"prosim, pozrite si v helpe" ####   ##                   #               ##   ##  ##                
######  ##                  ##               ##   ##  ##                 
##      #####  ####  ##### #### #####  ###   ##   ##  ##                 
##      ## ##  # ##  ## ##  ##  ##  ## ##    ##   ##  ##                
##  ##  ## ## #####  ## ##  ##  ###### ##    ##   ##  ##                 
######  ## ## ## ##  ## ##  ##  ## ### ##     ## ##   ##  ##                 
 #####  ## ## #####  #####  ###  ####  ##      ###    ##  ##             
                     ##                                                  
6.11.2001            ##                                                  

napiste SELECT
1. Ali Baba | datum.nar
..
SELECT CAST( (
  SELECT COUNT(*) FROM rn_student s2 
  WHERE s1.datumnarod<=s2.datumnarod)
AS CHAR)|| '. ' || s1.meno || ' ' || s1.priezvisko, CHAR(s1.datumnarod,EUR)
  FROM rn_student s1
ORDER BY s1.datumnarod DESC

alt.
SELECT CHAR( (
  SELECT COUNT(*) FROM rn_student s2 
  WHERE s1.datumnarod<=s2.datumnarod))
  || '. ' || s1.meno || ' ' || s1.priezvisko, CHAR(s1.datumnarod,EUR)
  FROM rn_student s1
ORDER BY s1.datumnarod DESC

alt.
SELECT (
  SELECT CHAR(COUNT(*)) FROM rn_student s2 
  WHERE s1.datumnarod<=s2.datumnarod)
  || '. ' || s1.meno || ' ' || s1.priezvisko, CHAR(s1.datumnarod,EUR)
  FROM rn_student s1
ORDER BY s1.datumnarod DESC

chceme
1.|Ali Baba      |Bagdad
2.|Ruzena Sipova |Kosice
(sortovane podla mesta)

SELECT
  (SELECT count(*) 
   FROM rn_student s2 
   WHERE s2.idmesto=m1.id AND
   s2.priezvisko <= s1.priezvisko 
  ),
  s1.meno || ' ' || s1.priezvisko, m1.nazov
FROM rn_student s1 
JOIN rn_mesto m1 ON s1.idmesto=m1.id
ORDER BY m1.nazov, s1.priezvisko
1 |Ali Baba |Bagdad 
1 |Ruzena Sipova |Bardejov 
1 |Jan Hrasko |Kosice 
2 |Jan Polienko |Kosice 
3 |Juraj Trulo |Kosice 
1 |Juraj Trulo |Trebisov  

select char((select count(*) 
from mm_student st2,mm_mesto ms2 
where (st2.idmesto=ms2.id) and 
  (st2.priezvisko<=st1.priezvisko) and
  (st2.idmesto=st1.idmesto) 
group by st2.idmesto))
|| '.', st1.meno || ' ' || st1.priezvisko,ms1.nazov 

klauzula WITH
najdime najvacsi datum
SELECT MAX(datumnarod) from rn_student
1983-11-07  

druhy najvacsi datum
SELECT 

WITH novatabulka(najstarsi) AS (SELECT MAX(datumnarod) FROM rn_student)
SELECT max(datumnarod) FROM rn_student, novatabulka
WHERE novatabulka.najstarsi>datumnarod
1982-02-03  

2. najvacsi datum a menom

WITH m(najstarsi) AS
  (SELECT max(datumnarod) FROM rn_student)
SELECT meno, priezvisko, datumnarod
FROM rn_student,m
WHERE datumnarod=(SELECT max(s.datumnarod) FROM rn_student s  
WHERE s.datumnarod < m.najstarsi)
Juraj |Trulo |1982-02-03  

CREATE TABLE rn_UCITEL (
  id integer not null,
  meno VARCHAR (20) not null,
  priezvisko VARCHAR (30) not null,
  PRIMARY KEY (id)
)
CREATE TABLE rn_PREDMET (
  id integer not null,
  kod VARCHAR(10) not null,
  nazov VARCHAR(30) not null,
  iducitel integer not null,
  kredit integer not null,
  PRIMARY KEY (id)
)

CREATE TABLE rn_ZAPISANE (
  idstudent integer not null,
  idpredmet integer not null,
  znamka integer,
  primary key(idstudent, idpredmet)
)

 ####   ##                   #               ##   ##  ##  ##             
######  ##                  ##               ##   ##  ##  ##               
##      #####  ####  ##### #### #####  ###   ##   ##  ##  ##               
##      ## ##  # ##  ## ##  ##  ##  ## ##    ##   ##  ##  ##              
##  ##  ## ## #####  ## ##  ##  ###### ##    ##   ##  ##  ##               
######  ## ## ## ##  ## ##  ##  ## ### ##     ## ##   ##  ##  ##                 
 #####  ## ## #####  #####  ###  ####  ##      ###    ##  ##  ##           
                     ##                                                  
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? ####   ##                   #               ##   ##  ##  ##  ##           
######  ##                  ##               ##   ##  ##  ##  ##             
##      #####  ####  ##### #### #####  ###   ##   ##  ##  ##  ##             
##      ## ##  # ##  ## ##  ##  ##  ## ##    ##   ##  ##  ##  ##            
##  ##  ## ## #####  ## ##  ##  ###### ##    ##   ##  ##  ##  ##             
######  ## ## ## ##  ## ##  ##  ## ### ##     ## ##   ##  ##  ##  ##                 
 #####  ## ## #####  #####  ###  ####  ##      ###    ##  ##  ##  ##         
                     ##                                                  
20.11.2001           ##                                                  

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

kosice 0
bagdad 1

selektnime ucitela, studenta, pocet predmetov
SELECT u.meno, u.priezvisko, s.meno, s.priezvisko, p.kod
FROM
  rn_ucitel u JOIN rn_predmet p ON p.iducitel=u.id
  JOIN rn_zapisane z ON z.idpredmet=p.id
  JOIN rn_student s ON s.id=z.idstudent
GROUP BY u.meno, u.priezvisko, s.meno, s.priezvisko

selektnime studenta a priemernu znamku
select s.id, s.meno, s.priezvisko, avg(cast(z.znamka as DECIMAL) 5,2)
from student s join zapisane on s.id=z.idstudent
group by s.id

 ####   ##                   #                ##  ##   ##  
######  ##                  ##                ##   ## ##   
##      #####  ####  ##### #### #####  ###    ##    ###    
##      ## ##  # ##  ## ##  ##  ##  ## ##     ##     #    
##  ##  ## ## #####  ## ##  ##  ###### ##     ##    ###    
######  ## ## ## ##  ## ##  ##  ## ### ##     ##   ## ##   ##                 
 #####  ## ## #####  #####  ###  ####  ##     ##  ##   ##  ##         
                     ##                                                  
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
4.12.2001
rekurzia rulez!!!
mame tabulku clen
vyselektujme cloveka a jeho potomkov
ALTER TABLE RN_CLEN DROP FOREIGN KEY RN_NAIDOTEC

UPDATE rn_clen SET idotec=0 WHERE id=1

referencia sa neda znova nahodit...

WITH rn_potomkovia (id, idpotomok) AS
(SELECT koren.idotec, koren.id
 FROM rn_clen koren
 WHERE koren.idotec is not null
 UNION ALL
 SELECT otec.id, dieta.id
 FROM rn_potomkovia otec, rn_clen dieta
 WHERE otec.idpotomok=dieta.idotec
)
SELECT * FROM rn_potomkovia
ORDER BY 1,2

uroven vnarania
WITH rn_potomkovia (id, idpotomok, uroven) AS
(SELECT koren.idotec, koren.id, 1
 FROM rn_clen koren
 WHERE koren.idotec is not null
 UNION ALL
 SELECT otec.id, dieta.id, uroven+1
 FROM rn_potomkovia otec, rn_clen dieta
 WHERE otec.idpotomok=dieta.idotec
)
SELECT * FROM rn_potomkovia
ORDER BY 1,2

WITH rn_potomkovia (id, idpotomok, uroven) AS
(SELECT koren.idotec, koren.id, 1
 FROM rn_clen koren
 WHERE koren.idotec is not null
 UNION ALL
 SELECT otec.id, dieta.id, uroven+1
 FROM rn_potomkovia otec, rn_clen dieta
 WHERE otec.idpotomok=dieta.idotec
)
SELECT * FROM rn_potomkovia
ORDER BY 1,2

este lepsie

WITH rn_potomkovia (id, idpotomok, uroven) AS
(SELECT koren.idotec, koren.id, 1
 FROM rn_clen koren
 WHERE koren.idotec is not null
 UNION ALL
 SELECT otec.id, dieta.id, uroven+1
 FROM rn_potomkovia otec, rn_clen dieta
 WHERE otec.idpotomok=dieta.idotec AND uroven<2
)
SELECT * FROM rn_potomkovia
ORDER BY 1,2

rn_predmet
ID
skratka
nazov

rn_popis_predmetu
IDPREDMET
IDTYPTEXTU
TEXT

rn_typ_textu
ID
popis
1.anotacia
2.literatura
--------------------

CREATE TABLE rn_predmet2(
  id integer not null,
  skratka VARCHAR(10), 
  nazov VARCHAR(20),
  kredit integer,
  PRIMARY KEY(id)
);

CREATE TABLE rn_popis_predmetu(
  idpredmet integer not null,
  idtyptextu integer not null,
  idtext VARCHAR(20),
  PRIMARY KEY(idpredmet, idtyptextu)
);

CREATE TABLE rn_typ_textu(
  id integer not null,
  popis CHAR,
  PRIMARY KEY(id)
);

ALTER TABLE rn_popis_predmetu
ADD CONSTRAINT rn_naid
FOREIGN KEY (id) REFERENCES rn_predmet2

ALTER TABLE rn_popis_predmetu ADD CONSTRAINT rn_naid FOREIGN KEY (idpredmet) REFERENCES rn_predmet2

ALTER TABLE rn_popis_predmetu ADD CONSTRAINT rn_natyptextuid FOREIGN KEY (idtyptextu) REFERENCES rn_typ_textu

</pre>