SHOW DATABASES;

USE OsobaVztah;

SHOW TABLES;

SHOW TABLES FROM OsobaDB;

USE OsobaVztah;

SELECT * FROM osoba;

SELECT meno, priezvisko, pohlavie FROM osoba;

SELECT meno AS 'Name', priezvisko "Surname", pohlavie Gender FROM osoba;

SELECT meno FROM osoba;

SELECT DISTINCT meno FROM osoba;

SELECT meno, priezvisko, dat_nar 'Datum narodenia' 
FROM osoba
ORDER BY priezvisko ASC, 1 DESC, 'Datum narodenia';

SELECT * 
FROM osoba
ORDER BY vyska DESC
LIMIT 3;

SELECT * 
FROM osoba
ORDER BY vyska DESC
LIMIT 4, 2;

-- alebo

SELECT * 
FROM osoba
ORDER BY vyska DESC
LIMIT 2 OFFSET 4;

SELECT * 
FROM osoba
WHERE id < 4;

SELECT * 
FROM osoba 
WHERE pohlavie = 'z' AND priezvisko <> rodne_priezvisko AND dat_smrti IS NULL AND dat_nar < '1990-01-01' AND vyska > 160;

-- Osoby s vyskou 167, 169, 182
SELECT * 
FROM osoba 
WHERE vyska = 167 OR vyska = 169 OR vyska = 182;

-- alebo

SELECT * FROM osoba WHERE vyska IN (167, 169, 182);

-- 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');

-- 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;

-- 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;

-- Priezviska zacinaju slovom Novak
SELECT * 
FROM osoba 
WHERE priezvisko LIKE 'Novak%';

-- Priezviska obsahuju ova
SELECT * 
FROM osoba 
WHERE priezvisko LIKE '%ova%';

-- Priezviska koncia na ova
SELECT * 
FROM osoba 
WHERE priezvisko LIKE '%ova';

-- Priezviska koncia na ova a nejaky znak
SELECT * 
FROM osoba 
WHERE priezvisko LIKE '%ova_';

-- Priezviska obsahuju znaky o alebo a
SELECT * 
FROM osoba 
WHERE priezvisko LIKE '%o%' OR priezvisko LIKE '%a%';

-- 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;

USE OsobaVztah;

SELECT 
	concat(meno, ' ',priezvisko) 'Meno a priezvisko', 
    CASE pohlavie 
		WHEN 'M' THEN 'Muz'
		WHEN 'Z' THEN 'Zena'
		ELSE 'Musite zadat m alebo z' 
	END pohlavie
FROM osoba;

SELECT
	concat(meno, ' ',priezvisko) 'Meno a priezvisko', 
    CASE 
		WHEN pohlavie = 'M' THEN 'Muz' 
		WHEN pohlavie = 'Z' THEN 'Zena'
		ELSE 'Musite zadat m alebo z'
	END pohlavie
FROM osoba;


SELECT * FROM osoba WHERE dat_smrti IS NULL;
        
SELECT * FROM osoba WHERE dat_smrti IS NOT NULL;

SELECT priezvisko, meno, dat_nar FROM osoba WHERE YEAR(dat_nar) <=1980;

SELECT priezvisko, meno, dat_nar FROM osoba WHERE YEAR(dat_nar) BETWEEN 1980 AND 1985;

SELECT priezvisko, meno, dat_nar FROM osoba WHERE 1980 <= YEAR(dat_nar) AND YEAR(dat_nar) <= 1985;

SELECT * FROM osoba WHERE MONTH(dat_nar) = 9 AND DAY(dat_nar) BETWEEN 10 AND 30;

SELECT NOW();

SELECT CURRENT_DATE;

SELECT  WEEK( CURRENT_DATE );

SELECT  WEEKDAY( CURRENT_DATE );

SELECT DAYOFWEEK(NOW());


SELECT 
*, 
CASE WEEKDAY(dat_nar) 
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 'Den narodenia'
FROM osoba;

SELECT * FROM osoba WHERE WEEKDAY(dat_nar)  = 1;

SELECT * FROM osoba WHERE DAYOFWEEK (dat_nar) = 2;

SELECT meno, priezvisko, TIMESTAMPDIFF(YEAR,dat_nar,NOW()) vek FROM osoba;

SELECT ADDDATE(current_date(), 1);