edited by
20,083 views
61 votes
61 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?

  1. All queries return identical row sets for any database
  2. Query$2$ and Query$4$ return identical row sets for all databases but there exist databases for which Query$1$ and Query$2$ return different row sets
  3. There exist databases for which Query$3$ returns strictly fewer rows than Query$2$
  4. There exist databases for which Query$4$ will encounter an integrity violation at runtime
edited by

7 Answers

Best answer
51 votes
51 votes

Query$1$ and Query$3:$ output will be the same 

and Query$2$ and Query$4:$ output will be same

I have run these queries on the 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, answer should be B.

edited by
35 votes
35 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.

22 votes
22 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

2 votes
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

edited by
Answer:

Related questions