Exam 1z0-082 topic 1 question 12 discussion

B, E and F

B, E, F

B,E,F 自己結合は内部も外部も対応してるのでDは誤りです。

A - FALSE; use OLD syntax, e.g. SELECT ... FROM employees e, employees m WHERE e.mgr=m.empno ... B - TRUE; when omitting, the error "ORA-00918: column ambiguously defined" appears C - FALSE; e.g. SELECT ... FROM employees e1, employees e2 WHERE e1.msal > e2.msal D - FALSE; valid query is e.g. SELECT e1.empno,e1.ename,e2.ename ... FROM employees e1 RIGHT OUTER JOIN employees e2 ON e1.mgr = e2.empno; E - TRUE; valid query is e.g. SELECT e.ename employee,e.bdate birth, m.ename manager FROM employees e JOIN employees m ON e.mgr = m.empno; F - TRUE; see answer B

The correct answers are B,E,F C and D are incorrect because it must not be an equijoin or an inner join. That is, it can be an equijoin , non-equijoin, inner join, outer join. A is incorrect, because it is not mandatory for an inner join to use the on clause, if you use old inner join syntax. That is, you can join two tables in a select, without using the word inner join and on clause.

I meant to say BCE

B,C,F for me. Joining a table to itself on a condition is called a self join. A table is joined to itself to compare rows in the same table. The join condition would have to be an equijoin. If it is not there is another name for the join and it's called non equijoins. I guess a self non equijoin would be possible but this goes against the point I'm making. A self join is joining the same table to itself ON a condition and that is why it has to be an equijoin. For example the left outer joined table would show all the rows from the left and unmatched rows on the right filled with nulls where there was no matches. I don't think in self joins there would be any unmatched rows due to a missing column on the right. An outer self join is not possible because it's the same table your joining. Again, you can force a outer self join but this is more of an outer join than a self join.

When you are doing a self-join, keep in mind that you are joining two "individual" data sets. There exists no restriction within Oracle that forces you to use an ON clause join rather than a WHERE clause join, or an INNER join versus an OUTER join.

a) false: you can use WHERE clause instead b) true: otherwise it reports ORA-00918 c) false: can be any condition d) false: can be any join e) true: can be used, but not necessary f) true: can be any join

B,E,F is correct

I would like to understand why the ON clause "can" instead of "must" be used? My answer was A, B and F.

Actually the Self Join CAN be the A, C and D. The word MUST makes the question wrong.

B, D , E looks correct. I don't think self join can be outer.