oracle例题

1.查询工资高于500或是岗位为manager的雇员的信息，同时还要满足他们的姓名首写字母为大写J：
select * from emp where (sal>500 or job='manager') and ename like 'J%';
2.按工资由低到高的顺序显示员工信息：
select * from emp order by sal asc;注：desc高到低；asc?低到高，升序这是默认的
3.按照部门号升序而雇员的工资降序排列。（知道某个部门最高工资的员工最低工资的员工是谁。）
select * from emp order by deptno,sal desc;
4.使用列的别名排序按升序显示员工年薪信息:
select ename,(sal+nvl(comm,0))*12 年薪 from emp order by 年薪
5.使用子查询。（分组函数：max,min,avg,sum,count.）
select min(sal),max(sal) from emp //在这里查询的列都必须是分组函数。
6.显示工资最高的员工信息：
select ename, sal from emp where sal=(select max(sal) from emp);
注：where?后的列如sal不能直接等于分组函数，Sal=max（sal）是错的。要解决此题就要用到子查询。
7.子查询（分步拆分思想）:显示与smith同一部门的员工信息。?

（1）查询SMITH?所在的部门：
select deptno from emp where ename='SMITH'
（2）显示：
select * from emp where deptno=(select deptno from emp where ename='SMITH');从后边向前进行筛选到结果。
8.查询与SMITH的部门和岗位完全相同的所有雇员的信息。
（1）首先查询SMITH所在的部门和岗位：
select deptno,job from emp where ename='SMITH';
（2）显示最终结果：
select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
（SMITH的记录也会显示出来）?
如果只想把除SMITH之外的记录显示出来：
?
select*from emp where(deptno,job)=(select deptno,job from emp where ename='SMITH') and ename<>'SMITH';
9.查询和10号部门的工作相同的雇员的名字，岗位，工资，部门号。
（1）部门10工作职位：
select distinct job from emp where deptno=10;
（2）显示最终结果：
select * from emp where job in (select distinct job from emp where deptno =10);
10.在多行子查询中使用all操作符。显示工资比部门30所有员工的工资高的员工姓名，工资和部门号?
（1）先查出来30号部门的工资：
select sal from emp where deptno =30;
（2）最终结果：
select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);
比所有30号部
门员工的工资高。还有一种方法：查询条件大于30号部门的最大值即可如（3）?
（3）select ename,sal,deptno from emp where sal>(select max(sal)from emp where deptno=30);
11.显示工资比部门30的任意一个员工的工资高的员工姓名，工资和部门号。?再多行子查询中使用any操作符。
(1) select ename,sal,deptno from emp where sal>any(select sal from emp where deptno=30);
或：
(2) select ename,sal,deptno from emp wher

e sal>(select min(sal) from emp where deptno=30);
12.?显示工资高于平均工资的员工信息。
（1）先求出平均工资：
select avg(sal) from emp;
（2）显示员工工资高于平均工资的员工信息:
select ename,sal from emp where sal>(select avg(sal) from emp);
13．group by 用于对查询的结果进行分组统计。统计各部门，每个岗位的工资之和；
(1) select sum(sal),deptno,job from emp group by deptno,job
统计30号部门，每个岗位的工资之和；
(2)select sum(sal),deptno,job from emp group by deptno,job having deptno=30;
注意：用于分组统计的列必须要出现在查询的列表；having子句用于限制分组显示结果。
14.显示每个部门的平均工资和最高工资。group by用于对查询的结果分组统计。having子句用于限制分组显示结果,进一步筛选。
select avg(sal),max(sal),deptno from emp group by deptno;。//deptNO必须出现在列里面。
15.显示每个部门的每个岗位的平均工资和最低工资。
select avg(sal),max(sal),deptno,job from emp group by deptno,job;
16.显示平均工资低于2000的部门号和它的平均工资。
select avg(sal),deptno from emp group by deptno having avg(sal)<2000;
对数据分组的总结：
1.分组函数只能出现在选择列表，having，order?by子句中。不能出现在如where?列名?=分组函数，不能做条件使用。
2.如果在select语句种同时包含group by，having，order by那么他们的顺序是group by，having，order by。
先分组，再对分组结果进行筛选，还可以进行排序。
select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000 order by avg(sal);
3.在选择列中如果有列,表达式,和分组函数,那么这些列和表达式必须有一个出现在group by子句中,否则会出错.如：
select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)>2000;这里deptno就一定要出现在group?by中。
4.在多表查询时要选择查询条件，否则会出现笛卡尔集，在进行n表查询时至少需要n-1个条件。
总结：一般查询语句涉及到函数的，有关小于最大，大于平均值，最小值这类的，要注意使用子查询。拆分思想很重要。
二：经典多表查询
1.显示雇员名，雇员工资以及所在部门的名称；（涉及到多表查询）
select a1.ename,a1.sal,a2.dname,a1.deptno from emp a1,dept a2 where a1.deptno=a2.deptno;
2.显示部门号为10的部门名，员工名和工资:
（1)先显示所有雇员的部门名，员工名和工资：
select a1.ename,a1.sal,a2.dname,a1.deptno from emp a1,dept a2 where a1.deptno=a2.deptno;
（2）加条件限制显示最终结果：
select a1.ename,a1.sal,a2.dname,a1.deptno from emp a1,dept a2 where a1.deptno=a2.deptno and a1.deptno=10;
3.显示各个员工的姓名，工资，及其工资级别。
select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.

sal between a2.losal and a2.hisal;
4.显示雇员名，雇员工资及所在部门的名字，并按部门排序。
select ename,sal,dname from emp,dept where emp.deptno=dept.deptno order by dept.deptno;
注:group by 后的字段必须出现在select之后。但order by 不用。
5.自连接：自连接是指在同一张表的连接查询。
如：显示”FORD”的上级领导的姓名
（1）显示各个员工对应的上级领导
select boss.ename,boss.empno,work.ename,work.sal,work.empno from work,boss where boss.empno=work.mgr
（2）加限制条件：
select boss.ename,boss.empno,work.ename,work.sal,work.empno from work,boss where boss.empno=work.mgr and work.ename='FORD';
多表查询必须弄明白笛卡尔积：
如果查询的列涉及到A和B两张表，如果没有限制对等条件的话，那么A表的每一行与B表的所有行进行匹配，
这时会返回A.line*B.line行即A表的行数乘以B表的行数，加了查询限制条件之后就会只显示哪些符合条件的记录。