Week 5

Question 1

What is an aggregate function?

Answer 1

A function that operates on a multi-set of values in a column of a relation and return a single value

Question 2

What are the six aggregate functions?

Answer 2

<ul>
<li>Avg</li>
<li>Min</li>
<li>Max</li>
<li>Sum</li>
<li>Count</li>
<li>Group_concat</li>
</ul>

Question 3

Are nulls counted in count(*)?

Answer 3

No

Question 4

What is the only aggregate function that would return a number if a collection only had null values?

Answer 4

Count

Question 5

What are the set operations?

Answer 5

<ul>
<li>Union</li>
<li>Intersect</li>
<li>Except</li>
</ul>

Question 6

How do set operations deal with duplicates?

Answer 6

Automatically eliminates them

Question 7

What operations can you use if you wish to use set operations without ignoring duplicates?

Answer 7

<ul>
<li>Union all</li>
<li>Intersect all</li>
<li>Except all</li>
</ul>

Question 8

What do all the set operations require?

Answer 8

Identical attributes in the two operands

Question 9

Which query in a nested query is performed first?

Answer 9

The inner query

Question 10

What does the some operator?

Answer 10

Contains at least one row

Question 11

What does the exists operation?

Answer 11

Returns a value true if the argument relation is nonempty

Question 12

What does the unique operation do?

Answer 12

Constructs tests whether a subquery has any duplicate tuples in its result, evaluates to true on an empty set

Question 13

What is a scalar subquery?

Answer 13

One where a single value is expected

Question 14

Where can you use a scalar subquery that is fun?

Answer 14

In the select clause

Question 15

What do join relations do?

Answer 15

Take two relations and return as a result another relation

Question 16

What is the join condition?

Answer 16

Defines that the tuples in the two relations match and what attributes are presented in the result of the join

Question 17

What is the join type?

Answer 17

Define how tuples in each relation that do not match are going to be treated based on the join condition

Question 18

What are the four types of joins?

Answer 18

<ul>
<li>Inner join</li>
<li>Left outer join</li>
<li>Right outer join</li>
<li>Full outer join</li>
</ul>

Question 19

What is the using keyword used for?

Answer 19

When two relations match on multiple attributes and you want to specify what attribute to join on

Question 20

How do inner and outer joins differ?

Answer 20

An inner join discards tuples when they don’t match and an outer join keeps tuples by filling the missing information with nulls

Question 21

What do left and right refer to?

Answer 21

Keep the X matching tuples even if they don’t appear in the other table

Question 22

What happens when you use on on a join?

Answer 22

Duplicate of attributes you used for join

Question 23

What is the equivalent for on in a full join?

Answer 23

using

Question 24

What are integrity constraints used for?

Answer 24

To ensure accuracy of data stored by making sure authorized changs do not result in the loss of data consistency

Question 25

What are the four integrity constraints that can be applied on a single relation?

Answer 25

<ol>
<li>Primary key</li>
<li>Not null</li>
<li>Unique</li>
<li>check(P)</li>
</ol>

Question 26

What are referential integrity constraints used for?

Answer 26

To ensure that a value that appears in one relation also appears in another relation for a related set of attributes

Question 27

What are the three referential actions?

Answer 27

<ol>
<li>Cascade</li>
<li>Restrict</li>
<li>Set null</li>
</ol>

Question 28

What does cascade do?

Answer 28

Updates or deletes cascade to the referencing table

Question 29

What does restrict do?

Answer 29

Prevents updates of attrbute values or deletions of rows that are referenced by rows in this table (not allowed if affect relations with foreign keys)

Question 30

What does set null do?

Answer 30

Updates or set attribute values in referencing relation to null

Question 31

What are assertions?

Answer 31

Conditions that the database must always satisfy