挑战SQL语句面试题

目录

写在文章前

题目及解答

个人对题目的思考

---

写在文章前

首先声明本人不是技术大佬、也不是培训老师，只是在工作中写过两年PL/SQL语句，有一定的数据处理经验，也是一个学习者。

在书上看到一份某企业招聘数据库工程师笔试题，其中有一个大题是SQL应用，就尝试做了一下。并且到DBMS中去验证了一番。这里将题目与作者写的答案分享出来，供学习数据库的朋友们参考、指正！ 

题目及解答

使用SCOTT/TIGER用户下的EMP和DEPT表完成下列练习，表结构说明如下：

EMP员工表（EMPNO员工编号、ENAME员工姓名、JOB工作、MGR上级编号、HIREDATE受雇日期、SAL薪金、COMM佣金、DEPTNO部门编号）

DEPT部门表（DEPRNO部门编号、DNAME部门名称、LOC地点）

工资=薪金 + 佣金

注： SCOTT/TIGER是Oracle数据库管理系统自带的数据库，所以下面的语句都是基于Oracle的。

---

1、列出所有至少有一个员工的部门。

答：第一种写法：select dname from dept where deptno in (select deptno from emp);

       第二种写法：select distinct dp.dname from emp e left join dept dp on e.deptno=dp.deptno where dp.deptno is not null;

SQL> select dname from dept where deptno in (select deptno from emp);
DNAME
--------------
ACCOUNTING
RESEARCH
SALES

SQL> select distinct dp.dname from emp e left join dept dp on e.deptno=dp.deptno where dp.deptno is not null;
DNAME
--------------
ACCOUNTING
RESEARCH
SALES

2、列出薪金比SMITH多的所有员工

答：select ename from emp where sal >(select max(sal) from emp where ename='SMITH');

SQL> select ename from emp where sal >(select max(sal) from emp where ename='SMITH');
ENAME
----------
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
13 rows selected

3、列出所有员工及其上级的姓名

答：select e.ename,mg.ename as mgr from emp e left join emp mg on e.mgr = mg.empno;

SQL> select e.ename,mg.ename as mgr from emp e left join emp mg on e.mgr = mg.empno;
ENAME      MGR
---------- ----------
FORD       JONES
SCOTT      JONES
JAMES      BLAKE
TURNER     BLAKE
MARTIN     BLAKE
WARD       BLAKE
ALLEN      BLAKE
MILLER     CLARK
ADAMS      SCOTT
CLARK      KING
BLAKE      KING
JONES      KING
SMITH      FORD
KING       
14 rows selected

 4、列出受雇日期早于其直属上级的员工的所有员工。

答：select e.ename from emp e left join emp mg on e.mgr = mg.ename where e.hiredate< mg.hiredate;

SQL> select e.ename from emp e left join emp mg on e.mgr = mg.empno where e.hiredate< mg.hiredate;
ENAME
----------
ALLEN
WARD
BLAKE
JONES
CLARK
SMITH
6 rows selected

 5、列出部门名称和这些部门的员工信息，同时列出没有员工的部门。

答：select dp.dname,e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm from dept dp left join emp e on dp.deptno=e.deptno;

SQL> select dp.dname,e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm from dept dp left join emp e on dp.deptno=e.deptno;
DNAME          EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM
-------------- ----- ---------- --------- ----- ----------- --------- ---------
ACCOUNTING      7782 CLARK      MANAGER    7839 1981-6-9      2450.00 
ACCOUNTING      7839 KING       PRESIDENT       1981-11-17    5000.00 
ACCOUNTING      7934 MILLER     CLERK      7782 1982-1-23     1300.00 
RESEARCH        7566 JONES      MANAGER    7839 1981-4-2      2975.00 
RESEARCH        7902 FORD       ANALYST    7566 1981-12-3     3000.00 
RESEARCH        7876 ADAMS      CLERK      7788 1987-5-23     1100.00 
RESEARCH        7369 SMITH      CLERK      7902 1980-12-17     800.00 
RESEARCH        7788 SCOTT      ANALYST    7566 1987-4-19     3000.00 
SALES           7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00
SALES           7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00
SALES           7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00
SALES           7900 JAMES      CLERK      7698 1981-12-3      950.00 
SALES           7698 BLAKE      MANAGER    7839 1981-5-1      2850.00 
SALES           7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00
OPERATIONS                                                            
15 rows selected

 6、列出所有工作为CLERK办事员的姓名及其部门名称。

答：select dp.dname,e.ename from dept dp right join emp e on dp.deptno=e.deptno where e.job='CLERK';

SQL> select e.ename,dp.dname from dept dp right join emp e on dp.deptno=e.deptno where e.job='CLERK';
ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ADAMS      RESEARCH
JAMES      SALES
MILLER     ACCOUNTING

 7、列出薪金大于1500的各种工作。

答：select distinct job from emp where sal>1500;

SQL> select distinct job from emp where sal>1500;
JOB
---------
SALESMAN
PRESIDENT
MANAGER
ANALYST

 8、列出在部门SALES销售部工作的员工姓名，假定不知道销售部的部门编号

答：第一种写法：select * from emp where deptno in (select deptno from dept where dname='SALES');

第二种写法：select e.ename from emp e left join dept dp on e.deptno=dp.deptno where dp.dname='SALES';

SQL> select e.ename from join emp e  left dept dp on e.deptno=dp.deptno where dp.dname='SALES'
  2  /
ENAME
----------
WARD
TURNER
ALLEN
JAMES
BLAKE
MARTIN
6 rows selected

SQL> select e.ename from emp e left join dept dp on e.deptno=dp.deptno where dp.dname='SALES';
ENAME
----------
WARD
TURNER
ALLEN
JAMES
BLAKE
MARTIN
6 rows selected

 9、列出薪金高于公司平均薪金的员工。

答：select ename from emp where sal > (select avg(sal) from emp);

SQL> select ename from emp where sal > (select avg(sal) from emp);
ENAME
----------
JONES
BLAKE
CLARK
SCOTT
KING
FORD
6 rows selected

10、列出于员工SCOTT从事相同工作的员工。

答：select e.ename from emp e  where e.job  in (select job from emp where ename='SCOTT');

SQL> select e.ename from emp e where e.job in (select job from emp where ename='SCOTT');
ENAME
----------
FORD
SCOTT

11、列出薪金等于部门编号为30的员工的所有员工的姓名和薪金。

答：第一种： select ename,sal from emp where sal in(select sal from emp where deptno=30);

       第二种：select distinct e.ename,e.sal from emp e left join emp e30 on  e30.deptno=30 where e.sal=e30.sal and e30.empno is not null;

SQL> select ename,sal from emp where sal in(select sal from emp where deptno=30);
ENAME            SAL
---------- ---------
ALLEN        1600.00
MARTIN       1250.00
WARD         1250.00
BLAKE        2850.00
TURNER       1500.00
JAMES         950.00
6 rows selected

SQL> select distinct e.ename,e.sal from emp e left join emp e30 on  e30.deptno=30 where e.sal=e30.sal and e30.empno is not null;
ENAME            SAL
---------- ---------
ALLEN        1600.00
MARTIN       1250.00
TURNER       1500.00
JAMES         950.00
WARD         1250.00
BLAKE        2850.00
6 rows selected

 12、列出薪金大于在部门编号为30的所有员工的所有员工的姓名和薪金。

答：select ename,sal from emp where sal > (select max(sal) from emp where deptno=30);

SQL> select ename,sal from emp where sal > (select max(sal) from emp where deptno=30);
ENAME            SAL
---------- ---------
JONES        2975.00
SCOTT        3000.00
KING         5000.00
FORD         3000.00

13、列出在每个部门工作的员工数量、平均工资和平均服务期限。 

答：select count(empno) as 员工数量,round(avg(sal+nvl(comm,0))) as 平均工资,round(avg(months_between(sysdate,hiredate))/12) as 平均期限 from emp group by deptno;

SQL> select count(empno) as 员工数量,round(avg(sal+nvl(comm,0))) as 平均工资,round(avg(months_between(sysdate,hiredate))/12) as 平均期限 from emp group by deptno;
      员工数量       平均工资       平均期限
---------- ---------- ----------
         6       1933         40
         5       2175         38
         3       2917         40

14、列出所有员工的姓名、部门名称、工资

答：select e.ename,e.sal,dp.dname from emp e left join dept dp on e.deptno = dp.deptno;

SQL> select e.ename,e.sal,dp.dname from emp e left join dept dp on e.deptno = dp.deptno;
ENAME            SAL DNAME
---------- --------- --------------
MILLER       1300.00 ACCOUNTING
KING         5000.00 ACCOUNTING
CLARK        2450.00 ACCOUNTING
FORD         3000.00 RESEARCH
ADAMS        1100.00 RESEARCH
SCOTT        3000.00 RESEARCH
JONES        2975.00 RESEARCH
SMITH         800.00 RESEARCH
JAMES         950.00 SALES
TURNER       1500.00 SALES
BLAKE        2850.00 SALES
MARTIN       1250.00 SALES
WARD         1250.00 SALES
ALLEN        1600.00 SALES
14 rows selected

 15、列出所有部门的详细信息和人数

答：select dp.deptno,dp.dname,dp.loc,nvl(c.count,0) from dept dp left join (select deptno,count(*) as count from emp group by deptno) c on dp.deptno=c.deptno;

SQL>  select dp.deptno,dp.dname,dp.loc,nvl(c.count,0) from dept dp left join (select deptno,count(*) as count from emp group by deptno) c on dp.deptno=c.deptno;
DEPTNO DNAME          LOC           NVL(C.COUNT,0)
------ -------------- ------------- --------------
    10 ACCOUNTING     NEW YORK                   3
    20 RESEARCH       DALLAS                     5
    30 SALES          CHICAGO                    6
    40 OPERATIONS     BOSTON                     0

 16、列出各种工作的最低工资

答：select job,min(sal+nvl(comm,0)) as pjgz from emp group by job;

SQL> select job,min(sal+nvl(comm,0)) as pjgz from emp group by job;
JOB             PJGZ
--------- ----------
CLERK            800
SALESMAN        1500
PRESIDENT       5000
MANAGER         2450
ANALYST         3000

 17、列出工作为MANAGER的员工的最低薪金

答：select job,min(sal) as zdgz from emp group by job having job ='MANAGER';

SQL> select job,min(sal) as zdgz from emp group by job having job ='MANAGER'
  2  /
JOB             ZDGZ
--------- ----------
MANAGER         2450

18、列出所有员工年工资，按年薪从低到高排序。

答：select ename,sal+nvl(comm,0) as ngz from emp order by sal asc;

SQL> select ename,sal+nvl(comm,0) as ngz from emp order by sal asc;
ENAME             NGZ
---------- ----------
SMITH             800
JAMES             950
ADAMS            1100
WARD             1750
MARTIN           2650
MILLER           1300
TURNER           1500
ALLEN            1900
CLARK            2450
BLAKE            2850
JONES            2975
SCOTT            3000
FORD             3000
KING             5000
14 rows selected

个人对题目的思考

从考试题目的问题可以看出，解答考试题的核心还是答案要“踩点”，即写到考核的点上，这一点与生产环境实际使用还是有很大区别。

题目设置的表结构不复杂，且使用了Oracle自带数据库作为基础数据，所问题目全部是查询语句，在查询语句中，应该是想考察应试者对数据的查询分析的能力。

在答题中，使用了查询语句基本格式，联表查，子查询的基本格式，聚合函数，单行函数，时间、数字计算等知识技能。考得很基础、一般的教程都有。但却不容易做好。因为在笔试环境中，使用手写SQL、且无法测试验证，这对基本功的要求还是很高的。平时还是要在命令行中多练习，多实践。

此外，除非特别提到“特殊情况”，如：数据量大需考虑优化、有重复数据等，解答考试题目时还是默认在最简单的情况下答题，因为手写语句不宜过长，影响阅读。