SQL面试用题

                                                                               SQL面试用题

  1. 查询每个月倒数第 2 天入职的员工的信息.
  2. 查询出 last_name 为 ‘Chen’ 的 manager 的信息.
  3. 查询平均工资高于 8000 的部门 id 和它的平均工资.
  4. 查询工资最低的员工信息: last_name, salary
  5. 查询平均工资最低的部门信息
  6. 查询平均工资最低的部门信息和该部门的平均工资
  7. 查询平均工资最高的 job 信息
  8. 查询平均工资高于公司平均工资的部门有哪些?
  9. 查询出公司中所有 manager 的详细信息.
  10. 各个部门中 最高工资中最低的那个部门的 最低工资是多少
  11. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
  12. 查询 1999 年来公司的人所有员工的最高工资的那个员工的信息.
  13. 返回其它部门中比job_id‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary

 

************************answers*********************

  1. 查询每个月倒数第 2 天入职的员工的信息.

select last_name, hire_date

from employees

where hire_date = last_day(hire_date) – 1

  1. 查询出 last_name 为 ‘Chen’ 的 manager 的信息.

1). 通过两条 sql 查询:

select manager_id

from employees

where lower(last_name) = ‘chen’ –返回的结果为 108

select *

from employees

where employee_id = 108

2). 通过一条 sql 查询(自连接):

select m.*

from employees e, employees m

where e.manager_id = m.employee_id and e.last_name = ‘Chen’

3). 通过一条 sql 查询(子查询):

select *

from employees

where employee_id = (

                      select manager_id

                      from employees

                      where last_name = ‘Chen’

                    )

  1. 查询平均工资高于 8000 的部门 id 和它的平均工资.

SELECT department_id, avg(salary)

FROM employees e

GROUP BY department_id

HAVING avg(salary) > 8000

  1. 查询工资最低的员工信息: last_name, salary

SELECT last_name, salary

FROM employees

WHERE salary = (

SELECT min(salary)

FROM employees

)

  1. 查询平均工资最低的部门信息

SELECT *

FROM departments

WHERE department_id = (

SELECT department_id

FROM employees

GROUP BY department_id

HAVING avg(salary) = (

SELECT min(avg(salary))

FROM employees

GROUP BY department_id

)

)

  1. 查询平均工资最低的部门信息和该部门的平均工资

select d.*, (select avg(salary) from employees where department_id = d.department_id)

from departments d

where d.department_id = (

      SELECT department_id

      FROM employees

      GROUP BY department_id

      HAVING avg(salary) = (

 SELECT min(avg(salary))

 FROM employees

 GROUP BY department_id

  )

      )

  1. 查询平均工资最高的 job 信息

1). 按 job_id 分组, 查询最高的平均工资

SELECT max(avg(salary))

FROM employees

GROUP BY job_id

2). 查询出平均工资等于 1) 的 job_id

SELECT job_id

FROM employees

GROUP BY job_id

HAVING avg(salary) = (

SELECT max(avg(salary))

FROM employees

GROUP BY job_id

)

3). 查询出 2) 对应的 job 信息

SELECT *

FROM jobs

WHERE job_id = (

SELECT job_id

FROM employees

GROUP BY job_id

HAVING avg(salary) = (

SELECT max(avg(salary))

FROM employees

GROUP BY job_id

)

)

  1. 查询平均工资高于公司平均工资的部门有哪些?

1). 查询出公司的平均工资

SELECT avg(salary)

FROM employees

2). 查询平均工资高于 1) 的部门 ID

SELECT department_id

FROM employees

GROUP BY department_id

HAVING avg(salary) > (

SELECT avg(salary)

FROM employees

)

  1. 查询出公司中所有 manager 的详细信息.

1). 查询出所有的 manager_id

SELECT distinct manager_id

FROM employeess

2). 查询出 employee_id 为 1) 查询结果的那些员工的信息

SELECT employee_id, last_name

FROM employees

WHERE employee_id in (

SELECT distinct manager_id

FROM employees

)

  1. 各个部门中 最高工资中最低的那个部门的 最低工资是多少

1). 查询出各个部门的最高工资

SELECT max(salary)

FROM employees

GROUP BY department_id

2). 查询出 1) 对应的查询结果的最低值: 各个部门中最低的最高工资(无法查询对应的 department_id)

SELECT min(max(salary))

FROM employees

GROUP BY department_id

3). 查询出 2) 所对应的部门 id 是多少: 各个部门中最高工资等于 2) 的那个部门的 id

SELECT department_id

FROM employees

GROUP BY department_id

HAVING max(salary) = (

SELECT min(max(salary))

FROM employees

GROUP BY department_id

)

4). 查询出 3) 所在部门的最低工资

SELECT min(salary)

FROM employees

WHERE department_id = (

SELECT department_id

FROM employees

GROUP BY department_id

HAVING max(salary) = (

SELECT min(max(salary))

FROM employees

GROUP BY department_id

)

)

  1. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

1). 各个部门中, 查询平均工资最高的平均工资是多少

SELECT max(avg(salary))

FROM employees

GROUP BY department_id

2). 各个部门中, 平均工资等于 1) 的那个部门的部门号是多少

SELECT department_id

FROM employees

GROUP BY department_id

HAVING avg(salary) = (

SELECT max(avg(salary))

FROM employees

GROUP BY department_id

)

3). 查询出 2) 对应的部门的 manager_id

SELECT manager_id

FROM departments

WHERE department_id = (

SELECT department_id

FROM employees

GROUP BY department_id

HAVING avg(salary) = (

SELECT max(avg(salary))

FROM employees

GROUP BY department_id

)

)

4). 查询出 employee_id 为 3) 查询的 manager_id 的员工的 last_name, department_id, email, salary

SELECT last_name, department_id, email, salary

FROM employees

WHERE employee_id = (

SELECT manager_id

FROM departments

WHERE department_id = (

SELECT department_id

FROM employees

GROUP BY department_id

HAVING avg(salary) = (

SELECT max(avg(salary))

FROM employees

GROUP BY department_id

)

)

)

  1. 查询 1999 年来公司的人所有员工的最高工资的那个员工的信息.

1). 查询出 1999 年来公司的所有的员工的 salary

SELECT salary

FROM employees

WHERE to_char(hire_date, ‘yyyy’) = ‘1999’

2). 查询出 1) 对应的结果的最大值

SELECT max(salary)

FROM employees

WHERE to_char(hire_date, ‘yyyy’) = ‘1999’

3). 查询工资等于 2) 对应的结果且 1999 年入职的员工信息

SELECT *

FROM employees

WHERE to_char(hire_date, ‘yyyy’) = ‘1999’ AND salary = (

SELECT max(salary)

FROM employees

WHERE to_char(hire_date, ‘yyyy’) = ‘1999’

)

  1. 返回其它部门中比job_id‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary

SELECT employee_id, last_name, job_id, salary

FROM   employees

WHERE  salary < ALL

                    (SELECT salary

                     FROM   employees

                     WHERE  job_id = ‘IT_PROG’)

AND    job_id <> ‘IT_PROG’;

******************高级子查询******************

  • 书写多列子查询
  • 在 FROM 子句中使用子查询
  • 在SQL中使用单列子查询
  • 书写相关子查询
  • 使用 EXISTS 和 NOT EXISTS 操作符
  • 使用子查询更新和删除数据
  • 使用 WITH 子句

–多列子查询(不成对比较 & 成对比较)

  1. 查询与141号或174号员工的manager_iddepartment_id相同的其他员工的employee_id, manager_id, department_id

[方式一]

SELECT  employee_id, manager_id, department_id

FROM    employees

WHERE   manager_id IN
                  (SELECT  manager_id

                   FROM    employees

                   WHERE   employee_id IN (174,141))

AND     department_id IN
                  (SELECT  department_id

                   FROM    employees

                   WHERE   employee_id IN (174,141))

AND employee_id NOT IN(174,141);

[方式二]

SELECT employee_id, manager_id, department_id

FROM employees

WHERE  (manager_id, department_id) IN

                      (SELECT manager_id, department_id

                       FROM   employees

                       WHERE  employee_id IN (141,174))

AND employee_id NOT IN (141,174); 

–在 FROM 子句中使用子查询

  1. 返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资

[方式一]

select last_name,department_id,salary,

(select avg(salary)from employees e3

where e1.department_id = e3.department_id

group by department_id) avg_salary

from employees e1

where salary >

         (select avg(salary)

          from employees e2  

          where e1.department_id = e2.department_id

          –group by department_id

          )

[方式二]

SELECT  a.last_name, a.salary,

        a.department_id, b.salavg

FROM    employees a, (SELECT   department_id,

                      AVG(salary) salavg

                      FROM     employees

                      GROUP BY department_id) b

WHERE   a.department_id = b.department_id

AND     a.salary > b.salavg;

单列子查询表达式

  • Oracle8i只在下列情况下可以使用, 例如:
    • SELECT 语句 (FROM 和WHERE 子句)
    • INSERT 语句中的VALUES列表中
  • Oracle9i中单列子查询表达式可在下列情况下使用:
    • DECODE  和 CASE
    • SELECT 中除 GROUP BY 子句以外的所有子句中
  1. 显式员工的employee_id,last_namelocation。其中,若员工department_idlocation_id1800department_id相同,则location’Canada’,其余则为’USA’

SELECT employee_id, last_name,

       (CASE department_id

        WHEN  (SELECT department_id FROM departments

WHERE location_id = 1800)

        THEN ‘Canada’ ELSE ‘USA’ END) location

FROM   employees;

  1. 查询员工的employee_id,last_name,要求按照员工的department_name排序

SELECT   employee_id, last_name

FROM     employees e

ORDER BY (SELECT department_name

  FROM departments d

  WHERE e.department_id = d.department_id);

相关子查询

5.查询员工中工资大于本部门平均工资的员工的last_name,

salary和其department_id

SELECT last_name, salary, department_id

FROM   employees outer

WHERE  salary >

(SELECT AVG(salary)

 FROM   employees

 WHERE  department_id =  

        outer.department_id) ;

  1. employees表中employee_idjob_history表中employee_id相同的数目不小于2,输出这些相同id的员工的employee_id,last_name和其job_id

SELECT e.employee_id, last_name,e.job_id

FROM   employees e

WHERE  2 <= (SELECT COUNT(*)

             FROM   job_history

             WHERE  employee_id = e.employee_id);

EXISTS 操作符

  • EXISTS 操作符检查在子查询中是否存在满足条件的行
  • 如果在子查询中存在满足条件的行:
    • 不在子查询中继续查找
    • 条件返回 TRUE
  1. 查询公司管理者的employee_id,last_name,job_id,

department_id信息

SELECT employee_id, last_name, job_id, department_id

FROM   employees outer

WHERE EXISTS ( SELECT ‘X’

                 FROM   employees

                 WHERE  manager_id =

                        outer.employee_id);

  1. 查询departments表中,不存在于employees表中的部门的department_iddepartment_name

SELECT department_id, department_name

FROM departments d

WHERE NOT EXISTS (SELECT ‘X’

                  FROM   employees

                  WHERE  department_id

                         = d.department_id);

–关于数据更新

9.修改表employees,添加department_name列,赋予department_id相应的部门名称。 

ALTER TABLE employees

ADD(department_name VARCHAR2(14));

UPDATE employees e

SET    department_name =

              (SELECT department_name

       FROM   departments d

       WHERE  e.department_id = d.department_id);

–关于数据删除

10.删除表employees中,其与emp_history表皆有的数据

DELETE FROM employees E

WHERE employee_id in  

           (SELECT employee_id

            FROM   emp_history

            WHERE  employee_id = E.employee_id);

WITH 子句

  1. 查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息

WITH
dept_costs  AS (

   SELECT  d.department_name, SUM(e.salary) AS dept_total

   FROM    employees e, departments d

   WHERE   e.department_id = d.department_id

   GROUP BY d.department_name),

avg_cost    AS (

   SELECT SUM(dept_total)/COUNT(*) AS dept_avg

   FROM   dept_costs)

SELECT *

FROM   dept_costs

WHERE  dept_total >

        (SELECT dept_avg

         FROM avg_cost)

ORDER BY department_name;

附加题目:

12.查询员工的last_name, department_id, salary.其中员工的salary,department_id与有奖金的任何一个员工的salary,

department_id相同即可

select last_name, department_id, salary

from employees 

where (salary,department_id) in (

select salary,department_id

from employees

where commission_pct is not null

                                )

13.选择工资大于所有JOB_ID = ‘SA_MAN’的员工的工资的员工的last_name, job_id, salary

select last_name, job_id, salary

from employees

where salary > all(

                  select salary

                  from employees

                  where job_id = ‘SA_MAN’

                  )

14.选择所有没有管理者的员工的last_name

select last_name

from employees e1

where not exists (

              select ‘A’

              from employees e2

              where e1.manager_id = e2.employee_id

              )

  1. 查询10,50,20号部门的job_id,department_id并且department_id按10,50,20的顺序排列

Column dummy noprint;

select job_id , department_id ,1 dummy

from employees

where department_id = 10

union

select job_id , department_id , 2

from employees

where department_id = 50

union

select job_id , department_id , 3

from employees

where department_id= 20

order by 3


上一篇:
下一篇:
关于尚硅谷
教育理念
名师团队
学员心声
资源下载
视频下载
资料下载
工具下载
加入我们
招聘岗位
岗位介绍
招贤纳师
联系我们
电话:010-56253825
邮箱:info@atguigu.com
地址:北京市昌平区宏福科技园综合楼6层(北京校区)

 深圳市宝安区西部硅谷大厦B座C区一层(深圳校区)

上海市松江区谷阳北路166号大江商厦6层(上海校区)