SQL多表练习题

最新推荐文章于 2023-04-21 15:28:17 发布

原创最新推荐文章于 2023-04-21 15:28:17 发布 · 1.5k 阅读

1 ·

CC 4.0 BY-SA版权

SQL 专栏收录该内容

1 篇文章

订阅专栏

本文提供了一道SQL练习题，涉及多表查询。通过查询emp、dept和salgrade表，可以获取至少有员工的部门详情，包括部门编号、名称、位置以及员工人数。

emp表：

dept表：

salgrade表：

1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。

select deptno,dname,loc from dept where deptno in(select deptno from emp group by deptno having count(deptno)>=1);
+--------+-----------+--------+
| deptno | dname     | loc    |
+--------+-----------+--------+
|     10 | 教研部    | 北京   |
|     20 | 学工部    | 上海   |
|     30 | 销售部    | 广州   |
+--------+-----------+--------+
3 rows in set (0.17 sec)
2. 列出薪金比关羽高的所有员工。
select * from emp where sal>(select sal from emp where ename="关羽");
+-------+-----------+-----------+------+------------+----------+------+--------+
| empno | ename     | job       | mgr | hiredate   | sal      | COMM | deptno |
+-------+-----------+-----------+------+------------+----------+------+--------+
| 1004 | 刘备      | 经理      | 1009 | 2001-04-02 | 29750.00 | NULL |     20 |
| 1008 | 诸葛亮    | 分析师    | 1004 | 2007-04-19 | 30000.00 | NULL |     20 |
| 1009 | 曾阿牛    | 董事长    | NULL | 2001-11-17 | 50000.00 | NULL |     10 |
| 1013 | 庞统      | 分析师    | 1004 | 2001-12-03 | 30000.00 | NULL |     20 |
+-------+-----------+-----------+------+------------+----------+------+--------+
4 rows in set (0.00 sec)
3. 列出所有员工的姓名及其直接上级的姓名。
select b.ename,a.ename from emp a left join emp b on a.mgr=b.empno;
+-----------+-----------+
| ename     | ename     |
+-----------+-----------+
| 庞统      | 甘宁      |
| 关羽      | 黛绮丝    |
| 关羽      | 殷天正    |
| 曾阿牛    | 刘备      |
| 关羽      | 谢逊      |
| 曾阿牛    | 关羽      |
| 曾阿牛    | 张飞      |
| 刘备      | 诸葛亮    |
| NULL      | 曾阿牛    |
| 关羽      | 韦一笑    |
| 诸葛亮    | 周泰      |
| 关羽      | 程普      |
| 刘备      | 庞统      |
| 张飞      | 黄盖      |
+-----------+-----------+
14 rows in set (0.00 sec)

4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
select a.empno,a.ename,dept.dname from emp a left outer join emp b on a.empno=b.mgr left outer join dept on a.deptno=dept.deptno where a.hiredate > b.hiredate; +-------+-----------+-----------+
| empno | ename     | dname     |
+-------+-----------+-----------+
| 1009 | 曾阿牛    | 教研部    |
| 1009 | 曾阿牛    | 教研部    |
| 1009 | 曾阿牛    | 教研部    |
| 1013 | 庞统      | 学工部    |
| 1006 | 关羽      | 销售部    |
| 1006 | 关羽      | 销售部    |
+-------+-----------+-----------+
6 rows in set (0.00 sec)

5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select emp.*,dept.dname from emp left join dept on emp.deptno=dept.deptno;
+-------+-----------+-----------+------+------------+----------+----------+--------+-----------+
| empno | ename     | job       | mgr | hiredate   | sal      | COMM     | deptno | dname     |
+-------+-----------+-----------+------+------------+----------+----------+--------+-----------+
| 1007 | 张飞      | 经理      | 1009 | 2001-09-01 | 24500.00 |     NULL |     10 | 教研部    |
| 1009 | 曾阿牛    | 董事长    | NULL | 2001-11-17 | 50000.00 |     NULL |     10 | 教研部    |
| 1014 | 黄盖      | 文员      | 1007 | 2002-01-23 | 13000.00 |     NULL |     10 | 教研部    |
| 1001 | 甘宁      | 文员      | 1013 | 2000-12-17 | 8000.00 |     NULL |     20 | 学工部    |
| 1004 | 刘备      | 经理      | 1009 | 2001-04-02 | 29750.00 |     NULL |     20 | 学工部    |
| 1008 | 诸葛亮    | 分析师    | 1004 | 2007-04-19 | 30000.00 |     NULL |     20 | 学工部    |
| 1011 | 周泰      | 文员      | 1008 | 2007-05-23 | 11000.00 |     NULL |     20 | 学工部    |
| 1013 | 庞统      | 分析师    | 1004 | 2001-12-03 | 30000.00 |     NULL |     20 | 学工部    |
| 1002 | 黛绮丝    | 销售员    | 1006 | 2001-02-20 | 16000.00 | 3000.00 |     30 | 销售部    |
| 1003 | 殷天正    | 销售员    | 1006 | 2001-02-22 | 12500.00 | 5000.00 |     30 | 销售部    |
| 1005 | 谢逊      | 销售员    | 1006 | 2001-09-28 | 12500.00 | 14000.00 |     30 | 销售部    |
| 1006 | 关羽      | 经理      | 1009 | 2001-05-01 | 28500.00 |     NULL |     30 | 销售部    |
| 1010 | 韦一笑    | 销售员    | 1006 | 2001-09-08 | 15000.00 |     0.00 |     30 | 销售部    |
| 1012 | 程普      | 文员      | 1006 | 2001-12-03 | 9500.00 |     NULL |     30 | 销售部    |
+-------+-----------+-----------+------+------------+----------+----------+--------+-----------+
14 rows in set (0.02 sec)

6. 列出所有文员的姓名及其部门名称，部门的人数
select emp.ename,dept.dname,count(*) from emp left outer join dept on emp.deptno=dept.deptno group by dept.deptno in(select deptno from emp where emp.job="文员");
+--------+-----------+----------+
| ename | dname     | count(*) |
+--------+-----------+----------+
| 张飞   | 教研部    |       14 |
+--------+-----------+----------+
1 row in set (0.00 sec)

7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。
select job,count(*) from emp group by job having min(sal)>15000;
+-----------+----------+
| job       | count(*) |
+-----------+----------+
| 分析师    |        2 |
| 经理      |        3 |
| 董事长    |        1 |
+-----------+----------+
3 rows in set (0.04 sec)
8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
select emp.ename,dept.deptno from emp,dept where emp.deptno=dept.deptno and dname="销售部";
+-----------+--------+
| ename     | deptno |
+-----------+--------+
| 黛绮丝    |     30 |
| 殷天正    |     30 |
| 谢逊      |     30 |
| 关羽      |     30 |
| 韦一笑    |     30 |
| 程普      |     30 |
+-----------+--------+
6 rows in set (0.00 sec)
9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。

10.列出与庞统从事相同工作的所有员工及部门名称。
select emp.*,dept.dname from emp,dept where emp.deptno=dept.deptno and emp.job=(select job from emp where ename="庞统");
+-------+-----------+-----------+------+------------+----------+------+--------+-----------+
| empno | ename     | job       | mgr | hiredate   | sal      | COMM | deptno | dname     |
+-------+-----------+-----------+------+------------+----------+------+--------+-----------+
| 1008 | 诸葛亮    | 分析师    | 1004 | 2007-04-19 | 30000.00 | NULL |     20 | 学工部    |
| 1013 | 庞统      | 分析师    | 1004 | 2001-12-03 | 30000.00 | NULL |     20 | 学工部    |
+-------+---------
11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno and emp.sal> All (select sal from emp where deptno=30);         +-----------+----------+-----------+
| ename     | sal      | dname     |
+-----------+----------+-----------+
| 曾阿牛    | 50000.00 | 教研部    |
| 刘备      | 29750.00 | 学工部    |
| 诸葛亮    | 30000.00 | 学工部    |
| 庞统      | 30000.00 | 学工部    |
+-----------+----------+-----------+
4 rows in set (0.00 sec)

12.列出每个部门的员工数量、平均工资。
select count(*),avg(sal) from emp where deptno in(select deptno from dept) group by deptno;
+----------+--------------+
| count(*) | avg(sal)     |
+----------+--------------+
|        3 | 29166.666667 |
|        5 | 21750.000000 |
|        6 | 15666.666667 |
+----------+--------------+
3 rows in set (0.00 sec)
select count(*),avg(sal) from emp group by deptno;
+----------+--------------+
| count(*) | avg(sal)     |
+----------+--------------+
|        3 | 29166.666667 |
|        5 | 21750.000000 |
|        6 | 15666.666667 |
+----------+--------------+
3 rows in set (0.00 sec)