Oracle資料庫——SQL進階查詢

標籤：大於   完成   集合運算   oss   注意   包括   資料庫   varchar2   count   

 

一、涉及內容

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

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

二、具體操作

（一）根據Oracle資料庫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部門工作的員工的姓名資訊。

用子查詢實現:

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

用串連查詢實現:

 

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

 

 

注意兩種實現方式，在行和列上的變化。

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

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

  6.查詢入職日期早於其上級領導的所有員工的資訊。

select t1.ename as 員工姓名,t2.ename 經理姓名 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 t2 where t1.job=t2.job and t1.deptno <>t2.deptno

  8.查詢10號部門員工及其領導的資訊。

select t1.ename as 員工姓名,t2.ename 經理姓名 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集合運算，查詢工資大於2500，並且工作為ANALYST的僱員資訊。

select * from scott.emp where sal>2500 intersectselect * from scott.emp where job=‘ANALYST

 11.使用MINUS集合查詢工資大於2500，但工作不是ANALYST的僱員資訊。

select * from scott.emp where sal>2500minusselect * 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.查詢部門人數大於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.查詢人數最多的部門資訊。

 

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

 

 19.查詢至少有一個員工的部門資訊。

select * from scott.dept where deptno in(select deptno 　　　　　　　　　　from 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 e                     where 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.查詢每個員工的領導所在部門的資訊。

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

 23.查詢平均工資低於2000的部門及其員工資訊。

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

 

（二）習題

  1.如果需要將僱員表中的所有行串連到僱員表中的所有行，則應建立哪種類型的串連？（B）

    A.等值串連    B.笛卡爾乘積  C.內串連  D.外串連

  2.如果需要從顧客表和訂單表中查詢所有顧客及其下達的所有訂單，並且要求查詢結果中先按顧客所在公司名稱的升序排列，再按訂單金額的降序排列。應執行以下哪條語句？（B）

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

  FROM 顧客 c,訂單 o

  WHERE c.顧客標識=o.顧客標識

  ORDER BY 金額 DESC, 公司名稱;

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

  FROM 顧客 c,訂單 o

  WHERE c.顧客標識=o.顧客標識

  ORDER BY公司名稱,金額 DESC;

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

  FROM 顧客 c,訂單 o

  WHERE c.顧客標識=o.顧客標識

  ORDER BY公司名稱,金額;

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

  FROM 顧客 c,訂單 o

  WHERE c.顧客標識=o.顧客標識

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

 3.評估以下SQL語句：

    SELECT e.僱員標識,e.姓氏,e.名字,d.部門名稱

  FROM 僱員 e,部門 d

  WHERE e.部門標識=d.部門標識

  AND 僱員.部門標識>5000

  ORDER BY 4;

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

A. SELECT e.僱員標識,e.姓氏,e.名字,d.部門名稱 

B. FROM 僱員 e,部門 d

C. WHERE e.部門標識=d.部門標識

D. AND 僱員.部門標識>5000

E. ORDER BY 4;

 4.評估以下語句：

    SELECT 部門標識,AVG(薪金)

  FROM 僱員

  WHERE 職務標識<> 69 879

  GROUP BY 部門標識

  HAVING AVG(薪金)>35 000

  ORDER BY部門標識;

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

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

B. WHERE 職務標識<> 69 879

C. GROUP BY 部門標識

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.以下哪個運算子可用於多行子查詢？（A）

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

 

 7.假設資料庫中有顧客表和訂單記錄表。其中，顧客表中包括：客戶標識NUMBER（5）、姓名VARCHAR2（25）、信貸限額NUMBER（8,2）、開戶日期（DATE）；訂單記錄表中包括：訂單標識NUMBER（5）、客戶標識NUMBER（5）、訂單日期（DATE）、總計NUMBER（8,2）。以下哪種方案需要使用子查詢來返回需要的結果？（D）

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

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

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

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

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

A. SELECT 產品名稱 FROM 產品 WHERE 成本>（SELECT AVG（成本） FROM 產品）；

B. SELECT 產品名稱 FROM 產品 WHERE 成本> AVG（成本）；

C. SELECT AVG（成本）, 產品名稱 FROM 產品 WHERE 成本> AVG（成本）GROUP BY 產品名稱；

D. SELECT 產品名稱 FROM（SELECT AVG（成本） FROM 產品） WHERE 成本> AVG（成本）；

9.如果單行子查詢返回了空值且使用了等於比較子，外部查詢會返回什麼結果？（B）

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

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

10.如果需要建立包含多行子查詢的SELECT語句，可以使用哪個（些）比較子？（A）

 

A.IN、ANY和ALL            B.LIKE

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

 

 

 

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

Oracle資料庫——SQL進階查詢