View the exhibit for the structure of the STUDENT and FACULTY tables.
STUDENT
Name Null? Type
-—————– ——————- ————-
STUDENT_ID NOT NULL NUMBER(2)
STUDENT_NAME VARCHAR2(20)
FACULTY_ID VARCHAR2(2)
LOCATION_ID NUMBER(2)
FACULTY
Name Null? Type
-—————– ——————- ————-
FACULTY_ID NOT NULL NUMBER(2)
FACULTY_NAME VARCHAR2(20)
LOCATION_ID NUMBER(2)
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 statement 2 executes successfully and gives the required result.
B. Only statement 1 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.
|
Download Printable PDF. VALID exam to help you PASS. |
 |
if the data stored in faculty_id of table student can be “implicit conversed to number”, then both statements execute successfully and give the same result, otherwise, both will fail. So, I choose D .
The correct is D, try with this data.
The controversial field data type is converted to match and the return happens in the normal way.
In this case where an implicit conversion of data types is possible.
DROP TABLE student PURGE;
DROP TABLE faculty PURGE;
CREATE TABLE faculty (
faculty_id NUMBER(2) CONSTRAINT faculty_faculty_id_nn NOT NULL
,faculty_name VARCHAR2(20)
,location_id NUMBER(2));
INSERT INTO faculty VALUES (1, ‘UNIMAR’, 10);
INSERT INTO faculty VALUES (2, ‘FATEC – Garça’, 30);
INSERT INTO faculty VALUES (3, ‘FATEC – Marilia’, 40);
INSERT INTO faculty VALUES (5, ‘FAMEMA’, 60);
INSERT INTO faculty VALUES (6, ‘USP’, 70);
INSERT INTO faculty VALUES (7, ‘UNINOVE’, 80);
INSERT INTO faculty VALUES (8, ‘UNESP’, 90);
CREATE TABLE student (
student_id NUMBER(2) CONSTRAINT student_student_id_nn NOT NULL
,student_name VARCHAR2(20)
,faculty_id VARCHAR2(2)
,location_id NUMBER(2));
INSERT INTO student VALUES (30, ‘Mauricio’, 8, 90);
INSERT INTO student VALUES (31, ‘Camila’, 7, 80);
INSERT INTO student VALUES (32, ‘Cristiane’, 6, 70);
INSERT INTO student VALUES (33, ‘Thiago’, 5, 60);
INSERT INTO student VALUES (34, ‘Adilson’, 3, 40);
INSERT INTO student VALUES (35, ‘Gustavo’, 2, 30);
INSERT INTO student VALUES (36, ‘Leonardo’, 1, 10);
INSERT INTO student VALUES (37, ‘Rodrigo’, 1, 10);
INSERT INTO student VALUES (38, ‘Elias’, 8, 90);
INSERT INTO student VALUES (39, ‘Gele’, 7, 80);
INSERT INTO student VALUES (40, ‘Francisco’, 6, 70);
INSERT INTO student VALUES (41, ‘Joao’, 8, 90);
INSERT INTO student VALUES (42, ‘Edson’, 8, 90);
INSERT INTO student VALUES (43, ‘Alberto’, 6, 70);
INSERT INTO student VALUES (44, ‘Anderson’, 7, 80);
INSERT INTO student VALUES (45, ‘Allan’, 2, 30);
INSERT INTO student VALUES (46, ‘Eduardo’, 8, 90);
NATURAL The NATURAL keyword indicates that a natural join is being performed. A natural join is based on all columns in the two tables that have the same name. It selects rows from the two tables that have equal values in the relevant columns. If two columns with the same name do not have compatible data types, then an error is raised. When specifying columns that are involved in the natural join, do not qualify the column name with a table name or table alias.
The second query will result in an error because of the non-matching types.
It will give error if the table Student have string values instead of number for the faculty_id.
create table student (id number(2));
create table student_detail (id varchar(2));
INSERT INTO student VALUES(1);
INSERT INTO student_detail VALUES(‘A’);
SELECT id FROM student NATURAL JOIN student_detail;
Gives:
ORA-01722: invalid number
01722. 00000 – “invalid number”
*Cause: The specified number was invalid.
*Action: Specify a valid number.
The answer is not D.
NATURAL JOINS use all the columns with the same name AND data types. The FACULTY_ID data type is different in both tables, ergo, NATURAL JOIN will not join on FACULTY_ID at all, only LOCATION_ID.
The ‘most’ correct answer would be C because:
(a) both queries do execute successfully and
(b) both queries give different results
Neither query deterministically provides the right answer: faculty name followed by the number of students handled by the faculty at the base location BECAUSE no criteria for ‘BASE’ location is specified. The closest we get is a count of student_ids by faculty by location.
We would need a LOCATION_ID to reach the required result:
SELECT faculty_name, COUNT(student_id)
FROM student JOIN faculty
USING (faculty_id, location_id)
WHERE location_id = ‘BASE_ID’
GROUP BY faculty_name;
Note that B will have the correct answer in it. B maybe the answer that the test is looking for. But based on what the question is, the RIGHTEST answer is C.
Again: the question is aculty name followed by the number of students handled by the faculty AT THE BASE LOCATION (not every location!)
data type for faulty_id is different in both the tables, when column name is same but data type is different should use USING clause; Hence ANSWER B is correct.
natural join uses all column have same name and datatype so here natural join will depend on tow column as the the first statement
(TRUE) D. Both statements 1 and 2 execute successfully and give the same required result.
but how do you know which column uses natural join? location_id or faculty_id as a join criteria?
natual join uses all columns with same name. So, in this example there are two columns with both tables with same name. so it will be joined by FACULTY_ID and LOCATION_ID