51Testing软件测试论坛

标题: SQL语句面试题目：一般查询和高级子查询 [打印本页]
作者: 悠悠小仙仙    时间: 2017-9-21 16:31
标题: SQL语句面试题目：一般查询和高级子查询
几个表
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 表：


LOCATION_ID
NUMBER(4)
STREET_ADDRESS
VARCHAR2(40)
POSTAL_CODE
VARCHAR2(12)
CITY
VARCHAR2(30)
STATE_PROVINCE
VARCHAR2(25)
            
COUNTRY_ID
CHAR(2)

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 天入职的员工的信息.
  1. [plain] view plain copy
  2. select last_name, hire_date  
  3. from employees  
  4. where hire_date = last_day(hire_date) – 1  
复制代码
2. 查询出 last_name 为 'Chen' 的 manager 的信息.
1). 通过两条 sql 查询:
  1. select manager_id  
  2. from employees  
  3. where lower(last_name) = 'chen' -- 返回的结果为 108   
  4.    
  5. select *   
  6. from employees   
  7. where employee_id = 108   
复制代码
2). 通过一条 sql 查询(自连接):  
  1. select m.*   
  2. from employees e, employees m   
  3. where e.manager_id = m.employee_id and e.last_name= 'Chen'
复制代码
3). 通过一条 sql 查询(子查询):
  1. select *   
  2. from employees   
  3. where employee_id = (   
  4. select manager_id   
  5. from employees   
  6. where last_name = 'Chen'   
  7. )  
复制代码
查询平均工资高于 8000 的部门 id 和它的平均工资.
  1. SELECT department_id, avg(salary)   
  2. FROM employees e   
  3. GROUP BY department_id   
  4. HAVING avg(salary) > 8000  
复制代码
4. 查询工资最低的员工信息:last_name, salary
  1. SELECT last_name, salary   
  2. FROM employees   
  3. WHERE salary = ( SELECT min(salary)  FROM employees  )
复制代码
5.查询平均工资最低的部门信息
  1. SELECT *   
  2. FROM departments   
  3. WHERE department_id =(  
  4.     SELECT department_id   
  5.     FROM employees  
  6.     GROUP BY department_id  
  7.     HAVING avg(salary) =(  
  8.      SELECT min(avg(salary))  
  9.     FROM employees  
  10.     GROUP BY department_id )  
  11. )  
复制代码
6.查询平均工资最低的部门信息和该部门的平均工资
  1. select d.*, (select avg(salary)   
  2.                       from employees   
  3.                       where department_id = d.department_id)   
  4. from departments d   
  5. where d.department_id = (   
  6.        SELECTdepartment_id FROM employees   
  7.        GROUP BY department_id HAVING avg(salary) =(   
  8.                 SELECT min(avg(salary)) FROM employees   
  9.                GROUP BY department_id )   
  10.         )   
复制代码
7.查询平均工资最高的 job 信息1). 按 job_id 分组, 查询最高的平均工资
  1. SELECT max(avg(salary))  
  2. FROM employees   
  3. GROUP BY job_id   
复制代码
2). 查询出平均工资等于 1) 的 job_id
  1. SELECT job_id   
  2. FROM employees   
  3. GROUP BYjob_id   
  4. HAVING avg(salary) = (   
  5.            SELECT max(avg(salary))   
  6.            FROM employees   
  7.            GROUP BY job_id   
  8.       )   
复制代码
3). 查询出 2) 对应的 job 信息
  1. SELECT *   
  2. FROM jobs  
  3. WHERE job_id = (   
  4.           SELECT job_id   
  5.           FROM employees   
  6.           GROUP BY job_id   
  7.           HAVING avg(salary) = (   
  8.                     SELECT max(avg(salary))   
  9.                     FROM employees   
  10.                     GROUP BY job_id )   
  11.           )  
复制代码
8．查询平均工资高于公司平均工资的部门有哪些?  
1). 查询出公司的平均工资
  1. SELECT avg(salary)   
  2. FROM employees   
复制代码
2). 查询平均工资高于 1) 的部门 ID
  1. SELECT department_id   
  2. FROM employees  
  3. GROUP BY department_id   
  4. HAVING avg(salary) >   ( SELECT avg(salary)  FROMemployees)
复制代码
9.查询出公司中所有 manager的详细信息.（IN关键字） 1). 查询出所有的 manager_id
  1. SELECT   distinct  manager_id  FROM  employeess   
复制代码


2). 查询出 employee_id 为 1) 查询结果的那些员工的信息
  1. SELECT employee_id, last_name   
  2. FROM employees   
  3. WHERE employee_id  in  (   
  4.            SELECT distinct manager_id   
  5.            FROM employees   
  6. )   
复制代码
10.各个部门中 最高工资中最低的那个部门的最低工资是多少
1). 查询出各个部门的最高工资
  1. SELECT max(salary)   
  2. FROM employees   
  3. GROUP BY department_id   
复制代码
2). 查询出 1) 对应的查询结果的最低值: 各个部门中最低的最高工资(无法查询对应的 department_id)
  1. SELECT min(max(salary))   
  2. FROM employees   
  3. GROUP BY department_id   
复制代码
3). 查询出 2) 所对应的部门 id 是多少: 各个部门中最高工资等于的那个部门的 id
  1. SELECT department_id   
  2. FROM employees   
  3. GROUP BY department_id   
  4. HAVING max(salary) =(   
  5.        SELECT min(max(salary))   
  6.        FROM employees   
  7.        GROUP BY department_id   
  8.       )   
复制代码
4). 查询出 3) 所在部门的最低工资
  1. SELECT min(salary)  
  2. FROM employees   
  3. WHERE department_id = (   
  4.          SELECT department_id   
  5.          FROM employees   
  6.          GROUP BY department_id HAVING max(salary) =(   
  7.                    SELECT min(max(salary))   
  8.                    FROM employees   
  9.                    GROUP BY department_id   )   
  10. )  
复制代码






作者: 悠悠小仙仙    时间: 2017-9-21 16:32
11.查询平均工资最高的部门的manager 的详细信息: last_name,department_id, email, salary

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

  1. SELECT max(avg(salary))
  2. FROM employees
  3. GROUP BY department_id
复制代码

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

  1. SELECT department_id
  2. FROM employees
  3. GROUP BY department_id
  4. HAVING avg(salary) = (
  5. SELECT max(avg(salary))
  6. FROM employees
  7. GROUP BY department_id
  8. )
复制代码

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

  1. SELECT manager_id
  2. FROM departments
  3. WHERE department_id = (
  4. SELECT department_id
  5. FROM employees
  6. GROUP BY department_id
  7. HAVING avg(salary) = (
  8. SELECT max(avg(salary))
  9. FROM employees
  10. GROUP BY department_id )
  11. )
复制代码

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

  1. SELECT last_name, department_id, email,salary
  2. FROM employees
  3. WHERE employee_id = (
  4. SELECT manager_id
  5. FROM departments
  6. WHERE department_id = (
  7. SELECT department_id
  8. FROM employees
  9. GROUP BY department_id
  10. HAVING avg(salary) = (
  11. SELECT max(avg(salary))
  12. FROM employees
  13. GROUP BY department_id )
  14. )
  15. )
复制代码
12.查询 1999 年来公司的人中所有员工的最高工资的那个员工的信息.

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

  1. SELECT salary
  2. FROM employees
  3. WHERE to_char(hire_date,'yyyy') = '1999'
复制代码

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

  1. SELECT max(salary)
  2. FROM employees
  3. WHERE to_char(hire_date, 'yyyy') = '1999'
复制代码

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

  1. SELECT *
  2. FROM employees
  3. WHERE to_char(hire_date, 'yyyy') = '1999' AND salary = (
  4. SELECT max(salary)
  5. FROMemployees
  6. WHERE to_char(hire_date, 'yyyy') = '1999' )
复制代码

13.返回其它部门中比 job_id 为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及 salary
  1. SELECT employee_id, last_name, job_id,salary
  2. FROM employees
  3. WHERE salary< ALL (SELECT salary
  4. FROM employees
  5. WHERE job_id = 'IT_PROG')
  6. 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

[方式一]
  1. SELECT employee_id,manager_id, department_id
  2. FROM employees
  3. WHERE manager_id IN (
  4. SELECT manager_id
  5. FROM employees
  6. WHERE employee_id IN(174,141) )
  7. AND department_id IN (
  8. SELECT department_id
  9. FROM employees
  10. WHERE employee_id IN(174,141))
  11. AND employee_id NOTIN(174,141);
复制代码
[方式二]
  1. SELECT employee_id,manager_id, department_id
  2. FROM employees
  3. WHERE (manager_id,department_id) IN (
  4. SELECTmanager_id, department_id
  5. FROM employees
  6. WHERE employee_id IN (141,174))
  7. AND employee_id NOT IN (141,174);
复制代码



作者: 悠悠小仙仙    时间: 2017-9-21 16:32
2.在FROM 子句中使用子查询
2.返回比本部门平均工资高的员工的 last_name, department_id, salary 及平均工资

[方式一]
  1. select last_name,department_id,salary,
  2. (select avg(salary)
  3. from employees e3
  4. where e1.department_id = e3.department_id
  5. groupby department_id ) avg_salary
  6. from employees e1
  7. where salary >(select avg(salary)
  8. from employees e2
  9. where e1.department_id = e2.department_id
  10. group by department_id )
复制代码
[方式二]
  1. SELECT a.last_name,a.salary, a.department_id, b.salavg
  2. FROM employees a, (SELECT department_id,AVG(salary) salavg
  3. FROM employees
  4. GROUP BYdepartment_id) b
  5. WHERE a.department_id =b.department_id
  6. AND a.salary> b.salavg
复制代码
3.单列子查询表达式
•     Oracle8i只在下列情况下可以使用, 例如:
– SELECT 语句 (FROM 和 WHERE 子句)
– INSERT 语句中的 VALUES 列表中
•     Oracle9i中单列子查询表达式可在下列情况下使用:
– DECODE  和 CASE
– SELECT 中除 GROUP BY 子句以外的所有子句中

3.  显示员工的employee_id,last_name 和location。其中，若员工department_id 与 location_id 为1800 的 department_id 相同，则location 为’Canada’,其余则为’USA’。
  1. SELECT employee_id, last_name,
  2. ( CASE department_id
  3. WHEN ( SELECT department_id FROM departments
  4. WHERE location_id = 1800)
  5. THEN 'Canada' ELSE 'USA' END) location
  6. FROM employees;
复制代码

4.    查 询 员 工 的  employee_id,last_name, 要 求 按 照 员 工 的department_name 排序
  1. SELECT employee_id,last_name
  2. FROM employeese
  3. ORDER BY (SELECT department_name
  4. FROM departments d
  5. WHERE e.department_id = d.department_id);
复制代码
4.相关子查询
5.查询员工中工资大于本部门平均工资的员工的 last_name,salary 和其 department_id
  1. SELECT last_name, salary, department_id
  2. FROM employees outer
  3. WHERE salary > (SELECT AVG(salary)
  4. FROM employees
  5. WHERE department_id=outer.department_id);
复制代码
6.若 employees 表中employee_id 与 job_history 表中employee_id相同的数目不小于 2，输出这些相同 id 的员工的employee_id,last_name和其 job_id
  1. SELECT e.employee_id, last_name,e.job_id
  2. FROM employees e
  3. WHERE 2<= (SELECT COUNT(*)
  4. FROM job_history
  5. WHERE employee_id =e.employee_id);
复制代码
5.EXISTS 操作符
•     EXISTS 操作符检查在子查询中是否存在满足条件的行
•     如果在子查询中存在满足条件的行:
– 不在子查询中继续查找
– 条件返回 TRUE
7.  查询公司管理者的employee_id,last_name,job_id,department_id 信息
  1. SELECT employee_id, last_name, job_id, department_id
  2. FROM employees outer
  3. WHERE EXISTS ( SELECT 'X'
  4. FROM employees
  5. WHERE manager_id= outer.employee_id);
复制代码

8.   查询  departments 表中，不存在于  employees 表中的部门的department_id 和 department_name
  1. SELECT department_id,department_name
  2. FROM departments d
  3. WHERE NOT EXISTS (
  4. SELECT'X'
  5. FROM employees
  6. WHERE department_id =d.department_id);
复制代码
6.关于数据更新
9.修改表 employees,添加department_name 列，赋予department_id相应的部门名称。
  1. ALTER TABLE employees ADD(department_name VARCHAR2(14));

  3. UPDATE employees e
  4. SET department_name=
  5. ( SELECT department_name
  6. FROM departments d
  7. WHERE e.department_id= d.department_id);
复制代码
7.关于数据删除
10.删除表 employees 中，其与emp_history 表皆有的数据
  1. DELETE FROM employees E
  2. WHERE employee_id in(
  3. SELECT employee_id
  4. FROM emp_history
  5. WHERE employee_id =E.employee_id);
复制代码
8.WITH 子句
11.  查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息  
  1. WITH
  2. dept_costs AS (
  3. SELECT d.department_name,SUM(e.salary) AS dept_total
  4. FROM employeese, departments d
  5. WHERE e.department_id= d.department_id
  6. GROUPBY d.department_name),
  7. avg_cost AS(
  8. SELECT SUM(dept_total) / COUNT(*) AS dept_avg
  9. FROM dept_costs)

  11. SELECT *
  12. FROM dept_costs
  13. WHERE dept_total> (SELECT dept_avg FROM avg_cost)
  14. ORDER BY department_name;
复制代码
3 附加题目
NOTNULL/ALL/EXISTS/NOT EXISTS

12. 查询员工的  last_name,  department_id,  salary. 其中员工的salary,department_id 与有奖金的任何一个员工的 salary,department_id 相同即可
  1. select last_name, department_id, salary
  2. from employees
  3. where(salary,department_id) in (
  4. select salary,department_id
  5. from employees
  6. where commission_pct is not null
  7. )
复制代码
13.选择工资大于所有  JOB_ID =  'SA_MAN' 的员工的工资的员工的last_name,job_id, salary  
  1. select last_name, job_id, salary
  2. from employees
  3. where salary > all(
  4. selectsalary
  5. from employees
  6. where job_id = 'SA_MAN' )
复制代码
14.选择所有没有管理者的员工的 last_name  
  1. select last_name
  2. from employees e1
  3. where not exists (
  4. select 'A'
  5. from employees e2
  6. where e1.manager_id = e2.employee_id
  7. )
复制代码
15. 查询 10，50，20 号部门的job_id，department_id 并且

department_id 按 10，50，20 的顺序排列(union)
  1. Column dummy noprint;
  2. select job_id , department_id ,1dummy
  3. from employees
  4. wheredepartment_id = 10
  5. union
  6. select job_id , department_id , 2
  7. from employees
  8. wheredepartment_id = 50
  9. union
  10. select job_id , department_id , 3
  11. from employees
  12. wheredepartment_id= 20
  13. 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在分组数据判断时的不足，因为where执行优先级别要快于聚合语句。

2 With子句
with子句-----给子查询定义一个别名，可以通过别名来引用子查询，实现了一次定义多次使用。
WITH子句的格式为：   WITH  别名 AS（子查询）
定义好别名就可以在sql中通过别名来引用子查询

作者: 无名tuzi    时间: 2017-9-21 16:44
为楼主打call！！感谢！！
作者: 醉酒的熊猫    时间: 2017-9-21 21:20
楼主，有没的大量的相关数据啊！一个个的去创建好麻烦哦！想咨询下你有没得，可以减少创建数据的时间，谢谢了
作者: jingzizx    时间: 2017-9-22 08:07
作者: 炎帝    时间: 2017-9-22 13:56
插眼
作者: zero513    时间: 2017-10-12 16:41
谢谢分享
作者: zhuzaishijie0    时间: 2017-12-9 16:50
醉酒的熊猫 发表于 2017-9-21 21:20
楼主，有没的大量的相关数据啊！一个个的去创建好麻烦哦！想咨询下你有没得，可以减少创建数据的时间，谢谢 ...

想偷懒就直说
作者: zhuzaishijie0    时间: 2017-12-9 16:50
无名tuzi 发表于 2017-9-21 16:44
为楼主打call！！感谢！！

全面屏么？
作者: 小小小提姆    时间: 2017-12-27 17:00
顶一个



欢迎光临 51Testing软件测试论坛 (http://bbs.51testing.com/) Powered by Discuz! X3.2