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

    Unanswered: Help 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
    Singapore
    Posts
    89

    Re: Help with a couple of queries

    Originally posted by Centro360
    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.

    Why dont you use rank and dense rank options available in Oracle 9i.

    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
    Thanks and Regards,

    Praveen Pulikunnu

Posting Permissions

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