Študent po absolvovaní predmetu ovláda princípy relačných databáz, je schopný aplikovať štandardné dátové modely, navrhovať relačné databázy a sformulovať filtračné dopyty.
Zadanie robia iba tí študenti, ktorí sa NEzúčastnili obhajob návrhov projektov z predmetu PAZ1c alebo na prednáške predmetu DBS (matematici). Riešenie projektu pozostáva z nasledujúcich úloh:
Majiteľ reštaurácie U vlka chce nalákať zákazníkov aj tým, že im bude zverejňovať denné menu na internete. Pomôžte mu splniť nasledujúce zadania.
Navrhnite, vytvorte a naplňte tabuľku podľa nasledujúceho vzoru:
| Dátum | Číslo objednávky | Gramáž | Jedlo | Cena | Dostupnosť |
|---|---|---|---|---|---|
| 19.09.2022 | 1 |
150g | Kurací steak plnený syrom a suš. paradajkou, ryža, šalát (1,7) | 6.00€ |
|
| 19.09.2022 | 2 |
350g | Granatiersky pochod, kyslá uhorka (1) | 5.70€ |
|
| 20.09.2022 | 1 |
150g | Debrecínsky guľáš, kolienka (1,3) | 5.80€ |
|
| 20.09.2022 | 2 |
300g | Jablkovo- škoricové pirohy (1,3) | 4.70€ |
|
| 21.09.2022 | 1 |
150g | Hovädzí maďarský guľáš, domáca knedľa (1,7) | 5.80€ |
|
| 21.09.2022 | 2 |
Vyprážaný hermelín, listový šalát, hranolky, dresing (1,3,7) |
|
CREATE DATABASEDROP DATABASEUSECREATE TABLEDROP TABLEINSERTSELECTSHOW DATABASESSHOW TABLES
# Vytvorenie databazy
CREATE DATABASE restauracia_u_vlka;
-- Pouzitie databazy
USE restauracia_u_vlka;
/* Vytvorenie tabulky */
CREATE TABLE denne_menu
(
datum DATE,
cislo INT,
gramaz INT,
jedlo VARCHAR(300),
cena DEC(5,2),
dostupnost BOOL
);
# Vlozenie zaznamu do tabulky
INSERT INTO denne_menu VALUES ('2022-09-19', 1, 150, 'Kurací steak plnený syrom a suš. paradajkou, ryža, šalát (1,7)', 6, true);
-- Vlozenie viac zaznamov do tabulky
INSERT INTO denne_menu VALUES
('2022-09-19', 2, 350, 'Granatiersky pochod, kyslá uhorka (1)', 5.70, true),
('2022-09-20', 1, 150, 'Debrecínsky guľáš, kolienka (1,3)', 5.80, true);
/* Vlozenie zaznamu do tabulky */
INSERT denne_menu VALUES ('2022-09-20', 2, 300, 'Jablkovo- škoricové pirohy (1,3)', 4.70, false);
# Vlozenie zaznamu do tabulky
INSERT denne_menu(datum, cislo, gramaz, jedlo, cena, dostupnost)
VALUES ('2022-09-21', 1, 150, 'Hovädzí maďarský guľáš, domáca knedľa (1,7)', 5.80, NULL);
-- Vlozenie zaznamu do tabulky
INSERT denne_menu(datum, cislo, jedlo) VALUES ('2022-09-21', 2, 'Vyprážaný hermelín, listový šalát, hranolky, dresing (1,3,7)');
/* Zobrazenie zaznamov z tabulky */
SELECT * FROM denne_menu;
Upravte skript z 1. zadania tak, aby sa dal použiť viackrát.
CREATE DATABASE IF NOT EXISTSDROP DATABASE IF EXISTSUSECREATE TABLE IF NOT EXISTSDROP TABLE IF EXITSINSERTSELECT
# Zmazanie databazy ak existuje
DROP DATABASE IF EXISTS restauracia_u_vlka;
# Vytvorenie databazy ak neexistuje
CREATE DATABASE IF NOT EXISTS restauracia_u_vlka;
-- Pouzitie databazy
USE restauracia_u_vlka;
/* Vytvorenie tabulky ak neexistuje */
CREATE TABLE IF NOT EXISTS denne_menu
(
datum DATE,
cislo INT,
gramaz INT,
jedlo VARCHAR(300),
cena DEC(5,2),
dostupnost BOOL
);
# Vlozenie zaznamu do tabulky
INSERT INTO denne_menu VALUES ('2022-09-19', 1, 150, 'Kurací steak plnený syrom a suš. paradajkou, ryža, šalát (1,7)', 6, true);
-- Vlozenie viac zaznamov do tabulky
INSERT INTO denne_menu VALUES
('2022-09-19', 2, 350, 'Granatiersky pochod, kyslá uhorka (1)', 5.70, true),
('2022-09-20', 1, 150, 'Debrecínsky guľáš, kolienka (1,3)', 5.80, true);
/* Vlozenie zaznamu do tabulky */
INSERT denne_menu VALUES ('2022-09-20', 2, 300, 'Jablkovo- škoricové pirohy (1,3)', 4.70, false);
# Vlozenie zaznamu do tabulky
INSERT denne_menu(datum, cislo, gramaz, jedlo, cena, dostupnost)
VALUES ('2022-09-21', 1, 150, 'Hovädzí maďarský guľáš, domáca knedľa (1,7)', 5.80, NULL);
-- Vlozenie zaznamu do tabulky
INSERT denne_menu(datum, cislo, jedlo) VALUES ('2022-09-21', 2, 'Vyprážaný hermelín, listový šalát, hranolky, dresing (1,3,7)');
/* Zobrazenie zaznamov z tabulky */
SELECT * FROM denne_menu;
Zobrazte zoznam databáz a tabuliek.
SHOW DATABASESSHOW TABLES
# Zobrazenie databaz
SHOW DATABASES;
-- Pouzitie databazy
USE restauracia_u_vlka;
-- Zobrazenie tabuliek z aktualnej databazy
SHOW TABLES;
-- Zobrazenie tabuliek z konkretnej databazy
SHOW TABLES FROM restauracia_u_vlka;
Skúste zobraziť obsah tabuľky čo najvernejšie podľa 1. zadania. V sĺpci dostupnosť zobrazíme znak x ak je jedlo nedostupné alebo je NULL, inak zobrazíme reťazec s nulovou veľkosťou.
SELECTASDATE_FORMATCOALESCEIFNULLIF
# Zobrazenie tabulky z 1. zadania
SELECT
DATE_FORMAT(datum, '%d.%m.%Y') AS 'Dátum',
cislo 'Číslo objednávky',
IFNULL(CONCAT(gramaz, 'g'), null) "Gramáž",
jedlo Jedlo,
COALESCE(CONCAT(cena, '€'), null) AS Cena,
IF(dostupnost IS NULL, 'x', IF(dostupnost = false, 'x', '')) AS 'Dostupnosť'
FROM
denne_menu;
Dostali ste za úlohu spravovať databázu osôb. Importnite si ju a zoznámte sa s jej štruktúrou.
Zobrazte zoznam databáz.
SHOW DATABASES
# Zobrazenie databaz
SHOW DATABASES;
Zobrazte zoznam tabuliek z databázy OsobaDB.
SHOW TABLES
-- Pouzitie databazy
USE OsobaDB;
-- Zobrazenie tabuliek z aktualnej databazy
SHOW TABLES;
-- Zobrazenie tabuliek z databazy OsobaDB
SHOW TABLES FROM OsobaDB;
Zobrazte záznamy z tabuľky
SELECT * FROM tabulka
-- Pouzitie databazy OsobaDB
USE OsobaDB;
-- Zobrazenie zaznamov z tabulky Osoba
SELECT * FROM osoba;
Zobrazte len meno, priezvisko a pohlavie z tabuľky
SELECT
-- Zobrazenie stlpcov meno, priezvisko, pohlavie z tabulky Osoba
SELECT meno, priezvisko, pohlavie FROM osoba;
Zobrazte meno, priezvisko a pohlavie a názvy sĺpcov zobrazte po anglicky, t.j. Name, Surname, Gender.
AS
-- Zobrazenie stlpcov meno, priezvisko, pohlavie ako stlpce Name, Surname, Gender z tabulky Osoba
SELECT meno AS 'Name', priezvisko "Surname", pohlavie Gender FROM osoba;
Zobrazte mená osôb.
SELECT
-- Zobrazenie stlpca meno z tabulky Osoba
SELECT meno FROM osoba;
Zobrazte rôzne mená osôb.
DISTINCT
-- Zobrazenie rôznych mien z tabulky Osoba
SELECT DISTINCT meno FROM osoba;
Zoraďte záznamy podľa priezviska vzostupne, ak majú rovnaké priezvisko tak podľa mena zostupne, ak majú rovnaké meno a priezvisko tak podľa dátumu narodenia od najstaršieho.
ORDER BYASCDESC
-- Zoradenie podla priezviska vzostupne, mena zostupne a datumu narodenia vzostupne
SELECT meno, priezvisko, dat_nar 'Dátum narodenia'
FROM osoba
ORDER BY priezvisko ASC, 1 DESC, 'Dátum narodenia';
Zobrazte iba tri najvyššie osoby.
ORDER BYDESCLIMIT
-- Prve tri najvyssie osoby
SELECT *
FROM osoba
ORDER BY vyska DESC
LIMIT 3;
Zobrazte iba piatu a šiestu najvyššiu osobu.
ORDER BYDESCLIMIT
-- Piata a siesta najvyssia osoba
SELECT *
FROM osoba
ORDER BY vyska DESC
LIMIT 4, 2;
-- alebo
SELECT *
FROM osoba
ORDER BY vyska DESC
LIMIT 2 OFFSET 4;
Zobrazte osoby, ktoré majú id menšie ako štyri.
WHERE
-- Osoby, kde id < 4
SELECT *
FROM osoba
WHERE id < 4;
Zobrazte žijúce ženy (nemajú uvedený dátum smrti), ktoré sa vydali (priezvisko a rodné priezvisko sú rôzne), narodili sa pred 1.1.1990 a majú viac ako 160 cm.
WHEREAND
-- Zeny, ktore maju rozne priezvisko ako povodne, su zijuce, narodili sa pred 1.1.1990 a maju viac ako 160
SELECT *
FROM osoba
WHERE pohlavie = 'z' AND priezvisko <> rodne_priezvisko AND dat_smrti IS NULL AND dátum_narodenia < '1990-01-01' AND vyska > 160;
Zobrazte osoby, ktoré majú výšku 167, 169 alebo 182 cm.
WHEREORIN
-- Osoby s vyskou 167, 169, 182
SELECT *
FROM osoba
WHERE vyska = 167 OR vyska = 169 OR vyska = 182;
-- alebo
SELECT * FROM osoba WHERE výška IN (167, 169, 182);
Zobrazte osoby, ktoré sa nevolajú Jozef, Ján ani Zuzana.
WHEREANDNOT IN
-- Osoby, ktore sa nevolaju Jozef, Jan, Zuzana
SELECT *
FROM osoba
WHERE meno != 'Jozef' AND meno <> 'Ján' AND NOT meno = 'Zuzana';
-- alebo
SELECT *
FROM osoba
WHERE meno NOT IN ('Jozef','Ján','Zuzana');
Zobrazte osoby, ktoré vážia medzi 50 a 80 kg.
WHEREANDBETWEEN
-- Osoby, ktore vazia medzi 50 a 80
SELECT *
FROM osoba
WHERE vaha >= 50 AND 80 >= vaha;
-- alebo
SELECT *
FROM osoba
WHERE vaha BETWEEN 50 AND 80;
Zobrazte osoby, ktorých váha je menej ako 50 kg alebo viac ako 80 kg.
WHEREORNOT BETWEEN
-- Osoby, ktore vazia menej ako 50 alebo viac ako 80
SELECT *
FROM osoba
WHERE vaha < 50 OR vaha > 80;
-- alebo
SELECT *
FROM osoba
WHERE vaha NOT BETWEEN 50 AND 80;
LIKEDostali ste za úlohu spravovať databázu osôb. Importnite si ju a zoznámte sa s jej štruktúrou.
Zobrazte osoby, ktorých priezviská začínaju slovom Novák.
LIKE%_
-- Priezviska zacinaju slovom Novak
SELECT *
FROM osoba
WHERE priezvisko LIKE 'Novák%';
Zobrazte osoby, ktorých priezviská obsahujú ová.
LIKE%_
-- Priezviska obsahuju ova
SELECT *
FROM osoba
WHERE priezvisko LIKE '%ová%';
Zobrazte osoby, ktorých priezviská končia na ová.
LIKE%_
-- Priezviska koncia na ova
SELECT *
FROM osoba
WHERE priezvisko LIKE '%ová';
Zobrazte osoby, ktorých priezviská končia na ová a nejaký ľubovoľný neprázdny znak.
LIKE%_
-- Priezviska koncia na ova a nejaky znak
SELECT *
FROM osoba
WHERE priezvisko LIKE '%ová_';
Zobrazte osoby, ktorých priezviská obsahujú znaky o alebo a.
LIKE%_
-- Priezviska obsahuju znaky o alebo a
SELECT *
FROM osoba
WHERE priezvisko LIKE '%o%' OR priezvisko LIKE '%a%';
Zobrazte osoby, ktorých priezviská majú 5 znakov.
LIKE%_CHAR_LENGTH
-- Priezviska maju 5 znakov
SELECT *
FROM osoba
WHERE priezvisko LIKE '_____';
-- alebo
SELECT *
FROM osoba
WHERE CHAR_LENGTH(priezvisko) = 5;
-- alebo
SELECT *
FROM osoba
WHERE CHARACTER_LENGTH(priezvisko) = 5;
Pomocou regulárneho výrazu zobrazte osoby, ktorých priezviská začínaju slovom Novák.
NOT REGEXPREGEXPREGEXP_INSTR()REGEXP_LIKE()REGEXP_REPLACE()REGEXP_SUBSTR()RLIKE
-- Priezviska zacinaju slovom Novak
SELECT *
FROM osoba
WHERE priezvisko REGEXP '^Novák';
-- alebo
SELECT *
FROM osoba
WHERE priezvisko RLIKE '^Novák';
-- alebo
SELECT *
FROM osoba
WHERE REGEXP_LIKE(priezvisko, '^Novák');
Pomocou regulárneho výrazu zobrazte osoby, ktorých priezviská obsahujú ová.
NOT REGEXPREGEXPREGEXP_INSTR()REGEXP_LIKE()REGEXP_REPLACE()REGEXP_SUBSTR()RLIKE
-- Priezviska obsahuju ova
SELECT *
FROM osoba
WHERE priezvisko REGEXP 'ová';
-- alebo
SELECT *
FROM osoba
WHERE priezvisko RLIKE 'ová';
-- alebo
SELECT *
FROM osoba
WHERE REGEXP_LIKE(priezvisko, 'ová');
Pomocou regulárneho výrazu zobrazte osoby, ktorých priezviská končia na ová.
NOT REGEXPREGEXPREGEXP_INSTR()REGEXP_LIKE()REGEXP_REPLACE()REGEXP_SUBSTR()RLIKE
-- Priezviska koncia na ova
SELECT *
FROM osoba
WHERE priezvisko REGEXP 'ová$';
-- alebo
SELECT *
FROM osoba
WHERE priezvisko RLIKE 'ová$';
-- alebo
SELECT *
FROM osoba
WHERE REGEXP_LIKE(priezvisko, 'ová$');
Pomocou regulárneho výrazu zobrazte osoby, ktorých priezviská končia na ová a nejaký ľubovoľný neprázdny znak.
NOT REGEXPREGEXPREGEXP_INSTR()REGEXP_LIKE()REGEXP_REPLACE()REGEXP_SUBSTR()RLIKE
-- Priezviska koncia na ova a nejaky znak
SELECT *
FROM osoba
WHERE priezvisko REGEXP 'ová.$';
-- alebo
SELECT *
FROM osoba
WHERE priezvisko RLIKE 'ová.$';
-- alebo
SELECT *
FROM osoba
WHERE REGEXP_LIKE(priezvisko, 'ová.$');
Pomocou regulárneho výrazu zobrazte osoby, ktorých priezviská obsahujú znaky o alebo a.
NOT REGEXPREGEXPREGEXP_INSTR()REGEXP_LIKE()REGEXP_REPLACE()REGEXP_SUBSTR()RLIKE
-- Priezviska obsahuju znaky o alebo a
SELECT *
FROM osoba
WHERE priezvisko REGEXP '[oa]';
-- alebo
SELECT *
FROM osoba
WHERE priezvisko RLIKE '[oa]';
-- alebo
SELECT *
FROM osoba
WHERE REGEXP_LIKE(priezvisko, '[oa]');
Pomocou regulárneho výrazu zobrazte osoby, ktorých priezviská majú 5 znakov.
NOT REGEXPREGEXPREGEXP_INSTR()REGEXP_LIKE()REGEXP_REPLACE()REGEXP_SUBSTR()RLIKE
-- Priezviska maju 5 znakov
SELECT *
FROM osoba
WHERE priezvisko REGEXP '^.....$';
-- alebo
SELECT *
FROM osoba
WHERE priezvisko REGEXP '^.{5}$';
-- alebo
SELECT *
FROM osoba
WHERE priezvisko RLIKE '^.....$';
-- alebo
SELECT *
FROM osoba
WHERE priezvisko RLIKE '^.{5}$';
-- alebo
SELECT *
FROM osoba
WHERE REGEXP_LIKE(priezvisko, '^.....$');
-- alebo
SELECT *
FROM osoba
WHERE REGEXP_LIKE(priezvisko, '^.{5}$');
Zobrazte osoby, ktorých priezviská neobsahujú (malé i veľké) znaky k, l, m, n, o.
NOT REGEXPREGEXPREGEXP_INSTR()REGEXP_LIKE()REGEXP_REPLACE()REGEXP_SUBSTR()RLIKE
-- Priezviska maju 5 znakov
SELECT *
FROM osoba
WHERE priezvisko NOT REGEXP '[klmnoKLMNO]';
-- alebo
SELECT *
FROM osoba
WHERE priezvisko NOT REGEXP '[k-oK-O]';
-- alebo
SELECT *
FROM osoba
WHERE priezvisko NOT RLIKE '[klmnoKLMNO]';
-- alebo
SELECT *
FROM osoba
WHERE priezvisko NOT RLIKE '[k-oK-O]';
-- alebo
SELECT *
FROM osoba
WHERE NOT REGEXP_LIKE(priezvisko, 'klmnoKLMNO');
-- alebo
SELECT *
FROM osoba
WHERE NOT REGEXP_LIKE(priezvisko, 'k-oK-O');
Skúste napísať regulárny výraz, ktorý vyhodnotí, či email bol napísaný v správnom formáte.
NOT REGEXPREGEXPREGEXP_INSTR()REGEXP_LIKE()REGEXP_REPLACE()REGEXP_SUBSTR()RLIKE
# Vrati 1 ak email je v spravnom formate, inak vrati 0.
SELECT 'lukas.mino@upjs.sk' REGEXP '^[\w-\.]+@([\w-]+\.)+[\w-]{2,4}$';
-- Vrati 0
SELECT 'lukas,mino@upjs.sk' REGEXP '^[\w-\.]+@([\w-]+\.)+[\w-]{2,4}$';
Dostali ste za úlohu spravovať databázu osôb. Importnite si ju a zoznámte sa s jej štruktúrou.
Zobrazte priezviská osob a ich dĺžku v znakoch a bajtoch.
LENGTHCHAR_LENGTHCHARACTER_LENGTH
-- Priezviska a ich dlzky v znakoch a bajtoch
SELECT
priezvisko,
CHAR_LENGTH(priezvisko) 'Dĺžka v znakoch',
CHARACTER_LENGTH(priezvisko) 'Dĺžka v znakoch (iný spôsob)',
LENGTH(priezvisko) 'Dĺžka v bajtoch'
FROM osoba;
Zobrazte len jedinečné (neopakujúce sa) posledné znaky z priezvisk osôb zotriedené lexikograficky, tieto znaky preveďte na malé písmená a zobrazte ich ASCII hodnotu, potom na veľké písmeno a zobrazte ich ASCII hodnotu. Skúste zistiť či je rozdiel v ASCII hodnote medzi veľkými a malými písmenami rovnaký. Skúste previesť ASCII hodnotu naspäť na konkrétny znak.
ASCIILEFTMIDRIGHTSUBSTRSUBSTRINGLCASELOWERUCASEUPPER
-- ASCII hodnoty poslednych pismen priezvisk - porovnanie velkych a malych pismen
SELECT
DISTINCT
RIGHT(priezvisko,1) 'Posledný znak', -- mozeme pouzit aj SUBSTR(priezvisko, CHAR_LENGTH(priezvisko),1), resp. SUBSTRING(priezvisko, CHARACTER_LENGTH(priezvisko),1)
ASCII(RIGHT(priezvisko,1)) 'ASCII hodnota posledného znaku',
CHAR(ASCII(RIGHT(priezvisko,1)) USING ascii) 'Znak z ASCII hodnoty posledného znaku',
LCASE(RIGHT(priezvisko,1)) 'Malé písmeno', -- mozeme pouzit aj LOWER(RIGHT(priezvisko,1))
ASCII(LCASE(RIGHT(priezvisko,1))) 'ASCII hodnota malého písmena',
CHAR(ASCII(LCASE(RIGHT(priezvisko,1))) USING ascii) 'Znak z ASCII hodnoty malého písmena',
UCASE(RIGHT(priezvisko,1)) 'Veľké písmeno', -- mozeme pouzit aj UPPER(RIGHT(priezvisko,1))
ASCII(UCASE(RIGHT(priezvisko,1))) 'ASCII hodnota malého písmena',
CHAR(ASCII(UCASE(RIGHT(priezvisko,1))) USING ascii) 'Znak z ASCII hodnoty veľkého písmena',
ASCII(UCASE(RIGHT(priezvisko,1))) - ASCII(LCASE(RIGHT(priezvisko,1))) AS 'Rozdiel v ASCII hodnotách medzi veľkým a malým písmenom'
FROM osoba
ORDER BY 1;
CHAR vrátila otáznik).
Vytvorte premennú snickers, do ktorej uložte text Keď si hladný, nie si to ty. Potom premennú snickers vypíšte
REPLACEUPPERRIGHTSUBSTRINGMIDSUBSTRING_INDEXRPAD
-- Premenna snickers
SET @snickers = 'Keď si hladný, nie si to ty';
-- Vypise vsetky uzivatelske premenne
SELECT * FROM performance_schema.user_variables_by_thread;
-- Vypise vsetky aplikacne premenne
SHOW VARIABLES;
-- a. príkazom SELECT.
SELECT @snickers;
-- b. s medzerami nahradenými reťazcom ' vlastne '.
SELECT REPLACE(@snickers, ' ', ' vlastne ');
-- c. s veľkými písmenami.
SELECT UPPER(@snickers);
-- d. s iba poslednými 8 znakmi.
SELECT RIGHT(@snickers, 8);
-- e. s vynechanými prvými 8 znakmi.
SELECT SUBSTRING(@snickers, 8);
SELECT MID(@snickers, 8); # MID je synonymum ku SUBSTRING
-- f. s vynechanými prvými 8 znakmi. Vypíšte len 14 znakov.
SELECT SUBSTRING(@snickers, 8, 14);
SELECT MID(@snickers, 8, 14);
-- g. s iba prvými/poslednými dvoma slovami.
SELECT SUBSTRING_INDEX(@snickers, ' ', 2);
SELECT SUBSTRING_INDEX(@snickers, ' ', - 2);
-- h. s iba prvým slovom rozšíreným na 10 znakov, kde zvyšné znaky doplňte bodkami.
SELECT RPAD(SUBSTRING_INDEX(@snickers, ' ', 1), 10, '.'); # RPAD-right-padded, ked dáme slovo dlhšie ako počet znakov, tak ho odsekne
Zobrazte mená a priezviská osôb v jednom stĺpci.
CONCATCONCAT_WS
-- Meno a priezvisko v jednom stlpci
SELECT CONCAT(meno, ' ', priezvisko) AS 'Meno a priezvisko'
FROM osoba
ORDER BY priezvisko, meno;
-- alebo
SELECT CONCAT_WS(' ', meno, priezvisko) AS 'Meno a priezvisko'
FROM osoba
ORDER BY priezvisko, meno;
Zobrazte prvý znak mena, bodku, medzeru a priezvisko osôb v jednom stĺpci. Napr. A. Prvý.
CONCATCONCAT_WSSUBSTRINGLEFT
-- Prve pismeno z mena a priezvisko
SELECT CONCAT(SUBSTRING(meno, CHAR_LENGTH(meno),1), '. ', priezvisko)
FROM osoba;
-- alebo
SELECT CONCAT_WS('. ', LEFT(meno, 1), priezvisko) # retazcova funkcia LEFT vrati najlavejsich X znakov
FROM osoba;
Zobrazte meno, priezvisko, vysku po zaokruhlení a celočíselnú váhu (dolná celá časť) osôb.
ROUNDFLOORCEIL
-- Prve pismeno z mena a priezvisko
SELECT
meno,
priezvisko,
ROUND(vyska, 0) AS 'vyska' -- alebo ROUND(vyska)
FLOOR(vaha) AS 'vaha' # numericka funkcia - FLOOR(X) vrati najvaciu celociselnu hodnotu nie vacsiu ako X
FROM osoba;
Zobrazte dnešný dátum.
CURRENT_DATECURDATE
-- Dnesny datum
SELECT CURRENT_DATE();
-- alebo
SELECT CURDATE();
Zobrazte aktuálny čas.
CURRENT_TIMECURTIME
-- Aktualny cas
SELECT CURRENT_TIME();
-- alebo
SELECT CURTIME();
Zobrazte aktuálnu časovú pečiatku (dátum a čas v jednom stĺpci).
CURRENT_TIMESTAMP
-- Aktualna casova peciatka
SELECT CURRENT_TIMESTAMP();
Skúste na príklade ukázať rozdiel medzi funkciami NOW a SYSDATE.
NOWSYSDATE
SELECT NOW(); #vrati cas spustenia príkazu
SELECT SYSDATE(); #vrati cas kedy bola zavolana funkcia
-- Pozn.: CURRENT_TIMESTAMP a NOW vratia cas zaciatku vykonavania dopytu. Naproti tomu SYSDATE vrati aktualny cas. Vid. priklady:
-- Cas oboch volani NOW je rovnaky.
SELECT NOW(), SLEEP(2), NOW();
-- Cas druheho volania SYSDATE je posunuty o dve sekundy.
SELECT SYSDATE(), SLEEP(2), SYSDATE();
Zobrazte zajtrajší dátum.
ADDATECURRENT_DATECURDATE
-- Zajtrajsi datum
SELECT ADDDATE(CURRENT_DATE(), 1);
Zobrazte včerajší dátum.
SUBDATECURRENT_DATECURDATE
-- Vcerajsi datum
SELECT SUBDATE(CURRENT_DATE(), 1);
-- alebo
SELECT ADDDATE(CURRENT_DATE(), -1);
Zobrazte aký bude dátum presne o mesiac.
ADDDATETIMESTAMPADDCURRENT_DATECURDATE
-- Datum o mesiac
SELECT ADDDATE(CURRENT_DATE(), INTERVAL 1 MONTH);
-- alebo
SELECT TIMESTAMPADD(MONTH, 1, CURRENT_DATE());
Zobrazte koľkatý je dnes deň v roku.
DAYOFYEARCURRENT_DATECURDATE
-- Den v roku
SELECT DAYOFYEAR(CURRENT_DATE());
Zobrazte koľkatý je dnes týždeň v roku.
WEEKOFYEARCURRENT_DATECURDATE
-- Tyzden v roku
SELECT WEEKOFYEAR(CURRENT_DATE());
Zobrazte dnešný deň v týždni (po slovensky).
DAYNAMECURRENT_DATECURDATE
-- Den v tyzdni
-- po slovensky
SET lc_time_names = 'sk_SK';
SELECT DAYNAME(CURRENT_DATE()) slov;
-- po anglicky
SET lc_time_names = 'en_US';
SELECT DAYNAME(CURRENT_DATE()) ang;
-- alebo
SELECT
CASE WEEKDAY(CURRENT_DATE()) -- 0 = Monday
WHEN 0 THEN 'Pondelok'
WHEN 1 THEN 'Utorok'
WHEN 2 THEN 'Streda'
WHEN 3 THEN 'Stvrtok'
WHEN 4 THEN 'Piatok'
WHEN 5 THEN 'Sobota'
WHEN 6 THEN 'Nedela'
ELSE 'Nezname'
END 'Deň v týždni'
FROM osoba;
-- alebo
SELECT
CASE DAYOFWEEK(CURRENT_DATE()) -- 1 = Sunday
WHEN 1 THEN 'Nedela'
WHEN 2 THEN 'Pondelok'
WHEN 3 THEN 'Utorok'
WHEN 4 THEN 'Streda'
WHEN 5 THEN 'Stvrtok'
WHEN 6 THEN 'Piatok'
WHEN 7 THEN 'Sobota'
ELSE 'Nezname'
END 'Deň v týždni'
FROM osoba;
Zobrazte meno aktuálneho mesiaca (po slovensky).
MONTHNAMECURRENT_DATECURDATE
-- Nazov mesiaca
-- po slovensky
SET lc_time_names = 'sk_SK';
SELECT MONTHNAME(CURRENT_DATE()) slov;
-- po anglicky
SET lc_time_names = 'en_US';
SELECT MONTHNAME(CURRENT_DATE()) ang;
Zobrazte osoby narodené medzi rokmi 1980 a 1985.
YEARBETWEENAND
-- Osoby narodene medzi rokmi 1980 a 1985.
SELECT * FROM osoba WHERE YEAR(dat_nar) BETWEEN 1980 AND 1985;
-- alebo
SELECT * FROM osoba WHERE 1980 <= YEAR(dat_nar) AND YEAR(dat_nar) <= 1985;
Zobrazte osoby narodené medzi 10. a 20. dňom v septembri.
MONTHDAY
-- Osoby narodene medzi 10. a 20. dnom septembra.
SELECT * FROM osoba WHERE MONTH(dat_nar) = 9 AND DAY(dat_nar) BETWEEN 10 AND 20;
Zobrazte osoby narodené v pondelok.
WEEKDAYDAYOFWEEK
-- Osoby narodene v pondelok
SELECT * FROM osoba
WHERE WEEKDAY(dat_nar) = 1; -- 1 = Monday
-- alebo
SELECT * FROM osoba
WHERE DAYOFWEEK(dat_nar) = 2; -- 2 = Monday
Zobrazte meno, priezvisko a dátum narodenia osôb v slovenskom formáte (den.mesiac.rok).
DATE_FORMAT
-- Datum narodenia vo formate dd.MM.yyyy
SELECT
meno,
priezvisko,
DATE_FORMAT(dat_nar, '%d.%m.%Y')
FROM osoba;
Zobrazte meno, priezvisko, dátum narodenia a aktuálny vek u žijúcich osôb.
TIMESTAMPDIFFCURRENT_DATE
-- Meno, priezvisko, datum narodenia a vek
SELECT
meno,
priezvisko,
dat_nar,
TIMESTAMPDIFF(YEAR, dat_nar, CURRENT_DATE())
FROM
osoba;
Zobrazte meno, priezvisko, dátum narodenia, dátum smrti a vek (u žijúcich aktuálny vek, u zosnulých vek v čase smrti).
TIMESTAMPDIFFCURRENT_DATEIFNULLCOALESCE
-- Meno, priezvisko, datum narodenia, datum smrti a vek (u zosnulych vek v case umrtia)
SELECT
meno,
priezvisko,
dat_nar,
dat_smrti,
TIMESTAMPDIFF(YEAR, dat_nar, IFNULL(dat_smrti, CURRENT_DATE()))
FROM
osoba;
-- alebo
SELECT
meno,
priezvisko,
dat_nar,
dat_smrti,
TIMESTAMPDIFF(YEAR, dat_nar, COALESCE(dat_smrti, CURRENT_DATE()))
FROM
osoba;
Zobrazte číslo meno, priezvisko a pohlavie (muž, žena, neznáme).
CASE
-- Meno, priezvisko, pohlavie (muz/zena/nezname)
SELECT
meno,
priezvisko,s
CASE
WHEN pohlavie = 'm' THEN 'Muz'
WHEN pohlavie = 'z' THEN 'Zena'
ELSE 'Nezname'
END pohlavie
FROM osoba;
-- alebo
SELECT
meno,
priezvisko,s
CASE pohlavie
WHEN 'm' THEN 'Muz'
WHEN 'z' THEN 'Zena'
ELSE 'Nezname'
END pohlavie
FROM osoba;
Zobrazte číslo PI na 15 desatinných miest.
PICASTCONVERT
-- cislo PI
SELECT PI(); # defaultne zobrazi 7 desatinnych miest
-- cislo PI na 15 desatinných miest.
SELECT CAST(PI() AS DECIMAL(16, 15)); # CAST funkcia pretypuje vybrany vyraz na zadany typ
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.
SELECTWHERE
-- Informacie o pacientoch
SELECT
*
FROM pacienti
WHERE mesPrijem > 10000;
Zobrazte pacientov, ktorí majú uvedený príjem.
SELECTWHEREIS 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.
COUNTDISTINCT
-- 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.
COUNTBETWEEN
-- 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.
COUNTBETWEEN
-- 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) JOINLEFT OUTER JOINRIGHT 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.
INTERSECTINEXISTSINNER JOINNATURAL 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.
EXCEPTNOT INNOT EXISTSLEFT 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.
JOINUNION
-- 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.
JOININEXISTS
-- 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.
JOININANYEXISTS
-- 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.
JOININCOUNTSUM
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.
JOININ
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.
SUMWITH 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 TABLEADD 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 TABLEMODIFY COLUMNDEFAULT
-- 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 TABLECHANGE 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 TABLEDROP 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;