Test Your Oracle GROUP BY Clause Knowledge with This SQL Quiz

Progress

True / False

In Oracle, the GROUP BY clause is used to group rows that have the same values in specified columns into summary rows.

True False

The GROUP BY clause groups rows that share the same values in the specified columns and allows aggregate functions to be applied to each group.

True / False

The GROUP BY clause must be used with an aggregate function such as SUM, COUNT, AVG, MIN, or MAX in Oracle.

True False

The GROUP BY clause is typically used with aggregate functions to perform calculations on each group of rows.

True / False

In Oracle, columns in the GROUP BY clause must also appear in the SELECT list unless they are used with an aggregate function.

True False

Columns in the GROUP BY clause must be included in the SELECT list unless they are arguments to aggregate functions.

True / False

In Oracle, the GROUP BY clause can be used with the ORDER BY clause to sort the grouped result set.

True False

The GROUP BY clause can be followed by the ORDER BY clause to sort the grouped result set.

True / False

In Oracle, you can use column aliases defined in the SELECT clause in the GROUP BY clause.

True False

Column aliases defined in the SELECT clause cannot be used in the GROUP BY clause. The original column names must be used.

True / False

The GROUP BY clause in Oracle can group by expressions, including calculated columns and functions.

True False

The GROUP BY clause can include expressions, such as calculated columns and functions, to group rows.

True / False

In Oracle, the GROUP BY clause can be used in combination with the HAVING clause to filter groups based on aggregate values.

True False

The HAVING clause is used in conjunction with the GROUP BY clause to filter groups based on aggregate values.

True / False

In Oracle, the GROUP BY clause can be used to group by multiple columns, resulting in a multi-level grouping.

True False

The GROUP BY clause can include multiple columns to create multi-level groupings.

True / False

The GROUP BY clause in Oracle can be used with the ROLLUP and CUBE extensions to generate subtotals and grand totals.

True False

The ROLLUP and CUBE extensions can be used with the GROUP BY clause to generate subtotals and grand totals for different levels of grouping.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes knowledge on using the GROUP BY clause effectively, optimizing its performance, and understanding its interaction with aggregate functions and extensions like ROLLUP and CUBE.

True False

The OCP certification covers advanced topics, including the effective use of the GROUP BY clause, optimizing its performance, and understanding its interaction with aggregate functions and extensions like ROLLUP and CUBE.

Q11

Single Choice

What is the primary purpose of the GROUP BY clause in Oracle SQL?

To filter rows To aggregate data based on one or more columns To sort rows in ascending orderTo join tables

The GROUP BY clause in Oracle SQL is used to group rows that have the same values in specified columns, and it allows aggregate functions to be applied to each group.

Q12

Single Choice

Which of the following Oracle SQL queries correctly uses the GROUP BY clause?

SQL Code

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

Groups employees by their department and counts the number of employees in each department Groups employees by their department and counts the total number of employees Groups employees by their salary and counts the number of employeesCounts the number of employees without grouping

The query groups employees by department_id and uses the COUNT function to count the number of employees in each department.

Q13

Single Choice

Which clause is mandatory when using aggregate functions with the GROUP BY clause in Oracle SQL?

HAVING ORDER BY SELECTNone of the above

The SELECT clause is mandatory when using GROUP BY because it specifies the columns that are grouped and any aggregate functions applied to those groups.

Q14

Single Choice

What will the following Oracle SQL query output?

SQL Code

SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id;

The average salary for each job role The average salary of all employees The total salary for each job roleThe job role with the highest salary

The query groups employees by job_id and calculates the average salary for each job role using the AVG function.

Q15

Single Choice

Which Oracle SQL query would you use to find the maximum salary in each department?

SQL Code

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

Groups employees by department and returns the highest salary in each department Groups employees by salary and returns the highest salary in each department Groups departments by the highest salaryReturns the department with the highest overall salary

The query uses the GROUP BY clause to group employees by department_id and the MAX function to find the highest salary in each group.

Q16

Single Choice

What will the following Oracle SQL query return?

SQL Code

SELECT department_id, COUNT(employee_id)
FROM employees
WHERE salary > 5000
GROUP BY department_id;

The number of employees earning more than 5000 in each department The total number of employees in each department The number of employees earning less than 5000 in each departmentAn error due to incorrect use of GROUP BY

The query filters employees earning more than 5000 and then groups them by department_id, counting the number of employees in each group.

Q17

Single Choice

In Oracle SQL, which of the following is correct when using GROUP BY with multiple columns?

SQL Code

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

Groups employees by both department and job role and sums their salaries Groups employees by department only and sums their salaries Groups employees by job role only and sums their salariesSums the salaries without grouping

The query groups employees by both department_id and job_id and calculates the sum of salaries for each unique combination of department and job role.

Q18

Single Choice

Which Oracle SQL query would you use to list departments with more than 10 employees?

SQL Code

SELECT department_id, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) > 10;

Lists departments with more than 10 employees Lists all departments with the number of employees Lists departments with fewer than 10 employeesLists departments without any employees

The query uses the HAVING clause to filter groups created by GROUP BY and returns only those departments with more than 10 employees.

Q19

Single Choice

What will the following Oracle SQL query return?

SQL Code

SELECT job_id, SUM(salary) AS total_salary
FROM employees
WHERE department_id IN (10, 20)
GROUP BY job_id
HAVING SUM(salary) > 50000;

Job roles in departments 10 and 20 where the total salary exceeds 50,000 Job roles in department 10 where the total salary exceeds 50,000 Job roles with a total salary less than 50,000 in departments 10 and 20All job roles regardless of salary in departments 10 and 20

The query filters employees in departments 10 and 20, groups them by job_id, and then uses the HAVING clause to return only those job roles where the total salary exceeds 50,000.

Q20

Single Choice

Which of the following Oracle SQL queries demonstrates an advanced use of GROUP BY with a complex expression?

SQL Code

SELECT TO_CHAR(hire_date, 'YYYY') AS hire_year, department_id, COUNT(employee_id) AS hires_per_year
FROM employees
GROUP BY TO_CHAR(hire_date, 'YYYY'), department_id
ORDER BY hire_year DESC;

Groups employees by hire year and department, listing the number of hires per year per department Groups employees by hire year only and lists the number of hires Groups employees by department only and lists the number of hires per departmentReturns an error due to incorrect use of GROUP BY

The query uses a complex expression in the GROUP BY clause, grouping employees by the year they were hired and their department, and then lists the number of hires per year per department.

Q21

Multiple Choice

Which SQL code snippet correctly uses the GROUP BY clause to group employees by their department in Oracle?

SQL Code

SELECT department_id, COUNT(*) AS num_employees
FROM employees
GROUP BY department_id;

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id;

SELECT department_id, COUNT(*) AS num_employees FROM employees GROUP BY department_id; SELECT department_id, COUNT(*) FROM employees GROUP BY department_id; SELECT COUNT(*), department_id FROM employees GROUP BY department_id;SELECT department_id, salary FROM employees GROUP BY salary;

Options A, B, and C correctly use the GROUP BY clause to group employees by their department. Option D is incorrect because it uses a different grouping.

Q22

Multiple Choice

Which SQL code snippet demonstrates how to use the GROUP BY clause with a HAVING clause in Oracle?

SQL Code

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;

SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id HAVING AVG(salary) > 50000; SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 50000; SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 10;SELECT department_id, salary FROM employees GROUP BY department_id HAVING COUNT(*) > 50000;

Options A, B, and C correctly demonstrate how to use the GROUP BY clause with a HAVING clause. Option D uses an incorrect HAVING condition.

Q23

Multiple Choice

Which SQL code snippet uses the GROUP BY clause to group products by category and calculate the total quantity in stock in Oracle?

SQL Code

SELECT category_id, SUM(quantity_in_stock) AS total_quantity
FROM products
GROUP BY category_id;

SELECT category_id, SUM(quantity_in_stock)
FROM products
GROUP BY category_id;

SELECT category_id, SUM(quantity_in_stock) total_quantity
FROM products
GROUP BY category_id;

SELECT category_id, SUM(quantity_in_stock) AS total_quantity FROM products GROUP BY category_id; SELECT category_id, SUM(quantity_in_stock) FROM products GROUP BY category_id; SELECT category_id, SUM(quantity_in_stock) total_quantity FROM products GROUP BY category_id;SELECT category_id, quantity_in_stock FROM products GROUP BY category_id;

Options A, B, and C correctly use the GROUP BY clause to group products by category and calculate the total quantity in stock. Option D is incorrect because it lacks the SUM function.

Q24

Multiple Choice

Which SQL code snippet demonstrates how to use the GROUP BY clause to find the maximum salary in each department in Oracle?

SQL Code

SELECT department_id, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id;

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

SELECT department_id, MAX(salary) max_salary
FROM employees
GROUP BY department_id;

SELECT department_id, MAX(salary) AS max_salary FROM employees GROUP BY department_id; SELECT department_id, MAX(salary) FROM employees GROUP BY department_id; SELECT department_id, MAX(salary) max_salary FROM employees GROUP BY department_id;SELECT MAX(salary) FROM employees;

Options A, B, and C correctly use the GROUP BY clause to find the maximum salary in each department. Option D is incorrect because it does not group by department_id.

Q25

Multiple Choice

Which SQL code snippet uses the GROUP BY clause to count the number of employees in each job title in Oracle?

SQL Code

SELECT job_title, COUNT(*) AS num_employees
FROM employees
GROUP BY job_title;

SELECT job_title, COUNT(*)
FROM employees
GROUP BY job_title;

SELECT COUNT(*), job_title
FROM employees
GROUP BY job_title;

SELECT job_title, COUNT(*) AS num_employees FROM employees GROUP BY job_title; SELECT job_title, COUNT(*) FROM employees GROUP BY job_title; SELECT COUNT(*), job_title FROM employees GROUP BY job_title;SELECT job_title, salary FROM employees GROUP BY salary;

Options A, B, and C correctly use the GROUP BY clause to count the number of employees in each job title. Option D is incorrect because it uses a different grouping.

Q26

Multiple Choice

Which SQL code snippet demonstrates advanced use of the GROUP BY clause to calculate the average salary and group by department and job title in Oracle?

SQL Code

SELECT department_id, job_title, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id, job_title;

SELECT department_id, job_title, AVG(salary)
FROM employees
GROUP BY department_id, job_title;

SELECT department_id, job_title, AVG(salary) avg_salary
FROM employees
GROUP BY department_id, job_title;

SELECT department_id, job_title, AVG(salary) AS avg_salary FROM employees GROUP BY department_id, job_title; SELECT department_id, job_title, AVG(salary) FROM employees GROUP BY department_id, job_title; SELECT department_id, job_title, AVG(salary) avg_salary FROM employees GROUP BY department_id, job_title;SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

Options A, B, and C correctly use the GROUP BY clause to calculate the average salary and group by department and job title. Option D is incorrect because it does not group by job title.

Q27

Multiple Choice

Which SQL code snippet uses the GROUP BY clause to find the total sales amount for each product in Oracle?

SQL Code

SELECT product_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY product_id;

SELECT product_id, SUM(sales_amount)
FROM sales
GROUP BY product_id;

SELECT product_id, SUM(sales_amount) total_sales
FROM sales
GROUP BY product_id;

SELECT product_id, SUM(sales_amount) AS total_sales FROM sales GROUP BY product_id; SELECT product_id, SUM(sales_amount) FROM sales GROUP BY product_id; SELECT product_id, SUM(sales_amount) total_sales FROM sales GROUP BY product_id;SELECT product_id, sales_amount FROM sales GROUP BY product_id;

Options A, B, and C correctly use the GROUP BY clause to find the total sales amount for each product. Option D is incorrect because it does not include the SUM function.

Q28

Multiple Choice

Which SQL code snippet demonstrates advanced use of the GROUP BY clause with a JOIN to calculate the average order amount by customer in Oracle?

SQL Code

SELECT c.customer_id, AVG(o.order_amount) AS avg_order_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

SELECT customer_id, AVG(order_amount) AS avg_order_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

SELECT c.customer_id, AVG(order_amount) avg_order_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

SELECT c.customer_id, AVG(o.order_amount) AS avg_order_amount FROM customers c JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id; SELECT customer_id, AVG(order_amount) AS avg_order_amount FROM customers c JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id; SELECT c.customer_id, AVG(order_amount) avg_order_amount FROM customers c JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id;SELECT customer_id, AVG(order_amount) FROM customers JOIN orders ON customers.customer_id = orders.customer_id GROUP BY order_amount;

Options A, B, and C correctly use the GROUP BY clause with a JOIN to calculate the average order amount by customer. Option D is incorrect because it does not group by customer_id.

Q29

Multiple Choice

Which SQL code snippet demonstrates certification-level use of the GROUP BY clause to calculate the total revenue for each region in Oracle?

SQL Code

SELECT r.region_id, SUM(o.order_amount) AS total_revenue
FROM regions r
JOIN orders o ON r.region_id = o.region_id
GROUP BY r.region_id;

SELECT region_id, SUM(order_amount) AS total_revenue
FROM regions r
JOIN orders o ON r.region_id = o.region_id
GROUP BY r.region_id;

SELECT r.region_id, SUM(order_amount) total_revenue
FROM regions r
JOIN orders o ON r.region_id = o.region_id
GROUP BY r.region_id;

SELECT r.region_id, SUM(o.order_amount) AS total_revenue FROM regions r JOIN orders o ON r.region_id = o.region_id GROUP BY r.region_id; SELECT region_id, SUM(order_amount) AS total_revenue FROM regions r JOIN orders o ON r.region_id = o.region_id GROUP BY r.region_id; SELECT r.region_id, SUM(order_amount) total_revenue FROM regions r JOIN orders o ON r.region_id = o.region_id GROUP BY r.region_id;SELECT region_id, SUM(order_amount) FROM orders GROUP BY region_id;

Options A, B, and C correctly demonstrate certification-level use of the GROUP BY clause to calculate the total revenue for each region. Option D is incorrect because it does not include the JOIN.

Q30

Multiple Choice

Which SQL code snippet uses the GROUP BY clause with multiple columns to group sales by region and product category in Oracle?

SQL Code

SELECT r.region_name, p.category_name, SUM(s.sales_amount) AS total_sales
FROM sales s
JOIN regions r ON s.region_id = r.region_id
JOIN products p ON s.product_id = p.product_id
GROUP BY r.region_name, p.category_name;

SELECT region_name, category_name, SUM(sales_amount)
FROM sales s
JOIN regions r ON s.region_id = r.region_id
JOIN products p ON s.product_id = p.product_id
GROUP BY region_name, category_name;

SELECT r.region_name, p.category_name, SUM(sales_amount) total_sales
FROM sales s
JOIN regions r ON s.region_id = r.region_id
JOIN products p ON s.product_id = p.product_id
GROUP BY r.region_name, p.category_name;

SELECT r.region_name, p.category_name, SUM(s.sales_amount) AS total_sales FROM sales s JOIN regions r ON s.region_id = r.region_id JOIN products p ON s.product_id = p.product_id GROUP BY r.region_name, p.category_name; SELECT region_name, category_name, SUM(sales_amount) FROM sales s JOIN regions r ON s.region_id = r.region_id JOIN products p ON s.product_id = p.product_id GROUP BY region_name, category_name; SELECT r.region_name, p.category_name, SUM(sales_amount) total_sales FROM sales s JOIN regions r ON s.region_id = r.region_id JOIN products p ON s.product_id = p.product_id GROUP BY r.region_name, p.category_name;SELECT region_name, category_name, SUM(sales_amount) FROM sales GROUP BY region_name, category_name;

Options A, B, and C correctly use the GROUP BY clause with multiple columns to group sales by region and product category. Option D is incorrect because it does not include the JOINs.

Oracle Database Quiz Questions