====== Ukázkové dotazy na databázi FoodMart ======

===== Jednoduché dotazy =====
** Aktuální datum a čas **
<file>
SELECT now();
</file>

** Vyberte různá příjmení všech zaměstnanců **
<file>
SELECT DISTINCT last_name FROM employee
</file>

** Počty příjmení zaměstanců, řazeno sestupně dle počtu **
<file>
SELECT last_name, COUNT(last_name) FROM employee GROUP BY last_name ORDER BY count(last_name) DESC
</file>



/*-- 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ě **
<file>
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
</file>



===== 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]) **
<file>
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;
</file>

/* ** For Store 13 find total yearly sales and costs by country and city. (store_id, store_name, total sales, total cost, [total profit]) **
<file>
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;
</file>*/

/* -- 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) **
<file>
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;
</file>

/*
-- 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. **
<file>
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
</file>

/*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 **
<file>
SELECT SUM(units_ordered) FROM (
  SELECT * FROM inventory_fact_1997
  UNION
  SELECT * FROM inventory_fact_1998
) AS total_inv
</file>


** Jména produktů, které byly prodány v roce 1997, ale už ne v roce 1998 **
<file>
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)
</file>


===== Vnořené dotazy =====

** Zaměstnanci s nadprůměrným platem **
<file>
SELECT * FROM employee e WHERE e.salary > (SELECT AVG(salary) FROM employee)
</file>

** Vsechny obchody s udajem, kolik zamestnancu maji v oddelenich zacinajicich na "HQ" **
<file>
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
</file>

** Nejstarší zaměstnanci v každém obchodě **
<file>
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) 
</file>