LAB5
T_SQL Lab
Lab 5進階查詢-合併查詢(Join)
1. Write a query to display the last name, department number, and department name for
all employees.
列出 員工姓名last_name, 部門編號department_id, 及 所屬部門名稱 department_name
SELECT e.last_name, e.department_id, d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
2. Create a unique listing of all jobs that are in department 80. Include the location of the department in the output.
列出部門編號department_id為 80的 員工職務job_id及部門位置location_id, 消去重複值
SELECT DISTINCT job_id, location_id
FROM employees JOIN departments
ON employees.department_id = departments.department_id
WHERE employees.department_id = 80
3. Write a query to display the employee last name, department name, location ID, and city of all employees who earn a commission.
從員工資料表employees, 部門表departments, 位置表locations 三個表格中找出有領傭金commission_pct之員工姓名last_name, 部門名稱department_name,部門位置location_id, 及部門所在都市city
SELECT e.last_name, d.department_name, d.location_id, l.city
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
WHERE e.commission_pct IS NOT NULL
4. Display the employee last name and employee number along with their manager’s last name and manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively.
列出 員工姓名last_name 別名 Employee, 員工編號employee_id 別名 Emp#,主管姓名
別名 Manager及 主管編號 別名 Mgr#, 存檔成 lab5_4.sql
SELECT e.last_name, d.department_name, d.location_id, l.city
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
WHERE e.commission_pct IS NOT NULL
5. Modify lab5_4.sql to display all employees including King, who has no manager. Order the results by the employee number. Place your SQL statement in a text file named lab5_5.sql.
修改 lab5_4.sql 增加列出 員工姓名last_name為 King 且沒有主管的員工
SELECT w.last_name 'Employee', w.employee_id 'EMP#', m.last_name 'Manager',
m.employee_id 'Mgr#'
FROM employees w
LEFT OUTER JOIN employees m
ON (w.manager_id = m.employee_id)
ORDER BY 'EMP#'