10-mysql

select count(*) from wizzard_deposits;

select max(magic_wand_size) as longest_magic_wand from wizzard_deposits;

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;

select deposit_group from wizzard_deposits
group by deposit_group
order by min(magic_wand_size)
limit 1;

select deposit_group, sum(deposit_amount) as total_sum
from wizzard_deposits
group by deposit_group
order by total_sum;

select deposit_group, sum(deposit_amount) as total_sum
from wizzard_deposits
where magic_wand_creator = 'Ollivander family'
group by deposit_group
order by deposit_group;

select deposit_group, sum(deposit_amount) as total_sum
from wizzard_deposits
where magic_wand_creator = 'Ollivander family'
group by deposit_group
having total_sum < 150000
order by total_sum desc;

select deposit_group, magic_wand_creator, min(deposit_charge)
from wizzard_deposits
group by deposit_group, magic_wand_creator
order by magic_wand_creator, deposit_group;

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]'
else '[61+]'
end)
as age_group,
count(*)
from wizzard_deposits
group by age_group
order by age_group;

select left(first_name, 1) as f1 from wizzard_deposits
where deposit_group = 'Troll Chest'
group by f1
order by f1;

select deposit_group, is_deposit_expired, avg(deposit_interest)
from wizzard_deposits
where deposit_start_date > '1985-01-01'
group by deposit_group, is_deposit_expired
order by deposit_group desc, is_deposit_expired;

select sum(diff.next) as sum_diff
from (
select deposit_amount -
( select deposit_amount
from wizzard_deposits
where id = wd.id + 1) as 'next'
from wizzard_deposits as wd) as diff;

select department_id, min(salary)
from employees
where hire_date > '2000-01-01' and department_id in (2, 5, 7)
group by department_id
order by department_id;

create table empl as
select * from employees
where salary > 30000 and manager_id != 42;
update empl
set salary = salary + 5000
where department_id = 1;
select department_id, avg(salary)
from empl
group by department_id
order by department_id;

select department_id, max(salary)
from employees
group by department_id
having max(salary) not between 30000 and 70000
order by department_id;

select count(first_name) from employees
where manager_id is null;

select e.department_id,
(select distinct e2.salary
from employees as e2
where e2.department_id = e.department_id
order by e2.salary desc
limit 2, 1) as three
from employees as e
group by e.department_id
having three is not null
order by department_id;

select e1.first_name, e1.last_name, e1.department_id
from employees as e1
where salary >
(select avg(e2.salary) as sal
from employees as e2
where e2.department_id = e1.department_id
group by e2.department_id)
order by department_id, employee_id
limit 10;

select department_id , sum(salary) from employees
group by department_id
order by department_id;