``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);``