Shiridi Sai Baba

Group Functions - Questions


1. What result is returned by the following statement?
SELECT COUNT(*) FROM DUAL; (Choose the best answer.)

a.    NULL
b.    0
c.     1
d.    None of the above

2. Choose one correct statement regarding group functions.

a.    Group functions may only be used when a GROUP BY clause is present.
b.    Group functions can operate on multiple rows at a time.
c.     Group functions only operate on a single row at a time.
d.    Group functions can execute multiple times within a single group.

3. What value is returned after executing the following statement?
SELECT SUM(SALARY) FROM EMPLOYEES;
     Assume there are 10 employee records and each contains a SALARY value of 100, except for 1,
     which has a null value in the SALARY field. (Choose the best answer.)

a.    900
b.    1000
c.     NULL
d.    None of the above

4. Which values are returned after executing the following statement?
SELECT COUNT(*), COUNT(SALARY) FROM EMPLOYEES;
   Assume there are 10 employee records and each contains a SALARY value of 100, except for 1,
   which has a null value in their SALARY field. (Choose all that apply.)

a.    10 and 10
b.    10 and NULL
c.    10 and 9
d.    None of the above

5. What value is returned after executing the following statement?
SELECT AVG(NVL(SALARY,100)) FROM EMPLOYEES;
   Assume there are ten employee records and each contains a SALARY value of 100, except for
   one employee, who has a null value in the SALARY field. (Choose the best answer.)

a.    NULL
b.    90
c.     100
d.   None of the above

6. What value is returned after executing the following statement?
SELECT SUM((AVG(LENGTH(NVL(SALARY,0))))) FROM EMPLOYEES

GROUP BY SALARY;
   Assume there are ten employee records and each contains a SALARY value of 100, except for
   one, which has a null value in the SALARY field. (Choose the best answer.)

a.    An error is returned
b.    3
c.     4
d.    None of the above

7. How many records are returned by the following query?
SELECT SUM(SALARY), DEPARTMENT_ID FROM EMPLOYEES

GROUP BY DEPARTMENT_ID;
   Assume there are 11 nonnull and 1 null unique DEPARTMENT_ID values. All records have a
   nonnull SALARY value. (Choose the best answer.)

a.    12
b.    11
c.     NULL
d.    None of the above

8. What values are returned after executing the following statement?
SELECT JOB_ID, MAX_SALARY FROM JOBS GROUP BY MAX_SALARY;
   Assume that the JOBS table has ten records with the same JOB_ID value of DBA and the same
   MAX_SALARY value of 100. (Choose the best answer.)

a.    One row of output with the values DBA, 100
b.    Ten rows of output with the values DBA, 100
c.     An error is returned
d.    None of the above

9. How many rows of data are returned after executing the following statement?
SELECT DEPT_ID, SUM(NVL(SALARY,100)) FROM EMP

GROUP BY DEPT_ID HAVING SUM(SALARY) > 400;
   Assume the EMP table has ten rows and each contains a SALARY value of 100, except for one,
   which has a null value in the SALARY field. The first and second five rows have DEPT_ID
   values of 10 and 20, respectively. (Choose the best answer.)

a.    Two rows
b.    One row
c.     Zero rows
d.    None of the above

10. How many rows of data are returned after executing the following statement?
SELECT DEPT_ID, SUM(SALARY) FROM EMP GROUP BY DEPT_ID HAVING

SUM(NVL(SALARY,100)) > 400;
   Assume the EMP table has ten rows and each contains a SALARY value of 100, except for one,
   which has a null value in the SALARY field. The first and second five rows have DEPT_ID
   values of 10 and 20, respectively. (Choose the best answer.)

a.    Two rows
b.    One row
c.     Zero rows
d.    None of the above