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<=;
From:http://tw.wingwit.com/Article/program/Oracle/201311/18907.html
