in Databases
8,838 views
35 votes
35 votes

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
  1. 0 row and 4 columns
  2. 3 rows and 4 columns
  3. 3 rows and 5 columns
  4. 6 rows and 5 columns
in Databases
8.8k views

5 Answers

58 votes
58 votes
Best answer
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.
edited by

4 Comments

check again the common attributes is categoryid and they are shown as products.categoryID and categories.CategoryID seperately
2
2

sid1221

In cartesian product common attributes from both the tables will come!

0
0

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

 

0
0
13 votes
13 votes

1 comment

thanks
0
0
13 votes
13 votes
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.
by
7 votes
7 votes

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}$

2 Comments

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

Correct @mrinmoyh

0
0
Answer:

Related questions