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 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 Cvičení 2 Daniel Klimeš, Databázové systémy a SQL 10 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 11 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 Autor, Název akce Domácí úkol Daniel Klimeš, Databázové systémy a SQL 12 Kolika studií se účastní centra z Brna? Kolika studií se účastní centra z Prahy (všechny obvody)? Seznam studií, které se účastní Brno, ale nikdo z Prahy Autor, Název akce Subdotazy SQL Daniel Klimeš, Databázové systémy a SQL 13 Zanořené dotazy • uzavřené v kulatých závorkách () • vložení: místo názvu sloupce místo názvu tabulky v sekci WHERE Místo sloupce: SELECT COUNT(patient_id), (select count (*) FROM patients) FROM patient_study WHERE study_id = 3 ; - vnořený dotaz na pozici sloupce musí vrátit právě jeden řádek Autor, Název akce Zanořený dotaz Daniel Klimeš, Databázové systémy a SQL 14 Subdotaz na pozici FROM nahrazuje tabulku SELECT COUNT(*) FROM ( SELECT study_id, COUNT(*) FROM patient_st udy GROUP BY study_id ) Autor, Název akce Vnořený dotaz za WHERE Daniel Klimeš, Databázové systémy a SQL 15 Varianty: • WHERE sloupec = (SELECT sloupec FROM… • WHERE sloupec = ANY (SELECT sloupec FROM… • WHERE sloupec IN (SELECT sloupec FROM … • WHERE sloupec > ALL (SELECT sloupec FROM … • WHERE EXISTS (SELECT * FROM…. • WHERE NOT EXISTS (SELECT * FROM… SELECT * FROM patients WHERE date_of_birth = ( SELECT MAX(date_of_birth) FROM patients); SELECT * FROM patients WHERE date_of_birth IS NOT NULL AND date_of_birth >= ALL ( SELECT date_of_birth FROM patients); SELECT * FROM patients tab1 WHERE date_of_birth IS NOT NULL AND NOT EXISTS ( SELECT * FROM patients tab2 WHERE tab2. date_of_birth > tab1. date_of_birth ); Nejmladší pacient: Autor, Název akce Cvičení 2 Daniel Klimeš, Databázové systémy a SQL 16 Napište 3 varianty, které zobrazí řádek s nejstarším pacientem Napište dotaz, který vrátí všechny pacienty kromě nejstaršího a nejmladšího Vypište všechny studie a počet zařazených pacientů v jednotlivých letech a u každé nejmladšího a nejstaršího pacienta v daném roce STUDY_NAME, rok(DATE_OF_ENROLLMENT), min(date_of_birth), max(date_of_birth) Zjistěte počet pacientů ve studiích, kde počet pacientek není větší než 10 STUDY_NAME, počet pacientů Autor, Název akce Struktura registru Daniel Klimeš, Databázové systémy a SQL 17 Registr/studie se skládá z 1 až n formulářů, které se vyplňují v určité fázi péče o pacienta Formulář se skládá z 1 až n skupin otázek Skupina otázek je tvořena 1 až n otázkami Otázky mohou být různého datové typu, (číslo, text, datum, číselník) Číselník je sada povolených odpovědí na danou otázku (výběr z nabídky „roletka“, combo box) Autor, Název akce TRIALDB – datový model Daniel Klimeš, Databázové systémy a SQL 18 export_patients.jpg Formulář Skupina otázek Otázky Položky číselníku Číselník Autor, Název akce Cvičení Daniel Klimeš, Databázové systémy a SQL 19 •Najděte formulář bez definované skupiny otázek • •Najděte formulář s největším počtem skupin • •Najděte formulář s největším počtem otázek • •Najděte nejčastěji používaný číselník