Oracle Certification Chapter 4

Front 1

What is a Cartesian Join?

Back 1

A join that replicates each row from the first table with every row from the second table. It creates a join between tables by displaying every record combination

Can be created through two methods:

1) not including a joining condition in a WHERE clause
2) using the JOIN method with the CROSS JOIN keywords

Front 2

What is a Equality join?

Back 2

Creates a join through a commonly names and defined column

Can be created by two methods:

1) using the WHERE clause
2) using the join method with the NATURAL JOIN or JOIN...ON or JOIN...USING keywords

Front 3

What is a Non Equality Join

Back 3

Joins tables where there are no equivalent rows in the tables to be joined, e.g., to match values in one column of a table with a range of values in another table. Can be created in two ways:

1) using a WHERE clause
2) using the JOIN method with the JOIN...ON keywords

Front 4

What is a Self Join

Back 4

Joins a table to itself.
Can be created by two methods:

1) Using a WHERE clause
2) using the JOIN method with the JOIN...ON keywords

Front 5

What is an Outer Join

Back 5

Includes records of a table in output when there is no matching record in the other table.
Can be created by two methods:

1) Using the WHERE clause
2) using the JOIN method with the OUTER JOIN keywords, and also the keywords LEFT, RIGHT, or FULL

Front 6

What is the Outer Join Operator

Back 6

It is used to indicate the table containing the deficient rows. The operator is placed next to the table that should have null rows added to create a match.

Front 7

Set operators

Back 7

Used to combine results of multiple SELECT statements. Includes the keywords UNION, UNION ALL, INTERSECT and MINUS

Front 8

Example of an equality join using traditional method.

Back 8

SELECT title, name
FROM books, publisher
WHERE books.pubid = publisher.pubid;

Front 9

Example of an equality join using traditional method with table alias:

Back 9

SELECT title, p.pubid, name
FROM publisher p, books b
WHERE p.pubid = b.pubid;

Front 10

Example of an equality join using NATURAL JOIN

Back 10

SELECT title, pubid, name
FROM publisher NATURAL JOIN books;

Front 11

Example of an equality join using JOIN...USING

Back 11

SELECT title, pubid, name
FROM books JOIN publisher
USING(pubid);

Front 12

Example of an equality join using JOIN...ON

Back 12

SELECT title, name
FROM books b JOIN publisher p
ON b.pubid = p.pubid;

Front 13

Non-Equality Join Traditionl method

Back 13

SELECT title, gift
FROM books, promotion
WHERE retail
BETWEEN minretail AND maxretail;

Front 14

Non-Equality Join - JOIN method

Back 14

SELECT title, gift
FROM books JOIN promotion
ON retail BETWEEN minretail AND maxretail;