java面试题经典29例【第八季.pdf

序序 j java面试题经典29例 第八季 常瑞鹏 ava面试题经典29例 第八季 常瑞鹏 以下各个题目将用到上图的4个表 其关联关系如图所示 这个四个表中字段的具体情况参看如 下内容 EMPLOYEES employee id number 6 not null first namevarchar2 20 last name varchar2 25 not null email varchar2 25 notnull phone number varchar2 20 hire date date not null job id varchar2 10 notnull salary number 8 2 commission pct number 2 2 manager idnumber 6 department id number 4 DEPT department id number 4 not null department name varchar2 30 notnull 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 salnumber 1 EMPLOYEES employee id first name last name email phone number hire date job id salar y commission pct manager id department id job grades grade level lowest sal highes t sal 显示JOB GRADES表的结构 创建一个查询显示所有雇员的name job department name salary 和 grade DESC JOB GRADES SELECT e last name e job id d department name e salary j grade level FROM employees e departments d job grades j WHEREe department id d department id AND e salary BETWEEN j lowest sal ANDj highest sal OR SELECT e last name e job id d department name e salary j grade level FROM employees e JOIN departments d ON e department id d department id JOIN job grades j ON e salary BETWEEN j lowest sal ANDj highest sal 2 EMPLOYEES employee id first name last name email phone number hire date job id salar y commission pct manager id department id 创建一个查询显示那些在雇员 Davies 之后入 本公司工作的雇员的name 和 hire date SELECT e last name e hire date FROM employees e employees davies WHEREdavies last name Davies AND davies hire date e hire date 1 OR SELECT e last name e hire date FROM employees e JOINemployees davies ON davies last name Davies WHEREdavies hire date e hire date 3 EMPLOYEES employee id first name last name email phone number hire date job id salar y commission pct manager id department id 显示所有雇员的 names 和 hire dates 他们 在他们的经理之前进入本公司 连同他们的经理的名字和受雇日期一起显示 列标签分别为 Employee Emp Hired Manager 和 Mgr Hired SELECT w last name w hire date m last name m hire date FROM employees w employees m WHEREw manager id m employee id AND w hire date m hire date OR SELECT w last name w hire date m last name m hire date FROM employees w JOIN employees m ON w manager id m employee id WHERE w hire date 6000 ORDER BY MIN salary DESC 10 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 写一个查询显示每个部门的名字 地点 人数和部门中所有雇员的平均薪水 四舍五入薪水到两位小数 SELECT d department name Name d location id Location COUNT Numberof People ROUND AVG salary 2 Salary FROM employees e departments d WHERE e department id d department id GROUP BYd department name d location id 11 EMPLOYEES employee id first name last name email phone nu mber hire date job id salary commission pct manager id department id 创建一个查询 显示雇员总数 和在 1995 1996 1997 和 1998受雇的雇员人数 创建适当的列标题 SELECT COUNT total SUM DECODE TO CHAR hire date YYYY 1995 1 0 1 995 SUM DECODE TO CHAR hire date YYYY 1996 1 0 1 996 SUM DECODE TO CHAR hire date YYYY 1997 1 0 1 997 SUM DECODE TO CHAR hire date YYYY 1998 1 0 1 998 FROM employees 3 12 EMPLOYEES employee id first name last name email phone nu mber hire date job id salary commission pct manager id department id 创建一个混合 查询显示工作岗位和工作岗位的薪水合计 并且合计部门 20 50 80 和 90 的工作岗位的薪 水 给每列一个恰当的列标题 SELECT job id Job SUM DECODE department id 20 salary Dept 20 SUM DECODE department id 50 salary Dept 50 SUM DECODE department id 80 salary Dept 80 SUM DECODE department id 90 salary Dept 90 SUM salary Total FROM employees GROUP BY job id 13 EMPLOYEES employee id first name last name email phone nu mber hire date job id salary commission pct manager id department id 写一个查询显 示与 Zlotkey 在同一部门的雇员的 last name 和 hire date 结果中不包括 Zlotkey SELECT last name hire date FROM employees WHERE department id SELECT department id FROM employees WHERE last name Zlotkey AND last name Zlotkey 14 EMPLOYEES employee id first name last name email phone nu mber hire date job id salary commission pct manager id department id 创建一个查询 显示所有其薪水高于平均薪水的雇员的雇员号和名字 按薪水的升序排序 SELECT employee id last name FROM employees WHERE salary SELECT AVG salary FROM employees 15 EMPLOYEES employee id first name last name email phone nu mber hire date job id salary commission pct manager id department id 写一个查询显 示所有工作在有任一雇员的名字中包含一个u 的部门的雇员的雇员号和名字 SELECT employee id last name FROM employees WHERE department idIN SELECT department id FROM employees WHERE last name like u 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 显示所有部门地点号 departmentlocation ID 是1700的雇员的lastname departmentnumber 和jobID 4 SELECT last name department id job id FROM employees WHERE department idIN SELECT department id FROM departments WHERE location id 1700 17 EMPLOYEES employee id first name last name email phone nu mber hire date job id salary commission pct manager id department id 显示每个向 King 报告的雇员的名字和薪水 SELECT last name salary FROM employees WHERE manager id SELECT employee id FROM employees WHERE last name King 18 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 显示在Executive部门的每个雇员的 departmentnumber last name 和 job ID SELECT department id last name job id FROM employees WHERE department idIN SELECT department id FROM departments WHERE department name Executive 19 在15题基础上 查询显示所有收入高于平均薪水并且工作在有任 一雇员的名字中带有一个u 的部门的雇员的 employeenumbers last names 和 salaries SELECT employee id last name salary FROM employees WHERE department idIN SELECT department id FROM employees WHERE last name like u AND salary SELECT AVG salary FROM employees 20 创建一个称为EMPLOYEES VU的视图 它基于EMPLOYEES表中的雇员 号 雇员名和部门号 将雇员名的列标题改为EMPLOYEE CREATE OR REPLACE VIEW employees vu AS SELECT employee id last name employee department id FROM employees 21 在20题的基础上 显示 EMPLOYEES VU 视图的内容 SELECT 5 FROM employees vu 22 使用EMPLOYEES VU视图 输入一个查询来显示所有的雇员名和部 门号 SELECT employee department id FROM employees vu 23 创建一个名为DEPT50视图 其中包含部门50中的所有雇员的雇员 号 雇员名和部门号 视图的列标签为EMPNO EMPLOYEE和DEPTNO 不允许通过视图将一个雇员 重新分配到另一个部门 CREATE VIEW dept50 AS SELECT employee idempno last name employee department id deptno FROM employees WHERE department id 50 WITH CHECK OPTIONCONSTRAINT emp dept 50 24 在23题基础上 显示DEPT50视图的结构和内容 DESCRIBE dept50 SELECT FROM dept50 25 创建一个名为SALARY VU的视图 该视图基于所有雇员的名字 部 门名 薪水和薪水级别 用EMPLOYEES DEPARTMENTS和JOB GRADES表 分别命名列标签为 Employee Department Salary和Grade CREATE OR REPLACEVIEW