各种连接韦恩图:
首先先建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