The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+27 votes
3k views

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?

  1. All queries return identical row sets for any database
  2. Query2 and Query4 return identical row sets for all databases but there exist databases for which Query1 and Query2 return different row sets
  3. There exist databases for which Query3 returns strictly fewer rows than Query2
  4. There exist databases for which Query4 will encounter an integrity violation at runtime
asked in Databases by Active (3.7k points) | 3k views
+5
Before checking the answer, just read this line again & try yourself.

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

4 Answers

+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.

answered by Boss (13.5k points)
selected by
+2
Yes. But can you explain why C is false?
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..
+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.
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 ??
+3
C) is false because sometimes query3 and query2 will return equal no of rows
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.
+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.
0
sir... m totally confused with all such kinds of questions .. here why not C is the answer ??
0
wats the final answer :(??
+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
yes, the correct answer is 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.
0
Could anyone tell me the processing of Query 4 and exists for the above mentioned entries only ?
+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.

answered by Loyal (8.1k points)
+1

query 1 and  query 3 gives duplicates may be same or different 

query 2 and query 4 gives no duplicates.

so ans B

+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:

Table- ENROLLED

STUDENT

COURSE
       A       1
      A       2
      B       1
      B       2
      C       1
      C        2
Table- PAID

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

answered by (445 points)
0
anyone please verify it!
0
Yes, A is a more appropriate option. Since they are specifying row sets in options. And According to set properties.they does not allow duplicates.
+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

answered by Active (1.3k points)
edited by
Answer:

Related questions



Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true

38,079 questions
45,571 answers
132,066 comments
49,040 users