Untitled

56
CREATE PROCEDURE solution()
BEGIN
	select dep_name from departments d
    where not exists (
        select * from employees e where e.department = d.id 
    );
END

57

CREATE PROCEDURE solution()
BEGIN
	select h.holiday_date as ski_day
    from holidays h
    join weather w on w.sunny_date = h.holiday_date;
END

58

CREATE PROCEDURE solution()
BEGIN
	select p1.id id1, (
        select p2.id
        from positions p2
        where p2.id <> p1.id
        order by power(p1.x-p2.x, 2) + power(p1.y-p2.y, 2) asc limit 1
        ) id2
    from positions p1;
END

59

CREATE PROCEDURE solution()
BEGIN
	select round(avg(grade), 2) as average_grade
    from (
        select grade
        from students
        order by grade DESC
        limit 5
    ) as a;
END


60

CREATE PROCEDURE solution()
BEGIN
	select ifnull(sum(employees.salary = maxs)* maxs - sum(employees.salary = mins)* mins, 0) as salary_diff
    from employees, (
        select max(salary) maxs,
                min(salary) mins
        from 
            employees
    ) max_min ;
END

61

CREATE PROCEDURE solution()
BEGIN
	select name
    from (
        (select 1 as department, name from pr_department order by date_joined desc limit 5)
        UNION ALL
       ( select 2 as department, name from it_department order by date_joined desc limit 5)
        UNION ALL
        (select 3 as department, name from sales_department order by date_joined desc limit 5)
    ) y
    order by department asc , name;
END