0
EMPLOYEE (fmane, 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 get first and last names and ssn of each female employees with no dependents, but I get stuck at the final command part:

select e.lname, e.fname, e.ssn
from employee e
where e.ssn  not in (
select essn
from dependent
)

how do I add the gender part?

Jepessen
  • 11,744
  • 14
  • 82
  • 149
Steve mccain
  • 5
  • 3
  • `where e.ssn not in ( select essn from dependent ) and e.sex = 'Female'?` – potashin Nov 28 '15 at 22:13
  • this looks related to you question http://stackoverflow.com/q/33975601/3664960 is it some kind of exam you guys are making? – davejal Nov 28 '15 at 22:20

1 Answers1

0

In SQL, you can combine multiple filters by using the AND keyword.

select 
   e.lname, e.fname, e.ssn
from employee e
where e.ssn  not in (
                      select essn
                        from dependent
                     )
    AND e.sex = 'Female'
Andrew Shepherd
  • 44,254
  • 30
  • 139
  • 205
  • Thanks! can I put and e.sex before the where e.ssn not in clause? – Steve mccain Nov 28 '15 at 22:26
  • @Stevemccain: You can put the clauses in any order. It will not affect the speed of the query - the database should have a query optimizer that determines how best to apply the filters. – Andrew Shepherd Nov 28 '15 at 23:56
  • Thanks! and lastly if I just want the last and first name, not ssn, I know I must the e.ssn from select, but what about the part where e.ssn not in (......; do I change e.ssn to just ssn? – Steve mccain Nov 29 '15 at 00:48
  • I've seen in some clause people just use ssn not in or ssn exist, no e. in front of it – Steve mccain Nov 29 '15 at 00:49