Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Posts
    10

    Unanswered: Retry: Help! tricky query.

    Sorry i am a new comer, I lost my last two try:-(

    A self-related table:

    EMP:

    SSN Name Salary Supervisor
    111 Jim 1000 222
    222 Jack 3000
    333 Jerry 2000 222
    .....

    Pls list SSN and Name of the employee whose salary is less than 3/5 of his supervisor's?

    Many thanks!

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: Retry: Help! tricky query.

    Originally posted by oldersea
    Sorry i am a new comer, I lost my last two try:-(

    A self-related table:

    EMP:

    SSN Name Salary Supervisor
    111 Jim 1000 222
    222 Jack 3000
    333 Jerry 2000 222
    .....

    Pls list SSN and Name of the employee whose salary is less than 3/5 of his supervisor's?

    Many thanks!
    select distinct e.* from emp e, emp s where e.salary< (s.salary*3)/5

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    harshal, you have a cross join there, you are selecting everybody whose salary is less than 3/5 of anybody else's salary

    you need to match the employee to his/her supervisor through a join condition

    that's as much of a hint as i'm willing to give, because this sure looks like somebody's homework
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by r937 that's as much of a hint as i'm willing to give, because this sure looks like somebody's homework
    Do ya think ???

    -PatP

  5. #5
    Join Date
    Apr 2004
    Posts
    10
    Thank you for your hint, I have to say it is a home assignment for weeks later that my professor didn't talked about the principles yet. I am just reading the book myself and try to figure out it in advance.

    Now I kind of get an answer:

    select distinct e.*
    from EMP e left outer join EMP f
    on e.Supervisor = f.ssn
    Where e.salary < (s.salary*3)/5

    Is it right?

    Thanks!


    Originally posted by r937
    harshal, you have a cross join there, you are selecting everybody whose salary is less than 3/5 of anybody else's salary

    you need to match the employee to his/her supervisor through a join condition

    that's as much of a hint as i'm willing to give, because this sure looks like somebody's homework

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    studying in advance? i like that initiative

    first of all, you don't need distinct

    you'll be selecting employees, which are already distinct, and the fact that you're going from many to one (i.e. each employee has at most one supervisor) in the join means that your result rows will be one row per employee, so DISTINCT is not necessary (it is very expensive)

    second, you were correct in using LEFT OUTER, because not all employees will have supervisors

    you made a wee error in the table alias names

    here's your query corrected:
    PHP Code:
    select e.SSN 
         
    e.Name 
         
    e.Salary 
         
    f.Name   as SupervisorName
         
    f.Salary as SupervisorSalary
      from EMP e 
    left outer 
      join EMP s
        on e
    .Supervisor s.SSN
     where e
    .Salary < (s.Salary*3)/
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2004
    Posts
    10
    Thanks a lot!!! I appreciated it.

    I was doing all the pre-assignment whole night yeatoday.Its interesting!
    I was doing good in logic things but I didn't yet have the awareness about cost for the different statements or commands. I check my answers simply by run it and check the result.
    Originally posted by r937
    studying in advance? i like that initiative

    first of all, you don't need distinct

    you'll be selecting employees, which are already distinct, and the fact that you're going from many to one (i.e. each employee has at most one supervisor) in the join means that your result rows will be one row per employee, so DISTINCT is not necessary (it is very expensive)

    second, you were correct in using LEFT OUTER, because not all employees will have supervisors

    you made a wee error in the table alias names

    here's your query corrected:
    PHP Code:
    select e.SSN 
         
    e.Name 
         
    e.Salary 
         
    f.Name   as SupervisorName
         
    f.Salary as SupervisorSalary
      from EMP e 
    left outer 
      join EMP s
        on e
    .Supervisor s.SSN
     where e
    .Salary < (s.Salary*3)/

  8. #8
    Join Date
    Apr 2004
    Posts
    10
    Hi I think this statements also work for the query:

    select e.*
    from emp e, emp s
    where e.supervisor=s.ssn
    and e.salary< (s.salary*3)/5

    Rudy, do you think its same solution for the query, I am using Oracle SQL* plus. Does these two scripts work for all the other environment? Is there any difference in the cost between the two scripts?

    Thanks!


    Originally posted by r937
    studying in advance? i like that initiative

    first of all, you don't need distinct

    you'll be selecting employees, which are already distinct, and the fact that you're going from many to one (i.e. each employee has at most one supervisor) in the join means that your result rows will be one row per employee, so DISTINCT is not necessary (it is very expensive)

    second, you were correct in using LEFT OUTER, because not all employees will have supervisors

    you made a wee error in the table alias names

    here's your query corrected:
    PHP Code:
    select e.SSN 
         
    e.Name 
         
    e.Salary 
         
    f.Name   as SupervisorName
         
    f.Salary as SupervisorSalary
      from EMP e 
    left outer 
      join EMP s
        on e
    .Supervisor s.SSN
     where e
    .Salary < (s.Salary*3)/

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yours is not quite the same

    you select e.*, whereas i select a mix of e and s columns

    oops, i made a typo too

    here's the correction --
    PHP Code:
    select e.SSN 
         
    e.Name 
         
    e.Salary 
         
    , [b]s[/b].Name   as SupervisorName
         
    , [b]s[/b].Salary as SupervisorSalary
      from EMP e 
    left outer 
      join EMP s
        on e
    .Supervisor s.SSN
     where e
    .Salary < (s.Salary*3)/
    also, yours uses table list syntax, wherase mine uses JOIN syntax

    they are both inner joins, though, and should execute in the same time

    both will work in other databases, too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •