Test Your PostgreSQL HAVING Clause Knowledge with This Quiz

Progress

True / False

The HAVING clause in PostgreSQL is used to filter rows after the GROUP BY clause.

True False

The HAVING clause is used to filter groups of rows created by the GROUP BY clause.

True / False

In PostgreSQL, the HAVING clause can be used without a GROUP BY clause.

True False

The HAVING clause is specifically used to filter groups created by the GROUP BY clause, so it cannot be used without it.

True / False

The HAVING clause in PostgreSQL can include aggregate functions like SUM and COUNT.

True False

The HAVING clause can include aggregate functions to filter groups based on aggregate calculations.

True / False

In PostgreSQL, the HAVING clause is executed before the GROUP BY clause.

True False

The HAVING clause is executed after the GROUP BY clause.

True / False

You can use the HAVING clause in PostgreSQL to filter individual rows in a result set.

True False

The HAVING clause is used to filter groups, not individual rows. For filtering individual rows, the WHERE clause is used.

True / False

In PostgreSQL, it is possible to use a subquery within a HAVING clause.

True False

Subqueries can be used within a HAVING clause to filter groups based on the results of the subquery.

True / False

PostgreSQL allows the use of window functions directly in the HAVING clause.

True False

Window functions cannot be directly used in the HAVING clause. They are used in the SELECT clause or ORDER BY clause.

True / False

In PostgreSQL, the HAVING clause can reference columns that are not included in the GROUP BY clause or an aggregate function.

True False

Columns referenced in the HAVING clause must either be part of the GROUP BY clause or used in an aggregate function.

True / False

The HAVING clause can be used in conjunction with the ORDER BY clause in PostgreSQL.

True False

The HAVING clause can be used to filter groups, and the ORDER BY clause can then be used to sort the filtered results.

Q10

True / False

In PostgreSQL, you can use the HAVING clause to filter results based on a calculated column alias defined in the SELECT clause.

True False

In PostgreSQL, the HAVING clause cannot reference column aliases defined in the SELECT clause. The column must be recalculated or referenced directly.

Q11

Single Choice

What is the primary purpose of the HAVING clause in PostgreSQL?

To filter rows before the aggregation occurs To filter rows after the aggregation occurs To specify the order of rowsTo limit the number of rows returned

In PostgreSQL, the HAVING clause is used to filter the results of a GROUP BY query after the aggregation has been applied.

Q12

Single Choice

Which of the following SQL statements correctly uses the HAVING clause in PostgreSQL?

SQL Code

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

This query filters departments having more than 5 employees This query filters departments having exactly 5 employees This query filters departments having fewer than 5 employeesThis query filters employees who are part of more than 5 departments

The HAVING clause filters groups based on the condition specified after the aggregation function.

Q13

Single Choice

Can the HAVING clause be used without a GROUP BY clause in PostgreSQL?

Yes, it can be used in all queries No, it must be used with a GROUP BY clause Yes, but only in certain casesNo, HAVING is invalid without GROUP BY

While HAVING is typically used with GROUP BY, it can be used without it when applying aggregate functions to filter results.

Q14

Single Choice

Given the following SQL query, what does the HAVING clause do?

SQL Code

SELECT manager_id, SUM(salary)
FROM employees
GROUP BY manager_id
HAVING SUM(salary) > 50000;

Filters managers with total salaries exceeding $50,000 Filters managers with total salaries less than $50,000 Filters employees with salaries greater than $50,000Filters employees with salaries less than $50,000

The HAVING clause is applied after the aggregation, filtering out groups (in this case, managers) whose total salary is greater than $50,000.

Q15

Single Choice

In PostgreSQL, which clause is processed first in a query with both GROUP BY and HAVING?

GROUP BY HAVING WHEREORDER BY

The GROUP BY clause is processed before the HAVING clause. The HAVING clause filters the groups formed by GROUP BY.

Q16

Single Choice

Consider the following query

SQL Code

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;
What does this query return?

Departments with an average salary greater than $60,000 Employees earning more than $60,000 Departments with a total salary greater than $60,000Departments with more than 60 employees

The HAVING clause filters out departments where the average salary exceeds $60,000.

Q17

Single Choice

Which of the following queries correctly identifies departments where the average employee salary is above $75,000 and the total salary exceeds $500,000?

SQL Code

SELECT department, AVG(salary) AS avg_salary, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 75000 AND SUM(salary) > 500000;

This query is correct and filters departments meeting both conditions This query is incorrect because HAVING cannot handle multiple conditions This query is incorrect because AVG and SUM cannot be used together in HAVINGThis query is incorrect because the GROUP BY clause is missing

The HAVING clause can include multiple conditions and works with aggregate functions like AVG and SUM.

Q18

Single Choice

What would be the result of the following query in PostgreSQL?

SQL Code

SELECT job_title, COUNT(*)
FROM employees
GROUP BY job_title
HAVING COUNT(*) > 10 OR COUNT(*) < 3;

Job titles with either more than 10 or less than 3 employees Job titles with exactly 10 or 3 employees ) Job titles with more than 10 employeesJob titles with less than 3 employees

The HAVING clause filters groups based on multiple conditions using logical operators like OR.

Q19

Single Choice

How does the HAVING clause in PostgreSQL differ from the WHERE clause when both are used in a query with GROUP BY?

HAVING filters groups, WHERE filters rows before grouping WHERE filters groups, HAVING filters rows before grouping Both filter groups but in different ordersBoth filter rows but at different stages

The WHERE clause filters rows before they are grouped, whereas the HAVING clause filters groups after they have been formed.

Q20

Single Choice

In the context of PostgreSQL, consider a table sales with columns product_id, region, and revenue. Which of the following queries correctly retrieves regions where the average revenue per product exceeds $10,000 and the total revenue is more than $1,000,000?

SQL Code

SELECT region, AVG(revenue) AS avg_revenue, SUM(revenue) AS total_revenue
FROM sales
GROUP BY region
HAVING AVG(revenue) > 10000 AND SUM(revenue) > 1000000;

The query correctly retrieves the desired regions The query incorrectly filters the regions The query needs a WHERE clause for filteringThe query cannot use both AVG and SUM in the HAVING clause

The HAVING clause effectively filters regions based on both average and total revenue conditions after grouping by region.

Q21

Multiple Choice

Which SQL scripts correctly use the HAVING clause to filter groups in PostgreSQL?

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

The HAVING clause is used to filter groups based on aggregate values. The correct scripts will show how to filter departments where the total salary exceeds 200,000.

Q22

Multiple Choice

Identify the SQL scripts that correctly use the HAVING clause with multiple conditions in PostgreSQL.

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

The HAVING clause can be used with multiple conditions combined by logical operators like AND and OR. The correct scripts will show how to apply these conditions.

Q23

Multiple Choice

Determine the SQL scripts that correctly use the HAVING clause with aggregate functions in PostgreSQL.

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

The HAVING clause is typically used in conjunction with aggregate functions like SUM, AVG, COUNT, etc. The correct scripts will demonstrate how to filter groups based on the average salary.

Q24

Multiple Choice

Select the SQL scripts that correctly use HAVING with COUNT in PostgreSQL.

SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 5; SELECT department_id, COUNT(*) FROM employees HAVING COUNT(*) > 5 GROUP BY department_id; SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING SUM(salary) > 5;SELECT department_id, COUNT(*) FROM employees GROUP BY department_id WHERE COUNT(*) > 5;

The COUNT function is often used with HAVING to filter groups based on the number of rows in each group. The correct scripts will demonstrate how to filter departments with more than 5 employees.

Q25

Multiple Choice

Identify the SQL scripts that correctly use HAVING with DISTINCT in PostgreSQL.

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

The DISTINCT keyword can be used within aggregate functions in combination with HAVING to filter groups based on unique values. The correct scripts will demonstrate how to find departments with more than 3 distinct job roles.

Q26

Multiple Choice

Determine the SQL scripts that correctly use HAVING with complex expressions in PostgreSQL.

SELECT department_id, AVG(salary) * COUNT(*) AS total FROM employees GROUP BY department_id HAVING AVG(salary) * COUNT(*) > 500000; SELECT department_id, SUM(salary) * COUNT(*) AS total FROM employees GROUP BY department_id HAVING total > 500000; SELECT department_id, SUM(salary) * COUNT(*) AS total FROM employees HAVING total > 500000 GROUP BY department_id;SELECT department_id, AVG(salary) * COUNT(*) AS total FROM employees GROUP BY department_id HAVING SUM(salary) * COUNT(*) > 500000;

Complex expressions in HAVING can be used to filter groups based on more advanced calculations. The correct scripts will demonstrate how to apply a condition on the product of the average salary and the number of employees.

Q27

Multiple Choice

Identify the SQL scripts that correctly combine HAVING and WHERE clauses in PostgreSQL.

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

The WHERE clause filters rows before they are grouped, and the HAVING clause filters groups after they are formed. The correct scripts will demonstrate how to correctly apply both WHERE and HAVING.

Q28

Multiple Choice

Determine the SQL scripts that correctly use HAVING with subqueries in PostgreSQL.

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

Subqueries can be used within the HAVING clause to compare aggregate values to other calculated values. The correct scripts will demonstrate how to use a subquery to filter departments based on the total salary.

Q29

Multiple Choice

Select the SQL scripts that correctly use HAVING with conditional expressions in PostgreSQL.

SELECT department_id, MAX(salary), MIN(salary) FROM employees GROUP BY department_id HAVING MAX(salary) > 80000 OR MIN(salary) < 30000; SELECT department_id, MAX(salary), MIN(salary) FROM employees HAVING MAX(salary) > 80000 OR MIN(salary) < 30000 GROUP BY department_id; SELECT department_id, MAX(salary), MIN(salary) FROM employees GROUP BY department_id HAVING MAX(salary) > 80000 AND MIN(salary) < 30000;SELECT department_id, MAX(salary), MIN(salary) FROM employees GROUP BY department_id HAVING SUM(salary) > 80000 OR MIN(salary) < 30000;

Conditional expressions within the HAVING clause can be used to filter groups based on multiple conditions. The correct scripts will demonstrate how to apply these conditions to the maximum and minimum salaries.

Q30

Multiple Choice

Identify the SQL scripts that correctly use HAVING with CASE statements in PostgreSQL.

SELECT department_id, SUM(salary) FROM employees GROUP BY department_id HAVING SUM(salary) > 300000 AND COUNT(*) > 10; SELECT department_id, SUM(salary) FROM employees GROUP BY department_id HAVING SUM(salary) > 300000 AND CASE WHEN COUNT(*) > 10 THEN 1 ELSE 0 END = 1; SELECT department_id, SUM(salary) FROM employees HAVING SUM(salary) > 300000 AND COUNT(*) > 10 GROUP BY department_id;SELECT department_id, SUM(salary) FROM employees GROUP BY department_id HAVING CASE WHEN SUM(salary) > 300000 THEN COUNT(*) > 10 ELSE 0 END = 1;

CASE statements can be used within the HAVING clause to apply conditional logic to the groups. The correct scripts will demonstrate how to apply a CASE statement to filter departments based on multiple conditions.

PostgreSQL Database Quiz Questions