12,201 views
41 votes
41 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

5 Answers

Best answer
63 votes
63 votes
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.
15 votes
15 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.
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}$

Answer:

Related questions

38 votes
38 votes
3 answers
1
Ishrat Jahan asked Nov 2, 2014
10,851 views
Consider two tables in a relational database with columns and rows as follows:$$\overset{\text{Table: Student}}{\begin{array}{|c|c|c|} \hline \textbf {Roll_no} & \textbf{...
43 votes
43 votes
5 answers
2
Ishrat Jahan asked Nov 2, 2014
10,532 views
A table T1 in a relational database has the following rows and columns: $$\begin{array}{|c|c|c|} \hline \text {Roll no. } & \text {Marks} \\\hline 1& 10 \\\hline 2 & 20 ...
43 votes
43 votes
6 answers
4
Ishrat Jahan asked Nov 2, 2014
12,131 views
Consider the following schedule $S$ of transactions $T1$ and $T2:$$${\begin{array}{l|l}\textbf{T1}& \textbf{T2} \\\hline\text{Read(A)} \\\text{A = A – 10}\\& \text...