Data Aggregation - Exercises

01. Records’ Count
SELECT COUNT(*) FROM wizzard_deposits;
--------------
02. Longest Magic Wand
SELECT MAX(`magic_wand_size`) AS `longest magic wand`
FROM `wizzard_deposits`;
----------------
03. Longest Magic Wand per Deposit Groups
SELECT deposit_group,MAX(`magic_wand_size`) AS `longest magic wand`
FROM `wizzard_deposits` GROUP BY deposit_group
ORDER BY `longest magic wand`,deposit_group ASC;
-------------------
04. Smallest Deposit Group per Magic Wand Size
SELECT deposit_group
FROM `wizzard_deposits`
GROUP BY deposit_group
ORDER BY AVG(`magic_wand_size`)
LIMIT 1;
----------------
05. Deposits Sum
SELECT `deposit_group`,SUM(`deposit_amount`) AS `total_sum`
FROM `wizzard_deposits` GROUP BY deposit_group
ORDER BY total_sum ASC;
--------------------
06. Deposits Sum for Ollivander Family
SELECT `deposit_group`,SUM(`deposit_amount`) AS `total_sum`
FROM `wizzard_deposits` WHERE `magic_wand_creator` LIKE 'Ollivander family'
GROUP BY deposit_group
ORDER BY deposit_group ASC;
------------------
07. Deposits Filter
SELECT `deposit_group`,SUM(`deposit_amount`) AS `total_sum`
FROM `wizzard_deposits` WHERE `magic_wand_creator` LIKE 'Ollivander family'
GROUP BY deposit_group
HAVING total_sum < 150000
ORDER BY total_sum DESC;
--------------
08. Deposit Charge
SELECT deposit_group,magic_wand_creator,MIN(deposit_charge) AS
`min_deposit_charge` FROM `wizzard_deposits` GROUP BY deposit_group,
magic_wand_creator ORDER BY magic_wand_creator ASC,deposit_group ASC;
------------------
09. Age Groups
SELECT
CASE
    WHEN age BETWEEN 0 AND 10 THEN '[0-10]'
    WHEN age BETWEEN 11 AND 20 THEN '[11-20]'
    WHEN age BETWEEN 21 AND 30 THEN '[21-30]'
    WHEN age BETWEEN 31 AND 40 THEN '[31-40]'
    WHEN age BETWEEN 41 AND 50 THEN '[41-50]'
    WHEN age BETWEEN 51 AND 60 THEN '[51-60]'
    WHEN age > 60 THEN '[61+]'
END AS `age_group`,
COUNT(deposit_amount) AS `wizard_count`
FROM wizzard_deposits
GROUP BY age_group;
--------------
10. First Letter
SELECT DISTINCT SUBSTRING(first_name,1,1) AS `first_letter`
FROM `wizzard_deposits` WHERE `deposit_group` = 'Troll Chest'
GROUP BY `first_letter` ORDER BY `first_letter`;
----------------
11. Average Interest
SELECT `deposit_group`,`is_deposit_expired`,
AVG(`deposit_interest`) AS `average_interest`
FROM `wizzard_deposits`
WHERE DATE_FORMAT(`deposit_start_date`,'%Y-%m-%d') > '1985-01-01'
GROUP BY `deposit_group`,`is_deposit_expired`
ORDER BY `deposit_group` DESC, `is_deposit_expired` ASC;
----------------
12. Rich Wizard, Poor Wizard
CREATE VIEW rw_pw AS SELECT host.first_name AS `host_wizard`, host.deposit_amount AS `host_wizard_deposit`,
guest.first_name AS `guest_wizard`, guest.deposit_amount AS `guest_wizard_deposit`
FROM wizzard_deposits AS `host`, wizzard_deposits AS `guest` WHERE host.id+1=guest.id;
SELECT SUM(`host_wizard_deposit` - `guest_wizard_deposit`) AS `difference`
FROM rw_pw;
---------------
13. Employees Minimum Salaries
SELECT department_id,MIN(salary) FROM employees WHERE department_id IN(2,5,7)
AND hire_date > '2000-01-01' GROUP BY department_id ORDER BY department_id ASC
---------------
14. Employees Average Salaries
CREATE TABLE `higher_salary` SELECT * FROM employees WHERE
salary > 30000;
DELETE FROM `higher_salary` WHERE manager_id=42;
UPDATE `higher_salary` SET salary = salary+5000 WHERE department_id=1;
SELECT department_id,AVG(salary) AS `avg_salary`
FROM `higher_salary` GROUP BY department_id ORDER BY department_id ASC;
--------------
15. Employees Maximum Salaries
SELECT department_id, MAX(salary) AS `max_salary` FROM employees
GROUP BY department_id
HAVING NOT MAX(salary) BETWEEN 30000 AND 70000
ORDER BY department_id ASC;
----------------
16. Employees Count Salaries
SELECT COUNT(salary) FROM employees WHERE manager_id IS NULL;
-----------