Multiple choices questions in DBMS, Structured Query Language, Find equivalent alternate queries for the given SQL queries, Can we write equivalent alternate queries to an SQL query?

DBMS MCQ - Suggesting alternate equivalent queries to an SQL query

1. Consider a table STU with schema STU(Regno, Name, DOB, Phone) and Regno as the primary key. Which of the following queries is equivalent to the query "SELECT * FROM stu WHERE Regno=1 AND Phone=2;" Choose all queries that are equivalent.

a) select * from stu where regno=1 and phone=(select phone from stu where phone=2 order by phone fetch first 1 rows only);

b) select * from stu where regno=1 and phone in (select phone from stu where phone=2);

c) select * from stu where regno=1 and phone in (1, 2);

d) select * from stu where phone=2 and regno in (1, 2, 3);

Answer: (a) select * from stu where regno=1 and phone=(select phone from stu where phone=1 order by phone fetch first 1 rows only); and (b) select * from stu where regno=1 and phone in (select phone from stu where phone=2);

The query given in the question finds and displays all records that have 1 in register number attribute (column) and 2 in phone attribute from STU table.

Option (a) does the same thing using a sub-query. The sub-query here fetches all the phone numbers that are equivalent to 2. As phone attribute is not a key attribute, the result may consist of more than 1 entry. Hence, we use the ORDER BY clause and FETCH to include only the top 1 result.

Option (b) is does the same thing as the given query hence it is EQUIVALENT.

Option (c) is NOT EQUIVALENT because it accepts the record (1, 1) also which is not expected as per the given query.

Option (d) is NOT EQUIVALENT because it accepts records (1, 2), (2, 2) and (3, 2).

< Previous

Next >

************************

Related posts: