3.6 Sub Queries Flashcards
What can be said about this subquery
The outer SELECT statement uses a subquery to determine which languages are used by a larger percentage of a country’s population than Aruba’s official language.
Which query executes first?
- The subquery
2executes first to find the official language Percentage for ABW, which is 5.3. - The outer query
1executes using the value 5.3 returned by the subquery2.
What would this subquery return?
What would this subquery return?
What does this query return?
Dutch, Balochi, Kongo
switching step 1 & 2 would be ideal.
Using the Exist operator
What does this query return?
Sam Snead
- Lisa Ellison and Maria Rodriguez both have a ‘Spouse’ in the Family table, but Sam Snead does not.
- NOT EXISTS returns TRUE when no rows are selected in the subquery, so only Sam Snead is returned.
Using the Exist operator
What does this query return?
Lisa Ellison and Maria Rodriguez
- The correlated subquery returns one row for Lisa Ellison and one row for Maria Rodriguez.
- No row exists in Family that has Sam Snead’s Id and relationship ‘Spouse’.
What is a subquery?
A subquery, also known as a nested query or inner query, is a query embedded within another SQL query
Typically used in a SELECT statement’s WHERE clause to provide data to the outer query, influencing the outcome of the selection. The subquery is enclosed in parentheses.
What is the purpose of a subquery in SQL?
To provide data to the outer query, influencing the outcome of the selection
Typically used in the WHERE clause of a SELECT statement.
A subquery is enclosed in _______.
(parentheses)
What does a correlated subquery do?
It references a column from the outer query within the subquery’s WHERE clause
Rows selected depend on the current row being processed in the outer query.
A correlated subquery selects rows based on _______.
the current row being processed in the outer query.
What does the EXISTS operator do in SQL?
Returns TRUE if the subquery fetches at least one row, FALSE otherwise
NOT EXISTS returns TRUE if no rows are selected, FALSE otherwise.
How can subqueries be transformed for performance optimization?
By flattening subqueries into joins
Most subqueries following IN or EXISTS can be flattened into joins, enhancing efficiency.
What is a key concept of flattening subqueries?
It can significantly improve query performance in data-intensive applications
Example: INNER JOIN City ON CountryCode WHERE Population > 1000000.
Subqueries are used $.
Subqueries can be used in various clauses, including WHERE.
What role do subqueries play in SQL queries?
They serve as powerful tools for organizing complex SQL queries
Understanding the use of subqueries and correlated subqueries enhances data extraction strategies.
This is an example of what type of query
Selecting languages with a higher usage percentage than Aruba’s official language
This is an example of a subquery
This is an example of what type of query
- Selecting cities with a population larger THAN
- the country’s average city population
This is an example of a correlated subquery
What is an Alias?
A temporary name given to a table or column for the duration of a query.
Why use Aliases? 3
- simplifies complex queries
- avoids ambiguity
- improves clarity
How do you create an Alias using the AS Keyword?
Use the AS keyword to define an alias
SELECT Name AS N FROM City AS C
Can you create an Alias without the AS Keyword?
- The
ASkeyword is optional and may be omitted.
In the query what does E represent?
SELECT Name
FROM Employee E WHERE NOT EXISTS
(SELECT * FROM Family
WHERE Id = E.Id AND Relationship = 'Spouse');
E is an alias for the Employee table.
