Mysql经典入门练习题(四)

create table employee_test(
  id   int(11) ,
  name   varchar(20),
  sex   varchar(10),
  birthday   date ,
  salary   float(10,2) ,  -- 薪水
  bonus   float(10,2) ,   -- 奖金
  department varchar(20), -- 部门
  resume   varchar(200) -- 简介
 );
  
  
 -- 插入员工数据
 insert  into  employee_test values
 (1,'zhangsan','male','1980-11-25',2000,100,'总裁办','good body'),
 (2,'lisi','male','1980-04-25',1000,200,'研发部','good body'),
 (3,'xiaohong','female','1978-11-25',4000,100,'财务部','good girl'),
 (4,'wangwu','male','1981-01-25',4000,400,'人事部','good body'),
 (5,'zhaoliu','male','1978-12-25',2000,NULL,'人事部','good body'),
 (6,'tianqi','female','1998-05-25',2000,100,'人事部','good girl'),
 (7,'renba','male','1968-10-25',3000,500,'财务部','good body');

题目:

1.查询表中所有员工的姓名和对应的薪水
2.使用汉语展示员工信息。(列名翻译成中文展示)
3.查询姓名为zhangsan的员工的薪水
4.查询姓名为zhangsan的员工的总收入
5.查询薪水大于3000的员工信息
6.查询总收入大于4000的员工的姓名 部门 薪水 奖金
7.查询80后的员工
8.查询所有女性薪水大于4000的
9.查询所有女性薪水大于4000的员工按薪水降序排序
10.查询各个部门员工数量
11.查询各个部门的员工数量及薪水支出
12.查询各个部门的员工数量及总支出
13.查询公司男性和女性的数量
14.查询公司男性员工和女性员工的收入总和
15.查询公司中男性员工的收入总和
16.查询公司中总支出大于9000的部门
17.查询公司中所有z开头员工的平均工资
18.查询公司中z开头员工的工资总和
19.查询公司中z开头员工的总收入

🚴‍♂️
🚴‍♂️
🚴‍♂️
🚴‍♂️
🚴‍♂️
🚴‍♂️
🚴‍♂️
🚴‍♂️
🚴‍♂️
🚴‍♂️
🚴‍♂️
参考答案:

#查询表中所有员工的姓名和对应的薪水
select e.`name`,e.`salary` from employee_test e;
#使用汉语展示员工信息。(列名翻译成中文展示)
select `name` '姓名',sex '性别',birthday '生日',salary '薪水',bonus '奖金',department '部门',resume '简介' from employee_test;
#查询姓名为zhangsan的员工的薪水
select NAME,salary from employee_test where `name` = 'zhangsan';
#查询姓名为zhangsan的员工的总收入
select NAME,salary+bonus from employee_test where `name` = 'zhangsan';
#查询薪水大于3000的员工信息
select * FROM employee_test where salary >= 3000;
#查询总收入大于4000的员工的姓名 部门 薪水 奖金
select `name`,department,salary,bonus from employee_test where (salary + bonus) >= 4000;
#查询80后的员工
select * from employee_test where birthday >= '1980';
#查询所有女性薪水大于4000的
select * from employee_test where salary >= 4000 AND sex = 'female';
#查询所有女性薪水大于4000的员工按薪水降序排序
select * from employee_test where sex = 'female' AND salary >= 4000 ORDER BY salary DESC;
#查询各个部门员工数量
select department,COUNT(*) from employee_test GROUP BY department;
#查询各个部门的员工数量及薪水支出
select department,COUNT(*),SUM(salary) FROM employee_test GROUP BY department;
#查询各个部门的员工数量及总支出
select department,COUNT(*),SUM(salary)+SUM(bonus)AS `count` from employee_test GROUP BY department;
#查询公司男性和女性的数量
select sex,COUNT(*) FROM employee_test GROUP BY sex;
#查询公司男性员工和女性员工的收入总和
select sex,SUM(salary) + SUM(bonus)AS `count` FROM employee_test GROUP BY sex;
#查询公司中男性员工的收入总和
select sex,SUM(salary) + SUM(bonus)AS `count` FROM employee_test GROUP BY sex HAVING sex = 'male';
#查询公司中总支出大于9000的部门
select department,SUM(salary)+SUM(bonus)AS `count` FROM employee_test GROUP BY department HAVING `count` >= 9000;
#查询公司中所有z开头员工的平均工资
select AVG(salary) from employee_test where `name` LIKE 'z%';
#查询公司中z开头员工的工资总和
select SUM(salary) from employee_test where `name` LIKE 'z%';
#查询公司中z开头员工的总收入
select (SUM(salary) + SUM(bonus))AS `count` FROM employee_test where `name` like 'z%';

本系列题目均从网上收集,sql自己重写并修改了一些bug和错误,基本上应该没有错误,如有错误或问题可以评论留言,感谢观看