Test Your PostgreSQL Sub Queries Knowledge with This Quiz

Progress

True / False

A subquery in PostgreSQL can be used in the SELECT clause of an SQL statement.

True False

Subqueries can be used in various parts of an SQL statement, including the SELECT clause, to compute a value for each row selected.

True / False

Subqueries in PostgreSQL can only return a single value.

True False

Subqueries can return a single value (scalar subqueries), a single row, or multiple rows and columns.

True / False

In PostgreSQL, a subquery can be used within a WHERE clause to filter records.

True False

Subqueries are often used in the WHERE clause to filter records based on the results of the subquery.

True / False

In PostgreSQL, you can use a subquery in the FROM clause to create a temporary table for the main query.

True False

Subqueries in the FROM clause are known as derived tables and can be used to provide a result set that the main query can use as a temporary table.

True / False

PostgreSQL does not support correlated subqueries.

True False

PostgreSQL supports correlated subqueries, which are subqueries that reference columns from the outer query.

True / False

A subquery in PostgreSQL cannot use the ORDER BY clause.

True False

Subqueries can use the ORDER BY clause when used with LIMIT, OFFSET, or in certain contexts, but not all usages of ORDER BY in subqueries are meaningful or supported.

True / False

In PostgreSQL, a subquery can be used with the EXISTS operator to check for the existence of rows.

True False

The EXISTS operator in PostgreSQL is used with a subquery to test for the existence of rows that meet the specified criteria.

True / False

Subqueries in PostgreSQL can be nested up to an unlimited number of levels.

True False

While PostgreSQL supports nested subqueries, the practical limit depends on factors like memory and processing power, and overly complex nesting can lead to performance issues.

True / False

In PostgreSQL, a subquery used in the SELECT clause must always return a single row.

True False

A subquery in the SELECT clause must return a single row with a single column, otherwise, it will result in an error.

Q10

True / False

In PostgreSQL, using subqueries can sometimes be optimized by the query planner to improve performance.

True False

The PostgreSQL query planner can optimize certain subqueries to improve performance, such as transforming subqueries into joins or other operations when beneficial.

Q11

Single Choice

What is a subquery in PostgreSQL?

A query inside another query A separate standalone query A type of joinA function within a query

A subquery is a query embedded inside another query to retrieve data based on the result of the outer query.

Q12

Single Choice

Which of the following is a correct syntax for a subquery in PostgreSQL?

SQL Code

SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

Correct syntax Incorrect syntax Invalid table namesMissing join condition

This is a correct subquery, where the inner query calculates the average salary, and the outer query selects employees whose salary is higher than the average.

Q13

Single Choice

Can subqueries in PostgreSQL return more than one column?

Yes No Only in WHERE clauseOnly in SELECT clause

Subqueries can return more than one column when used in the SELECT or FROM clauses.

Q14

Single Choice

In PostgreSQL, which subquery type is used when the subquery is correlated with the outer query?

Correlated subquery Scalar subquery Inline subqueryNested subquery

A correlated subquery depends on values from the outer query for its execution and is executed once for every row processed by the outer query.

Q15

Single Choice

What is the result of the following PostgreSQL query?

SQL Code

SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name = 'HR');

Names of employees in the HR department All department names Employees with NULL department_idAn error due to a missing join

The subquery finds the department ID for 'HR', and the outer query retrieves the names of employees in that department.

Q16

Single Choice

Which clause is typically used with a subquery in PostgreSQL to filter rows based on the results of the subquery?

WHERE FROM GROUP BYORDER BY

The WHERE clause is commonly used with subqueries to filter data based on conditions returned by the subquery.

Q17

Single Choice

What will the following PostgreSQL query return?

SQL Code

SELECT name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);

The name of the employee with the highest salary All employees The sum of all salariesAn error in the subquery

The subquery finds the highest salary, and the outer query returns the name of the employee with that salary.

Q18

Single Choice

Which of the following is true about using a subquery in the FROM clause in PostgreSQL?

It is treated as a derived table It cannot be used with JOINs It must return a single valueIt must always have a GROUP BY clause

A subquery in the FROM clause is treated as a derived table or a temporary table for use in the main query.

Q19

Single Choice

What will the following PostgreSQL query return?

SQL Code

SELECT department, (SELECT COUNT(*) FROM employees e WHERE e.department_id = d.id) AS employee_count
FROM departments d;

Departments with the count of employees in each List of all employees Only departments with more than 10 employeesAn error due to subquery misplacement

The subquery counts employees per department, and the outer query returns each department with its employee count.

Q20

Single Choice

In PostgreSQL, what is the purpose of a scalar subquery, and how is it typically used in a SELECT clause?

SQL Code

SELECT name, (SELECT AVG(salary) FROM employees) AS avg_salary FROM employees;

To calculate a single value and apply it to each row To return multiple rows for aggregation To perform joins within a subqueryTo check for NULL values

Scalar subqueries return a single value that can be used in the outer query for every row processed, as seen in the calculation of the average salary for each employee.

Q21

Multiple Choice

Which SQL queries correctly utilize a subquery to find the highest salary from the employees table?

SELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM employees); SELECT * FROM employees WHERE salary > ALL (SELECT salary FROM employees); SELECT * FROM employees WHERE salary = MAX(salary);SELECT * FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);

A subquery is a query within another SQL query and is often used to retrieve data that will be used in the main query as a condition.

Q22

Multiple Choice

Which SQL queries correctly use subqueries to filter results in the orders table?

SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE total_spent > 1000); SELECT * FROM orders WHERE customer_id = (SELECT customer_id FROM customers WHERE total_spent > 1000); SELECT * FROM orders WHERE total_spent > 1000;SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM orders WHERE total_spent > 1000);

Subqueries can be used to filter results based on aggregate calculations or conditions that depend on another set of results.

Q23

Multiple Choice

Determine which SQL queries correctly apply subqueries to return employees who earn more than the department average salary.

SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id); SELECT * FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id); SELECT * FROM employees WHERE salary > (SELECT salary FROM employees WHERE department_id = employees.department_id);SELECT * FROM employees WHERE department_id = (SELECT department_id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees));

A subquery in the WHERE clause can be used to compare each employee's salary against the average salary in their department.

Q24

Multiple Choice

Which SQL queries correctly use subqueries to identify products with sales above the average?

SELECT * FROM products WHERE sales > (SELECT AVG(sales) FROM products); SELECT * FROM products WHERE sales > ALL (SELECT AVG(sales) FROM products); SELECT * FROM products WHERE sales = (SELECT MAX(sales) FROM products);SELECT * FROM products WHERE sales > (SELECT MIN(sales) FROM products);

Subqueries are often used in the WHERE clause to filter records based on calculations, such as finding records that are above or below a calculated average.

Q25

Multiple Choice

Identify SQL queries that correctly utilize subqueries to return customers with more than one order.

SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) > 1); SELECT * FROM customers WHERE (SELECT COUNT(*) FROM orders WHERE customer_id = customers.customer_id) > 1; SELECT * FROM customers WHERE orders > 1;SELECT * FROM customers WHERE customer_id = (SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) > 1);

Subqueries in the WHERE clause can be used to filter data based on a count of related records, such as finding customers with multiple orders.

Q26

Multiple Choice

Which SQL queries correctly use subqueries to return the names of employees who work in departments with more than 10 employees?

SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 10); SELECT name FROM employees WHERE (SELECT COUNT(*) FROM employees WHERE department_id = employees.department_id) > 10; SELECT name FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE COUNT(*) > 10);SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE COUNT(*) > 10);

Subqueries can be used to filter data by first calculating the count of related records, such as counting employees in a department, and then filtering based on this count.

Q27

Multiple Choice

Identify the SQL queries that correctly use subqueries to find departments where the average salary is above $50,000.

SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) > 50000; SELECT department_id FROM departments WHERE department_id IN (SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) > 50000); SELECT department_id FROM employees WHERE salary > 50000 GROUP BY department_id;SELECT department_id FROM employees WHERE AVG(salary) > 50000;

Subqueries in the HAVING clause can be used to filter groups based on calculated aggregates, such as finding departments with an average salary above a certain threshold.

Q28

Multiple Choice

Which SQL queries correctly use subqueries to return products that are more expensive than the average price in their category?

SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products WHERE category_id = products.category_id); SELECT * FROM products WHERE price = (SELECT AVG(price) FROM products WHERE category_id = products.category_id); SELECT * FROM products WHERE price > ALL (SELECT price FROM products WHERE category_id = products.category_id);SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);

Subqueries can be used to compare each product's price to the average price in its category by calculating the average within the subquery.

Q29

Multiple Choice

Determine the SQL queries that correctly use subqueries to find employees who earn more than any other employee in their department.

SELECT * FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = employees.department_id); SELECT * FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = employees.department_id); SELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM employees WHERE department_id = employees.department_id);SELECT * FROM employees WHERE department_id = (SELECT department_id FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = employees.department_id));

A subquery can be used with a comparison operator like > ANY to compare an employee's salary against all other salaries in their department.

Q30

Multiple Choice

Which SQL queries correctly use subqueries to return customer orders that exceed the average order amount?

SELECT * FROM orders WHERE amount > (SELECT AVG(amount) FROM orders); SELECT * FROM orders WHERE amount = (SELECT MAX(amount) FROM orders); SELECT * FROM orders WHERE amount > (SELECT AVG(amount) FROM customers WHERE customer_id = orders.customer_id);SELECT * FROM orders WHERE amount > ALL (SELECT amount FROM orders);

A subquery can be used to calculate the average order amount, and then this value can be used in the main query to filter orders that exceed this average.

PostgreSQL Database Quiz Questions