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

Question

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)

我想找到在芝加哥从事 4 个或更多项目的未婚经理的名字、姓氏和 SSN。

这是我到目前为止所拥有的：

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'
                            );

1. 当连接 d.mgrssn = e.ssn 和其中 (w.pno = p.pnumber( 时，我是否需要括号
2. 我认为我的 e.ssn 不在条款中不正确？

无论如何，请随意修改我的命令

Answer 1

不需要

您的子查询。只需将其全部打包到一个简单的查询中即可。

您将

受益于调整 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