0
Employee(fname, minit, lname, SSN, bdate, address, sex, salary, superssn, dno); fname,minit,lname is the employee's name; bdate is birth date; superssn is supervisor's social security #; dno is dept #
Department(dname, DNUMBER, mgrssn, mgrstartdate); mgrssn is manager ssn
Dept_locations(DNUMBER, DLOCATION); dlocation is department location
Project(Pname, PNUMBER, plocation, dnum)
Works_on(ESSN, PNO, hours); ESSN is employee ssn, pno is project number
Dependent(ESSN, DEPENDENT_NAME, sex, bdate, relationship)

I want to list last and first name, SSN of all managers who is NOT working on project 7 and located in Detroit

This look kind right:

Select e.ssn, e.lname, e.fname
from employee e, department d
where (d.mgrssn = e.ssn)

where e.ssn NOT in (
        select w.essn
        from works_on w
        where w.pno = '07'
        and p.plocation = 'Detroit'
)

I think I need to put a pno = works_on joint statement before pno = 07, but someones told me I don't need it. So i'm really confused now.

Also do I need to include where (d.mgrssn = e.ssn) in a bracket or not?

William D
  • 3
  • 1
  • You don't want managers from Detroit, or you do want managers from Detroit? Because your not in excludes all managers in Detroit working on project 7. – Randall Nov 30 '15 at 17:33
  • I dont want manger who form detroit. can you please write an answer, I will accept it – William D Nov 30 '15 at 17:47

1 Answers1

0

Here is the query in long form with joins:

select e.ssn, e.lname, e.fname
from employee e
  join works_on wo on wo.ESSN = e.ssn
  join project PO on po.pnumber = wo.pno
  join dept_location dl on dl.dnumber = po.dnum
where dl.dlocation != 'Detroit'
  or po.pnumber != 7

Here is the same query with a not in, in case you want to avoid a large number of joins:

select e.ssn, e.lname, e.fname
from employee e
where e.ssn not in
(select wo.essn from works_on wo
  join dept_locations dl on dnumber = wo.pno
  where dl.dlocation = 'Detroit'
    or po.pnumber = 7)

Hope that helps.

Randall
  • 1,441
  • 13
  • 19
  • thank also sometimes I see when using select, people just put lname ssn fname, and no e. in front of it. Do I need those? – William D Nov 30 '15 at 18:01
  • Nope, I just left them in for clarity, since there is only one table in the first select you don't need the table alias in front of the column names since there is no ambiguity (ie: no chance of two columns having the same name in two different tables, since there is only one table). The subquery might still need them because there is a chance a column name is shared between tables. – Randall Nov 30 '15 at 18:04