-1
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.

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'
                            );
  1. do I need a parenthesis when join d.mgrssn = e.ssn and where (w.pno = p.pnumber)
  2. I think my e.ssn not in clause is not correct?

Anyways feel free to corect my commands

Lin Wei
  • 87
  • 1
  • 5
  • 1) If you're not sure if you need parens or not, add them. 2) Why do you think it's not correct? In what way is it not working? – Ken White Nov 29 '15 at 05:12
  • can you please write an answer, I promise I will accept answer. I'm just really confused – Lin Wei Nov 29 '15 at 05:32
  • @ken white can you please write an answer, I promise I will accept answer. I'm just really confused – Lin Wei Nov 29 '15 at 05:42

1 Answers1

0

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

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
fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • Maybe that the join to dependent should b left join. If the table may not contain any rows for the employee if he/she is'nt married. – Richard L Nov 29 '15 at 08:16
  • Thank can you please write an query using not in and in for the commands? I promise I will accept it. my instructor said the review on exam it must be like that way – Lin Wei Nov 29 '15 at 08:17
  • @Richard L Thank can you please write an query using not in and in for the commands? I promise I will accept it. my instructor said the review on exam it must be like that way – Lin Wei Nov 29 '15 at 08:18
  • Well, your instructor doesn't seem to have much clue about this. What's wrong with your query? – fancyPants Nov 29 '15 at 12:10