SQL语句面试题目：一般查询和高级子查询

忘是亡心i 2022-06-07 21:13 599阅读 0赞

# 几个表 #

**employees 表：**

> EMPLOYEE\_ID NUMBER(6)
> 
> FIRST\_NAME VARCHAR2(20)
> 
> LAST\_NAME VARCHAR2(25)
> 
> EMAIL VARCHAR2(25)
> 
> PHONE\_NUMBER VARCHAR2(20)
> 
> HIRE\_DATE DATE
> 
> JOB\_ID VARCHAR2(10)
> 
> SALARY NUMBER(8,2)
> 
> COMMISSION\_PCT NUMBER(2,2)
> 
> MANAGER\_ID NUMBER(6)
> 
> DEPARTMENT\_ID NUMBER(4)

**jobs 表：**

> JOB\_ID VARCHAR2(10)
> 
> JOB\_TITLE VARCHAR2(35)
> 
> MIN\_SALARY NUMBER(6)
> 
> MAX\_SALARY NUMBER(6)

**departments 表：**

> DEPARTMENT\_ID NUMBER(4)
> 
> DEPARTMENT\_NAME VARCHAR2(30)
> 
> MANAGER\_ID NUMBER(6)
> 
> LOCATION\_ID NUMBER(4)

**locations 表：**

<table style="width:541px;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:bottom;width:90px;"> &nbsp; </td> 
 <td colspan="2" style="vertical-align:bottom;width:169px;"> LOCATION_ID </td> 
 <td style="vertical-align:bottom;width:37px;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:242px;"> NUMBER(4) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:90px;"> &nbsp; </td> 
 <td colspan="3" style="vertical-align:bottom;width:206px;"> STREET_ADDRESS </td> 
 <td style="vertical-align:bottom;width:242px;"> VARCHAR2(40) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:90px;"> &nbsp; </td> 
 <td colspan="3" style="vertical-align:bottom;width:206px;"> POSTAL_CODE </td> 
 <td style="vertical-align:bottom;width:242px;"> VARCHAR2(12) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:90px;"> &nbsp; </td> 
 <td colspan="3" style="vertical-align:bottom;width:206px;"> CITY </td> 
 <td style="vertical-align:bottom;width:242px;"> VARCHAR2(30) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:90px;"> &nbsp; </td> 
 <td colspan="3" style="vertical-align:bottom;width:206px;"> STATE_PROVINCE </td> 
 <td style="vertical-align:bottom;width:242px;"> VARCHAR2(25) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:90px;"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; </td> 
 <td colspan="3" style="vertical-align:bottom;width:206px;"> COUNTRY_ID </td> 
 <td style="vertical-align:bottom;width:242px;"> CHAR(2) </td> 
 </tr> 
 </tbody> 
</table>

**job\_grades 表：**

> GRADE\_LEVEL VARCHAR2(3)
> 
> LOWEST\_SAL NUMBER
> 
> HIGHEST\_SAL NUMBER

# 1 一般查询 #

**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

## 2. 查询出 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' 
    )

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

SELECT department_id, avg(salary) 
    FROM employees e 
    GROUP BY department_id 
    HAVING avg(salary) > 8000

## 4. 查询工资最低的员工信息:last\_name, salary ##

SELECT last_name, salary 
    FROM employees 
    WHERE salary = ( SELECT min(salary)  FROM employees  )

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

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 )
    )

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

select d.*, (select avg(salary) 
                          from employees 
                          where department_id = d.department_id) 
    from departments d 
    where d.department_id = ( 
           SELECTdepartment_id FROM employees 
           GROUP BY department_id HAVING avg(salary) =( 
                    SELECT min(avg(salary)) FROM employees 
                   GROUP BY department_id ) 
            )

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

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

SELECT max(avg(salary))
    FROM employees 
    GROUP BY job_id

2). 查询出平均工资等于 1) 的 job\_id

SELECT job_id 
    FROM employees 
    GROUP BYjob_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 ) 
              )

## 8．查询平均工资高于公司平均工资的部门有哪些? ##

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

SELECT avg(salary) 
    FROM employees

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

SELECT department_id 
    FROM employees
    GROUP BY department_id 
    HAVING avg(salary) >   ( SELECT avg(salary)  FROMemployees)

## 9.查询出公司中所有 manager的详细信息.（IN关键字） ##

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 
    )

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

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 是多少: 各个部门中最高工资等于的那个部门的 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   ) 
    )

## 11.查询平均工资最高的部门的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

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 ) 
                    ) 
            )

## 12.查询 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)
            FROMemployees 
            WHERE to_char(hire_date, 'yyyy') = '1999' )

## 13.返回其它部门中比 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';

**主要学会问题的拆分，由内而外 将大问题逐个拆分为小问题。**

# 2高级子查询 #

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

**书写多列子查询**

**在 FROM 子句中使用子查询**

**在 SQL 中使用单列子查询**

**书写相关子查询**

**使用 EXISTS 和 NOT EXISTS 操作符**

**使用子查询更新和删除数据**

**使用 WITH 子句**

## 1.多列子查询（不成对比较& 成对比较） ##

**1.查询与 141 号或 174 号员工的 manager\_id 和 department\_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 NOTIN(174,141);

**\[方式二\]**

SELECT  employee_id,manager_id, department_id 
    FROM  employees 
    WHERE     (manager_id,department_id) IN (
                                SELECTmanager_id, department_id 
                                FROM  employees 
                                WHERE     employee_id IN (141,174)) 
                                AND   employee_id NOT IN (141,174);

# 2.在FROM 子句中使用子查询 #

**2.返回比本部门平均工资高的员工的 last\_name, department\_id, salary 及平均工资**

**\[方式一\]**

select last_name,department_id,salary,
              (select avg(salary) 
               from employees e3
              where e1.department_id = e3.department_id
               groupby 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 BYdepartment_id) b 
    WHERE a.department_id =b.department_id 
                     AND     a.salary> b.salavg

# 3.单列子查询表达式 #

• Oracle8*i*只在下列情况下可以使用, 例如:

– SELECT 语句 (FROM 和 WHERE 子句)

– INSERT 语句中的 VALUES 列表中

• Oracle9*i*中单列子查询表达式可在下列情况下使用:

– DECODE 和 CASE

– SELECT 中除 GROUP BY 子句以外的所有子句中

**3. 显示员工的employee\_id,last\_name 和location。其中，若员工****department\_id 与 location\_id 为1800 的 department\_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;

4. **查 询 员 工 的 employee\_id,last\_name, 要 求 按 照 员 工 的****department\_name 排序**

SELECT  employee_id,last_name 
    FROM      employeese 
    ORDER BY (SELECT department_name 
              FROM departments d 
              WHERE e.department_id = d.department_id);

**单列、from、orderby等都可以进行子查询**

## 4.相关子查询 ##

**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);

**6.若 employees 表中employee\_id 与 job\_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);

# 5.EXISTS 操作符 #

> • EXISTS 操作符检查在子查询中是否存在满足条件的行
> 
> • **如果在子查询中存在满足条件的行:**
> 
> – 不在子查询中继续查找
> 
> – 条件返回 TRUE

7. **查询公司管理者的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);

**8. 查询 departments 表中，不存在于 employees 表中的部门的****department\_id 和 department\_name**

SELECT department_id,department_name 
    FROM departments d 
    WHERE NOT EXISTS (
                      SELECT'X' 
                      FROM  employees 
                      WHERE department_id =d.department_id);

# 6.关于数据更新 #

**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);

# 7.关于数据删除 #

**10.删除表 employees 中，其与emp\_history 表皆有的数据**

DELETE FROM employees E 
    WHERE employee_id in(
                  SELECT employee_id 
                  FROM  emp_history
                  WHERE  employee_id =E.employee_id);

## 8.WITH 子句 ##

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

WITH 
    dept_costs AS ( 
         SELECT     d.department_name,SUM(e.salary) AS dept_total 
         FROM          employeese, departments d 
        WHERE  e.department_id= d.department_id 
        GROUPBY 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;

# 3 附加题目 #

**NOTNULL/ALL/EXISTS/NOT EXISTS**

**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( 
         selectsalary 
         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 
    )

**15. 查询 10，50，20 号部门的job\_id，department\_id 并且**

**department\_id 按 10，50，20 的顺序排列(union)**

Column dummy noprint; 
       select job_id , department_id ,1dummy 
       from employees 
       wheredepartment_id = 10 
    union 
         select job_id , department_id , 2 
         from employees 
         wheredepartment_id = 50 
    union 
         select job_id , department_id , 3 
          from employees 
         wheredepartment_id= 20 
    order by 3

# 4 总结 #

## 1 Having 和Where的区别 ##

having 和where 都是用来筛选用的 ，但是**having** 是筛选**组，** 而**where**是筛选**记录。**他们有各自的区别：

1》当分组筛选的时候用having

> \-----------------------------------------------------
> 
> 用having就一定要和group by连用
> 
> 用group by不一有having （它只是一个筛选条件用的）
> 
> \-------------------------------------------------------

2》其它情况用where

3》where和having的执行级别不同  
在查询过程中聚合语句(sum,[min][],max,avg,count)要比having子句优先执行。

而where子句在查询过程中优先于聚合语句(sum,[min][],max,avg,count)。

having就是来弥补where在[分组数据][Link 1]判断时的不足，因为where执行优先级别要快于聚合语句。

## 2 With子句 ##

**with**子句-----给子查询定义一个别名，可以通过别名来**引用子查询**，实现了**一次定义多次使用**。

WITH子句的格式为：  **WITH 别名 AS（子查询）**

定义好别名就可以在sql中通过别名来引用子查询

[min]: https://www.baidu.com/s?wd=min&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1YLP1RkPWT1ujbsuhFWrjfs0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EnWmzP16snHRd
[Link 1]: https://www.baidu.com/s?wd=%E5%88%86%E7%BB%84%E6%95%B0%E6%8D%AE&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1YLP1RkPWT1ujbsuhFWrjfs0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EnWmzP16snHRd