OCP-1Z0-051 第122题 NATURAL JOIN（自然连接）和USING子句的用法

一、原题

View the Exhibit for the structure of the STUDENT and FACULTY tables.

You need to display the faculty name followed by the number of students handled by the faculty at the base location.
Examine the following two SQL statements:
Statement 1
SQL>SELECT faculty_name,COUNT(student_id)
            FROM student JOIN faculty
           USING (faculty_id, location_id)
   GROUP BY faculty_name;

Statement 2
SQL>SELECT faculty_name,COUNT(student_id)
             FROM student NATURAL JOIN faculty
     GROUP BY faculty_name;
Which statement is true regarding the outcome?
A. Only statement 1 executes successfully and gives the required result.
B. Only statement 2 executes successfully and gives the required result.
C. Both statements 1 and 2 execute successfully and give different results.
D. Both statements 1 and 2 execute successfully and give the same required result.

答案：D

二、题目翻译

查看STUDENT和FACULTY表的结构
要显示全体教师的名字及该教师所在位置的学生人数。
执行下面的两个语句
关于上面的两个语句，下面描述正确的是：
A.只有语句1执行成功并给出正确结果。
B.只有语句2执行成功并给出正确结果。
C.两个都执行成功，但给出不同结果。
D.两个都执行成功并给出相同结果。

三、题目解析

虽然FACULTY_ID在两个表中的数据类型不一致，但是如果两个列的数据是可以隐式转换的话，也能执行成功。

NATURAL JOIN 的用法详见：
         http://blog.csdn.net/holly2008/article/details/25501343