# $1988. 找出每所学校的最低分数要求
# https://leetcode-cn.com/problems/find-cutoff-score-for-each-school/

# SQL架构
Create table If Not Exists Schools (school_id int, capacity int);
Create table If Not Exists Exam (score int, student_count int);
Truncate table Schools;
insert into Schools (school_id, capacity) values ('11', '151');
insert into Schools (school_id, capacity) values ('5', '48');
insert into Schools (school_id, capacity) values ('9', '9');
insert into Schools (school_id, capacity) values ('10', '99');
Truncate table Exam;
insert into Exam (score, student_count) values ('975', '10');
insert into Exam (score, student_count) values ('966', '60');
insert into Exam (score, student_count) values ('844', '76');
insert into Exam (score, student_count) values ('749', '76');
insert into Exam (score, student_count) values ('744', '100');

# Write your MySQL query statement below
select
    school_id,
    ifnull(min(score), -1) as score
from
    (
        select
            s.school_id,
            e.score
        from
            Schools s
            left join Exam e on s.capacity >= e.student_count
    ) tmp
group by
    school_id;

# clean-up
drop table Schools;
drop table Exam;