Multiple Choice Questions & Answers – SQL

vibssingh

Test Your Knowledge: 25 SQL Subquery Questions

Last Updated On

HOME

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

vibssingh

SQL Multiple Choice Questions & Answers – JOINS

Last Updated On

HOME

Welcome to the SQL Quiz! This blog post features 25 multiple-choice questions that explore JOINS concept of SQL.

1. Which SQL JOIN returns only the rows that have matching values in both tables?

a) LEFT JOIN
b) RIGHT JOIN
c) INNER JOIN
d) FULL OUTER JOIN

Answer 1

c) INNER JOIN

INNER JOIN matches records in both tables based on the join condition.

2. What does the following SQL query return?

SELECT products.product_name, orders.order_date
FROM products
INNER JOIN orders ON products.product_id = orders.product_id;

a) Only products that have been ordered
b) All products, ordered and non-ordered
c) All orders, regardless of the products
d) None of the above

Answer 2

a) Only products that have been ordered

The INNER JOIN retrieves only products that have corresponding order records.

3. If you want all rows from the left table and only the matching rows from the right table, which join will you use?

a) INNER JOIN
b) RIGHT JOIN
c) LEFT JOIN
d) FULL JOIN

Answer 3

c) LEFT JOIN

LEFT JOIN keeps all rows from the left table and adds NULLs for non-matching right table rows.

4. Which join type would you use to include all students and their respective grades, whether or not the grades are recorded?

SELECT students.student_name, grades.grade
FROM students
__________ JOIN grades ON students.student_id = grades.student_id;

a) INNER JOIN
b) RIGHT JOIN
c) LEFT JOIN
d) FULL JOIN

Answer 4

c) LEFT JOIN

A LEFT JOIN includes all students and their grades, with NULL for students without recorded grades.

5. Which JOIN includes all records from both tables, filling NULLs where there are no matches?

a) INNER JOIN
b) CROSS JOIN
c) FULL OUTER JOIN
d) SELF JOIN

Answer 5

c) FULL OUTER JOIN

FULL OUTER JOIN returns all rows from both tables with NULLs where no match exists.

6. What result does the following query produce?

SELECT a.name, b.address
FROM customers a
FULL OUTER JOIN orders b ON a.customer_id = b.customer_id;

a) Only matching customers and orders
b) All customers and orders with matches and non-matches
c) Customers without orders
d) Orders without customers

Answer 6

b) All customers and orders with matches and non-matches

A FULL OUTER JOIN returns all records when there is a match in either table A or table B.

7. What does a CROSS JOIN do?

a) Matches only the common records
b) Returns Cartesian product of two tables
c) Returns only distinct records
d) Works only on primary keys

Answer 7

b) Returns Cartesian product of two tables

CROSS JOIN multiplies all rows from one table with all rows of another.

8. What does the following SQL query do?

SELECT e.employee_id, e.name, d.department_name
FROM employees e
CROSS JOIN departments d;

a) Joins each employee with all departments
b) Returns only matched employees and departments
c) Lists employees without departments
d) Lists departments without employees

Answer 8

a) Joins each employee with all departments

A CROSS JOIN creates a Cartesian product of both tables.

9. Which JOIN is used to join a table with itself?

a) CROSS JOIN
b) SELF JOIN
c) FULL OUTER JOIN
d) NATURAL JOIN

Answer 9

b) SELF JOIN

SELF JOIN is useful for hierarchical or recursive relationships.

10. What is a primary use case for a self join in SQL?

a) To join two unrelated databases
b) To compare rows within the same table
c) To join tables with similar schemas
d) To execute subqueries

Answer 10

b) To compare rows within the same table

SELECT a.employee_id, a.name, b.manager_id
FROM employees a
JOIN employees b ON a.manager_id = b.employee_id;

In the provided query, the employees table is joined with itself to find employees and their respective managers by matching employee IDs with manager IDs.

11. In a self join scenario, how would you distinguish between the different roles of the table being joined to itself?

Choose one option

a) Use different database instances
b) Use table aliases
c) Create a temporary table
d) Use a different SQL dialect

Answer 11

b) Use table aliases

SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;

In a self join, table aliases are employed to differentiate between the roles of the same table. Here, `e1` represents employees, and `e2` represents managers, allowing us to fetch the employee names along with their corresponding managers.

12.  To avoid a Cartesian product, always include a valid join condition in a WHERE clause.

Choose one option

a) True
b) False

Answer 12

a) True

If we don’t use WHERE clause in a join, it will do a cartesian product.

13. Which type of JOIN is used to select all records from the right table and the matched records from the left table?

Choose one option

a) OUTER JOIN
b) SELF JOIN
c) RIGHT JOIN
d) NATURAL JOIN

Answer 13

c) RIGHT JOIN

A RIGHT JOIN returns all records from the right table and the matched records from the left table. Unmatched left table records receive NULLs.

14. Given the tables employees and departments, which query will list all department names even if they have no employees?

Choose one option

a) LEFT JOIN
b) SELF JOIN
c) RIGHT JOIN
d) INNER JOIN

Answer 14

c) RIGHT JOIN

SELECT departments.department_name, employees.name
FROM departments
RIGHT JOIN employees ON departments.id = employees.department_id;

The RIGHT JOIN returns all department names, including those with no associated employees.

15. Can multiple JOIN operations be used in a single SQL query?

Choose one option

a) True
b) False

Answer 15

a) True

Multiple JOIN operations can be utilized in a single query to combine data from more than two tables.

16. In a SQL JOIN, what does the ON keyword do?

Choose one option

a) Specifies the columns for joining
b) Specifies the conditions for joining
c) Specifies the number of rows to return
d) Specifies the order of columns

Answer 16

b) Specifies the conditions for joining

The ON keyword specifies conditions that determine which rows are retrieved in the join operation.

17.  To combine data from multiple tables where relationships exist, which SQL feature should be used besides JOINs?

Choose one option

a) Subqueries
b) Cursors
c) Triggers
d) Indexes

Answer 17

a) Subqueries

Subqueries can be used to combine data or perform operations that retrieve data from multiple tables based on conditions.

18.  What is a NATURAL JOIN based on?

a) Explicit conditions
b) Common columns automatically determined
c) Primary keys
d) Foreign keys

Answer 18

b) Common columns automatically determined

A NATURAL JOIN automatically joins tables based on columns with matching names and compatible data types.

 19.  Below two tables (A & B) are given.

What will be the result of inner join between these tables?

a) 1,3,4
b) 1,2,3,4,5,6,7,8,9,10,12
c) 1,2,3,4,5,6,7
d) 1,3,4,8,9,10,12

Answer 19

a) 1,3,4

20. What will be the result of FULL OUTER JOIN between these tables?

Choose one option

a) 1,3,4
b) 1,2,3,4,5,6,7,8,9,10,12
c) 1,2,3,4,5,6,7
d) 1,3,4,8,9,10,12

Answer 20

b) 1,2,3,4,5,6,7,8,9,10,12

21. Select the correct query/queries for cross join:

a) Select * FROM Table1 T1 CROSS JOIN Table1 T2;
b) Select * FROM Table1 T1 ALL CROSS JOIN Table1 T2;
c) Select * FROM Table1 T1,Table1 T2;
d) Select * FROM Table1 T1 CROSS Table1 T2;
Answer 21

a) Select * FROM Table1 T1 CROSS JOIN Table1 T2;

22. Which clause can be used with a JOIN to filter records?

a) WHERE
b) HAVING
c) GROUP BY
d) ORDER BY

Answer 22

a) WHERE

The WHERE clause can be used to filter records after the JOIN operation, whereas HAVING is typically used with GROUP BY.

23. How can SQL JOINs be optimized for better performance?

a) Using indexes on join columns
b) Avoiding WHERE clauses
c) Using more complex joins
d) Increasing database storage

Answer 23

a) Using indexes on join columns

Optimizing SQL JOIN performance generally involves using indexes on the columns involved in the join conditions.

24. Consider the tables `students` and `enrollments`, both having the column `student_id`. What will the following SQL statement do?

SELECT * FROM students NATURAL JOIN enrollments;

a) Join using all columns that are common between the two tables
b) Require user to specify a join condition
c) Join using columns named `student_id`
d) Join with duplicate column names represented multiple times

Answer 24

c) Join using columns named `student_id`

The NATURAL JOIN will operate on all columns with the same name, joining on `student_id` as it’s the common column between `students` and `enrollments`.

25. What is a potential disadvantage of using NATURAL JOIN?

a) It is only available in a few SQL dialects
b) Can join tables that have no related columns
c) Lacks explicit control over join conditions
d) Always results in Cartesian product of the tables

Answer 25

c) Lacks explicit control over join conditions

NATURAL JOIN does not allow you to specify which common columns to use for joining, which can lead to unforeseen results if multiple columns share the same name across tables.

We would love to hear from you! Please leave your comments and share your scores in the section below