Week 4 Flashcards
(35 cards)
What is a subquery?
a query that is part of another query
What is a nested query?
a query that has an embedded subquery
What can a subquery be?
it can be a nested query itself!
What clauses can a subquery occur in?
SELECT, FROM, and WHERE (often appear here)
What is a good rule of thumb about nested queries?
AVOID THEM
What do we get when we use a subquery in a SELECT clause?
a single value
What do we get when we use a subquery in the FROM clause?
a relation
What do we get when we use a subquery in the WHERE or HAVING clause?
a single value to be compared with another value
Why are nested queries inefficient?
they run separately for each row which makes it slow for large datasets
What is subquery unnesting?
transforming a nested query into a single, flat query
How does unnesting work?
SQL rewrites the query to merge the subquery with the main query
What are quanitifiers?
they answer how many rows satisfy a condition
What is an existential quanitifier ask?
Does at least one row meet the condition?
What is an universal quantifier ask?
Do all rows meet the condition?
What are some key words that signify an existential quantifier?
“there exists”, “there is at least one”, or “for some”
What do we use for existential quanitifiers?
EXISTS, IN, NOT IN
What do join queries do?
essentially evaluate the presence of existential quantifiers
Existential quantifiers are ____________!
straightforward
Universal quantifiers pose a ___________.
challenger
What are some key words that signify universal quantifiers?
“given any”, “for all”, “for every”
What do we use for universal quantifiers?
=, ≠, <=, =>
What does it mean for a query to be monotone?
whenever we add tuples to one or more input tables, the answer to the query will not lose any output tuple
What is the monotone queries theorem?
if Q is a SELECT-FROM-WHERE query that does not have subqueries, and no aggregates, then it is monotone
If a query is monotonic, what does it imply?
that a nested query can be unnested and vice-versa
