Lecture 7 Flashcards
What are some of the limitations of relational algebra?
No aggregation operators (e.g. average, minimum, …), no duplicates allowed (which is problematic when counts matter, and could be expensive to compute)
What are the difference, and similarity between a bag, and a set?
Both say nothing about the order. Bags can have repetition.
Can you calculate the number of times a value appears in a bag for the intersection, union and difference? Assume that tuple t appears n times in R and m times in S.
R (union) S has n + m copies of t, R (intersection) S has min(n, m) copies of t, R - S has max(0, n - m) copies of t
What additional operations does SQL implement in addition to pure relational algebra?
delta: duplicate-elimination operator, gamma: grouping operator (i.e. group by attribute, then find aggregate within group), aggregation operator: (e.g. SUM), pi: extended projection operator to transom attributes, tau: sorting operator, outerjoin operator: join where unmatched tuples are kept.
How do aggregation operators deal with NULL?
Usually it’s ignored, however when counting the entire table it’s not.
What types of transformations can be done with the extended projection?
Type 1: Select attribute of R,
Type 2: Rename expression OR,
Type 3: create expression that requires evaluation.
Type 3 can have scalars and/or numerical attributes of R, to which we can apply e.g. addition, subtraction, division,… or it can have strings(-like) attributes of R e.g. for concatenation
How does an outer join work with tuples that cannot be matched?
They are kept, ad padded by null values for absent attributes in join.
What are the variations of the outer join?
Full outerjoin, left outer join (only keep dangling tuples from the left), right outer join. (both for natural and theta-join).
How does a basic SELECT query (with WHERE) statement work?
SELECT L FROM R WHERE C;
Here L is a list of tuples you want from the relation, R the table, C a certain condition.
Whare are some of the attributes that can be used for a SELECT statement?
+ for addition, - for subtraction (and changing sign), * for multiplication, / for division, DIV for integer division, % for modulo.
Many implementations also offer a lot more
How can you compare strings in SQL with the LIKE statement?
You can just compare to strings, however you can also create a pattern where (low underscore) can be one of any character, and % is can be any number of any characters.
Why is an escape character used, and how can you define it?
The % has as another meaning that it is a joker for any number of characters, however if we want to find % itself we need to define an escape as follows: e.g. LIKE ‘x%’ ESCAPE ‘x’.
How to test if NULL in SQL?
We can test using X IS NULL, and X IS NOT NULL.
How do the AND, OR, and XOR, operators deal with NULL?
AND: NULL when with TRUE, FALSE when with FALSE,
OR: NULL when with FALSE, TRUE when with TRUE,
XOR: NULL when one is NULL.
If both are NULL, all return NULL
How to order in SQL?
Just add at the end ORDER BY and then a list of items you want to order by. 1st has highest priority, etc.
