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.
Fill in the blank: 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 only used $.
Subqueries can be used in various clauses, including WHERE.
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
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?
-
SELECT Name AS N FROM City AS Ccreates the aliasNfor the Name column and aliasCfor the City table. - The
ASkeyword is optional and may be omitted.
What is the importance of the alias in the subquery?
SELECT Name
FROM Employee E
WHERE EXISTS
(SELECT *
FROM Family
WHERE Id = E.Id
AND Relationship = 'Spouse');
It allows referencing the Id of the current employee without repeating Employee.Id.
What does the alias N refer to in the example?
SELECT Name AS N FROM Employee AS E
N refers to the Name column.
What does the alias C refer to in the example?
SELECT Name AS N FROM City AS C
C refers to the City table.
