"In Paid, student is the primary key but in Enrolled, student alone is not the primary key."

The Gateway to Computer Science Excellence

First time here? Checkout the FAQ!

x

+27 votes

Consider the relation enrolled (student, course) in which (student, course) is the primary key, and the relation paid (student, amount) where student is the primary key. Assume no null values and no foreign keys or integrity constraints.

Given the following four queries:

Query1:

select student from enrolled where student in (select student from paid)

Query2:

select student from paid where student in (select student from enrolled)

Query3:

select E.student from enrolled E, paid P where E.student = P.student

Query4:

select student from paid where exists (select * from enrolled where enrolled.student = paid.student)

Which one of the following statements is correct?

- All queries return identical row sets for any database
- Query2 and Query4 return identical row sets for all databases but there exist databases for which Query1 and Query2 return different row sets
- There exist databases for which Query3 returns strictly fewer rows than Query2
- There exist databases for which Query4 will encounter an integrity violation at runtime

+26 votes

Best answer

Query 1 and Query 3: output will be same

and Query 2 and 4, output will be same

I have run these queries on online compiler, this what I get

BEGIN TRANSACTION; -- /* Create a table called NAMES */ -- CREATE TABLE E(Id integer); -- CREATE TABLE P(Id integer); -- -- /* Create few records in this table */ -- INSERT INTO E VALUES(1); -- INSERT INTO E VALUES(1); -- INSERT INTO E VALUES(3); -- INSERT INTO E VALUES(3); -- -- INSERT INTO P VALUES(1); -- INSERT INTO P VALUES(2); -- INSERT INTO P VALUES(3); -- INSERT INTO P VALUES(4); COMMIT; /* Display all the records from the table */ -- SELECT * FROM E; -- select "------"; -- SELECT * FROM P; -- select "------"; select "Query 1:"; select E.id from E where E.id in (select P.id from P); select "Query 2:"; select id from P where id in (select id from E); select "Query 3:"; select E.id from E e, P p where e.id = p.id; select "Query 4:"; select id from P where exists (select * from E where E.id = P.id); /* output */ Query 1: 1 1 3 3 Query 2: 1 3 Query 3: 1 1 3 3 Query 4: 1 3

So according to me answer should be **B.**

0

As far as number and duplicate tuples are concerned Query1 and Query1 will result into entirely different result BUT if we DON'T COUNT duplicate tuples from Query 1 , it is same as result produced by Query2.

On the web I found the answer to be (A). They furnish the REASON that although Queary1will have DUPLICATE tuples which Query2 would not have, still they PRODUCE same "ROWSET".

I am totally blown with this term "ROWSET". Please somebody explain what does this thiserm really mean. Do a rowset mean a set counting Distinct- rows only JUST LIKE WE DO IN A MATHEMATICAL SET where duplicate elements are not counted and so the sets A={ 1,2,2} is treated same as B={1,2}.

Please respond to this..

On the web I found the answer to be (A). They furnish the REASON that although Queary1will have DUPLICATE tuples which Query2 would not have, still they PRODUCE same "ROWSET".

I am totally blown with this term "ROWSET". Please somebody explain what does this thiserm really mean. Do a rowset mean a set counting Distinct- rows only JUST LIKE WE DO IN A MATHEMATICAL SET where duplicate elements are not counted and so the sets A={ 1,2,2} is treated same as B={1,2}.

Please respond to this..

+1

i think ans should be A.

here, numbers of rows which are returned from query are different as per Vikrant said. but the set of rows in each query is same.

and in option C, i am able to find some relations which are returning same number of rows for query 2 and 3. they mentioned strictly less than.

here, numbers of rows which are returned from query are different as per Vikrant said. but the set of rows in each query is same.

and in option C, i am able to find some relations which are returning same number of rows for query 2 and 3. they mentioned strictly less than.

0

Answer is any of (A) or (B). For (A) I DON'T understand the meaning of ROW-SET exactly. Could you discuss that please. (C) is always false because rows returned by Query3 are greater than or equal to that returned by Query2 in case one or more students has opted more than one course.

0

(C) is FALSE because in case some student has opted more than one course, Query3 will return greater number of rows than Query 2. In case each student has opted just one course Query3 will return equal number of rows as returned by Query2. Query3 never returns less number of rows than number of rows returned by Query2.

0

in option B they mentioned that Query 1 and 2 return different ROW SETS. they are returning the different number of rows but if we make a set with distinct rows (ROW SET) then it will be same. so i think B is not correct.

+2

As far as I know, row set means a set of rows returned by the query. It also has a rowid, which ensures that the set can contain duplicate values and still set elements be distinct. But could not find any reference for this.

0

So what should be the answer finally.I dint understand this statement of you "set can contain duplicate values and still set elements be distinct" !! Do we count the duplicate rows just once for a rowset or we do it number of times just because each row has unique id as you said ??

0

In Sql a Row will have duplicates by default.

But "Row set" means we include concept of set theory which says duplicate elements not allowed in set, thus "rows set" means rows without duplicate elements.

Thus answer should be A.

But "Row set" means we include concept of set theory which says duplicate elements not allowed in set, thus "rows set" means rows without duplicate elements.

Thus answer should be A.

+1

@arjun sir a set is collection of distinct object i.e. no duplicate should present . option A is correct here , not B . as they ask for rowsets

reference : https://en.wikipedia.org/wiki/Set_(mathematics)

+3

I already explained that. Given an SQL query, answer here should be obvious :O "row set" is used to clarify that it is a set of rows and not set of values.

0

It is not saying set of student it is saying set of rows..I think answer is B...if in question it says"Set of Student" then I think answer should be A.

+1

"row set" is used to clarify that it is a set of rows and not set of values

Hence answer Is Option B, not Option A.

0

They have given, no foreign keys or integrity constraints., else option c) could have been contradicted, by set theory properties, i.e. fk should be a subset of pk, if student in enroll in fk refering..

0

C is false because Q2 is a subset of Q3 but not vice versa. Q2 gives result for "there exists a student in table 1 who has paid for some course".Q3 is guaranteed to find such students because cartesian product will give us all possible combinations.

On the other hand there can be multiple entries for a given student in table 1,"this doesn't affect query 2 result" but we'll get multiple results for q3.

On the other hand there can be multiple entries for a given student in table 1,"this doesn't affect query 2 result" but we'll get multiple results for q3.

+14 votes

query 1 and 3:In enrolled table student is not a key, so duplicate student values might be present which will be printed in final result

query 2 and 4:In Paid table 'Student' is a key, ensuring it has unique values only therefore these two queries will return distinct student values.

Therefore only b is correct.

+4 votes

Correct answer is Option (A).

**Explanation :**

Query 1 and Query 3 are giving exactly same result i.e identical rows. Similarly, Query 2 and Query 4 are also giving identical rows. But all the** 4 queries are same in terms of identical row sets.**

**Identical row Set -** it means a set in which all elements (here rows) are same but no duplicate rows are present. Since it is defined in terms of set, and set don't allow duplicate entries.

So all the query will give same identical row set but (Q1,Q3) will give same rows and (Q2,Q4) will give same rows.

Lets take an example:

## STUDENT |
COURSE |
---|---|

A | 1 |

A | 2 |

B | 1 |

B | 2 |

C | 1 |

C | 2 |

## STUDENT |
## AMOUNT |
---|---|

A | 10 |

B | 11 |

C | 12 |

Query1:

select student from enrolled where student in (select student from paid)

**Output - inner query- (a,b,c) Final result- (a,a,b,b,c,c)**

Query2:

select student from paid where student in (select student from enrolled)

**Output - inner query- (a,a,b,b,c,c) Final result- (a,b,c)**

Query3:

select E.student from enrolled E, paid P where E.student = P.student

**Output-** 1st Cartesian product of (Enrolled , Paid) . Then ** Final result- (a,a,b,b,c,c)**

Query4:

select student from paid where exists (select * from enrolled where enrolled.student = paid.student)

**Output-** It's a corelated subquery. So, a tuple of paid is selected even if there is atleast 1 tuple in enrolled table such that there is match in student attribute. **Final result- (a,b,c)**

Thus, no of rows may be different. But **Row set** of all the query is (a,b,c).

Answer is option 1: All queries return identical row sets for any database

+2 votes

just check query 1 and query 2 are same or not and they are not equal try with some student name duplicate in enrolled table but unique in paid table because student is key in paid table so answers will be different for both the queries

**conclude answer B**

and if both are same then go with the solving whole question

- All categories
- General Aptitude 1.3k
- Engineering Mathematics 5.5k
- Digital Logic 2.1k
- Programming & DS 4k
- Algorithms 3.4k
- Theory of Computation 4.2k
- Compiler Design 1.6k
- Databases 3.1k
- CO & Architecture 2.7k
- Computer Networks 3.1k
- Non GATE 1.1k
- Others 1.4k
- Admissions 501
- Exam Queries 449
- Tier 1 Placement Questions 19
- Job Queries 62
- Projects 12

38,079 questions

45,571 answers

132,066 comments

49,040 users