1. SQL Flashcards
tables, rows, columns - synonyms
relations, tuples, attributes
What is the default order of the rows in SQL?
(i.e. if there is no ORDER BY in SELECT)
The order of the rows is nondeterministic. So any order would be correct, and we can’t make any assumptions about it.
NULL values - a general meaning
NULL represents an “unknown” or “missing” value
How NULL values interact with Boolean operators
NULL short-circuits with boolean operators. That means a boolean expression involving NULL will evaluate to:
– TRUE, if it’d evaluate to TRUE regardless of whether the NULL value is really TRUE or FALSE.
– FALSE, if it’d evaluate to FALSE regardless of whether the NULL value is really TRUE or FALSE.
– Or NULL, if it depends on the NULL value.
Examples:
NULL or TRUE => TRUE
NULL and FALSE => FALSE
NULL or FALSE => NULL
NULL and TRUE => NULL
i.e. exactly matches the “unknown” meaning
How NULL acts in WHERE clauses?
WHERE NULL is just like WHERE FALSE. The row in question does not get included.
How NULL acts in expressions?
If you do anything with NULL, you’ll just get NULL. For instance if x is NULL, then x > 3, 1 = x, and x + 4 all evaluate to NULL. Even x = NULL would evaluate to NULL; if you want to check whether x is NULL, then write x IS NULL or x IS NOT NULL instead.
How NULL acts in aggregates? (1 exception)
Every aggregate ignores NULL values except for COUNT(*). (So COUNT() returns the number of non-NULL values in the specified column, whereas COUNT(*) returns the number of rows in the table overall.)
Difference between WHERE and HAVING
• WHERE occurs before grouping. It filters out uninteresting rows. • HAVING occurs after grouping. It filters out uninteresting groups.
What’s wrong with this query?
SELECT age, name
FROM Person
GROUP BY age;
If you’re going to do any grouping/aggregation at all, then you must only SELECT grouped/aggregated columns.
In this example:
Each age group can include many names.
DBMS can’t decide which one to choose.
