雇员查询java面试题经典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,salary,commission_pct,manager_id,department_id)。job_grades(grade_level,lowest_sal,highest_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,salary,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

    -- 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,salary,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 < m.hire_date;

    4.  EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。表现全部雇员的最高、最低、计合和均匀薪水，列签标分离为：Maximum、Minimum、Sum 和 Average。四舍五入结果为近来的数整

      SELECTROUND(MAX(salary),0) "Maximum",

     ROUND(MIN(salary),0)"Minimum",

     ROUND(SUM(salary),0)"Sum",

     ROUND(AVG(salary),0)"Average"

     FROM employees;

    5.  在4题基础上，表现每中任务类型的最低、最高、计合和均匀薪水。

      SELECT job_id,ROUND(MAX(salary),0) "Maximum",

     ROUND(MIN(salary),0)"Minimum",

     ROUND(SUM(salary),0)"Sum",

     ROUND(AVG(salary),0)"Average"

     FROM employees

      GROUP BY job_id;

    6.  EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。写一个查询表现每一任务岗位的人数。

    SELECT   job_id,COUNT(*)

     FROM   employees

      GROUP BY job_id;

    7.  EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。定确理经人数，不需要列出他们，列签标是 Number of Managers。示提：用MANAGER_ID 列决议理经号。

      SELECT COUNT(DISTINCTmanager_id) "Number of Managers"

     FROM employees;

    8.  EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。写一个查询表现最高和最低薪水之间的差。列签标是 DIFFERENCE。

    SELECT MAX(salary) - MIN(salary) DIFFERENCE

     FROM employees;

    9.  EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。表现理经号和理经付给雇员的最低薪水。除排那些理经未知的人。除排最低薪水小于于等 $6,000 的组。按薪水降序排序出输。

    SELECT manager_id, MIN(salary)

     FROM employees

      WHERE manager_id ISNOT NULL

      GROUP BY manager_id

      HAVING MIN(salary)> 6000

      ORDER BY MIN(salary)DESC;

    10.            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)。写一个查询表现个每门部的名字、所在、人数和门部中全部雇员的均匀薪水。四舍五入薪水到两位小数。

    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_number,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;

    12.            EMPLOYEES(employee_id,first_name,last_name,email,phone_number,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_number,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_number,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_number,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_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。DEPT(department_id,department_name,manager_id,location_id)。表现全部门部所在号(departmentlocation ID ) 是1700的雇员的lastname、departmentnumber 和jobID。

      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_number,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_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。DEPT(department_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 *

     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 salary_vu

     AS

      SELECT e.last_name"Employee",

     d.department_name"Department",

     e.salary"Salary",

     j.grade_level"Grades"

      FROM employees e,

     departments d,

     job_grades j

      WHERE e.department_id= d.department_id

      AND e.salary BETWEENj.lowest_sal and j.highest_sal;

    26.            建创一个序列用于DEPT表的主键列，该序列从200开始，并且有最大值1000，序列的增量是10，序列的名字是DEPT_ID_SEQ。

    CREATE SEQUENCE dept_id_seq

      START WITH 200

      INCREMENT BY 10

     MAXVALUE 1000;

    27.            写一个查询，表现面下关于序列的信息：序列名、最大值、增量小大和最后的值

    SELECT sequence_name, max_value, increment_by, last_number

     FROM user_sequences;

    28.            DEPT(department_id,department_name,manager_id,location_id)。写一个脚本插入两行到 DEPT 表中，并应用66题建创的序列来生成主键。

      INSERT INTO dept

      VALUES(dept_id_seq.nextval, 'Education');

    29. 在 EMP 表中的键外列 DEPT_ID 上建创一个非唯一性引索。

      CREATE INDEX emp_dept_id_idx ON emp(dept_id);

文章结束给大家分享下程序员的一些笑话语录： 一条狗在街上闲逛，看见橱窗里一张告示：「招聘程序员。会编程，有团队精神，至少精通两种语言。均等机会。」
　　那条狗就进去申请，但是被拒绝了。
　　「我不能雇一条狗在公司里做事。」经理说。
　　狗不服气，指着告示上「均等机会」几字抗议。
　　经理没法，叹了口气，不屑地问道：「你会编程吗？」
　　那条狗默默地走到电脑前，编了个程序，运作准确。
　　「你有团队精神吗？」经理问。
　　那条狗掉头看了看门外，一大群野狗在外面虎视耽耽。
　　「我真的不能雇狗做这份工作。」经理气急败坏地说。
　　「就算会编程、有团队精神，但是我需要的雇员至少要能精通两种语言。」
　　那条狗抬头看着经理说：「喵－噢。」