经典Java实用面试题20例——转

字体 -

以下各个题目将用到上图的4个表,其关联关系如图所示。这个四个表中字段的具体情况参看如下内容:

EMPLOYEES(employee_id number(6) not null,first_name varchar2(20),last_name varchar2(25) not null,email varchar2(25) not null,phone_number varchar2(20),hire_date date not null,job_id varchar2(10) not null,salary number(8,2),commission_pct number(2,2),manager_id number(6),department_id number(4))
DEPT(department_id number(4) not null,department_name varchar2(30) not null,manager_id number(6),location_id number(4))
locations(location_id number(4) not null,city varchar2(20))
job_grades(grade_level varchar2(3),lowest_sal number,highest_sal number)

1. 写一个查询显示当前日期,列标签显示为 Date。

SELECT sysdate “Date” FROM dual;

2.EMPLOYEES

(employee_id,first_name,last_name,email,phone_number,

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

对每一个雇员,显示employee number、last_name、salary和 salary 增加 15%,并且表示成整数,列标签显示为 New Salary
SELECT employee_id, last_name, salary, ROUND(salary * 1.15, 0) “New Salary” FROM employees;

3. 在2题的基础上,添加一个列,该列从新薪水 New Salary 列(新薪水指的是增加15%以后的薪水)中减去旧薪水,列标签为Increase。

SELECT employee_id, last_name, salary, ROUND(salary * 1.15, 0) “New Salary”, ROUND(salary * 1.15, 0) - salary “Increase” FROM employees;

4.EMPLOYEES

(employee_id,first_name,last_name,email,phone_number,

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

写一个查询用首字母大写,其它字母小写显示雇员的last names,显示名字的长度,对所有名字开始字母是 J、A或 M 的雇员,给每列一个适当的标签。用雇员的last names 排序结果。

SELECT INITCAP(last_name) “Name”, LENGTH(last_name) “Length” FROM employees WHERE last_name LIKE ‘J%’ OR last_name LIKE ‘M%’ OR last_name LIKE ‘A%’ ORDER BY last_name;

5.EMPLOYEES

(employee_id,first_name,last_name,email,phone_number,

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

对每一个雇员,显示其的last name,并且计算从雇员受雇日期到今天的月数,列标签MONTHS_WORKED。按受雇月数排序结果,四舍五入月数到最靠近的整数月。

SELECT last_name, ROUND(MONTHS_BETWEEN (SYSDATE, hire_date)) MONTHS_WORKED FROM employees ORDER BY MONTHS_BETWEEN(SYSDATE, hire_date); 加国 无忧 51.CA

6.EMPLOYEES

(employee_id,first_name,last_name,email,phone_number,

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

写一个查询对每个雇员做计算:<雇员的 last name> earns monthly but wants <3倍 salary>。列标签 Dream Salaries。

SELECT last_name || ‘ earns ‘ || TO_CHAR(salary, ‘fm$99,999.00′) || ‘ monthly but wants ‘ || TO_CHAR(salary * 3, ‘fm$99,999.00′) || ‘.’ “Dream Salaries” FROM employees;

7.EMPLOYEES

(employee_id,first_name,last_name,email,phone_number,

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

创建一个查询显示所有雇员的last name 和 salary。格式化为15 个字符长度,用 $ 左填充,列标签 SALARY。

SELECT last_name, LPAD(salary, 15, ‘$’) SALARY FROM employ;

8.EMPLOYEES

(employee_id,first_name,last_name,email,phone_number,

hire_date,job_id,salary,commission_pct,manager_id,department_id)。 info.51.ca

显示每一个雇员的last name、hire date 和 salary 检查日期,该日期是服务六个月后的第一个星期一,列标签 REVIEW。格式化日期显示看起来象“Monday, the Thirty-First of July, 2000” 的样子。 info.51.ca

SELECT last_name, hire_date, TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date, 6),’MONDAY’), ‘fmDay, “the” Ddspth “of” Month, YYYY’) REVIEW FROM employees;

9.EMPLOYEES 51.CA

(employee_id,first_name,last_name,email,phone_number, 加国 无忧 51.CA

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

显示last name、hire date 和 雇员开始工作的周日,列标签 DAY,用星期一作为周的起始日排序结果。

SELECT last_name, hire_date, TO_CHAR(hire_date, ‘DAY’) DAY
FROM employees ORDER BY TO_CHAR(hire_date - 1, ‘d’);

10.EMPLOYEES

(employee_id,first_name,last_name,email,phone_number,

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

创建一个查询显示雇员的last names 和 commission (佣金)比率。如果雇员没有佣金,显示 “No Commission”,列标签COMM。

SELECT last_name, NVL(TO_CHAR(commission_pct), ‘No Commission’) COMM FROM employees;

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;

分享博文至:

    1 条评论

  1. 1. newjob123 - 2015年1月4日 21:55

    只要你是学习计算机或是软件开发相关专业的人,不管是科班出身,还是自学成才,一定都会对JAVA技术或多或少都有所涉猎。JAVA技术在各行各业不同技术开发领域中都拥有着无法替代的地位。而Java Developer作为IT行业高薪的职位,也越来越受到人们的青睐。

    然而,虽然很多拥有IT背景的大陆移民都能胜任Java Developer的工作,但在Java面试过程中:千变万化的技术问题、异国他乡陌生的企业文化、语言沟通的障碍……这些问题都成为华人求职路中的“拦路虎”,它们往往令我们措手不及,令之前的种种努力功亏一篑……

    为了帮助广大华人解决以上问题,在本次公益讲座上,Joe Yang将把Java技术面试中的“妙计”娓娓道来、倾囊相授。详尽解析大家在Java技术面试中遇到的各种问题,帮助大家打好Java求职路的“头阵”!

    时间:1月10日周六 上午11点

    主讲人:Joe Yang, Senior Java架构师,曾在联邦和安省政府项目中任高级架构师,现任Rogers IT高级Java架构师。在超过千万加元的项目中,主持系统设计。拥有十几年开发和教学经验,精通J2EE技术,Spring、Hibernate、Struts、SOA、ESB、Web Services、AJAX,讲解条理清晰。熟悉IT行业招聘流程,部门Java Developer职位招聘的技术负责人。

    『新职星期六』大型公益活动正在火爆进行中。 订位电话:416-644-1998 机会千载难缝,不容错过。座位有限,订满为止,赶快打电话定位吧!

发表评论