经典java面试题20例(2)

　　12. EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。用DECODE 函数，写一个查询，按照下面的数据显示所有雇员的基于 JOB_ID列值的级别。

　　工作 级别

　　AD_PRES A

　　ST_MAN B

　　IT_PROG C

　　SA_REP D

　　ST_CLERK E

　　不在上面的 0

　　SELECT job_id, decode (job_id, 'ST_CLERK', 'E', 'SA_REP', 'D', 'IT_PROG', 'C', 'ST_MAN', 'B', 'AD_PRES', 'A', '0') GRADE FROM employees;

　　13. EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。DEPT(department_id,department_name,manager_id,location_id)。写一个查询显示所有雇员的 last name、department number、and department name。

　　SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id;

　　14. EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。DEPT(department_id,department_name,manager_id,location_id)。创建一个在部门80 中的所有工作岗位的唯一列表，在输出中包括部门的地点。

　　SELECT DISTINCT job_id, location_id FROM employees, departments WHERE employees.department_id = departments.department_id AND employees.department_id = 80;

　　15. EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。DEPT(department_id,department_name,manager_id,location_id)。locations(location_id,city)。写一个查询显示所有有佣金的雇员的last name、department name、location ID和城市。

　　SELECT e.last_name, d.department_name, d.location_id, l.c ity FROM employees e, departments d, locations l WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND e.commission_pct IS NOT NULL;

　　16. EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。DEPT(department_id,department_name,manager_id,location_id)。显示所有在其last names 中有一个小写 a 的雇员的last name 和 department name。

　　SELECT last_name, department_name FROM employees, departments WHERE employees.department_id = departments.department_id AND last_name LIKE '%a%';

　　17. EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。DEPT(department_id,department_name,manager_id,location_id)。locations(location_id,city)。写一个查询显示那些工作在Toronto 的所有雇员的 last name、job、department number和 department name。

　　SELECT e.last_name, e.job_id, e.department_id, d.department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) JOIN locations l ON (d.location_id = l.location_id) WHERE LOWER(l.city) = 'toronto';

　　18. EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。显示雇员的last name 和 employee number 连同他们的经理的 last name 和 manager number。列标签分别为Employee、Emp#、Manager和 Mgr#

　　SELECT w.last_name "Employee", w.employee_id "EMP#", m.last_name "Manager", m.employee_id "Mgr#" FROM employees w join employees m ON (w.manager_id = m.employee_id);

　　19. 在18题基础上，显示所有雇员包括 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);

　　20. EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。创建一个查询显示所有与被指定雇员工作在同一部门的雇员(同事) 的 last names、department numbers。给每列一个适当的标签。

　　SELECT e.department_id department, e.last_name employee, c.last_name colleague FROM employees e JOIN employees c ON (e.department_id = c.department_id) WHERE e.employee_id <> c.employee_id ORDER BY e.department_id, e.last_name, c.last_name;

原文转自：http://blog.csdn.net/chrp99/article/details/8848265