Can anyone explain why Q1, Q2, Q3 will gives the same reason

Question

In the following, the schema of R is R(a,b).
Q1: SELECT * FROM R
Q2: (SELECT * FROM R) INTERSECT (SELECT* FROM R);
Q3: SELECT DISTINCT * FROM R;

They can have duplicate values too can anyone explain

Answer 1

If R contains duplicates then Q1 and Q3 are clearly different, compare:

SELECT * FROM (
SELECT 1,2
UNION ALL 2,3
UNION ALL 2,3
) R

Result:
1,2
2,3
2,3

with

SELECT DISTINCT * FROM (
SELECT 1,2
UNION ALL 2,3
UNION ALL 2,3
) R

Result:
1,2
2,3

However, if the key is `ab`, then all rows are unique by definition and there are no duplicates. If the key is `a` then there can be no two columns with the same `a` and `b` values either so there are no duplicates. Same goes for when the key is `b`.

Answer 2

Schindlabua
12
23
23
Not possible as same row value not possible in rdbms, or did I miss your logic

Answer 3

Q1 is take everything from R
Q2 is using intersect, it'll return the data if. both left and right query have/share that same data. since both query are same, the result will be same thus everything will be return. (i'm not sure what happem if there's a duplicate rows inside the table(s) )
Q3 is same as Q1 but the distinct operation will remove any duplicate data from the result instead of showing everything

Answer 4

Preity Not in theory because relations are sets and set members are unique.

In practice you will usually have a key which will prevent duplicates from happening. However given no key both MYSQL nor MSSQL happily accept duplicate rows:

CREATE TABLE R(a integer, b integer);
INSERT INTO R(1,2);
INSERT INTO R(1,2);
SELECT * FROM R;

In your question you said "They can have duplicate values too" so I thought that is what you meant.

Answer 5

Schindlabua
I'm solving this question so in that information is missing. But yes I want to know if duplicate exist then what the answer effect on the query. in this question given answer is option a and I'm getting C that's confusing
https://www.sololearn.com/post/155300/?ref=app

Answer 6

If you applied distinct in all attributes(means distinct *) of table . There is no meaning of that because in rdbms table all rows ( tuples) are unique. So the third query will affect much or not

Answer 7

Preity Yeah you got it. This is where theory differs from practice.

All attributes of a relation always form a superkey which also means there can be no two rows that are the same.
In this case DISTINCT does nothing because each row is unique anyway.

Seeing how this is a textbook question you would be right in saying Q1, Q2, Q3 do the same.

Answer 8

Schindlabua cool thanks for the help.
Taste thanks for your comment too