Week 5 Flashcards
What is an aggregate function?
A function that operates on a multi-set of values in a column of a relation and return a single value
What are the six aggregate functions?
<ul> <li>Avg</li> <li>Min</li> <li>Max</li> <li>Sum</li> <li>Count</li> <li>Group_concat</li> </ul>
Are nulls counted in count(*)?
No
What is the only aggregate function that would return a number if a collection only had null values?
Count
What are the set operations?
<ul> <li>Union</li> <li>Intersect</li> <li>Except</li> </ul>
How do set operations deal with duplicates?
Automatically eliminates them
What operations can you use if you wish to use set operations without ignoring duplicates?
<ul> <li>Union all</li> <li>Intersect all</li> <li>Except all</li> </ul>
What do all the set operations require?
Identical attributes in the two operands
Which query in a nested query is performed first?
The inner query
What does the some operator?
Contains at least one row
What does the exists operation?
Returns a value true if the argument relation is nonempty
What does the unique operation do?
Constructs tests whether a subquery has any duplicate tuples in its result, evaluates to true on an empty set
What is a scalar subquery?
One where a single value is expected
Where can you use a scalar subquery that is fun?
In the select clause
What do join relations do?
Take two relations and return as a result another relation
What is the join condition?
Defines that the tuples in the two relations match and what attributes are presented in the result of the join
What is the join type?
Define how tuples in each relation that do not match are going to be treated based on the join condition
What are the four types of joins?
<ul> <li>Inner join</li> <li>Left outer join</li> <li>Right outer join</li> <li>Full outer join</li> </ul>
What is the using keyword used for?
When two relations match on multiple attributes and you want to specify what attribute to join on
How do inner and outer joins differ?
An inner join discards tuples when they don’t match and an outer join keeps tuples by filling the missing information with nulls
What do left and right refer to?
Keep the X matching tuples even if they don’t appear in the other table
What happens when you use on on a join?
Duplicate of attributes you used for join
What is the equivalent for on in a full join?
using
What are integrity constraints used for?
To ensure accuracy of data stored by making sure authorized changs do not result in the loss of data consistency
- Primary key
- Not null
- Unique
- check(P)
- Cascade
- Restrict
- Set null
