8,838 views

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

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$

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

sid1221

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

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

### 1 comment

thanks
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

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

by

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

Correct @mrinmoyh