1z0-071 Oracle Database 12c SQL 第51题 JOIN

最新推荐文章于 2021-04-03 14:17:42 发布

转载最新推荐文章于 2021-04-03 14:17:42 发布 · 1.1k 阅读

文章标签：

#1z0-071 #ocp #oracle 11g #oracle 12c

数据库：Oracle DBA成长之路专栏收录该内容

165 篇文章

订阅专栏

本文通过两个具体的SQL查询语句展示了如何使用JOIN从STUDENT和FACULTY表中获取每位教师对应的学生数量，并对比了使用不同JOIN类型的结果，发现两种查询方式均能成功执行并返回相同的数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Q51. 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:

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.

Correct Answer: D

create table student (
student_id number(2) not null,
student_name varchar2(20),
faculty_id varchar2(2),
location_id number(2));

create table faculty (
faculty_id number(2) not null,
faculty_name varchar2(20),
location_id number(2));

insert into student
select 1,’David’,11,10 from dual;

insert into faculty
select 11,’PUC’,10 from dual;

select faculty_name,count(student_id)
from student join faculty
using (faculty_id,location_id)
group by faculty_name;

FACULTY_NAME COUNT(STUDENT_ID)
——————– —————–
PUC 1

select faculty_name,count(student_id)
from student natural join faculty
group by faculty_name;

FACULTY_NAME COUNT(STUDENT_ID)
——————– —————–
PUC 1

So, the correct answer is D