58 votes

SQL allows duplicate tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below:

select * from R where a in (select S.a from S)

- select R.* from R, S where R.a=S.a
- select distinct R.* from R,S where R.a=S.a
- select R.* from R,(select distinct a from S) as S1 where R.a=S1.a
- select R.* from R,S where R.a=S.a and is unique R

7

to all

option D is wrong

why because** Unique** keyword does not allow redundant tuples in R so we may not get correct result while querying

4

Take an example table of R having duplicate rows the evaluate the given query in question, you'll see duplicate tuples will be present in the output but not in b) and d) since "distinct" is used thus both are out.

Now take another table having all unique elements under attribute 'a' of table R and table S has duplicate values of some values of 'a' attribute which will produce output with only unique elements in 'a' since R.a has all unique values. Now when you evaluate the same with option a) you'll find duplicate tuples come into the result. So a) also out.

Thus c) is the answer.

In c) if duplicates are there in R.a they will be preserved and if all elements are unique in R.a, even then also c) produces output with only unique values of R.a.

Now take another table having all unique elements under attribute 'a' of table R and table S has duplicate values of some values of 'a' attribute which will produce output with only unique elements in 'a' since R.a has all unique values. Now when you evaluate the same with option a) you'll find duplicate tuples come into the result. So a) also out.

Thus c) is the answer.

In c) if duplicates are there in R.a they will be preserved and if all elements are unique in R.a, even then also c) produces output with only unique values of R.a.

73 votes

Best answer

**C)**

Consider the following instances of $R$ and $S$

$$\overset{R}{\begin{array}{|l|l|}\hline \text{A} & \text{B} & \text{C} \\\hline \text{1} & \text{2} &\text{3} \\\hline \text{1} & \text{2} &\text{3} \\\hline \text{7} & \text{8} &\text{9} \\\hline \text{7} & \text{8} &\text{9} \\\hline \end{array}} \qquad \overset{S}{\begin{array}{|l|l|}\hline \text{A} & \text{X} & \text{Z} \\\hline \text{1} & \text{2} &\text{3} \\\hline \text{3} & \text{5} &\text{7} \\\hline \text{7} & \text{6} &\text{5} \\\hline \text{7} & \text{6} &\text{5} \\\hline \end{array}}$$

Now output of given query

select * from R where a in (select S.a from S)

$$\begin{array}{|l|l|}\hline \text{A} & \text{B} & \text{C} \\\hline \text{1} & \text{2} &\text{3} \\\hline \text{1} & \text{2} &\text{3} \\\hline \text{7} & \text{8} &\text{9} \\\hline \text{7} & \text{8} &\text{9} \\\hline \end{array}$$

For Option,

A) since multiplicity of tuples is disturbed

select R.* from R, S where R.a=S.a

∴ Output will be

$$\begin{array}{|l|l|}\hline \text{A} & \text{B} & \text{C} \\\hline \text{1} & \text{2} &\text{3} \\\hline \text{1} & \text{2} &\text{3} \\\hline \text{7} & \text{8} &\text{9} \\\hline \text{7} & \text{8} &\text{9} \\\hline \text{7} & \text{8} &\text{9} \\\hline \text{7} & \text{8} &\text{9} \\\hline \end{array}$$

B)

select distinct R.* from R,S where R.a=S.a

∵ only Distinct R will be chosen in the end so, output will be

$$\begin{array}{|l|l|}\hline \text{A} & \text{B} & \text{C} \\\hline \text{1} & \text{2} &\text{3}\\\hline \text{7} & \text{8} &\text{9} \\\hline \end{array}$$

C) **ANSWER**

select R.* from R,(select distinct a from S) as S1 where R.a=S1.a

Multiplicity of tuples is maintained. ∵ Multiplicity of duplicate tuples will be distributed when there is a match between R.a and S.a and for that match S.a’s value is repeated.

So, Output will be

$$\begin{array}{|l|l|}\hline \text{A} & \text{B} & \text{C} \\\hline \text{1} & \text{2} &\text{3} \\\hline \text{1} & \text{2} &\text{3} \\\hline \text{7} & \text{8} &\text{9} \\\hline \text{7} & \text{8} &\text{9} \\\hline \end{array}$$

25

In theory, for a relation, duplicate tuples are not allowed, as a relation is basically a set, and set cannot contain duplicates. But in SQL it is allowed.

0

I got confusion with option C)

`select R.* from R,(select distinct a from S) as S1 where R.a=S1.a`

Here S1 return 1,3,7

R and S1 natural join is performed . Which produces same result as of option A ?

Where did I went wrong ? Could someple pls correct me ?

0

@akhilnadhpc : i think you got error in the "where a in " part. where a in (..), it will only match inside the bracket,it doesnt matter whether there are repeating values inside the bracket,so acc to this example it will return 1177

0

@resilientknight Thanks. Got it :)

According to my understanding ,

`select R.* from R,(select distinct a from S) as S1 where R.a=S1.a`

Distinct a from s => 1,3,7

Natural Join R with S =>(1,1,7,7) * (1,3,7) => (1,1,7,7)

0

Exact two same tuple is not allowed in any relation and you did this mistake in the relation R

correct me if i am wrong

correct me if i am wrong

8

select * from R where a in(select S.a from S)

Here maximum tupple will be all tupple of R. i.e. Tupple cannot exceed number of tupple in R [ duplicate of s.a doesn't matter]

select R.* from R, S where R.a=S.a = number of tupple can be more than R b/c of duplicate in s.a

select distinct R.* from R,S where R.a=S.a = here maximum tupple always subset of original R.

select R.* from R,(select distinct a from S) as S1 where R.a=S1.a. exactly what question says.

select R.* from R,S where R.a=S.a and is unique R = is unique R says tuples of R must be unique

0

Is the query in option D even correct? Is "is unique R" part of actual query? I am getting syntax error while running "select R.* from R,S where R.a=S.a and is unique R"

https://www.jdoodle.com/a/MQZ

0

The answer has been clearly understood but in option D considering** "is unique"**, is it actually allowed in SQL?

4 votes

It must be **C** bcoz it is the only option in which **lossless decomposition** condition can be safisfied.

and on joining the two tables..we'll not get any extra row.

**For lossless decomposition** : from the joining table's....one of the table's column must be key(or we can say unique or distinct).

for example R(a,b,c) and S(x,b,z) are two tables.So,for lossless decomposition the common attribute (i.e., 'b' ) for one of the table must be unique.

**select R.* from R,(select distinct a from S) as S1 where R.a=S1.a**

Here we are simply making the column 'a' of table 'S' distinct.Therefore satisfying the above condition.

Correct me if I am wrong

0 votes

**Multiplicity of duplicate tuples** will be distributed when there is a match between R.a and S.a; and for that match, S.a's value is repeated in each cases except the third case.

So, the output of query given in the question matches with the output of (C).

–5 votes

(A) select R.* from R, S where R.a=S.a

statement states that if R has duplicate tuples, then it will decide the join mutiplicity, so no need to filter out distict tuples

Please correct me if i am wrong !!!

Please explain me what does the D options do ?

statement states that if R has duplicate tuples, then it will decide the join mutiplicity, so no need to filter out distict tuples

Please correct me if i am wrong !!!

Please explain me what does the D options do ?

1

https://www.jdoodle.com/a/MQZ