Puzzle of the Week #8: Find job titles represented in every department

Find job titles represented in every department. Write a single SELECT statement only.

Note: Use only emp table.

Expected Result: (Only clerks and managers work in all 3 departments: 10,20, and 30)

JOB
--------
CLERK
MANAGER

To submit your answer (one or more!) please start following this blog and add a comment to this post.

A correct answer (and workarounds!) will be published here in a week.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Advertisements

11 thoughts on “Puzzle of the Week #8: Find job titles represented in every department

  1. Ashwini Kumar April 18, 2016 / 8:19 am

    select job
    from emp e
    group by job
    having count(distinct deptno)=(select count(distinct deptno) from emp);

  2. Zohar Elkayam April 18, 2016 / 4:01 pm

    When using emp only once (single table scan)

    select distinct job
    from
        (
            select job,
                count (distinct deptno) over (partition by job) k,
                count (distinct deptno) over () j
            from scott.emp
        )
    where j = k
    

    or this (which is just a variation on the first query):

    select distinct job
    from
        (
            select job,
                count (distinct deptno) over (partition by job) j
            from scott.emp
        )
    where j =
        (
            select count (distinct deptno) k from scott.emp
        )
    

    for the non-analytic function – I think it was already posted…

    • Zahar Hilkevich April 19, 2016 / 7:06 am

      These are all good queries, and they all have one thing in common: COUNT(DISTINCT deptno) function (Analytic or Aggregate). I found very challenging to find a solution that does not use this function. Give it a try!

  3. sajith April 18, 2016 / 11:18 pm

    select ENAME,JOB
    ,COUNT(1) from emp where job in (‘CLERK’,’MANAGER’) AND DEPTNO IN (10,20,30)
    GROUP BY ENAME
    ,JOB
    HAVING COUNT(1)>2
    ORDER BY ENAME

  4. sajith April 18, 2016 / 11:32 pm

    select ename,
    JOB
    from emp GROUP BY ENAME,
    JOB
    HAVING COUNT(DISTINCT deptno)>2

    • Zahar Hilkevich April 21, 2016 / 6:45 am

      if there are 6 different departments in emp table, the query you proposed will not return correct result.

  5. sajith April 21, 2016 / 6:07 am

    select job
    from
    (
    select job
    , case when (job=’CLERK’ and deptno in (10,20,30)) then 1 else 0 end as clerk
    , case when (job=’MANAGER’ and deptno in (10,20,30)) then 1 else 0 end as manager
    from emp)
    group by job
    having sum(clerk)>=3 or sum(manager)>=3

    • Zahar Hilkevich April 21, 2016 / 6:44 am

      A solution cannot use any hard-coded values, such as job titles (‘CLERK’, ‘MANAGER’) or deptno (10,20,30). The query should work correctly for any content. The expected result is shown only for “predefined” content of the emp table in scott schema.

  6. Deepak Mahto April 21, 2016 / 11:20 am

    SELECT DISTINCT job
    FROM
    (SELECT job ,
    COUNT(DISTINCT deptno) over (partition BY job) cnt_dept,
    COUNT(DISTINCT job) over (partition BY deptno) cnt_job
    FROM SCOTT.emp
    )
    WHERE cnt_job = cnt_dept;

  7. Amarjot May 11, 2016 / 7:38 am

    Hi,

    By using cor-related sub query

    with tmp(x,y) as ( select 10, ‘amar342’ from dual union all
    select 10, ‘sumit’ from dual union all
    select 10, ‘sumit’ from dual union all
    select 20, ‘sumit’ from dual union all
    select 20, ‘sdf’ from dual union all
    select 20, ‘amar342’ from dual union all
    select 30, ‘sumit’ from dual union all
    select 30, ‘amar342’ from dual union all
    select 30, ‘amar342’ from dual
    )
    select distinct y from tmp a
    where (select count(distinct x) -1 from tmp)
    = ( select count(1) from
    (select x,y from tmp group by x,y) b
    where a.x != b.x and a.y = b.y
    )

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s