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)計salary的累計和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
);
