OCP-1Z0-051-V9.02-122题
来源:互联网 发布:pc6软件 编辑:程序博客网 时间:2024/05/19 13:58
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.
Answer: Dscott@TEST0924> create table student
2 (student_id number(2) not null,
3 student_name varchar2(20),
4 faculty_id varchar2(2),
5 location_id number(2)
6 );
Table created.
scott@TEST0924> create table faculty
2 (faculty_id number(2) not null,
3 faculty_name varchar2(20),
4 location_id number(2)
5 );
Table created.
2、向这两张表中插入数据
scott@TEST0924> insert into student values(2,'zhansan3','20',11);
1 row created.
scott@TEST0924> insert into faculty values(20,'zhansan2',11);
1 row created.
3、执行查询语句,可成功执行
scott@TEST0924> SELECT faculty_name,COUNT(student_id) FROM student JOIN faculty
2 USING (faculty_id, location_id) GROUP BY faculty_name;
FACULTY_NAME COUNT(STUDENT_ID)
-------------------- -----------------
zhansan2 1
scott@TEST0924> SELECT faculty_name,COUNT(student_id) FROM student NATURAL JOIN faculty
2 GROUP BY faculty_name;
FACULTY_NAME COUNT(STUDENT_ID)
-------------------- -----------------
zhansan2 1
4,如果向student表中的faculty_id插入字符类。
scott@TEST0924> insert into student values(2,'zhansan3','a1',11);
1 row created.
5、查询失败
scott@TEST0924> SELECT faculty_name,COUNT(student_id) FROM student NATURAL JOIN faculty
2 GROUP BY faculty_name;
SELECT faculty_name,COUNT(student_id) FROM student NATURAL JOIN faculty
*
ERROR at line 1:
ORA-01722: invalid number
scott@TEST0924> SELECT faculty_name,COUNT(student_id) FROM student JOIN faculty
2 USING (faculty_id, location_id) GROUP BY faculty_name;
USING (faculty_id, location_id) GROUP BY faculty_name
*
ERROR at line 2:
ORA-01722: invalid number
总结,当且仅当student表中的faculty_id列能隐式转换为数字类型时,这两个查询语句才可以查询成功。
- OCP-1Z0-051-V9.02-122题
- OCP-1Z0-051-V9.02-1题
- OCP-1Z0-051-V9.02-1题
- OCP-1Z0-051-V9.02-1题
- OCP-1Z0-051-V9.02-11题
- OCP-1Z0-051-V9.02-12题
- OCP-1Z0-051-V9.02-14题
- OCP-1Z0-051-V9.02-15题
- OCP-1Z0-051-V9.02-17题
- OCP-1Z0-051-V9.02-18题
- OCP-1Z0-051-V9.02-21题
- OCP-1Z0-051-V9.02-118题
- OCP-1Z0-051-V9.02-120题
- OCP-1Z0-051-V9.02-32题
- OCP-1Z0-051-V9.02-33题
- OCP-1Z0-051-V9.02-93题
- OCP-1Z0-051-V9.02-94题
- OCP-1Z0-051-V9.02-95题
- C语言求素数的不同解法
- CListBox类常见函数用法
- UCOS_STM32的任务切换
- STM32的中断处理1
- Source Insight : 程序员最得心应手的代码阅读和编辑工具(高效)
- OCP-1Z0-051-V9.02-122题
- teraSort相关
- VDC 的SDL OSD
- 黑马程序员_<<基础加强--1.5新特性(泛型)(下)>>
- 字符串专项:LA 3490
- 移动技术
- vc6.0画走势图、折线图、饼状图的准备工作
- Wine QQ 2012 ibus中文输入解决方案
- OCP-1Z0-051-V9.02-123题