sql练习汇总（oracle和sqlserver两个版本的答案）

最近练习sql语句，先把表结构贴来，这几张应该很常见，记得有一家公司笔试题就有这些表和问题,当时刚出来实习，sql根本不会，直接挂掉了。说多都是心酸史呀！

1.建表的SQL

注:下面的sql语句中的类型是oracle数据库类型的。

（1）CREATE TABLE EMP

       (EMPNONUMBER(4) NOT NULL,

        ENAMEVARCHAR2(10),

        JOBVARCHAR2(9),

        MGRNUMBER(4),

       HIREDATE DATE,

        SALNUMBER(7, 2),

        COMMNUMBER(7, 2),

       DEPTNO NUMBER(2));

（2）CREATE TABLE DEPT

      (DEPTNO NUMBER(2),

        DNAMEVARCHAR2(14),

        LOC VARCHAR2(13) );

（3）CREATE TABLE BONUS

       (ENAME VARCHAR2(10),

        JOB   VARCHAR2(9),

        SAL   NUMBER,

        COMM  NUMBER);

（4）CREATE TABLE SALGRADE

       (GRADE NUMBER,

        LOSAL NUMBER,

        HISAL NUMBER);

（5）CREATE TABLE DUMMY

       (DUMMY NUMBER);

 

2.四张表结构和数据

表一：部门表DEPT（使用DESC DEPT;查询）

NO	名称	类型	描述
1	DEPTNO	NUMBER(2)	表示部门编号有两位数字所组成
2	DNAME	VARCHAR2（14）	表示部门名称最多由14个字符所组成
3	LOC	VARCHAR2（13）	表示部门所在位置

 

 

（SELECT* FROM DEPT；）

NO	DEPTNO	DNAME	LOC
1	10	ACCOUNTING（财务部，会计部）	NEWYORK（纽约）
2	20	RESEARCH（调研部）	DALLAS（达拉斯）
3	30	SALES（营业部，市场部）	CHICAGO（芝加哥）
4	40	OPERATIONS（运营部）	BOSTON（波士顿）

 

表二：雇员表EMP（使用DESCEMP;查询）

名称	类型	描述
EMPNO	NUMBER（4）	表示雇员编号，由四个数字组成
ENAME	VARCHAR2（10）	表示雇员姓名，由10个字符组成
JOB	VARCHAR2（9）	表示雇员的职位，由9个字符组成
MGR	NUMBER（4）	表示雇员对应的领导编号，领导也是雇员
HIREDATE	DATE	表示雇员的雇佣日期
SAL	NUMBER（7,2）	表示雇员的基本工资，由两位小数5位整数和2位小数组成，共7位
COMM	NUMBER（7,2）	表示雇员的奖金
DEPTNO	NUMBER（2）	表示雇员所在部门的编号

 

（SELECT* FROM EMP;）

NO	EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
1	7369	SMITH（史密斯）	CLERK（办事员）	7902	17-12月-80	800		20
2	7499	ALLEN（艾伦）	SALESMAN（销售员）	7698	20-2月-81	1600	300	30
3	7521	WARD （沃德）	SALESMAN	7698	22-2月-81	1250	500	30
4	7566	JONES （琼斯）	MANAGER（经理主管）	7839	02-4月-81	2975		20
5	7654	MARTIN（）马丁	SALESMAN	7698	28-9月-81	1250	1400	30
6	7698	BLAKE （布雷克）	MANAGER	7839	01-5月-81	2850		30
7	7782	CLARK（克拉克）	MANAGER	7839	09-6月-81	2450		10
8	7788	SCOTT（斯科特）	ANALYST（分析员）	7566	19-4月-87	3000		20
9	7839	KING（金）	PRESIDENT（总经理，总裁）		17-11月-81	5000		10
10	7844	TURNER（特纳）	SALESMAN	7698	08-9月-81	1500	0	30
11	7876	ADANS（奥丹斯）	CLERK	7788	23-5月-87	1100		20
12	7900	JAMES（詹姆斯）	CLERK	7698	03-12月-81	950		30
13	7902	FORD（福特）	ANALYST	7566	03-12月-81	3000		20
14	7934	MILLER（米勒）	CLERK	7782	23-1月-82	1300		10

 

表三：工资等级表：（DESC SALGRADE）

NO	名称	类型	描述
1	GRADE	NUMBER	工资的等级
2	LOSAL	NUMBER	此等级的最低工资
3	HISAL	NUMBER	此等级的最高工资

 

（SELECT* FROM SALGRADE；查询）

NO	GRADE	LOSAL	HISAL
1	1	700	1200
2	2	1201	1400
3	3	1401	2000
4	4	2001	3000
5	5	3001	9999

 

 

表四：工资表BONUS：（DESCBONUS）

NO	名称	类型	描述
1	ENAME	VARCHAR2（10）	雇员姓名
2	JOB	VARCHAR2(9)	雇员职位
3	SAL	NUMBER	雇员基本工资
4	COMM	NUMBER	奖金，提成

 

3.练习题

注：20道题，都是写出对应的sql,每道题中的答案中：

（1）是我下载前别人给的答案（用oracle函数），（2）是我写的（sqlsever函数）。

我用vs10自带的sqlserver2008，有些不爽，运行sql语句，它会把你写的sql语句修改成它的模板；有的可能没有（2），因为是通用的或者我还没想到用另一种写出来，有的下面会有些注解，新手练习，相互学习。（1）和（2）的sql可能有问题，请指正。本人菜鸟一枚，如果你有好的答案或者sql写错了，请留言，我会完善sql.谢谢！

--1.列出至少有一个员工的所有部门

(1)select d.dname,t1.co

from (select e.deptno,count(e.deptno) co from emp e group by e.deptno) t1,

dept d

where d.deptno =t1.deptnod

and t1.co > 1;

(2) SELECT    e.DEPTNO, d.DNAME, d.LOC,COUNT(*) AS 总人数

FROM        EMPAS e LEFT OUTER JOIN  DEPT AS d ON e.DEPTNO= d.DEPTNO

GROUP BY    e.DEPTNO,d.DNAME, d.LOC

HAVING      (COUNT(*) >= 1)

--2.列出薪金比‘SMITH’多的所有员工

select *

from emp

where sal > (selecte.sal from emp e where e.ename = 'SMITH');

--3.列出所有员工的姓名以及直接上级的姓名

(1)select e1.ename,e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;

(2)SELECT    e1.EMPNO,e1.ENAME, e2.EMPNO AS 上级工号,e2.ENAME AS 上级姓名

FROM    EMP AS e1 LEFT OUTER JOIN    EMP AS e2 ON e1.MGR = e2.EMPNO

注：(1)中sql会漏掉没有上级的员工信息

--4.列出受雇日期早于直接上级入职日期的职工编号，姓名，部门名称

（1）select e1.empno 员工号,

e1.ename 员工姓名,

e1.hiredate 入职日期,

e2.ename 上级姓名,

e2.hiredate 上级入职日期

from emp e1, empsaj

where e1.mgr =e2.empno

and e1.hiredate <e2.hiredate;

（2）SELECT   e1.EMPNO, e1.ENAME, e1.DEPTNO,t.DNAME, e1.HIREDATE, e2.EMPNO AS 上级工号, e2.ENAME AS 上级姓名, e2.HIREDATE AS 上级雇佣日期

FROM   EMP ASe1 LEFT OUTER JOIN   EMP AS e2 ON e1.MGR= e2.EMPNO INNER   JOIN DEPT AS t ON e1.DEPTNO= t.DEPTNO

WHERE  (e1.HIREDATE< e2.HIREDATE)

注：(2)中sql是被编译处理后的sql

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

注：(部门表 左连接 员工表)或者 (员工表右连接 部门表)

（1）select d.dname, e.*

from emp e

right outer join deptd

on e.deptno = d.deptno

order by d.dname;

（2）SELECT  d.DNAME,e.EMPNO, e.ENAME, e.JOB, e.MGR, e.HIREDATE, e.SAL, e.COMM, e.DEPTNO

FROM  DEPT AS d LEFT OUTER JOIN    EMP AS e ON d.DEPTNO = e.DEPTNO

注：（1）是(员工表 右连接 部门表) （2）(部门表 左连接 员工表)

--6.列出所有职位为'CLERK'的姓名 和部门名称，部门人数

(1)

select e.ename,d.dname

from emp e, dept d

where e.deptno =d.deptno

and e.job = 'CLERK';

（2）SELECT          d.DEPTNO, d.DNAME, d.LOC, COUNT(*) AS'SUM'

FROM     DEPT AS d LEFT OUTER JOIN   EMP AS e ON d.DEPTNO = e.DEPTNO

WHERE    (d.DEPTNO IN

                 (SELECT          DEPTNO

                  FROM               EMP AS e

                  WHERE           (JOB = 'CLERK')))

GROUP BY  d.DEPTNO, d.DNAME, d.LOC

注：（1）的sql存在问题：没有计算部门人数

 --7.列出最低工薪大于1500的各种工作以及从事此工作的全部雇员的人数

select distinct e.job,count(*)

from emp e

where e.sal > 1500

group by e.job;

注：先决条件是的工薪大于1500

--8.列出在部门'SALES'(销售部)工作的员工信息,假设不知道销售部门的部门c

（1）select e.ename from emp e where e.job = 'SALESMAN';

（2）select *  from emp  where deptno= (select deptno  from dept where DNAME='SALES');

注：（1）错误：部门为'SALES'，不是job

--9.列出薪金高于公司平均薪金的所有员工,部门,上级领导,工资级别

select e2.ename, e2.deptno,e3.ename, s.grade

from emp e2, emp e3,salgrade s

where e2.sal >(select avg(e.sal) from emp e)

and (e2.sal betweens.losal and s.hisal)

and e2.mgr = e3.empno;

--10.列出与'SCOTT'从事相同工作的所有员工及部门名称

select e.ename,d.dname

from emp e, dept d

where e.job in (selecte2.job from emp e2 where e2.ename = 'SCOTT')

and e.deptno =d.deptno;

注：为什么用in 不用 =，如果有 >=2 个人的名字为'SCOTT', 用=就出错。

--11.列出薪金等于部门20中的员工的薪金的所有员工和薪金

select *

from emp

where sal in (selecte.sal from emp e where e.deptno = 20);

注：用in而不是=

--12.列出薪金高于部门30中的员工的薪金的所有员工和薪金,部门名称

select e2.ename,e2.sal, d.dname

from emp e2, dept d

where sal >

(select max(t1.sal)

from (select e.salfrom emp e where e.deptno = 30) t1)

and d.deptno =e2.deptno;

注: 薪金高于部门30中的员工的薪金,思路：找到部门30最高的的薪金，然后查找比这个薪金高的员工资料。

--13.列出每个部门员工的数量,平均工资和平均年限

（1）select t2.*, t1.ro3 平均年限

from (select e2.deptno,count(*) 员工数量, avg(e2.sal) 平均工资

from emp e2

group by e2.deptno)t2,

(select ro2.deptno,avg(ro2.ro) ro3

from (selecte3.deptno,

round(months_between(sysdate,e3.hiredate) / 12) ro

from emp e3) ro2

group by ro2.deptno)t1

where t1.deptno =t2.deptno;

（2）SELECT   e.DEPTNO, d.DNAME, COUNT(*) AS '总人数', AVG(e.SAL) AS '平均薪金', AVG(DATEDIFF(year, e.HIREDATE,GETDATE()))  AS '平均年限'

FROM      EMPAS e INNER JOIN   DEPT AS d ON e.DEPTNO =d.DEPTNO

GROUP BY   e.DEPTNO, d.DNAME

注：在Sqlserver中: datediff(day/year…,起始日期，结束日期)函数，计算两个时间的间隔，（2）的sql只适用用于SqlServer.

--14.列出所有员工的姓名，工资，部门名称

select e.ename, e.sal,d.dname

from emp e, dept d

where d.deptno =e.deptno;

--15.列出所有部门信息和部门人数

(1)select d.*, nvl(t1.c, 0)部门人数

from dept d

left outer join(select e.deptno, count(e.deptno) c

from emp e

group by e.deptno) t1

on d.deptno =t1.deptno;

(2)

SELECT         e.DEPTNO, d.DNAME, COUNT(*) AS '总人数'

FROM       DEPT AS d LEFT OUTER JOIN    EMPAS e ON d.DEPTNO = e.DEPTNO

GROUP BY   e.DEPTNO, d.DNAME

（3）SELECT     d.DEPTNO,d.DNAME, d.LOC, ISNULL(t1.c, 0) AS 部门人数

FROM       DEPT AS d LEFT OUTER JOIN

    (SELECT          DEPTNO,COUNT(DEPTNO) AS c

                                  FROM               EMP AS e

                                  GROUP BY    DEPTNO) AS t1 ON d.DEPTNO = t1.DEPTNO

注：(1)中NVl（）函数,如果第一个参数不为null，返回它，如果为null,返回第二参数（其实就是NULL给他一个默认值）；oracle函数。提醒一点：NULL这个值表示UNKNOWN(未知):它不表示“”(空字符串)。

（3）中ISNULL和oracle中NVl（）相同的作用，是sqlserver函数。

（2）是有问题的：

如果有个部门没有员工,

一、查出的结果对应的DEPTNO为null；

二、总人数为1,（不要惊讶，因为用的count函数,两个表连接后查询有一行的数据的，但是只是有的值为null,）；

有人会说：e.DEPTNO修改为  d.DEPTNO(group by 后面那个也修改),这个只能解决上面提到的第一个问题（查出的结果对应的DEPTNO为null），但是第二个还是没有解决。

--16.列出各种工作的最低工薪以及从事此工作的员工信息

（1）select *

from emp, (select e.jobj, min(e.sal) s from emp e group by e.job) t1

where emp.job = t1.j

and emp.sal = t1.s;

（2） SELECT          e.EMPNO, e.ENAME, e.JOB, e.MGR,e.HIREDATE, e.SAL, e.COMM, e.DEPTNO, c.sal1, c.JOB AS Expr1

FROM    EMPAS e INNER JOIN

                                (SELECT          MIN(SAL) AS sal1, JOB

                                  FROM               EMP

                                  GROUP BY    JOB) AS c ON e.JOB = c.JOB AND e.SAL =c.sal1

--17.列出各个部门职位为'MANAGER'(经理)的最低工薪

select a.dname,min(a.sal)

from (select d.dname,e.sal

from emp e, dept d

where e.deptno = d.deptno

and e.job = 'MANAGER')a

group by a.dname;

（2）SELECT  EMPNO, ENAME,DEPTNO, MIN(SAL) AS sal

FROM     EMP

WHERE   (JOB = 'MANAGER')

GROUP BY   ENAME, DEPTNO, EMPNO

--18.列出所有员工的年薪，按照年薪由低到高排序

select e.ename, e.sal* 12 from emp e order by e.sal;

--19.查出每个员工的上级领导，并且求出这些主管的工资超过3000

select e1.ename 本人姓名, e2.ename 领导姓名, e2.sal 领导工资

from emp e1, emp e2

where e1.mgr =e2.empno

and e2.sal > 3000;

--20.求出部门名称中带有'S'的部门员工的工资合计，部门人数

select d.dname 部门名称, t2.su 工资合计, t2.co 部门人数

from (select e.deptno,sum(e.sal) su, count(e.empno) co

from emp e

group by e.deptno) t2,

dept d

where t2.deptno in(select d.deptno from dept d where d.dname like '%S%')

and d.deptno =t2.deptno;

（2）SELECT  d.DEPTNO, d.DNAME, ISNULL(e.count,0) AS 部门人数, ISNULL(e.sum,0) AS 工资合计

FROM  DEPT AS d LEFT OUTER JOIN     (SELECT          DEPTNO, COUNT(*) AS count, SUM(SAL)AS sum

FROM        EMP

GROUP BY    DEPTNO) AS e ON d.DEPTNO = e.DEPTNO

WHERE      (d.DNAMELIKE '%S%')

注：前面解释过NVL（）和ISnull()两个函数的用法（问题15的答案注解中）

4.总结

        以上的练习题只是初级的，学会一些基本的用法，还有一些基本的sql知识我会后续慢慢写出来，比如oracle和sqlserver基本函数，sql语句的优化，列转行等。

借用前辈们的一句话：“SQL开发是一门语言，它很容易学，但是很难掌握”，我感觉说得很有道理，我实习前不是很注重sql的学习，当出去进行几次面试时才意识到这个东西还是很重要的，找实习工作时有个考官问了我这样一个问题：select语句的执行顺序。我当时就懵了，这个问题的答案网上一大堆，有兴趣的话可以百度一下。