Path: utzoo!news-server.csri.toronto.edu!rutgers!att!pacbell.com!decwrl!fernwood!oracle!news
From: cyeung@uk.oracle.com (Charles Yeung)
Newsgroups: comp.databases
Subject: Re: Division in embedded sql
Keywords: Division, ORACLE
Message-ID: <1991Mar13.103654.21142@oracle.com>
Date: 13 Mar 91 10:36:54 GMT
References: <1991Mar4.211343.22513@cs.wayne.edu>
Sender: news@oracle.com
Organization: Oracle Corp.
Lines: 62

nis@artemis.cs.wayne.edu (Nitin Shah) writes:

>I want to implement division operation of relational algebra in sql
>embedded in 'c' on ORACLE.

>Let me clarify by an example.
>emp      
>    EmpNo    Project
>    1000     1
>    1000     2
>    1000     3
>    1001     1 
>    1002     1 
>    1002     2 
> 
>Project 
>    Pno     Pname  
>    1       a 
>    2       b 
>    3       c 
> 
> 
>query: 
>    Give EmpNo of employees who work on project `a` and `b`.
> 
>Answer: 
>    EmpNo 
>    1000 
>    1002 
> 
>Can anyone help?

>Thanks.

>My email address : nis@cs.wayne.edu

>Nitin

------------------------------------------------------------------------------


First of all, I hope your emp table does contain only project assignments 
information and not employee information. If not I think you better normalise
your data i.e have an emp table (for employee info) and a assignment table
(for project assignments info).

My solution to your question is :

    select empno from emp, project where emp.project = pno and project = 'a' 
    intersect 
    select empno from emp, project where emp.project = pno and project = 'b' ;


I hope this will help.


-------------------------------------------------------------------------------
Charles Yeung				     Internet : cyeung@uk.oracle.com
Oracle EDC, Oracle Park,                  Oracle*Mail : CYEUNG on UKPYR2
Bittams Lane, Guildford Road,                   Phone : 093287.2020 X 2167
Chertsey, Surrey  KT16 9RG                        FAX : 093287.3293
-------------------------------------------------------------------------------