Test Your Knowledge of Oracle COUNT, SUM, AVG, MIN, MAX Functions

Progress

True / False

In Oracle, the COUNT function is used to count the number of rows in a query result.

True False

The COUNT function returns the number of rows in a query result, including rows with NULL values.

True / False

The SUM function in Oracle calculates the total sum of a numeric column.

True False

The SUM function calculates the total sum of the values in a numeric column.

True / False

In Oracle, the AVG function returns the average value of a numeric column.

True False

The AVG function calculates the average of the values in a numeric column.

True / False

The MIN function in Oracle returns the minimum value of a column, ignoring NULL values.

True False

The MIN function returns the smallest non-NULL value in a column.

True / False

In Oracle, the MAX function can be used with both numeric and non-numeric data types.

True False

The MAX function can be used to find the largest value in a column, and it works with both numeric and non-numeric data types such as strings and dates.

True / False

The COUNT function in Oracle can count distinct values in a column using COUNT(DISTINCT column_name)

True False

The COUNT function can be used with the DISTINCT keyword to count only the unique values in a column.

True / False

In Oracle, the SUM function can be used to calculate the total sum of a column with a mixture of numeric and non-numeric values.

True False

The SUM function can only be used on numeric columns. Non-numeric values will cause an error.

True / False

In Oracle, you can use the AVG function in a HAVING clause to filter groups based on their average values.

True False

The AVG function can be used in the HAVING clause to filter groups based on the average values of a column.

True / False

The MIN and MAX functions in Oracle can be used together in a single query to find the range of values in a column.

True False

The MIN and MAX functions can be used in the same query to find the smallest and largest values in a column, effectively determining the range.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes knowledge on using the COUNT, SUM, AVG, MIN, and MAX functions effectively, understanding their limitations, and optimizing their performance in SQL queries.

True False

The OCP certification covers advanced topics, including the effective use of the COUNT, SUM, AVG, MIN, and MAX functions, understanding their limitations, and optimizing their performance in SQL queries.

Q11

Single Choice

In ORACLE SQL, what does the COUNT function return when used on a column?

The sum of all values in the column The total number of rows in the column, including NULL values The total number of non-NULL values in the columnThe average value of the column

The COUNT function in Oracle SQL returns the number of non-NULL values in a column.

Q12

Single Choice

Which ORACLE SQL function would you use to find the largest value in a numeric column?

COUNT SUM MAXAVG

The MAX function is used to find the largest value in a numeric column in Oracle SQL.

Q13

Single Choice

What does the SUM function do in an ORACLE SQL query?

It counts the total number of rows It calculates the total sum of all non-NULL values in a numeric column It finds the smallest value in a columnIt calculates the average value of a column

The SUM function in Oracle SQL calculates the total sum of all non-NULL values in a numeric column.

Q14

Single Choice

Given the following ORACLE SQL query: sqlWhat does this query return?

SQL Code

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

The total number of departments The total number of employees The number of employees in each departmentThe department with the most employees

This query returns the number of employees in each department, as grouped by department_id.

Q15

Single Choice

Consider the following Oracle SQL query:What does this query return?

SQL Code

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

The average salary of all employees The average salary within each department The total salary of all employeesThe highest salary within each department

This query calculates the average salary within each department, grouped by department_id.

Q16

Single Choice

Which of the following ORACLE SQL queries will return the sum of salaries for all employees in each department where the sum exceeds 100,000?

SELECT department_id, SUM(salary) FROM employees GROUP BY department_id HAVING SUM(salary) > 100000; SELECT department_id, SUM(salary) FROM employees WHERE SUM(salary) > 100000 GROUP BY department_id; SELECT department_id, SUM(salary) FROM employees GROUP BY department_id WHERE SUM(salary) > 100000;SELECT department_id, SUM(salary) FROM employees HAVING SUM(salary) > 100000 GROUP BY department_id;

The HAVING clause is used after the GROUP BY clause to filter departments where the sum of salaries exceeds 100,000.

Q17

Single Choice

Given the following ORACLE SQL query:What does this query return?

SQL Code

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

The minimum salary in the company The maximum salary in the company The minimum and maximum salaries within each departmentThe total salaries in each department

This query returns the minimum and maximum salaries within each department, grouped by department_id.

Q18

Single Choice

Consider the following Oracle SQL query:What does this query return?

SQL Code

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

Departments with an average salary greater than 50,000 and their employee counts Departments with less than 50,000 average salary The total number of employees and average salary for each departmentDepartments with more than 50,000 employees

The query returns the departments with an average salary greater than 50,000 along with the count of employees in those departments.

Q19

Single Choice

Which of the following ORACLE SQL queries correctly finds the department with the highest average salary?

SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) = MAX(AVG(salary)); SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC FETCH FIRST 1 ROWS ONLY; SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) > MAX(salary);SELECT department_id FROM employees GROUP BY department_id HAVING MAX(salary);

This query orders the departments by average salary in descending order and fetches the first row, which corresponds to the department with the highest average salary

Q20

Single Choice

Given the following ORACLE SQL query:What does this query do?

SQL Code

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

Returns job IDs with a total salary greater than the maximum department salary Returns departments with the highest total salary Returns job IDs with the lowest salaryReturns departments with the same total salary as the highest department salary

The HAVING clause with the subquery compares the total salary of each job ID with the maximum total salary across departments, returning job IDs where the sum exceeds the maximum department salary.

Q21

Multiple Choice

Which SQL code snippet correctly calculates the total number of employees in each department using the COUNT function in Oracle?

SQL Code

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

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

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

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

Options A, B, and C correctly use the COUNT function to calculate the total number of employees in each department. Option D is incorrect because it does not group by department_id.

Q22

Multiple Choice

Which SQL code snippet demonstrates how to use the SUM function to calculate the total sales 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 SUM function to calculate the total sales for each product. Option D is incorrect because it does not group by product_id.

Q23

Multiple Choice

Which SQL code snippet uses the AVG function to calculate the average salary for each job title in Oracle?

SQL Code

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

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

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

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

Options A, B, and C correctly use the AVG function to calculate the average salary for each job title. Option D is incorrect because it does not group by job_title.

Q24

Multiple Choice

Which SQL code snippet demonstrates how to use the MIN function to find the minimum order amount for each customer in Oracle?

SQL Code

SELECT customer_id, MIN(order_amount) AS min_order
FROM orders
GROUP BY customer_id;

SELECT customer_id, MIN(order_amount)
FROM orders
GROUP BY customer_id;

SELECT customer_id, MIN(order_amount) min_order
FROM orders
GROUP BY customer_id;

SELECT customer_id, MIN(order_amount) AS min_order FROM orders GROUP BY customer_id; SELECT customer_id, MIN(order_amount) FROM orders GROUP BY customer_id; SELECT customer_id, MIN(order_amount) min_order FROM orders GROUP BY customer_id;SELECT customer_id, order_amount FROM orders GROUP BY customer_id;

Options A, B, and C correctly use the MIN function to find the minimum order amount for each customer. Option D is incorrect because it does not group by customer_id.

Q25

Multiple Choice

Which SQL code snippet uses the MAX function to determine the highest 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 department_id, salary FROM employees GROUP BY department_id;

Options A, B, and C correctly use the MAX function to determine the highest salary in each department. Option D is incorrect because it does not group by department_id.

Q26

Multiple Choice

Which SQL code snippet demonstrates advanced use of the COUNT and SUM functions to calculate the number of orders and total order amount per customer in Oracle?

SQL Code

SELECT customer_id, COUNT(order_id) AS num_orders, SUM(order_amount) AS total_spent
FROM orders
GROUP BY customer_id;

SELECT customer_id, COUNT(order_id), SUM(order_amount)
FROM orders
GROUP BY customer_id;

SELECT customer_id, COUNT(order_id) num_orders, SUM(order_amount) total_spent
FROM orders
GROUP BY customer_id;

SELECT customer_id, COUNT(order_id) AS num_orders, SUM(order_amount) AS total_spent FROM orders GROUP BY customer_id; SELECT customer_id, COUNT(order_id), SUM(order_amount) FROM orders GROUP BY customer_id; SELECT customer_id, COUNT(order_id) num_orders, SUM(order_amount) total_spent FROM orders GROUP BY customer_id;SELECT customer_id, order_amount FROM orders GROUP BY customer_id;

Options A, B, and C correctly demonstrate advanced use of the COUNT and SUM functions to calculate the number of orders and total order amount per customer. Option D is incorrect because it does not group by customer_id.

Q27

Multiple Choice

Which SQL code snippet demonstrates how to use the AVG and MIN functions together to find the average and minimum sales per region in Oracle?

SQL Code

SELECT region_id, AVG(sales_amount) AS avg_sales, MIN(sales_amount) AS min_sales
FROM sales
GROUP BY region_id;

SELECT region_id, AVG(sales_amount), MIN(sales_amount)
FROM sales
GROUP BY region_id;

SELECT region_id, AVG(sales_amount) avg_sales, MIN(sales_amount) min_sales
FROM sales
GROUP BY region_id;

SELECT region_id, AVG(sales_amount) AS avg_sales, MIN(sales_amount) AS min_sales FROM sales GROUP BY region_id; SELECT region_id, AVG(sales_amount), MIN(sales_amount) FROM sales GROUP BY region_id; SELECT region_id, AVG(sales_amount) avg_sales, MIN(sales_amount) min_sales FROM sales GROUP BY region_id;SELECT region_id, sales_amount FROM sales GROUP BY region_id;

Options A, B, and C correctly use the AVG and MIN functions together to find the average and minimum sales per region. Option D is incorrect because it does not group by region_id.

Q28

Multiple Choice

Which SQL code snippet demonstrates certification-level use of the COUNT and AVG functions to analyze employee salaries by department in Oracle?

SQL Code

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

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

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

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

Options A, B, and C correctly demonstrate certification-level use of the COUNT and AVG functions to analyze employee salaries by department. Option D is incorrect because it does not group by department_id.

Q29

Multiple Choice

Which SQL code snippet demonstrates how to use the SUM and MAX functions together to calculate the total and highest sales for each product in Oracle?

SQL Code

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

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

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

SELECT product_id, SUM(sales_amount) AS total_sales, MAX(sales_amount) AS max_sales FROM sales GROUP BY product_id; SELECT product_id, SUM(sales_amount), MAX(sales_amount) FROM sales GROUP BY product_id; SELECT product_id, SUM(sales_amount) total_sales, MAX(sales_amount) max_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 SUM and MAX functions together to calculate the total and highest sales for each product. Option D is incorrect because it does not group by product_id.

Q30

Multiple Choice

Which SQL code snippet demonstrates certification-level use of COUNT, SUM, and AVG functions to generate a summary report of orders by customer in Oracle?

SQL Code

SELECT customer_id, COUNT(order_id) AS num_orders, SUM(order_amount) AS total_spent,
 AVG(order_amount) AS avg_order
FROM orders
GROUP BY customer_id;

SELECT customer_id, COUNT(order_id), SUM(order_amount),
 AVG(order_amount)
FROM orders
GROUP BY customer_id;

SELECT customer_id, COUNT(order_id) num_orders, SUM(order_amount) total_spent,
 AVG(order_amount) avg_order
FROM orders
GROUP BY customer_id;

SELECT customer_id, COUNT(order_id) AS num_orders, SUM(order_amount) AS total_spent, AVG(order_amount) AS avg_order FROM orders GROUP BY customer_id; SELECT customer_id, COUNT(order_id), SUM(order_amount), AVG(order_amount) FROM orders GROUP BY customer_id; SELECT customer_id, COUNT(order_id) num_orders, SUM(order_amount) total_spent, AVG(order_amount) avg_order FROM orders GROUP BY customer_id;SELECT customer_id, order_amount FROM orders GROUP BY customer_id;

Options A, B, and C correctly demonstrate certification-level use of COUNT, SUM, and AVG functions to generate a summary report of orders by customer. Option D is incorrect because it does not group by customer_id.

Oracle Database Quiz Questions