Why subqueries are needed in SQL - Quick Recap

Recall & Review

beginner

What is a subquery in SQL?

A subquery is a query nested inside another query. It helps to get intermediate results that the main query can use.

beginner

Why do we use subqueries instead of writing one big query?

Subqueries break complex problems into smaller parts, making queries easier to write and understand.

intermediate

How do subqueries help in filtering data?

Subqueries can find specific values first, then the main query uses those values to filter results precisely.

intermediate

Can subqueries be used in SELECT, WHERE, and FROM clauses?

Yes, subqueries can appear in SELECT to calculate values, in WHERE to filter rows, and in FROM to act like temporary tables.

beginner

Give a real-life example where subqueries are useful.

Imagine finding employees who earn more than the average salary. A subquery calculates the average salary, then the main query finds employees above it.

What is the main reason to use a subquery in SQL?

A To break complex queries into smaller parts

B To speed up the database server hardware

C To avoid using any conditions in queries

D To store data permanently

Subqueries help by breaking complex queries into smaller, manageable parts.

Where can a subquery NOT be used?

A In the database schema definition

B In the FROM clause

C In the WHERE clause

D In the SELECT clause

Subqueries are used inside queries, not in schema definitions.

Which of these is a benefit of using subqueries?

A They make queries harder to read

B They replace indexes

C They delete data automatically

D They allow filtering based on calculated values

Subqueries allow filtering based on values calculated inside the subquery.

A subquery that returns multiple rows can be used with which operator?

A LIKE

B IN

C BETWEEN

D IS NULL

The IN operator works with subqueries returning multiple rows.

What does a subquery inside the FROM clause act like?

A A database user

B A permanent index

C A temporary table

D A stored procedure

Subqueries in FROM act like temporary tables for the main query.

Explain why subqueries are useful when writing SQL queries.

Think about how subqueries help manage complexity and filtering.

Describe a scenario where using a subquery is better than a single flat query.

Consider filtering based on a calculated value like average salary.