在HR帳戶的employees, job_history資料表中,查詢公司內從來沒有更換過工作的員工,並顯示員工編號和姓氏。
SELECT employee_id, last_name
FROM employees
MINUS
SELECT employee_id, e.last_name
FROM job_history j JOIN employees e
Using (employee_id);
在HR帳戶的employees和departments資料表中,查詢公司有設主管的部門代碼。
SELECT department_id
FROM departments
SELECT department_id
FROM departments
WHERE mannger_id IS NULL;
查詢並顯示每位員工目前職務,任職部門和先前職務與待過的部門,可以重複顯示(如果資料相同)。
SELECT employee_id, job_id, department_id
FROM employees
UNION ALL
SELECT employee_id, job_id, department_id
FROM job_history
ORDER BY 1;
查詢HR帳戶的employees和job_history資料表,顯示每位員工目前職務,任職部門和先前職務與待過的部門,不可以重複顯示(如果資料相同)。
說明:
SELECT employee_id, job_id
FROM employees
UNION
SELECT employee_id, job_id
FROM job_history;
查詢HR帳戶的employees和job_history資料表,公司員工曾更換工作,但現在回來任職先前職務,並顯示員工編號、姓氏和職務。
SELECT employee_id, last_name, job_id
FROM employees
INTERSECT
SELECT employee_id, e.last_name, j.job_id
FROM job_history j JOIN employees e
Using (employee_id);