/*(1)查询员工薪水以及员工姓名,然后判断,
如果薪水小于1000,显示“薪水低”,
如果1000到2000,显示“中等”,
如果大于2000,显示“薪水不错”。*/
select empname 员工姓名,’薪水低’ 工资情况,sal
from emp2
where sal <1000
union
select empname 员工姓名,’薪水中等’ 工资情况,sal
from emp2
where sal between 1000 and 2000
union
select empname 员工姓名,’薪水高’ 工资情况,sal
from emp2
where sal > 2000
/*3查询名字中包含A
并且在部门MANAGER和部门CLERK就职的员工信息*/
select *
from emp2
where empname like ‘%A%’
AND JOB in (‘MANAGER’,’CLERK’)
/5查询emp中员工的年工资,并由大到小依次排列/
select nvl(sal,0)*12+nvl(comm,0) as “Sal”,empname
from emp2
order by “Sal” desc
/*9查询emp表中1981年雇用的所有员工,
或者是1987年5月雇用的员工*/
select * from emp2
where hiredate between to_date(‘1981-1-1’,’yyyy-mm-dd’)
and to_date(‘1981-12-31’,’yyyy-mm-dd’)
or
hiredate between to_date(‘1987-5-1’,’yyyy-mm-dd’)
and to_date(‘1987-5-31’,’yyyy-mm-dd’)
;
select * from emp2
where to_char(hiredate,’yyyymmdd’) like ‘1981%’
or to_char(hiredate,’yyyymmdd’) like ‘198705%’
;
select * from emp2
where to_char(hiredate,’yyyy’) = ‘1981’
or to_char(hiredate,’yyyymm’) = ‘198705’