您的当前位置：首页正文

SQL练习题

来源：吉趣旅游网

练习答案：

①查询雇员表中工资最高的雇员的员工号、员工姓名、工资和部门号。 select empno,ename,sal,deptno from emp where sal = (select max(sal) from emp);

②单条查询语句综合练习题：

薪水大于1200的雇员，按照部门编号进行分组，分组后的平均薪水必须大于1500，查询各分组的平均工资，按照工资的倒序进行排列。 select avg(sal) avg_sal, deptno from emp where sal > 1200 group by deptno having avg_sal > 1500 order by avg_sal desc;

说明：此句基本上包含了SQL语句的子语句和排列顺序：select（要查询的字段）->from（从哪一张或哪几张表或视图）->where（过滤条件）->group by(having)（分组及条件）->order by（按哪个或哪几个字段进行升序或降序排列）。

注意：SqlServer4.1中可能不支持在order语句中使用组函数avg，报错说：invalid use of group function（错误提示和现象有点对不上）

解决办法：给avg(sal)起个别名avg_sal，这样在order语句中就直接使用这个别名

③等值连接：

查询每个雇员和其所在的部门名

select ename,dname from emp,dept where (emp.deptno = dept.deptno); 或者（推荐）(on中就写连接条件，where中就写过滤条件，各司其职) select ename,dname from emp join dept on(emp.deptno = dept.deptno);

④非等值连接：

查询每个雇员姓名及其工资所在的等级

select ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal);

⑤查询雇员名第2个字母不是a的雇员的姓名、所在的组名、工资所在的等级。三张表的连接查询（先连接，再加上where语句进行过滤） select ename,dname,grade

from emp e join dept d on(e.deptno = d.deptno) join salgrade s on(e.sal between s.losal and s.hisal) where ename not like '_a%';

⑥查询每个雇员和其经理的姓名

自连接：（事实上只有一张表，但把它当成两张表来用，使用别名来进行区分） select e1.ename,e2.ename from emp e1,emp e2 where (e1.mgr = e2.empno); 或者：（推荐用join语句）

select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.empno);

⑦查询每个雇员和其经理的姓名（包括公司老板本身（他上面没有经理））左外连接（会把左表中不符合连接条件的记录也显示出来）：

select e1.ename,e2.ename from emp e1 left join emp e2 on(e1.mgr = e2.empno);

⑧查询每个雇员的姓名及其所在部门的部门名（包括没有雇员的部门）右外连接（会把右表中不符合连接条件的记录也显示出来）：

select ename,dname from emp e right join dept d on(e.deptno = d.deptno);

⑨子查询1：查询每个部门中工资最高的人的姓名、薪水和部门编号先求出每个部门中的最高工资：

select max(sal) max_sal,deptno from emp group by deptno 在使用连接查询：

select ename,sal,e.deptno from emp e join

(select max(sal) max_sal,deptno from emp group by deptno) t on(e.sal = t.max_sal and e.deptno = t.deptno);

⑩子查询2：查询每个部门平均工资所在的等级 select deptno,avg_sal,grade from salgrade

join(select deptno,avg(sal) avg_sal from emp group by deptno) t on(t.avg_sal between salgrade.losal and salgrade.hisal);

或者：

select deptno,avg_sal,grade from

(select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on(t.avg_sal between s.losal and s.hisal);

⑪子查询3：查询每个部门内平均的薪水等级先求每个人的薪水等级

select ename,deptno,grade from emp join salgrade s on(emp.sal between s.losal and s.hisal); 再按组进行分组求平均

select deptno,avg(grade) from

(select ename,deptno,grade from emp join salgrade s on(emp.sal between s.losal and s.hisal)) t group by deptno;

⑫子查询4：查询雇员中有哪些人是经理人：

select ename from emp where empno in(select distinct mgr from emp); 或者：

select ename from emp join (select distinct mgr from emp) t on(emp.empno=t.mgr);

⑬子查询5：不准用库函数，求雇员表中薪水的最高值。

思路：两张完全相同的雇员表，左边一张，右边一张。拿左表中的薪水和右表中的薪水进行比较，左表中的最高薪水值必定不可能小于右表中的某一薪水值。先求出emp表中最高薪水以下的所有薪水值

select distinct e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal); 不在此列的薪水值即为最高薪水值

select distinct sal from emp where sal not in(select distinct e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal));

⑭子查询6：平均薪水最高的部门的部门编号

①：先求出每个部门的平均薪水和部门号（把这个看成一张表） select avg(sal) avg_sal,deptno from emp group by deptno; ②：再求出平均薪水最高值（把这个看成一个值）

select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) t;

③：对①表使用②条件进行查询即可 select avg_sal,deptno from

(select avg(sal) avg_sal,deptno from emp group by deptno) t1 where avg_sal=

(select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) t2);

说明:在Oracle中可以使用组函数嵌套来稍微简化SQL语句的复杂程度（最多是两层嵌套）： select max(avg(sal)) from emp group by deptno;来求出平均薪水最高值注意：这种组函数嵌套在MySQL中不可以用

⑮子查询7：求平均薪水最高的部门的部门名称（在子查询6的基础之上）说明：在实际应用中，达到这种程度的SQL语句已经算比较复杂了。 select dname from dept where deptno = ( select deptno from

(select avg(sal) avg_sal,deptno from emp group by deptno) t1 where avg_sal=

(select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) t2) );

-----------------------------------------------------------------------------------------------------

⑯子查询8：查询平均薪水的等级最低的部门名称

说明：在实际应用中，很少使用到如此复杂的SQL语句。但要掌握一步步的思路，本例是为了锻炼逻辑能力。

①求平均薪水

select avg(sal) avg_sal,deptno from emp group by deptno;

②求平均薪水的等级

select avg_sal,deptno,grade from

(select avg(sal) avg_sal,deptno from emp group by deptno) t join salgrade s on(t.avg_sal between s.losal and hisal);

③求平均薪水的等级最低的那个值 select min(grade) from