SQL dotazy - SELECT
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.
Vytvoříme driver pro databáze Postgres:
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.
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
Připojíme se k databázi pomocí Aliasu Foodmart a přepneme se do okna SQL
Dotazy
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.
Vypište celou tabulku zaměstnanců (skladu).
Vypište pouze sloupce first_name, last_name, birth_date z tabulky zaměstnanců.
Vypište jména pozic zaměstnanců (sloupec position_title) tak, aby se žádnéjméno neopakovalo.
Vypište přijmení zaměstnanců podle abecedy od a do z
Vypište přijmení zaměstnanců podle abecedy od z do a
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
Vypište pouze zaměstnance s platem větším než 40.000
Vyzkoušejte postupně další podmínky (=, >, <, >=, ⇐)
Vypište pouze zaměstnance s platem mezi 10.000 a 20.000
Vyzkoušejte i složené podmínky s operátory AND, OR, NOT
Vypište pouze zaměstnance jejichž přijmení začíná na S
Vypište pouze zaměstnance jejichž jméno končí na S a srovnejte je podle abecedy
Spojte tabulku zaměstnanců (employee) a tabulku skladů (store) a vypište všechny údaje.
Vypište všechny zaměstnance, kteří pracují v obchodu Store 1.
Spočítejte kolik takových zaměstnanců v databázi existuje.
Dotazy s klauzulemi GROUP BY, HAVING
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.
Zjistěte počty zaměstnanců podle jejich profese (position_title).
Zjistěte průměrné platy zaměstnanců podle profese, seřaďte podle průměrného platu vzestupně.
Zjistěte průměrné platy zaměstnanců - mužů podle profese, seřaďte podle průměrného platu sestupně.
Zjistěte pro každý obchod (store) počet zaměstnanců, seřaďte podle jména obchodu.
Zjistěte jména obchodů s více jak 50 zaměstnanci.
Zjistěte jména obchodů, kde pracuje více než 40 žen.
Vnořené dotazy
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)
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)