数据库习题练习

创建数据库,代码如下:

create database dbtest;
use dbtest;
DROP
TABLE IF EXISTS EMP; DROP TABLE IF EXISTS DEPT; DROP TABLE IF EXISTS SALGRADE; CREATE TABLE DEPT -- 部门表 (DEPTNO int(2) not null , DNAME VARCHAR(14) , LOC VARCHAR(13), primary key (DEPTNO) ); CREATE TABLE EMP -- 雇员表 (EMPNO int(4) not null , ENAME VARCHAR(10), JOB VARCHAR(9), MGR INT(4), HIREDATE DATE DEFAULT NULL, SAL DOUBLE(7,2), COMM DOUBLE(7,2), primary key (EMPNO), DEPTNO INT(2) ) ; CREATE TABLE SALGRADE -- 薪水等级表 ( GRADE INT, LOSAL INT, HISAL INT ); -- 插入数据 INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 10, ACCOUNTING, NEW YORK); INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 20, RESEARCH, DALLAS); INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 30, SALES, CHICAGO); INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 40, OPERATIONS, BOSTON); commit; INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7369, SMITH, CLERK, 7902, 1980-12-17 , 800, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7499, ALLEN, SALESMAN, 7698, 1981-02-20 , 1600, 300, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7521, WARD, SALESMAN, 7698, 1981-02-22 , 1250, 500, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7566, JONES, MANAGER, 7839, 1981-04-02 , 2975, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7654, MARTIN, SALESMAN, 7698, 1981-09-28 , 1250, 1400, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7698, BLAKE, MANAGER, 7839, 1981-05-01 , 2850, NULL, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7782, CLARK, MANAGER, 7839, 1981-06-09 , 2450, NULL, 10); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7788, SCOTT, ANALYST, 7566, 1987-04-19 , 3000, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7839, KING, PRESIDENT, NULL, 1981-11-17 , 5000, NULL, 10); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7844, TURNER, SALESMAN, 7698, 1981-09-08 , 1500, 0, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7876, ADAMS, CLERK, 7788, 1987-05-23 , 1100, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7900, JAMES, CLERK, 7698, 1981-12-03 , 950, NULL, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7902, FORD, ANALYST, 7566, 1981-12-03 , 3000, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7934, MILLER, CLERK, 7782, 1982-01-23 , 1300, NULL, 10); commit; INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 1, 700, 1200); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 2, 1201, 1400); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 3, 1401, 2000); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 4, 2001, 3000); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 5, 3001, 9999); commit;

1:取得每个部门最高薪水的人员名称

  第一步:首先将部门分组,查询出每个部门的最高薪水

  第二步:将上面查询到的表当做临时表t与emp连接查询,查询条件为,emp表的部门编号与t表的部门编号deptno相等,并且emp表的sal值 与 t表 maxsal值相等

select 
    e.eanme,e.deptno
from 
    (select deptno,max(sal) as maxsal from emp group by deptno) t -- 将查询到的表当做临时表t
join
    emp e
on
    t.deptno = e.deptno and t.maxsal = e.sal;

  运行结果:

+-------+--------+
| ename | deptno |
+-------+--------+
| BLAKE |     30 |
| SCOTT |     20 |
| KING  |     10 |
| FORD  |     20 |
+-------+--------+

 

2:哪些人的薪水在部门的平均薪水之上

  第一步:查询出部门的平均薪水

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

  第二部:查询出各部门薪水高于平均薪水的人员,条件为 sal>平均薪水

select t.*, e.ename,e.sal from ( select deptno,avg(sal) as avgsal from emp group by deptno) t join emp e on t.deptno = e.deptno and e.sal > avgsal;

  结果

+--------+-------------+-------+---------+
| deptno | avgsal      | ename | sal     |
+--------+-------------+-------+---------+
|     30 | 1566.666667 | ALLEN | 1600.00 |
|     20 | 2175.000000 | JONES | 2975.00 |
|     30 | 1566.666667 | BLAKE | 2850.00 |
|     20 | 2175.000000 | SCOTT | 3000.00 |
|     10 | 2916.666667 | KING  | 5000.00 |
|     20 | 2175.000000 | FORD  | 3000.00 |
+--------+-------------+-------+---------+

3.取得部门中(所有人)平均的薪水等级

  第一步:查询每一个人的薪水等级

  第二部:将查询到的数据按照部门分组,求薪水等级的平均值

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

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

+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
|     20 |       2.8000 |
|     30 |       2.5000 |
|     10 |       3.6667 |
+--------+--------------+

4.不准使用max函数,求出最高薪水(给出两种解决方案):

  使用max函数得出结果为:

  

mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+

 

  第一种:降序排列,分页查询(需要注意sal的值可能有多个相同的值)

mysql> select ename,sal from emp order by sal desc limit 0,1;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+

  第二种:表的自连接

 select sal from emp where sal not in (select a.sal from emp a join emp b on a.sal < b.sal);
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+

5.取得平均薪水最高的部门名称和部门编号(两种解决方案)

  第一种:

select t.*,d.dname from (select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1) t join dept d on t.deptno = d.deptno;
+--------+-------------+------------+
| deptno | avgsal      | dname      |
+--------+-------------+------------+
|     10 | 2916.666667 | ACCOUNTING |
+--------+-------------+------------+

  第二种:

mysql> select deptno, avg(sal) as avgsal from emp group by deptno  having avgsal = (select max(t.avgsal) from  (select deptno,avg(sal) as avgsal from emp group by deptno) t);
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

 6.

数据库习题练习

上一篇:mysql读写分离, 这个骚操作, 应该给满分


下一篇:mysql性能优化