====== Ukázkové dotazy na databázi FoodMart ======
===== Jednoduché dotazy =====
** Aktuální datum a čas **
SELECT now();
** Vyberte různá příjmení všech zaměstnanců **
SELECT DISTINCT last_name FROM employee
** Počty příjmení zaměstanců, řazeno sestupně dle počtu **
SELECT last_name, COUNT(last_name) FROM employee GROUP BY last_name ORDER BY count(last_name) DESC
/*-- Vyberte počet různých příjmení
SELECT count(*) FROM (SELECT DISTINCT last_name FROM employee) AS distinct_names
-- Vyberte jen ta příjmení, jejichž nositelů je více než 5, seřazeno dle počtu vzestupně
SELECT last_name, count(last_name) FROM employee GROUP BY last_name HAVING count(last_name) > 5 ORDER BY count(last_name) ASC
*/
** Procentuální zastoupení jmen zaměstnanců, záznamy seřazeny podle procentuálního zastoupní sestupně **
SELECT last_name,
(CAST(COUNT(last_name) as FLOAT)/(SELECT count(DISTINCT last_name) FROM employee)*100) AS percent
FROM employee GROUP BY last_name ORDER BY percent DESC
===== Dotazy spojující více tabulek =====
** Roční prodeje a zisky v jednotlivých obchodech (store_id, store_name, total sales, total cost, [total profit]) **
SELECT store.store_id, store.store_name,
SUM(ysales.store_sales * ysales.unit_sales) AS sales, SUM(ysales.store_cost* ysales.unit_sales ) AS cost,
SUM((ysales.store_sales - ysales.store_cost)*ysales.unit_sales) AS profit
FROM store JOIN sales_fact_1997 AS ysales USING (store_id)
GROUP BY store.store_id, store.store_name
ORDER BY 3 DESC;
/* ** For Store 13 find total yearly sales and costs by country and city. (store_id, store_name, total sales, total cost, [total profit]) **
SELECT
ysales.store_id AS ID,
cust.city AS CITY,
cust.country AS COUNTRY,
SUM(ysales.store_sales * ysales.unit_sale) AS SALES,
SUM(ysales.store_cost * ysales.unit_sale) AS COSTS
FROM sales_fact_1997 AS ysales
JOIN customer AS cust on cust.customer_id=ysales.customer_id
WHERE ysales.store_id=(select store_id from store where store_name='Store 13')
GROUP BY ysales.store_id,cust.city,cust.country
ORDER BY 4 DESC;
*/
/* -- Vyberte 6-15. zaměstnance s nejvyšším platem, jména zaměstnanců řaďte vzestupně
SELECT full_name, salary FROM employee ORDER BY salary DESC, full_name ASC LIMIT 10 OFFSET 5*/
** Všichni zákazníci a počet jejich nákupních týdnu (týdny, ve které nakupovali) větší než půlku týdnů v roce (365/7/2) **
SELECT cust.customer_id, cust.fname, cust.lname, count(distinct stime.week_of_year) as purchases_weeks,
COUNT(DISTINCT stime.time_id) as customer_purchases
FROM customer AS cust JOIN sales_fact_1998 AS ysales USING (customer_id)
JOIN time_by_day AS stime USING (time_id)
GROUP BY cust.customer_id, cust.fname, cust.lname
HAVING COUNT(DISTINCT stime.week_of_year) > (365/7/2)
ORDER BY purchases_weeks DESC;
/*
-- Vyberte počty zaměstnanců na jednotlivých pobočkách (store.name), výstup seřaďte dle počtu sestupně
SELECT store_name, count(e.employee_id) as count FROM employee e JOIN store s on(e.store_id = s.store_id) GROUP BY store_name ORDER BY count DESC
-- Vyberte počty zaměstnanců na jednotlivých pobočkách dle pohlaví, seřaďte dle celkového počtu zaměstnanců sestupně
SELECT s.store_name,
(SELECT count(*) from employee em WHERE em.store_id = s.store_id AND gender = 'M') AS male_count,
(SELECT count(*) from employee em WHERE em.store_id = s.store_id AND gender = 'F') AS female_count,
(SELECT count(*) from employee em WHERE em.store_id = s.store_id) AS total_count
FROM employee e JOIN store s on(e.store_id = s.store_id) GROUP BY s.store_name, s.store_id ORDER BY (total_count) DESC
-- Spočítejte platy vyplacené jednotlivým zaměstnancům celkem (převedeno na dolary)
SELECT employee.full_name, sum(salary_paid * conversion_ratio)
FROM salary JOIN employee ON(salary.employee_id = employee.employee_id) JOIN currency USING(currency_id)
GROUP BY employee.full_name
*/
** Platy u všech zaměstnanců, které byly vyplaceny v mexických peso. Pokud nebyl nějakému zaměstatnanci vyplacen plat v pesech, má ve výpisu 0. **
SELECT e.full_name, COALESCE(SUM(salary_paid), 0)
FROM employee e LEFT JOIN
(SELECT * FROM salary JOIN currency USING(currency_id) WHERE currency = 'Mexican Peso') AS mexsal
ON (mexsal.employee_id = e.employee_id) GROUP BY e.employee_id,e.full_name
/*SELECT distinct product_name FROM
inventory_fact_1997 inv97 JOIN product USING(product_id) WHERE inv97.product_id NOT IN (SELECT product_id from inventory_fact_1998)*/
===== Union, Except =====
** Počet dodaných jednotek zboží v letech 1998 a 1997 **
SELECT SUM(units_ordered) FROM (
SELECT * FROM inventory_fact_1997
UNION
SELECT * FROM inventory_fact_1998
) AS total_inv
** Jména produktů, které byly prodány v roce 1997, ale už ne v roce 1998 **
SELECT product_name FROM (
SELECT product_id FROM inventory_fact_1997
EXCEPT
SELECT product_id FROM inventory_fact_1998
) as total_inv
JOIN product USING(product_id)
===== Vnořené dotazy =====
** Zaměstnanci s nadprůměrným platem **
SELECT * FROM employee e WHERE e.salary > (SELECT AVG(salary) FROM employee)
** Vsechny obchody s udajem, kolik zamestnancu maji v oddelenich zacinajicich na "HQ" **
SELECT s.store_id, s.store_name, COALESCE(counts.cnt, 0)
FROM store s LEFT JOIN
(SELECT e.store_id, count(*) AS cnt
FROM employee e
WHERE e.department_id IN (SELECT department_id FROM department WHERE department_description LIKE 'HQ%')
GROUP BY e.store_id) counts
ON s.store_id=counts.store_id
** Nejstarší zaměstnanci v každém obchodě **
SELECT s.store_id, s.store_name, e.full_name, e.birth_date
FROM store s JOIN employee e USING (store_id)
JOIN
(SELECT store_id, MIN(birth_date) AS datum
FROM employee
GROUP BY store_id) nejstarsi
ON (e.store_id=nejstarsi.store_id) AND (e.birth_date=nejstarsi.datum)