Test by Bikram | Databases | Test 1 | Question: 22

Question

Consider the following set of relations:   
EMP(eno, ename, dno)
DEPT(dno,dname)
Primary key columns are in bold and dno in EMP is a foreign key referring primary key of DEPT table.

Now consider the following queries:

QUERY : 1

SELECT * FROM emp e, dept d WHERE e.dno = d.dno;

QUERY : 2

SELECT * FROM emp NATURAL JOIN dept ;

Then which of the following statements are TRUE regarding the above queries?

1. Both Query:1 and Query:2 returns same no of rows.
2. Both Query:1 and Query:2 returns same no of columns.
3. Both Query:1 and Query:2 returns different no of rows.
4. Both Query:1 and Query:2 returns different no of columns.

• A. I, II only
• B. I, IV only
• C. II, III only
• D. III, IV only

Comments

How answer is 3 I am getting 0 rows as internal query is not returning anything???

---

Q1 columns : (Eno, ename, dno, dno, dname)

Q2 columns: (Eno, ename, dno, dname)

Result of both query is same.

Answer 1

EQUI JOIN (vs) NATURAL JOIN
Both returns same no of rows. But in Natural Join, the common columns will exist only ONCE, where as in Equi Join, the common columns repeats twice one for each table.

Comments

Sir , I typed B (the given correct answer is 2 )  in the dialogue box and it deducted my 2 marks

---

you are correct Harsh,

due to some technical fault you get less 2 marks but dont worry as your concept is correct.

---

@abhinav @harsh and @lokesh

your marks get increased , correct answer is option B , now made it multiple choice question.

Answer 2

emp NATURAL JOIN dept

Q2 has a Natural Join

FROM emp e, dept d 

WHERE e.dno = d.dno;

Q1 has a cross product with equality condition

 

Both produce the same result for the number rows, but in Cross Product columns would be duplicated, while that won't happen in case of join.

So, I and IV are correct.

 

Option B