Test Your MySQL Subqueries Knowledge with This Quiz

Progress

True / False

A subquery in MySQL can be used in the WHERE clause to filter results.

True False

In MySQL, subqueries can be used in various clauses including WHERE, FROM, and SELECT. When used in the WHERE clause, they help in filtering results based on conditions that involve another query.

True / False

Subqueries in MySQL must always be placed in parentheses.

True False

Subqueries in MySQL need to be enclosed in parentheses to distinguish them from the main query and to ensure correct execution.

True / False

A subquery can return multiple columns in MySQL.

True False

A subquery used in the WHERE clause or as a scalar value must return a single column. If multiple columns are needed, the subquery should be used in the FROM clause or as a derived table.

True / False

A subquery in MySQL can be used in the SELECT clause to compute a value for each row.

True False

Subqueries can be used in the SELECT clause to compute a value for each row of the result set. For example, you might use a subquery to return an aggregate value that is displayed alongside the main query results.

True / False

Correlated subqueries in MySQL can reference columns from the outer query.

True False

Correlated subqueries in MySQL are dependent on columns from the outer query. They execute once for each row processed by the outer query and can reference outer query columns to filter results.

True / False

MySQL supports using subqueries in the HAVING clause.

True False

MySQL allows the use of subqueries in the HAVING clause. This is useful for filtering groups of results based on conditions that involve aggregate functions.

True / False

In MySQL, a subquery in the FROM clause is known as a derived table or a common table expression (CTE).

True False

In MySQL, subqueries in the FROM clause are known as derived tables. Common Table Expressions (CTEs) are supported in MySQL 8.0 and later, and are defined using the WITH clause.

True / False

You can use a subquery in MySQL to update multiple rows in the parent query.

True False

MySQL allows subqueries in the UPDATE statement, which can be used to update multiple rows based on the result of the subquery.

True / False

Subqueries in MySQL are executed only once, regardless of the number of times they are referenced.

True False

In MySQL, the execution behavior of subqueries depends on their type and context. For correlated subqueries, they are executed multiple times—once for each row processed by the outer query.

Q10

True / False

In MySQL, the EXISTS operator can be used in a subquery to test for the presence of rows that meet certain criteria.

True False

The EXISTS operator in MySQL is used in subqueries to check if the subquery returns any rows. It returns TRUE if the subquery contains any rows and FALSE otherwise.

Q11

Single Choice

Which of the following best describes a subquery in MySQL?

query that is used within another query query that returns data from a single table only query that performs data modificationquery that creates a new database

subquery is a query nested inside another query, often used to retrieve data to be used in the main query.

Q12

Single Choice

Which MySQL keyword is used to check if a subquery returns any rows?

EXISTS LIKE DISTINCTJOIN

The EXISTS keyword is used in MySQL to check if a subquery returns any rows. It returns TRUE if the subquery returns one or more rows.

Q13

Single Choice

What is the result of the following SQL query?

SQL Code

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

Names of employees earning less than the average salary Names of employees earning more than the average salary The average salary of all employeesNames of all employees

This query uses a subquery to find the average salary, then selects names of employees whose salary is greater than this average.

Q14

Single Choice

In MySQL, which of the following is true about correlated subqueries?

They are executed once for the entire main query They are executed for each row processed by the main query. They cannot use table aliases.They can only be used in SELECT statements.

A correlated subquery is a subquery that references a column from the outer query. It is evaluated once for each row processed by the outer query.

Q15

Single Choice

What will be the output of this SQL query?

SQL Code

SELECT department_id, (SELECT COUNT(*) FROM employees WHERE department_id = d.department_id) AS emp_count
FROM departments d;

The total number of employees in the company The number of employees in each department The department ID and the total number of departmentsThe average number of employees per department

This query uses a subquery to count the number of employees in each department, matching department_id in the employees table with the department_id from the departments table.

Q16

Single Choice

What does this query return?

SQL Code

Consider the following SQL query:
SELECT product_id, product_name
FROM products
WHERE price = (SELECT MAX(price) FROM products);

The ID and name of the product with the highest price The ID and name of all products The maximum price of all productsThe average price of all products

The subquery returns the maximum price, and the main query selects the product_id and product_name of the product that has this price.

Q17

Single Choice

Which of the following statements about using subqueries in MySQL is true?

Subqueries can only return a single column Subqueries can be used in the SELECT, WHERE, or HAVING clauses Subqueries must always be correlatedSubqueries can only be used with numeric data types

Subqueries can appear in different clauses of a query, such as SELECT, WHERE, or HAVING. They are not limited to returning a single column or working only with numeric data.

Q18

Single Choice

What does this query do?

SQL Code

Analyze the following query:
SELECT order_id, order_date
FROM orders
WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA')
AND order_date = (SELECT MAX(order_date) FROM orders WHERE customer_id = orders.customer_id);

Finds the most recent order date for all customers from the USA Lists orders made by all customers from the USA on the most recent order date Lists the last order date for each customerFinds the order date of the first order by USA customers

The subquery within the IN clause filters customers from the USA. The subquery in the WHERE clause checks for the most recent order date for each of these customers

Q19

Single Choice

What will be the output of the following query?

SQL Code

SELECT employee_id, salary
FROM employees e
WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = e.department_id AND job_title = 'Manager');

All employees who earn more than the highest-paid manager in their department All employees who earn more than the lowest-paid manager in their department All managers in each departmentAll employees who earn the most in their department

The subquery retrieves the salaries of all managers in the same department, and the ALL keyword ensures that only employees who earn more than the highest salary among those managers are selected.

Q20

Single Choice

What is the likely error or outcome?

SQL Code

Given the following SQL query:
SELECT employee_id, first_name, last_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees GROUP BY department_id);

The query will successfully run and return all employees with salaries above their department's average The query will throw an error because the subquery returns multiple rows The query will return employees who earn more than the company's average salaryThe query will return an empty result set

The subquery returns multiple rows due to the GROUP BY clause, leading to an error when trying to compare it with a single salary value in the outer query.

Q21

Multiple Choice

Which SQL query uses a subquery to find the names of employees who earn more than the average salary?

SQL Code

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

SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); SELECT name FROM employees WHERE salary < (SELECT AVG(salary) FROM employees); SELECT name FROM employees WHERE salary > (SELECT salary FROM employees WHERE salary = (SELECT AVG(salary) FROM employees));SELECT name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);

This query uses a subquery to calculate the average salary and then selects the names of employees whose salary exceeds this average.

Q22

Multiple Choice

Identify the SQL query that uses a subquery to find products that have been ordered more than 10 times.

SQL Code

SELECT product_name FROM products
WHERE product_id IN (SELECT product_id FROM orders GROUP BY product_id HAVING COUNT(*) > 10);

SELECT product_name FROM products WHERE product_id IN (SELECT product_id FROM orders GROUP BY product_id HAVING COUNT(*) > 10); SELECT product_name FROM products WHERE product_id = (SELECT product_id FROM orders WHERE COUNT(*) > 10); SELECT product_name FROM products WHERE product_id = (SELECT product_id FROM orders GROUP BY product_id HAVING COUNT(*) > 10);SELECT product_name FROM products WHERE product_id IN (SELECT product_id FROM orders GROUP BY product_id HAVING COUNT(*) < 10);

The subquery groups orders by product_id and filters those with a count greater than 10, which are then used in the main query to find the corresponding product names.

Q23

Multiple Choice

Which SQL query uses a subquery to list customers who have placed orders with a total value greater than $1000?

SQL Code

SELECT customer_name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders GROUP BY customer_id HAVING SUM(order_total) > 1000);

SELECT customer_name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders GROUP BY customer_id HAVING SUM(order_total) > 1000); SELECT customer_name FROM customers WHERE customer_id = (SELECT customer_id FROM orders GROUP BY customer_id HAVING SUM(order_total) > 1000); SELECT customer_name FROM customers WHERE customer_id = (SELECT customer_id FROM orders WHERE SUM(order_total) > 1000);SELECT customer_name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders GROUP BY customer_id HAVING SUM(order_total) < 1000);

The subquery sums the order totals for each customer and filters those with a total greater than $1000. The main query then retrieves the names of these customers.

Q24

Multiple Choice

Determine the SQL query that uses a subquery to find all departments with an average employee salary above $50,000.

SQL Code

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

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

The subquery calculates the average salary for each department and filters those with an average above $50,000. The main query retrieves the corresponding department names.

Q25

Multiple Choice

Which SQL query uses a subquery to find the employees who have the highest salary in their department?

SQL Code

SELECT name FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees e2 WHERE e2.department_id = employees.department_id);

SELECT name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees e2 WHERE e2.department_id = employees.department_id); SELECT name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees); SELECT name FROM employees WHERE salary IN (SELECT MAX(salary) FROM employees GROUP BY department_id);SELECT name FROM employees WHERE salary = (SELECT salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees));

The subquery finds the maximum salary in each department, and the main query selects the employees who earn that salary.

Q26

Multiple Choice

Which SQL query uses a subquery to list products that have never been ordered?

SQL Code

SELECT product_name FROM products
WHERE product_id NOT IN (SELECT DISTINCT product_id FROM orders);

SELECT product_name FROM products WHERE product_id NOT IN (SELECT DISTINCT product_id FROM orders); SELECT product_name FROM products WHERE product_id IN (SELECT product_id FROM orders WHERE product_id IS NULL); SELECT product_name FROM products WHERE product_id NOT IN (SELECT product_id FROM orders);SELECT product_name FROM products WHERE product_id IS NULL IN (SELECT DISTINCT product_id FROM orders);

The subquery selects all distinct product IDs from the orders table, and the main query filters out those products from the products table that are not in this list.

Q27

Multiple Choice

Identify the SQL query that uses a subquery to find all customers who have never placed an order.

SQL Code

SELECT customer_name FROM customers
WHERE customer_id NOT IN (SELECT DISTINCT customer_id FROM orders);

SELECT customer_name FROM customers WHERE customer_id NOT IN (SELECT DISTINCT customer_id FROM orders); SELECT customer_name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE customer_id IS NULL); SELECT customer_name FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders);SELECT customer_name FROM customers WHERE customer_id IS NULL IN (SELECT DISTINCT customer_id FROM orders);

The subquery selects all distinct customer IDs from the orders table, and the main query retrieves the names of customers who are not in this list.

Q28

Multiple Choice

Which SQL query uses a subquery to find departments where the total salary exceeds $500,000?

SQL Code

SELECT department_name FROM departments
WHERE department_id IN (SELECT department_id FROM employees GROUP BY department_id HAVING SUM(salary) > 500000);

SELECT department_name FROM departments WHERE department_id IN (SELECT department_id FROM employees GROUP BY department_id HAVING SUM(salary) > 500000); SELECT department_name FROM departments WHERE department_id = (SELECT department_id FROM employees GROUP BY department_id HAVING SUM(salary) > 500000); SELECT department_name FROM departments WHERE department_id = (SELECT department_id FROM employees WHERE SUM(salary) > 500000);SELECT department_name FROM departments WHERE department_id IN (SELECT department_id FROM employees GROUP BY department_id HAVING SUM(salary) < 500000);

The subquery groups employees by department and filters those departments with a total salary exceeding $500,000. The main query retrieves the corresponding department names.

Q29

Multiple Choice

Determine the SQL query that uses a subquery to find the average salary of employees in each department.

SQL Code

SELECT department_name, AVG(salary) FROM employees
WHERE department_id IN (SELECT department_id FROM departments);
GROUP BY department_name;

SELECT department_name, AVG(salary) FROM employees WHERE department_id IN (SELECT department_id FROM departments) GROUP BY department_name; SELECT department_name, AVG(salary) FROM employees WHERE department_id = (SELECT department_id FROM departments) GROUP BY department_name; SELECT department_name, AVG(salary) FROM employees GROUP BY department_name;SELECT department_name, AVG(salary) FROM employees WHERE department_id IN (SELECT department_id FROM departments);

This query groups employees by department, and a subquery retrieves the average salary for each department.

Q30

Multiple Choice

Which SQL query uses a subquery to list the names of employees who work in the same department as 'John Doe'?

SQL Code

SELECT name FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE name = 'John Doe');

SELECT name FROM employees WHERE department_id = (SELECT department_id FROM employees WHERE name = 'John Doe'); SELECT name FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE name = 'John Doe'); SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM employees WHERE name = 'John Doe');SELECT name FROM employees WHERE department_id = (SELECT department_id FROM employees WHERE name = 'Jane Doe');

The subquery finds the department_id of 'John Doe', and the main query selects the names of all employees who work in the same department.

MySQL Database Quiz Questions