OCP 1Z0 051 122

122. 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 s tatement 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. 

两个表相同字段有两个(faculty_id, location_id)  
所以两个语句效果相同

TEST >CREATE TABLE student
  2  (
  3  student_id NUMBER(2) NOT NULL,
  4  sutdent_name VARCHAR2(20),
  5  faculty_id VARCHAR2(2),
  6  location_id NUMBER(2)
  7  );


表已创建。

TEST >
TEST >CREATE TABLE faculty
  2  (
  3  faculty_id VARCHAR2(2),
  4  faculty_name VARCHAR2(20),
  5  location_id NUMBER(2)
  6  );


表已创建。

TEST >SELECT faculty_name, COUNT(student_id)
  2    FROM student
  3    JOIN faculty
  4   USING (faculty_id, location_id)
  5   GROUP BY faculty_name;


未选定行


TEST >
TEST >SELECT faculty_name, COUNT(student_id)
  2    FROM student NATURAL
  3    JOIN faculty
  4   GROUP BY faculty_name;


未选定行

Answer: D