Test Your SQL Aggregate Function Knowledge: COUNT, SUM, AVG, MIN, MAX

Progress

True / False

The COUNT function in MySQL can only be used with numeric columns.

True False

The COUNT function in MySQL can be used with any column, not just numeric ones. It counts the number of rows returned by the query.

True / False

The SUM function in MySQL ignores NULL values in the column.

True False

The SUM function in MySQL calculates the total of a numeric column, ignoring any NULL values.

True / False

In MySQL, the AVG function always returns an integer value.

True False

The AVG function in MySQL returns the average value of a numeric column as a floating-point number, not necessarily an integer.

True / False

The MIN and MAX functions in MySQL can be used with date columns.

True False

Both MIN and MAX functions in MySQL can be used with date columns to find the earliest and latest dates, respectively.

True / False

MySQL, using the COUNT function with a specific column name will count only the non-NULL values of that column.

True False

When using COUNT(column_name), MySQL counts only the rows where the specified column is not NULL.

True / False

The SUM function in MySQL can be used to add up the values of a string column.

True False

The SUM function in MySQL is designed to work only with numeric columns, not string columns.

True / False

In MySQL, the AVG function will return NULL if all values in the column are NULL.

True False

If all values in the column are NULL, the AVG function in MySQL will return NULL as there are no numeric values to calculate an average.

True / False

MySQL’s COUNT function can be used with the DISTINCT keyword to count unique values.

True False

The COUNT function in MySQL can be combined with the DISTINCT keyword to count the number of unique values in a column.

True / False

The MIN and MAX functions in MySQL can be used in the same query to find the range of values in a numeric column.

True False

MIN and MAX functions can be used in the same query to determine the smallest and largest values in a numeric column, effectively giving the range.

Q10

True / False

In MySQL, the SUM and AVG functions can be used together in a single query to calculate both the total and average of a numeric column.

True False

The SUM and AVG functions can be used together in a single query in MySQL to compute both the total sum and the average of values in a numeric column.

Q11

Single Choice

Which of the following SQL clauses can be used to filter groups based on the result of the COUNT function?

SQL Code

SELECT department, COUNT(*)
FROM employees
GROUP BY department
___ COUNT(*) > 5;

WHERE HAVING LIMITORDER BY

The HAVING clause is used to filter groups based on aggregate functions like COUNT(). The WHERE clause cannot be used with aggregate functions.

Q12

Single Choice

In MySQL, how would you filter groups of products to show only those with an average price greater than 100?

SQL Code

SELECT category, AVG(price)
FROM products
GROUP BY category
HAVING AVG(price) > ___;

100 10 50500

The HAVING clause filters the grouped categories based on the average price. In this case, only categories with an average price greater than 100 will be shown.

Q13

Single Choice

Which MySQL function can be used with the HAVING clause to find the minimum salary in a group of departments?

SQL Code

SELECT department, MIN(salary)
FROM employees
GROUP BY department
HAVING MIN(salary) > 30000;

SUM MAX COUNTMIN

The MIN function is used to find the minimum value within a group, and it can be combined with the HAVING clause to filter results.

Q14

Single Choice

You want to find all departments where the total salary exceeds $500,000. Which SQL code correctly implements this using a HAVING clause?

SQL Code

SELECT department, SUM(salary)
FROM employees
GROUP BY department
HAVING SUM(salary) > ___;

100000 250000 500000750000

The SUM function calculates the total salary for each department, and the HAVING clause filters the results to show only departments with a total salary exceeding $500,000.

Q15

Single Choice

Which of the following SQL queries will correctly return categories with a maximum price greater than $200?

SQL Code

SELECT category, MAX(price)
FROM products
GROUP BY category
HAVING ___ > 200;

SUM(price) MAX(price) COUNT(price)AVG(price)

The MAX function is used to find the maximum value in each category, and the HAVING clause ensures only categories with a maximum price greater than $200 are returned.

Q16

Single Choice

What will the following SQL code return?

SQL Code

SELECT manager_id, AVG(salary)
FROM employees
GROUP BY manager_id
HAVING AVG(salary) < 80000;

Employees with a salary less than 80,000 Managers with an average employee salary less than 80,000 Departments with average salary greater than 80,000All employees

The query groups the employees by their manager and calculates the average salary per manager. The HAVING clause then filters out managers where the average salary is less than $80,000.

Q17

Single Choice

How do you filter out departments where the minimum employee salary is exactly $50,000 using the HAVING clause

SQL Code

SELECT department, MIN(salary)
FROM employees
GROUP BY department
HAVING MIN(salary) <> 50000;

HAVING MIN(salary) = 50000 HAVING MIN(salary) > 50000 HAVING MIN(salary) <> 50000HAVING COUNT(salary) = 50000

The HAVING clause is used here to exclude departments where the minimum salary is exactly $50,000 by using the <> (not equal) operator.

Q18

Single Choice

Which of the following queries shows departments with at least one employee having a salary greater than the average salary of that department?

SQL Code

SELECT department
FROM employees
GROUP BY department
HAVING MAX(salary) > AVG(salary);

HAVING COUNT(salary) > AVG(salary) HAVING MAX(salary) > AVG(salary) HAVING MIN(salary) > AVG(salary)HAVING SUM(salary) > AVG(salary)

The query compares the maximum salary in each department to the average salary and returns departments where the maximum salary exceeds the average.

Q19

Single Choice

Which SQL statement will find products in categories where the total sales amount exceeds the maximum single sale?

SQL Code

SELECT category
FROM sales
GROUP BY category
HAVING SUM(amount) > MAX(amount);

HAVING SUM(amount) > MAX(amount) HAVING AVG(amount) > MAX(amount) HAVING COUNT(amount) > MAX(amount)HAVING MIN(amount) > MAX(amount)

This query returns categories where the total sales amount (SUM(amount)) is greater than the largest single sale (MAX(amount)) within that category.

Q20

Single Choice

Which of the following statements is true regarding the HAVING clause when used with aggregate functions in MySQL?

The HAVING clause can be used without GROUP BY. The HAVING clause filters rows before grouping. The HAVING clause filters rows after the GROUP BY clause.The HAVING clause cannot be used with COUNT, SUM, AVG, MIN, or MAX.

The HAVING clause is used to filter groups after they have been formed by the GROUP BY clause, particularly when using aggregate functions like COUNT, SUM, AVG, MIN, and MAX.

Q21

Multiple Choice

Which query uses the COUNT function to find the total number of orders in the 'orders' table?

SQL Code

SELECT COUNT(order_id) AS total_orders
FROM orders;

SELECT COUNT(order_id) AS total_orders FROM orders; SELECT COUNT(*) AS total_orders FROM orders; SELECT COUNT(DISTINCT order_id) AS total_orders FROM orders;SELECT total_orders = COUNT(order_id) FROM orders;

The COUNT function is used to count the number of non-NULL values in the 'order_id' column, giving the total number of orders.

Q22

Multiple Choice

Identify the correct SQL query that uses the SUM function to calculate the total sales amount in the 'sales' table.

SQL Code

SELECT SUM(sales_amount) AS total_sales
FROM sales;

SELECT SUM(sales_amount) AS total_sales FROM sales; SELECT SUM(sales_amount) FROM sales; SELECT sales_amount = SUM(sales_amount) FROM sales;SELECT SUM(sales_amount) AS total_sales FROM sales WHERE sales_amount IS NOT NULL;

The SUM function adds up all the values in the 'sales_amount' column, giving the total sales amount.

Q23

Multiple Choice

Which query correctly uses the AVG function to find the average price of products in the 'products' table?

SQL Code

SELECT AVG(price) AS avg_price
FROM products;

SELECT AVG(price) AS avg_price FROM products; SELECT AVG(price) FROM products; SELECT AVG(price) AS avg_price FROM products WHERE price IS NOT NULL;SELECT price = AVG(price) FROM products;

The AVG function calculates the average value of the 'price' column, giving the average product price.

Q24

Multiple Choice

Determine the correct SQL query that uses the MIN function to find the minimum revenue in the 'customers' table.

SQL Code

SELECT MIN(revenue) AS min_revenue
FROM customers;

SELECT MIN(revenue) AS min_revenue FROM customers; SELECT MIN(revenue) FROM customers; SELECT MIN(revenue) AS min_revenue FROM customers WHERE revenue IS NOT NULL;SELECT revenue = MIN(revenue) FROM customers;

The MIN function returns the smallest value in the 'revenue' column, giving the minimum revenue.

Q25

Multiple Choice

Which SQL query uses the MAX function to find the highest transaction amount in the 'transactions' table?

SQL Code

SELECT MAX(transaction_amount) AS max_amount
FROM transactions;

SELECT MAX(transaction_amount) AS max_amount FROM transactions; SELECT MAX(transaction_amount) FROM transactions; SELECT MAX(transaction_amount) AS max_amount FROM transactions WHERE transaction_amount IS NOT NULL;SELECT transaction_amount = MAX(transaction_amount) FROM transactions;

The MAX function returns the largest value in the 'transaction_amount' column, giving the highest transaction amount.

Q26

Multiple Choice

Which query correctly uses the COUNT and AVG functions together to find the number of orders and the average order amount in the 'orders' table?

SQL Code

SELECT COUNT(order_id) AS total_orders, AVG(order_amount) AS avg_amount
FROM orders;

SELECT COUNT(order_id) AS total_orders, AVG(order_amount) AS avg_amount FROM orders; SELECT COUNT(order_id), AVG(order_amount) FROM orders; SELECT total_orders = COUNT(order_id), avg_amount = AVG(order_amount) FROM orders;SELECT COUNT(order_id) AS total_orders, AVG(order_amount) AS avg_amount FROM orders WHERE order_amount IS NOT NULL;

This query uses the COUNT function to count the number of orders and the AVG function to calculate the average order amount.

Q27

Multiple Choice

Determine the correct SQL query that uses the SUM and MIN functions to find the total sales and the minimum sales amount in the 'sales' table.

SQL Code

SELECT SUM(sales_amount) AS total_sales, MIN(sales_amount) AS min_sales
FROM sales;

SELECT SUM(sales_amount) AS total_sales, MIN(sales_amount) AS min_sales FROM sales; SELECT SUM(sales_amount), MIN(sales_amount) FROM sales; SELECT total_sales = SUM(sales_amount), min_sales = MIN(sales_amount) FROM sales;SELECT SUM(sales_amount) AS total_sales, MIN(sales_amount) AS min_sales FROM sales WHERE sales_amount IS NOT NULL;

This query uses the SUM function to calculate the total sales amount and the MIN function to find the smallest sales amount.

Q28

Multiple Choice

Which SQL query uses the AVG and MAX functions together to find the average and maximum price of products in the 'products' table?

SQL Code

SELECT AVG(price) AS avg_price, MAX(price) AS max_price
FROM products;

SELECT AVG(price) AS avg_price, MAX(price) AS max_price FROM products; SELECT AVG(price), MAX(price) FROM products; SELECT avg_price = AVG(price), max_price = MAX(price) FROM products;SELECT AVG(price) AS avg_price, MAX(price) AS max_price FROM products WHERE price IS NOT NULL;

The query uses the AVG function to calculate the average price and the MAX function to find the maximum price.

Q29

Multiple Choice

Identify the correct query that uses the COUNT and MIN functions to find the number of clients and the minimum revenue in the 'clients' table.

SQL Code

SELECT COUNT(client_id) AS total_clients, MIN(revenue) AS min_revenue
FROM clients;

SELECT COUNT(client_id) AS total_clients, MIN(revenue) AS min_revenue FROM clients; SELECT COUNT(client_id), MIN(revenue) FROM clients; SELECT total_clients = COUNT(client_id), min_revenue = MIN(revenue) FROM clients;SELECT COUNT(client_id) AS total_clients, MIN(revenue) AS min_revenue FROM clients WHERE revenue IS NOT NULL;

This query uses the COUNT function to count the number of clients and the MIN function to find the minimum revenue.

Q30

Multiple Choice

Which SQL query correctly uses the SUM, AVG, and MAX functions together to find the total, average, and maximum transaction amounts in the 'transactions' table?

SQL Code

SELECT SUM(transaction_amount) AS total_amount, AVG(transaction_amount) AS avg_amount, MAX(transaction_amount) AS max_amount
FROM transactions;

SELECT SUM(transaction_amount) AS total_amount, AVG(transaction_amount) AS avg_amount, MAX(transaction_amount) AS max_amount FROM transactions; SELECT SUM(transaction_amount), AVG(transaction_amount), MAX(transaction_amount) FROM transactions; SELECT total_amount = SUM(transaction_amount), avg_amount = AVG(transaction_amount), max_amount = MAX(transaction_amount) FROM transactions;SELECT SUM(transaction_amount) AS total_amount, AVG(transaction_amount) AS avg_amount, MAX(transaction_amount) AS max_amount FROM transactions WHERE transaction_amount IS NOT NULL;

This query combines the SUM, AVG, and MAX functions to calculate the total, average, and maximum transaction amounts.

MySQL Database Quiz Questions