sql练习

1.查询员工表中的姓名和性别

• select name,sex from emp;

2.查询员工表所有字段

• select * from emp;

3.查询每个员工一年的总收入

• select name,(salary*12+bonus) total from emp;

4.为查询结果指定别名

• select name 员工姓名,hiredate 入职日期 from emp;

5.查询女员工的信息

• select * from emp where sex='女';

6.查询月薪在8000-12000的员工

• select * from emp where salary between 8000 and 12000;

7.查询员工表中叫做“李白”，“鲁班七号”或“瑶”的信息

• select * from emp where name='李白' or name='鲁班七号' or name='瑶';
• select * from emp where name  in('李白','鲁班七号','瑶');

8.查询员工姓名中带“云”的员工信息

• select * from emp where name like '%云%';

9.查询有奖金的员工信息

• select * from emp where bonus is not null;

10.查询2010年1月1日后入职的女员工

• select name,sex,hiredate from emp where hiredate>'2010-1-1' and sex='女';

11.查询薪水从高到低

• select * from emp order by salary desc;

12.查询薪水一样多的员工，再按照奖金由高到低排序

• select * from emp order by salary desc,bonus desc;

13.员工姓名是中文，按照姓名的拼音顺序进行排序

• SELECT name  FROM emp ORDER BY CONVERT(name USING GBK);

14.查询薪水最高的三位员工

• select * from emp order by salary desc limit 0,3;

15.查询按范围显示，小于10000为“低收入”，大于等于10000并且小于20000为“中等收入”，大于20000显示为高收入

• select name,
•              case when salary<10000 then '低收入'
•                   when salary<20000 then '中等收入'
•                         else '高收入'
•              end '薪水等级'
• from emp;

16.统计员工数量、平均月薪、最高月薪、最低月薪以及月薪总和

• select count(*) 员工数量,avg(salary) 平均月薪,max(salary) 最高月薪,min(salary) 最低月薪,sum(salary) 月薪总和 from emp;

17.count(*)和count(bonus)结果是否相同?
     不一样，bonus有空值

• select count(*),count(bonus) from emp;

18.统计每个部门的员工数量和平均月薪

• select d.name,count(e.name),avg(e.salary) from emp e inner join dept d on e.did=d.id group by d.name;
• select id,count(*),avg(salary) from emp group by did;

19.查看平均月薪大于10000的部门

• select d.name,avg(e.salary) from dept d inner join emp e on d.id=e.did group by d.name having avg(e.salary)>10000;

20.查询月薪超过5000，员工数量大于2的部门

• select d.name from dept d inner join emp e on d.id=e.did where e.salary>5000 group by d.name having count(*)>2;

21.查询员工名称及其所在部门

• select e.name,d.name from emp e inner join dept d on e.did=d.id;

22.统计部门员工数量并显示部门名称

• select d.name,count(*) from dept d inner join emp e on d.id=e.did group by d.name;

23.查询每个员工领导的姓名

• select e.name 员工,l.name 领导 from emp e left join leader l on e.mid=l.mid;

24.查询月薪大于员工平均薪资的员工

• select * from emp where salary>(select avg(salary) from emp);

25.哪些部门员工的月薪高于本部门的平均值

• select
• e.name,    e.salary,    d.asalary 
• from
•     emp e,
•     ( select did, AVG( salary ) asalary from emp group by did ) d 
• where
•     e.salary > d.asalary 
•     and e.did = d.did;

26.显示员工信息时，增加一列，用于显示该员工所在部门的人数

• select *,(select count(*) from emp where did=e.did) dept_num from emp e;

27.找出哪些部门中有女性员工

• select * from dept d where exists(select * from emp e where e.sex='女' and e.did=d.id);