The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+29 votes
3.9k 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) | 3.9k views
+11
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

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

+1
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 ?
0

Can this be better description of why option C is false?:
 

Note that in query 3, we are selecting student from enrolled, in which student is not pk. So, in cartesian product of query 3, students will be repeated and so does in the output of the whole query 3. On the other hand, in query 2, we are selecting student from paid, where it is a pk. So query 3 output will always be greater than equal (when no student repeat in enrolled) to query 2 output. Query 3 can never return fewer rows than query 2.

Also isnt the output of query 1 and query 3 will always be same?

0
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);

 

If these are the data in the tables, why doesn't Query4 return 1,1, 3 ?
0

 here why not C is the answer ??

 check key given for both tables. For enrolled <student,course> is key means value of student can repeat but this is not the case with Paid. 

0
The options are very confusing especially B. I think it all depends on selection of the table.
+18 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.2k 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

+6 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 Junior (515 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.
0
So finally A will be the answer, row set means distinct elements from a set  ex if row contain (1,2,2,1,2,1,3,4,3,2,3,2,) so row set will be (1,2,3,4)
+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.4k 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

44,459 questions
49,916 answers
165,419 comments
65,897 users