sql语句练习

``1.查询工资大于等于3000的员工姓名和工资 
select name,sal from emp where sal>=3000

2.查询1号部门的员工姓名和工作
select name,job from emp where dept_id=1;

3.查询不是程序员的员工姓名和工作(两种写法)
select name,job from emp where job!=“程序员”;

4.查询奖金等于300的员工姓名,工资和工作
select name,sal,job from emp where comm=300;

5.查询1号部门工资大于2000的员工信息
select * from emp where dept_id=1 and sal>2000;

6.查询3号部门或工资等于5000的员工信息
select * from emp where dept_id=3 or sal=5000;

7.查询出CEO和项目经理的名字
select name from emp where job=“CEO” or job=“项目经理”;

8.查询工资为3000,1500和5000的员工信息
select * from emp where sal in(3000,1500,5000);

9.查询工资不等于3000,1500和5000的员工信息
select * from emp where sal not in(3000,1500,5000);

10.查询工资在1000到2000之间的员工信息
select * from emp where sal between 1000 and 2000;

11.查询工资在1000到2000以外的员工信息
select * from emp where sal not between 1000 and 2000;

12.查询有领导的员工姓名和领导id
select name,id from emp where manager is not null;

13.查询没有领导的员工姓名和领导id
select name,id from emp where manager is null;

14.查询员工表中出现了哪几种不同的工作
select distinct job from emp;

15.查询员工表中出现了那几个部门的id
select distinct dept_id from emp;

16.查询姓孙的员工姓名
select name from emp where name like “孙%”;

17.查询名字最后一个字是精的员工信息
select * from emp where name like “%精”;

18.查询工作中包含销售的员工信息
select * from emp where job like “%销售%”;

19.查询工作中第二个字是售的员工信息
select * from emp where job like “_售%”;

20.查询名字中包含僧的员工并且工资高于2000的员工信息
select * from emp where name like “%僧%” and sal>2000;

21.查询1号和2号部门中工作以市开头的员工信息
select * from emp where job like “市%” and dept_id in(1,2);

22.查询所有员工的姓名和工资 按照工资升序排序
select name,sal from emp order by sal;

23.查询所有员工的姓名和工资 按照工资降序排序
select name,sal from emp order by sal desc;

24.查询所有员工姓名 工资和部门id 按照部门id降序排序,如果部门id一致则按照工资升序排序
select name,sal,dept_id from emp order by dept_id,sal ;

25.查询员工表中3号部门工资高于1500的员工信息
select * from emp where dept_id=3 and sal>1500;

26.查询2号部门员工或者没有领导的员工信息
select * from emp where dept_id=2 or manager is null;

27.查询有领导的员工姓名,工资按照工资降序排序
select name,sal from emp where manager is not null order by sal desc;

28.查询2号和3号部门的员工姓名和入职日期hiredate 按照入职
日期降序排序
select name,hiredate from emp where dept_id in(2,3) order by hiredate desc;

29.查询名字中包含僧和包含精的员工姓名
select name from emp where name like “%僧%” or name like “%精%”;

30.查询工资高于2000的工作有哪几种?
select distinct job from emp where sal>2000;

31.查询工资最高的前三个员工
select * from emp order by sal desc limit 0,3;

32.查询员工表按照id排序, 第2页的5条数据
select * from emp order by dept_id limit 5,5;

33.查询员工表按照id排序, 第3页的4条数据
select * from emp order by dept_id limit 8,4;

34.查询3号部门工资最低的员工姓名和工资
select name,sal, min(sal) from emp where dept_id=3;

35.查询工作不是人事的员工中工资降序第二页的3条数据
  select job from emp where job!=“人事” order by sal desc limit 3,3;

36.查询每个员工的姓名,工资和年终奖(年终奖=5个月的工资)
select name,sal,sal*5 年终奖 from emp;

37.给3号部门所有员工涨薪5块钱 
update emp set sal=sal+5 where dept_id=3;

38.查询没有领导的员工和3号部门的员工,工资降序取前三条
select name from emp where manager is null and dept_id=3 order by sal desc limit 0,3;

39.查询2号部门的最高工资 
select max(sal) from emp where dept_id=2;

40.查询有领导的员工中工资在1000到2000之间的人数
select name,count() from emp where manager is not null and sal between 1000 and 2000;

41.查询3号部门的工资总和
select sum(sal) from emp where dept_id=3;

42.查询程序员和销售的总人数 
select count(*) from emp where job=“程序员” or job=“销售”;

43.查询1号部门有领导的员工的平均工资 
select avg(sal) from emp where dept_id=1 and manager is not null;

44.查询1号部门的最低工资和最高工资
select min(sal) 最低工资,max(sal) 最高工资 from emp where dept_id=1;

45.查询和销售相关的工作人数
select count() from emp where job like “%销售%”;

46.查询工资不是1500和3000的员工人数
select count(*) from emp where sal not in(1500,3000);

47.查询1号部门出现了哪几种工作
select distinct job from emp;

48.查询名字包含精的员工数量
select count() from emp where name like “%精%”;

49.查询和销售相关的工作一个月工资总和
select sum(sal) from emp where job like “%销售%”;

50.查询2号部门的最高工资和最低工资起别名
select max(sal) 最高工资,min(sal) 最低工资 from emp where dept_id=2;

51.查询每个部门的平均工资
select dept_id,avg(sal) from emp group by dept_id;

52.查询每种工作的平均工资
select job,avg(sal) from emp group by job;

53.查询每个部门的最高工资
select dept_id, max(sal) from emp group by dept_id;

54.查询每种工作的最低工资
select job,min(sal) from emp group by job;

55.查询每个部门工资高于2000的人数
select dept_id,count(*) from emp where sal>2000 group by dept_id;

56.查询每个部门有领导的员工人数
select dept_id,count() from emp where manager is not null group by dept_id;

57.查询1号部门每种工作的最低工资
select dept_id,min(sal) from emp where dept_id=1 group by job;

58.查询平均工资最高的部门id和平均工资
select dept_id,avg(sal) from emp group by dept_id order by avg(sal) desc limit 0,1;

59.查询每个部门的平均工资,要求平均工资大于2000
select dept_id,avg(sal) c from emp group by dept_id having c>2000;

60.查询每种工作的人数,只查询人数大于1的
select job,count(*) a from emp group by job having a>1;

61.查询每个部门的工资总和,只查询有领导的员工, 并且要求工资
总和大于5400.
select dept_id,sum(sal) b from emp where manager is not null group by dept_id having b>5400;

62.查询每个部门的平均工资, 只查询工资在1000到3000之间的,
并且过滤掉平均工资低于2000的
select dept_id,avg(sal) p from emp where sal between 1000 and 3000 group by dept_id having p>2000;

63.查询工资大于2号部门平均工资的员工信息
select * from emp where sal>(select avg(sal) from emp where dept_id=2);

64.查询工资高于程序员最高工资的员工信息
select * from emp where sal>(select max(sal) from emp where job=“程 序员”);
65.查询工资最高的员工信息
select * from emp where sal = (select max(sal) from emp);

66.查询孙悟空的部门信息(用到dept部门表)
select * from dept where id=(select dept_id from emp where name=“孙悟空”);

67.查询和孙悟空相同工作的其它员工信息
select * from emp where job=(select job from emp where name=“孙悟空”) and name!=“孙悟空”;

68.查询最低工资员工的同事信息(同事代表同一部门)
select * from emp where dept_id=(select dept_id from emp where sal=(select min(sal) from emp)) and sal != (select min(sal) from emp);``