Kerala PSC AP Exam

Question

A relational database contains two tables employee and department in which employee table has coumns emp-no 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,'computer science')
Insert into department values(2,'information technology')
insert into employee values(1,'Navin',1)
insert into employee values(2,'Mukesh',2)
insert into employee values(3,'Suresh',2)

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

Select * from employee, 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

Answer 1

the two tables will have 2 tuples , 3 tuples thus there cartesian product will be 6 rows and since there are 3+2 collumns thus 5 collums will be there.  GATE-IT-2004

Answer 2

Sice we have same column name in both table e.g dept_id

so natural join is performed for emp.dept_id = dept.dept_id

natural join: 1st do the cartesian product then apply the above condition.

It will return 3 rows and 4 columns

Comments

natural join is applied based on common attribute in relational algebra. But in SQL it is not happening by default.

---

@cse 23 can u explain in tabular formet