emp 员工表(empno 员工号/ename 员工姓名/job 工作/mgr 上级编号/hiredate 受雇日期/sal 薪金/comm 佣金/deptno 部门编号)
dept 部门表(deptno 部门编号/dname 部门名称/loc 地点)
工资＝ 薪金＋ 佣金
．列出至少有一个员工的所有部门
．列出薪金比SMITH多的所有员工
．列出所有员工的姓名及其直接上级的姓名
．列出受雇日期早于其直接上级的所有员工
．列出部门名称和这些部门的员工信息同时列出那些没有员工的部门
．列出所有CLERK（办事员）的姓名及其部门名称
．列出最低薪金大于 的各种工作
．列出在部门SALES（销售部）工作的员工的姓名假定不知道销售部的部门编号
．列出薪金高于公司平均薪金的所有员工
．列出与SCOTT从事相同工作的所有员工
．列出薪金等于部门 中员工的薪金的所有员工的姓名和薪金
．列出薪金高于在部门 工作的所有员工的薪金的员工姓名和薪金
．列出在每个部门工作的员工数量平均工资和平均服务期限
．列出所有员工的姓名部门名称和工资
．列出所有部门的详细信息和部门人数
．列出各种工作的最低工资
．列出各个部门的MANAGER（经理）的最低薪金
．列出所有员工的年工资按年薪从低到高排序
select dname from dept where deptno in(
select deptno from emp);
select * from emp where sal>(
select sal from emp where ename=SMITH);
select aename(
select ename from emp b where bempno=amgr) as bossname from emp a;
select aename from emp a where ahiredate<(
select hiredate from emp b where bempno=amgr);
select adnamebempnobenamebjobbmgrbhiredatebsalmbdeptno
from dept a left join emp b on adeptno=bdeptno;
select aenamebdname from emp a join dept b
on adeptno=bdeptno and ajob=CLERK;
select distinct job as HighSalJob from emp group by job having min(sal)>;
select ename from emp where deptno=(
select deptno from dept where dname=SALES);
select ename from emp where sal>(
select avg(sal) from emp);
select ename from emp where job=(
select job from emp where ename=SCOTT);
select aenameasal from emp a where asal in (
select bsal from emp b where bdeptno=) and adeptno<>;
select enamesal from emp where sal>(
select max(sal) from emp where deptno=);
select
(select bdname from dept b where adeptno=bdeptno) as deptname
count(deptno) as deptcount
avg(sal) as deptavgsal
from emp a group by deptno;
select
aename
(select bdname from dept b where bdeptno=adeptno) as deptname
sal
from emp a;
select
adeptno
adname
aloc
(select count(deptno) from emp b where bdeptno=adeptno group by bdeptno) as deptcount
from dept a;
select jobavg(sal) from emp group by job;
select deptnomin(sal) from emp where job=MANAGER group by deptno;
select ename(sal+nvl(comm))* as salpersal from emp order by salpersal;
ORACLE 子句查询分组等
A同表子查询作为条件
a 给出人口多于Russia(俄国)的国家名称SELECT name FROM bbc
WHERE population>
(SELECT population FROM bbc
WHERE name=Russia)
b给出India(印度) Iran(伊朗)所在地区的所有国家的所有信息SELECT * FROM bbc
WHERE region IN
(SELECT region FROM bbc
WHERE name IN (IndiaIran))
c给出人均GDP 超过United Kingdom(英国)的欧洲国家 SELECT name FROM bbc
WHERE region=Europe AND gdp/population >
(SELECT gdp/population FROM bbc
WHERE name=United Kingdom)
d这个查询实际上等同于以下这个:
select eename from emp e(select empno from emp where ename = KING) e whe
re emgr = eempno;
你可以用EXISTS 写同样的查询你只要把外部查询一栏移到一个像下面这样的子查询环境中就可以了
select ename from emp e
where exists (select from emp where emgr = empno and ename = KING);
当你在一个WHERE 子句中写EXISTS 时又等于向最优化传达了这样一条信息即你想让外部查询先运行使用每一个值来从内部查询（假定EXISTS＝由外而内）中得到一个值
B异表子查询作为条件
aselect * from studentExam where studentid=( select studentid from student where name=吴丽丽);
bselect * from studentexam where studentid in (select studentid from student) order by studentid;
cselect * from student where studentid in (select studentid from studentexam where mark>);
select studentexammarkstudentexamstudentid as seid studentstudentidstudentname from studentexamstudent where studentstudentid=studentexamstudentid;
过滤分组:
顺序为先分组再过滤最后进行统计(实际值)
select studentidcount(*) as highpasses from studentexamwhere mark>group by studentid;
假使我们不想通过数据表中的实际值而是通过聚合函数的结果来过过滤查询的结果
select studentidavg(mark) as averagemarkfrom studentexamwhere avg(mark)< oravg(mark)>group by studentid;(此句错误where 句子是不能用聚合函数作条件的)此时要用having
select studentidavg(mark) from studentexam group by studentid having avg(mark)> or avg(mark)<;
select studentidavg(mark) from studentexam where studentid in()group by
studentid having avg(mark)>;(先分组再过滤再having 聚合最后再统计)
select studentid avg(mark) as averagemarkfrom studentexamwhere examid in()group by studentidhaving avg(mark)< or avg(mark)>;
返回限定行数查询:
select name from student where rownum<=;
oracle 中使用rownum 关键字指定但该关键字必须在where 子句中与一个比较运算符一起指定而不能与order by 一起配合便用因为rownum 维护的是原始行号如果需要用group
by\order by 就用子句查询作表使用的方法:
select studentidaveragemark from(select studentidavg(mark) as averagemarkfrom
studentexamgroup by studentid order by averagemark desc)where rownum<=;
