SELECT * FROM sys.databases;

USE OsobaVztah;
GO

SELECT * FROM sys.tables;

USE OsobaVztah;
GO

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 'Dátum narodenia' 
FROM osoba
ORDER BY priezvisko ASC, 1 DESC, 'Dátum narodenia';

SELECT TOP 3 * 
FROM osoba
ORDER BY vyska DESC;

SELECT TOP(3) * 
FROM osoba
ORDER BY vyska DESC;

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

-- alebo

SELECT * 
FROM osoba
ORDER BY vyska DESC
OFFSET 4 ROWS FETCH NEXT 2 ROWS ONLY;

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 LEN(priezvisko) = 5;

USE OsobaVztah;
GO

SELECT meno+' '+priezvisko, CASE pohlavie 
								WHEN 'M' THEN 'Muž'
								WHEN 'Z' THEN 'Žena'
								ELSE 'Musite zadat m alebo z'
							END 
	FROM Osoba;
 
---- alebo

SELECT meno+' '+priezvisko, CASE 
								WHEN pohlavie = 'M' THEN 'Muz' 
								WHEN pohlavie = 'Z' THEN 'Zena'
								ELSE 'Musite zadat m alebo z'
							END 
FROM Osoba;


SELECT * FROM Osoba 
WHERE dat_nar IS NULL;

SELECT * FROM Osoba 
WHERE dat_nar 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;

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


PRINT GETDATE();

SELECT GETDATE();


SELECT DATENAME( dw, GETDATE() );

SET LANGUAGE Slovak
SELECT DATENAME( dw, GETDATE() )
SET LANGUAGE English;


SELECT DATEPART( dw, GETDATE() - 1);

---- alebo

SET DATEFIRST 1;
SELECT DATEPART( dw, GETDATE())
SET DATEFIRST 7; 


SET LANGUAGE English
SELECT DATENAME(dw, dat_nar) FROM Osoba;

SET LANGUAGE Slovak
SELECT DATENAME(dw, dat_nar) FROM Osoba
SET LANGUAGE English;


SELECT DATEPART(dw, dat_nar - 1), DATENAME(dw, dat_nar) 
FROM Osoba;

---- alebo

SET DATEFIRST 1;
SELECT DATEPART(dw, dat_nar),
	   DATENAME(dw, dat_nar) FROM Osoba;
SET DATEFIRST 7;


SELECT DATEPART(dw, dat_nar) - 1, DATENAME(dw, dat_nar) 
FROM Osoba



SELECT *, DATENAME(dw, dat_nar) 
FROM Osoba 
WHERE DATEPART(dw, dat_nar) = 2 + 1;

---- alebo

SET DATEFIRST 1;
SELECT * FROM Osoba WHERE DATEPART(dw, dat_nar) = 2
SET DATEFIRST 7;


SELECT meno, priezvisko, YEAR(  GETDATE()) - Year(dat_nar) 
FROM Osoba;

---- alebo

SELECT meno, priezvisko, DATEDIFF(year, dat_nar,  GETDATE() ) 
FROM Osoba;

SELECT DATEPART(dayofyear, GETDATE());


SELECT GETDATE() + 1;

SELECT DATEADD(dw, 1, GETDATE() );


SELECT DATENAME(dw, GETDATE() + 1);

----  alebo 

SELECT DATENAME(dw, DATEADD(dw, 1, GETDATE() ) );