SQL練習題目（MySQL）

標籤：int   統計   count   and   年齡   select   階段   where   com   

有如下員工表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）