java面试题经典20例【第九季_常瑞鹏】.pdf

序序 j java面试题经典20例 第九季 常瑞鹏 ava面试题经典20例 第九季 常瑞鹏 以下各个题目将用到上图的4个表 其关联关系如图所示 这个四个表中字段的具体情况参看如 下内容 EMPLOYEES employee id number 6 not null fi rst 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 loca on id number 4 loca ons loca on 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 salar y 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 salar y 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 1 5 EMPLOYEES employee id first name last name email phone number hire date job id salar y 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 salar y commission pct manager id department id 写一个查询对每个雇员做计算 earns monthly but wants 列标签 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 salar y 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 salar y 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 salar y 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 nu mber 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 nu mber 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 12 EMPLOYEES employee id first name last name email phone nu mber hire date job id salary commission pct manager id department id 用DECODE 函 数 写一个查询 按照下面的数据显示所有雇员的基于 JOB ID列值的级别 工作 级别 2 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 nu mber hire date job id salary commission pct manager id department id DEPT departme nt 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 nu mber hire date job id salary commission pct manager id department id DEPT departme nt 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 nu mber hire date job id salary commission pct manager id department id DEPT departme nt 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 mission pct IS NOT NULL 16 EMPLOYEES employee id first name last name email phone nu mber hire date job id salary commission pct manager id department id DEPT departme nt 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 nu mber hire date job id salary commission pct manager id department id DEPT departme nt 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 nu mber hire date job id salary commission pct manager id department id 显示雇员的 last name 和 employee number 连同他们的经理的 last name 和 manager number 列标签分 别为Employee Emp Manager和 Mgr 3 SELECT w last name Employee w employee id EMP m last name Manager