OraFAQ Forum: Training & Certification » Query to find common job in all departments.

Home » Other » Training & Certification » Query to find common job in all departments. (Oracle 9i, Win 2000 Professional)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query to find common job in all departments. [message #312111]

Tue, 08 April 2008 00:41

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.

Report message to a moderator

Re: Query to find common job in all departments. [message #312113 is a reply to message #312111]

Tue, 08 April 2008 00:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I want query to find common JOB in all departments.
Proceed to do so.

Report message to a moderator

Re: Query to find common job in all departments. [message #312126 is a reply to message #312111]

Tue, 08 April 2008 01:07

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Query to find common job in all departments. [message #312129 is a reply to message #312126]

Tue, 08 April 2008 01:14

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.

Report message to a moderator

Re: Query to find common job in all departments. [message #312136 is a reply to message #312129]

Tue, 08 April 2008 01:25

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Formatting a post is explained in OraFAQ Forum Guide, "How to format your post?" section.

Regards
Michel

Report message to a moderator

Re: Query to find common job in all departments. [message #312586 is a reply to message #312136]

Wed, 09 April 2008 06:28

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.

Report message to a moderator

Re: Query to find common job in all departments. [message #312598 is a reply to message #312586]

Wed, 09 April 2008 07:02

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Query to find common job in all departments. [message #312604 is a reply to message #312598]

Wed, 09 April 2008 07:24

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.

Report message to a moderator

Re: Query to find common job in all departments. [message #312607 is a reply to message #312604]

Wed, 09 April 2008 07:29

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

sheryl_sharma
Messages: 14
Registered: November 2007

Junior Member

Can you please provide me the query, to do it?

Report message to a moderator

Re: Query to find common job in all departments. [message #312863 is a reply to message #312850]

Wed, 09 April 2008 23:50

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.

Report message to a moderator

Re: Query to find common job in all departments. [message #312891 is a reply to message #312863]

Thu, 10 April 2008 01:14

sheryl_sharma
Messages: 14
Registered: November 2007

Junior Member

I tried doing it many times, thats why I have asked.

Report message to a moderator

Re: Query to find common job in all departments. [message #312893 is a reply to message #312850]

Thu, 10 April 2008 01:19

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Query to find common job in all departments. [message #312904 is a reply to message #312111]

Thu, 10 April 2008 01:37

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

Report message to a moderator

Re: Query to find common job in all departments. [message #312906 is a reply to message #312904]

Thu, 10 April 2008 01:41

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	Log switch query
Next Topic:	some answers please

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 14:55:46 CST 2024