Results 1 to 3 of 3

Thread: Query question for an Oracle class

  1. #1
    Junior Member
    Join Date
    Feb 2009
    Answers
    1

    Query question for an Oracle class

    Need some help in figuring out the answer to a query for my oracle class. This one has me stumped.

    This is the question: the hr department needs a list of countries that have no departments located in them. Display the country id and the name of the countries. Use set operators to create the query.


    The structure for the dbs is:

    Departments TABLE -
    Has fields:
    department_id not null number(4)
    department_name not null varchar2(30)
    manager_id number(6)
    location_id number(4)


    TABLE:
    Locations TABLE:
    Fields list:
    location_id not null number(4)
    street_address varchar2(40)
    postal_code varchar2(12)
    city not null varchar2(30)
    state_province varchar2(25)
    country_id char(2)


    TABLE
    Countries TABLE
    Field list:
    country_id not null char(2)
    country_name varchar2(40)
    region_id number


    So far, i have the query:

    select country_id, country_name from countries
    where country_id in
    (select country_id from countries
    minus (select country_id from locations))

    However, the result is not correct, since I have not linked in the departments table. The answer needs to include set operators.
    I'm stumped because the set operators seem to restrict the fields that can be generated based on the first set operator.
    The departments table does not contain the country name or country id (but needs to be linked in) & the final result can only include the fields country id, and country name.

    I can get the result through joins, but the question requires the use of set operators.

    Thanks

    Last edited by jennifer5555; 03-15-2009 at 06:30 PM. Reason: To make question and DB structures more readable

  2. #2
    Expert Member
    Join Date
    Nov 2008
    Answers
    300

    Re: Query question for an Oracle class

    Try this Query:

    Code:
    SELECT country_id,country_name FROm countries
    MINUS
    SELECT country_id,country_name FROM conutries c, locations l,departments d
    WHERE d.location_id = l.location_id
    AND l.country_id = c.country_id



  3. #3
    Junior Member
    Join Date
    Mar 2009
    Answers
    1

    Re: Query question for an Oracle class

    U can query this way also.

    select country_name from countries where country_id not in
    (select country_id from countries cou,locations loc,departments dept
    where cou.country_id = loc.country_id and loc.location_id = dept.location_id)

    But this query will degrade the performance. Hence using MINUS will be more advantage.


  4.  Sponsored Ads
    Ad


     

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