r******d
发帖数: 1 | 1 Have the tables:
employee(fname, lname, ssn)
works(essn,projectno,hours)
how to write the following query in relational calculus
"retrive the names of employees who work on all the project
that 'john smith' works on"?
Thx! | p*****e
发帖数: 58 | 2 if you want to get all employees who work on ANY project
that john smith works on then the SQL is:
select e.ess, e.fname, e.lname, wk.projectno, wk.hours
from employee e
inner join works wk on e.ess = wk.essn
inner join
(select distinct w.projectno
from works w inner join employee ref on ref.ess=w.essn
where ref.lname='Smith' and ref.fname='John')
p
on p.projectno = wk.projectno
【在 r******d 的大作中提到】
: Have the tables:
: employee(fname, lname, ssn)
: works(essn,projectno,hours)
: how to write the following query in relational calculus
: "retrive the names of employees who work on all the project
: that 'john smith' works on"?
: Thx!
| p*****e
发帖数: 58 | 3
If you want to get all employees who work on ANY (not ALL)
projects
that john smith works on, then the SQL is:
There are also other ways to write the SQL. Here is another
one:
Select e1.*, w1.*
from employee e1 inner join works w1 on e1.ssn=w1.essn
inner join works w2 on w1.projectno=w2.projectno
inner join employee e2 on w2.essn=e2.ssn
where e2.fname='john' and e2.lname='smith'
【在 p*****e 的大作中提到】
: if you want to get all employees who work on ANY project
: that john smith works on then the SQL is:
: select e.ess, e.fname, e.lname, wk.projectno, wk.hours
: from employee e
: inner join works wk on e.ess = wk.essn
: inner join
: (select distinct w.projectno
: from works w inner join employee ref on ref.ess=w.essn
: where ref.lname='Smith' and ref.fname='John')
: p
| p****s
发帖数: 3184 | 4
Relational calculus has two major categories:
tuple relational calculus and domain relational calculus,
First-order logic expression must be used and the BBS doesn't support
the math notations.
Even though SQL follows tuple relational calculus, SQL is NOT relational
calculus. When this guy submit your answer as his homework solution,
TA may give him 0 point. :)
【在 p*****e 的大作中提到】
: if you want to get all employees who work on ANY project
: that john smith works on then the SQL is:
: select e.ess, e.fname, e.lname, wk.projectno, wk.hours
: from employee e
: inner join works wk on e.ess = wk.essn
: inner join
: (select distinct w.projectno
: from works w inner join employee ref on ref.ess=w.essn
: where ref.lname='Smith' and ref.fname='John')
: p
|
|
