單行子查詢傳回多個行_sql多表查詢練習，習題總結

組函數(聚合函數)：

組函數操作行集，給出每組的結果。組函數不象單行函數，組函數對行的集合進行操作，對每組給出一個結果。這些集合可能是整個表或者是表分成的組。

組函數和單行函數的差別：

單行函數對查詢到每個結果集做處理，而組函數隻對分組資料做處理。

單行函數對每個結果集傳回一個結果，而組函數對每個分組傳回一個結果。

組函數的類型

1. AVG 平均值 ---->對封裝資料做平均值運算
2. COUNT 計數
3. MAX 最大值
4. MIN 最小值
5. SUM 合計

所有組函數忽略空值。為了用一個值代替空值，用 NVL、NVL2 或 COALESCE 函數

計算

有傭金的員工

的傭金平均值。

select avg(e.commission_pct) from employees e;
           

計算

所有員工

的傭金的平均值。

select avg(nvl(e.commission_pct),0) from employees e;
           

GROUP BY (group by) 子句文法

GROUP BY 子句可以把表中的行劃分為組。然後可以用組函數傳回每一組的摘要資訊。

1. 如果在 SELECT 子句中包含了組函數，就不能選擇單獨的結果，除非單獨的列出現在 GROUP BY 子句中。
2. 在 GROUP BY 子句中必須包含列。
3. 在 GROUP BY 子句中你不能用列别名
4. group by 分組後不能再用where 對條件過濾 select出現的列要在 group by 句子 中
5. having --- group by 分組後可以用having 進行條件過濾

注意：在 SELECT 清單中的任何列必須在 GROUP BY 子句中。

求每個部門的平均薪水。

select avg(e.salary) from employees e group by e.department_id;
           

顯示在每個部門中付給每個工作崗位的合計薪水的報告。

select e.job_id,sum(e.salary) from employees e group by e.job_id;
           

GROUP BY 子句的執行順序

先進行資料查詢，在對資料進行分組，然後執行組函數。

限制分組結果 having

分組後不能使用where 可以用having 進行條件過濾

顯示那些最高薪水大于 $10,000 的部門的部門号和最高薪水。

select e.department_id,max(e.salary) from employees e group by e.department_id having max(e.salary)>10000;
           

嵌套組函數

在使用組函數時我們也可以根據需要來做組函數的嵌套使用。

顯示部門中的最大平均薪水。

select max(avg(e.salary)) from employees e group by e.department_id;
           

子查詢

子查詢是一個 SELECT 語句，它是嵌在另一個 SELECT 語句中的子句。

• 子查詢 (内查詢) 在主查詢之前執行一次
• 子查詢的結果被用于主查詢 (外查詢)

使用子查詢的原則

• 子查詢放在圓括号中。
• 将子查詢放在比較條件的右邊。
• 在單行子查詢中用單行運算符，在多行子查詢中用多行運算符。

誰的薪水比 Abel 高。

select em.last_name from employees em where em.salary>(select e.salary from employees e where e.last_name='Abel');
           

多行子查詢

• 傳回多于一行
• 使用多行比較符

in 等于清單中的任何成員

any 比較子查詢傳回的每個值 滿足其中一個條件即可

<any 小于其中任意值，相當于小于其中最大值傳回結果，

>any 大于其中任意值，相當于大于其中最小值就傳回結果

all 比較子查詢的全部值 滿足全部條件

<all 小于最小值

>all 大于最大值

查找各部門收入為部門最低的那些雇員。顯示他們的名字，薪水以及部門 ID。

select em.last_name,em.salary,em.department_id from employees em where em.salary in (select min(e.salary) from employees e group by e.department_id);
           

顯示工作崗位不是 IT_ PROG 的雇員，并且這些雇員的的薪水少于 IT_PROG 工作崗位的雇員的 ID、名字、工作崗位和薪水。

select e.employee_id,e.last_name,e.job_id,e.salary from employees e where e.job_id <> 'IT_PROG' and e.salary < any (select em.salary from employees em where em.job_id = 'IT_PROG');
           

顯示那些薪水低于工作崗位 IT_ PROG 的最低薪水，并且工作崗位不是 IT_PROG 的所有雇員。

select em.last_name,em.salary from employees em where em.job_id<>'IT_PROG' and em.salary<(select min(e.salary) from employees e where e.job_id='IT_PROG');
           

練習

自連接配接

顯示雇員的 last name 和 employee number 連同他們的經理的 last name 和 manager number。列标簽分别為Employee、Emp#、Manager 和 Mgr#。（注意：雇員manager_id 和 經理的employee_id 相等）

select e.last_name "Employss",e.employee_id "Emp#",m.last_name "Manager",m.employee_id "Mgr#" from employees e , employees m where e.manager_id=m.employee_id;
           

查詢所有雇員的經理包括 King，他沒有經理。顯示雇員的名字、雇員 ID、經理名、經理 ID、用雇員号排序結果。

select e.last_name,e.employee_id,m.last_name,e.manager_id from employees e left join employees m on (e.manager_id=m.employee_id) order by e.employee_id;
           

建立一個查詢顯示那些在雇員 Davies 之後入本公司工作的雇員的 name 和 hire date（先找到Davies 的入職資訊在判斷）

select e.last_name,e.hire_date from employees e,employees em where e.last_name='Davies' and e.hire_date<em.hire_date;
           

顯示所有雇員的 names 和 hire dates，他們在他們的經理之前進入本公司，連同他們的經理的名字和受雇日期一起顯示。列标簽分别為 Employee、Emp Hired、Manager 和Mgr Hired。（思路，建立連接配接：雇員manager_id 和 經理的employee_id 相等，日期越後越大）

select e.last_name "Employee",e.hire_date "Emp Hired",ma.last_name "Manager",ma.hire_date "Mgr Hired" from employees e,employees ma  where e.manager_id=ma.employee_id and e.hire_date< ma.hire_date;
           

組函數 （group by）

寫一個查詢顯示每個部門的名字、地點城市、人數和部門中所有雇員的平均薪水。四舍五入薪水到兩位小數。（）

select e.department_id,l.city,count(e.last_name),round(avg(e.salary),2) from employees e,departments d,locations l where e.department_id=d.department_id and d.location_id=l.location_id group by e.department_id,l.city;
           

誰的薪水比 Abel 高。（1.子查詢，2.内連接配接）

1.select d.last_name from employees d where d.salary>(select e.salary from employees e where e.last_name='Abel');
2.select e.last_name from employees e,employees d where d.last_name='Abel' and e.salary>d.salary;
           

顯示那些 job ID 與雇員 141 相同的雇員的名字與 job ID。（内連接配接查詢）

select e.last_name,e.employee_id from employees e,employees d where d.employee_id='141' and e.job_id=d.job_id;
           

顯示那些 job ID 與雇員 141 相同的雇員的名字與 job ID。（子查詢）

select d.last_name,d.employee_id from employees d where d.job_id=(select e.job_id from employees e where e.employee_id='141');
           

顯示 job ID 與雇員 141 相同，并且薪水 高于雇員 143 的那些雇員。(子查詢)

select d.last_name,d.employee_id,d.salary from employees d where d.job_id=(select e.job_id from employees e where e.employee_id='141') and d.salary>(select e.salary from employees e where e.employee_id='141');
           

顯示所有其最低薪水小于 部門 50 的最低薪水的部門号和最低薪水。

select e.last_name,e.job_id,e.salary from employees e where e.salary=(select min(salary)from employees);
           

顯示所有其最低薪水大于 部門 50 的最低薪水的部門号和最低薪水。

顯示那些薪水低于工作崗位 IT_ PROG 的最低薪水，并且工作崗位不是 IT_PROG 的所有雇員。

select em.last_name from employees em where em.job_id <>'IT_PROG' and em.salary<(select min(e.salary) from employees e where e.job_id='IT_PROG');
           

---<all

select em.last_name from employees em where em.job_id <>'IT_PROG' and em.salary< all(select e.salary from employees e where e.job_id='IT_PROG');
           

寫一個查詢顯示與 Zlotkey 在同一部門的雇員的 last name和 hire date（1.子查詢 2.内連接配接）

1.select em.last_name,em.hire_date from employees em where em.department_id  = (select e.department_id from employees e where e.last_name='Zlotkey');
2.select e.last_name,e.department_id from employees e,employees em where em.last_name='Zlotkey' and em.department_id=e.department_id;
           

寫一個查詢顯示與 Zlotkey 在同一部門的雇員的 last name和 hire date，結果中不包括 Zlotkey。

select em.last_name,em.hire_date from employees em,(select  e.last_name,e.department_id from employees e where e.last_name='Zlotkey') emp where em.department_id  =emp.department_id and em.last_name<>emp.last_name ;
           

建立一個查詢顯示所有其薪水高于平均薪水的雇員的雇員号和名字。按薪水的升序排序。

select e.department_id,e.last_name from employees e where e.salary> (select avg(em.salary) from employees em) order by e.salary asc;
           

寫一個查詢顯示所有工作在有任一雇員的名字中包含一個 u的部門中的雇員的雇員号和名字。

select em.employee_id,em.last_name from employees em where em.department_id in (select distinct e.department_id from employees e where e.last_name like '%u%');
           

顯示所有部門地點号 (department location ID ) 是 1700的雇員的 last name、department number 和 job ID。（1.内連接配接2.子查詢）

1.select e.last_name,d.department_id,e.job_id from employees e,departments d where d.location_id=1700 and d.department_id=e.department_id;
2.select e.last_name,e.department_id,e.job_id from employees e where e.department_id in (select d.department_id from departments d where d.location_id=1700);
           

顯示經理 King 下的雇員的名字和薪水。（1.子查詢 2.内連接配接）

1.select m.last_name,m.salary from employees m where m.manager_id in (select e.employee_id from employees e where e.last_name='King');
2.select e.last_name,e.salary from employees e,employees em where em.last_name='King' and em.employee_id=e.manager_id;
           

顯 示 在 Executive 部 門 的 每 個 雇 員 的 departmentnumber、last name 和 job ID。（1.子連結2.内連接配接）

1.select em.department_id,em.last_name,em.job_id from employees em where em.department_id=(select d.department_id from departments d where d.department_name='Executive');
2.select e.department_id,e.last_name,e.job_id from employees e,departments d where d.department_name='Executive' and d.department_id=e.department_id;
           

查詢顯示所有收入高于平均薪水并且工作在有任一雇員的名字中帶有一個 u 的部門的雇員的 employee numbers、lastnames 和 salaries。（1.子查詢 2.内連接配接）

1.select e.employee_id,e.last_name,e.salary from employees e where e.salary > (select avg(salary) from employees) and e.department_id in (select emp.department_id from employees emp where emp.last_name like ('%u%'));
2.select distinct e.employee_id,e.last_name,e.salary from employees e,employees em where e.department_id=em.department_id and em.last_name like '%u%' and e.salary>(select avg(salary) from employees );
           

顯示那些雇員低于他們部門最高薪水的雇員的名字，薪水，部門号和他們部門最高的薪水

select e.last_name,e.salary,e.department_id,ma.aa from employees e ,(select max(e1.salary) aa,e1.department_id from employees e1 group by e1.department_id ) ma where  e.salary < ma.aa and e.department_id=ma.department_id;