[新手向]Oracle常用查询实例（一）

以下查询均只涉及scott用户下的emp表

1.查询scott用户下的所有表

> select * from tabs;

2.查询雇员表中的全部信息

> select * from emp;

3.查询雇员编号，姓名，工作，工资。

> select empno,ename,job,sal from emp;

4.查询雇员编号，姓名，工作，工资，列表标题需中文显示。

> select empno 编号, ename 姓名, job 工作, sal 工资 from emp;

5.查询雇员工作种类。

> select distinct job from emp;

6.查询所有雇员编号,姓名,工作.按以下格式显示:编号:7369-姓名:SMITH-工作:CLERK (返回一个列)

> select  '编号：' || empno || '-姓名：' || ename || '-工作：' || job as 详情 from emp;

7.查询雇员编号,姓名,工作,年薪

> select empno,ename,job,(sal + nvl(comm,0))*12 YearlySalary from emp;

8.查询工资大于1500的雇员所有信息

> select * from emp where sal >1500;

9.查询可以得到奖金的雇员的名字与奖金

> select ename,comm from emp where NVL(comm,0)>0;

> select ename,comm from emp where DECODE(comm,null,0,comm) > 0;

10.查询工资大于1500或可以得到奖金的雇员

> select ename from emp where sal > 1500 or nvl(comm,0) > 0;

> select ename from emp where sal > 1500 or DECODE(comm,null,0,comm) > 0;

11.查询工资大于1500并且可以领取奖金的雇员

> select ename from emp where sal > 1500 and nvl(comm,0) > 0;

> select ename from emp where sal > 1500 and DECODE(comm,null,0,comm) > 0;

12.查询工资不大于1500或者不可以领取奖金的雇员

> select ename from emp where sal <= 1500 or nvl(comm,0) = 0;

13.查询工资在1500到3000的所有雇员信息

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

14.查询系统当前时间

> select sysdate from dual;

15.查询在1981年雇用的员工信息

> select * from emp where hiredate like '%81%';

> select * from emp where to_char(hiredate,'YYYY') = '1981';

> select * from emp where hiredate <= to_date('1981-12-31','YYYY-mm-dd') and hiredate >= to_date('1981-01-01','YYYY-mm-dd');

> select * from emp where hiredate between to_date('1981-01-01','YYYY-mm-dd') and to_date('1981-12-31','YYYY-mm-dd');

16.查询雇员姓名中第三个字母为”A”的雇员信息

> select * from emp where ename like '__A%';

17.查询雇员编号为7369的雇员信息

> select  * from emp where empno = '7369';

18.查询雇员编号不为7369的雇员信息

> select  * from emp where empno != '7369';

> select  * from emp where empno <> '7369';

19.查询编号是7369,7900的雇员信息

> select * from emp where empno in (7369,7900);

20.查询编号不是7369,7900的雇员信息

> select * from emp where empno not in (7369,7900);

21.查询雇员信息,按工资由低到高排序

> select * from emp order by sal;

> select * from emp order by sal asc;

22.查询雇员信息,按工资由高到低排序

> select * from emp order by sal desc;

23.请查询没有领导的员工名和职位

> select ename,job from emp where mgr is null;

24.查询有领导的员工名和职位

> select ename,job from emp where mgr is not null;

25.查询所有员工名、职位以及领导名

> select e1.ename,e1.job,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno(+);

26.查询部门30中的所有员工信息

> select * from emp where deptno = 30;

27.列出所有办事员(CLERK)的姓名,编号和部门编号(按部门编号升序排列)

> select ename,empno,deptno from emp where job = 'CLERK' order by deptno;

28.找出佣金高于薪金的员工

> select * from emp where nvl(comm,0) > sal;

29.找出佣金低于薪金的40%的员工信息

> select * from emp where nvl(comm,0) < sal*0.4;

30.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料

> select *  from emp  where deptno = '10' and job = 'MANAGER'  union   select *  from emp where job = 'CLERK' and deptno = '20';

> select *  from emp  where (deptno = '10' and job = 'MANAGER') or (job = 'CLERK' and deptno = '20');