mysql DQL-基本查询练习（2）

create table emp(
	empno int,						-- 员工编号
	pname varchar(20),		-- 员工名字
	job varchar(50),			# 工作名字
	mgr int,							-- 上级领导编号
	hiredate date,				-- 入职日期
	sal int,							-- 薪金
	comn int, 						-- 奖金
	deptno int 						-- 部门编号	
);
desc emp;
drop table emp;
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,20);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,30);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1480,20);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','PANAOEK',7839,'1907-06-19',2990,NuLL,30);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,10);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',null,'1981-11-17',150210,null,20);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,10);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,30);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,20);
INSERT INTo emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,30);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

select * from emp;
-- 1.按员工编号排序不在10号部门工作的员工信息
select * from emp where deptno != 10;
-- 2.查询姓名第二个字母不是"A"且薪水大于1000的员工信息，按年薪降序排列
select * from emp where pname not like '_A%' and sal > 1000 order by (sal * 12 + ifnull(comn,0)) desc
;
-- 3.求每个部门的平均薪水
select deptno,avg(sal) from emp GROUP BY deptno;
-- 4.求各个部门的最高薪水
select deptno,max(sal) from emp group by deptno;
-- 5.求每个部门每个岗位的最高薪水
select deptno,job,max(sal) from emp group by job,deptno;
-- 6.求平均薪水大于2000的部门编号
select deptno,avg(sal) avg_sal from emp group by deptno having avg(sal) > 2000;
-- 7.将部门平均薪水大于1500的部门编号列出来，按照平均薪水降序排列
select deptno,avg(sal) from emp group by deptno having avg(sal) > 1500 order by avg(sal) desc; 
-- 8.选择公司中有奖金的员工姓名,工资
select pname,sal from emp where comn is not NULL;
-- 9.查询员工最高工资和最低工资的差距
select max(sal) - min(sal) from emp;