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.
Explanation:
If faculty_id (varchar2(2))in student table has a string value instead of number then both the statements will fail. Because faculty_id in faculty table is a number column.
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
You are right as long as you are inserting numeric values in varchar2 column. If faculty_id in student table has values like ‘ENG’ or MATH’ then what will happen. This column is a varchar2 data type. On the other hand, faculty_id in faculty table is a number column. One of the conditions of joining is the data type should match.
the queries in Statement 1 and 2 are fragile… it depends what data is in faculty_id…
But from the given options, answer is D. So we should focus on the given options and not go into unnecessary details.
Hi Guys,
I’m doing preparation for Oracle Database 12c: sql fundamentals, so i have need some dumies for it. if you guys have any idea about Oracle Databse 12c: sql fundamentals then please send me at [email protected] ..
Thanks,
Amit
Finally ans is D.
What if Faculty_id had a string value in the Student table, and a number value in the Faculty table !
The answer is D, since the Faculty_ID column has diffirent data type in the two tables
the both queries will executed and return the same results.
We have to focus on what is given in the question, dont have look for other options.
Not correct question :
if you insert
insert into student select 6,’PUC’,’AA’,25 from dual;
E.
Both statements 1 and 2 fails
You are perfectly wright but we do not have the option <>. So the data has to be presumed in faculty_id from student as something which can be converted to number as ’11’ ex.
Regards,
Unless you do not have an answer (option) – both query execute with error !!! which correspond to faculty_id in student table as ‘AA’ or ‘AB’ (character) then data has to presumed ’11’ or something that can be converted into numerical. So the correct answer must be D. I saw as answer to this question B which is wrong in my opinion !
I created these tables and filled them and both queries give the same result but I can´t understand. The NATURAL JOIN is when all columns in one table have the same name, data type and length as corresponding columns in the second table.
Guys,
if u look at the column name of “location_id” in students table and
column name in “Location id” from faculty table both are different.
it will fail for the first query itself
I think the correct answer is B. The first query executes successfully but location_id can be different in STUDENT and FACULTY:
– in the table STUDENT – location of a student
– in the table FACULTY – location of a faculty
The location is not a common information for STUDENT and FACULTY.
I wanted to write C: both execute successfully but give different results
I was wrong. D is the answer.
Answer D is wrong. Statement 1 will fail because the faculty table doesn’t have the column “location_id”. There is a typo in the exhibit and the name is “location id” so “_” is missing there. Therefore the correct answer is B.