57.使用含有關(guān)鍵字exists查找未分配具體部門的員工的所有信息

SELECT *
FROM employees
WHERE NOT EXISTS(SELECT emp_no
                FROM dept_emp
                WHERE emp_no = employees.emp_no)

58.獲取employees中的行數(shù)據(jù)，且這些行也存在于emp_v中

SELECT em.*
FROM employees AS em, emp_v AS ev
WHERE em.emp_no = ev.emp_no

59.獲取有獎金的員工相關(guān)信息

SELECT em.emp_no, em.first_name, em.last_name, eb.btype, sal.salary, 
(CASE eb.btype 
 WHEN 1 THEN sal.salary * 0.1
 WHEN 2 THEN sal.salary * 0.2
 ELSE sal.salary * 0.3 END) AS bonus
FROM employees AS em INNER JOIN emp_bonus AS eb
ON em.emp_no = eb.emp_no
INNER JOIN salaries AS sal
ON em.emp_no = sal.emp_no
AND sal.to_date = '9999-01-01'

注：最好自己做一個連接圖表示表與表之間的聯(lián)系，這道題很明顯沒用到dept_emp表。

60.統(tǒng)計(jì)salary的累計(jì)和running_total

SELECT s1.emp_no, s1.salary, 
(SELECT SUM(s2.salary)
FROM salaries AS s2 
WHERE s2.emp_no <= s1.emp_no
AND s2.to_date = '9999-01-01') AS running_total
FROM salaries AS s1
WHERE s1.to_date = '9999-01-01'
ORDER BY s1.emp_no;

61.對于employees表中，給出奇數(shù)行first_name

SELECT first_name FROM ( 
    SELECT e2.first_name,
    (SELECT COUNT(*) 
     FROM employees e1
     WHERE e1.first_name <= e2.first_name ) AS rownum 
    FROM employees e2 
    WHERE rownum % 2 =1 
);