?

一、涉及內(nèi)容

　　1.掌握SELECT語句的多表連接查詢。

　　2.掌握SELECT語句的子查詢。

二、具體操作

（一）根據(jù)Oracle數(shù)據(jù)庫scott方案下的emp表和dept表，完成下列操作：

? 1.查詢所有工種為CLERK的員工的姓名及其部門名稱。

select ename,dname
from scott.emp t1 inner join scott.dept t2 on t1.deptno=t2.deptno
where job='CLERK';

? 2.查詢所有部門及其員工信息，包括那些沒有員工的部門。

select * from scott.emp t1 right join scott.dept t2 on t1.deptno=t2.deptno

? 3.查詢所有員工及其部門信息，包括那些還不屬于任何部門的員工。

select * from scott.emp t1 left join scott.dept t2 on t1.deptno=t2.deptno

? 4.查詢在SALES部門工作的員工的姓名信息。

?

用子查詢實(shí)現(xiàn):

select * from scott.emp where deptno=(select deptno from scott.dept where dname='SALES')

用連接查詢實(shí)現(xiàn):

?

select * from scott.emp t1 inner join scott.dept t2 on t1.deptno=t2.deptno
where t2.dname='SALES';

?

?

注意兩種實(shí)現(xiàn)方式，在行和列上的變化。

? 5.查詢所有員工的姓名及其直接上級的姓名。

select t1.ename as 員工姓名,t2.ename 經(jīng)理姓名 from scott.emp t1,scott.emp t2 where t1.mgr=t2.empno;

? 6.查詢?nèi)肼毴掌谠缬谄渖霞夘I(lǐng)導(dǎo)的所有員工的信息。

select t1.ename as 員工姓名,t2.ename 經(jīng)理姓名 from scott.emp t1,scott.emp t2 where t1.mgr=t2.empno and t1.hiredate<t2.hiredate

? 7.查詢從事同一種工作但不屬于同一部門的員工信息。

select t1.ename,t1.job,t1.deptno,t2.ename,t2.job,t2.deptno from scott.emp t1 cross join scott.emp t2where t1.job=t2.job and t1.deptno <>t2.deptno

? 8.查詢10號部門員工及其領(lǐng)導(dǎo)的信息。

select t1.ename as 員工姓名,t2.ename 經(jīng)理姓名 from scott.emp t1,scott.emp t2 where t1.mgr=t2.empno and t1.deptno=10;

? 9.使用UNION將工資大于2500的雇員信息與工作為ANALYST的雇員信息合并。

select * from scott.emp where sal>2500 union select * from scott.emp where job='ANALYST'

?10.通過INTERSECT集合運(yùn)算，查詢工資大于2500，并且工作為ANALYST的雇員信息。

select * from scott.emp where sal>2500 intersect select * from scott.emp where job='ANALYST

?11.使用MINUS集合查詢工資大于2500，但工作不是ANALYST的雇員信息。

select * from scott.emp where sal>2500 minus select * from scott.emp where job='ANALYST';

?12.查詢工資高于公司平均工資的所有員工信息。

select * from scott.emp where sal>(select avg(sal) from scott.emp)

?13.查詢與SMITH員工從事相同工作的所有員工信息。

select * from scott.emp where job=(select job from scott.emp where ename='SMITH')

?14.查詢工資比SMITH員工工資高的所有員工信息。

select * from scott.emp where sal>(select sal from scott.emp where ename='SMITH')

?15.查詢比所有在30號部門中工作的員工的工資都高的員工姓名和工資。

select ename,sal from scott.emp where sal>all(select sal from scott.emp where deptno=30)

?16.查詢部門人數(shù)大于5的部門的員工信息。

select * from scott.emp
where deptno in (select deptno from scott.emp group by deptno having count(*)>5);

?17.查詢所有員工工資都大于2000的部門的信息。

select * from scott.dept where deptno in(select deptno from scott.emp group by deptno having min(sal)>2000)

?18.查詢?nèi)藬?shù)最多的部門信息。

select * from scott.dept where deptno in (select deptno from (select deptno,count(*) as 人數(shù)from scott.emp group by deptno) where 人數(shù)=(select max(人數(shù)) from(select deptno,count(*) as 人數(shù) from scott.emp group by deptno)));

?19.查詢至少有一個員工的部門信息。

select * from scott.dept where deptno in(select deptnofrom scott.emp group by deptno having count(*)>=1)

?20.查詢工資高于本部門平均工資的員工信息。

select * from scott.emp e where sal>(select avg(sal)from scott.emp group by deptno having e.deptno=deptno);

?21.查詢工資高于本部門平均工資的員工信息及其部門的平均工資。

select * from((select * from scott.emp ewhere sal>(select avg(sal)from scott.emp group by deptno having e.deptno=deptno)) t1 inner join(select avg(sal),deptno from scott.emp group by deptno) t2 on t1.deptno=t2.deptno);

?22.查詢每個員工的領(lǐng)導(dǎo)所在部門的信息。

select * from scott.dept where deptno in(select distinct deptno from scott.emp where empno in(select distinct mgrfrom scott.emp));

?23.查詢平均工資低于2000的部門及其員工信息。

select * from scott.emp t1,scott.dept t2 where t1.deptno=t2.deptno and t1.deptno in(select deptnofrom scott.emp group by deptno having avg(sal)<2000)

?

（二）習(xí)題

? 1.如果需要將雇員表中的所有行連接到雇員表中的所有行，則應(yīng)創(chuàng)建哪種類型的連接？（B）

??? A.等值連接??? B.笛卡爾乘積 ?C.內(nèi)連接? D.外連接

? 2.如果需要從顧客表和訂單表中查詢所有顧客及其下達(dá)的所有訂單，并且要求查詢結(jié)果中先按顧客所在公司名稱的升序排列，再按訂單金額的降序排列。應(yīng)執(zhí)行以下哪條語句？（B）

A.SELECT c.顧客標(biāo)識,c.公司名稱,o.訂單日期,o.顧客標(biāo)識,o.金額

? FROM 顧客 c,訂單 o

? WHERE c.顧客標(biāo)識=o.顧客標(biāo)識

? ORDER BY 金額 DESC, 公司名稱;

B. SELECT c.顧客標(biāo)識,c.公司名稱,o.訂單日期,o.顧客標(biāo)識,o.金額

? FROM 顧客 c,訂單 o

? WHERE c.顧客標(biāo)識=o.顧客標(biāo)識

? ORDER BY公司名稱,金額 DESC;

C. SELECT c.顧客標(biāo)識,c.公司名稱,o.訂單日期,o.顧客標(biāo)識,o.金額

??FROM 顧客 c,訂單 o

? WHERE c.顧客標(biāo)識=o.顧客標(biāo)識

? ORDER BY公司名稱,金額;

D. SELECT c.顧客標(biāo)識,c.公司名稱,o.訂單日期,o.顧客標(biāo)識,o.金額

? FROM 顧客 c,訂單 o

? WHERE c.顧客標(biāo)識=o.顧客標(biāo)識

? ORDER BY公司名稱 ASC,金額 DESC;

?3.評估以下SQL語句：

?? ?SELECT e.雇員標(biāo)識,e.姓氏,e.名字,d.部門名稱

? FROM 雇員 e,部門 d

? WHERE e.部門標(biāo)識=d.部門標(biāo)識

??AND 雇員.部門標(biāo)識>5000

? ORDER BY 4;

哪個字句的語法有錯誤？（E）

A. SELECT e.雇員標(biāo)識,e.姓氏,e.名字,d.部門名稱?

B. FROM 雇員 e,部門 d

C. WHERE e.部門標(biāo)識=d.部門標(biāo)識

D. AND 雇員.部門標(biāo)識>5000

E. ORDER BY 4;

?4.評估以下語句：

? ??SELECT 部門標(biāo)識,AVG(薪金)

? FROM 雇員

? WHERE 職務(wù)標(biāo)識<> 69 879

? GROUP BY 部門標(biāo)識

? HAVING AVG(薪金)>35 000

? ORDER BY部門標(biāo)識;

哪些子句限制了返回結(jié)果？請選擇兩個正確答案。（BD）

A. SELECT 部門標(biāo)識,AVG(薪金)

B. WHERE 職務(wù)標(biāo)識<> 69 879

C. GROUP BY 部門標(biāo)識

D. HAVING AVG(薪金)>35 000

?

5.在SELECT語句中各個子句的正確順序是什么？（C）

A. SELECT

?? FROM

?? WHERE

?? ORDER BY

?? GROUP BY

?? HAVING

B. ?SELECT

?? ?FROM

HAVING??

??? ORDER BY

WHERE

??? GROUP BY

??

C. ?SELECT

?? ?FROM

WHERE

GROUP BY

HAVING??

??? ORDER BY

???

D. ?SELECT

?? ?FROM

WHERE

HAVING??

??? ORDER BY

??? GROUP BY

?6.以下哪個運(yùn)算符可用于多行子查詢？（A）

??? A.IN?? ???????? ?B.<> ????????? C.=? ?????????? D.LIKE

?

?7.假設(shè)數(shù)據(jù)庫中有顧客表和訂單歷史記錄表。其中，顧客表中包括：客戶標(biāo)識NUMBER（5）、姓名VARCHAR2（25）、信貸限額NUMBER（8,2）、開戶日期（DATE）；訂單歷史記錄表中包括：訂單標(biāo)識NUMBER（5）、客戶標(biāo)識NUMBER（5）、訂單日期（DATE）、總計NUMBER（8,2）。以下哪種方案需要使用子查詢來返回需要的結(jié)果？（D）

A.需要顯示每個顧客賬戶下的開戶日期

B.需要顯示顧客下達(dá)訂單的各個日期

C.需要顯示在特定日期下達(dá)的所有訂單

D.需要顯示與編號為25950的訂單的下達(dá)日期相同的所有訂單

8.如果希望在報表中顯示成本值高于所有產(chǎn)品平均成本的產(chǎn)品名稱，應(yīng)使用以下哪些SELECT語句？（B）

A. SELECT 產(chǎn)品名稱 FROM 產(chǎn)品 WHERE 成本>（SELECT AVG（成本） FROM 產(chǎn)品）；

B. SELECT 產(chǎn)品名稱 FROM 產(chǎn)品 WHERE 成本> AVG（成本）；

C. SELECT AVG（成本）, 產(chǎn)品名稱 FROM 產(chǎn)品 WHERE 成本> AVG（成本）GROUP BY 產(chǎn)品名稱；

D. SELECT 產(chǎn)品名稱 FROM（SELECT AVG（成本） FROM 產(chǎn)品） WHERE 成本> AVG（成本）；

9.如果單行子查詢返回了空值且使用了等于比較運(yùn)算符，外部查詢會返回什么結(jié)果？（B）

?A.不返回任何行?? ????? ???B.返回表中的所有行

?C.返回空值????? ?????? ???D.返回錯誤

10.如果需要創(chuàng)建包含多行子查詢的SELECT語句，可以使用哪個（些）比較運(yùn)算符？（A）

?

A.IN、ANY和ALL?? ????? ???B.LIKE

C.BETWEEN…AND…???? ?? ???D.=、< 和 >

?

來源：https://www.cnblogs.com/shenxiaolin/p/5514072.html

轉(zhuǎn)載于:https://www.cnblogs.com/CaptainFM/p/10689887.html

總結(jié)

以上是生活随笔為你收集整理的Oracle数据库——SQL高级查询的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。