Re: ORACLE Query

From: Sean McMurray <smcmurr_at_admin.dce.utah.edu>
Date: Thu, 29 Apr 1999 14:14:03 -0700
Message-ID: <3728CB9B.5FFB6C6D@admin.dce.utah.edu>

nayakviv_at_my-dejanews.com wrote:

> Hi ,
>
> I have to create a table of person skills. This table would have all the
> skills associated with a person.
>
> columns in the table would be
> person_name
> skill_name
> skill_level
>
> e.g of entries would be
> mike, oracle , 7
> mike, unix , 5
> mike, c, 8
> john , oracle , 5
> john , unix , 8
> john , java , 7
> john , C++, 9
>
> The feature that is to be implemented is that i want to query this table for
> all persons with a specified skill set e.g 1. oracle and unix and C 2.
> oracle and unix and ( java or C++ )
>
> and so on .
>
> What is the best way to write the query . Is it possible and if not , how to
> design the table .
>
> Regards
> Vivek

create table people(

    name varchar2(64),
    job ...,

    .
    .
    .,

    CONSTRAINT pk_people PRIMARY KEY(name) );

create table skills(

    skill varchar2(8),
    description varchar2(256),
    CONSTRAINT pk_skill PRIMARY KEY (skill) );

create table personal_skills(

    name varchar2(64),
    skill varchar2(8),
    level number,
    CONSTRAINT pk_personal_skills PRIMARY KEY (name, skill),     CONSTAINT fk_personal_person FOREIGN KEY (name)

        REFERENCES people (name),
    CONSTRAINT fk_personal_skill FOREIGN KEY (skill)

        REFERENCES skills(skill)
);

select name

    from personal skills
    where skill='ORACLE'
INTERSECT
select name

    from personal skills
    where skill='UNIX'
INTERSECT
select name

    from personal skills
    where skill='C';

select name

    from personal skills
    where skill='ORACLE'
INTERSECT
select name

    from personal skills
    where skill='UNIX'
INTERSECT
select name

    from personal skills
    where skill='JAVA' or skill='C++'; Received on Thu Apr 29 1999 - 16:14:03 CDT