MySQL练习，几道简单的sql习题

一、有一张员工表emp，字段：姓名name，性别sex，部门depart，工资salary。查询以下数据：

---

1. 查询男女员工的平均工资
2. 查询各部门的总薪水
3. 查询总薪水排名第二的部门
4. 查询姓名重复的员工信息
5. 查询各部门薪水大于10000的男性员工的平均薪水

--创建员工数据表
drop table if exists emp;
create table emp(
	id int primary key auto_increment,
	name varchar(20),
	sex varchar(1),
	depart varchar(10),
	salary int
);

insert into emp (name,sex,depart,salary) values
('张三','男','人事',12000),
('李四','女','财务',8000),
('赵五','女','人事',9000),
('李一','女','物流',22000),
('王六','女','人事',4000),
('董七','男','企划',2000),
('陈八','男','财务',10000),
('耿九','男','财务',1000),
('孙二','女','人事',19000),
('李十','男','物流',7000),
('张三三','男','企划',8000),
('李四四','男','企划',12000),
('王五五','男','物流',9000),
('李十','女','人事',22000),
('李十','女','财务',8000),
('张三三','女','人事',9000),
('董七','男','物流',22000),
('陈八','女','人事',4000);

--1
select sex,avg(salary) from emp group by sex;

--2
select depart,sum(salary) from emp group by depart;

--3
select depart,sum(salary) from emp group by depart order by sum(salary) desc;
select depart,sum(salary) from emp group by depart order by sum(salary) desc limit 1 offset 1;

--4
select name,count(name) from emp group by name having count(name)>1;

--5
select depart,avg(salary) from emp where salary>10000 and sex='男' group by depart;

二、【同程艺龙2020届校招笔试题】有员工表、部门表和薪资表，根据查询条件写出对应的sql

---

现在有员工表、部门表和薪资表。部门表depart的字段有depart_id， name；员工表 staff 的字段有 staff_id， name， age， depart_id；薪资表salary 的字段有 salary_id，staff_id，salary，month。

1. 求每个部门’2016-09’月份的部门薪水总额
2. 求每个部门的部门人数，要求输出部门名称和人数
3. 求公司每个部门的月支出薪资数，要求输出月份和本月薪资总数

--创建表，写入数据
create table depart(
	depart_id int primary key auto_increment,
	name varchar(20)
);

create table staff(
	staff_id int primary key auto_increment,
	name varchar(20),
	age int,
	depart_id int,
	foreign key (depart_id) references depart (depart_id)
);

create table salary(
	salary_id int primary key auto_increment,
	staff_id int,
	salary int,
	month timestamp,
	foreign key (staff_id) references staff (staff_id)
);
 
insert into depart (name) values
('运营部'),
('财务部'),
('人力资源部'),
('策划部');

insert into staff (name,age,depart_id) values
('张一',19,1),
('李一',26,1),
('赵一',29,1),
('张二',19,2),
('李二',26,2),
('赵二',29,2),
('张三',19,3),
('李三',22,3),
('赵三',29,3),
('李四',25,4),
('赵四',24,4),
('王四',23,4);

insert into salary (staff_id,salary,month) values
(1,5900,'2016-9-1'),
(1,6900,'2016-8-1'),
(2,13000,'2016-9-1'),
(2,9000,'2016-8-1'),
(3,9000,'2016-9-1'),
(3,8000,'2016-8-1'),
(4,10200,'2016-9-1'),
(4,15200,'2016-8-1'),
(5,10500,'2016-9-1'),
(5,9500,'2016-8-1'),
(6,11000,'2016-9-1'),
(6,15000,'2016-8-1'),
(7,9000,'2016-9-1'),
(7,8300,'2016-8-1'),
(8,9200,'2016-9-1'),
(8,12000,'2016-8-1'),
(9,12500,'2016-9-1'),
(9,16000,'2016-8-1'),
(10,8500,'2016-9-1'),
(10,9800,'2016-8-1'),
(11,10000,'2016-9-1'),
(11,9000,'2016-8-1'),
(12,10000,'2016-9-1'),
(12,11000,'2016-8-1');

--求每个部门'2016-09'月份的部门薪水总额
select
	depart.depart_id,
	depart.name,
	sum(salary.salary)
from
	salary
	join staff sta on sta.staff_id = salary.staff_id
	join depart on depart.depart_id = sta.depart_id
where
	salary.month = '2016-9-1'
group by
	depart.depart_id;

--求每个部门的部门人数，要求输出部门名称和人数
select
	depart.name,
	count(*)
from
	staff
	join depart on depart.depart_id = staff.depart_id
where
	staff.depart_id = depart.depart_id
group by
	depart.depart_id;

--求公司每个部门的月支出薪资数，要求输出月份和本月薪资总数
select
	depart.name,
	sum(salary.salary),
	salary.month
from
	salary
	join staff on staff.staff_id = salary.staff_id
	join depart on depart.depart_id = staff.depart_id
group by
	depart.depart_id,
	salary.month;

三、【交大思诺2020届校招笔试题】写出以下数据库的查询条件

---

有两个表分别如下：
表A(varchar(32) name,int grade)
数据：zhangshan 80,lisi 60,wangwu 84
表B(varchar(32) name,int age)
数据：zhangshan 26,lisi 24,wangwu 26,wutian 26
写SQL语句得到如下查询结果：

NAME	GRADE	AGE
zhangshan	80	26
lisi	60	24
wangwu	84	26
wutian	null	26

--创建表，写入数据
create table A(name varchar(32) primary key, grade int);

create table B(
	name varchar(32) primary key,
	age int
);

insert into
	A(name, grade)
values
('zhangshan', 80),
('lisi', 60),
('wangwu', 84);


insert into
	B(name, age)
values
('zhangshan', 26),
('lisi', 24),
('wangwu', 26),
('wutian', 26);

select
	B.name NAME,
	A.grade GRADE,
	B.age AGE
from
	A
	right join B on A.name = B.name;