3.6

Subquery

A query within another SQL query, used in a SELECT statement's WHERE clause to return data to the outer query.

Correlated Subquery

A subquery is correlated when it references columns from the outer query; its rows depend on the outer query.

Alias

A temporary name assigned to a table or column, created using the AS keyword to help differentiate between columns in a query.

EXISTS operator

Returns TRUE if the subquery returns any rows; used to test for the presence of records.

NOT EXISTS operator

Returns TRUE if the subquery returns no rows, indicating that no record fulfills the condition.

Flatenning (Subqueries)

The process of replacing a subquery with a JOIN, modifying the SQL query structure for optimization.

INNER JOIN

A type of JOIN that returns rows when there is at least one match in both tables.

DISTINCT clause

Used to eliminate duplicate rows from the result set in SQL queries.

SELECT statement

A SQL command used to query data from a database, specifying the columns to be returned.

WHERE clause

A SQL clause used to filter records that fulfill specific conditions.

City table

A table that contains data on cities, typically including columns like Name, CountryCode, and Population.

Employee table

A table that encompasses details about employees, including their IDs, names, and salaries.

Family table

A table that stores familial relationships and names, often linked to an Employee table.

Correlated Subquery Example

An example where the outer query selects rows from one table while the inner subquery calculates values based on those selected rows.

Population

The total number of inhabitants in a given city, often used in queries to filter or analyze data.

Language Percentage

Statistical representation of the percentage of a given language spoken within a population in a city or country.