经典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);

　　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 employees;

　　8. EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。显示每一个雇员的last name、hire date 和 salary 检查日期，该日期是服务六个月后的第一个星期一，列标签 REVIEW。格式化日期显示看起来象“Monday, the Thirty-First of July, 2000” 的样子。

　　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(employee_id,first_name,last_name,email,phone_number,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;

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