I am new to using SQL and DBMS in general, and I keep getting this query wrong.

Question

I am new to using SQL and DBMS in general, and I keep getting this query wrong. I am supposed to list all employee's first and last names who work on all projects which the employee with the ssn=123456789 works on.

The relevant information about the tables is as follows:
EMP(fname,lname,minit,ssn,bdate,address,sex,salary,super_ssn,dno)
DEPARTMENT(dname,dnumber,mgr_ssn,mgr_start_date)
DEP_LOCATION(dnumber,dlocation)
WORK_ON(essn,pno,hours)
PROJECT(pname,pnumber,plocation,dnum)

I got fairly close with this query, but it returned one employee who did not work on the same project in addition to those who did.

SELECT fname,lname FROM EMP WHERE DNO=
(SELECT DNO FROM EMP WHERE ssn='123456789');

Any help getting a solution to this query would be appreciated.

Note:I also need to find the first and last names of employees who do not work on any project which the employee with the ssn=123456789 works on.

Explanation / Answer

SELECT fname,lname FROM EMP WHERE DNO in (SELECT DNO FROM EMP WHERE ssn='123456789') and dno in (SELECT DNO FROM PROJECT); SELECT fname,lname FROM EMP WHERE DNO in (SELECT DNO FROM EMP WHERE ssn='123456789') and dno not in (SELECT DNO FROM PROJECT);;

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.