OCP课程6：SQL之使用组函数

stonebox

这章主要讲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

郑全

不错的文档。