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.
1st statement is not true, as “Faculty ID” in both the table have different datatype(varchar2 and number)
As in 2nd statement its natural join , so the join condition will be based only on location id.
Datatypes don’t have to match as long as they are convertible to each other. However if the datatypes are not consistent, the optimizer may not be able to use the indexes.
You may have seen in the training documentation for the 1Z0-051 course a statement like
“With natural joins you risk an error if similarly named columns don’t have the same data type”
This is in fact true. So why does the question above suggest something different?
Here is why ….
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),
FACULTY_NAME VARCHAR2(20),
LOCATION_ID NUMBER(2));
INSERT INTO STUDENT VALUES(1,’A’,’1′,’1′);
INSERT INTO STUDENT VALUES(2,’B’,’1′,’1′);
INSERT INTO STUDENT VALUES(3,’C’,’1′,’2′);
INSERT INTO STUDENT VALUES(4,’C’,’2′,’1′);
INSERT INTO FACULTY VALUES(1,’F A’,1);
INSERT INTO FACULTY VALUES(2,’F B’,1);
INSERT INTO FACULTY VALUES(1,’F B’,2);
commit;
SELECT FACULTY_NAME, COUNT(STUDENT_ID)
FROM STUDENT NATURAL JOIN FACULTY
GROUP BY FACULTY_NAME
/
FACULTY_NAME COUNT(STUDENT_ID)
——————– —————–
F A 2
F B 2
The same results can also be got via
SELECT FACULTY_NAME, COUNT(STUDENT_ID)
FROM STUDENT JOIN FACULTY
USING (FACULTY_ID, LOCATION_ID)
GROUP BY FACULTY_NAME;
FACULTY_NAME COUNT(STUDENT_ID)
——————– —————–
F A 2
F B 2
Hey so far so good!!!!
Hence one could say that this is the answer to the question above is right. Well I don’t agree and here is why.
So we see that a NATURAL JOIN can work even if the joining columns have different datatypes providing that these join columns contain data that is compatible with both these datatypes.
We have a 1 in a NUMBER column and a ‘1’ in a VARCHAR2 column.
Now lets break this.
update student set FACULTY_ID=’D’ WHERE STUDENT_ID=3;
commit;
SELECT FACULTY_NAME, COUNT(STUDENT_ID)
FROM STUDENT NATURAL JOIN FACULTY
GROUP BY FACULTY_NAME
/
SELECT FACULTY_NAME, COUNT(STUDENT_ID)
*
ERROR at line 1:
ORA-01722: invalid number
Also ……
SELECT FACULTY_NAME, COUNT(STUDENT_ID)
FROM STUDENT JOIN FACULTY
USING (FACULTY_ID, LOCATION_ID)
GROUP BY FACULTY_NAME;
USING (FACULTY_ID, LOCATION_ID)
*
ERROR at line 3:
ORA-01722: invalid number
Oh dear so the question above is a “special case”.
You are right pal!!! Since a character is included on faculty_id in student table, so, answer above is dependent on data in student table. For me answer D is wrong.
OK, it’s a special case, but…
… you still have to choose from one of the four answers.
D is the correct answer, even if you break it like you did:
both statement give exactly the same result – even in your special case (ORA-01722)
A is correct. In NATURAL JOIN, the character data type can’t be implicitly converted into numeric data type and ora-01722 invalid number error will be raised
Excellent explainition
Incredible points. Outstanding arguments. Keep up the good work.|