目录
4.求工资在7500到8500元之间，年龄最大的人的姓名及部门
10.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表
16.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序
练习1
题目素材1
-- 创建部门表
create table if not exists dept3( deptno varchar(20) primary key ,
-- 部门号 name varchar(20) -- 部门名字 ) charset=utf8;
-- 创建员工表
create table if not exists emp3( eid varchar(20) primary key , -- 员工编号
ename varchar(20), -- 员工名字
age int, -- 员工年龄
dept_id varchar(20) -- 员工所属部门 ) charset=uf8;
-- 给dept3表添加数据
insert into dept3 values('1001','研发部');
insert into dept3 values('1002','销售部');
insert into dept3 values('1003','财务部');
insert into dept3 values('1004','人事部');
-- 给emp3表添加数据
insert into emp3 values('1','乔峰',20, '1001'); insert into emp3 values('2','段誉',21, '1001');
insert into emp3 values('3','虚竹',23, '1001'); insert into emp3 values('4','阿紫',18, '1001');
insert into emp3 values('5','扫地僧',85, '1002'); insert into emp3 values('6','李秋水',33, '1002');
insert into emp3 values('7','鸠摩智',50, '1002'); insert into emp3 values('8','天山童姥',60, '1003');
insert into emp3 values('9','慕容博',58, '1003'); insert into emp3 values('10','丁春秋',71, '1005');
1、查询每个部门的所属员工
mysql> select name,group_concat(ename) from dept3 d left join emp3 e on d.deptno=e.dept_id group by d.deptno;
2、查询研发部门的所属员工
mysql> select name,emp3.* from dept3,emp3 where dept3.deptno=emp3.dept_id and dept3.name='研发部';
3、查询研发部和销售部的所属员工
mysql> select name,group_concat(ename) from dept3 d right join emp3 e on d.deptno=e.dept_id where d.name='研发部' or d.name='销售部' group by deptno;
4、查询每个部门的员工数,并升序排序
mysql> select name,count(eid) from dept3 d left join emp3 e on d.deptno=e.dept_id group by d.deptno order by count(eid) asc;
5、查询人数大于等于3的部门，并按照人数降序排序
mysql> select name,count(eid) from dept3 d left join emp3 e on d.deptno=e.dept_id group by d.deptno having count(eid)>=3 order by count(eid) desc;
练习2
题目素材2
新增员工表emp和部门表dept
create table dept (dept1 int ,dept_name varchar(11)) charset=utf8;
create table emp (sid int ,name varchar(11),age int,worktime_start date,incoming int,dept2 int) charset=utf8;insert into dept values
(101,'财务'),
(102,'销售'),
(103,'IT技术'),
(104,'行政');insert into emp values
(1789,'张三',35,'1980/1/1',4000,101),
(1674,'李四',32,'1983/4/1',3500,101),
(1776,'王五',24,'1990/7/1',2000,101),
(1568,'赵六',57,'1970/10/11',7500,102),
(1564,'荣七',64,'1963/10/11',8500,102),
(1879,'牛八',55,'1971/10/20',7300,103);
1.找出销售部门中年纪最大的员工的姓名
mysql> select dept_name,name from dept d inner join emp e on d.dept1=e.dept2
-> where dept_name='销售' and age in (select max(age) from emp group by dept2);
2.求财务部门最低工资的员工姓名
mysql> select dept_name,name from emp e inner join dept d
-> on e.dept2=d.dept1
-> where incoming in (select min(incoming) from emp group by dept2)
-> and dept_name='财务';
3.列出每个部门收入总和高于9000的部门名称
mysql> select dept_name from dept d inner join
-> (select dept2,sum(incoming) sum from emp group by dept2) e
-> on d.dept1=e.dept2
-> and sum>9000;
4.求工资在7500到8500元之间，年龄最大的人的姓名及部门
mysql> select name,dept_name from dept d inner join emp e
-> on d.dept1=e.dept2
-> where age in (select max(age) from emp group by dept2)
-> and incoming between 7500 and 8500;
5.找出销售部门收入最低的员工入职时间
mysql> select dept_name,name,worktime_start from emp e inner join dept d
-> on d.dept1=e.dept2
-> where incoming in (select min(incoming) from emp group by dept2)
-> and d.dept_name='销售';
6.财务部门收入超过2000元的员工姓名
mysql> select name from emp e inner join dept d on e.dept2=d.dept1
-> where e.incoming>2000 and d.dept_name='财务';
7.列出每个部门的平均收入及部门名称
mysql> select dept_name,avg from dept d left join
-> (select dept2,avg(incoming) avg from emp group by dept2) e
-> on d.dept1=e.dept2;
8.IT技术部入职员工的员工号
mysql> select dept_name,group_concat(sid) from dept d left join emp e
-> on d.dept1=e.dept2
-> where dept_name='IT技术' group by dept2;
9.财务部门的收入总和；
mysql> select dept_name,sum(incoming) from dept d inner join emp e
-> on d.dept1=e.dept2
-> where dept_name='财务' group by dept2;
10.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表
mysql> select * from emp order by dept2 desc,worktime_start asc;
11.找出哪个部门还没有员工入职；
方法一：
mysql> select dept_name from dept d left join
-> (select dept2,count(1) count from emp group by dept2) e
-> on d.dept1=e.dept2
-> where e.count is NULL;
方法二：
mysql> select distinct dept_name from dept d,emp e
-> where d.dept1 not in (select dept2 from emp);
12.列出部门员工收入大于7000的部门编号，部门名称；
mysql> select dept1,dept_name,name from dept d left join emp e on d.dept1=e.dept2
-> where e.incoming>7000;
13.列出每一个部门的员工总收入及部门名称；
mysql> select dept_name,sum from dept d left join
-> (select dept2,sum(incoming) sum from emp group by dept2) e
-> on d.dept1=e.dept2;
14.列出每一个部门中年纪最大的员工姓名，部门名称；
mysql> select dept_name,name from dept d left join emp e
-> on d.dept1=e.dept2
-> where age in (select max(age) from emp group by dept2);
15.求李四的收入及部门名称
mysql> select name,dept_name,incoming from emp e,dept d
-> where e.name='李四' and d.dept1=e.dept2;
16.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序
避免出现两个一样的最大值被漏掉的情况。
mysql> select * from dept INNER JOIN
-> (SELECT emp.name,emp.incoming,emp.dept2 from emp RIGHT JOIN
-> (select MAX(incoming) as incoming,dept2 from emp group by dept2) new1 on emp.dept2=new1.dept2 and emp.incoming=new1.incoming) new2
-> on dept.dept1=new2.dept2
-> order by incoming desc;
17.列出部门员工数大于1个的部门名称
mysql> select dept_name from dept d left join
-> (select dept2,count(sid) count from emp group by dept2) e
-> on d.dept1=e.dept2
-> where count > 1;
19.查找张三所在的部门名称
mysql> select name,dept_name from dept d,emp e
-> where d.dept1=e.dept2 and name='张三';
318
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
