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

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.

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

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.

+1

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

query 2 and query 4 gives no duplicates.

so ans B

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

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

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

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

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

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

edited by

1
2