Re: Tricky SQL

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Wed, 22 Nov 2000 19:39:35 +0100
Message-ID: <974918593.22545.0.pluto.d4ee154e@news.demon.nl>

<pmcadam_at_my-deja.com> wrote in message news:8vh29h$1co$1_at_nnrp1.deja.com...
> OK all you SQL Gods out there. Can you answer this one.
>
> table employee:
> employeeid(PK), name, companyid(FK)
>
> table company:
> companyid(PK) , name
>
> table department:
> departmentid(PK) , name, companyid(FK)
>
> · Write a query to list all the department names from all
> companies that (the companies) have more than 25 employees and that
> (the company) have a name that starts with an 'A'.
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

select departmentid, d.name, c.companyid, c.name from department d, company c
where c.companyid
in (select companyid

     from employee
     group by companyid
     having count(*) > 25

)

Easy!

Regards,

Sybrand Bakker, Oracle DBA Received on Wed Nov 22 2000 - 12:39:35 CST