################ ################# ############# ################## ####### ######## ###### ####### ####### ####### ####### ####### ######## ####### ####### ############### ############### ####### ####### ####### ######## ############ ####### ####### ####### ####### ####### ###### ################## ################### ################# ############### ################# ############# D A T A B Á Z O V É S Y S T É M Y ´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´ Zimny semester 2001/2002, cvici(l) Mgr. Nemcek Spisal R.Novotny, novrob00@duro.science.upjs.sk ´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´ Zopar uzitocnych URL ************************************************* * http://rnml.host.sk - umiestnenie tohto textu * * vyuka.science.upjs.sk - klient pre DB2 * ************************************************* Powered by Martin Nemèek & DB2. DB2 is a registered trademark of IBM Corp. #### ## # ## ###### ## ## ## ## ##### #### ##### #### ##### ### ## ## ## ## # ## ## ## ## ## ## ## ## ## ## ## ## ##### ## ## ## ###### ## ## ###### ## ## ## ## ## ## ## ## ### ## ## ## ##### ## ## ##### ##### ### #### ## ## ## ## 2.10.2001 ## 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, datum_narod 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 (co Ruzena Sipova? ma IDTYPIZBY=null) ak chceme vidiet aj Ruzenu Sipovu: Spajanie tabuliek inak ako cez WHERE -- JOINY -------------------------------------- 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 (trochu inak): SELECT meno, priezvisko, m.nazov, izba, i.poplatok 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. (???????? zobrazi hodnoty A? mne sa zda, ze NULL...) Podobne RIGHT OUTER JOIN. SELECT meno, priezvisko, m.nazov FROM rn_student s RIGHT OUTER JOIN rn_mesto m ON s.idmesto=m.id MENO PRIEZVISKO NAZOV ---------- -------------------- -------------------- Jan Hrasko Kosice Jan Polienko Kosice Juraj Trulo Kosice Ruzena Sipova Bardejov Ali Baba Bagdad Juraj Trulo Trebisov - - Presov ! vypiste vsetkych Kosicanov, ktori byvaju na internate 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 pri agregacnych funkciach treba zGROUPovat podla vsetkych neagregovanych atributov! 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 rn_student as std RIGHT OUTER JOIN rn_skupina AS odbor ON std.idskupina = odbor.id; ! na ktorom odbore je aspon jeden student SELECT sk.kod FROM rn_skupina sk WHERE sk.id in (SELECT DISTINCT st.idskupina FROM rn_student st) M MI MF ! na ktorom odbore nie je ziaden student SELECT sk.kod FROM rn_skupina sk WHERE sk.id NOT IN (SELECT DISTINCT st.idskupina FROM rn_student st) alebo: SELECT sk.kod, sk.id FROM rn_skupina sk WHERE NOT EXISTS (SELECT * FROM rn_student st WHERE st.idskupina=sk.id) alebo (vyrobil som ja...) SELECT sk.nazov FROM rn_skupina sk LEFT OUTER JOIN rn_student s ON sk.id=s.idskupina WHERE s.id IS NULL ! 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 (pozn. preco DESC? treba ho?) 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 (pozn. prosim Vas, co robi toto? je to neuplne...) 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 MAX(s.datumnarod) FROM rn_student s WHERE s.datumnarod < (SELECT MAX(s2.datumnarod) FROM rn_student s2) alebo cez WITH 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 3. Vybrat prve meno podla abecedy. (Vyber prveho riadku tabulky) SELECT s.meno FROM rn_student s WHERE ( SELECT count(*) FROM rn_student ss WHERE ss.meno < s.meno ) < 1 ----------------------------------------------------------- !Definujme si novu strukturu 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 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); 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_zapisane add foreign key rn_idu2 (idpredmet) references rn_predmet; 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 Alois |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 s id=4 vsetky predmety, ktore uci Jirasek Hromadne INSERT-y, UPDATE-y, DELETE-y ------------------------------------- INSERT INTO rn_zapisane (idstudent, idpredmet) najprv ! 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 (priezvisko, meno ucitela, pocet predmetov, ktore uci) Alois, Jirasek, 2 SELECT u.meno, u.priezvisko, COUNT(p.id) FROM rn_ucitel u JOIN rn_predmet p ON p.iducitel=u.id GROUP by u.meno, u.priezvisko ! meno, priezvisko ucitela, predmet, kolko studentov je zapisanych na danom predmete SELECT u.meno, u.priezvisko, p.kod, COUNT(s.id) AS pocet_studentov FROM rn_zapisane z JOIN rn_student s on s.id=z.idstudent JOIN rn_predmet p on p.id=z.idpredmet JOIN rn_ucitel u on u.id=p.iducitel GROUP BY u.meno, u.priezvisko, p.kod MENO PRIEZVISKO KOD POCET_STUDENTOV -------- ----------- ----- --------------- Alois Jirasek GRA1 4 Alois Jirasek PS1 2 Julius Fucik MA1a 4 Julius Fucik MA1b 5 #### ## # ## ## ## ## ## ###### ## ## ## ## ## ## ## ## ##### #### ##### #### ##### ### ## ## ## ## ## ## ## ## # ## ## ## ## ## ## ## ## ## ## ## ## ## ## ## ## ##### ## ## ## ###### ## ## ## ## ## ## ###### ## ## ## ## ## ## ## ## ### ## ## ## ## ## ## ## ##### ## ## ##### ##### ### #### ## ### ## ## ## ## ## 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 ! ucitel, student, pocet predmetov # ???? k comu je tento SELECT??? naviac je zly...) 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 ! student a priemerna znamka SELECT s.meno, s.priezvisko, DECIMAL(AVG(DECIMAL(z.znamka,5,4)),5,2) FROM rn_zapisane z JOIN rn_student s ON s.id=z.idstudent JOIN rn_predmet p ON p.id=z.idpredmet GROUP by s.meno, s.priezvisko MENO PRIEZVISKO 3 ------- ----------- ------- Ali Baba 2,00 Jan Hrasko 1,66 Jan Polienko - Juraj Trulo 2,20 Ruzena Sipova 1,00 #### ## # ## ## ## ###### ## ## ## ## ## ## ##### #### ##### #### ##### ### ## ### ## ## ## # ## ## ## ## ## ## ## ## # ## ## ## ## ##### ## ## ## ###### ## ## ### ###### ## ## ## ## ## ## ## ## ### ## ## ## ## ## ##### ## ## ##### ##### ### #### ## ## ## ## ## ## 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.meno, s.priezvisko, SUM(p.kredit) FROM rn_zapisane z JOIN rn_student s ON s.id=z.idstudent JOIN rn_predmet p ON p.id=z.idpredmet GROUP by s.meno, s.priezvisko MENO PRIEZVISKO 3 ------- ----------- --- Ali Baba 10 Jan Hrasko 16 Jan Polienko 6 Juraj Trulo 37 Ruzena Sipova 11 ! vyselektujme studenta, pocet ziskanych predmetov SELECT s.meno, s.priezvisko, SUM(p.kredit) FROM rn_zapisane z JOIN rn_student s ON s.id=z.idstudent JOIN rn_predmet p ON p.id=z.idpredmet WHERE z.znamka IS NOT NULL GROUP by s.meno, s.priezvisko MENO PRIEZVISKO 3 ------- ----------- --- Ali Baba 10 Jan Hrasko 16 Juraj Trulo 27 Ruzena Sipova 5 ! 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 vysledny uplny SELECT: WITH mozne (id, kredit) as ( select s.id, SUM (p.kredit) from rn_student as s join rn_zapisane as z on z.idstudent = s.id join rn_predmet as p on p.id = z.idpredmet group by s.id ), ziskane (id, kredit) as ( select s.id, SUM (p.kredit) from rn_student as s join rn_zapisane as z on z.idstudent = s.id join rn_predmet as p on p.id = z.idpredmet where z.znamka is not null group by s.id ) SELECT std.meno, std.priezvisko, moz.kredit, zis.kredit FROM rn_student std LEFT OUTER JOIN mozne moz ON moz.id=std.id LEFT OUTER JOIN ziskane zis ON zis.id=std.id ----------------------------------------------------------- Definujme si inu datovu strukturu: 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 doplnme data 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 najprv upravme referencie: 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 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 nie kazdy predmet ma aj anotaciu aj literaturu vyberte predmety a ich prisluchajuce anotacie a literaturu vyberte prve 2 predmety s najvacsim kreditom usporiadane podla skratky