MySQL——查询练习题

首先创建两张表并添加数据

create table emp(
	empno int(4) primary key,
	ename varchar(10),
	job varchar(9),
	mgr int(4),
	hiredate date,
	sal decimal(7,2),
	comm decimal(7,2),
	deptno int(2)
);

insert into emp values
	(7369,'Smith','clerk',7902,'1980-12-17',800,null,20),
	(7499,'Allen','salesman',7698,'1981-02-20',1600,300,30),
    (7521,'Ward','salesman',7698,'1981-02-22',1250,500,30),
    (7566,'Jones','manager',7839,'1981-04-02',2975,null,20),
    (7654,'Maritn','salesman',7698,'1981-09-28',1250,1400,30),
    (7698,'Blake','manager',7839,'1981-05-01',2850,null,30),
    (7782,'Clark','manager',7839,'1981-06-09',2450,null,10),
    (7788,'Scott','analyst',7566,'1987-04-19',3000,null,20),
    (7839,'King','president',null,'1981-11-17',5000,null,10),
    (7844,'Turner','salesman',7698,'1981-09-08',1500,0,30),
    (7876,'Adms','clerk',7788,'1987-05-23',950,null,30),
    (7900,'James','clerk',7689,'1981-12-03',950,null,30),
    (7902,'Ford','analyst',7566,'1981-12-03',3000,null,20),
    (7934,'Miller','clerk',7782,'1982-01-23',1300,null,10);

create table dept(
    deptno int(2) primary key,
    dname varchar(14),
    loc varchar(13)
);

insert into dept values
	(10,'Accounting','New York'),
	(20,'Research','Dallas'),
	(30,'Sales','Chicage'),
	(40,'Operations','Boston');

1、查询20号部门的所有员工信息。

select * from emp where deptno=20;

2、查询津贴(comm字段)高于月薪(sal字段)的员工信息。

select * from emp where comm>sal;

3、查询津贴高于月薪的20%的员工信息。

select * from emp where comm>sal*0.2;

4、查询10号部门中职位为“manager”和20号部门中职位为“clerk”的员工的信息。

select * from emp where (job='manager' and deptno=10) or (job='clerk' and deptno=20);

5、查询所有职位不是“manager”和“clerk”，并且月薪大于或等于2000的员工详细信息。

select * from emp where job not in('manager','clerk') and sal>=2000;

6、查询没有津贴或津贴低于100的员工信息。

select * from emp where comm is null or comm<100;

7、查询员工工龄大于或等于10年的员工信息。

select * from emp where datediff(now(),hiredate)/365>=10;

8、查询员工信息，要求以全部字母大写的方式显示所有员工的姓名。

select upper(ename) from emp;

9、查询在2月份入职的所有员工信息。

select * from emp where month(hiredate)=2;

10、显示所有员工的姓名、入职的年份和月份，按入职日期所在的月份排序，若月份相同则按入职的年份排序。

select ename,year(hiredate) y,month(hiredate) m from emp order by m,y; 

11、统计各个职位的人数与平均月薪。

select job,count(*),avg(sal) from emp order by job;

12、统计每个部门中各个职位的人数与平均月薪。

select deptno,job,count(*),avg(sal) from emp group by job;

13、统计平均月薪最高的部门编号(提示：使用部门编号分组后，按照平均月薪降序排序，之后只显示第一条记录)。

select deptno,avg(sal) from emp group by deptno order by avg(sal) desc limit 1;

14、查询从事同一种职位但不属于同一部门的员工信息：

select e1.* from emp e1,emp e2 where (e1.job=e2.job) and (e1.deptno!=e2.depnto);

15、查询各个部门的详细信息以及部门人数、部门平均月薪；

select d.deptno,d.dname,count(e.empno),avg(sal) 
from emp e,dept d
where e.deptno=d.deptno group by d.deptno;

16、查询10号部门员工以及领导的信息；

select * from emp where emp in(select mgr from emp where deptno=10) or deptno=10;

17、查询月薪为某个部门平均月薪的员工信息:

select * from emp
where sal in(select avg(sal) from emp group by deptno);

18、查询月薪高于本部门平均月薪的员工的信息：

select * from emp e1
where sal>(select avg(sal) from emp e2 where e2.deptno=e1.depnto);

19、查询月薪高于本部门平均月薪的员工的信息及其部门的平均月薪；

select *,(select avg(sal) from emp e2 where e2.deptno=e1.deptno)
from emp e1
where sal>(select avg(sal) from emp e2 where e2.deptno=e1.deptno);

20、查询所有员工月薪都大于1000的部门的信息；

select * from  dept
where deptno in(select deptno from emp group by deptno having min(sal)>1000);

21、查询所有员工月薪都大于1000的部门的信息及其员工信息；

select * from emp e join dept d on d.deptno in (select deptno from emp where deptno not in(select distinct deptno from emp where sal<1000))and d.deptno=e.deptno; 

select * from dept d left outer join emp e on d.deptno=e.deptno
where e.deptno in(select deptno from emp e1 group by deptno having min(sal)>1000);

22、查询所有员工月薪都在900～3000之间的部门的信息；

select * from dept where deptno not in(select deptno from emp where sal not between 900 and 3000);

23、查询所有月薪都在900～3000之间的员工所在部门的员工信息；

select * from emp e where e.deptno not in(select distinct e.deptno from emp e where e.sal not between 900 and 3000);

24、查询每个员工的领导所在部门的信息；

select d.* from dept d 
where d.deptno in(select distinct e2.deptno from emp e1,emp e2 where e1.mgr=e2.deptno);

25、查询30号部门中月薪排序前三名的员工信息。

select * from emp where deptno=30 order by sal desc limit 3;