duminică, 4 aprilie 2010

Test: Quiz: Group By and Having Clauses, ROLLUP and CUBE Operations, and Grouping Sets

Section 1

1. Which of the following SQL statements could display the number of people with the same last name: Mark for Review
(1) Points


SELECT first_name, last_name, COUNT(employee_id)
FROM EMPLOYEES
GROUP BY last_name;



SELECT employee_id, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name;



SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name;

(*)


SELECT employee_id, DISTINCT(last_name)
FROM EMPLOYEES
GROUP BY last_name;





2. The use of GROUP BY GROUPING SETS(....) can speed up the execution of complex report statements? (True or False) Mark for Review
(1) Points


True (*)


False




3. The following is a valid statement:

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

True or False?
Mark for Review
(1) Points


True (*)


False




4. Read the following SELECT statement. Choose the column or columns that must be included in the GROUP BY clause.

SELECT COUNT(last_name), grade, gender
FROM STUDENTS
GROUP_BY ?????;
Mark for Review
(1) Points


last_name


last_name, grade


grade, gender (*)


last_name, gender




5. Examine the following statement:

SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS((department_id, manager_id), (department_id, job_id))

What data will this query generate?

Mark for Review
(1) Points


Total salaries for (department_id, job_id) and (department_id, manager_id) (*)


Total salaries for (department_id, job_id, manager_id)


Total for (job_id, manager_id)


The statement will fail.




6. If you want to include subtotals and grant totals for all columns mentioned in a GROUP BY clause you should use which of the following extensions to the GROUP BY clause? Mark for Review
(1) Points


ROLLUP


CUBE (*)


GROUP BY ALL COLUMNS


HAVING




7. Is the following statement correct?

SELECT department_id, AVG(salary)
FROM employees;

Mark for Review
(1) Points


No, because a GROUP BY department_id clause is needed (*)


No, because the SELECT clause cannot contain both individual columns and group functions


No, because the AVG function cannot be used on the salary column


Yes




8. Examine the following statement:

SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS(.......);

Select the correct GROUP BY GROUPING SETS clause from the following list:

Mark for Review
(1) Points


GROUP BY GROUPING SETS (department_id, AVG(salary)), (department_id, job_id), (department_id, manager_id)


GROUP BY GROUPING SETS (department_id, salary), (department_id, job_id), (department_id, manager_id)


GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, job_id), (manager_id, job_id)) (*)


GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, SUM(salary), (manager_id, job_id))




9. How would you alter the following query to list only employees where more than one employee exists with the same last_name:

SELECT last_name, COUNT(employee_id)
FROM EMPLOYEES
GROUP BY last_name;

Mark for Review
(1) Points


SELECT last_name, COUNT(employee_id)
FROM EMPLOYEES
WHERE COUNT(*) > 1
GROUP BY last_name



SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name
HAVING COUNT(last_name) > 1;

(*)


SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name
EXISTS COUNT(last_name) > 1;



SELECT employee_id, DISTINCT(last_name)
FROM EMPLOYEES
GROUP BY last_name
HAVING last_name > 1;







10. Is the following statement correct:

SELECT first_name, last_name, salary, department_id, COUNT(employee_id)
FROM employees
WHERE department_id = 50
GROUP BY last_name, first_name, department_id;

Mark for Review
(1) Points


Yes


No, beause you cannot have a WHERE-clause when you use group functions.


No, because the statement is missing salary in the GROUP BY clause (*)


Yes, because Oracle will correct any mistakes in the statement itself






11. Examine the following statement:

SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id, manager_id)

What extra data will this query generate?

Mark for Review
(1) Points


Subtotals for department_id, and grand totals for salary.


Subtotals for department_id, job_id and grand totals for salary.


Subtotals for department_id, job_id, manager_id and grand totals for salary.


The statement will fail. (*)





http://www.materie.ro/materie/test-quiz-group-by-and-having-clauses-rollup-and-cube-operations-and-grouping-sets-44

2 comentarii:

  1. A mai aparut o intrebare... acum Quizul acesta este impartit in doua.

    Examine the following statement:
    SELECT department_id, manager_id, job_id, SUM(salary)
    FROM employees
    GROUP BY GROUPING SETS((department_id, manager_id), (department_id, job_id))

    What data will this query generate?

    Mark for Review
    (1) Points

    Sum of salaries for (department_id, job_id) and (department_id, manager_id) (*)

    Sum of salaries for (department_id, job_id, manager_id)

    Subtotals for (job_id, manager_id)

    The statement will fail.


    Correct

    RăspundeţiȘtergere
  2. thx ;)..chiar nu stiam ca a mai aparut inca o intrebare.

    RăspundeţiȘtergere