sql练习

2024-06-16 15:18| 来源: 网络整理| 查看: 265

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 salary5000 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.哪些部门员工的月薪高于本部门的平均值

selecte.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);

【本文地址】

公司简介

联系我们