Subquery in FROM clause (derived table) in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a subquery in the FROM clause, also known as a derived table?

A subquery in the FROM clause is a query nested inside the FROM part of a main query. It acts like a temporary table that the main query can use to get results.

beginner

Why do we use a subquery in the FROM clause?

We use it to simplify complex queries by breaking them into smaller parts. It helps to organize data before applying filters or joins in the main query.

beginner

How do you give a name to a derived table in SQL?

You give a derived table an alias right after the closing parenthesis of the subquery. This alias is used to refer to the derived table in the main query.

intermediate

Example: What does this SQL do? SELECT dt.category, dt.total_sales FROM (SELECT category, SUM(sales) AS total_sales FROM products GROUP BY category) AS dt WHERE dt.total_sales > 1000;

This query first calculates total sales per category inside the subquery (derived table). Then, the main query selects categories where total sales are more than 1000.

intermediate

Can a derived table be used with JOINs in the main query?

Yes, a derived table can be joined with other tables or derived tables in the main query to combine and filter data effectively.

What is the purpose of a subquery in the FROM clause?

A To update data in the database

B To create a temporary table for the main query to use

C To filter rows after the main query runs

D To delete rows from a table

A subquery in the FROM clause creates a temporary table (derived table) that the main query can use.

How do you refer to a derived table in the main query?

A By the column names

B By its original table name

C By the database name

D By an alias given after the subquery

You must give the derived table an alias and use that alias to refer to it in the main query.

Which SQL keyword is used to name a derived table?

A AS

B FROM

C WHERE

D GROUP BY

The AS keyword is used to assign an alias to the derived table.

Can a derived table contain aggregate functions like SUM or COUNT?

A Yes, it can perform aggregations

B No, aggregates are not allowed

C Only COUNT is allowed

D Only SUM is allowed

Derived tables can include aggregate functions to summarize data before the main query uses it.

Is this SQL valid? SELECT * FROM (SELECT id FROM users) WHERE id > 10;

A Yes, it is valid

B No, subqueries cannot be in FROM

C No, the derived table needs an alias

D No, WHERE cannot be used here

Derived tables must have an alias to be valid in SQL.

Explain what a derived table is and how it is used in SQL queries.

Think about how you can create a small table inside a query to help with filtering or joining.

Describe the steps to write a query using a subquery in the FROM clause with an example.

Start with a simple aggregation inside the subquery, then filter results in the main query.