目录
4.求工资在7500到8500元之间,年龄最大的人的姓名及部门
10.先按部门号大小排序,再依据入职时间由早到晚排序员工信息表
16.列出每个部门中收入最高的员工姓名,部门名称,收入,并按照收入降序
表结构
员工表emp
部门表dept
创建表
命令:
create table dept (dept1 int ,dept_name varchar(11));
命令:
create table emp (sid int ,name varchar(11),age int,worktime_start date,incoming int,dept2 int);
表内容如下:
表数据插入
命令:
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.找出销售部门中年纪最大的员工的姓名
命令:
方式一:
select emp.name from emp inner join dept on emp.dept2=dept.dept1 where dept.dept_name='销售' order by emp.age desc limit 1;
方式二:
select max(age) from dept,emp where dept.dept1=emp.dept2 and dept_name='销售';
select name from dept,emp where dept.dept1=emp.dept2 and age=(select max(age) from dept,emp where dept.dept1=emp.dept2 and dept_name='销售') and dept_name='销售';
2.求财务部门最低工资的员工姓名
命令:
方式一:
select emp.name from emp inner join dept on emp.dept2=dept.dept1 where dept.dept_name='财务' order by emp.incoming limit 1;
方式二:
select min(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name='财务';
select name from dept,emp where dept.dept1=emp.dept2 and incoming=(select min(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name='财务') and dept_name='财务';
3.列出每个部门收入总和高于9000的部门名称
命令:
select dept.dept_name from emp left join dept on emp.dept2=dept.dept1 group by dept.dept_name having sum(incoming)>9000;
或
select dept_name from dept,emp where dept.dept1=emp.dept2 group by dept_name having sum(incoming)>9000;
4.求工资在7500到8500元之间,年龄最大的人的姓名及部门
命令:
方式一:
select emp.name,dept.dept_name
from dept inner join emp
on emp.dept2=dept.dept1
where emp.age=(select max(emp.age) from emp where emp.incoming between 7500 and 8500);
方式二:
select max(age) from emp;
select name,dept_name from dept,emp where dept.dept1=emp.dept2 and incoming between 7500 and 8500 and age=(select max(age) from emp);
select name,dept_name from dept,emp where dept.dept1=emp.dept2 and incoming >= 7500 and incoming<=8500 and age=(select max(age) from emp);
5.找出销售部门收入最低的员工入职时间
命令:
方式一:
select emp.worktime_start from emp where emp.incoming = (select min(emp.incoming) from emp inner join dept on emp.dept2=dept.dept1 where dept.dept_name='销售');
方式二:
select min(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name='销售';
select worktime_start from emp where incoming=(select min(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name='销售');
6.财务部门收入超过2000元的员工姓名
命令:
select emp.name,emp.incoming from emp inner join dept on emp.dept2=dept.dept1 and emp.incoming>2000 and dept.dept_name='财务';
select name from dept,emp where dept.dept1=emp.dept2 and dept_name='财务' and incoming>2000;
7.列出每个部门的平均收入及部门名称
命令:
select dept.dept_name, avg(emp.incoming) from dept inner join emp on emp.dept2=dept.dept1 group by dept.dept_name;
select t.dept_name,avg(incoming) from (select * from dept,emp where dept.dept1=emp.dept2) t group by t.dept_name;
select dept_name,avg(incoming) from dept,emp where dept.dept1=emp.dept2 group by dept_name;
8.IT技术部入职员工的员工号
命令:
select emp.sid from emp inner join dept on emp.dept2=dept.dept1 where dept.dept_name='IT技术';
select sid from dept,emp where dept.dept1=emp.dept2 and dept_name='IT技术';
9.财务部门的收入总和;
命令:
select sum(emp.incoming) as 收入总和 from emp inner join dept on emp.dept2=dept.dept1 where dept.dept_name='财务';
select sum(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name='财务';
10.先按部门号大小排序,再依据入职时间由早到晚排序员工信息表
命令:
方式一:
select *from emp right join dept on emp.dept2=dept.dept1 order by dept.dept1 asc,worktime_start asc;
方式二:
select * from emp order by dept2 desc,worktime_start asc;
11.找出哪个部门还没有员工入职;
命令:
方式一:
select dept.dept_name from dept left join emp on emp.dept2=dept.dept1 where dept.dept1 not in (select emp.dept2 from emp);
方式二:
select dept_name from dept LEFT JOIN emp on dept.dept1=emp.dept2 where sid is null;
方式三:
select dept_name from dept where dept1 not in (select dept2 from emp);
12.列出部门员工收入大于7000的部门编号,部门名称;
命令:
select distinct dept.dept1,dept.dept_name from dept inner join emp on emp.dept2=dept.dept1 where emp.incoming>7000;
或
select distinct dept1,dept_name from dept,emp where dept.dept1=emp.dept2 and incoming>7000;
13.列出每一个部门的员工总收入及部门名称;
命令:
select dept.dept_name,sum(emp.incoming) as 员工总收入 from dept inner join emp on emp.dept2=dept.dept1 group by dept.dept_name;
或
select sum(incoming),dept_name from dept,emp where dept.dept1=emp.dept2 group by dept_name;
14.列出每一个部门中年纪最大的员工姓名,部门名称;
命令:
方式一:
select emp.name,dept.dept_name from emp inner join dept on emp.dept2=dept.dept1
where emp.age in (select max(emp.age) from dept inner join emp on emp.dept2=dept.dept1 group by dept.dept_name);
方式二:
select name,dept_name from dept,emp where dept.dept1=emp.dept2 and age in (select max(age) from emp group by dept2);
15.求李四的收入及部门名称
命令:
select emp.name,emp.incoming,dept.dept_name from emp inner join dept on emp.dept2=dept.dept1 where emp.name='李四' ;
或
select incoming,dept_name from dept,emp where dept.dept1=emp.dept2 and name='李四';
16.列出每个部门中收入最高的员工姓名,部门名称,收入,并按照收入降序
命令:
select name,dept_name,incoming from dept,emp where dept.dept1=emp.dept2 and incoming in (select max(incoming) from emp group by dept2) order by incoming desc;
17.列出部门员工数大于1个的部门名称
命令:
select dept_name from dept,emp where emp.dept2=dept.dept1 group by dept_name having count(*)>1;
18.查找张三所在的部门名称
命令:
方法一:
select dept_name from dept inner join emp on emp.dept2=dept.dept1 where emp.name='张三';
方法二:
select dept_name from dept,emp where dept.dept1=emp.dept2 and name='张三';