CODEWITHSUNDEEP
sqlgroup-by
Lin Wei
Lin Wei

Reputation: 87

transfering managers commands into employees, using SQL commands groupby and having

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 last name of employees who work on 3 or more projects, use group by and having in a correlated subquery.

I've done a similar problem in finding manager before but now I want to find employee, I tried to transform the code, here is what I have:

anything I should change?

SELECT Lname
FROM   Employee       e,
       Department     d
WHERE  (e.ssn = d.mgrssn)
       AND e.ssn IN (SELECT w.essn
                     FROM   works_on     w,
                            Project      p
                     WHERE  w.pno = p.pnumber
                     GROUP BY
                            w.essn
                     HAVING COUNT(*) >= 2)

Upvotes: 1

Views: 56

Answers (1)

PK20
PK20

Reputation: 1066

You can change your query as follows.

select Lname
  from Employee e
where e.ssn in (
                  select  w.essn
                   from   works_on w, Project p
                  where w.pno = p.pnumber
                  group by w.essn
                  having count(*) >= 3
                 )

Upvotes: 1

Related Questions