2.限定查询

1.查询工资大于1500的雇员信息

select * from emp 
where sal>1500;

2.查询雇员编号不是7369的雇员信息

select * from emp 
where empno!=7369;

3.查询姓名是smith的雇员编号、姓名、工资、入职时间

select empno,ename,sal,hiredate 
from emp 
where ename='smith';

4.查询没有奖金的雇员信息

select * from emp 
where comm is null;

5.查询基本工资大于1000，并且可以获取奖金的雇员姓名、工资、奖金

select ename,sal,comm from emp 
where sal>1000 and comm is not null;

6.查询从事销售工作，或工资大于等于2000的雇员信息

select * from emp 
where job='saleman' or sal>=2000;

7.查询从事销售工作，并且工资不小于1500的雇员的编号、姓名、职位、工资、入职时间

select empno,ename,job,sal,hiredate 
from emp 
where job!='saleman' and sal>=1500;

select empno,ename,job,sal,hiredate
from emp 
where (not job='saleman') and (not sal<1500);

8.查询基本工资大于1500，但小于3000的雇员信息

select * from emp 
where sal>1500 and sal<3000;

select * from emp
where sal between 1500 and 3000;

9.查询1981年入职的雇员编号、姓名、入职时间、所在部门编号

select empno,ename,hiredate,deptno 
from emp
where hiredate between '1981-1-1' and '1981-12-31';

10.查询编号为7369、7499、7788的雇员信息

select * from emp 
where empno=7369 or empno=7499 or empno=7788;

select * from emp
where empno in (7369,7499,7788);

11.查询姓名为smith、allen、king的雇员编号、姓名、入职时间

select empno,ename,hiredate
from emp
where ename in ('smith','allen','king');

12.查询雇员姓名以S开头的雇员信息

select * from emp
where ename like 'S%';

13.查询雇员姓名中包含M的雇员信息

select * from emp
where ename like '%M%';

14.查询从事销售工作，并且姓名长度为4个字符的雇员信息

select * from emp
where job='saleman' and ename like '____';

15.查询1981年入职的雇员编号、姓名、入职时间、所在部门编号

select empno,ename,hiredate,deptno 
from emp 
where hiredate like '1981%';