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 函数。

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

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

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

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 函数可用于返回部门中每个组由一个部门组成时的最大工资。

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

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

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

*答案：B。 *带参数函数的数据类型可以是 CHAR、VARCHAR2、NUMBER 或 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 值。

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 值但统计重复值。

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 值但统计重复值。

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) 统计不同的非空值。

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 ' '。

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

  • [style="loweralpha"]一、该函数接受多个数字输入并返回所有值的方差*

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

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

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

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

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 作为一个分组函数，计算在部门工作的一组员工的薪水总额。

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 列表中的组之前，将形成组，并计算组函数。

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”。

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

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

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

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

18. Which of the following statements is correct?

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

  • [style="arabic"]1. 下列哪项不是有效的 SQL 语句？*

答案：A。

20. Which of the following statements is correct?

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

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

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

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

答案：A。

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

  1. 组函数可以嵌套在组函数内。

  2. 组函数可以嵌套在单行函数内。

  3. 单行函数可以嵌套在组函数内。

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

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

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

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

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

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

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

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 ' ' 和列名规范。

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

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

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 子句是最后一个要处理的子句，用于对最终数据集进行排序。

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 子句根据指定列列表对数据进行分组。

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

回答：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 会引发异常。

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 函数返回由部门形成的组中的最低工资。

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. *

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

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

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

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 ;

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

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

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

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

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

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查询中的哪个子句会产生错误？

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。

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查询会显示每个职位类别的最大和最小工资？

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

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)

查看下面查询中的错误。

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子句中。

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

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 job, COUNT(employee_id ),sum(salary )
FROM employees
GROUP BY job
HAVING SUM (salary ) > 5000;

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

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

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

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

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

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 子句不支持嵌套聚合函数。

45. Predict the output of the below query

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

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

46. Predict the output of the below query

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

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

47. Which of the below query executes successfully?

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

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 子句中，以从最后的数据集中过滤出组。

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

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

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

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

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

51. Which of the following statements are true?

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

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)

找出以下查询中的错误。

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

*回答：D. *

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

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

54. 检查给定的表结构。

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)

预测以下两个查询的输出

Query - 1

SELECT avg(comm)
FROM employees ;

Query - 2

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

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

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

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

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)

预测下面这个查询的结果

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

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

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

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

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 子句之前，但它只能在计算完组结果后才能处理。

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 子句限制参与组子句处理的行数。

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

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

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. 预测以下查询的结果。

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

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

62. Interpret the output of the below query.

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

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

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

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

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

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

65. Choose the correct statements about the HAVING clause.

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

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

SELECT count(*) FROM dual GROUP BY dummy;

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

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

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

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 函数计算员工的工资总额。

68. What is the output of the below query?

SELECT AVG (salary ) FROM employees ;

*答案：A. * AVG（薪资）函数计算薪资的平均值并忽略空值。在此情况下，AVG（薪资）=（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。

70. What is the output of the below query?

SELECT VARIANCE (salary ) FROM employees ;

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

71. What is the output of the below query?

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

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

72. What is the output of the below query?

SELECT STDDEV (salary ) FROM employees ;

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

73. What is the output of the below query?

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

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

74. What is the output of the below query?

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

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

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 人的部门？

*回答：D. *

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

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

  • 使用以下 SELECT 语句回答 77 至 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 子句用于限制行。

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

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

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

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

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

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

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

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

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 函数用来检索列的最小值。当与日期列一起使用时，它会从列中提取最小日期。

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

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

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

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

85. What are the statistical group functions in Oracle?

*答案：B、C. *VARIANCE 和 STATS 是 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 子句中包含的一个列或一组列。

87. 检查给定的表结构。

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 语句不会执行的最佳解释是什么？

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

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

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

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

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 修饰符筛选掉重复项。

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;

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

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

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

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

92. 检查给定的表结构。

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 , MIN (hiredate)
FROM employees
GROUP by department_id ;

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

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

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

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

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

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

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

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

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

97.检查给出的表结构。

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 ,job,count(*)
FROM employees
GROUP BY department_id ,job
ORDER BY department_id ,count(*);

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