# 连接表，筛选出有奖金的人。
# 计算奖金并单加一列

select 
    e.emp_no as emp_no,
    e.first_name as first_name,
    e.last_name as last_name,
    eb.btype as btype,
    s.salary as salary,
    round(case
        when eb.btype = 1 then 0.1*s.salary
        when eb.btype = 2 then 0.2*s.salary
        when eb.btype not in (1,2) then 0.3*s.salary
    end,1) as bonus 
from
    employees as e
    inner join emp_bonus as eb on e.emp_no = eb.emp_no 
    inner join salaries as s on e.emp_no = s.emp_no
where
    s.to_date = '9999-01-01'