目录
三、有一张员工表emp,字段:姓名name,性别sex,部门depart,工资salary。
一、【交大思诺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;
二、【同程艺龙2020届校招笔试题】
有员工表、部门表和薪资表,根据查询条件写出对应的sql
现在有员工表、部门表和薪资表。部门表depart的字段有depart_id, name;员工表 staff 的字段有 staff_id, name, age, depart_id;薪资表salary 的字段有 salary_id,staff_id,salary,month。
(问题a):求每个部门'2016-09'月份的部门薪水总额
(问题b):求每个部门的部门人数,要求输出部门名称和人数
(问题c):求公司每个部门的月支出薪资数,要求输出月份和本月薪资总数
--创建表,写入数据 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');
--求每个部门的部门人数,要求输出部门名称和人数 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;
三、有一张员工表emp,字段:姓名name,性别sex,部门depart,工资salary。
查询以下数据:
- 查询男女员工的平均工资
- 查询各部门的总薪水
- 查询总薪水排名第二的部门
- 查询姓名重复的员工信息
- 查询各部门薪水大于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;