4 GL

jazyky
	-procedurální
	-deklarativní (neprocedurální)

	-soběstačné
	-na bázi hostitelského jazyka
SQL
-DDL
-DML
-DCL
spec. utility

DML
	-SELECT (dotazovací jazyk)
	-INSERT, UPDATE, DELETE (manipulace)

SQL SELECT je
	-neprocedurální
	-soběstačný

---všechny příklady jsou pro PostgreSQL, až na ty, kde je napsáno MySQL---


\d Objednavky


SELECT CisloObjednavky, DatumObjednavky, Mezisoucet FROM Objednavky WHERE DatumObjednavky>='1998-05-01';

SELECT ZemePrijemce, SUM(Mezisoucet), AVG(Mezisoucet) FROM Objednavky WHERE DatumObjednavky>='1998-01-01' GROUP BY ZemePrijemce;

SELECT CisloObjednavky,DatumObjednavky, Mezisoucet FROM Objednavky WHERE Mezisoucet BETWEEN 1000 AND 2000;

SELECT CisloObjednavky,DatumObjednavky,ZemePrijemce, Mezisoucet FROM Objednavky WHERE ZemePrijemce IN ('Polsko','Isko','Portugalsko');


SELECT CisloObjednavky,JmenoPrijemce, Mezisoucet  FROM Objednavky WHERE JmenoPrijemce LIKE '%Gourmet%';

---MySQL
SELECT CisloObjednavky, DatumObjednavky, Mezisoucet FROM Objednavky WHERE YEAR(DatumObjednavky)=1998 limit 100;  


\d Zakaznici

SELECT Firma FROM Zakaznici WHERE Fax IS NULL;

SELECT Firma FROM Zakaznici WHERE Fax IS NOT NULL;

---MySQL
SELECT CisloObjednavky, DatumObjednavky, Mezisoucet FROM Objednavky WHERE YEAR(DatumObjednavky)=1998 AND ZemePrijemce="Norsko";


--AGREGACE-----------------
SELECT KodZakaznika, SUM(Mezisoucet) FROM Objednavky WHERE DatumObjednavky>='1998-05-01'GROUP BY KodZakaznika;

SELECT Zeme, COUNT(*), COUNT(Fax) FROM Zakaznici GROUP BY Zeme;

SELECT Zeme,COUNT(DISTINCT Mesto) FROM Zakaznici GROUP BY Zeme;

SELECT COUNT(DISTINCT Mesto),COUNT(Mesto) FROM Zakaznici;

SELECT Zeme,COUNT(DISTINCT Region),COUNT(Region) FROM Zakaznici GROUP BY Zeme;

SELECT Mesto FROM Zakaznici WHERE Zeme='USA';

SELECT Zeme,Mesto,COUNT(*) FROM Zakaznici GROUP BY Zeme,Mesto;

SELECT Zeme,Mesto,COUNT(*) FROM Zakaznici GROUP BY Zeme,Mesto ORDER BY COUNT(*) DESC;

SELECT Zeme,Mesto,COUNT(*) FROM Zakaznici GROUP BY Zeme,Mesto HAVING COUNT(*)>1 ORDER BY COUNT(*) DESC;

SELECT Zeme,Mesto,COUNT(*) FROM Zakaznici GROUP BY Zeme,Mesto HAVING COUNT(*)>1 ORDER BY COUNT(*) DESC;

---MySQL
SELECT ZemePrijemce,YEAR(DatumObjednavky), SUM(Mezisoucet) AS trzba FROM Objednavky WHERE CisloZamestnace=3 GROUP BY ZemePrijemce,YEAR(DatumObjednavky) HAVING trzba>1000000 ORDER BY 3 DESC, 1,2;

SELECT DatumOdeslani, SUM(Mezisoucet) FROM Objednavky WHERE DatumObjednavky>='1998-05-01' GROUP BY DatumOdeslani;