OCP課程6：SQL之使用組函式

這章主要講Oracle有哪些組函式，以及分組函式的使用。

組函式對一組資料進行操作，然後產生一個結果。

 

 

1、常用組函式

組函式的語法：

 

例子：查詢job_id包含REP的員工的平均薪水，最大薪水，最小薪水及總和

SQL> select avg(salary),max(salary),min(salary),sum(salary) from employees where job_id like '%REP%';

AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)

----------- ----------- ----------- -----------

8272.72727       11500        6000      273000

avg和sum只能用於數字型別。

min和max可以用於數字型別，字元型別及日期型別。

 

例子：查詢最早入職和最晚入職的日期

SQL> select min(hire_date),max(hire_date) from employees;

MIN(HIRE_DAT MAX(HIRE_DAT

------------ ------------

13-JAN-01    21-APR-08

count(*)返回表的總行數，包含重複的

 

例子：查詢在部門50的人數

SQL> select count(*) from employees where department_id=50;

  COUNT(*)

----------

        45

也可以使用非空的字元來表示，不能使用null

SQL> select count(1) from employees where department_id=50;

  COUNT(1)

----------

        45

count(expr)返回不包含null的行數

 

例子：查詢在部門80中有提成的人數

SQL> select count(commission_pct) from employees where department_id=80;

COUNT(COMMISSION_PCT)

---------------------

                   34

count(distinct expr)返回不重複及非空的行數

 

例子：查詢人員表中的部門數量

SQL> select count(distinct department_id) from employees;

COUNT(DISTINCTDEPARTMENT_ID)

----------------------------

                          11

 

例子：查詢有提成人員的平均提成（組函式是預設忽略null的）

SQL> select avg(commission_pct) from employees;

AVG(COMMISSION_PCT)

-------------------

         .222857143

 

例子：查詢公司所有人員的平均提成

SQL> select avg(nvl(commission_pct,0)) from employees;

AVG(NVL(COMMISSION_PCT,0))

--------------------------

                .072897196

 

 

2、使用group by子句建立分組

select子句中不在組函式當中的欄位必須也要出現在group by子句

 

例子：查詢每個部門的平均薪水

SQL> select department_id,avg(salary) from employees group by department_id;

DEPARTMENT_ID AVG(SALARY)

------------- -----------

          100  8601.33333

但是在group by子句中的欄位則不一定在select子句

 

例子：查詢每個部門的平均薪水

SQL> select avg(salary) from employees group by department_id;

AVG(SALARY)

-----------

8601.33333

 

例子：查詢每個部門下每種工作的總薪水

SQL> select department_id dept_id,job_id,sum(salary) from employees group by department_id,job_id;

   DEPT_ID JOB_ID     SUM(SALARY)

---------- ---------- -----------

       110 AC_ACCOUNT        8300

 

例子：group by子句不能使用列別名，同時列別名也不能用於where子句和having子句，只能用於order by子句

SQL> select department_id deptid,job_id,sum(salary) from employees group by deptid,job_id;

select department_id deptid,job_id,sum(salary) from employees group by deptid,job_id

                                                                       *

ERROR at line 1:

ORA-00904: "DEPTID": invalid identifier

 

例子：如果select子句中除了組函式，還包括其他欄位，但這些欄位如果不在group by子句或者沒有group by子句，就會報錯

SQL> select department_id,count(last_name) from employees;

select department_id,count(last_name) from employees

       *

ERROR at line 1:

ORA-00937: not a single-group group function

 

例子：where子句中不能使用組函式，否則報錯

SQL> select department_id,avg(salary) from employees where avg(salary)>8000 group by department_id;

select department_id,avg(salary) from employees where avg(salary)>8000 group by department_id

                                                      *

ERROR at line 1:

ORA-00934: group function is not allowed here

 

 

3、使用having子句限制分組結果

注意語句的順序，group by子句在where子句之後，order by子句位於最後，但是having子句和group by子句的位置可以互換。

 

例子：查詢每個部門的最大薪水，並顯示大於10000的

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

DEPARTMENT_ID MAX(SALARY)

------------- -----------

          100       12008

 

例子：查詢除了工作編號包含REP的每種工作的合計薪水，並顯示合計薪水大於13000的，按照合計薪水從低到高排序

SQL> select job_id,sum(salary) from employees where job_id not like '%REP%' group by job_id having sum(salary)>13000 order by sum(salary);

JOB_ID     SUM(SALARY)

---------- -----------

PU_CLERK         13900

 

 

4、巢狀組函式

與上一章的單行函式一樣，組函式也可以巢狀

 

例子：查詢按部門分組，最大平均薪水

SQL> select max(avg(salary)) from employees group by department_id;

MAX(AVG(SALARY))

----------------

      19333.3333

 

 

5、總結

• 分組函式會跳過空，求記錄數，求平均，求最大，求最小，裡面有空的，不會計算，會跳過，特別是取平均的時候，有值的才會去計算，沒有值的不去管他了，可能會和手工計算的有差距。
• 分組針對多個欄位分組，如果select子句的欄位沒有在分組函式里面，就必須要放到group by後面
• 對分組的結果進行限定，使用的是having子句，where語句限定的是分組之前的結果，分組之後的結果要用having子句

 

 

6、相關習題

（1）View the Exhibit and examine the details of the ORDER_ITEMS table. Evaluate the following SQL  statements:       Statement  1:  SELECT MAX(unit_price*quantity) "Maximum Order" FROM order_items? Statement 2:  SELECT  MAX(unit_price*quantity)  "Maximum  Order" FROM order_items GROUP BY order_id? Which statements are true regarding the output of these SQL statements? (Choose all that apply.)

A.Statement 1 would return only one row of output.

B.Both the statements would give the same output.

C.Statement 2 would return multiple rows of output.

D.Statement 1 would not return any row because the GROUP BY clause is missing.

E.Both statements would ignore NULL values for the UNIT_PRICE and QUANTITY columns.

 

答案：ACE

 

 

（2）View the Exhibit and examine the description of the PRODUCT_INFORMATION table.

Which SQL statement would retrieve from the table the number of products having LIST_PRICE as NULL?

A.SELECT COUNT(list_price) FROM product_information WHERE list_price IS NULL?
B.SELECT COUNT(list_price) FROM product_information WHERE list_price = NULL?
C.SELECT COUNT(NVL(list_price, 0)) FROM product_information WHERE list_price IS NULL?
D.SELECT COUNT(DISTINCT list_price) FROM product_information WHERE list_price IS NULL?

 

答案：C

 

 

（3）View the Exhibit and examine the structure of the ORDER_ITEMS table. You need to display the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table. Which query would produce the desired output?

A.SELECT order_id FROM order_items WHERE(unit_price*quantity) = MAX(unit_price*quantity) GROUP BY order_id;

B.SELECT  order_id  FROM  order_items  WHERE(unit_price*quantity)  =  (SELECT MAX(unit_price*quantity) FROM order_items) GROUP BY order_id;

C.SELECT  order_id  FROM  order_items  WHERE  (unit_price*quantity)  =  (SELECT MAX(unit_price*quantity) FROM order_items GROUP BY order_id);

D.SELECT  order_id  FROM order_items GROUP  BY  order_id  HAVING  SUM(unit_price*quantity) =(SELECT MAX(SUM(unit_price*quantity)) FROM order_items GROUP BY order_id);

 

答案：D

 

 

（4）Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)
A.You can use column alias in the GROUP BY clause.
B.Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.
C.The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.
D.Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
E.If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY clause.

 

答案：DE

 

 

（5）View the Exhibit and examine the description of the ORDER_ITEMS table. The following SQL statement was written to retrieve the rows for the PRODUCT_ID that has a UNIT_PRICE of more than 1,000 and has been ordered more than five times: SELECT product_id, COUNT(order_id) total, unit_price  FROM order_items  WHERE unit_price>1000  AND  COUNT(order_id)>5 GROUP BY product_id, unit_price；Which statement is true regarding this SQL statement？

A.The statement would execute and give you the desired result.
B.The statement would not execute because the aggregate function is used in the WHERE clause.
C.The statement would not execute because the WHERE clause should have the OR logical operator instead of AND.
D.The statement would not execute because in the SELECT clause, the UNIT_PRICE column is placed after the column having the aggregate function.

 

答案：B

 

 

（6）Which three  statements  are  true  regarding the WHERE and HAVING  clauses  in  a  SQL statement?(Choose three.)
A.The HAVING clause conditions can have aggregate functions.
B.The HAVING clause conditions can use aliases for the columns.
C.WHERE and HAVING clauses cannot be used together in a SQL statement.
D.The WHERE clause is used to exclude rows before the grouping of data.
E.The HAVING clause is used to exclude one or more aggregated results after grouping data.

 

答案：ADE

 

 

（7）Which three statements are true regarding group functions？(Choose three.)
A.They can be used on columns or expressions.
B.They can be passed as an argument to another group function.
C.They can be used only with a SQL statement that has the GROUP BY clause.
D.They can be used on only one column in the SELECT clause of a SQL statement.
E.They can be used along with the single-row function in the SELECT clause of a SQL statement.

 

答案：ABE

 

 

（8）View the Exhibit and examine the structure of the ORDER_ITEMS table. Examine the following SQL statement: SELECT order_id, product_id, unit_price FROM order_items WHERE unit_price = (SELECT MAX(unit_price) FROM order_items GROUP BY order_id)；You want to display the PRODUCT_ID of the product that has the highest UNIT_PRICE per ORDER_ID. What correction should be made in the above SQL statement to achieve this ？

A.Replace = with the IN operator.
B.Replace = with the >ANY operator.
C.Replace = with the >ALL operator.
D.Remove the GROUP BY clause from the subquery and place it in the main query.

 

答案：A

 

 

（9）View the Exhibit and examine the description of the ORDERS table. Your manager asked you to get  the  SALES_REP_ID  and the  total  numbers  of  orders  placed  by  each of  the  sales representatives. Which statement would provide the desired result？

A.SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders GROUP BY sales_rep_id ；
B.SELECT  sales_rep_id,  COUNT(order_id) total_orders FROM orders GROUP BY sales_rep_id,total_orders；
C.SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders  ；
D.SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders WHERE sales_rep_id IS NOT NULL；

 

答案：A

 

 

（10）View the Exhibit and examine the description of the ORDERS table. You need to display CUSTOMER_ID for all customers who have placed orders more than three times in the last six months. You issued the following SQL statement: SELECT customer_id,COUNT(order_id) FROM orders WHERE COUNT(order_id)>3 AND order_date BETWEEN ADD_MONTH(SYSDATE,6) AND SYSDATE GROUP BY customer_id； Which statement is true regarding the execution of the above statement？

A.It would execute successfully and provide the desired result.
B.It would not execute because the WHERE clause cannot have an aggregate function.
C.It would not execute because the ORDER_ID column is not included in the GROUP BY clause.
D.It would not execute because the GROUP BY clause should be placed before the WHERE clause.

 

答案：B

 

 

（11）View the Exhibit and examine the structure of the PRODUCT_INFORMATION table. Which two queries would work？(Choose two.)

A.SELECT product_name FROM product_information WHERE list_price  = (SELECT AVG(list_price) FROM product_information)；
B.SELECT product_status FROM product_information GROUP BY product_status WHERE list_price < (SELECT AVG(list_price) FROM product_information)；
C.SELECT product_status FROM product_information GROUP BY product_status HAVING list_price > (SELECT AVG(list_price) FROM product_information) ；
D.SELECT product_name FROM product_information WHERE list_price < ANY(SELECT AVG(list_price) FROM product_information GROUP BY product_status) ；

 

答案：AD

 

 

（12）View the Exhibit and examine the description of the ORDERS table. The orders in the ORDERS table  are  placed through sales  representatives  only.  You are  given the  task  to get  the SALES_REP_ID from the ORDERS table of those sales representatives who have successfully referred more than 10 customers. Which statement would achieve this purpose ？

A.SELECT sales_rep_id, COUNT(customer_id) "Total" FROM orders HAVING COUNT(customer_id) > 10；
B.SELECT sales_rep_id, COUNT(customer_id) "Total" FROM orders WHERE COUNT(customer_id) > 10 GROUP BY sales_rep_id ；
C.SELECT sales_rep_id, COUNT(customer_id) "Total" FROM orders GROUP BY sales_rep_id HAVING total > 10；
D.SELECT sales_rep_id, COUNT(customer_id) "Total" FROM orders GROUP BY sales_rep_id HAVING COUNT(customer_id) > 10

 

答案：D