软件测试论坛 › [软件测试新手上路] › SQL语句面试题目：一般查询和高级子查询

悠悠小仙仙 发表于 2017-9-21 16:31:45

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_ADDRESSVARCHAR2(40)
POSTAL_CODEVARCHAR2(12)
CITYVARCHAR2(30)
STATE_PROVINCEVARCHAR2(25)
            COUNTRY_IDCHAR(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 天入职的员工的信息. view plain copy
select last_name, hire_date
from employees
where hire_date = last_day(hire_date) – 12. 查询出 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) > 80004. 查询工资最低的员工信息: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   distinctmanager_idFROMemployeess   

2). 查询出 employee_id 为 1) 查询结果的那些员工的信息
SELECT employee_id, last_name   
FROM employees   
WHERE employee_idin(   
         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   )   
)

悠悠小仙仙 发表于 2017-9-21 16:32:11

11.查询平均工资最高的部门的manager 的详细信息: last_name,department_id, email, salary
1). 各个部门中, 查询平均工资最高的平均工资是多少 SELECT max(avg(salary))
FROM employees
GROUP BY department_id2). 各个部门中, 平均工资等于 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 )
)
)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);

悠悠小仙仙 发表于 2017-9-21 16:32:25

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.salavg3.单列子查询表达式 •   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’。 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);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, salaryselect last_name, job_id, salary
from employees
where salary > all(
selectsalary
from employees
where job_id = 'SA_MAN' )14.选择所有没有管理者的员工的 last_nameselect 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 34 总结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:17

为楼主打call！！感谢！！

醉酒的熊猫 发表于 2017-9-21 21:20:22

楼主，有没的大量的相关数据啊！一个个的去创建好麻烦哦！想咨询下你有没得，可以减少创建数据的时间，谢谢了

jingzizx 发表于 2017-9-22 08:07:04

:lol

炎帝 发表于 2017-9-22 13:56:21

插眼

zero513 发表于 2017-10-12 16:41:26

谢谢分享

zhuzaishijie0 发表于 2017-12-9 16:50:10

醉酒的熊猫 发表于 2017-9-21 21:20
楼主，有没的大量的相关数据啊！一个个的去创建好麻烦哦！想咨询下你有没得，可以减少创建数据的时间，谢谢 ...

想偷懒就直说

zhuzaishijie0 发表于 2017-12-9 16:50:32

无名tuzi 发表于 2017-9-21 16:44
为楼主打call！！感谢！！

全面屏么？

小小小提姆 发表于 2017-12-27 17:00:15

顶一个

查看完整版本: SQL语句面试题目：一般查询和高级子查询