Results 1 to 10 of 10

Thread: Sequence of SQL statement processed

  1. #1
    Junior Member
    Join Date
    Feb 2008
    Answers
    6

    Sequence of SQL statement processed

    I would like to know In what sequence SQL statement are processed...?
    (The clauses of the subselect processing)

    Thanks
    Kevin


  2. #2
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Sequence of SQL statement processed

    From inner block to outer block.


  3. #3
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Sequence of SQL statement processed

    Quote Originally Posted by kevinphilip View Post
    I would like to know In what sequence SQL statement are processed...?
    (The clauses of the subselect processing)

    Thanks
    Kevin
    follow this link


  4. #4
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Sequence of SQL statement processed

    Quote Originally Posted by debasisdas View Post
    From inner block to outer block.
    Not always I think..

    Generally for subqueries the optimizer first runs the inner most block first then depending upong the result set , process the outer block.

    But in case of EXISTS, optimizer runs main query first and then applies dataset to the subquery.


  5. #5
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Sequence of SQL statement processed

    Quote Originally Posted by krishnaindia2007 View Post
    Not always I think..

    Generally for subqueries the optimizer first runs the inner most block first then depending upong the result set , process the outer block.

    But in case of EXISTS, optimizer runs main query first and then applies dataset to the subquery.
    Yes you are right . That depends on the query.


  6. #6
    Junior Member
    Join Date
    Apr 2008
    Answers
    4

    Re: Sequence of SQL statement processed

    Could you please explain how the count function works here? question)sql query to get 4th or 5th maximum value from a table ans: select * from jobhistory x where (5=(select count(distinct(y.salary)) from jobhistory y where y.salary>=x.salary))


  7. #7
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Sequence of SQL statement processed

    Quote Originally Posted by kirandstyle View Post
    Could you please explain how the count function works here? question)sql query to get 4th or 5th maximum value from a table ans: select * from jobhistory x where (5=(select count(distinct(y.salary)) from jobhistory y where y.salary>=x.salary))
    Why do not you start a new thread for your postings?

    It returns the record with 5th highest salary not 4th of 5th maximum value.

    Count returns number of records that meets specified codition.

    It is an example for correlated query i.e inner query is evaluated once for each row of outer query.

    Each time it checks whether the salary is 5th highest salary or not and if it is fifth highest sal then displays particulars of that record.


  8. #8
    Junior Member
    Join Date
    Apr 2008
    Answers
    4

    Re: Sequence of SQL statement processed

    Quote Originally Posted by krishnaindia2007 View Post
    Why do not you start a new thread for your postings?

    It returns the record with 5th highest salary not 4th of 5th maximum value.

    Count returns number of records that meets specified codition.

    It is an example for correlated query i.e inner query is evaluated once for each row of outer query.

    Each time it checks whether the salary is 5th highest salary or not and if it is fifth highest sal then displays particulars of that record.
    could you please explain me how does the below work. suppose in a situation we have a table called jobhistory where, we have values in salary column as (3000,4000,5000,6000,7000)
    so in order to calculate the nth highest salary i used the abve sql query...but i m confused like how does the part of the sql query i.e., y.salary>=x.salary works.
    does this statement means tht each value of salary column in jobhistory table y looks for >= condition in each value of salary coulumn in table x.
    if so...suppose say 3000 of y table is comparing itself to all values of salary in table x
    then 3000>=3000
    3000>=4000
    3000>=5000
    3000>=6000
    3000>=7000

    4000>=3000
    4000>=4000
    4000>=5000
    4000>=6000
    4000>=7000
    and sooooo on....
    if this is correct
    then in statement to calculate the nth or 5th highest salary
    i.e.,select * from jobhistory x where (5=(select count(distinct(y.salary)) from jobhistory y where y.salary>=x.salary))

    how the statement count will work
    how does the 1st highest salary is determined from the above combinations.
    Sir,
    Please explain me how exactly this works.
    i Understood the statement syntactically but logically im a little confused like how this works


  9. #9
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Sequence of SQL statement processed

    >> Please explain me how exactly this works.
    i Understood the statement syntactically but logically im a little confused like how this works



    The correlated subquery performs the following steps for each row of the parent query:
    1. The sal of the row is determined (i.e. x.salary from parent statement).
    2. Then it compares that x.salary with each distinct salary value of jobhistory table.
    3. For example if x.salary is 5000. Then it checks no of records in jobhistory table having more than 5000. If the count is exactly 5 then the codition becomes true.
    4. Then outer query or parent statement is executed.

    I hope now it is clear.


  10. #10
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Sequence of SQL statement processed

    Quote Originally Posted by kirandstyle View Post
    Could you please explain how the count function works here? question)sql query to get 4th or 5th maximum value from a table ans: select * from jobhistory x where (5=(select count(distinct(y.salary)) from jobhistory y where y.salary>=x.salary))
    Please not hijack threads started by others .
    Start a thread of your own for a new question in the related forum

    Regards
    Moderator.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact