create table `dept`(
`dept1` VARCHAR(10) COMMENT "部门id",
`dept_name` VARCHAR(20) COMMENT "部门名称"
) COMMENT "部门类别表";
insert into dept values(101,"chaiwu"),
(102,"yunwei"),
(103,"xiaoshou"),
(104,"jidian"),
(105,"zhubo"),
(106,"HR");
create table `emp`(
`id` varchar(10) COMMENT "员工id",
`name` VARCHAR(25) COMMENT "名字",
`age` VARCHAR(10) COMMENT "年龄",
`worktime` VARCHAR(10) COMMENT "入职时间",
`dept2` varchar(10) COMMENT "部门id",
`incoming` int(10) COMMENT "工资"
) COMMENT "员工信息表";
insert into emp (id,name,age,worktime,incoming,dept2)
VALUES (1500,'wangyi',25,'2022/2/1',8900,101),
(1501,'hufei',27,'2022/3/1',7500,102),
(1502,'heisen',30,'2022/3/20',8900,102),
(1503,'huahudie',25,'2022/8/9',9000,102),
(1504,'baimu',25,'2022/01/01',4000,103),
(1505,'leilei',27,'2016/05/09',27800,106),
(1506,'liuxiaoyi',28,'2017/05/09',16000,105),
(1507,'haiwu',27,'2018/09/06',6300,104),
(1508,'wanggou',22,'2023/2/1',4900,101);
create table `dept`(
`dept1` VARCHAR(10) COMMENT "部门id",
`dept_name` VARCHAR(20) COMMENT "部门名称"
) COMMENT "部门类别表";
create table `emp`(
`id` varchar(10) COMMENT "员工id",
`name` VARCHAR(25) COMMENT "名字",dept
`age` VARCHAR(10) COMMENT "年龄",
`worktime` VARCHAR(10) COMMENT "入职时间",
`dept2` varchar(10) COMMENT "部门id",
`incoming` int(10) COMMENT "工资"
) COMMENT "员工信息表";
列出每个部门里面有那些员工及部门名称;
select dept_name,group_concat(name) from dept left join emp on dept.dept1=emp.dept2 group by dept_name;
运维部门的收入总和;
select sum(incoming) "收入总和"from dept left join emp on dept.dept1=emp.dept2 where dept_name="yunwei";
HR部入职员工的员工号
select id from dept left join emp on dept.dept1=emp.dept2 where dept_name="HR";
财务部门收入超过5000元的员工姓名
select dept_name,incoming from dept left join emnp on dept.dept1=emp.dept2 where dept_name="chaiwu" and incoming>"5000";
找出销售部收入最低的员工的入职时间;
select emp.worktime from dept left join emp on dept.dept1 = emp.dept2 where dept_name="xiaoshou"order by incoming limit 1;
找出年龄小于平均年龄的员工的姓名,ID和部门名称
select id,name,dept_name,age from dept left join emp on dept.dept1 = emp.dept2 where age<(select avg(age)from emp) group by id,name,dept_name,age;
求财务部门最低工资的员工姓名;
select emp.name from dept left join emp on dept.dept1 = emp.dept2 where dept_name="chaiwu" order by incoming limit 1;