目录
写在文章前
首先声明本人不是技术大佬、也不是培训老师，只是在工作中写过两年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
SALES2、列出薪金比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 selected3、列出所有员工及其上级的姓名
答：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 selected4、列出受雇日期早于其直属上级的员工的所有员工。
答：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 selected5、列出部门名称和这些部门的员工信息，同时列出没有员工的部门。
答：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 selected6、列出所有工作为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 ACCOUNTING7、列出薪金大于1500的各种工作。
答：select distinct job from emp where sal>1500;
SQL> select distinct job from emp where sal>1500;
JOB
---------
SALESMAN
PRESIDENT
MANAGER
ANALYST8、列出在部门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 selected9、列出薪金高于公司平均薪金的员工。
答：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 selected10、列出于员工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 selected12、列出薪金大于在部门编号为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.0013、列出在每个部门工作的员工数量、平均工资和平均服务期限。
答：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 4014、列出所有员工的姓名、部门名称、工资
答：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 selected15、列出所有部门的详细信息和人数
答：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 300017、列出工作为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 245018、列出所有员工年工资，按年薪从低到高排序。
答：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、且无法测试验证，这对基本功的要求还是很高的。平时还是要在命令行中多练习，多实践。
此外，除非特别提到“特殊情况”，如：数据量大需考虑优化、有重复数据等，解答考试题目时还是默认在最简单的情况下答题，因为手写语句不宜过长，影响阅读。
2567
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
