sql语句练习

本文主要是介绍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 ) t
jion emp e
on t,deptno=e.deptnon and t.maxsal=e.sal

2.哪些人的薪水在部门平均薪水之上
部门平均薪水：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) t
on e.deptno = t.deptno
where 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.hisal
select 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 deptno
select d.deptno,max(t.avgsal)  maxavgsal from dept d
right join (select deptno, avg(sal) avgsal from emp group by deptno) t
on d.deptno = t.deptno
order by maxavgsal limit 0,1

6.取得平均薪水最高的部门的部门名称
select d.dneme,max(t.avgsal)  maxavgsal from dept d
right join (select deptno, avg(sal) avgsal from emp group by deptno) t
on d.deptno = t.deptno

这篇关于sql语句练习的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！