Autor, Název akce Databázové systémy a SQL Lekce 3 Daniel Klimeš 1 Autor, Název akce Práce s více tabulkami Daniel Klimeš, Databázové systémy a SQL 2 ID pacienta Jmeno Prijmeni 1 Jan Novák 2 Jana Nová 3 Karel Starý ID pacienta Datum vysetreni Vysledek vysetreni 1 12.1.2011 39,5 1 15.3.2011 36,8 2 2.2.2011 37,5 ID lekare Jmeno Prijmeni 10 Petr Šikovný 20 Jana Levá 30 Karel Starý Spojení sloupců = JOIN + + Spojení řádků – množinové operace Autor, Název akce Množinové operace Daniel Klimeš, Databázové systémy a SQL 3 • UNION Sjednocení množin – duplicitní řádky vyloučeny • UNION ALL Sjednocení množin včetně duplicit • INTERSECT Průnik množin – pouze shodné řádky • MINUS Rozdíl množin Operace s dotazy, které vrací stejnou datovou strukturu (stejné sloupce) SELECT sloupec FROM tabulka UNION SELECT sloupec FROM tabulka2 Počet sloupců prvního a druhého dotazu musí být stejný a musí být stejného datového typu Autor, Název akce SELECT – více tabulek Daniel Klimeš, Databázové systémy a SQL 4 ID pacienta Jmeno Prijmeni 1 Jan Novák 2 Jana Nová 3 Karel Starý ID pacienta Datum vysetreni Vysledek vysetreni 1 12.1.2011 39,5 1 15.3.2011 36,8 2 2.2.2011 37,5 Spojování tabulek = join Druhy spojení: • vnitřní – inner join – jen spojitelné řádky • vnější – outer join - left join, right join, full join všechny řádky jedné tabulky + napojitelné řádky druhé tabulky Autor, Název akce JOIN - syntaxe Daniel Klimeš, Databázové systémy a SQL 5 Vnitřní spojení SELECT * FROM tabulka1, tabulka2 WHERE tabulka1.sloupec (PK) = tabulka2.sloupec (FK) SELECT * FROM pacient, vysetreni WHERE pacient.id_pacienta = vysetreni.id_pacienta nebo SELECT * FROM pacient JOIN vysetreni ON pacient.id_pacienta = vysetreni.id_pacienta ID pacienta Jmeno Prijmeni ID_pacienta Datum vysetreni Vysledek vysetreni 1 Jan Novák 1 12.1.2011 39,5 1 Jan Novák 1 15.3.2011 36,8 2 Jana Nová 2 2.2.2011 37,5 Autor, Název akce OUTER JOIN – syntaxe Daniel Klimeš, Databázové systémy a SQL 6 Vnější spojení SELECT * FROM tabulka1 LEFT JOIN tabulka2 ON tabulka1.sloupec = tabulka2.sloupec SELECT * FROM pacient LEFT JOIN vysetreni ON pacient.id_pacienta = vysetreni.id_pacienta ID pacienta Jmeno Prijmeni ID_pacienta Datum vysetreni Vysledek vysetreni 1 Jan Novák 1 12.1.2011 39,5 1 Jan Novák 1 15.3.2011 36,8 2 Jana Nová 2 2.2.2011 37,5 3 Karel Starý ORACLE varianta SELECT * FROM tabulka1, tabulka2 WHERE tabulka1.sloupec = tabulka2.sloupec(+) SELECT * FROM pacient, vysetreni WHERE pacient.id_pacienta = vysetreni.id_pacienta(+) Autor, Název akce VYUKA – datový model Daniel Klimeš, Databázové systémy a SQL 7 Vazba student – predmet m-n => „mezitabulka“ VYUKA C:\aa\export_vilp.jpg Autor, Název akce Cvičení 1 Daniel Klimeš, Databázové systémy a SQL 8 1) Zapište své jméno do tabulky STUDENT 2) Zapište si vybraný předmět/předměty do tabulky VYUKA 7) Odhlašte sebe ze všech předmětů 8) Přihlaste se jedním příkazem do všech předmětů 3) Vypište studenty zapsané do alespoň jednoho předmětu 4) Vypište všechny studenty s vybraným předmětem/předměty 5) Vypište všechny předměty a k nim počet zapsaných studentů 6) Vypište učící učitele a jeho studenty Autor, Název akce TRIALDB – datový model Daniel Klimeš, Databázové systémy a SQL 9 export_patients.jpg Vazba pacienti – studie m-n => „mezitabulka“ PATIENT_STUDY Vazba studie – pracoviště m-n => „mezitabulka“ STUDIES_SITES Autor, Název akce Domácí úkol Daniel Klimeš, Databázové systémy a SQL 10 V POSTGRESQL 1) Vytvořit schéma student včetně constraints 2) Vytvořit schéma patients včetně constraints Dle vzoru a informací v souboru schema.sql Ignorujte odkazy ve schematu na project UID a user_id v teto prezentaci Poslat upravený skript na klimes@iba.muni.cz Autor, Název akce Cvičení 2 Daniel Klimeš, Databázové systémy a SQL 11 Zjistěte počet pacientů v jednotlivých studiích STUDY_NAME, počet pacientů Zjistěte počet pacientů dle pohlaví v jednotlivých studiích STUDY_NAME, pohlaví, počet pacientů Zjistěte počet zapojených pracovišť do jednotlivých studií STUDY_NAME, počet pracovišť Vypište pracoviště zapojená do více studií SITE, počet studií Vypište všechny studie a počet zařazených pacientů v jednotlivých letech STUDY_NAME, rok(DATE_OF_ENROLLMENT) Zjistěte počet pacientů v jednotlivých studiích po pracovištích STUDY_NAME, SITE, počet pacientů Autor, Název akce Cvičení 3 Daniel Klimeš, Databázové systémy a SQL 12 Vypište všechny pacienty ze studií study_id 3 a 23 Vypište všechny unikátní pacienty ze studií study_id 3 a 23 Vypište společné pacienty ze studií study_id 3 a 23