SQL查询命令使用和不处于clausing，有点复杂，在最后一步丢失

EMPLOYEE (fname, minit, lname, ssn, birthdate, address, sex, salary, superssn, dno)
KEY: ssn

DEPARTMENT (dname, dnumber, mgrssn, mgrstartdate)
KEY: dnumber.

PROJECT (pname, pnumber, plocation, dnum)
KEY: pnumber.

WORKS_ON (essn, pno, hours)
KEY: (essn, pno)

DEPENDENT (essn, dependent-name, sex, bdate, relationship)
KEY: (essn, dependent-name)

I want to find first and last name and SSN of the unmarried managers who work on 4 or more projects in chicago.

我想找到在芝加哥开展4个或更多项目的未婚经理的名字和姓氏以及SSN。

This is what I have so far:

这是我到目前为止:

SELECT e.lname, e.ssn
FROM  employee e, department d
wher (d.mgrssn = e.ssn)
and e.ssn in (
                          select  w.essn
                          from   works_on w, project p
                          where  (w.pno = p.pnumber)
                          and p.plocation = 'cleveland'
                          group by w.essn
                          having count(*) >= 4
                     )
      AND  e.ssn NOT  in ( 
                          select essn
                          from  dependent
                          where relationship = 'Spouse'
                            );

do I need a parenthesis when join d.mgrssn = e.ssn and where (w.pno = p.pnumber)

加入d.mgrssn = e.ssn和where(w.pno = p.pnumber)时是否需要括号?

I think my e.ssn not in clause is not correct?

我认为我的e.ssn不在条款中是不正确的?

Anyways feel free to corect my commands

无论如何,我可以*地修改我的命令

1 个解决方案

#1

There's no need for your subqueries. Just pack it all into one simple query.

您的子查询不需要。只需将其打包成一个简单的查询即可。

And you would benefit from adapting the join syntax that became standard in 1992 ;)

你会受益于改编1992年成为标准的连接语法;)

SELECT
e.ssn, e.lname
FROM
employee e
JOIN works_on w ON e.ssn = w.essn
JOIN dependent d ON e.ssn = d.essn
JOIN project p ON w.pno = p.number
WHERE
d.relationship != 'Spouse'
AND p.location = 'Cleveland'
GROUP BY e.ssn, e.lname
HAVING COUNT(*) >= 4

#1