Understanding SQL Queries

Question

What is the best way to understand/decode correlated nested queries. I have read all the concept of Keywords/Functions such as EXISTS, NOT EXISTS, IN, EXCEPT, UNIQUE, UNION etc. I also solved question based on queries. I'm able to solve or extract the meaning of query but it takes too much time. I think I'm not able to visualize the query correctly. I want to know how you visualize or solve query? How do you extract the meaning out of it? What is the right way/method to solve such type of question?

Here is way to understand queries but it doesn't talk about correlated nested query. It is easy to write query but little hard to understand meaning of query written by others.

Answer 1

Lets say we have three sets 

     1.Sailors

     2.Boats

     3.Reservation

1.IN/NOT IN--

                  SELECT S.SNAME FROM SAILORS S WHERE S.SID IN

                                 (SELECT R.SID FROM RESERVES R WHERE R.BID =103)  

This query works as follow

                   1.Take all the suppliers id from reservation table who reserve boat number 103.----->this is set S

                   2.Now take every sailor row from sailor table such that, that sid is in set S and print the sailor names of such                             sailors.

           So,this query prints "sailor names of all the sailors which reserve boat number 103"

Now take case when inner query result is empty or no supplier book boat number 103.

In that case ,there will be no row in inner table ,hence we will get empty result.

So,In is just a "equal condition",which check every row of outer table with every row of inner table.

And ,Not in is  "Not equal to condition".

Lets take one another example

      SELECT S.SNAME FROM SAILORS S WHERE S.SID  NOT IN

                            (    (SELECT R.SID FROM RESERVES R WHERE R.BID  

                                  NOT IN 

                               (SELECT B.SID FROM BOATS B WHERE B.color ="RED") )

2.Operator ANY/ALL

                Select s.rating from Sailor S where S.rating >=ALL

                                                     (Select S.rating From Sailor S)

This query return highest rating from the sailor table.

For each outer table row it compare it with every row of inner table with given operator.

If operator result true in every case then it print outer table row.

ALL-true when set is empty-same as For all quantifier

ANY-false when set is empty-same as there exist quantifier

                    

3.Exists/Not Exists

This operator work in corelated nested queries.

Exist-true when there is at least one row in inner query result---true when inner query result is non-empty

Not exist -true when there is no row in inner table-----true when inner query result is empty

1.Select s.sid from sailors S where exists

              (Select * from Reserves R where R.bid=007

                  and R.sid=S.sid )

2.

1.Select s.sid from sailors S where not exists

              (Select * from Reserves R where R.bid=007

                  and R.sid=S.sid )

for above query inner query result should be empty.

And inner query result is empty when selected sid from outer query doesn't reserve boat 007.

So,result is all those sid which didn't reserve any boat with boat number 007.

3.

Select s.sid from sailors S where not exists

        (  select * from Boat B where

           not exist   (Select * from Reserves R where R.bid=B.bid

                  and R.sid=S.sid )

)

Hence this query select all those suppliers id which have at least one boat.

Comments

Sir last query is not correct. Which table does B refers to?

---

Edited.

---

Sir is there any other way to visualize last query. It is quite difficult and takes time.

---

Yes.i  thought of writing it in terms of predicates.

but that didn't work.

Just assign value to outer query and remove it.

Then do same with next level,and check whether result will be empty or not.