Mysql查询练习

表结构和初始化数据

drop database if exists emps;

create database emps;

use emps;

create table emp(

EMPNO INT, -- 员工号

ENAME VARCHAR(10), -- 员工姓名

JOB VARCHAR(9), -- 工作岗位

MGR int, -- 经理的员工号，外键

HIREDATE date, -- 入职时间

SAL double, -- 底薪

COMM double, -- 提成

DEPTNO int, -- 部门编号

primary key(EMPNO)

) ;



CREATE TABLE dept (

DEPTNO int, -- 部门编号

DNAME varchar(13), -- 部门名称

LOC VARCHAR(13), -- 部门地点

primary key(DEPTNO)

) ;



-- 插入数据

Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,'2003-12-17',800,null,20);

Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,'2007-9-3',1600,300,30);

Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,'2005-3-8',1250,500,30);

Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,'2007-7-7',2975,null,20);

Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,'2005-5-6',1250,1400,30);

Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,'2005-5-6',2850,null,30);

Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,'2010-3-1',2450,null,10);

Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,'2005-5-6',3000,null,20);

Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,'2001-5-6',5000,null,10);

Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,'2008-8-8',1500,0,30);

Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,'2005-5-6',1100,null,20);

Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,'2005-5-6',950,null,30);

Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,'2005-5-6',3000,null,20);

Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,'2005-5-6',1300,null,10);



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');

-- 1．列出至少有一个员工的所有部门。
SELECT dname FROM dept WHERE deptno IN(SELECT deptno FROM emp);

-- 2．列出薪金比“SMITH”多或者相等的所有员工。
SELECT ename FROM emp WHERE sal >=(SELECT sal FROM emp WHERE ename="SMITH");

-- 3．列出所有员工的姓名及其直接上级的姓名。
SELECT a.ename,(SELECT ename FROM emp b WHERE b.empno=a.mgr) boss_name FROM emp a;

-- 4．列出受雇日期早于其直接上级的所有员工。
SELECT a.ename FROM emp a WHERE a.`HIREDATE`<(SELECT HIREDATE FROM emp b WHERE b.`EMPNO`=a.`MGR`);

-- 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT a.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno 
FROM dept a LEFT JOIN emp b ON a.deptno=b.deptno;

-- 6．列出所有“CLERK”（办事员）的姓名及其部门名称。
SELECT e.ename,d.dname FROM emp e JOIN dept d ON e.`DEPTNO`=d.`DEPTNO` AND e.`JOB`="CLERK";

-- 7．列出最低薪金大于1500的各种工作。
SELECT DISTINCT JOB HighSalJob FROM emp GROUP BY JOB HAVING MIN(sal)>1500;

-- 8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
SELECT ename FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='sales');

-- 9．列出薪金高于公司平均薪金的所有员工。
SELECT *FROM emp WHERE sal>(SELECT AVG(sal) FROM emp);

-- 10．列出与“SCOTT”从事相同工作的所有员工。
SELECT ename FROM emp WHERE job=(SELECT job FROM emp WHERE ename='scott');

-- 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
SELECT ename,sal FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno='30');

-- 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
SELECT ename,sal FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno='30');

-- 13．列出在每个部门工作的部门名称,员工数量、平均工资。
SELECT d.dname,COUNT(*) AS 员工数量,AVG(sal) FROM emp e JOIN dept d ON e.deptno=d.deptno GROUP BY dname;

-- 14．列出所有员工的姓名、部门名称和工资(含奖金)。
SELECT e.`ENAME`,d.`DNAME`,(IFNULL(sal+comm,sal+0))AS 工资 FROM emp e JOIN dept d ON d.`DEPTNO`=e.`DEPTNO`;

-- 15．列出所有部门的详细信息和部门人数。
SELECT d.*,COUNT(*) AS 部门人数 FROM dept d JOIN emp e ON e.`DEPTNO`=d.`DEPTNO` GROUP BY d.dname;

-- 16．列出各种工作的最低工资。
SELECT job,AVG(sal) FROM emp GROUP BY job;

-- 17．列出各个MANAGER（经理）的最低薪金。
SELECT ename,MIN(sal)AS 最低薪资 FROM emp WHERE job='manager' GROUP BY empno;

-- 18．列出所有员工的年工资,按年薪从低到高排序。
SELECT ename,(sal*12) AS 年工资 FROM emp ORDER BY 年工资; 

-- 19．查询所有部门的最高工资、最低工资、平均工资
SELECT deptno,IFNULL(MIN(sal),0),IFNULL(MIN(sal),0),IFNULL(AVG(sal),0) 
FROM (SELECT e.sal,d.deptno FROM emp e RIGHT JOIN dept d ON d.`DEPTNO`=e.`DEPTNO`) e GROUP BY e.deptno
-- 20．查询每种工作的最高薪资，并按照薪资高低排序
SELECT job,MAX(sal) FROM emp GROUP BY job ORDER BY sal DESC;

-- 21．查询平均薪资大于2000的工作
SELECT DISTINCT job FROM emp WHERE sal>(SELECT AVG(sal) FROM emp);