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. (*)
*OBS: New Question :
12. 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.
