Database-chap 9

Question 1

The relational join operation merges rows from 2 tables and returns the rows with one of
the following conditions

Answer 1

– Have common values in common columns (natural
joint)
– Meet a given join condition (equality or inequality)
– Have common values in common columns or have no
matching values

Question 2

Relational Set Operators

Answer 2

-Work properly if relations are union-compatible
– Names of relation attributes must be the same and
their data types must be identical

• Inner join
  – Only rows that meet a given criterion are selected
• Outer join
  – Returns not only matching rows but the rows with
  unmatched attribute values for one table or both
  tables to be joined

Question 3

UNION

Answer 3

will exclude duplicate records

Question 4

UNION ALL

Answer 4

will retain the duplicate rows

Question 5

INTERSECT

Answer 5

Combine rows from two queries, returning only the rows that appear in both sets

Question 6

MINUS

Answer 6

Combines rows from two queries and returns only the rows that appear in the first set but not
in the second

Question 7

JOIN ON Clause

Answer 7

• If no common attribute exist in both tables
• Query will return only the rows that meet the indicated join condition

Question 8

Outer Joins

Answer 8

• Returns not only matching rows, but also rows with unmatched attribute values for one table or
  both tables to be joined
• Three types
  – Left
  – Right
  – Full

Question 9

LEFT Outer Joins

Answer 9

Returns rows matching the join condition AND rows in the left table with unmatched values in
the table

Question 10

RIGHT Outer Join

Answer 10

• Returns rows matching the join condition AND rows in the RIGHT table with unmatched values
  in the table

Question 11

FULL Outer Joins

Answer 11

• Returns rows matching the join condition, but also returns all of the rows with unmatched values in the table on either side

Question 12

Triggers

Answer 12

A trigger is procedural SQL code that is automatically invoked by the RDBMS upon the occurrence of a
given data manipulation event. It is useful to remember that:
– A trigger is invoked before or after a data row is inserted,
updated or deleted.

Question 13

Types of Triggers

Answer 13

-A statement-level trigger is assumed if you omit the FOR EACH ROW keywords.
-A row-level trigger requires use of the FOR EACH ROW keywords.