oracle 基本操作练习——简单的sql语句

一.在scott模式下，查询scott模式拥有的数据表。
select * from tab;

二.以下检索均在scott模式下
   1.查询dept和salgrade表的所有记录。
select * from dept;
select * from salgrade;

   2.查询emp表中员工编号，员工名称和职位列。
select empno,ename,job from emp;

   3.检索emp表中sal列，然后将其值调整为1.1倍。
select sal from emp;
update emp set sal = sal*1.1;

   4.查询emp表中empno，ename，job并显示为别名“员工编号”，“员工名称”，“职位”。
select empno as 员工编号, ename as 员工名称, job as 职位 from emp;

   5.显示emp表的所有职位（提示：消除重复记录）。
select distinct job from emp;

   6.显示工资不等于3000，950和800的员工的编号，名称和工资。
select empno as 员工编号, ename as 员工名称, sal as 工资 from emp where sal not in(880,1760)

   7.在emp表中查询以“s”开头的员工名称的信息。
select ename as 员工名称 from emp where ename like 'S%'

   8.查询职务为"PRESIDENT","MANAGER"和"ANALYST"的职工的编号，名称和职位。
select empno as 员工编号, ename as 员工名称, job as 职位 from emp where job in('PRESIDENT','MANAGER','ANALYST')

   9.查询emp表中员工工资在2000到3000之间的员工的编号，姓名和工资。
select empno as 员工编号, ename as 员工名称, sal as 工资 from emp where sal >= 2000 and sal <= 3000

  10.查询emp表中comm列为空的员工的信息。
select empno as 员工编号, ename as 员工名称 from emp where comm is null

  11.在emp表中，显示平均工资大于2000的部门编号和平均工资。
select deptno as 部门编号, avg(sal) as 平均工资 from emp  group by deptno having avg(sal) > 2000

  12.在emp表中，按照部门编号，员工编号升序显示员工的姓名，部门编号及员工编号。
select empno as 员工编号, ename as 员工名称, deptno as 部门编号 from emp  order by deptno asc, empno asc

  13.检索emp和dept表中，职位为“MANAGER”的员工的编号，员工名称和部门名称。
select empno as 员工编号, ename as 员工名称, dname as 部门名称 from emp,dept where emp.deptno = dept.deptno and job = 'MANAGER'

  14.查询emp表中部门名称（dname）为“RESEARCH”的员工编号，员工名称及职位。
select empno as 员工编号, ename as 员工名称, job as 职位 from emp,dept where emp.deptno=dept.deptno and dname='RESEARCH'

  15.在emp表中查询既不是最高工资也不是最低工资的员工的编号，名称及工资。
select empno as 员工编号, ename as 员工名称, sal as 工资 from emp where sal != (select max(sal) from emp) and sal != (select min(sal) from emp)


  16.查询工资大于部门编号为10的任意一个员工工资即可得其他部门的员工编号，名称及工资。
select empno as 员工编号,ename as 员工名称, sal as 工资 from emp where sal > (select min(sal) from emp where deptno = 10)

  17.查询工资大于部门编号为30的所有员工工资的员工编号，名称及工资。
select empno as 员工编号,ename as 员工名称, sal as 工资 from emp where sal > (select max(sal) from emp where deptno = 30)

  18.查询工资大于同职位的平均工资的员工编号，名称及工资。
select empno,ename,job from emp emp1 where emp1.sal > (select avg(sal) from emp emp2 where emp2.job = emp1.job)