Sql Certificate 简明教程

SQL - Using the Group Functions Questions

1. Which of the following is NOT a GROUP BY function?

*答案：C。*NVL 是一个通用的函数，用于为 NULL 值提供备用值。函数 MAX、MIN 和 AVG 可以用作 GROUP BY 函数。

*Answer: C. *NVL is a general function used to provide alternate value to the NULL values. The functions MAX, MIN and AVG can be used as GROUP BY functions.

2. Which of the following functions can be used without GROUP BY clause in SELECT query?

*答案：A、B、C、D。 *如果 SELECT 查询中没有选择其他列，则在查询中可以使用所有列出的组函数。

*Answer: A, B, C, D. *All the listed group functions can be used in a query provided no other columns are selected in the SELECT query.

*3. 下面哪个 SELECT 查询可以返回员工获得的最大工资的部门号？（考虑给定的表结构） *

*3. Which of the following SELECT query returns the department number with maximum salary compensated to an employee? (Consider the table structure as given) *

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

*答案：B。 *MAX 函数可用于返回部门中每个组由一个部门组成时的最大工资。

*Answer: B. *The MAX function can be used to return the maximum salary in a department where each group is formed by a department.

4. Which of the following statements are true about the COUNT function?

Answer: B. *The COUNT( ) 统计包括重复值和 NULL 值在内行的数量。使用 DISTINCT 和 ALL 关键字来限制重复值和 NULL 值。

Answer: B. *The COUNT() counts the number of rows including duplicates and NULLs. Use DISTINCT and ALL keyword to restrict duplicate and NULL values.

5. What are the appropriate data types accepted by GROUP BY functions?

*答案：B。 *带参数函数的数据类型可以是 CHAR、VARCHAR2、NUMBER 或 DATE。

*Answer: B. *The data types for the functions with an argument may be CHAR, VARCHAR2, NUMBER or DATE.

6. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.

SELECT COUNT (*) FROM t_count;

Answer: A. *The COUNT( ) 统计包括重复值和 NULL 值在内行的数量。使用 DISTINCT 和 ALL 关键字来限制重复值和 NULL 值。

Answer: A. *The COUNT() counts the number of rows including duplicates and NULLs. Use DISTINCT and ALL keyword to restrict duplicate and NULL values.

7. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.

SELECT COUNT (num) FROM t_count;

*答案：C。 *COUNT(column) 忽略 NULL 值但统计重复值。

*Answer: C. *COUNT (column) ignores the NULL values but counts the duplicates.

8. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.

SELECT COUNT (ALL num) FROM t_count;

*答案：C。 *COUNT(ALL column) 忽略 NULL 值但统计重复值。

*Answer: C. *COUNT(ALL column) ignores the NULL values but counts the duplicates.

9. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.

SELECT COUNT (DISTINCT num) FROM t_count;

*答案：B。 *COUNT(DISTINCT column) 统计不同的非空值。

*Answer: B. *COUNT (DISTINCT column) counts the distinct not null values.

10. What happens when the below query is executed in SQL Plus?*

SELECT COUNT() FROM dual;

Answer: C. *COUNT function requires minimum one argument which can be either the column with [ALL | DISTINCT] modifier or ' '。

Answer: C. *COUNT function requires minimum one argument which can be either the column with [ALL | DISTINCT] modifier or ''.

*11. 以下是有关 SQL 中 VARIANCE 函数的一些语句。 *

*11. Here are few statements about VARIANCE function in SQL. *

i. The function accepts multiple numeric inputs and returns variance of all the values*

*答案：C. VARIANCE 函数接受单个数字参数（即列名），作为所有列值方差的返回值，其中包含 NULL 值。

*Answer: C. *The VARIANCE function accepts single numeric argument as the column name and returns variance of all the column values considering NULLs.

12. Which of the following is NOT a GROUP BY extensions in SQL?

*答案：A. GROUPING SETS 运算可用于仅使用一个查询执行 multiple GROUP BY 聚合。

*Answer: A. *GROUPING SETS operations can be used to perform multiple GROUP BY aggregations with a single query.

13. 选择关于下面查询的正确陈述。将表结构视为已给。

*13. Select the correct statements about the below query. Consider the table structure as given. *

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SELECT department_id , SUM(salary )
FROM employees
GROUP BY department_id ;

*答案：A. SUM 作为一个分组函数，计算在部门工作的一组员工的薪水总额。

*Answer: A. *SUM is a group function which calculates the sum of salaries of a group of employees working in a department.

14. Which clause is used to filter the query output based on aggregated results using a group by function?

*答案：D. HAVING 子句用于限制组结果。你可以使用 HAVING 子句指定要显示的组，用这种方式进一步根据聚合信息限制组。HAVING 子句可以在 GROUP BY 子句前面，但建议先放置 GROUP BY 子句，因为这更符合逻辑。在将 HAVING 子句应用于 SELECT 列表中的组之前，将形成组，并计算组函数。

*Answer: D. *HAVING Clause is used for restricting group results. You use the HAVING clause to specify the groups that are to be displayed, thus further restricting the groups on the basis of aggregate information. The HAVING clause can precede the GROUP BY clause, but it is recommended that you place the GROUP BY clause first because it is more logical. Groups are formed and group functions are calculated before the HAVING clause is applied to the groups in the SELECT list.

15. Examine the given table structure and predict the outcome of the following query.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SELECT count(*)
FROM employees
WHERE comm = NULL;

*答案：B. 使用 WHERE 条件排除 NULL 是让查询忽略 NULL 的一种方式。但是这里对 IS NULL 运算符的使用是错误的。条件应为“WHERE comm IS NULL”。

*Answer: B. * Excluding out NULLs using WHERE condition is a way to direct the query to ignore NULLs. But here the usage of IS NULL operator is wrong. The condition should be 'WHERE comm IS NULL'.

16. Which of the following statements is true about the group functions?

*答案：C. AVG 函数仅可与数字值一同使用。具有这种限制的其他函数包括 SUM、STDDEV 和 VARIANCE。

*Answer: C. *The AVG function can be only used with numeric values. Other functions which have such restriction are SUM, STDDEV and VARIANCE.

17. Which of the following is a valid SELECT statement?

*答案：A. 列别名不能在 GROUP BY 或 HAVING 子句中使用。

*Answer: A. *Column aliases cannot be used in GROUP BY or HAVING clause.

18. Which of the following statements is correct?

*答案：D. 尽管如果 HAVING 子句出现在 GROUP BY 子句之前，Oracle 不会引发错误，但它只有在 GROUP BY 子句被处理完并组准备过滤后才会被处理。

*Answer: D. *Though Oracle doesn’t raise error if HAVING clause precedes the GROUP BY clause but it is processed only after the GROUP BY clause is processed and group are ready to be filtered.

19. Which of the following is not a valid SQL statement?*

答案：A。

*Answer: A. *

20. Which of the following statements is correct?

*答案：C、D. WHERE 子句在对行进行分组和处理之前对其进行限制，而 HAVING 子句则限制组。

*Answer: C, D. *The WHERE clause restricts the rows before they are grouped and processed while HAVING clause restricts the groups.

21. Which of the following is a valid SQL statement?

*答案：B。*GROUP BY 子句必须包含组函数内部使用的列外的所有列。

*Answer: B. *The GROUP BY clause must contain all the columns except the one which is used inside the group function.

22. Which of the following SELECT statements lists only the book with the largest profit?

答案：A。

*Answer: A. *

23. Which of the following statement(s) is/are correct?

A group function can be nested inside a group function.
A group function can be nested inside a single-row function.
A single-row function can be nested inside a group function.

*答案：A、B、C。*组函数只能嵌套到两层深度。组函数可以嵌套在单行函数内（将 AVG 嵌套在 TO_CHAR 函数中）。此外，单行函数可以嵌套在组函数内。

*Answer: A, B, C. *Group functions can be nested only to a depth of two. Group functions can be nested inside single-row functions (AVG embedded in a TO_CHAR function). In addition, single-row functions can be nested inside group functions.

24. Which of the following functions is used to calculate the total value stored in a specified column?

*答案：D。*SUM 函数用于获取数值的总和。

*Answer: D. *SUM function is used to get the addition of numeric values.

25. Which of the following SELECT statements lists the highest retail price of all books in the Family category?

*答案：A。*由于在分组之前必须限制 FAMILY 类别，因此必须使用 WHERE 子句而不是 HAVING 子句来筛选表行。

*Answer: A. *Since the category FAMILY has to be restricted before grouping, table rows must be filtered using WHERE clause and not HAVING clause.

26. Which of the following functions can be used to include NULL values in calculations?

*答案：B。*NVL 是一个提供 NULL 值替代值的通用函数。它实际上可能会对使用 AVG、STDDEV 和 VARIANCE 组函数的算术计算产生影响。

*Answer: B.*NVL is a general function to provide alternate values to the NULL values. It can really make a difference in arithmetic calculations using AVG, STDDEV and VARIANCE group functions.

27. Which of the following is not a valid statement?

Answer: A. *The ALL keyword counts duplicates but ignores NULLs. Duplicates are also included with ' ' 和列名规范。

Answer: A. *The ALL keyword counts duplicates but ignores NULLs. Duplicates are also included with '' and column name specification.

28. Which of the following SQL statements determines how many total customers were referred by other customers?

*答案：B。*将所有客户都视为一个组，COUNT(referred) 将仅统计由某人推荐的人数。COUNT(referred) 将忽略列的 NULL 值。

*Answer: B. *Considering all customers as one group, COUNT(referred) will count only those who are referred by someone. COUNT(referred) will ignore NULL values of the column.

29. Determine the correct order of execution of following clauses in a SELECT statement.

1.SELECT

2.FROM

3.WHERE

4.GROUP BY

5.HAVING

6.ORDER BY

*答案：A. *处理顺序从 FROM 子句开始以获取表名，然后使用 WHERE 子句限制行，使用 GROUP BY 子句对其进行分组，使用 HAVING 子句限制组。ORDER BY 子句是最后一个要处理的子句，用于对最终数据集进行排序。

*Answer: A. *Processing order starts from FROM clause to get the table names, then restricting rows using WHERE clause, grouping them using GROUP BY clause, restricting groups using HAVING clause. ORDER BY clause is the last one to be processed to sort the final data set.

30. Which of the below clauses is used to group a set of rows based on a column or set of columns?

*答案：C. *GROUP BY 子句根据指定列列表对数据进行分组。

*Answer: C. *GROUP BY clause forms the groups of the data based on the column list specified.

31. Which of the following group functions can be used for population variance and population standard deviation problems?

回答：A、B。

*Answer: A, B. *

32. Select the positions in a SELECT query where a group function can appear.

*答案：A、C、D. *组函数可以在 SELECT、ORDER BY 和 HAVING 子句中出现。如果在 WHERE 或 GROUP BY 子句中使用组函数，则 Oracle 会引发异常。

*Answer: A, C, D. *Group functions can appear in SELECT, ORDER BY and HAVING clause. Oracle raises exception if group functions are used in WHERE or GROUP BY clauses.

33. Examine the structure of the EMPLOYEES table as given. Which query will return the minimum salary in each department?

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

*答案：B. *MIN 函数返回由部门形成的组中的最低工资。

*Answer: B. *MIN function returns the minimum salary in a group formed by department.

34. Examine the structure for the table EMPLOYEES and Interpret the output of the below query

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SELECT COUNT(*), COUNT(all comm) FROM employees ;

*回答：D. *

*Answer: D. *

35. 关于组函数，以下哪些说法正确？

*35. Which of the following are true about group functions? *

*答案：C. *组函数只能嵌套到两个深度。组函数可以在单行函数中嵌套（AVG 嵌入在 TO_CHAR 函数中）。此外，单行函数可以在组函数中嵌套。

*Answer: C. *Group functions can be nested only to a depth of two. Group functions can be nested inside single-row functions (AVG embedded in a TO_CHAR function). In addition, single-row functions can be nested inside group functions.

36. 检查 EMPLOYEES 表给定的结构。您希望通过执行以下 SQL 语句创建“emp_dept_sales”视图。

*36. Examine the structure of the table EMPLOYEES as given. You want to create a "emp_dept_sales" view by executing the following SQL statements. *

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

CREATE VIEW emp_dept_sales
AS
SELECT d.department_name , sum(e.salary )
FROM employees  e, departments  d
where e.department_id =d.department_id
GROUP by d.department_name ;

关于上述语句的执行，哪种说法是正确的？

Which statement is true regarding the execution of the above statement?

*Answer: D. *视图上的DML操作执行规则。如果你在视图中加入了group functions， GROUP BY子句或 DISTINCT关键词，你将无法使用该视图添加数据。伪列 ROWNUM关键词是由表达式定义的列，它们是基础表中未被视图选中的非空列。

*Answer: D. *Rules for Performing DML Operations on a View. You cannot add data through a view if the view includes group functions or a GROUP BY clause or DISTINCT keyword. The pseudo column ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view.

37. Which of the following statements are true regarding views?

*Answer: C、D. *视图上的DML操作执行规则。如果你在视图中加入了group functions， GROUP BY子句或 DISTINCT关键词，你将无法使用该视图添加数据。伪列 ROWNUM关键词是由表达式定义的列，它们是基础表中未被视图选中的非空列。

*Answer: C, D. * Rules for Performing DML Operations on a View. You cannot add data through a view if the view includes group functions or a GROUP BY clause or DISTINCT keyword. The pseudo column ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view.

*38. 查看给定的表格结构。 *

*38. Examine the table structure as given. *

SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)

下面SQL查询中的哪个子句会产生错误？

Which clause in the below SQL query generates error?

SELECT department_id , avg(salary )
FROM departments
WHERE upper(job) in ('SALES','CLERK')
GROUP BY job
ORDER BY department_id ;

*Answer: D. *GROUP BY子句必须包含出现在SELECT语句中的所有列。它会产生错误，因为JOB并不是一个被选中的列。它应该使用 DEPARTMENT_ID替换JOB。

*Answer: D. *GROUP BY clause must contain all the columns appearing in the SELECT statement. It raises error because JOB is not a selected column. It should have used DEPARTMENT_ID in placed of JOB.

39. Examine the table structure as given.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

下面哪个SELECT查询会显示每个职位类别的最大和最小工资？

Which of the below SELECT query will display the maximum and minimum salary earned by each job category?

*Answer: B. *SELECT语句中可以出现多个组函数。

*Answer: B. *More than one group function can appear in the SELECT statement.

40. Consider the table structure as given.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

查看下面查询中的错误。

Examine the error in the below query.

SELECT department_id
FROM employees
WHERE hiredate > '01-JAN-1985'
AND COUNT(*) > 2
GROUP by department_id
HAVING SUM (salary ) > 1000;

*Answer: D. *WHERE子句中不能使用组函数。它们只能出现在SELECT、HAVING、ORDER BY子句中。

*Answer: D. *Group functions cannot be used in WHERE clause. The can appear in SELECT, HAVING and ORDER BY clause.

*41. 查看给定的表格结构。 *

*41. Examine the table structure as given. *

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

预测下面这个查询的结果

Predict the outcome of the below query

SELECT job, COUNT(employee_id ),sum(salary )
FROM employees
GROUP BY job
HAVING SUM (salary ) > 5000;

*Answer: D. *HAVING子句会限制分组结果。COUNT函数用于计数，而SUM函数用于对数字值求和。

*Answer: D. *The HAVING clause restricts the group results. COUNT function is used for counting while SUM is used for adding the numeric values.

42. What is true of using group functions on columns that contain NULL values?

*Answer: A. *除了COUNT函数之外，所有的组函数都会忽略空值。

*Answer: A. *Except COUNT function, all the group functions ignore NULL values.

43. Which of the following statetments are true about the usage of GROUP BY columns in a subquery?

*Answer: A. *子查询与主查询一样，也可以包含GROUP BY和ORDER BY子句。

*Answer: A. *Like the primary query, a subquery can contain a GROUP BY as well as ORDER BY clause.

Examine the table structure as given and answer the questions 44 to 49 that follow.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

44. Predict the outcome of the below query

SELECT avg(max(salary ))
FROM employees
GROUP BY department_id
HAVING avg(max(salary ))>100;

*答案： B. *HAVING 子句不支持嵌套聚合函数。

*Answer: B. *The HAVING clause doesn’t allows nesting of aggregate functions.

45. Predict the output of the below query

SELECT avg(salary ), department_id
FROM employees
GROUP BY department_id ;

*答案： D. *GROUP 函数可以在 SELECT 查询中以任意顺序（在分组列之前或之后）使用。

*Answer: D. *Group functions can be used in any sequence (before or after the group by columns) in a SELECT query.

46. Predict the output of the below query

SELECT lower(job),avg(salary )
FROM employees
GROUP BY upper(job);

*答案： D. *函数 LOWER 是单行函数，必须在 GROUP BY 子句中指定以将 EMPLOYEES 数据分组为基础。

*Answer: D. *The function LOWER, being a single row function must be specified in the GROUP BY clause to base the grouping of EMPLOYEES data.

47. Which of the below query executes successfully?

*答案： B、 C. *第一个查询在整个 EMPLOYEES 数据上操作，而第二个查询则以部门的组为单位处理数据。

*Answer: B, C. *The first query operates of the whole EMPLOYEES data while the second one processes the data in groups of department.

48. Identify the error in the below SELECT statement.

SELECT department_id , AVG (salary )
FROM employees
GROUP BY department_id
HAVING department_id  > 10;

*答案： A. *GROUP BY 表达式可以用在 HAVING 子句中，以从最后的数据集中过滤出组。

*Answer: A. *GROUP BY expressions can be used in HAVING clause to filter out the groups from the final data set.

*49. 预测以下查询的输出 *

*49. Predict the output of the below query *

SELECT department_id , AVG (salary )
FROM employees
GROUP BY department_id
HAVING (department_id >10 and AVG(salary )>2000);

*答案： C. *HAVING 子句可以施加使用 AND 或 OR 运算符连接的多个条件来过滤组。

*Answer: C. *The HAVING clause can impose multiple conditions joined using AND or OR operator filter the groups.

50. Which of the following group functions can be used with DATE values?

*答案： B、 D. *GROUP 函数 AVG 和 SUM 只能与数字数据一起使用。

*Answer: B, D. *The group function AVG and SUM can be used with numeric data only.

51. Which of the following statements are true?

*答案： A、 B、 D. *GROUP 函数 AVG、 SUM、 VARIANCE 和 STDDEV 只能与数字数据一起使用。没有一个 GROUP 函数可以与 LONG 数据类型一起使用。

*Answer: A, B, D. *The group functions AVG,SUM, VARIANCE and STDDEV can be used with numeric data only. None of the group functions can be used with LONG data type.

52. Examine the table structure as given.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

找出以下查询中的错误。

Identify the error in the below query.

SELECT department_id , avg(salary ), count(hiredate)
FROM employees
GROUP BY department_id ;

*回答：D. *

*Answer: D. *

53. Which of the following group function can be used with LOB data types?

*答案：D. *聚集函数不能与 LOB 数据类型一起使用。

*Answer: D. * No aggregate function can be used with LOB data types.

54. 检查给定的表结构。

*54. Examine the table structure as given. *

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

预测以下两个查询的输出

Predict the output of the below two queries

Query - 1

SELECT avg(comm)
FROM employees ;

Query - 2

SELECT avg(nvl(comm,0))
FROM employees ;

*答案：B. *AVG 函数在计算数字数据的平均值时忽略 NULL 值。AVG(column) 将仅针对非空值计算平均值。然而，如果使用 NVL 将 NULL 值替换为零，则将考虑所有值。

*Answer: B. *The AVG function ignores NULL values while calculating the average of numeric data. AVG(column) will calculate average for only non null values. However, if NVL is used to substitute NULLs with a zero, all the values will be considered.

55. Choose the correct statements about the GROUP BY clause.

*答案：D. *根据处理顺序，GROUP BY 子句必须出现在 SELECT 查询的 WHERE 子句之后。

*Answer: D. *As per the processing sequence, the GROUP BY clause must appear after the WHERE clause in a SELECT query.

56. Examine the table structure as given.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

预测下面这个查询的结果

Predict the outcome of the below query

SELECT department_id ,avg(salary )
FROM employees
GROUP BY department_id , job
ORDER BY department_id ;

*答案：B. *尽管 GROUP BY 子句隐式对组进行了排序，但可以在查询中同时使用 GROUP BY 和 ORDER BY 子句。

*Answer: B. *Though GROUP BY clause implicitly sorts the groups, the GROUP BY and ORDER BY clauses can be used together in a query.

57. Which clause should you use to exclude group results in a query using group functions?

*答案：B. *HAVING 子句用于限制组。

*Answer: B. *HAVING clause is used to restrict the groups.

Examine the table structure as given and answer the questions 58 and 59 that follow.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

58. Predict the outcome of the below query

SELECT department_id ,avg(salary )
FROM employees
HAVING avg(salary )>2000
GROUP BY department_id
ORDER BY department_id

*答案：A. *HAVING 子句可以位于 GROUP BY 子句之前，但它只能在计算完组结果后才能处理。

*Answer: A. *HAVING clause can precede the GROUP BY clause but it is processed only after the group results are calculated.

59. Predict the outcome of the below query

SELECT department_id , COUNT(first_name )
FROM employees
WHERE job IN ('SALESMAN','CLERK','MANAGER','ANALYST')
GROUP BY department_id
HAVING AVG(salary ) BETWEEN 2000 AND 3000;

*答案：D. *WHERE 子句限制参与组子句处理的行数。

*Answer: D. *The WHERE clause restricts the number of rows participating in group clause processing.

60. Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement?

*答案： A、C。WHERE 和 HAVING 子句可以一起用于查询中。WHERE 排除组处理前的行，而 HAVING 则限制组。

*Answer: A, C. *WHERE and HAVING clause can be used together in a query. WHERE excludes the rows before group processing while HAVING restricts the groups.

Examine the table structure as given and answer the questions 61 and 62 that follow.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

61. 预测以下查询的结果。

*61. Predict the outcome of the below query. *

SELECT department_id , avg(salary )
FROM employees
HAVING avg(salary ) > min(salary )
GROUP BY department_id ;

*答案： C。分组函数可被 HAVING 子句用来过滤组。

*Answer: C. *Group functions can be used by HAVING clause to filter the groups.

62. Interpret the output of the below query.

SELECT SUM(AVG(LENGTH(first_name )))
FROM employees
GROUP BY department_id ;

*答案： A。组函数可以与 SELECT 查询中的单行函数或通用函数一起使用。

*Answer: A. *Group functions can be used with single row or general functions in the SELECT query.

63. Up to how many levels, the group functions can be nested?

*答案： B。分组函数最多可以嵌套 2 层。然而，单行函数可以嵌套到任意层数。

*Answer: B. *Group functions can be nested maximum up to 2 levels. However, single row functions can be nested up to any number of levels.

64. What is the limit of number of groups within the groups created by GROUP BY clause?

Answer: D. 可以形成的组和子组没有数量限制。

Answer: D. There is no limit to the number of groups and subgroups that can be formed.

65. Choose the correct statements about the HAVING clause.

*答案： A、C。只有当存在 GROUP BY 子句时，HAVING 子句才能出现在查询中，但反之则不然。

*Answer: A, C. *HAVING clause can only appear in a query if GROUP BY clause is present, but vice versa is not true.

66. 下面查询的输出是什么。

*66. What is the output of the below query. *

SELECT count(*) FROM dual GROUP BY dummy;

*答案： A。DUAL 表包含一个 CHAR(1) 类型的单列 DUMMY，其值为 'X'。

*Answer: A. *The DUAL table contains single column DUMMY of type CHAR(1) whose value is 'X'.

Based on the below scenario, answer the question from 67 to 74.

一个组织有 14 名员工以 1000 的固定工资工作。该公司招募了 5 名新员工，其工资尚未由薪资部门确定。然而，在月末处理期间，人力资源薪资部门生成了若干份报告来核对组织的财务数据。检查给定的表结构。

An organization has 14 employees who work on fixed salary of 1000. The company recruits 5 new employees whose salary is not yet fixed by the payroll department. However, during the month end processing, the HR payroll department generates several reports to reconcile the financial data of the organization. Examine the table structure as given.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

67. What is the output of the below query?

SELECT SUM (salary ) FROM employees ;

*答案： B。SUM 函数计算员工的工资总额。

*Answer: B. *The SUM function adds the salaries of the employees.

68. What is the output of the below query?

SELECT AVG (salary ) FROM employees ;

*答案：A. * AVG（薪资）函数计算薪资的平均值并忽略空值。在此情况下，AVG（薪资）=（14*1000）/14=1000。

*Answer: A. *The AVG (salary ) function calculates the average of salaries and ignoring the NULL values. In this case, AVG(salary)=(14*1000)/14=1000.

69. What is the output of the below query?

SELECT AVG (nvl(salary ,0)) FROM employees ;

*答案：C. * AVG（NVL（薪资，0））为 Null 值提供一个替代值，并使其能够参与平均值计算。在此情况下，（14*1000）/19 = 736.84。

*Answer: C. *The AVG(NVL(salary ,0)) gives an alternate value to the NULLs and enables them to participate in average calculation. In this case, (14*1000)/19 = 736.84.

70. What is the output of the below query?

SELECT VARIANCE (salary ) FROM employees ;

*答案：B. * VARIANCE（薪资）计算薪资列值的方差，忽略 Null 值。

*Answer: B. *The VARIANCE (salary ) calculates the variance of salary column values ignoring NULLs.

71. What is the output of the below query?

SELECT VARIANCE (nvl(salary ,0)) FROM employees ;

*答案：D. * VARIANCE（NL（薪资，0））计算薪资列值的方差，包括 Null 值。

*Answer: D. *The VARIANCE (NL(salary ,0)) calculates the variance of salary column values including NULLs.

72. What is the output of the below query?

SELECT STDDEV (salary ) FROM employees ;

*答案：C. * STDDEV（薪资）计算薪资列值的标准偏差，忽略 Null 值。

*Answer: C. *The STDDEV (salary ) calculates the standard deviation of salary column values ignoring NULLs.

73. What is the output of the below query?

SELECT STDDEV (nvl(salary ,0)) FROM employees ;

*答案：B. * STDDEV（nvl（薪资，0））计算薪资列值的标准偏差，包括 Null 值。

*Answer: B. *The STDDEV (nvl(salary ,0)) calculates the standard deviation of salary column values including NULLs.

74. What is the output of the below query?

Answer: C. *COUNT( ）包括 Null 值，而 COUNT（薪资）忽略 Null 值。

Answer: C. *COUNT() includes NULLs while COUNT(salary ) ignores NULL values.

75. 检查已给出的表格结构。

*75. Examine the table structure as given. *

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

下面哪个查询将给出在其中工作的员工超过 5 人的部门？

Which of the below query will give the department who have more than 5 employees working in it?

*回答：D. *

*Answer: D. *

76. Which of the following are true about the CUBE extension of GROUP BY?

*答案：B. * CUBE、ROLLUP 是用于 OLAP 处理的 GROUP BY 扩展。每当形成列的新排列时，CUBE 都会汇总结果。

*Answer: B. *CUBE, ROLLUP are the GROUP BY extensions used for OLAP processing. CUBE aggregates the results whenever a new permutation of column is formed.

Use the following SELECT statement to answer below questions 77 to 82:*

1 SELECT customer#, COUNT(*)
2 FROM customers JOIN orders USING (customer#)
3 WHERE orderdate > '02-APR-09'
4 GROUP BY customer#
5 HAVING COUNT(*) > 2;

77. Which line of the SELECT statement is used to restrict the number of records the query processes?

*答案：B. *在形成组之前，WHERE 子句用于限制行。

*Answer: B. *WHERE clause is used to restrict the rows before the groups are formed.

78. Which line of the SELECT statement is used to restrict groups displayed in the query results?

*答案：D. *在组处理完成后，HAVING 用于限制组结果。

*Answer: D. *HAVING is used to restrict the group results after the group processing is over.

79. Which line of the SELECT statement is used to group data stored in the database?

*答案：C. *GROUP BY 子句利用按列分组对表中的数据分组。

*Answer: C. *GROUP BY clause uses the group by columns to group the data in the table.

80. Which clause must be included for the query to execute successfully?

*答案：C. *因为 SELECT 子句包含 CUSTOMER# 列，所以必须在 GROUP BY 子句中有 CUSTOMER# 列。

*Answer: C. *Because the SELECT clause contains the CUSTOMER# column, it is mandatory to have GROUP BY clause with the CUSTOMER# column.

81. What is the purpose of using COUNT( ）在 SELECT 查询中？*

81. What is the purpose of using COUNT() in the SELECT query?*

Answer: B. *It counts the number of rows processing under a group. In this case, group is formed by the customer and COUNT( ）统计每个客户下的订单数。

Answer: B. *It counts the number of rows processing under a group. In this case, group is formed by the customer and COUNT() counts the orders placed by each customer.

82. Which of the following functions can be used to determine the earliest ship date for all orders recently processed by JustLee Books?

*答案：C. *MIN 函数用来检索列的最小值。当与日期列一起使用时，它会从列中提取最小日期。

*Answer: C. *MIN function is used to retrieve the least value of the column. When used with date columns, it fetches the minimum date from the column.

83. Which of the following is not a valid SELECT statement?

*答案：D. *GROUP BY 子句必须指定 SELECT 子句中包含的一个列或一组列。此处的 PUBID 不包含在 SELECT 子句中，因此查询无效。

*Answer: D. *The GROUP BY clause must specify a column or set of columns contained in the SELECT clause. Here PUBID is not contained in the SELECT clause, hence the query is not valid.

84. Which of the below statements are true about the nesting of group functions?

*答案：A、C、D. *在一个包含嵌套函数的表达式中，最里面的函数首先执行，其结果输入到下一个函数，向外移动。单行函数可以很好地与分组函数一起使用，后者最多可以嵌套 2 级。

*Answer: A, C, D. *In an expression containing nested functions, the innermost function is executed first whose result is fed into the next function moving in outwards direction. Single row functions can be well used with group functions which can be maximum nested up to 2 levels.

85. What are the statistical group functions in Oracle?

*答案：B、C. *VARIANCE 和 STATS 是 Oracle SQL 中可用的统计分组函数。

*Answer: B, C. *VARIANCE and STATS are the statistical group functions available in Oracle SQL.

86. If the SELECT list contains a column and a group functions, which of the following clause must be mandatorily included?

*答案：C. *GROUP BY 子句必须包含 SELECT 子句中包含的一个列或一组列。

*Answer: C. *GROUP BY clause should necessarily contain the column or set of columns contained in the SELECT clause.

87. 检查给定的表结构。

*87. Examine the table structure as given. *

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

下面为什么 SQL 语句不会执行的最佳解释是什么？

What is the best explanation as to why this SQL statement will NOT execute?

SELECT department_id "Department", AVG (salary)"Average"
FROM employees
GROUP BY Department;

*答案：B。*GROUP BY 子句和 HAVING 子句都无法与列别名一起使用。

*Answer: B. *Neither GROUP BY clause nor HAVING clause works with column alias.

88. 以下哪些数据类型与 AVG、SUM、VARIANCE 和 STDDEV 函数兼容？

*88. Which of the following data types are compatible with AVG, SUM, VARIANCE, and STDDEV functions? *

*答案：A。*函数 AVG、SUM、VARIANCE 和 STDDEV 只能强制与数字数据类型一起使用。

*Answer: A. *The functions AVG, SUM, VARIANCE and STDDEV mandatorily work with numeric data type only.

Examine the table structure as given below and answer the questions 89 and 90 that follow.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

89. Which of the below query will display the number of distinct job categories working in each department?

*答案：D。*使用 DISTINCT 修饰符筛选掉重复项。

*Answer: D. *Use DISTINCT modifier to filter out the duplicates.

90. Evaluate this SQL statement:

SELECT employee_id , first_name , department_id , SUM(salary )
FROM employees
WHERE salary  > 1000
GROUP BY department_id , employee_id , first_name
ORDER BY hiredate;

为什么此语句会导致错误？

Why will this statement cause an error?

*答案：D。*在 SELECT 和 ORDER BY 子句中出现的列必须包含在 GROUP BY 子句中。

*Answer: D. *All the columns appearing in SELECT and ORDER BY clause must be included in the GROUP BY clause.

91. 以下哪种关于 GROUP BY 子句的说法是正确的？

*91. Which of the following statements is true about the GROUP BY clause? *

*答案：A。*可以使用 WHERE 子句在将行划分到组之前排除行。

*Answer: A. *Using a WHERE clause, you can exclude rows before dividing them into groups.

92. 检查给定的表结构。

*92. Examine the table structure as given. *

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

解释下面查询的结果。

Interpret the outcome of the below query.

SELECT department_id , MIN (hiredate)
FROM employees
GROUP by department_id ;

*答案：C。*查询返回每个部门的最早入职员工。

*Answer: C. *The query returns the earliest hired employee in each department.

93. 哪种关于组函数的说法是正确的？

*93. Which statement about group functions is true? *

Answer: A. *All the group functions except COUNT( ）、忽略 NULL 值。这是因为它们直接处理特定列中包含的值。

Answer: A. *All the group functions except COUNT(), ignore NULL values. It is because they process the values directly contained in a specific column.

94. 以下哪些子句表示组函数的有效用法？

*94. Which of the following clauses represent valid uses of group functions? *

答案：B、C、D。组合函数只能出现在 SELECT、HAVING 和 ORDER BY 从句中。

*Answer: B, C, D. *Group functions can appear in SELECT, HAVING and ORDER BY clauses only.

95.下列有关 GROUP BY 从句的说法中，哪些是正确的？

*95. Which of the following statements are true about the GROUP BY clause? *

答案：B。数据分组基于 GROUP BY 从句中列出现的顺序。

*Answer: B. *The grouping of data is based on the sequence of columns appearing in the GROUP BY clause.

96. What is difference between WHERE clause and HAVING clause?

答案：A、B、D。WHERE 从句在分组前限制行，但 HAVING 从句限制组。

*Answer: A, B, D. *WHERE clause restricts the rows before grouping but HAVING restricts the groups.

97.检查给出的表结构。

*97. Examine the table structure as given. *

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

预测以下查询结果。

Predict the outcome of the below query.

SELECT department_id ,job,count(*)
FROM employees
GROUP BY department_id ,job
ORDER BY department_id ,count(*);

答案：A。ORDER BY 从句可将组函数用于排序。

*Answer: A. *ORDER BY clause can use the group functions for sorting.