經典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