Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    13

    Unhappy Unanswered: Urgent help needed with a couple of queries

    Hello,

    I have written a couple of queries which I would like to have some feedback on on how they can be improved.

    The first query is:

    SELECT p.pname
    FROM person p, employee e
    WHERE p.pnum = e.pnum
    AND e.salary IN
    (SELECT MIN(e.salary)
    FROM employee e);

    Basically, this query finds the name of the employee who earns the lowest salary. BUT, how do I manipulate this query in such a way that it finds the name of the employee who earns the SECOND lowest salary? I cannot seem to figure it out.


    The second query in question is:

    SELECT p.pname, e.office
    FROM person p LEFT JOIN employee e
    ON p.pnum = e.pnum;

    This query most probably doesn't work. It is intended to find the name(s) of the employee(s) who do not share an office. Could someone please help me out with this sticky query?

    A schema diagram can be found at
    http://www.cs.ucl.ac.uk/staff/j.lewi..._employ_er.gif

    Please help if you can.

    Thank you very very much indeed.

    Sincerely,
    c360

  2. #2
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55
    For the first one, i sould write the query like this :

    PHP Code:
    SELECT p.pname
    FROM person p
    employee e
    WHERE p
    .pnum e.pnum
    ORDER BY e
    .salary ASC 
    Then you'll have all employees order by their salary.

    If you just want the second one, try this :
    PHP Code:
    SELECT p.pname
    FROM person p
    employee e
    WHERE p
    .pnum e.pnum
    AND rownum 2
    ORDER BY e
    .salary ASC 
    For the second one, if i understand what you want to do, i sould write the query like this :

    PHP Code:
    SELECT p.name
    FROM person p
    WHERE p
    .pnum NOT IN (SELECT e.pnum
                                       FROM employee e


  3. #3
    Join Date
    Apr 2004
    Posts
    13

    Unhappy

    Hello venderic,

    Thank you for your reply!

    About the second question, I wanted to find the names of the employees who do not share an office.

    Any idea on this?

    Many thanks,
    c360

Posting Permissions

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