Home » Other » Training & Certification » Query to find common job in all departments. (Oracle 9i, Win 2000 Professional)
Query to find common job in all departments. [message #312111] Tue, 08 April 2008 00:41 Go to next message
sheryl_sharma
Messages: 14
Registered: November 2007
Junior Member
I have a table called emp.
Columns are:
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO.

I want query to find common JOB in all departments.
Re: Query to find common job in all departments. [message #312113 is a reply to message #312111] Tue, 08 April 2008 00:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I want query to find common JOB in all departments.
Proceed to do so.
Re: Query to find common job in all departments. [message #312126 is a reply to message #312111] Tue, 08 April 2008 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should know that you have to first post what you tried and why you can't achieve the goal.

Don't forget to format your post when you'll reply.

Regards
Michel
Re: Query to find common job in all departments. [message #312129 is a reply to message #312126] Tue, 08 April 2008 01:14 Go to previous messageGo to next message
sheryl_sharma
Messages: 14
Registered: November 2007
Junior Member
Sorry

I tried following query

Select distinct e1.job from emp e1,emp e2
where e1.job=e2.job
and e1.deptno < e2.deptno.

The data is

EMPNO JOB DEPTNO
-------- --------- ----------
7369 CLERK 40
7499 SALESMAN 30
7521 SALESMAN 30
7566 MANAGER 20
7654 SALESMAN 30
7698 MANAGER 30
7782 MANAGER 10
7788 ANALYST 20
7839 PRESIDENT 10
7844 SALESMAN 30
7876 CLERK 20

EMPNO JOB DEPTNO
-------- --------- ----------
7900 CLERK 30
7902 ANALYST 20
7934 CLERK 10


Using above query the answer is

MANAGER
CLERK

I should get :-

CLERK as an output.
Re: Query to find common job in all departments. [message #312136 is a reply to message #312129] Tue, 08 April 2008 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Formatting a post is explained in OraFAQ Forum Guide, "How to format your post?" section.

Regards
Michel
Re: Query to find common job in all departments. [message #312586 is a reply to message #312136] Wed, 09 April 2008 06:28 Go to previous messageGo to next message
sheryl_sharma
Messages: 14
Registered: November 2007
Junior Member
I tried following query
SELECT DISTINCT e1.job from emp e1,emp e2 
WHERE e1.job=e2.job
AND e1.deptno < e2.deptno


The data is

EMPNO JOB DEPTNO
-------- --------- ----------
7369 CLERK 40
7499 SALESMAN 30
7521 SALESMAN 30
7566 MANAGER 20
7654 SALESMAN 30
7698 MANAGER 30
7782 MANAGER 10
7788 ANALYST 20
7839 PRESIDENT 10
7844 SALESMAN 30
7876 CLERK 20
7900 CLERK 30
7902 ANALYST 20
7934 CLERK 10


Using above query the answer is

MANAGER
CLERK

I should get :-

CLERK as an output as it is the common job in all department.



Re: Query to find common job in all departments. [message #312598 is a reply to message #312586] Wed, 09 April 2008 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand the logic of your query.
If you want the jobs that are in all departments then count all jobs in each department (aggregate count), count all jobs (analytic count) when they are equal you find a department.

Regards
Michel
Re: Query to find common job in all departments. [message #312604 is a reply to message #312598] Wed, 09 April 2008 07:24 Go to previous messageGo to next message
sheryl_sharma
Messages: 14
Registered: November 2007
Junior Member
I dont want deptno in output. It should show Job Title like 'CLERK' in output, which is available in all departments.

Re: Query to find common job in all departments. [message #312607 is a reply to message #312604] Wed, 09 April 2008 07:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Replace deptno by job and job by department in my sentence.

It was just an example to do it maybe too complex.
You can do it using HAVING clause.

Regards
Michel

[Updated on: Wed, 09 April 2008 07:35]

Report message to a moderator

Re: Query to find common job in all departments. [message #312850 is a reply to message #312607] Wed, 09 April 2008 22:39 Go to previous messageGo to next message
sheryl_sharma
Messages: 14
Registered: November 2007
Junior Member
Can you please provide me the query, to do it?
Re: Query to find common job in all departments. [message #312863 is a reply to message #312850] Wed, 09 April 2008 23:50 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Why do you want us to do your homework? If we do, it will only resolve in you asking us to solve the next assignment, because you fall behind in knowledge for not solving this one yourself.
Re: Query to find common job in all departments. [message #312891 is a reply to message #312863] Thu, 10 April 2008 01:14 Go to previous messageGo to next message
sheryl_sharma
Messages: 14
Registered: November 2007
Junior Member
I tried doing it many times, thats why I have asked.
Re: Query to find common job in all departments. [message #312893 is a reply to message #312850] Thu, 10 April 2008 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Let's do step by step.
1/ How do you get the list of (job, deptno)?
2/ Then how do you get the number of deptno per job
3/ Then how do you get the number of deptno
4/ Then how can you restrict the result of 2 when the number is the same than the one gotten in 3.

Regards
Michel
Re: Query to find common job in all departments. [message #312904 is a reply to message #312111] Thu, 10 April 2008 01:37 Go to previous messageGo to next message
sheryl_sharma
Messages: 14
Registered: November 2007
Junior Member
Oh thank you very much I got the answer:

SELECT DISTINCT e1.job FROM emp e1,(SELECT COUNT(DISTINCT deptno) deptjob,job FROM emp a GROUP BY JOB) e2,
(SELECT COUNT(DISTINCT deptno) dept FROM emp b) e3
WHERE e2.deptjob=e3.dept
AND e1.job=e2.job

Re: Query to find common job in all departments. [message #312906 is a reply to message #312904] Thu, 10 April 2008 01:41 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's another one:
SQL> select job
  2  from ( select distinct job, deptno from emp )
  3  group by job
  4  having count(*) = (select count(distinct deptno) from emp)
  5  order by job
  6  /
JOB
---------
CLERK
MANAGER

2 rows selected.

Regards
Michel
Previous Topic: Log switch query
Next Topic: some answers please
Goto Forum:
  


Current Time: Thu Mar 28 05:07:25 CDT 2024