Autor, Název akce Databázové systémy a SQL Lekce 4 Daniel Klimeš Autor, Název akce Databázové objekty Daniel Klimeš, Databázové systémy a SQL •Nejvýznamnější databázové objekty • Tabulky (tables) • Pohledy (views) • Indexy (indexes) • Sekvence (sequences) • Procedury (procedures) • Funkce (functions) • Triggery (triggers) Informace o objektech jsou uloženy v metadatech (systémových datech) databáze Přístup k nim je databázově specifický ORACLE • systémové tabulky – uživatelům pouze pro čtení • metatabulka o metatabulkách – DICTIONARY • tabulky USER_XXX – objekty vytvořené uživatelem • tabulky ALL_XXX – objekty přístupné uživateli • tabulky DBA_XXX – všechny objekty databáze – přístupné jen administrátorovi • Autor, Název akce Tabulky Daniel Klimeš, Databázové systémy a SQL • Metatabulky USER_TABLES, ALL_TABLES, DBA_TABLES • sloupec table_name • metatabulka TAB • sloupec tname, tabtype • Sloupce tabulky - USER_TAB_COLUMNS • table_name, column_name, data_type DDL příkazy pro manipulaci s tabulkami • CREATE TABLE • DROP TABLE • ALTER TABLE • RENAME TABLE Autor, Název akce Pohledy (VIEWS) Daniel Klimeš, Databázové systémy a SQL •Pohled = uložený SQL dotaz •Pracuje se s ním stejně jako s tabulkou •Ve většině případů je možný pouze SELECT • • CREATE VIEW v_ukazka AS SELECT ps.patient_id, study_name FROM patient_study ps, studies s WHERE ps.study_id = s.study_id SELECT study_name, count(*) FROM v_ukazka GROUP BY study_name DDL pro pohledy: CREATE OR REPLACE VIEW AS DROP VIEW ORACLE metadata • user_views, tab Autor, Název akce Indexes Daniel Klimeš, Databázové systémy a SQL http://t2.gstatic.com/images?q=tbn:ANd9GcS9N-4620UX6QGkL1BjwS17HbDWd-gotYpYiNoSHszQetpztSOBZg •Indexy jsou obdobou kartotéky • Umožňují rychlejší vyhledávání záznamů ve velkých tabulkách • Urychlují SELECT dotazy, zpomalují INSERT, UPDATE, DELETE • Indexy se vytváří nad jedním nebo více sloupci tabulky • Standardně nad primárním klíčem a cizími klíči • Dále nad sloupci, které se často používají za WHERE •DDL pro indexy • CREATE INDEX • DROP INDEX • ALTER INDEX ORACLE metadata user_indexes ind Autor, Název akce Sekvence Daniel Klimeš, Databázové systémy a SQL •Sekvence generují za všech okolností unikátní čísla – posloupnost • Použití pro primární klíče při insertech nových řádků • • SELECT jmeno_sekv.NEXTVAL from DUAL • SELECT jmeno_sekv.CURRVAL from DUAL • • Každé zavolání NEXTVAL vrátí další číslo v posloupnosti bez ohledu na transakce Při neúspěšném použití vygenerovaného ID vznikají “díry” v posloupnosti •ORACLE DDL • CREATE SEQUENCE • DROP SEQUENCE • ALTER SEQUENCE • ORACLE metadata • user_sequences •FIREBIRD • CREATE GENERATOR • DROP GENERATOR Autor, Název akce Cvičení 1) Zjistěte počet řádků v tabulce questions SELECT COUNT(*), COUNT(DISTINCT question_id) FROM questions 2) Ověřte, zda QUESTION_ID je unikátní v tabulce questions SELECT COUNT(*) FROM questions SELECT COUNT(*), COUNT(DISTINCT question_description) FROM questions 3) Ověřte, zda QUESTION_DESCRIPTION je unikátní SELECT question_description, COUNT(*) FROM questions GROUP BY question_description HAVING COUNT(*) > 10 4) Vypište QUESTION_DESCRIPTION, které se opakují více než 10x Daniel Klimeš, Databázové systémy a SQL Autor, Název akce Cvičení Najděte formulář s největším počtem otázek • tabulky CLUSTERS •QUESTION_GROUP_CLUSTERS •QUESTION_GROUP_QUESTIONS • Vypište vše z uvedených tabulek vnitřním spojením přes příslušné klíče SELECT * FROM clusters c, question_group_clusters qg_cluster, question_group_questions qg_question WHERE c.cluster_id = qg_cluster.cluster_id AND qg_cluster.question_group_id = qg_question.question_group_id Daniel Klimeš, Databázové systémy a SQL Autor, Název akce Cvičení •Seskupte dle cluster_id a cluster_description a spočítejte počet řádků = počet otázek SELECT c.cluster_id, c.cluster_description, COUNT(*) FROM clusters c, question_group_clusters qg_cluster, question_group_questions qg_question WHERE c.cluster_id = qg_cluster.cluster_id AND qg_cluster.question_group_id = qg_question.question_group_id GROUP BY c.cluster_id, c.cluster_description •Vypište řádky s maximální hodnotou COUNT(*) Daniel Klimeš, Databázové systémy a SQL Autor, Název akce Cvičení SELECT cluster_description, pocet FROM ( SELECT c.cluster_id, c.cluster_description, COUNT(*) pocet FROM clusters c, question_group_clusters qg_cluster, question_group_questions qg_question WHERE c.cluster_id = qg_cluster.cluster_id AND qg_cluster.question_group_id = qg_question.question_group_id GROUP BY c.cluster_id, c.cluster_description ORDER BY count(*) DESC ) WHERE ROWNUM = 1 Daniel Klimeš, Databázové systémy a SQL Autor, Název akce Cvičení Daniel Klimeš, Databázové systémy a SQL • Najděte záznamy v tabulce QUESTIONS s datatype = ‘E’, pro které neexistuje záznam v tabulce DISCRETE_VALUE_GROUPS SELECT * FROM questions q WHERE datatype ='E' AND NOT EXISTS(SELECT * FROM discrete_value_groups dvg WHERE Q.DISCRETE_VALUE_GROUP_ID = DVG.DISCRETE_VALUE_GRP_ID) Autor, Název akce FIREBIRD – testovací databáze Tabulka Pacienti •ID •Jmeno •Datum_narozeni •Pohlavi Tabulka Vysetreni •ID_vysetreni •ID •Datum_vysetreni •Typ_vysetreni • Vysledek Minulé cvičení: 1.Zjistěte počet pacientů 2.Zjistěte počet vyšetření typu 7 3.Vypište přehled jmeno, počet vyšetření 1.SELECT COUNT(*) FROM pacienti 2.SELECT COUNT(id_vysetreni) FROM vysetreni WHERE typ_vysetreni = 7 3.SELECT p.id, p.jmeno, COUNT(id_vysetreni) AS pocet_vysetreni FROM pacienti p LEFT JOIN vysetreni v ON p.id = v.id GROUP BY p.id, p.jmeno Daniel Klimeš, Databázové systémy a SQL Autor, Název akce FIREBIRD – testovací databáze Tabulka Pacienti •ID •Jmeno •Datum_narozeni •Pohlavi Tabulka Vysetreni •ID_vysetreni •ID •Datum_vysetreni •Typ_vysetreni • Vysledek Cvičení: 1.Ověřte, zda ID_vysetreni v tabulce vysetreni je unikátní 2.Vypište id_vysetreni z tabulky vysetreni, která jsou duplicitní 3.Ověřte, zda každé vyšetření má rodičovský záznam v tabulce pacienti 1. Daniel Klimeš, Databázové systémy a SQL