1Z0-071 Joining Multiple Tables 2 — Questions and Answers
Question 1: What is a self-join used for?
- Joining a table to itself to compare rows within the same table (Correct answer)
- Joining two identical tables from different schemas
- Joining a table using its primary key only
- Joining without a WHERE clause
Correct answer: Joining a table to itself to compare rows within the same table
A self-join joins a table to itself using aliases, typically to compare rows such as employee-manager relationships.
Question 2: In a three-table join, how many join conditions are typically required?
- 1
- 2 (Correct answer)
- 3
- 4
Correct answer: 2
For N tables you need at minimum N-1 join conditions, so 3 tables require at least 2 join conditions.
Question 3: Which Oracle-proprietary outer join syntax uses the (+) operator?
- SELECT * FROM a LEFT JOIN b ON a.id = b.id(+)
- SELECT * FROM a, b WHERE a.id = b.id(+) (Correct answer)
- SELECT * FROM a OUTER b WHERE a.id(+) = b.id(+)
- SELECT * FROM a, b ON a.id(+) = b.id
Correct answer: SELECT * FROM a, b WHERE a.id = b.id(+)
Oracle's proprietary outer join syntax places (+) on the side of the table that may have missing rows (the optional side).
Question 4: Which type of join condition compares columns using an operator other than equals (=)?
- Equijoin
- Non-equijoin (Correct answer)
- Self-join
- Natural join
Correct answer: Non-equijoin
A non-equijoin uses comparison operators like BETWEEN, <, or > instead of = to match rows.
Question 5: When using a USING clause in a JOIN, what happens if you try to prefix the USING column with a table alias?
- The query returns duplicate columns
- Oracle raises an ORA-25154 error (Correct answer)
- The alias is silently ignored
- The query uses the left table's value
Correct answer: Oracle raises an ORA-25154 error
Oracle raises ORA-25154 if you prefix the USING clause column with a table alias because it is common to both tables.
Question 6: Which statement about NATURAL JOIN is TRUE?
- You can specify which columns to join on
- It joins only on primary key columns
- It automatically joins on all columns with the same name (Correct answer)
- It always performs a FULL OUTER JOIN
Correct answer: It automatically joins on all columns with the same name
NATURAL JOIN implicitly equi-joins all columns that share the same name between the two tables.
1Z0-071 Joining Multiple Tables 2
What is a self-join used for?