Chapter 2: 限制性查询和数据的排询

Chapter 2: 限制性查询和数据的排询

select  e.empno, e.ename, e.sal
from emp e
where e.sal > 1500;

select  e.empno, e.ename, e.sal
from emp e
where e.sal between 1500 and 2900;

alter session set NLS_DATE_LANGUAGE = AMERICAN;

select  e.empno, e.ename, e.sal
from emp e
where e.hiredate between '01-JAN-81' and '31-MAY-82';


select  e.empno, e.ename, e.sal
from emp e
where e.hiredate not between '01-JAN-81' and '31-MAY-82';

select  e.empno, e.ename, e.job, e.sal
from emp e
where e.job = 'SALESMAN';

select  e.empno, e.ename, e.job, e.sal
from emp e
where e.job in ('SALESMAN','CLERK','MANAGER');

select  e.empno, e.ename, e.job, e.sal
from emp e
where e.job not in ('ANALYST','PRESIDENT');


select  e.empno, e.ename, e.job, e.sal
from emp e
where e.job like 'SAL%';

select  e.empno, e.ename, e.job, e.sal
from emp e
where e.job like 'S_L_S%';

select  e.empno, e.ename, e.sal, e.hiredate
from emp e
where e.hiredate like '%81';

create table dept_temp
as
select *
from dept;

select * from dept_temp;

insert into dept_temp
values (88, 'IT_RESEARCH','BEIJING');

select  *
from dept_temp dt
where dt.dname like 'IT\_%' escape '\';

select  *
from dept_temp dt
where dt.dname like 'IT~_%' escape '~';

select e.empno, e.ename, e.sal
from emp e
where e.sal >= 1500
order by e.sal;

select e.empno, e.ename, e.sal
from emp e
where e.sal >= 1500
order by e.sal desc;

desc //descending order
asc //ascending order

select e.empno AS "Employee Number", e.ename name ,(500 + e.sal)*12 "Annual Salary"
from emp e
order by "Annual Salary" desc;

select e.empno AS "Employee Number", e.ename name ,(500 + e.sal)*12 "Annual Salary"
from emp e
order by (500 + e.sal)*12 desc;

select e.empno AS "Employee Number", e.ename name ,(500 + e.sal)*12 "Annual Salary"
from emp e
order by 3 desc;

select e.empno AS "Employee Number", e.ename name ,(500 + e.sal)*12 "Annual Salary"
from emp e
order by e.job asc, e.sal desc;

select e.ename, e.job, e.sal
from emp e
order by e.empno;