sql

drop table if exists emp;
drop table if exists depart;
create table if not exists depart(
                                   dept_id integer primary key auto_increment
                                     comment '部门id',
                                   dept_name varchar(10) comment '部门名',
                                   dept_des varchar(300) comment '部门介绍'
) comment '部门表';

create table if not exists emp(
                                emp_id integer primary key auto_increment
                                  comment '员工id',
                                emp_name varchar(20) comment '员工姓名',
                                emp_salary integer comment '员工薪资',
                                emp_age integer comment '员工年龄',
                                d_id integer comment '部门id',
                                foreign key (d_id) references depart(dept_id)
);


insert into depart values (null, '研发部','xxxxxx');
insert into depart values (null, '市场部','aaaaaa');
insert into depart values (null, '人事部','bbbb');
insert into depart values (null, '营销部','ccccc');
insert into depart values (null, '行政部','ddd');

insert into emp values (null, '鄂俊峰', 6200, 25, 1);
insert into emp values (null, '张博', 4000, 22, 2);
insert into emp values (null, '杨翰文', 5000, 23, 3);
insert into emp values (null, '于琦', 6000, 18, 4);
insert into emp values (null, '曹帅', 5500, 22, 5);
insert into emp values (null, '杨里生', 4000, 20, 1);
insert into emp values (null, '于志强', 7000, 25, 2);

select *from emp;
# 1. 查询所有员工的部门名称和其薪资
select emp.emp_name,d.dept_name,emp.emp_salary
from emp join depart d
  on emp.d_id = d.dept_id;


# 2. 查询所有员工的名字和其部门描述
select emp.emp_name,d.dept_des from emp join depart d
  on emp.d_id = d.dept_id;

# 3. 查询市场部的所有员工名字和其薪资
select emp_name,emp_salary from emp join depart
on emp.d_id = depart.dept_id and depart.dept_name='市场部';

# 4. 查询市场部的大于22岁的员工名字和其薪资
select emp_name,emp_salary from emp join depart
on emp.d_id = depart.dept_id and depart.dept_name='市场部'and emp.emp_age>22;

# 5. 查询每个部门的名字和该部门的员工的平均薪资

select dept_name,avg(emp.emp_salary) from depart join emp
on  emp.d_id = depart.dept_id
group by dept_name;


# 6. 查询每个部门的名字和该部门的员工的人数
select dept_name,count(*) from depart join emp
on  emp.d_id = depart.dept_id
group by dept_name;

# 7. 查询每个部门中, 年龄大于22岁的员工人数和部门名字
select dept_name,count(*) from depart join emp
on emp.d_id = depart.dept_id
where emp.emp_age>22
group by dept_name;

# 8. 查询平均薪资大于5000的部门和该部门的平均年龄
select dept_name,floor(avg(emp_age))  from depart join emp
on emp.d_id = depart.dept_id
group by dept_name
having avg(emp_salary)>5000;