使用组功能问题

1.以下哪一项不是GROUP BY函数？

1. MAX

2. MIN

3. NVL

4. AVG

答案:C. NVL是一种通用函数，用于为NULL值提供备用值. MAX，MIN和AVG函数可用作GROUP BY函数.

2. SELECT查询中没有GROUP BY子句可以使用以下哪些函数？

1. COUNT

2. MAX

3. MIN

4. AVG

答案: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)

1.  SELECT department_id，max(salary)FROM employees;

2.  SELECT department_id，max(salary)FROM employees GROUP BY department_id;

3.  SELECT max(salary)FROM employees GROUP BY department_id;

4.  SELECT max(salary)FROM employees;

答案:B. MAX功能n可用于返回每个组由部门组成的部门的最高工资.

4.关于COUNT函数，下列哪一项陈述是正确的？

1. COUNT函数计算数字行

2. COUNT(*)函数计算具有重复项和NULL值的行数

3. COUNT(DISTINCT)函数计算的数量不同的行

4. COUNT(*)相当于COUNT(ALL)

答案:B. COUNT(*)计算包括重复和NULL的行数.使用DISTINCT和ALL关键字来限制重复和
NULL值.

5. GROUP BY函数接受的适当数据类型是什么？

1. 嵌套表

2. NUMBER

3. CLOB

4. DATE

答案:B.带参数的函数的数据类型可以是CHAR，VARCHAR2，NUMBER或DATE.

6.表T_COUNT具有12个数值，分别为1,2,3,32,1,1，null，24,12，null，32，null.预测以下查询的输出.

 SELECT COUNT(*)FROM t_count;

1. 12

2. 6

3. 9

4. 引发异常，因为COUNT函数不适用于NULL值

答案:A. COUNT(* )计算包括重复和NULL的行数.使用DISTINCT和ALL关键字来限制重复和
NULL值.

7.表T_COUNT具有12个数值，分别为1,2,3,32,1,1，null，24,12，null，32，null.预测以下查询的输出.

 SELECT COUNT(num)FROM t_count;

1. 12

2. 6

3. 9

4. 引发异常，因为COUNT函数不适用于NULL值

答案:C. COUNT(列)忽略NULL值但计算重复项.

8.表T_COUNT具有12个数值，分别为1,2,3,32,1,1，null，24,12，null，32，null.预测以下查询的输出.

 SELECT COUNT(ALL num)FROM t_count;

1. 12

2. 6

3. 9

4. 引发异常，因为COUNT函数不能与NULL值一起使用

答案:C. COUNT(全部列)忽略NULL值但计算重复项.

9.表T_COUNT具有12个数值，分别为1,2,3,32,1,1，null，24,12，null，32，null.预测以下查询的输出.

 SELECT COUNT(DISTINCT num)FROM t_count;

1. 12

2. 6

3. 9

4. 引发异常，因为COUNT函数不适用于NULL值

答案:B. COUNT(DISTINCT列)计算不同的非空值.

10.在SQL * Plus中执行以下查询时会发生什么？

 SELECT COUNT()FROM dual;

1. 执行成功并且不返回任何输出

2. 执行成功并将输出返回为"1"

3. 引发异常"ORA-00909:参数数量无效"

4. 引发异常"ORA-00904:"COUNT":无效标识符"因为COUNT函数没有不适用于DUAL表

答案:C. COUNT函数需要至少一个参数，该参数可以是[所有| DISTINCT]修饰符或'*'.

11.这里有一些关于SQL中VARIANCE函数的陈述.

i.该函数接受多个数字输入并返回所有值的方差

ii.该函数接受一个数字列并返回所有列值的方差，包括NULL

iii.该函数接受一个数字列并返回除NULL以外的所有列值的方差

从以下选项中选择正确的组合.

1. i和iii

2. i和ii

3. ii

4. iii

答案:C. VARIANCE函数接受单个数字参数作为列名并返回方差考虑
NULLs的所有列值.

12.以下哪项不是SQL中的GROUP BY扩展？

1. GROUP BY

2. GROUPING SETS

3. CUBE

4. ROLLUP

答案:A. GROUPING SETS操作可用于通过单个查询执行多个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 ;

1. SUM是按功能划分的，因为它处理在部门工作的一组员工

2. SUM是一个聚合函数，因为它为每组数据生成一个结果

3. SUM是一个单行函数，因为它返回一个组的单个值即部门

4. SUM是一个按扩展名分组的功能，因为它使用GROUP BY克劳斯e对部门进行逻辑分组

答案:A. SUM是一个组函数，用于计算组的工资总和在某个部门工作的员工.

14.哪个子句用于根据使用按功能分组的聚合结果过滤查询输出？

1. WHERE

2. LIMIT

3. GROUP WHERE

4. HAVING

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

15.检查给定的表结构并预测以下查询的结果.

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;

1. 查询返回无佣金的员工数

2. 查询抛出错误，因为在搜索NULL值时无法使用等号

3. 查询返回佣金为NULL值的部门中的员工数

4. 查询抛出错误，因为查询中缺少GROUP BY子句

答案:B.使用WHERE条件排除NULL是一种指导查询忽略NULL的方法.但是这里IS NULL运算符的用法是
错误.条件应为'WHERE comm IS NULL'.

16.关于组函数，下列哪一项是正确的？

1. 可以使用MIN函数只有数字数据.

2. MAX函数只能用于日期值.

3. AVG函数只能用于数字数据.

4. SUM函数不能是嵌套函数的一部分.

答案:C. AVG功能只能用于数值.具有此类限制的其他函数是SUM，STDDEV和VARIANCE.

17.以下哪一项是有效的SELECT语句？

1.  SELECT AVG(零售成本)FROM books GROUP BY类别;

2.  SELECT类别，AVG(零售成本) FROM books;

3.  SELECT类别，AVG(零售成本)FROM books WHERE AVG(零售成本)> 8.56 GROUP BY类别;

4.  SELECT类别，AVG(零售成本)利润来自书籍GROUP BY类别获利> 8.56 ;

答案:A.列别名不能用于GROUP BY或HAVING子句.

18.以下哪项陈述是正确的？

1. WHERE子句只能包含组函数该函数也没有列在SELECT子句中.

2. 组函数不能用于SELECT，FROM或WHERE子句.

3. HAVING子句始终在WHERE子句之前处理.

4. GROUP BY子句始终在HAVING子句之前处理.

答案:D.虽然如果HAVING子句在GROUP BY子句之前，Oracle不会引发错误，但只有在处理GROUP BY子句并准备好过滤组之后才处理它.

19.以下哪项不是有效的SQL语句？

1.  SELECT MIN(pubdate)FROM books GROUP BY category HAVING pubid = 4;

2.  SELECT MIN(pubdate)FROM books WHERE category ='COOKING';

3.  SELECT COUNT(*)FROM orders WHERE customer＃= 1005;

4.  SELECT MAX(COUNT(customer＃))来自订单GROUP BY客户＃;

答案:A.

20.以下哪项陈述是正确的？

1. COUNT函数可用于确定多少行包含NULL值.

2. 除非ALL子句包含在SELECT子句中，否则组函数中只包含不同的值.

3. WHERE子句限制处理哪些行.

4. HAVING子句确定在查询结果中显示哪些组.

答案:C，D.在HAVING子句限制组时，WHERE子句在对行进行分组和处理之前限制行.

21.以下哪项是有效的SQL语句？

1.  SELECT customer＃，order＃，MAX(shipdate-orderdate)FROM orders GROUP BY customer＃WHERE customer＃= 1001;

2.  SELECT customer＃，COUNT(order＃)FROM orders GROUP BY customer＃;

3.  SELECT customer＃， COUNT(订单＃)FROM订单GROUP BY COUNT(订单＃);

4.  SELECT customer＃，COUNT(订单＃ )FROM FROM GROUP BY order＃;

答案:B. GROUP BY子句必须包含除组函数内使用的列之外的所有列.

22.以下哪个SELECT语句仅列出利润最大的图书？

1.  SELECT title，MAX(零售成本)FROM books GROUP BY title;

2.  SELECT title ，MAX(零售成本)FROM books GROUP BY title HAVING MAX(零售成本);

3.  SELECT title， MAX(零售费用)FROM books;

4. 以上都不是

答案:A.

23.以下哪项陈述是正确的？

1.组函数可以嵌套在组函数中.

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

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

1. 1

2. 2

3. 3

4. 1和3

答案:A，B，C.组函数只能嵌套到2的深度.组函数可以嵌套在单行函数中(嵌入在TO_CHAR函数中的AVG).此外，单行函数可以嵌套在组函数中.

24.以下哪个函数用于计算存储在指定列中的总值？

1. COUNT

2. ADD

3. TOTAL

4. SUM

答案:D. SUM函数用于获取数值.

25.以下哪个SELECT语句列出了家庭类别中所有图书的最高零售价格？

1.  SELECT MAX(零售)FROM books WHERE category ='FAMILY';

2.  SELECT MAX(零售)FROM books HAVING category ='FAMILY';

3.  SELECT retail FROM books WHERE category =' FAMILY'拥有最大值(零售);

4. 以上都不是

答案:A.由于在分组之前必须限制类别FAMILY，因此必须使用WHERE子句而不是HAVING子句过滤表行.

26.可以使用以下哪些函数在计算中包含NULL值？

1. SUM

2. NVL

3. MAX

4. MIN

答案:B. NVL是一种为NULL值提供备用值的通用函数.它确实可以使用AVG，STDDEV和VARIANCE组函数进行算术计算.

27.以下哪项不是有效的声明？

1. 您必须输入ALL关键字组函数包含所有重复值.

2. AVG函数可用于查找两个日期之间的平均计算差异.

3. MIN和MAX函数可用于VARCHAR2列.

4. 以上所有

答案:A. ALL关键字计算重复项但忽略NULL.副本也包含在'*'和列名称规范中.

28.以下哪个SQL语句决定了其他客户推荐的总客户数量？

1.  SELECT customer＃，SUM(refer)FROM customers GROUP BY customer＃;

2.  SELECT COUNT(推荐)FROM customers;

3.  SELECT COUNT(*)FROM customers;

4.  SELECT COUNT(*)FROM customers WHERE引用IS NULL;

答案:B.将所有客户视为一个群组，COUNT(推荐)仅会计算某人推荐的人数. COUNT(引用)将
忽略列的NULL值.

29.确定SELECT语句中以下子句的正确执行顺序.

1.SELECT

2.FROM

3 .WHERE

4.GROUP BY

5.HAVING

6.ORDER BY

1. 2-3-4-5-1-6

2. 1-2-3-4-5-6

3. 6-5-4-3-2-1

4. 5-4-2-3-1-6

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

30.以下哪个子句用于根据一列或一组列对一组行进行分组？

1. HAVING

2. WHERE

3. GROUP BY

4. GROUPING

答案:C. GROUP BY子句根据指定的列表构成数据组.

31.以下哪组功能可用于人口差异和人口标准差问题？

1. VAR_POP

2. STDDEV_POP

3. VARIANCE

4. STDDEV_SASMP

答案:A，B.

32.在SELECT查询中选择可以显示组功能的位置.

1. SELECT语句

2. WHERE子句

3. ORDER BY子句

4. GROUP BY子句

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

33.检查给定的EMPLOYEES表的结构.哪个查询将返回每个部门的最低工资？

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)

1. 选择department_id，MIN(薪水)来自EMPLOYEES;

2.  SELECT department_id，MIN(薪水)来自EMPLOYEES GROUP BY department_id;

3.  SELECT department_id，来自EMPLOYEES GROUP BY薪水的MIN(薪水);

4.  SELECT department_id，MIN(薪水)来自EMPLOYEES GROUP BY employee_id;

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

34.检查表EMPLOYEES的结构并解释以下查询的输出

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;

1. 它会抛出错误，因为在查询中只能使用一个聚合函数.

2. 因为缺少GROUP BY子句而抛出错误.

3. 它成功执行并为两者返回相同的值.

4. 它成功执行COUNT (*)包括NULL和COUNT(所有comm)，不包括NULL.

答案:D.

35.关于群组功能，以下哪一项是正确的？

1. 您可以在SELECT语句的任何子句中使用组函数.

2. 只能在select子句的列列表和SELECT语句的WHERE子句中使用组函数.

3. 可以将单行列与组函数混合使用通过对单行列进行分组，在SELECT语句的列列表中.

4. 您可以将列名，表达式，常量或函数作为参数传递给组函数.

答案:C.组功能只能嵌套到两个深度.组函数可以嵌套在单行函数中(嵌入在
TO_CHAR函数中的AVG).此外，单行函数可以嵌套在组函数中.

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 
其中e.department_id = d.department_id 
 GROUP by d.department_name;

关于执行上述陈述，哪个陈述是正确的？

1. 视图将被创建您可以在视图上执行DLM操作

2. 不会创建视图，因为c不允许使用join语句创建视图

3. 视图将不会创建，因为创建视图不允许使用GROUP BY子句

4. 视图将被创建但不会视图上将允许DML操作

答案:D.在视图上执行DML操作的规则.如果视图包含组函数或GROUP BY子句或DISTINCT关键字，则无法通过视图添加数据.伪列ROWNUM关键字列由基本表中的表达式NOT NULL列定义，未由
视图选择.

37.关于视图，下列哪一项陈述是正确的？

1. 定义视图的子查询不能包含GROUP BY子句

2. 使用可以更新DISTINCT关键字的子查询创建视图

3. 数据操作语言(DML)操作可以在使用具有表的所有NOT NULL列的子查询创建的视图上执行

4. 使用具有伪列ROWNUM关键字的子查询创建的视图不能已更新

答案:C，D.在视图上执行DML操作的规则.如果视图包含组函数或GROUP BY子句或DISTINCT关键字，则无法通过视图添加数据.伪列ROWNUM关键字列由基本表中的表达式NOT NULL列定义，未由
视图选择.

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 ;

1. WHERE

2. SELECT

3. ORDER BY

4. GROUP BY

答案:D. GROUP BY子句必须包含SELECT语句中出现的所有列.它会引发错误，因为JOB不是选定的
列.它应该在放置JOB时使用DEPARTMENT_ID.

39.检查给定的表结构.

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查询将显示每个工作类别所赚取的最高和最低工资？

1.  SELECT job，MAX(薪水)，MIN(薪水)FROM employees GROUP BY department_id;

2.  SELECT job，MAX(薪水)，MIN(薪水)FROM employees GROUP BY job;

3.  SELECT job，MAX(薪水)，MIN(薪水)FROM employees;

4. 两个聚合函数不能在SELECT语句中一起使用.

答案:B. SELECT中可以出现多个组函数声明.

40.考虑给定的表结构.

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;

1. 它成功执行并生成所需的结果.

2. 它会产生错误，因为COID(*)也应该在SELECT子句中指定.

3. 它成功执行但没有产生结果，因为COUNT(p应该使用rod_id)而不是COUNT(*).

4. 它会产生错误，因为COUNT(*)应仅在HAVING子句中，而不在WHERE子句中.

答案: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;

1. It executes successfully and lists the count of employees under each job category but ignores the HAVING clause since "salary " is not in GROUP BY clause.

2. It throws error because HAVING clause is invalid.

3. It throws error because "salary " is not included in the GROUP BY clause.

4. It executes successfully and lists the count of employees under each category having sum of salary greater than 5000.

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?

1. Group functions on columns ignore NULL values.

2. Group functions on columns returning dates include NULL values.

3. Group functions on columns returning numbers include NULL values.

4. Group functions on columns cannot be accurately used on columns that contain NULL values.

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?

1. Subqueries can contain GROUP BY and ORDER BY clauses.

2. Subqueries cannot contain GROUP BY and ORDER BY clauses.

3. Subqueries can contain ORDER BY but not the GROUP BY clause.

4. Subqueries cannot contain ORDER BY but can have GROUP BY clause.

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;

1. It executes successfully.

2. It gives an error because the HAVING clause is not valid.

3. It gives an error because the GROUP BY expression is not valid.

4. It gives an error because aggregate functions cannot be nested in SELECT statement.

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 ;

1. It gives error because an aggregate function cannot appear just after SELECT clause.

2. It gives error because GROUP BY clause is invalid.

3. It executes without errors but produces no output.

4. It executes successfully and gives average salary in each department.

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);

1. It executes successfully and displays "job" in lower case.

2. It executes successfully but display "job" in original case.

3. It throws error because singe row and aggregate functions cannot be used together.

4. It throws error because case conversion in the SELECT list mismatches with the case conversion GROUP BY clause.

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?

1. SELECT employee_id , COUNT(hiredate-sysdate) FROM employees ;

2. SELECT AVG(salary ), MAX(salary ) FROM employees ;

3. SELECT AVG(salary ), MAX(salary ) FROM employees  GROUP BY department_id ;

4. SELECT AVG(hiredate) FROM 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;

1. It executes successfully and displays average salary of departments higher than 10.

2. It throws error because non aggregated column cannot be used in HAVING clause.

3. It executes successfully but displays wrong result for the departments.

4. It throws error because HAVING clause must be placed before GROUP BY clause.

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

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);

1. It throws error because multiple conditions cannot be given in HAVING clause.

2. It throws error because a non aggregate column cannot be used in HAVING clause.

3. It executes successfully and displays average salary of department higher than 10 and greater than 2000.

4. It executes successfully but no result is displayed.

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?

1. AVG

2. MIN

3. SUM

4. COUNT

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

51. Which of the following statements are true?

1. AVG and SUM can be used only with numeric data types.

2. STDDEV and VARIANCE can be used only with numeric data types.

3. MAX can be used with LONG data type.

4. MAX and MIN cannot be used with LOB or LONG data types.

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 ;

1. Multiple aggregate functions cannot be used in a single SELECT query

2. GROUP BY clause is invalid

3. COUNT function cannot be used with DATE values

4. No errors and it executes successfully

Answer: D.

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

1. MAX

2. MIN

3. COUNT

4. None of these

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

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 ;

1. Both the queries produce same result

2. Query - 1 and Query - 2 produce different results because Query-1 considers NULL values of COMM and Query-2 substitutes NULL values of COMM with zero

3. Query - 1 produces error because COMM has NULL values

4. Query - 2 produces error because NVL cannot be nested with aggregate function.

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.

1. Column alias can be used in the GROUP BY clause.

2. GROUP BY column must be in the SELECT clause.

3. GROUP BY clause must appear together with HAVING clause a SELECT query.

4. GROUP BY clause must appear after WHERE clause in a SELECT query.

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 ;

1. It throws error because GROUP BY column list doesn’t matches with SELECT column list.

2. It executes successfully and produces average salary of a job category in each department.

3. It executes successfully and produces average salary for a department in eac h job category.

4. It throws error because GROUP BY and ORDER BY clause have different list of columns.

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?

1. WHERE

2. HAVING

3. GROUP BY

4. ORDER BY

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

1. It executes successfully.

2. It throws error because HAVING clause precedes the GROUP BY clause.

3. It throws error because HAVING clause uses the aggregate function.

4. It executes but no results are displayed because HAVING clause precedes the GROUP BY clause.

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;

1. It returns an error because the BETWEEN operator cannot be used in the HAVING clause.

2. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.

3. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.

4. It executes successfully.

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?

1. The HAVING clause can be used with group functions in subqueries.

2. The WHERE clause can be used to exclude rows after dividing them into groups.

3. The WHERE clause can be used to exclude rows before dividing them into groups.

4. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.

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. Predict the outcome of the below query.

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

1. It throws an error because the aggregate functions used in HAVING clause must be in SELECT list.

2. It throws an error because the HAVING clause appears before GROUP BY clause.

3. It displays the departments whose average salary is greater than the minimum salary of the department.

4. It displays the departments whose average salary is greater than the minimum salary of the organization.

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 ;

1. It calculates the sum of averages of length of employee’s name in each department.

2. It calculates the average length of employee’s name in each department.

3. It throws error because single row function cannot be used with group functions.

4. It throws error because group column DEPARTMENT_ID  is not used in the SELECT list.

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?

1. 1

2. 2

3. 3

4. No limits

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?

1. 1

2. 2

3. 3

4. No Limit

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.

1. The HAVING clause is an optional clause in SELECT statement.

2. The HAVING clause is a mandatory clause if SELECT statement uses a GROUP BY clause.

3. The HAVING clause can appear in a SELECT statement only if it uses a GROUP BY clause.

4. The HAVING clause is a mandatory clause if SELECT statement uses a GROUP BY clause.

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

66. What is the output of the below query.

SELECT count(*) FROM dual GROUP BY dummy;

1. 1

2. 0

3. NULL

4. Throws error because group functions cannot be applied on DUAL table.

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.

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 ;

1. NULL

2. 14000

3. 19000

4. 0

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 ;

1. 1000

2. 736.84

3. NULL

4. 0

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 ;

1. 1000

2. NULL

3. 736.84

4. 0

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 ;

1. 1000

2. 0

3. NULL

4. 204678.36

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 ;

1. 1000

2. 0

3. NULL

4. 204678.36

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 ;

1. 1

2. 1000

3. 0

4. 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 ;

1. 0

2. 452.41

3. 1000

4. 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?

select count(*),count(salary ) from employees  ;

1. 19,19

2. 14,19

3. 19,14

4. 14,14

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

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)

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

1. SELECT department_id  FROM employees  WHERE COUNT(*)> 5 GROUP BY department_id ;

2. SELECT department_id  FROM employees  HAVING COUNT(*)> 5;

3. SELECT department_id  FROM employees  GROUP BY employee_id  HAVING COUNT(*)> 5;

4. SELECT department_id  FROM employees  GROUP BY department_id  HAVING COUNT(*)> 5;

Answer: D.

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

1. Enables performing multiple GROUP BY clauses with a single query.

2. Performs aggregations for all possible combinations of columns included.

3. Performs increasing levels of cumulative subtotals, based on the provided column list.

4. None of the above

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?

1. 1

2. 3

3. 4

4. 5

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?

1. 1

2. 3

3. 4

4. 5

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?

1. 1

2. 3

3. 4

4. 5

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?

1. 1

2. 3

3. 4

4. 5

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(*) in the SELECT query?

1. The number of records in the specified tables

2. The number of orders placed by each customer

3. The number of NULL values in the specified tables

4. The number of customers who have placed an order

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?

1. COUNT function

2. MAX function

3. MIN function

4. STDDEV function

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?

1. SELECT STDDEV(retail) FROM books;

2. SELECT AVG(SUM(retail)) FROM orders NATURAL JOIN orderitems NATURAL JOIN books GROUP BY customer#;

3. SELECT order#, TO_CHAR(SUM(retail),’999.99’) FROM orderitems JOIN books USING (isbn) GROUP BY order#;

4. SELECT title, VARIANCE(retail-cost) FROM books GROUP BY pubid;

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?

1. The inner most function is resolved first.

2. Oracle allows nesting of group function up to 3 levels.

3. Single row functions can be nested with group functions.

4. Oracle allows nesting of group function up to 2 levels.

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?

1. AVG

2. STDDEV

3. VARIANCE

4. STATS

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?

1. ORDER BY

2. HAVING

3. GROUP BY

4. None of these

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

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)

 

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;

1. Salaries cannot be averaged as not all the numbers will divide evenly.

2. You cannot use a column alias in the GROUP BY clause.

3. The GROUP BY clause must have something to GROUP.

4. The department id is not listed in the depart ments table.

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

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

1. Only numeric data types

2. Integers only

3. Any data type

4. All except numeric

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?

1. SELECT department_id , COUNT(DISTINCT job) FROM employees  GROUP BY job;

2. SELECT department_id , COUNT(job) FROM employees  GROUP BY employee_id ;

3. SELECT department_id , COUNT(job) FROM employees  GROUP BY department_id ;

4. SELECT department_id , COUNT(DISTINCT job) FROM employees  GROUP BY department_id ;

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?

1. The HAVING clause is missing.

2. The WHERE clause contains a syntax error.

3. The SALARY column is NOT included in the GROUP BY clause.

4. The HIRE_DATE column is NOT included in the GROUP BY clause.

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

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

1. To exclude rows before dividing them into groups using the GROUP BY clause, you use should a WHERE clause.

2. You must use the HAVING clause with the GROUP BY clause.

3. Column alias can be used in a GROUP BY clause.

4. By default, rows are not sorted when a GROUP BY clause is used.

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

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 ;

1. The earliest hire date in the organization.

2. The latest hire date in the organization.

3. The earliest hire date in a department.

4. The latest hire date in a department.

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

93. Which statement about group functions is true?

1. Group functions except COUNT(*), ignore null values.

2. A query that includes a group function in the SELECT list must include a GROUP BY clause.

3. Group functions can be used in a WHERE clause.

4. Group functions can only be used in a SELECT list.

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. Which of the following clauses represent valid uses of group functions?

1. GROUP BY MAX(salary)

2. ORDER BY AVG(salary)

3. HAVING MAX(salary)> 10000

4. SELECT AVG(NVL(salary, 0))

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

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

1. The last column listed in the GROUP BY clause is the most major grouping.

2. The first column listed in the GROUP BY clause is the most major grouping.

3. A GROUP BY clause cannot be used without an ORDER BY clause.

4. The GROUP BY clause do not ensure the sorting of output.

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?

1. WHERE clause restrict rows before grouping while HAVING clause restricts groups.

2. WHERE clause cannot contain a group function but HAVING clause can have.

3. WHERE clause can join multiple conditions using AND or OR operators but HAVING clause cannot.

4. WHERE clause can appear in SELECT query without GROUP BY clause but HAVING clause cannot.

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

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(*);

1. It executes successfully.

2. It throws error because ORDER BY clause is invalid.

3. It throws error because GROUP BY clause is invalid.

4. It throws error because GROUP BY and ORDER BY clause cannot be used together.

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