首页 / MYSQL / SQL练习题目(MySQL)
SQL练习题目(MySQL)
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SQL练习题目(MySQL),小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含2151字,纯文字阅读大概需要4分钟。
内容图文
![SQL练习题目(MySQL)](/upload/InfoBanner/zyjiaocheng/489/76d43e750146460b9106ac809a279cdd.jpg)
有如下员工表employee:
建表sql为:
CREATE TABLE `employee` (
`id` int(11) NOT NULL,
`name` varchar(50) DEFAULT NULL,
`salary` int(11) DEFAULT NULL,
`deptid` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
1.查出每个部门高于部门平均工资的员工名单
select a.deptid,a.name from employee a,(select deptid,avg(salary) as salary from employee group by deptid) b where a.deptid=b.deptid and a.salary>b.salary;
2、列出各个部门中工资高于本部门平均工资的员工数量和部门编号,并按部门编号排序
select a.deptid,count(a.id) as num from employee a,(select deptid,avg(salary) as salary from employee group by deptid) b where a.deptid=b.deptid and a.salary>b.salary group by a.deptid order by a.deptid;
3.求每个部门工资小于130000的人员的平均薪水
select deptid,avg(salary) as average_salary from employee group by deptid having avg(salary)<130000;
4、统计各个薪水阶段的员工数量和占比
select salary_type,count(id) as num,count(id)*100/(select count(id) from employee) as percent from (select id,case when salary<120000 then ‘<12w‘ when salary>=120000 and salary<150000 then ‘12w~15w‘ else ‘>15w‘ end as salary_type from employee) a group by salary_type;
5.统计各个部门各个年龄段的平均薪水
方法一:
select deptid, sum(case when age < 20 then salary else 0 end) / sum(case when age <20 then 1 else 0 end) as ‘20岁以下平均工资‘, sum(case when age >= 20 and age <30 then salary else 0 end) / sum(case when age >= 20 and age <30 then 1 else 0 end) as ‘20至30岁平均工资‘, sum(case when age >= 30 then salary else 0 end) / sum(case when age >=30 then 1 else 0 end) as ‘>30岁及以上平均工资‘ from employee group by deptid;
方法二:
select deptid, age_type,avg(salary) as salary from (select id,salary,deptid,case when age <=20 then ‘20岁以下平均工资‘ when age>20 and age<=30 then ‘20至30岁平均工资‘ else ‘大于30岁平均工资‘ end as age_type from employee) a group by deptid,age_type;
SQL练习题目(MySQL)
标签:int 统计 count and 年龄 select 阶段 where com
本文系统来源:http://www.cnblogs.com/zhengyyao/p/7295113.html
内容总结
以上是互联网集市为您收集整理的SQL练习题目(MySQL)全部内容,希望文章能够帮你解决SQL练习题目(MySQL)所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。