Popis definiční části jazyka SQL,specifikace deklarace primárních a cizích klíčů a příklady na modelu knihovny

Příkazy

SQL příkazy se dělí na čtyři základní skupiny:

• Příkazy pro manipulaci s daty (SELECT, INSERT, UPDATE, DELETE, …)
• Příkazy pro definici dat (CREATE, ALTER, DROP, …)
• Příkazy pro řízení přístupových práv (GRANT, REVOKE)
• Příkazy pro řízení transakcí (START TRANSACTION, COMMIT, ROLLBACK)

Konceptuální schéma knihovny

Konceptuální schéma knihovny pomocí notace Crow's Foot
File:SQL_knihovna.png

FK (foreign key) = cizí klíč
PK (primary key) = primární klíč
NN = not null - sloupec nesmí být prázdný.
CHAR(20) = text délky maximálně 20 znaků
DECIMAL(10,0) = první číslo označuje celkový počet míst v číslici, druhé počet míst za desetinní čárkou. V tomto případu se jedná o 10 místní číslo s 0 desetinnými místy. (Např. Decimal(4,3) jsou čísla od -9,999 do 9,999.)

Data definition language

Vytvoř tabulku dílo.
CREATE TABLE Dilo
(id_dilo PK INT,
nazev CHAR(200),
popis CHAR(200));

Vysvětlení interpunkce: středník odděluje jednotlivé SQL dotazy, čárka odděluje vstupní parametry.

Vytvoř tabulku vydání.
CREATE TABLE vydani
(id_vyd PK INT,
id_dilo FK INT REFERENCES Dilo(id_dilo),
cis_vyd CHAR(20),
rok_vyd CHAR(20));

Vysvětlení references: references píšeme, protože používáme cizí klíč - musíme definovat, kam cizí klíč odkazuje.

Vytvoř tabulku autorství.
CREATE TABLE Autorstvi
(id_autorstvi PK INT,
id_dilo FK INT REFERENCES Dilo(id_dilo),
id_aut FK INT REFERENCES Autor(id_aut));

Vymaž tabulku druh sekundárního autorství.
DROP TABLE Drusekaut;

Vymaž databázi knihovna.
DROP DATABASE Knihovna;

Uprav tabulku dílo: přidej sloupec evidenční číslo.
ALTER TABLE dilo
ADD ev_cis CHAR(200);

Uprav tabulku pokuta: odstraň datum pokuty.
ALTER TABLE pokuta
DROP dat_pok;

Vysvětlení: datový typ, např. "CHAR(200)" nebo "INT" píšeme jenom tehdy, když něco vytváříme. Když mažeme, nemusíme datový typ psát/definovat.

Data manipulation language

Vlož dílo "Těžký časy".
INSERT INTO Dilo(nazev, popis)
VALUES("Těžký časy", "Soubor 35 krátkých povídek.");

Vlož autor "Charles Bukowski".
INSERT INTO Autor(jme_aut,pri_aut)
VALUES("Charles","Bukowski");

Vymaž pokuty, které vznikly na Vánoce.
DELETE FROM Pokuta
WHERE dat_pok = "24122016";

Vymaž všechny pokuty.
DELETE FROM Pokuta;

Vymaž všechny autory.
DELETE FROM Autor;

Vysvětlení: bez definice WHERE mažu všechno.

Zmaž všechny výpůjčky od Vánoc do Silvestra.
DELETE FROM Vypujcka
WHERE dat_od = "24122016"
AND dat_do = "31122016";

Vysvětlení formy data: v konceptuálním schématu knihovny máme definovanou délku data na 8 znaků. Můžeme tedy datum napsat ve formě 24122016 nebo 24.12.16 - podstatné je dodržet délku pole, co je 8 znaků.

Zmaž autora Charles Bukowski.
DELETE FROM Autor
WHERE jme_aut = "Charles"
AND pri_aut = "Bukowski";

Změň cenu stokorunových výtisků na 120 Kč.
UPDATE Vytisk
SET cena = 120
WHERE cena = 100;

Změň křestní jméno Petra Nováka na Josef.
UPDATE ctenar
SET jme_cte = „Josef“
WHERE jme_cte = „Petr“
AND pri_cte = „Novák“;

Vypiš všechny data z tabulky dílo.
SELECT *
FROM Dilo;

Vypiš příjmení všech autorů.
SELECT pri_aut
FROM Autor;

Z výtisků vypiš všechny signatury a ceny (v pořadí signatura, cena).
SELECT signatura, cena
FROM Vytisk;

Vypiš z tabulky výpůjčka datumy výpůjčky a datumy vrácení tak, aby měli sloupce názvy "datum vypujcky" a "datum vraceni".
SELECT dat_od AS datum_vypujcky, dat_do AS datum_vraceni
FROM Vypujcka;

Vysvětlení: v datum_vraceni je "_" místo mezery, protože SQL by jinak za mezerou čekal další klíčové slovo/příkaz.

Vypiš z tabulky pokut id pokuty jako "cislo pokuty" a výšku pokuty ze dne 28.12.2016.
SELECT id_pok AS cislo_pokuty, vyse_pok
FROM Pokuta
WHERE dat_pok="28122016";

Vysvětlení: datum píšeme v uvozovkách, protože se jedná o řetězec. Jenom datový typ CHAR vypisujeme v uvozovkách, datový typ Integer a Decimal vypisujeme bez uvozovek.

Vypiš z tabulky čtenář id jako "cislo ctenare", příjmení jako "prijmeni" a adresu všech čtenářů, kteří se jmenují "Pavel".
SELECT id_cte AS cislo_ctenare, pri_cte AS prijmeni, adresa
FROM Ctenar
WHERE jme_cte = "Pavel";

Z tabulky Vydání vypiš všechno o vydáních, které mají id díla "20" a rok vydání "2016".
SELECT *
FROM Vydani
WHERE id_dilo = 20
AND rok_vyd = "2016";

Přepoj dílo "Těžký časy" s autorem "Charles Bukowski" v tabulce autorství.
SELECT id_dilo
FROM Dilo
WHERE nazev = "Těžký časy";
(Systém ukáže id, např. 12)

SELECT id_aut
FROM Autor
WHERE jme_aut = "Charles"
AND pri_aut = "Bukowski";
(Systém ukáže id, např. 21)

INSERT INTO Autorstvi(id_dilo, id_aut)
VALUES(12, 21);

Vypiš všechny čtenáře, kteří si nikdy nic nevypůjčili.
SELECT *
FROM Ctenar
WHERE id_cte NOT IN (SELECT id_cte FROM Vypujcka);

Vypiš součet všech pokut.
SELECT SUM(vyse_pok)
FROM Pokuta;

Vypiš nejdražší a nejlevnější výtisk daného díla.
SELECT MAX(cena), MIN(cena)
FROM Vytisk
GROUP BY id_dilo;

Vstupní parametr SELECT neobsahuje AND. Jenom za WHERE následuje AND.

Zjisti, kolik máme autoru v databázi.
SELECT COUNT(*) AS pocet_autoru FROM Autor;

Vysvětlení: * je tady v závorce, protože je to parametr agregační funkce.

Zjisti průměrnou cenu výtisků.
SELECT AVG(cena) FROM Vytisk;

Uveď průměrnou cenu výtisků. Sloupec nazvi průměrná cena.
SELECT AVG(cena) AS prumerna_cena FROM Vytisk;

Odkazy

Použitá literatura

• Programování v SQL. In: Ucimedatabaze.cz: Podpora výuky databázových systémů na SOŠ, založené na technologiích společnosti ORACLE [online]. 2011, s. 55 [cit. 2016-12-16]. Dostupné z: http://ucimedatabaze.cz/wp-content/uploads/2012/02/CZ_programovani_SQL.pdf
• Entity-relationship model. In: Wikipedie: Otevřená encyklopedie [online]. [cit. 2016-12-16]. Dostupné z: https://cs.wikipedia.org/wiki/Entity-relationship_model
• POKORNÝ, Jaroslav a Ivan HALAŠKA. Databázové systémy: vybrané kapitoly a cvičení. 2. přepr. vyd. Praha: Karolinum, 1998. ISBN 8071846872.
• SQL. In: Wikipedie: Otevřená encyklopedie [online]. [cit. 2016-12-17]. Dostupné z: https://cs.wikipedia.org/wiki/SQL
• Basic SQL statements: DDL and DML. In: Database design with UML and SQL, 3rd edition [online]. [cit. 2016-12-24]. Dostupné z: http://www.tomjewett.com/dbdesign/dbdesign.php?page=ddldml.php
• W3schools.com [online]. [cit. 2017-01-03]. Dostupné z: http://www.w3schools.com/sql/default.asp

Externí odkazy

• w3schools
• SQLZOO
• Matematicko-fyzikální fakulta
• Krokodýlovy databáze

Související články

• Informační systém
• Databáze

Klíčová slova

Databázové systémy, Jazyk SQL, E-R konceptuální model, Informační systém, Informace, Data