Test Your PostgreSQL COUNT, SUM, AVG, MIN, MAX Knowledge with This Quiz

Progress

True / False

The COUNT function in PostgreSQL can be used to count both rows and specific column values.

True False

The COUNT function can count all rows when used as COUNT(*) or specific column values when used as COUNT(column_name).

True / False

In PostgreSQL, the SUM function can only be used with numeric data types.

True False

The SUM function is designed to add up numeric values and cannot be used with non-numeric data types.

True / False

The AVG function in PostgreSQL ignores NULL values in the column it operates on.

True False

The AVG function calculates the average by ignoring NULL values in the column.

True / False

In PostgreSQL, the MIN and MAX functions can be used with date and time data types.

True False

The MIN and MAX functions can be used with various data types, including numeric, string, and date/time.

True / False

Using the SUM function on an empty set of rows in PostgreSQL will return NULL.

True False

Using the SUM function on an empty set of rows will return 0, not NULL.

True / False

The COUNT function with a DISTINCT keyword in PostgreSQL counts all unique non-null values.

True False

The COUNT(DISTINCT column_name) function counts only unique non-null values in the specified column.

True / False

In PostgreSQL, the SUM and AVG functions can be used in combination with the GROUP BY clause to calculate sums and averages for groups of rows.

True False

The SUM and AVG functions can be used with the GROUP BY clause to calculate sums and averages for each group of rows.

True / False

In PostgreSQL, the COUNT function can be used with a conditional expression to count rows that meet specific criteria.

True False

You can use the COUNT function with a conditional expression like COUNT(CASE WHEN condition THEN 1 ELSE NULL END) to count rows meeting specific criteria.

True / False

When using the AVG function in PostgreSQL, if all values in the column are NULL, the result will be NULL.

True False

If all values in the column are NULL, the AVG function will return NULL because there are no non-null values to average.

Q10

True / False

In PostgreSQL, combining MIN and MAX functions with window functions allows for calculating minimum and maximum values over specific partitions of data.

True False

PostgreSQL supports window functions, and combining MIN and MAX with window functions allows for calculating minimum and maximum values over defined partitions of data, providing more granular control over data analysis.

Q11

Single Choice

What does the COUNT(*) function do in PostgreSQL?

Counts the number of distinct values in a column Counts the number of non-null values in a column Counts the total number of rows, including those with NULL valuesCounts the number of unique rows

The COUNT(*) function in PostgreSQL counts the total number of rows in a table, including rows with NULL values.

Q12

Single Choice

Which PostgreSQL function would you use to calculate the average value of a numeric column?

AVG() SUM() COUNT()MIN()

The AVG() function in PostgreSQL is used to calculate the average value of a numeric column.

Q13

Single Choice

Given a table employees with a column salary, which function will return the highest salary?

MIN(salary) MAX(salary) SUM(salary)COUNT(salary)

The MAX() function in PostgreSQL returns the highest value in a specified column.

Q14

Single Choice

What will the following SQL query return in PostgreSQL?

SQL Code

SELECT COUNT(DISTINCT department) FROM employees;

The total number of rows in the employees table The total number of distinct departments The number of departments with more than one employeeThe sum of all department IDs

The query returns the total number of distinct department values in the employees table.

Q15

Single Choice

How would you calculate the total of all sales amounts in a sales table in PostgreSQL?

SELECT AVG(amount) FROM sales; SELECT COUNT(amount) FROM sales; SELECT SUM(amount) FROM sales;SELECT MAX(amount) FROM sales;

The SUM() function in PostgreSQL calculates the total of all values in a specified column.

Q16

Single Choice

What does the following PostgreSQL query return?

SQL Code

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

The maximum salary in the HR department The average salary of employees in the HR department The total salary paid in the HR departmentThe number of employees in the HR department

The query returns the average salary of employees who work in the HR department.

Q17

Single Choice

What will the following SQL query return in PostgreSQL?

SQL Code

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

The total salary in the company The total salary for each department The highest salary in each departmentThe average salary in the company

The query returns the total salary for each department by grouping the results by department.

Q18

Single Choice

In PostgreSQL, which function can be used in conjunction with GROUP BY to find the department with the lowest average salary?

MAX() MIN() SUM()AVG()

The MIN() function can be used after calculating the average salary per department to find the department with the lowest average.

Q19

Single Choice

Given the following PostgreSQL query, what result will be returned?

SQL Code

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

Departments with more than 10 employees Total number of departments Departments with fewer than 10 employeesThe average number of employees in each department

The query returns the departments that have more than 10 employees.

Q20

Single Choice

Which PostgreSQL query correctly returns the average salary of employees, excluding the highest and lowest salary?

SELECT AVG(salary) FROM employees; SELECT AVG(salary) FROM employees WHERE salary NOT IN (SELECT MAX(salary), MIN(salary) FROM employees); SELECT (SUM(salary) - MAX(salary) - MIN(salary)) / (COUNT(salary) - 2) FROM employees;SELECT AVG(salary) FROM employees WHERE salary != (SELECT MAX(salary) FROM employees);

The query calculates the average salary after excluding the highest and lowest salary by subtracting them from the total sum and dividing by the count minus two.

Q21

Multiple Choice

Which SQL scripts correctly use the COUNT function to find the number of employees in each department in PostgreSQL?

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

The COUNT function is used to count the number of rows that match a specified condition. The correct scripts will demonstrate how to count employees in each department.

Q22

Multiple Choice

Identify the SQL scripts that correctly calculate the total salary in each department using the SUM function in PostgreSQL.

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

The SUM function adds up all the values in a specified column. The correct scripts will demonstrate how to calculate the total salary for each department.

Q23

Multiple Choice

Which SQL scripts correctly calculate the average salary in each department using the AVG function in PostgreSQL?

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id; SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 50000; SELECT department_id, AVG(salary) FROM employees WHERE salary > 0 GROUP BY department_id;SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) IS NOT NULL;

The AVG function calculates the average value of a numeric column. The correct scripts will demonstrate how to calculate the average salary for each department.

Q24

Multiple Choice

Select the SQL scripts that correctly find the highest salary in each department using the MAX function in PostgreSQL.

SELECT department_id, MAX(salary) FROM employees GROUP BY department_id; SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary) > 50000; SELECT department_id, MAX(salary) FROM employees WHERE salary > 0 GROUP BY department_id;SELECT department_id, MAX(salary) FROM employees GROUP BY department_id WHERE salary > 50000;

The MAX function returns the maximum value in a column. The correct scripts will demonstrate how to find the highest salary for each department.

Q25

Multiple Choice

Determine the SQL scripts that correctly find the lowest salary in each department using the MIN function in PostgreSQL.

SELECT department_id, MIN(salary) FROM employees GROUP BY department_id; SELECT department_id, MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary) < 30000; SELECT department_id, MIN(salary) FROM employees WHERE salary > 0 GROUP BY department_id;SELECT department_id, MIN(salary) FROM employees GROUP BY department_id WHERE salary < 30000;

The MIN function returns the minimum value in a column. The correct scripts will demonstrate how to find the lowest salary for each department.

Q26

Multiple Choice

Identify the SQL scripts that correctly combine multiple aggregate functions in a single query in PostgreSQL.

SELECT department_id, COUNT(*), SUM(salary), AVG(salary), MAX(salary), MIN(salary) FROM employees GROUP BY department_id; SELECT department_id, COUNT(employee_id), SUM(salary), AVG(salary), MAX(salary), MIN(salary) FROM employees GROUP BY department_id; SELECT department_id, COUNT(*), SUM(salary), AVG(salary), MAX(salary), MIN(salary) FROM employees GROUP BY department_id HAVING COUNT(*) > 1;SELECT department_id, COUNT(*), SUM(salary), AVG(salary), MAX(salary), MIN(salary) FROM employees GROUP BY department_id WHERE SUM(salary) > 100000;

Combining multiple aggregate functions in a single query is a common requirement. The correct scripts will demonstrate how to calculate different aggregate values for each department.

Q27

Multiple Choice

Which SQL scripts correctly use the AVG function with a WHERE clause in PostgreSQL?

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

The WHERE clause filters rows before the AVG function is applied. The correct scripts will demonstrate how to calculate the average salary for a subset of employees.

Q28

Multiple Choice

Determine the SQL scripts that correctly use the COUNT function with DISTINCT in PostgreSQL.

SELECT department_id, COUNT(DISTINCT job_id) FROM employees GROUP BY department_id; SELECT department_id, COUNT(job_id) FROM employees GROUP BY department_id HAVING COUNT(DISTINCT job_id) > 3; SELECT department_id, COUNT(DISTINCT job_id) FROM employees WHERE job_id IS NOT NULL GROUP BY department_id;SELECT department_id, COUNT(DISTINCT job_id) FROM employees GROUP BY department_id WHERE COUNT(DISTINCT job_id) > 3;

The DISTINCT keyword is used within the COUNT function to count unique values. The correct scripts will demonstrate how to count distinct job roles in each department.

Q29

Multiple Choice

Which SQL scripts correctly calculate the total and average salary for employees with a specific job role in PostgreSQL?

SELECT SUM(salary), AVG(salary) FROM employees WHERE job_id = 'Manager'; SELECT job_id, SUM(salary), AVG(salary) FROM employees WHERE job_id = 'Manager'; SELECT SUM(salary), AVG(salary) FROM employees WHERE job_id = 'Manager' GROUP BY job_id;SELECT job_id, SUM(salary), AVG(salary) FROM employees GROUP BY job_id WHERE job_id = 'Manager';

The SUM and AVG functions can be combined with a WHERE clause to calculate aggregate values for a specific subset of data. The correct scripts will demonstrate how to calculate total and average salaries for 'Manager' employees.

Q30

Multiple Choice

Identify the SQL scripts that correctly use multiple aggregate functions with conditional expressions in PostgreSQL.

SELECT department_id, AVG(salary), SUM(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 60000 AND SUM(salary) < 500000; SELECT department_id, AVG(salary), SUM(salary) FROM employees HAVING AVG(salary) > 60000 AND SUM(salary) < 500000 GROUP BY department_id; SELECT department_id, AVG(salary), SUM(salary) FROM employees GROUP BY department_id WHERE AVG(salary) > 60000 AND SUM(salary) < 500000;SELECT department_id, AVG(salary), SUM(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 60000 OR SUM(salary) < 500000;

Conditional expressions can be used within aggregate functions to filter data based on multiple conditions. The correct scripts will demonstrate how to apply these conditions to the total and average salaries.

PostgreSQL Database Quiz Questions