GATE IT 2004 | Question: 74

Question

A relational database contains two tables student and department in which student table has columns roll_no, name and dept_id and department table has columns dept_id and dept_name. The following insert statements were executed successfully to populate the empty tables:

Insert into department values (1, 'Mathematics')
Insert into department values (2, 'Physics')
Insert into student values (l, 'Navin', 1)
Insert into student values (2, 'Mukesh', 2)
Insert into student values (3, 'Gita', 1)

How many rows and columns will be retrieved by the following SQL statement?

Select * from student, department

• A. 0 row and 4 columns
• B. 3 rows and 4 columns
• C. 3 rows and 5 columns
• D. 6 rows and 5 columns

Comments

here in the sql query it is not given any join its default so we have done cartesian product which results in 6 rows and 5 columns

---

by default we will use Cartesian product

---

$\text{Option B, if question was "Natural Join" instead}$

\[
\begin{array}{|c|c|c|c|}
\hline
\text{dept\_id} & \text{roll\_no} & \text{name} & \text{dept\_name} \\
\hline
1 & 1 & \text{Navi} & \text{Mathematics} \\
1 & 3 & \text{Gita} & \text{Mathematics} \\
2 & 2 & \text{Mukesh} & \text{Physics} \\
\hline
\end{array}
\]

also,for a natural join:

Common attributes first → remaining attributes of left relation → remaining attributes of right relation.

\[
\underbrace{\text{dept\_id}}_{\text{common}}
\quad|\quad
\underbrace{\text{roll\_no, name}}_{\text{student}}
\quad|\quad
\underbrace{\text{dept\_name}}_{\text{department}}
\]

Answer 1

Since, there is no specific joining condition specified, it will retrieve Cartesian product of the table

Number of rows = product of number of rows in each relation $=3\times 2 = 6$

Number of columns = sum of number of columns $= 3+2 = 5$

Answer: D.

Comments

The Cartesian product:-

roll_no	name	dept_id	dept_id	dept_name
l	Navin	1	1	Mathematics
l	Navin	1	2	Physics
2	Mukesh	2	1	Mathematics
2	Mukesh	2	2	Physics
3	Gita	3	1	Mathematics
3	Gita	3	2	Physics

 

---

@sid1221

sql does not permit same attribute name in same relation then how here 5 attribute ??

https://gateoverflow.in/1502/gate1999_2-25

“It is taughing about same relation i.e. same table but here student and department are two different table”

 

Correct me if I am wrong.

---

"SQL does not permit same attribute name in same relation" is Explained Here: https://gateoverflow.in/1502/gate-cse-1999-question-2-25?show=429843#a429843

Answer 2

In join, first of all we do cross product, then we compare the matching attributes usually and then we apply required conditions if any.

In Relational Algebra, Natural Join means it automatically does Cross Product + Matching of common attributes.
(Though we mention it sometimes too)

In SQL   select * from table1,table2;
It means cross product only. It is not going filter based on matching columns, we have to always explicitly mention it like student.dept_id = department.dept_id;    but it is not mentioned.

So it is clear that in cross product we will get 3*2 rows and 5 columns.

Answer 3

https://www.javatpoint.com/sql-cross-join

1.  

Comments

thanks

Answer 4

In Sql,there is no explicit  natural join .It can be done using -:

select * from student s ,department d where s.dept_id=d.dept_id;

$\text{see output here}-:$ http://sqlfiddle.com/#!9/a40082/8

But in the question ,it is given as-:

Select * from student, department

$\text{It will simply perform the Cartesian product}-:$http://sqlfiddle.com/#!9/a40082/6

---

Had the question asked for Relational Algebra, then there would be natural join (on Dept_id) and the answer would have been $\text{3 rows and 5 columns}$

Comments

If it would ask in relational algebra then the ans. would be 3 rows & 4 columns, I think

---

Correct @mrinmoyh

Answer 5

by using natural join 3 rows and 4 columns get

Answer 6

Important point

Instead of  cross product if you have a natural join then  Student(ROLL,NAME,DID) ,DEPARTMENT(DID,DNAME) then R natural join S will have only 4 columns(roll,name,did,dname) and 6 rows .But if you take cross product then we will have 5 columns and 6 rows