Last Updated On
Welcome to the SQL Quiz! This blog post features 25 multiple-choice questions that explore Subqueries concept of SQL.
1. What is a subquery in SQL?
a) A query that modifies data
b) A query within another SQL query
c) A query that creates tables
d) A query that deletes data
Answer 1
b) A query within another SQL query
A subquery is a query nested within another SQL query, often used to provide results for the outer query to act upon
2. Which of the following is true about sub-queries?
a) They execute after the main query executes
b) They execute in parallel to the main query
c) The user can execute the main query and then, if wanted, execute the sub-query
d) They execute before the main query executes.
Answer 2
d) They execute before the main query executes.
The sub-query always executes before the execution of the main query. Subqueries are completed first. The result of the subquery is used as input for the outer query.
3. Which of the following is a method for writing a sub-query in a main query?
a) By using JOINS
b) By using WHERE clause
c) By using the GROUP BY clause
d) By writing a SELECT statement embedded in the clause of another SELECT statement
Answer 3
d) By writing a SELECT statement embedded in the clause of another SELECT statement
A subquery is a complete query nested in the SELECT, FROM, HAVING, or WHERE
clause of another query. The subquery must be enclosed in parentheses and have a SELECT and aFROM clause, at a minimum
4. What will the following subquery do?
SELECT employee_id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
a) Select employees with below-average salary
b) Select employees with above-average salary
c) Throw an error
d) Select all employees
Answer 4
b) Select employees with above-average salary
It selects employees whose salary is greater than the average salary of all employees.
5. In the given scenarios, which one would appropriately justify the usage of sub-query?
a) When we need to sum up values
b) When we need to convert character values into date or number values
c) When we need to select rows from a table with a condition that depends on the data from the same or different table.
d) None of the above
Answer 5
c) When we need to select rows from a table with a condition that depends on the data from the same or different table.
6. Which of the following single-row operators can be used for writing a sub-query?
a) >=
b) <
c) =
d) All of the above
Answer 6
d) All of the above
Single-row operators include =, >, <, >=, <=, and <>
7. In which scenario is a subquery most appropriately used?
a) To sort data in descending order
b) To filter results based on aggregated data from another table
c) To duplicate data between tables
d) To drop tables from a database
Answer 7
b) To filter results based on aggregated data from another table
Subqueries are commonly used to filter or manipulate data based on the results of a secondary query, especially with aggregated data.
8. What would be the effect of this query?
SELECT * FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE name = 'Sales');
a) List all departments
b) List all employees in all departments
c) List all employees in the Sales department
d) List all employees not in Sales department
Answer 8
c) List all employees in the Sales department
The subquery finds the department_id for ‘Sales’, and the outer query selects employees matching that department.
9. Which of the following multi-row operators can be used with a sub-query?
a) IN
b) ANY
c) ALL
d) All of the above
Answer 9
d) All of the above
Multiple-row subqueries return more than one row of results.Operators that can beused with multiple-row subqueries include IN, ALL, ANY, and EXISTS.
10. Can a subquery return multiple columns and be used in a WHERE clause?
a) Yes, if using EXISTS
b) Yes, if using JOIN
c) No, must return a single column
d) Yes, always
Answer 10
c) No, must return a single column
In a WHERE clause, subqueries must return a single column to be a valid comparison or condition.
11. Which of the following is a correlated subquery?
Choose one option
a) SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
b) SELECT * FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);
c) SELECT * FROM employees WHERE salary IN (SELECT salary FROM employees);
d) SELECT * FROM employees WHERE id IN (SELECT id FROM departments);
Answer 11
b)
It refers to the outer query’s row, making it a correlated subquery that executes for each row processed by the outer query.
12. How many levels of nested subqueries does SQL typically allow?
Choose one option
a) One
b) Two
c) Unlimited
d) 255
Answer 12
d) 255
SQL standards typically allow up to 255 levels of nested subqueries, though this might vary by database implementation.
13. Where can subqueries NOT be used in SQL?
Choose one option
a) FROM clause
b) SELECT clause
c) JOIN clause
d) IN clause
Answer 13
c) JOIN clause
Subqueries are not included as part of JOIN conditions; they appear as tables need explicit relations in a join.
14. What will be the output of the below query?
SELECT * FROM employees
WHERE salary BETWEEN (SELECT max(salary) FROM employees WHERE department_id = 100) AND
(SELECT min(salary) FROM employees WHERE department_id = 100);
This query returns an error. What is the reason for the error?
a) A GROUP BY clause should be used as the function MAX is used
b) Both the sub-queries cannot use the same department ID in the same outer query
c) BETWEEN operator cannot be used with a sub-query
d) SELECT clause should have columns mentioned and not a asterix ∗
Answer 14
c) BETWEEN operator cannot be used with a sub-query
15. What kind of subquery is this?
SELECT department_id FROM departments WHERE department_id = (SELECT MAX(department_id) FROM departments);
Choose one option
a) Scalar
b) Correlated
c) Non-correlated
d) Nested
Answer 15
a) Scalar
This is a scalar subquery that returns a single value and is used to select the row matching the maximum department_id
16. Which of the following is a valid example of a subquery within an UPDATE statement?
a) UPDATE employees SET salary = salary * 1.1 WHERE department_id = (SELECT department_id FROM departments);
b) UPDATE employees SET salary = salary * 1.1 WHERE department_id = (SELECT department_id FROM departments WHERE name = 'HR');
c) UPDATE employees USING (SELECT department_id FROM departments WHERE name = 'HR');
d) UPDATE employees SET salary = 1.1 * (SELECT salary FROM employees);
Answer 16
b)
This query uses a subquery to update employee salaries pertaining to the HR department only.
17. What does the following SQL statement do?
DELETE FROM employees WHERE manager_id IN (SELECT employee_id FROM employees WHERE department_id = 3);
Choose one option
a) Deletes all employees
b) Deletes managers in department 3
c) Deletes employees managed by those in department 3
d) Deletes all managers
Answer 17
c) Deletes employees managed by those in department 3
This usage of a subquery identifies all employees who report directly to managers specified in department 3.
18. What will be the outcome of the following query?
SELECT first_name, last_name, salary FROM employees WHERE salary ANY
(SELECT salary FROM employees);
a) It executes successfully giving the desired results
b) It executes successfully but does not give the desired results
c) It throws an ORA error
d) It executes successfully and gives two values for each row obtained in the result set
Answer 18
c) It throws an ORA error
Multi-row operators cannot be used in single-row sub-queries and vice versa.
19. Which of the following is a valid use of a subquery in the FROM clause?
a) SELECT * FROM (SELECT column1 FROM table1) AS subquery;
b) SELECT * FROM table1 WHERE column1 = (SELECT column2 FROM table2);
c) SELECT * FROM table1 INNER JOIN (SELECT column2 FROM table2) AS subquery;
d) SELECT column1 FROM table1 WHERE column2 = (SELECT column2 FROM table2) AND column3 = (SELECT column3 FROM table3);
Answer 19
a)
A valid use of a subquery in the FROM clause is treating the subquery as a temporary table, allowing the outer query to select from it.
20. What will be the outcome of the following query?
SELECT first_name, last_name FROM employees WHERE emp_id NOT IN
(SELECT manager_id, hire_date FROM employees WHERE manager_id IS NOT NULL);
Choose one option
a) The NOT IN operator used is invalid
b) The WHERE clause in the sub-query is incorrectly written
c) The column in the sub-query SELECT clause should only be one when there’s an inequality used in the main query
d) The sub-query uses the same table as the main query
Answer 20
c) The column in the sub-query SELECT clause should only be one when there’s an inequality used in the main query
The columns selected in the sub-query should be same as on the other side of comparison operator. Any inequality of data type or number of columns would result in an ORA error.
21. When used in a SELECT statement, a subquery is also known as a:
a) Derived table
b) Join
c) Index
d) Constraint
Answer 21
a) Derived table
When a subquery is used in the FROM clause, it’s often called a derived or inline table.
22. Which SQL statement can include a subquery?
a) SELECT
b) INSERT
c) UPDATE
d) All of the above
Answer 22
d) All of the above
Subqueries can be used in SELECT, INSERT, UPDATE, or DELETE statements.
23. Which keyword can be used to handle subqueries that may return NULL?
a) IGNORE
b) IS NOT NULL
c) NVL or COALESCE
d) NULLIF
Answer 23
c) NVL or COALESCE
The NVL or COALESCE functions can replace NULLs with a specified value if returned by a subquery.
24. What is a correlated subquery?
a) A subquery that can be run independently
b) A subquery that references columns from the outer query
c) A subquery that updates multiple tables
d) A subquery that creates indices
Answer 24
b) A subquery that references columns from the outer query
Correlated subqueries use values from the outer query and run once for each row processed by the outer query.
25. What is the result of a subquery that returns NULL when it’s used with the IN operator?
a) Always includes NULL
b) Excludes all NULLs and matches
c) Returns no rows
d) Matches nothing, including NULL
Answer 25
d) Matches nothing, including NULL
A subquery with NULL makes IN operator skip or ignore such direct results, unless IS NULL is explicitly used.
We would love to hear from you! Please leave your comments and share your scores in the section below
QA Automation Expert 