COUNT, SUM, AVG, MIN, MAX in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this COUNT query?

Consider a table employees with columns id, department, and salary. The table has 5 rows with these departments: 'HR', 'IT', 'IT', 'HR', 'Finance'. What is the result of this query?

SELECT COUNT(*) FROM employees WHERE department = 'IT';

PostgreSQL

SELECT COUNT(*) FROM employees WHERE department = 'IT';

A 2

B 3

C 5

D 0

💡 Hint

COUNT(*) counts all rows that match the condition.

There are exactly 2 employees in the 'IT' department, so COUNT(*) returns 2.

❓ query_result

intermediate

What is the average salary for the HR department?

Given the employees table with salary values: 50000, 60000, 55000, 70000, 65000 for departments 'HR', 'IT', 'IT', 'HR', 'Finance' respectively, what does this query return?

SELECT AVG(salary) FROM employees WHERE department = 'HR';

PostgreSQL

SELECT AVG(salary) FROM employees WHERE department = 'HR';

A 65000

B 60000

C 55000

D 70000

💡 Hint

AVG calculates the average of the selected salaries.

The HR salaries are 50000 and 70000. Their average is (50000 + 70000) / 2 = 60000.

📝 Syntax

advanced

Which query correctly sums salaries for Finance department?

Choose the query that correctly sums the salaries of employees in the 'Finance' department.

A SELECT SUM(salary) FROM employees WHERE department = 'Finance';

B SELECT SUM salary FROM employees WHERE department = 'Finance';

C SELECT SUM(salary) FROM employees GROUP BY department = 'Finance';

D SELECT SUM(salary) WHERE department = 'Finance' FROM employees;

💡 Hint

SUM needs parentheses and WHERE clause after FROM.

Option A uses correct syntax: SUM(salary) with WHERE clause after FROM. Others have syntax errors.

❓ query_result

advanced

What is the minimum salary in the employees table?

Given the salaries 50000, 60000, 55000, 70000, 65000 in the employees table, what does this query return?

SELECT MIN(salary) FROM employees;

PostgreSQL

SELECT MIN(salary) FROM employees;

A 60000

B 55000

C 50000

D 65000

💡 Hint

MIN returns the smallest value in the column.

The smallest salary is 50000, so MIN(salary) returns 50000.

🧠 Conceptual

expert

Which aggregate function ignores NULL values in its calculation?

In SQL aggregation, which function does NOT count NULL values when computing its result?

A COUNT(*)

B AVG(column_name)

C SUM(column_name)

D COUNT(column_name)

💡 Hint

Think about how COUNT(*) differs from COUNT(column_name).

COUNT(column_name) counts only non-NULL values, ignoring NULLs. COUNT(*) counts all rows including NULLs. SUM and AVG also ignore NULLs in their calculations.