nis@artemis.cs.wayne.edu (Nitin Shah) (03/05/91)
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
Nitinmmorris@oracle.com (Martin Morris) (03/06/91)
In article <1991Mar4.211343.22513@cs.wayne.edu> 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? > This join should work: SELECT DISTINCT e.empno FROM empno e, project p WHERE p.pname = 'a' OR p.pname = 'b' AND p.Pno = e.Project ORDER BY e.empno >Thanks. > no problem.. >My email address : nis@cs.wayne.edu > >Nitin ------------------------------------------------------------------------------ Martin Morris SELECT STANDARD_DISCLAIMER "Everybody is lost Oracle UK FROM LEGAL_SPEAKS; but me!" -- Indy
cheung@mathcs.emory.edu (Shun Yan Cheung) (03/06/91)
In article <1991Mar5.220941.25393@oracle.com> mmorris@oracle.UUCP (Martin Morris) writes: >In article <1991Mar4.211343.22513@cs.wayne.edu> nis@artemis.cs.wayne.edu (Nitin Shah) writes: >>I want to implement division operation of relational algebra in sql >>embedded in 'c' on ORACLE. >> >>query: >> Give EmpNo of employees who work on project `a` and `b`. This is not what you would call division. The division qurey would be: Give EmpNo who work on all projects that Emp x (1002) works on. 'This join should work: ' 'SELECT DISTINCT e.empno 'FROM empno e, ' project p 'WHERE p.pname = 'a' 'OR p.pname = 'b' 'AND p.Pno = e.Project 'ORDER BY e.empno This works for a given set of projects (i.e., if you know the set is {a, b}). It does not work in general when you don't know this set. The answer to the division question can be found on page 187 in `Fundamentals of Database Systems' by Elmasri and Navathe. -- Shun Yan Cheung | cheung@mathcs.emory.edu Internet Emory University | cheung@emory.bitnet BITNET Dept of Math and CS | Voice: (404) 727-3823 Atlanta, GA 30322 | Engineering: make it work. Research: make it work BETTER
hpp@cbnewsm.att.com (hariharan.p.panamanna) (03/06/91)
In article <1991Mar4.211343.22513@cs.wayne.edu> 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 > Try this: select empno from emp where pno = (select pno from project where pname = 'a' and pname = 'b') --Hari cbnewsm!att!com!hpp
hpp@cbnewsm.att.com (hariharan.p.panamanna) (03/06/91)
In the sub-query , it should be 'OR' instead of "AND". ie where pname = 'a' or pname = 'b' ^^ Sorry for the inconvenience. -- Hari cbnewsm!att!com!hpp
cheung@mathcs.emory.edu (Shun Yan Cheung) (03/07/91)
I wrote:
Division query:
Give EmpNo who work on all projects that Emp x (1002)
works on.
Example database:
emp
EmpNo Project
1000 1
1000 2
1000 3
1001 1
1002 1
1002 2
The answer to the division question can be found on page 187 in
`Fundamentals of Database Systems' by Elmasri and Navathe.
To which Robert replied:
>Robert Garvey Sybase, Inc
>robert@sybase.com 6475 Christie Ave
>{sun,lll-tis,pyramid,pacbell}!sybase!robert Emeryville, CA 94608-1010
>
>Could you please post the general solution you referred to? I know of
>one that requires another table containing the projects you're
>interested in, but I'm pretty sure it's not adherent to the ANSI SQL
>standard. Like others, I don't have easy access to the text.
Here goes some bandwidth:
SELECT A.EmpNo
FROM emp A
WHERE NOT EXISTS (SELECT *
FROM emp B
WHERE
B.Project IN (SELECT C.Project
FROM emp C
WHERE C.EmpNo = x (i.e., 1002))
AND
NOT EXISTS (SELECT *
FROM emp D
WHERE
D.EmpNo = A. EmpNo
AND D.Project = B.Project)
);
Note: this query will output x (1002) also, put in an additional
A.EmpNo <> x if you are a purist.
--
Shun Yan Cheung | cheung@mathcs.emory.edu Internet
Emory University | cheung@emory.bitnet BITNET
Dept of Math and CS | Voice: (404) 727-3823
Atlanta, GA 30322 | Engineering: make it work. Research: make it work BETTERcheung@mathcs.emory.edu (Shun Yan Cheung) (03/07/91)
In article <1991Mar4.211343.22513@cs.wayne.edu> 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 > >query: > Give EmpNo of employees who work on project `a` and `b`. > >Answer: > EmpNo > 1000 > 1002 That's not a division, a division operation is something like: Give EmpNo of employees who work on all projects tha employee 1002 works on. The following quering will do the trick. For explanation, see Elmasri and Navathe's book. This reference contains a similar query. I am posting the solution after receiving an e-mail request. Sorry for the waste of bandwidth :-). SELECT A.EmpNo FROM emp A WHERE NOT EXISTS (SELECT * FROM emp B WHERE B.Project IN (SELECT C.Project FROM emp C WHERE C.EmpNo = 1002) AND NOT EXISTS (SELECT * FROM emp D WHERE D.EmpNo = A. EmpNo AND D.Project = B.Project) ); -- Shun Yan Cheung | cheung@mathcs.emory.edu Internet Emory University | cheung@emory.bitnet BITNET Dept of Math and CS | Voice: (404) 727-3823 Atlanta, GA 30322 | Engineering: make it work. Research: make it work BETTER
cyeung@uk.oracle.com (Charles Yeung) (03/13/91)
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 -------------------------------------------------------------------------------
ubiquity@cs.utexas.edu (Richard Hoffman) (03/18/91)
In article <1991Mar4.211343.22513@cs.wayne.edu> nis@artemis.cs.wayne.edu (Nitin Shah) wrote: >>emp Project >> EmpNo Project Pno Pname >> 1000 1 1 a >> 1000 2 2 b >> 1000 3 3 c >> 1001 1 >> 1002 1 >> 1002 2 >> >>query: >> Give EmpNo of employees who work on project `a` and `b`. >> >>Answer: >> EmpNo >> 1000 >> 1002 In article <1991Mar5.220941.25393@oracle.com> mmorris@oracle.UUCP (Martin Morris) suggests the following: >SELECT DISTINCT e.empno >FROM empno e, > project p >WHERE p.pname = 'a' >OR p.pname = 'b' >AND p.Pno = e.Project >ORDER BY e.empno But this will give the EmpNo of employees who work on *either* a and b, not *both* a and b. For instance, 1001 would be included in the answer set, because he/she works on project a (but not b). A slightly tricky (because it doesn't really use division) approach which gets the right answer is: SELECT e.empno FROM empno e, project p WHERE p.Pno = e.Project AND p.pname IN ('a','b') GROUP BY e.empno HAVING count(*) = 2 I haven't actually tried this out, so I can't guarantee that it works, but you get the idea. This or something like it ought to work. Another possibility: SELECT e.empno FROM empno e WHERE e.empno IN (SELECT k.empno FROM empno k, project p WHERE p.Pno = k.Project AND p.pname = 'a') AND e.empno IN (SELECT k.empno FROM empno k, project p WHERE p.Pno = k.Project AND p.pname = 'b') Yet another possibility: SELECT e.empno FROM empno e, project p WHERE p.Pno = e.Project AND p.pname = 'a' INTERSECT SELECT e.empno FROM empno e, project p WHERE p.Pno = e.Project AND p.pname = 'b' I don't know whether Oracle has INTERSECT, but it's part of SQL2. Chris Date shows how to simulate the division operator using EXISTS (or maybe one of the quantified predicates) in several of his books, but I can never remember how to do it. -- Richard Hoffman IBM Personal Systems Database Development (512) 823-1822 1529 Ben Crenshaw Way Austin, TX 78746 "Life is a gamble at terrible odds; (512) 327-9232 if it were a bet you wouldn't take it" (Tom Stoppard)
cyeung@uk.oracle.com (Charles Yeung) (03/18/91)
ubiquity@cs.utexas.edu (Richard Hoffman) writes: >In article <1991Mar4.211343.22513@cs.wayne.edu> nis@artemis.cs.wayne.edu >(Nitin Shah) wrote: >>>emp Project >>> EmpNo Project Pno Pname >>> 1000 1 1 a >>> 1000 2 2 b >>> 1000 3 3 c >>> 1001 1 >>> 1002 1 >>> 1002 2 >>> >>>query: >>> Give EmpNo of employees who work on project `a` and `b`. >>> >>>Answer: >>> EmpNo >>> 1000 >>> 1002 > >In article <1991Mar5.220941.25393@oracle.com> mmorris@oracle.UUCP (Martin >Morris) suggests the following: >>SELECT DISTINCT e.empno >>FROM empno e, >> project p >>WHERE p.pname = 'a' >>OR p.pname = 'b' >>AND p.Pno = e.Project >>ORDER BY e.empno >But this will give the EmpNo of employees who work on *either* a and b, not >*both* a and b. For instance, 1001 would be included in the answer set, >because he/she works on project a (but not b). >A slightly tricky (because it doesn't really use division) approach >which gets the right answer is: > SELECT e.empno > FROM empno e, project p > WHERE p.Pno = e.Project AND p.pname IN ('a','b') > GROUP BY e.empno > HAVING count(*) = 2 >I haven't actually tried this out, so I can't guarantee that it works, >but you get the idea. This or something like it ought to work. >Another possibility: > SELECT e.empno > FROM empno e > WHERE e.empno IN (SELECT k.empno > FROM empno k, project p > WHERE p.Pno = k.Project AND p.pname = 'a') > AND e.empno IN (SELECT k.empno > FROM empno k, project p > WHERE p.Pno = k.Project AND p.pname = 'b') >Yet another possibility: > SELECT e.empno > FROM empno e, project p > WHERE p.Pno = e.Project AND p.pname = 'a' > INTERSECT > SELECT e.empno > FROM empno e, project p > WHERE p.Pno = e.Project AND p.pname = 'b' >I don't know whether Oracle has INTERSECT, but it's part of SQL2. >Chris Date shows how to simulate the division operator using EXISTS (or >maybe one of the quantified predicates) in several of his books, but I can >never remember how to do it. >-- >Richard Hoffman IBM Personal Systems Database Development (512) 823-1822 >1529 Ben Crenshaw Way >Austin, TX 78746 "Life is a gamble at terrible odds; >(512) 327-9232 if it were a bet you wouldn't take it" (Tom Stoppard) ------------------------------------------------------------------------------ YES ! Oracle has INTERSECT , MINUS and UNION. ------------------------------------------------------------------------------- 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, UK FAX : 093287.3293 -------------------------------------------------------------------------------