GATE Overflow | Mock GATE | Test 1 | Question: 27

Question

A single query in DBMS can be executed through different algorithms or re-written in different forms and structures. The most optimal pathway of getting the correct output among all these algorithms can be obtained by the process of query optimization.
Consider the following query:
With emp_table as select columnA from Employee where columnB> 3000;
Emp_table intersect (select columnA from Emp_table where columnC<3000);
Identify the correct optimized SQL query.

• A. select columnA from Employee where columnB>3000 intersect select columnA from Employee where columnC<3000;
• B. select columnA from Employee where columnB >3000 and columnC<3000;
• C. select columnA from Employee;
• D. The given query is already in optimized form.

Comments

Yes then you are right, first column A is selected. But then option B is not matching.

Then ans should be A right?

---

@Arpit Patel

i think that same time consume by the query that is in the option A because it also traverse the DB 2 times so i think D might be correct according to given options

---

Both A and actual query is same. So yeah D can be ans being no other query more optimized.

Answer 1

(B) is the correct option! 

As, SQL is a row by row processing of the relation data, it takes one row apply both the conditions

where $columnB>3000$ and $ColumnC<3000$,

And, if the record passes both the conditions then it will be selected. if there are n records in a table then we need to fetch exactly them once.

But if we first find those records where $columnB>3000$ and then we find the records where $ColumnC<3000$, and then take their INTERSECTION, then it's a too much work, as, first we are fetching the entire table twice and then applying INTERSECTION operation, furthermore, intersection is an expensive operation because it has to compare the results set and then it removes duplicate also.

Comments

In the question it is given that select column A from emp_table where column c<3000

But In the Emp_table there is only 1 attribute which is A . Then how is it possible?