脚本宝典收集整理的这篇文章主要介绍了sql语句练习，脚本宝典觉得挺不错的，现在分享给大家，也给大家做个参考。

各种连接韦恩图：

首先先建3张表：

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;SELECT *FROM emp;SELECT *FROM dept;SELECT *FROM salgrade;

34道练习题==========================

1.取得每个部门最高薪水的人员名称部门最高薪水：select deptno,max(sal) from emp group by deptno;select e.ename,t.* from (select deptno,max(sal)  maxsal from emp group by deptno ) tjion emp eon t,deptno=e.deptnon and t.maxsal=e.sal2.哪些人的薪水在部门平均薪水之上部门平均薪水：select deptno, avg(sal) from emp group by deptno;select e.ename, e.sal,e.deptno from emp e join ( select deptno, avg(sal) avgsal from emp group by deptno) ton e.deptno = t.deptnowhere e.sal>t.avgsal order by deptno;3.取得部门中（所有人）平均薪水等级所有人平均薪水等级：select e.*,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisalselect t.deptno,avg(t.grade) avggrade from (select e.*,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t group by t.deptno;4.不准用函数（max),取得最高薪水select ename,sal from emp order by sal desc limit 0,1;5.取得平均薪水最高的部门的部门编号每个部门的平均薪水等级：select deptno, avg(sal) avgsal from emp group by deptnoselect d.deptno,max(t.avgsal)  maxavgsal from dept dright join (select deptno, avg(sal) avgsal from emp group by deptno) ton d.deptno = t.deptnoorder by maxavgsal limit 0,16.取得平均薪水最高的部门的部门名称select d.dneme,max(t.avgsal)  maxavgsal from dept dright join (select deptno, avg(sal) avgsal from emp group by deptno) ton d.deptno = t.deptno

脚本宝典总结

以上是脚本宝典为你收集整理的sql语句练习全部内容，希望文章能够帮你解决sql语句练习所遇到的问题。

如果觉得脚本宝典网站内容还不错，欢迎将脚本宝典推荐好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。