计算工资

CREATE TABLE emp_bonus (empno INT , received DATE , TYPE INT);
INSERT  INTO emp_bonus VALUES( 7934, DATE '2005-5-17', 1 );
INSERT  INTO emp_bonus VALUES( 7934, DATE '2005-2-15', 2 );
INSERT  INTO emp_bonus VALUES( 7839, DATE '2005-2-15', 3 );
INSERT  INTO emp_bonus VALUES( 7782, DATE '2005-2-15', 1 );

员工的奖金的根据TYPE计算，TYPE1的奖金为员工工资的10%，TYPE2的奖金为员工工资的20%，TYPE3的奖金为员工工资的30%。
现要求返回上述员工也就是部门10的所有员工的工资及奖金。

SQL> SELECT e.deptno,
  2         e.empno,
  3         e.ename,
  4         e.sal,
  5         (e.sal * CASE
  6           WHEN eb.type = 1 THEN
  7            0.1
  8           WHEN eb.type = 2 THEN
  9            0.2
 10           WHEN eb.type = 3 THEN
 11            0.3
 12         END) AS bonus
 13    FROM emp e
 14   INNER JOIN emp_bonus eb ON (e.empno = eb.empno)
 15   WHERE e.deptno = 10
 16   ORDER BY 1, 2;

    DEPTNO      EMPNO ENAME             SAL      BONUS
---------- --