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