LOGIN用户登陆

51登录名:

用户密码:

新注册 | 找回密码

经典Java实用面试题20例分享(二)——转

已有 193 次阅读  2015-01-04 20:21   标签important  border  normal  color  style 

11.EMPLOYEES(employee_id,first_name,last_name,email,phone_number,


hire_date,job_id,salary,commission_pct,manager_id,department_id)。

创建一个查询显示雇员的last names 并带星号显示他们的年薪,每个星号表示1000美圆。按薪水降序排序数据。列标签为EMPLOYEES_AND_THEIR_SALARIES。


SELECT rpad(last_name, 8)||' '|| rpad(' ', (salary*12)/1000+1, '*') EMPLOYEES_AND_THEIR_SALARIES FROM employees ORDER BY salary DESC; info.51.ca 无忧资讯

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列值的级别。 info.51.ca

工作 级别
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, info.51.ca 无忧资讯

hire_date,job_id,salary,commission_pct,manager_id,department_id)。DEPT(department_id,department_name,manager_id,location_id)。创建一个在部门80 中的所有工作岗位的唯一列表,在输出中包括部门的地点。 加拿大 51网

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, 加拿大 51网

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;


点击 了解更多
分享 举报