V této hodině budeme pracovat s demonstrační databází Foodmart. Ta obsahuje data z datového skladu popisujícího prodeje jídla a doprovodné informace. Zde budeme pracovat s tabulkou zaměstnanci (table employee) a tabulkou sklady (table store). Vztah mezi těmito tabulkami je 1:N.

K databázi se připojíme pomocí programu Squirrel (doporučený download, používali jsme už v minulých hodinách.

1. Vytvoříme driver pro databáze Postgres:
   • Uložte si driver postgresql-9.0-801.jdbc3.jar někam na disk.
   • Ve Squirrelu v záložce Drivers klepněte myší na PostgreSQL a následně na tužku (Modify)
   • V okně Extra Class Path přidejte pomocí tlačítka Add uložený soubor z bodu a)
   • Stiskněte tlačítko Extra Class Path a dole vyberte Class Name.
   • 
2. Vytvořte nový alias na databázi Foodmart
   • V záložce Aliases stiskněte tlačítko plus (Create a new Alias)
   • Vyplňte Name: Foodmart, Driver: PostgreSQL
   • URL: jdbc:postgresql: lomítko lomítko screwdriver.felk.cvut.cz:5433 lomítko foodmart
   • Username: pisstudent Password: data
   • 
3. Připojíme se k databázi pomocí Aliasu Foodmart a přepneme se do okna SQL

Pro výběr dat slouží v SQL příkaz SELECT, jehož obecné schéma je:

SELECT sloupce

FROM tabulky

WHERE podmínky

GROUP BY spojit podle…

HAVING podmínky na skupiny

ORDER BY seřadit podle…

V této části se seznámíme s tučně označenými klauzulemi příkazu SELECT, tj. FROM, WHERE, ORDER BY. Příkazy budeme přímo zkoušet na databázi Foodmart. Uvedené příklady berte jako ukázky příkazů, vymyslete ke každému příkazu jiný podobný. Pozor ve Squirrelu je nastaven implicitně limit počtu řádků na 100, v některých případech to může být nežádoucí.

Vypracování je možné získat označením textu za slovem SELECT, napsáno bíle.

1. Vypište celou tabulku zaměstnanců (skladu).
   • SELECT * FROM employee
2. Vypište pouze sloupce first_name, last_name, birth_date z tabulky zaměstnanců.
   • SELECT first_name, last_name, birth_date FROM employee
3. Vypište jména pozic zaměstnanců (sloupec position_title) tak, aby se žádnéjméno neopakovalo.
   • SELECT distinct position_title FROM employee
4. Vypište přijmení zaměstnanců podle abecedy od a do z
   • SELECT last_name FROM employee order by last_name asc
5. Vypište přijmení zaměstnanců podle abecedy od z do a
   • SELECT last_name FROM employee order by last_name desc
6. Vypište jména a přijmení zaměstnanců a seřaďte je podle abecedy nejprve podle přijmení a pak podle jména
   • SELECT first_name, last_name FROM employee order by last_name, first_name
7. Vypište pouze zaměstnance s platem větším než 40.000
   • SELECT * from employee where salary>40000
8. Vyzkoušejte postupně další podmínky (=, >, <, >=, ⇐)
9. Vypište pouze zaměstnance s platem mezi 10.000 a 20.000
   • SELECT * from employee where salary between 10000 and 20000
10. Vyzkoušejte i složené podmínky s operátory AND, OR, NOT
11. Vypište pouze zaměstnance jejichž přijmení začíná na S
    • SELECT * from employee where last_name like 'S%'
12. Vypište pouze zaměstnance jejichž jméno končí na S a srovnejte je podle abecedy
    • SELECT * from employee where first_name like '%s' order by last_name
13. Spojte tabulku zaměstnanců (employee) a tabulku skladů (store) a vypište všechny údaje.
    • SELECT * from store as s, employee as e where s.store_id=e.store_id
14. Vypište všechny zaměstnance, kteří pracují v obchodu Store 1.
    • SELECT * from store as s, employee as e where s.store_id=e.store_id and store_name='Store 1'
15. Spočítejte kolik takových zaměstnanců v databázi existuje.
    • SELECT count(*) from store as s, employee as e where s.store_id=e.store_id and store_name='Store 1'

Pro výběr dat slouží v SQL příkaz SELECT:

SELECT sloupce

FROM tabulky

WHERE podmínky

GROUP BY spojit podle…

HAVING podmínky na skupiny

ORDER BY seřadit podle…

V této části se zaměříme na klauzule GROUP BY a HAVING. Doposud jsme pracovali s jednotlivými zaměstnanci. Často potřebujeme pracovat ne pouze s jednotlivými zaměstnanci, ale se skupinami zaměstnanců. Například v našem příkladě nás mohou zajímat skupiny zaměstnanců podle obchodu, ve kterém pracují (tabulka store). Vzhledem k tomu, že pracujeme se skupinou (množinou), nás budou zajímat tzv. agregované hodnoty - počet COUNT, průměr AVG, minimum MIN, maximum MAX, součet SUM pro zvolené skupiny. Například nás nezajímá věk jednoho konkrétního zaměstnance, ale zajímáme se o charakteristiku celé skupiny zaměstnanců, tedy o průměrný věk, nebo o nejnižší věk apod.

Vypracování je možné opět získat označením textu za slovem SELECT, napsáno bíle.

1. Zjistěte počty zaměstnanců podle jejich profese (position_title).
   • SELECT position_title, count(*) FROM employee GROUP BY position_title ORDER BY position_title
2. Zjistěte průměrné platy zaměstnanců podle profese, seřaďte podle průměrného platu vzestupně.
   • SELECT position_title, avg(salary) as avg FROM employee GROUP BY position_title ORDER BY avg
3. Zjistěte průměrné platy zaměstnanců - mužů podle profese, seřaďte podle průměrného platu sestupně.
   • SELECT position_title, avg(salary) as avg FROM employee WHERE gender='M' GROUP BY position_title ORDER BY avg desc
4. Zjistěte pro každý obchod (store) počet zaměstnanců, seřaďte podle jména obchodu.
   • SELECT store_name, count(*) FROM store as s, employee as e WHERE s.store_id=e.store_id GROUP BY store_name ORDER BY by store_name
5. Zjistěte jména obchodů s více jak 50 zaměstnanci.
   • SELECT store_name, count(*) as count FROM store as s, employee as e WHERE s.store_id=e.store_id GROUP BY store_name HAVING count(*)>50
6. Zjistěte jména obchodů, kde pracuje více než 40 žen.
   • SELECT store_name, count(*) as count FROM store as s, employee as e WHERE s.store_id=e.store_id AND gender='F' GROUP BY store_name HAVING count(*)>40

V některých případech potřebujeme nejprve zjistit z databáze nějakou hodnotu, kterou následně použijeme v dotazu. Například chceme najít všechny zaměstnance obchodů s vyšším než průměrným platem. Nejprve musíme z databáze zjistit velikost průměrného platu: SELECT avg(salary) from employee. Výsledek následně použijeme pro hledání všech zaměstnanců, kteří mají plat vyšší než průměrný: SELECT * from employee where salary>(SELECT avg(salary) from employee)

1. Zjistěte počet zaměstnanců, kteří měli nižší než je průměrný věk při nástupu do zaměstnání. Pomůcka: věk při nástupu do zaměstnání je (date_part('year',hire_date)-date_part('year',birth_date)).
   • SELECT count(*) FROM employee WHERE (date_part('year',hire_date)-date_part('year',birth_date))<(SELECT avg(date_part('year',hire_date)-date_part('year',birth_date)) FROM employee)