Študent po absolvovaní predmetu zvládne použitie náročnejších techník relačných databáz, teoretickú analýzu funkčných závislostí atribútov a je schopný pracovať s nerelačnými databázami. (Motivácia)
doc. RNDr. Csaba Török, CSc.
csaba.torok@upjs.sk
RNDr. Lukáš Miňo, PhD.
lukas.mino@upjs.sk
Čas: Utorok 15:20 - 16:50
Miestnosť: SJ2P11 - Jesenná 5, 2. poschodie, poslucháreň
Čas: Štvrtok 13:30 - 15:00
Miestnosť: SA1C04 - Park Angelinum 9, 1. poschodie, počítačová učebňa
Čas: Štvrtok 15:20 - 16:50
Miestnosť: SA1C03 - Park Angelinum 9, 1. poschodie, počítačová učebňa
Študent po absolvovaní predmetu zvládne použitie náročnejších techník relačných databáz, teoretickú analýzu funkčných závislostí atribútov a je schopný pracovať s nerelačnými databázami.
Plagiátorstvo nie je akceptovateľné. U poskytovateľa a samozrejme prijímateľa/plagiátora budú všetky doteraz získané body anulované. Body sa anulujú od momentu zistenia podvodu, nie kedy bol podvod zrealizovaný. Zainteresované osoby môžu sa naďalej zúčastňovať danej výučby a získavať body od daného momentu. O danom incidente budú informovaní všetci kolegovia a daný prehrešok bude posunutý etickej komisii, ktorá môže rozhodnúť aj vylúčením zo štúdia.
Príklad 1:
V 9. týždni výučby sa zistilo, že poskytovateľ poskytol prijímateľovi svoje riešenie 5. sady úloh v čase riešení 5. domáceho zadania.
V danom čase už boli tri priebežné previerky, prebieha 7. sada domácich úloh, bol obhájený projekt a napísaná polsemestrálna previerka.
Doteraz získané body (do riešenia 7. sady úloh vrátane) sa obom aktérom anulujú.
Avšak môžu získať body za poslednú priebežnú previerku, zvyšné sady domácich úloh a koncosemestrálnu previerku.
Maximálny počet bodov, ktoré ešte môžu získať je 1+3+15=19 bodov plus nové body za aktivitu.
Musia ešte obhájiť svoje správanie pred etickou komisiou, ktorá ich môže vylúčiť zo štúdia.
Príklad 2:
V 12. týždni výučby bol u plagiátora objavený ťahák pri písaní poslednej priebežnej previerky. Do konca výučby zostáva posledná sada domácich úloh a koncosemestálna previerka.
Doteraz získané body sa anulujú.
Plagiátor môže získať body za poslednú sadu domácich úloh a koncosemestrálnu previerku.
Maximálny počet bodov, ktoré ešte môže získať je 1+15=16 bodov plus nové body za aktivitu.
Musí ešte obhájiť svoje správanie pred etickou komisiou, ktorá ho môže vylúčiť zo štúdia.
Príklad 3:
Na/po koncosemstrálnej previerke sa zistilo, že poskytovateľ poskytol časť svojho riešenia prijímateľovi.
U poskytovateľa a prijímateľa/plagiátora budú všetky doteraz získané body anulované.
Zainteresované osoby môžu naďalej ísť na skúšku, avšak z cvičení dostávajú automaticky nula bodov.
O danom incidente budú informovaní všetci kolegovia a daný prehrešok bude posunutý etickej komisii, ktorá môže rozhodnúť aj vylúčením zo štúdia.
Príklad 4:
Pred koncosemstrálnou previerkou sa zistilo, že poskytovateľ poskytol časť svojich riešení počas 3. až 5. sady domácich úloh prijímateľovi.
U poskytovateľa a prijímateľa/plagiátora budú všetky doteraz získané body anulované.
Maximálny počet bodov, ktoré ešte môžu získať je 15 bodov za koncosemestrálnu previerku.
O danom incidente budú informovaní všetci kolegovia a daný prehrešok bude posunutý etickej komisii, ktorá môže rozhodnúť aj vylúčením zo štúdia.
Príklad 5:
V poslednom týždni semestra sa zistilo, že poskytovateľ poskytol svoje riešenia domácich úloh počas riešenia 1. sady domácich úloh druhej skupine.
U poskytovateľa a prijímateľov budú všetky doteraz získané body anulované.
Keďže poskytovateľ už absolvoval koncosemestrálnu previerku, z cvičení získava nula bodov.
Keďže prijímatelia ešte nepísali koncosemestrálnu previerku, môžu získať maximálne 15 bodov.
O danom incidente budú informovaní všetci kolegovia a daný prehrešok bude posunutý etickej komisii, ktorá môže rozhodnúť aj vylúčením zo štúdia.
SELECT
p.krstne AS Pacient,
l.krstne AS Lekar,
SUM(n.poplatok) AS Poplatok
FROM navstevy n
JOIN lekari l ON l.idL = n.idL
JOIN pacienti p ON p.idP = n.idP
WHERE
n.id < 4
GROUP BY p.krstne, l.krstne
Pomôcka: Query-converter – SQL to Relational Algebra (www.grammaticalframework.org)
DBMS | MySQL | Microsoft SQL Server |
---|---|---|
Developer |
|
|
Licence |
|
|
Website | ||
Dokumentácia | ||
Tutorial | ||
Comments | ||
CREATE/ALTER/DROP DATABASE |
|
|
Schema | ||
CREATE/ALTER/DROP TABLE |
|
|
Data Types | ||
Type Conversion | ||
Constraints |
|
|
INSERT/UPDATE/DELETE | ||
SHOW | ||
SELECT | ||
Aliases |
|
|
Sorting Data |
|
|
Limiting Rows | ||
Filtering Data |
|
|
Operators and Functions | ||
Joining Tables |
|
|
Grouping Data |
|
|
Subqueries |
|
|
Set Operators |
|
|
Extension of the SQL | MySQL (Oracle) | Transact-SQL (Microsoft SQL Server) |
---|---|---|
Data Types | ||
Operators and Functions | ||
Variables | ||
Flow Control | ||
Temporary Tables | ||
|
|
Hlavnou výhodou indexu je možnosť rýchleho vyhľadávania záznamov podľa nejakej podmienky.
Výber indexu a jeho typu treba dôkladne premyslieť. Existujú všeobecné odporúčania, kedy (ne)použiť index a aký typ. SQL Server má index-tuning system: Database Tuning Advisor (DTA).
Treba mať na pamäti, že ak index sa skladá z viacerých atribútov, potom pri vkladaní nových riadkov alebo modifikovaní daných stĺpcov prebieha viac dodatočných úkonov, súvisiacich s údržbou indexu.
B+ strom je univerzálny (rôzne kľúče), jeho hlavnou výhodou je rozsahové vyhľadávanie, má dynamickú štruktúru.
Po úspešnom absolvovaní predchádzajúcich týždoch Vás presunuli na medicínsky projekt. Dostali ste za úlohu spravovať databázu polikliniky. Importnite si ju a zoznámte sa s jej štruktúrou.
Zobrazte informácie o pacientoch.
SELECT
-- Informacie o pacientoch
SELECT
*
FROM pacienti;
Zobrazte len pacientov, ktorí majú príjem nad 10000.
SELECT
WHERE
-- Informacie o pacientoch
SELECT
*
FROM pacienti
WHERE mesPrijem > 10000;
Zobrazte pacientov, ktorí majú uvedený príjem.
SELECT
WHERE
IS NOT NULL
-- Informacie o pacientoch, ktori maju mes. prijem uvedeny
SELECT
*
FROM pacienti
WHERE mesPrijem IS NOT NULL;
Zobrazte najväčší mesačný príjem spomedzi pacientov.
MAX
-- Maximalny mesacny prijem
SELECT
MAX(mesPrijem)
FROM pacienti;
Zobrazte meno pacienta, ktorý ma najväčší mesačný príjem.
MAX
-- Meno pacienta, ktory ma mes. prijem 35000
SELECT
krstne
FROM pacienti
WHERE mesPrijem = 35000;
-- Meno pacienta, ktory ma najvacsi mesacny prijem
SELECT
krstne
FROM pacienti
WHERE mesPrijem = (SELECT MAX(mesPrijem) FROM pacienti);
Zistite koľko návštev mal lekár s id = 3.
COUNT
-- Pocet navstev u lekara s id = 3
SELECT
COUNT(*) 'Počet návštev'
FROM navstevy
WHERE idL = 3;
Zobrazte počet návštev u jednotlivých lekárov.
COUNT
-- Pocet navstev u jednotlivych lekarov
SELECT
idL,
COUNT(*) 'Počet návštev'
FROM navstevy
GROUP BY idL;
Zobrazte koľko rôznych pacientov navštívilo jednotlivých lekárov.
COUNT
DISTINCT
-- Pocet roznych pacientov u jednotlivych lekarov
SELECT
idL,
COUNT(DISTINCT idP) 'Počet pacientov'
FROM navstevy
GROUP BY idL;
Zobrazte lekárov a počet návštev, ktorí mali viac ako 3 návštevy.
COUNT
-- Pocet navstev u jednotlivych lekarov, ktori mali viac ako 3 navstevy
SELECT
idL,
COUNT(*) 'Počet návštev'
FROM navstevy
GROUP BY idL
HAVING COUNT(*) > 3;
Zobrazte počet návštev jednotlivých pacientov.
COUNT
-- Pocet navstev jednotlivych pacientov
SELECT
idP,
COUNT(*) 'Počet návštev'
FROM navstevy
GROUP BY idP;
Zobrazte počet návštev pacientov u jednotlivých lekárov.
COUNT
-- Pocet navstev pacientov u lekarov
SELECT
idP Pacient,
idL Lekar,
COUNT(*) Pocet
FROM navstevy
GROUP BY idP, idL
ORDER By idP, idL;
-- alebo
SELECT
idP Pacient,
idL Lekar,
COUNT(*) Pocet
FROM navstevy
GROUP BY idL, idP
ORDER By idP, idL;
Zobrazte návštevy medzi 5.7. a 9.9.2008.
BETWEEN
-- Navstevy medzi 5.7.2008 a 9.9.2008
SELECT
*
FROM navstevy
WHERE den BETWEEN '2008-07-05' AND '2008-09-09';
Zobrazte počet návštev medzi 5.7. a 9.9.2008.
COUNT
BETWEEN
-- Pocet navstev medzi 5.7.2008 a 9.9.2008
SELECT
COUNT(*)
FROM navstevy
WHERE den BETWEEN '2008-07-05' AND '2008-09-09';
Zobrazte počet návštev medzi 5.7. a 9.9.2008 u jednotlivých lekárov.
COUNT
BETWEEN
-- Pocet navstev medzi 5.7.2008 a 9.9.2008
SELECT
idL,
COUNT(*) 'Počet návštev'
FROM navstevy
WHERE den BETWEEN '2008-07-05' AND '2008-09-09';
Zobrazte prvý deň návštevy.
MIN
-- Prvy den navstevy
SELECT
MIN(den)
FROM navstevy;
Zobrazte informácie o prvej návšteve.
MIN
-- Prvy den navstevy
SELECT
MIN(den)
FROM navstevy;
Zistite ktorý lekár by bol v abecednom poradí posledný.
MAX
-- Posledne krstne meno lekara v abecede
SELECT
MAX(krstne)
FROM lekari;
Dostali ste za úlohu spravovať databázu polikliniky. Importnite si ju a zoznámte sa s jej štruktúrou.
Importnite si databázu množiny a zoznámte sa s jej štruktúrou. Zobrazte výsledky spojenia dvoch tabuliek a a b cez stĺpec x pomocou CROSS JOIN, (INNER) JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN.
CROSS JOIN
(INNER) JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
USE mnoziny;
-- CROSS JOIN
SELECT * FROM a, b;
-- INNER JOIN
SELECT * FROM a JOIN b ON b.x = a.x;
-- alebo
SELECT * FROM a JOIN b USING(x);
-- LEFT OUTER JOIN
SELECT * FROM a LEFT OUTER JOIN b ON b.x = a.x;
-- alebo
SELECT * FROM a LEFT OUTER JOIN b USING(x);
-- RIGHT OUTER JOIN
SELECT * FROM a RIGHT OUTER JOIN b ON b.x = a.x;
-- alebo
SELECT * FROM a RIGHT OUTER JOIN b USING(x);
V ďalšich zadaniach budete pracovať s databázou poliklinika. Zobrazte id rôznych pacientov, ktorí už kedysi navštívili zubného lekára.
JOIN
USE poliklinika;
-- Neefektivne riesenie - karteziansky sucin dvoch tabuliek
SELECT
DISTINCT n.idp
FROM
navstevy n, -- <=> navstevy AS n,
lekari l
WHERE
l.idl = n.idl AND l.spec = 'zubny';
-- (optimizer to v skutocnosti radsej prevedie na riesenie nizsie)
-- Spravne riesenie pomocou prepajacieho operatora JOIN
SELECT
DISTINCT n.idp
FROM navstevy n
JOIN lekari l ON l.idl = n.idl
WHERE
l.spec = 'zubny';
-- Ak prepajame dve tabulky na identickych stlpcov, tak MySQL podporuje takuto skratenu syntax.
SELECT
DISTINCT n.idp
FROM navstevy n
JOIN lekari l USING(idl)
WHERE
l.spec = 'zubny';
Zobrazte id pacientov, ktorí navštívili zubára, pričom vypíšte aj dátum návštevy.
JOIN
SELECT
n.idp,
n.den
FROM navstevy n
JOIN lekari l USING (idl)
WHERE
l.spec = 'zubny';
Zobrazte id pacientov, ktorí navštívili zubára, pričom vypíšte aj dátum návštevy, meno lekára a meno pacienta.
JOIN
SELECT
p.idp,
p.krstne 'Meno pacienta',
l.krstne 'Meno lekara',
n.den
FROM navstevy n
JOIN lekari l ON l.idL = n.idL
JOIN pacienti p ON p.idP = n.idP
WHERE
l.spec = 'zubny';
-- alebo pomocou USING
SELECT
p.idp,
p.krstne 'Meno pacienta',
l.krstne 'Meno lekara',
n.den
FROM navstevy n
JOIN lekari l USING (idl)
JOIN pacienti p USING (idp)
WHERE
l.spec = 'zubny';
-- Skratena syntax pre viac JOIN operatorov
SELECT
p.idp,
p.krstne 'Meno',
l.krstne 'Meno lekara',
n.den
FROM navstevy n
JOIN (lekari l, pacienti p) USING (idl, idp)
WHERE
l.spec = 'zubny';
-- alebo
SELECT
p.idp,
p.krstne 'Meno',
n.den
FROM navstevy n
JOIN (lekari l, pacienti p) ON l.idl = n.idl AND p.idp = n.idp
WHERE
l.spec = 'zubny';
Zobrazte počet všetkých návštev u zubára.
JOIN
SELECT
COUNT(n.idp)
FROM navstevy n
JOIN lekari l ON l.idl = n.idl
WHERE
l.spec = 'zubny';
-- alebo
SELECT
COUNT(*)
FROM
(
SELECT
n.idP, n.den
FROM navstevy n
JOIN lekari l ON l.idL = n.idL
WHERE
l.spec = 'Zubny'
) AS T;
-- alebo
SELECT
SUM(T.pocet)
FROM
(
SELECT
n.idP,
COUNT(*) AS pocet
FROM navstevy n
JOIN lekari l ON L.idL = N.idL
WHERE
l.spec = 'Zubny'
GROUP BY n.idP
) AS T;
Zobrazte mená pacientov, ktorí už navštívili lekára Ota alebo Imra. Uveďte aj mená lekárov a výsledok zoraďte podľa mien lekárov, pacientov.
JOIN
SELECT
p.krstne 'Pacient',
l.krstne 'Lekar'
FROM pacienti p
JOIN navstevy n ON n.idp = p.idp
JOIN lekari l ON l.idl = n.idl
WHERE
l.krstne IN ('Oto' , 'Imro')
ORDER BY l.krstne, p.krstne;
Zobrazte mená pacientov, ktorí už navštívili lekára Ota a Imra.
JOIN
-- NEFUNGUJE
SELECT
p.krstne 'Pacient',
l.krstne 'Lekar'
FROM pacienti p
JOIN navstevy n ON n.idp = p.idp
JOIN lekari l ON l.idl = n.idl
WHERE
l.krstne = 'Oto' AND l.krstne = 'Imro' # ved navsteva prislucha iba jednemu lekarovi, a neexistuje navsteva kde je lekar aj Oto aj Imro
ORDER BY l.krstne, p.krstne;
-- SPRAVNE
SELECT
p.krstne 'Pacient',
lekar_oto.krstne 'Lekar Oto',
lekar_imro.krstne 'Lekar Imro'
FROM pacienti p
JOIN navstevy navstevy_u_ota ON p.idP = navstevy_u_ota.idP
JOIN lekari lekar_oto ON navstevy_u_ota.idL = lekar_oto.idL
JOIN navstevy navstevy_u_imra ON p.idP = navstevy_u_imra.idP
JOIN lekari lekar_imro ON navstevy_u_imra.idL = lekar_imro.idL
WHERE
lekar_oto.krstne = 'Oto' AND lekar_imro.krstne = 'Imro';
-- alebo
SELECT
p2.krstne 'Pacient'
FROM navstevy n2
JOIN lekari l2 ON l2.idL = n2.idL
JOIN pacienti p2 ON p2.idP = n2.idP
WHERE
l2.krstne = 'Imro'
AND p2.idP IN
(
SELECT
p.idP
FROM navstevy n
JOIN lekari l ON l.idL = n.idL
JOIN Pacienti p ON p.idP = n.idP
WHERE L.krstne = 'Oto'
);
Dostali ste za úlohu navrhnúť štruktúru na sprevádzkovanie eshopu.
Navrhnite vhodnú štruktúru na sprevádzkovanie eshopu.
Dostali ste za úlohu spravovať databázu polikliniky. Importnite si ju a zoznámte sa s jej štruktúrou.
Importnite si databázu množiny a zoznámte sa s jej štruktúrou. Zobrazte výsledky zjednotenia tabuliek a a b.
UNION
USE mnoziny;
-- Zjednotenie
SELECT x, y FROM a
UNION
SELECT x, y FROM b;
Zobrazte výsledky zjednotenia tabuliek a a b tak, aby sa riadky mohli opakovať.
UNION ALL
USE mnoziny;
-- Zjednotenie s opakovanim
SELECT x, y FROM a
UNION ALL
SELECT x, y FROM b;
Zobrazte výsledky prieniku tabuliek a a b.
INTERSECT
IN
EXISTS
INNER JOIN
NATURAL JOIN
USE mnoziny;
-- Prienik
-- Prienik pomocou IN
SELECT x, y
FROM a
WHERE (x, y) IN (SELECT x, y FROM b);
-- Prienik pomocou EXISTS
SELECT x, y
FROM a
WHERE EXISTS (SELECT * FROM b WHERE b.x = a.x AND b.y = a.y);
-- Prienik pomocou INNER JOIN
SELECT a.x, a.y
FROM a
JOIN b ON b.x = a.x AND b.y = a.y;
-- Prienik pomocou INNER JOIN
SELECT DISTINCT a.x AS x, a.y AS y
FROM a
INNER JOIN b USING (x,y);
-- Prienik pomocou NATURAL JOIN
SELECT x, y
FROM a
NATURAL JOIN b;
-- Prienik pomocou INTERSECT (od verzie 8.0.31)
TABLE a INTERSECT TABLE b;
Zobrazte výsledky rozdielu tabuliek a a b.
EXCEPT
NOT IN
NOT EXISTS
LEFT OUTER JOIN
USE mnoziny;
-- Rozdiel
-- Rozdiel pomocou NOT IN
SELECT x, y
FROM a
WHERE (x, y) NOT IN (SELECT x, y FROM b);
-- Rozdiel pomocou NOT EXISTS
SELECT x, y
FROM a
WHERE NOT EXISTS (SELECT * FROM b WHERE b.x = a.x AND b.y = a.y);
-- Rozdiel pomocou LEFT OUTER JOIN
SELECT a.x, a.y
FROM a
LEFT OUTER JOIN b ON b.x = a.x AND b.y = a.y
WHERE b.x IS NULL;
-- Rozdiel pomocou LEFT OUTER JOIN
SELECT DISTINCT a.x AS x, a.y AS y
FROM a
LEFT OUTER JOIN b USING (x,y)
WHERE b.x IS NULL;
-- Rozdiel pomocou EXCEPT (od verzie 8.0.31)
TABLE a EXCEPT TABLE b;
Zobrazte mená pacientov, ktorí už navštívili lekára Ota alebo Imra. Uveďte aj mená lekárov a výsledok zoraďte podľa mien lekárov, pacientov.
JOIN
UNION
-- Len s pouzitim JOIN
SELECT
p.krstne 'Pacient',
l.krstne 'Lekar'
FROM pacienti p
JOIN navstevy n ON n.idp = p.idp
JOIN lekari l ON l.idl = n.idl
WHERE
l.krstne IN ('Oto' , 'Imro')
ORDER BY l.krstne, p.krstne;
-- alebo
-- S pouzitim JOIN a UNION
SELECT
p.krstne 'Pacient',
l.krstne 'Lekar'
FROM pacienti p
JOIN navstevy n ON n.idp = p.idp
JOIN lekari l ON n.idl = l.idl
WHERE
l.krstne = 'Oto'
UNION
SELECT
p.krstne 'Pacient',
l.krstne 'Lekar'
FROM pacienti p
JOIN navstevy n ON n.idp = p.idp
JOIN lekari l ON n.idl = l.idl
WHERE
l.krstne = 'Imro'
ORDER BY Lekar, Pacient;
-- alebo
-- Bez pouzitia JOIN a pouzitim UNION
SELECT
p.krstne 'Pacient',
'Oto' as 'Lekar'
FROM pacienti p
WHERE
idp IN
(
SELECT n.idp FROM navstevy n WHERE n.idl =
(
SELECT l.idl FROM lekari l where krstne = 'Oto'
)
)
UNION
SELECT
p.krstne 'Pacient',
'Imro' as 'Lekar'
FROM pacienti p
WHERE
idp IN
(
SELECT n.idp FROM navstevy n WHERE n.idl =
(
SELECT l.idl FROM lekari l where krstne = 'Imro'
)
)
ORDER BY Lekar, Pacient;
Zobrazte mená pacientov, ktorí už navštívili lekára Ota a Imra.
JOIN
IN
EXISTS
-- S pouzitim JOIN
SELECT
p.krstne 'Pacient',
lekar_oto.krstne 'Lekar Oto',
lekar_imro.krstne 'Lekar Imro'
FROM pacienti p
JOIN navstevy navstevy_u_ota ON p.idP = navstevy_u_ota.idP
JOIN lekari lekar_oto ON navstevy_u_ota.idL = lekar_oto.idL
JOIN navstevy navstevy_u_imra ON p.idP = navstevy_u_imra.idP
JOIN lekari lekar_imro ON navstevy_u_imra.idL = lekar_imro.idL
WHERE
lekar_oto.krstne = 'Oto' AND lekar_imro.krstne = 'Imro';
-- alebo
-- S pouzitim JOIN a IN
SELECT
p2.krstne 'Pacient'
FROM navstevy n2
JOIN lekari l2 ON l2.idL = n2.idL
JOIN pacienti p2 ON p2.idP = n2.idP
WHERE
l2.krstne = 'Imro'
AND p2.idP IN
(
SELECT
p.idP
FROM navstevy n
JOIN lekari l ON l.idL = n.idL
JOIN Pacienti p ON p.idP = n.idP
WHERE L.krstne = 'Oto'
);
-- alebo
-- Bez pouzitia JOIN
SELECT
p.krstne 'Pacient'
FROM pacienti p
WHERE
idp IN
(
SELECT idp
FROM
(
SELECT n.idp FROM navstevy n WHERE n.idl =
(
SELECT l.idl FROM lekari l WHERE krstne = 'Oto'
)
) a
WHERE EXISTS
(
SELECT *
FROM
(
SELECT n.idp FROM navstevy n WHERE n.idl =
(
SELECT l.idl FROM lekari l WHERE krstne = 'Imro'
)
) b
WHERE b.idp = a.idp
)
);
Zobrazte mená pacientov, ktorí navštívili lekára s id 1.
JOIN
IN
ANY
EXISTS
-- S pouzitim JOIN
SELECT
p.krstne 'Pacient'
FROM pacienti p
JOIN navstevy n ON n.idP = p.idP
WHERE
idL = 1;
-- alebo
-- S pouzitim IN
SELECT
p.krstne 'Pacient'
FROM pacienti p
WHERE p.idP IN
(
SELECT n.idP FROM navstevy n WHERE n.idL = 1
);
-- alebo
-- S pouzitim ANY
SELECT
p.krstne 'Pacient'
FROM pacienti p
WHERE p.idP = ANY
(
SELECT n.idP FROM navstevy n WHERE n.idL = 1
);
-- alebo
-- S pouzitim EXISTS
SELECT
p.krstne 'Pacient'
FROM pacienti p
WHERE EXISTS -- EXISTS vrati true ak vnutorny SELECT vrati aspon jeden riadok
(
SELECT n.idP FROM navstevy n WHERE n.idL = 1 AND n.idP = p.idP -- korelacia, t.j. stlpec p.idP z vonkajsieho SELECT pouzijeme vo vnutorom SELECT
);
Zobrazte počet všetkých návštev u zubára.
JOIN
IN
COUNT
SUM
SELECT
COUNT(n.idp)
FROM navstevy n
JOIN lekari l ON l.idl = n.idl
WHERE
l.spec = 'zubny';
-- alebo
SELECT
COUNT(*)
FROM
(
SELECT
n.idP, n.den
FROM navstevy n
JOIN lekari l ON l.idL = n.idL
WHERE
l.spec = 'Zubny'
) AS T;
-- alebo
SELECT
SUM(T.pocet)
FROM
(
SELECT
n.idP,
COUNT(*) AS pocet
FROM navstevy n
JOIN lekari l ON L.idL = N.idL
WHERE
l.spec = 'Zubny'
GROUP BY n.idP
) AS T;
-- alebo
-- S pouzitim IN
SELECT
COUNT(idN)
FROM
navstevy
WHERE
idL IN (
SELECT
idl
FROM
lekari
WHERE
spec = 'Zubny'
);
Zistite mená pacientov, ktorí už kedysi navštívili zubného lekára.
JOIN
IN
SELECT
p.krstne 'Pacient'
FROM pacienti p
JOIN navstevy n ON n.idP = p.idP
JOIN lekari l ON l.idL = n.idL
WHERE
l.spec = 'zubny';
-- alebo
-- S pouzitim IN
SELECT
p.krstne 'Pacient'
FROM pacienti p
WHERE
p.idP IN
(
SELECT n.idP FROM navstevy n
WHERE n.idL IN
(
SELECT idL FROM lekari l
WHERE l.spec = 'Zubny'
)
);
Zobrazte dátum narodenia najmladšieho/-ej lekára/-ky.
MAX
SELECT MAX(datnar) 'Najmladsi'
FROM lekari;
Zobrazte krstné meno, špecializáciu a dátum narodenia najmladšieho/-ej lekára/-ky.
MAX
-- Pomocou MAX
SELECT
l.krstne, l.spec, l.datnar
FROM lekari l
WHERE
l.datNar =
(
SELECT MAX(l1.datnar) FROM lekari l1
);
-- alebo
-- Pomocou ALL
SELECT
l.krstne, l.spec, l.datnar
FROM lekari l
WHERE
l.datNar >= ALL
(
SELECT l1.datnar FROM lekari l1
);
Nájdite údaje druhého najmladšieho/-ej lekára/-ky.
MAX
-- Pomocou MAX
SELECT
l.krstne, l.spec, l.datnar
FROM lekari l
WHERE
l.datNar =
(
SELECT
MAX(l2.datnar)
FROM
lekari l2
WHERE
l2.datnar <
(
SELECT
MAX(l1.datnar)
FROM
lekari l1
)
);
-- alebo
-- Pomocou ALL
SELECT
l.krstne, l.spec, l.datnar
FROM
(
SELECT
l3.krstne, l3.spec, l3.datnar
FROM
lekari l3
WHERE
l3.datnar <
(
SELECT
l2.datnar
FROM
lekari l2
WHERE
l2.datnar >= ALL
(
SELECT
l1.datnar
FROM
lekari l1
)
)
) l
WHERE
l.datnar >= ALL (
SELECT
l3.datnar
FROM
lekari l3
WHERE
l3.datnar <
(
SELECT
l2.datnar
FROM
lekari l2
WHERE
l2.datnar >= ALL
(
SELECT
l1.datnar
FROM
lekari l1
)
)
);
Nájdite všetky údaje o treťom/-tej najmladšom/-ej lekárovi/-ke.
MAX
-- Pomocou MAX
SELECT *
FROM lekari
WHERE
datnar =
(
SELECT max(l3.datNar)
FROM lekari l3
WHERE l3.datNar <
(
SELECT max(l2.datnar)
FROM lekari l2
WHERE l2.datNar <
(
SELECT max(l1.datnar) FROM lekari l1
)
)
);
Zistite koľko zarobili jednotliví lekári. Vo výsledku chceme vidieť krstné meno lekára a ich celkový príjem.
SUM
SELECT
l.krstne,
SUM(n.poplatok) as prijem
FROM navstevy n
JOIN lekari l ON l.idL = n.idL
GROUP BY l.krstne;
Zistite koľko zarobili jednotliví lekári. Vo výsledku chceme vidieť aj koľko zarobili dokopy.
SUM
WITH ROLLUP
SELECT
CASE WHEN l.krstne IS NULL THEN 'Spolu' ELSE l.krstne END krstne,
SUM(n.poplatok) as prijem
FROM navstevy n
JOIN lekari l ON l.idL = n.idL
GROUP BY l.krstne
WITH ROLLUP;
DÁTUM | DOMÁCI | HOSTIA | VÝSLEDOK |
---|---|---|---|
13.05.2022 15:20 | Francúzsko | Slovensko | 2:4 |
13.05.2022 19:20 | Nemecko | Kanada | 3:5 |
14.05.2022 11:20 | Dánsko | Kazachstan | 9:1 |
14.05.2022 15:20 | Švajčiarsko | Taliansko | 5:2 |
14.05.2022 19:20 | Slovensko | Nemecko | 1:2 |
15.05.2022 11:20 | Taliansko | Kanada | 1:6 |
15.05.2022 15:20 | Francúzsko | Kazachstan | 2:1 |
15.05.2022 19:20 | Dánsko | Švajčiarsko | 0:6 |
16.05.2022 15:20 | Slovensko | Kanada | 1:5 |
16.05.2022 19:20 | Francúzsko | Nemecko | 2:3 |
17.05.2022 15:20 | Taliansko | Dánsko | 1:2 |
17.05.2022 19:20 | Švajčiarsko | Kazachstan | 3:2 |
18.05.2022 15:20 | Francúzsko | Taliansko | 2:1 pp |
18.05.2022 19:20 | Švajčiarsko | Slovensko | 5:3 |
19.05.2022 17:00 | Nemecko | Dánsko | 1:0 |
19.05.2022 20:30 | Kanada | Kazachstan | 6:3 |
20.05.2022 15:20 | Nemecko | Taliansko | 9:4 |
20.05.2022 19:20 | Kazachstan | Slovensko | 3:4 |
21.05.2022 11:20 | Dánsko | Francúzsko | 3:0 |
21.05.2022 15:20 | Kanada | Švajčiarsko | 3:6 |
21.05.2022 19:20 | Taliansko | Slovensko | 1:3 |
22.05.2022 15:20 | Kazachstan | Nemecko | 4:5 |
22.05.2022 19:20 | Švajčiarsko | Francúzsko | 5:2 |
23.05.2022 15:20 | Kazachstan | Taliansko | 5:2 |
23.05.2022 19:20 | Kanada | Dánsko | 2:3 |
24.05.2022 11:20 | Nemecko | Švajčiarsko | 3:4 sn |
24.05.2022 15:20 | Slovensko | Dánsko | 7:1 |
24.05.2022 19:20 | Kanada | Francúzsko | 7:1 |
Dátum | Deň | Domáci | Hostia | Konečný stav |
---|---|---|---|---|
13.05.2022 15:20 | utorok | Francúzsko | Slovensko | Vyhrali |
14.05.2022 19:20 | sobota | Slovensko | Nemecko | Prehrali |
16.05.2022 15:20 | pondelok | Slovensko | Kanada | Prehrali |
18.05.2022 19:20 | streda | Švajčiarsko | Slovensko | Prehrali |
20.05.2022 19:20 | piatok | Kazachstan | Slovensko | Vyhrali |
21.05.2022 19:20 | sobota | Taliansko | Slovensko | Vyhrali |
24.05.2022 15:20 | utorok | Slovensko | Dánsko | Vyhrali |
Dostali ste za úlohu spravovať upravenú databázu osôb. Importnite si ju a zoznámte sa s jej štruktúrou.
Importnite si databázu osôb a zoznámte sa s jej štruktúrou. Vložte záznamy z pôvodnej databázy osôb do novej databázy.
INSERT SELECT
USE OsobaDB2;
INSERT osoba(id,meno,priezvisko,rodne_priezvisko,dat_nar,dat_smrti,pohlavie, vyska, vaha)
SELECT id,meno,priezvisko,rodne_priezvisko,dat_nar,dat_smrti,pohlavie, vyska, vaha
FROM osobadb.osoba;
Vložte záznam s id 14 do nového záznamu (s novým id 24).
INSERT SELECT
INSERT osoba(id,meno,priezvisko,rodne_priezvisko,datum_narodenia,dat_smrti,pohlavie, vyska, vaha)
SELECT 24,meno,priezvisko,rodne_priezvisko,dat_nar,dat_smrti,pohlavie, vyska, vaha
FROM osoba
WHERE id = 14;
Osoba s novovytvoreným záznam s id = 24 sa vydala. Upravte priezvisko na Dáša, pôvodné priezvisko nastavte podľa aktuálneho priezviska a nové priezvisko osoby je Dášová.
UPDATE
SET SQL_SAFE_UPDATES = 0;
UPDATE osoba
SET meno = 'Dáša', rodne_priezvisko = priezvisko, priezvisko = 'Dášová'
WHERE id = 24;
Odstráňte záznam s id = 24.
DELETE
SET SQL_SAFE_UPDATES = 0;
DELETE FROM osoba
WHERE id = 24;
Pridajte stĺpec vek a zaktualizujte vek u žijúcich osôb.
ALTER TABLE
ADD COLUMN
-- Pridanie stlpca
ALTER TABLE osoba ADD COLUMN vek INT;
-- alebo
ALTER TABLE osoba ADD vek INT;
-- Aktualizacia veku
UPDATE Osoba
SET vek = TIMESTAMPDIFF(YEAR, dat_nar, CURRENT_DATE())
WHERE dat_smrti NULL;
U zosnulých osôb zaktualizujte vek na vek úmrtia. Nastavte tĺpec vek na povinný s tým, že ak sa pri pridaní záznamu vek neuvedie, nastaví sa jeho hodnota na nulu.
ALTER TABLE
MODIFY COLUMN
DEFAULT
-- Aktualizacia veku
UPDATE Osoba
SET vek = TIMESTAMPDIFF(YEAR, dat_nar, IFNULL(dat_smrti, CURRENT_DATE()))
WHERE dat_smrti IS NOT NULL;
-- Modifikacia stlpca
ALTER TABLE osoba MODIFY COLUMN vek INT NOT NULL DEFAULT 0;
-- alebo
ALTER TABLE osoba MODIFY vek INT NOT NULL DEFAULT 0;
-- alebo
ALTER TABLE osoba CHANGE COLUMN vek vek INT NOT NULL DEFAULT 0;
-- alebo
ALTER TABLE osoba CHANGE vek vek INT NOT NULL DEFAULT 0;
Premenujte stĺpec vek na pocet_rokov.
ALTER TABLE
CHANGE COLUMN
ALTER TABLE osoba CHANGE COLUMN vek pocet_rokov INT NOT NULL DEFAULT 0;
-- alebo
ALTER TABLE osoba CHANGE vek pocet_rokov INT NOT NULL DEFAULT 0;
Odstráňte stĺpec vek/pocet_rokov.
ALTER TABLE
DROP COLUMN
ALTER TABLE osoba DROP COLUMN pocet_rokov;
-- alebo
ALTER TABLE osoba DROP pocet_rokov;
Zistite aké integritné obmedzenia už sú v databáze vytvorené.
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
SELECT * FROM information_schema.table_constraints
WHERE constraint_schema = 'osobadb2';
Pridajte primárny kľúč nad stĺpcom id a nastavte, aby sa automaticky zvyšovali hodnoty od 101. Overte správanie sa primárného kľúča na vyhovujúcich/nevyhovujúcich záznamoch.
PRIMARY KEY
ALTER TABLE osoba CHANGE id id INT NOT NULL PRIMARY KEY AUTO_INCREMENT;
-- alebo len pridanie kluca
ALTER TABLE osoba ADD PRIMARY KEY (id);
ALTER TABLE osoba AUTO_INCREMENT = 101;
Odstráňte primárny kľúč nad tabuľkou osôb.
PRIMARY KEY
ALTER TABLE osoba DROP PRIMARY KEY;
Pridajte obmedzenie, že pohlavie môže nadobúdať len hodnoty m alebo z. Pokúste sa pridať záznamy, ktoré vyhovujú/nevyhovujú danej podmienke. Na konci odstránte dané obmedzenie a skúste vložiť nevyhovujúci záznam.
CHECK
ALTER TABLE osoba ADD CONSTRAINT CHK_pohlavie CHECK(pohlavie IN('m','z'));
-- alebo
ALTER TABLE osoba ADD CHECK(pohlavie IN('m','z'));
-- odstranenie
ALTER TABLE osoba DROP CHECK CHK_pohlavie;
Pridajte obmedzenie, kde meno alebo priezvisko musia byť uvedené. Pokúste sa pridať záznamy, ktoré vyhovujú/nevyhovujú danej podmienke. Na konci odstránte dané obmedzenie a skúste vložiť nevyhovujúci záznam.
CHECK
ALTER TABLE osoba ADD CONSTRAINT CHK_not_null CHECK ( meno is not null or priezvisko is not null );
-- alebo
ALTER TABLE osoba ADD CHECK ( meno is not null or priezvisko is not null );
-- odstranenie
ALTER TABLE osoba DROP CHECK CHK_not_null;
Pridajte obmedzenie, kde trojica pohlavie, výška a váha musia byť jedinečné. Pokúste sa pridať záznamy, ktoré vyhovujú/nevyhovujú danej podmienke. Na konci odstránte dané obmedzenie a skúste vložiť nevyhovujúci záznam.
UNIQUE
ALTER TABLE osoba ADD CONSTRAINT UQ_pvv UNIQUE (pohlavie, vaha, vyska);
-- alebo
ALTER TABLE osoba ADD UNIQUE (pohlavie, vaha, vyska);
-- odstranenie
ALTER TABLE osoba DROP INDEX UQ_pvv;
Pridajte stĺpce otec, matka, ktoré budú zároveň cudzím kľúčom. Pokúste sa pridať záznamy, ktoré vyhovujú/nevyhovujú danej podmienke. Na konci odstránte dané obmedzenie a skúste vložiť nevyhovujúci záznam.
FOREIGN KEY
ALTER TABLE osoba ADD otec INT;
ALTER TABLE osoba ADD CONSTRAINT FK_otec FOREIGN KEY (otec) REFERENCES osoba (id);
ALTER TABLE osoba ADD COLUMN matka INTEGER, ADD FOREIGN KEY (matka) REFERENCES osoba (id);
-- odstranenie
ALTER TABLE osoba FOREIGN KEY FK_otec;
ALTER TABLE osoba FOREIGN KEY FK_matka;
Povoľte/zakážte integritné obmedzenie pre cudzie kľúče. Pokúste sa pridať záznamy, ktoré vyhovujú/nevyhovujú danej podmienke.
FOREIGN KEY
SET FOREIGN_KEY_CHECKS=0;
SET FOREIGN_KEY_CHECKS=1;
ALTER TABLE osoba DISABLE KEYS;
ALTER TABLE osoba ENABLE KEYS;
Importnite si databázu MOZ a zoznámte sa s jej štruktúrou. Skúste nastaviť cudzie kľúče tak, aby sa pri aktualizácii hodnoty primárneho kľúča zaktualizovali aj hodnoty cudzích kľučov a pri mazaní miestnosti aby sa miestnosť na oddelení nastavila na NULL hodnotu. Mazanie pracovísk pokial majú zamestnanca nepovolíme.
FOREIGN KEY
USE MOZ;
ALTER TABLE Oddelenie
ADD CONSTRAINT fk_Miest
FOREIGN KEY (Miestnost)
REFERENCES Miestnost(idM)
ON DELETE SET NULL
ON UPDATE CASCADE;
-- RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
ALTER TABLE Zamestnanci
ADD CONSTRAINT fk_PNaOdd FOREIGN KEY (Prac_na_odd)
REFERENCES Oddelenie(Nazov_odd)
ON DELETE NO ACTION
ON UPDATE CASCADE;
-- RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
Importnite si upravenú databázu Osôb a zoznámte sa s jej štruktúrou. Premenujte tabľku Osoba na Person.
RENAME TABLE
USE OsobaDB2;
RENAME TABLE osoba TO person;