query help

Hi,

I wasn't sure where to post this exactly, but I'm currently a student trying to learn SQL and need a little help with some queries using Oracle SQL.

1. How do you write a query to display, for example, jobs that are common to department 10, 20, and 30?

2. How you write a query to show a department #, department name, and # of employees that employee less 6 employees and then to display those with the max number of employees?

I know that subqueries have to be performed in order to return the correct results. I would appreciate any tips you might have, and any info that would help me understand this better.

Thank you.

Im new(ish) to SQL, but...

for point 1 theres no need to have a subquery, i think it will just read...



For the second i dont really understand what you are asking for?

Hope this helps....students should help students

Hi,
For ur first question try out this:

select job from temp
where job in ( select job from temp where job in
(select job from temp where dept=10)
and dept=20)
and dept=30;


Hope this helps... Good Luck

Dude whats that query, i dont understand at all

Thanks for the tip, however, that query doesn't return what I'm looking for. The query above gives me a list of all the job names in Departments 10, 20, and 30. I need to run a query that returns the common job names out of all those departments.

Hi,

Consider a table temp with a structure:
dept number,
job varchar2(25)

We're in need of job common to the depts 10,20 & 30.

The innermost query:
select job from temp where dept=10
-returns all the jobs in dept 10.

The next query:
select job from temp where job in
(select job from temp where dept=10)
and dept=20
-returns only those jobs that are found in dept 20 also.

The final returns those jobs that are found in dept 30 also.Thus we arrive at the result set.
I'm new to SQL so there might be some other easy way also.Not sure about it.This query worked in my system.Would like to know whether it is correct???