Autor, Název akce Databázové systémy a SQL Lekce 4 Daniel Klimeš 1 Autor, Název akce Homework •Import ukol.csv •Smaž řádky obsahujíc datum větší než 1.10.2015 /delete rows with date greater than 2015-10-01 •Ponechte pouze řádky kde/ keep only rows • Datnar < datdg < lecbaod < lecbado < datumrti •Zkontrolujte, zda u všech řádků jsou všechna datumy. Přpadné neúplné smažte / Check rows, if any date is missing, delete the row •Ve sloupci LEU musí být číslo, převeďte na číslo, co převést jde, uvedenou jednotku odstraňte In the column leu must be number, remove unit from the column and convert into number •Hodnota leu nesmí být větší než 10 Delete row where leu > 10 •Povolené druhy léčby jsou ABCDEF, jiné ne • keep only row where druhleby = (A,B,C,D,E,F) Daniel Klimeš, Obhajoba disertační práce 2 Autor, Název akce PRÁCE S VÍCE TABULKAMI • Daniel Klimeš, Databázové systémy a SQL 3 Autor, Název akce Práce s více tabulkami / more tables Daniel Klimeš, Databázové systémy a SQL 4 PatientID Firstname Lastname 1 Jan Novák 2 Jana Nová 3 Karel Starý PatientID ExamDate ExamResult 1 12.1.2011 39,5 1 15.3.2011 36,8 2 2.2.2011 37,5 + Autor, Název akce Vazby/ Relationships Daniel Klimeš, Databázové systémy a SQL 5 ENTITY = tabulky/tables RELATIONSHIP = vazba E-R diagramy = datové modely (data models) 1:1 – jeden řádek tabulky A má vazbu s jedním řádkem tabulky B one row of table A with one row in table B 1:n – k jednomu řádku tabulky A se váže 0 až N řádků tabulky B one row of table A with 0,1 or many rows in table B m:n – k jednomu řádku tabulky A se váže 0 až N řádků tabulky B ale zároveň k jednomu řádku z B se váže 0 až N řádků A one row in table A with with 0,1 or many rows in table B, but one row in table B with with 0,1 or many rows in table B as well Type of relationship: Autor, Název akce ER diagram Daniel Klimeš, Databázové systémy a SQL 6 1:n 1:n n:1 Vidlička = dětská závislá tabulka teacher subject Autor, Název akce Postup tvorby datového modelu Daniel Klimeš, Databázové systémy a SQL 7 •Definice entit (tabulek) •Stanovení primárních klíčů všech tabulek •Tvorba vazeb •Migrace primárního klíče rodičovské tabulky do dětské tabulky •Cizí klíč může, ale nemusí být součástí primárního klíče dětské tabulky Autor, Název akce Task 1 Daniel Klimeš, Databázové systémy a SQL 8 Spusťte/Run skript2.sql • •Vytvořte si vlastní předmět (řádek v tabulce předmět) Create a new subject = insert row into table predmet •Zkuste vytvořit předmět s neexistujícím UCO_teacher try to create a subject with empty UCO_teacher •Přihlaste se do zvolených předmětů registr any subject(s) •Odhlašte se ze všech předmětů cancel all your registrations •Přihlaste se do všech dostupných předmětů Registr all subjects by one sql command •Zkuste smazat všechny učitele try to delete all rows from teacher Autor, Název akce DOTAZOVÁNÍ VÍCE TABULEK • Daniel Klimeš, Databázové systémy a SQL 9 Autor, Název akce Práce s více tabulkami Daniel Klimeš, Databázové systémy a SQL 10 PatientID Firstname Lastname 1 Jan Novák 2 Jana Nová 3 Karel Starý PatientID Date_of_exam Result 1 12.1.2011 39,5 1 15.3.2011 36,8 2 2.2.2011 37,5 PhysicianID Firstname Lastname 10 Petr Šikovný 20 Jana Levá 30 Karel Starý Spojení sloupců = JOIN + + Spojení řádků – množinové operace Autor, Název akce SELECT – více tabulek Daniel Klimeš, Databázové systémy a SQL 11 PatientID Firstname Lastname 1 Jan Novák 2 Jana Nová 3 Karel Starý PatientID Date_of_exam Result 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 12 Vnitřní spojení / Inner join SELECT * FROM pacient JOIN vysetreni ON pacient.id_pacienta = vysetreni.id_pacienta PatientID Firstname Lastname PatientID Date_of_exam Result 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 13 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 PatientID Firstname Lastname PatientID Date_of_exam Result 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 Task Daniel Klimeš, Databázové systémy a SQL 14 Vypište studenty zapsané do alespoň jednoho předmětu Select students with one or more registered subjects Vypište všechny studenty s vybraným předmětem/předměty Select all students with a given registered subject Vypište všechny předměty a k nim počet zapsaných studentů Select all subject with number of registered students Vypište učící učitele a jeho předměty Select teachers and their subjects Vypište své jméno a své předměty Select your name with your subjects Vypište učící učitele a jeho studenty Select teachers and their students Vypište všechny učitele a počet jeho studentů Select all teachers and their number of students Autor, Název akce ER diagram Daniel Klimeš, Databázové systémy a SQL 15 Autor, Název akce Práce s více tabulkami Daniel Klimeš, Databázové systémy a SQL 16 PatientID Firstname Lastname 1 Jan Novák 2 Jana Nová 3 Karel Starý PatientID Date_of_exam Result 1 12.1.2011 39,5 1 15.3.2011 36,8 2 2.2.2011 37,5 PhysicianID Firstname Lastname 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 17 • UNION Sjednocení množin – duplicitní řádky vyloučeny • UNION ALL Sjednocení množin včetně duplicit • INTERSECT Průnik množin – pouze shodné řádky • EXCEPT Rozdíl množin • MINUS Rozdíl množin (ORACLE) 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 Task Daniel Klimeš, Databázové systémy a SQL 18 Vypište seznam všech studentů a učitelů (jméno, příjmení) Select firstname and lastname of students and teachers Přidejte jednoho učitele mezi studenty a vyzkoušejte všechny množinové operace (průnik, rozdíl) Add a copy of one row from table teacher to student and try all set functions