LAB6
T_SQL Lab
Lab 6進階查詢-子查詢(Subquery)
1. Write a query to display the last name and hire date of any employee in the same
department as Zlotkey. Exclude Zlotkey.
找出 與員工姓名 last_name 為 Zlotkey 相同部門編號department_id的員工, 列出員工姓名 last_name及就職日期hire_date, 不含 Zlotkey本人
SELECT last_name, hire_date
FROM employees
WHERE department_id = (SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey')
AND last_name <> 'Zlotkey'
2. Create a query to display the employee numbers and last names of all employees who earn more than the average salary. Sort the results in ascending order of salary.
查出員工薪資salary 大於平均薪資的員工, 列出員工姓名 last_name及薪資salary並依薪資salary
從小到大排列
SELECT SALARY, LAST_NAME
FROM EMPLOYEES
WHERE SALARY> (SELECT AVG(SALARY) FROM EMPLOYEES )
ORDER BY salary
3. Write a query that displays the employee numbers and last names of all employees who work in a department with any employee whose last name contains a u. Place your SQL statement in a text file named lab6_3.sql. Run your query.
找出 與員工姓名 last_name 中含有 ‘u’ 的員工中, 相同部門編號department_id的員工, 列出員工編號employee_id, 員工姓名 last_name
SELECT employee_id, last_name
FROM employees
WHERE department_id IN (SELECT department_id
FROM employees
WHERE last_name like '%u%')
4. Display the last name and salary of every employee who reports to King.
查出 那些員工的主管是King, 列出 員工姓名 last_name, 薪資salary
SELECT last_name, salary
FROM employees
WHERE manager_id = (SELECT employee_id
FROM employees
WHERE last_name = 'King')
5. Display the department number, last name, and job ID for every employee in the Executive department.
查出 那些員工的任職於部門名稱(department_name) Executive, 列出 部門編號department_id, 員工姓名 last_name, 職務代號job_id
SELECT DEPARTMENT_ID,LAST_NAME,JOB_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE DEPARTMENT_NAME = 'EXECUTIVE')