Solved

ORACLE SQL Query - multiple tables, joins

Posted on 2013-11-22
22
452 Views
Last Modified: 2013-12-04
Hi Experts,

I need help with a query.

Table 1:  Employees
Empl_id
last_name
Mgr_id
Salary
Dept_id

Table 2:  Departments
Dept_id
Dept_name
Mgr_id

The goal is to create a list of department names, the manager id, last name of mgr, and average salary for each department.

I know I have to join the empl and dept tables to each other and then somehow join the employees table to itself.

I can get the proper average as long as I don't add the mgr_id, then the groups break and its a mess............................

This is for a class so any help would be appreciated.  As part of the solution, what I want to do is to understand the order of the joins and how that makes a difference in the outcome.

Thank you,

S.....
0
Comment
Question by:svillardi
  • 13
  • 7
  • 2
22 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39670475
This is a classic homework assignment....
Show us what you got done?
:p
0
 

Author Comment

by:svillardi
ID: 39670483
You asked for it... I've struggled with it for hours...  remember, these are all works in progress.  I am trying to take this apart piece by piece...

SELECT d.department_name, m.manager_id, m.last_name, TRUNC(AVG(m.salary),0)
FROM departments d JOIN employees m
ON d.department_id = m.department_id
JOIN employees e
ON (m.employee_id = e.manager_id)
GROUP BY d.department_name, m.manager_id, m.last_name



SELECT d.department_name, w.last_name AS "Manager", AVG(w.salary)
FROM employees w JOIN employees m
ON (w.manager_id = m.employee_id)
JOIN departments d
on (m.department_id = d.department_id)
GROUP BY d.department_name, w.last_name

This one (because it's incomplete, of course... is the only one which maintains the correct average salary)

SELECT d.department_name, AVG(m.salary)
FROM employees m join departments d
ON (m.department_id = d.department_id)
GROUP BY d.department_name
0
 

Author Comment

by:svillardi
ID: 39670487
Now, I am just cutting and pasting here........so I am duplicating work, I'm sure....

SELECT d.department_name, m.manager_id, m.employee_id TRUNC(AVG(e.salary),0)
FROM department d JOIN employees e
ON d.department_id = e.department_id
JOIN employees m
ON (e.employee_id = m.manager_id)
GROUP BY d.department_name, m.manager_id, m.employee_id

select *
from departments

SELECT d.department_name, e.manager_id, e.salary
FROM employees e JOIN employees m
ON (e.manager_id = m.employee_id) JOIN
departments d
ON (e.department_id = d.department_id)
0
 

Author Comment

by:svillardi
ID: 39670495
Now, here's one I figured out that was a little simpler, but it has a key component -- the employees table is joined to itself....I don't quite fully understand it, but I know that it allows one field to equal another......  how, I don't get that part... LOL  So I tried to use the same approach in my other problem.

SELECT w.last_name AS "Employee", w.hire_date AS "Emp Hired", m.last_name AS "Manager", m.hire_date AS "Mgr Hired"
FROM employees w LEFT OUTER JOIN employees m
ON (w.manager_id = m.employee_id)
WHERE w.hire_date < m.hire_date

THANKS!!
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39670541
You are on the right track, is this what you need?:
SELECT d.department_name AS "Department"
     , m.last_name AS "Manager"
     , m.hire_date AS "Mgr Hired"
     , AVG ( w.salary) "Avg Salary"
  FROM departments s
       LEFT OUTER JOIN employees m ON (m.employee_id = d.manager_id)
       LEFT OUTER JOIN employees w ON (w.manager_id = m.employee_id)
 WHERE w.employee_id != m.employee_id
GROUP BY d.department_name, m.last_name, m.hire_date;

Open in new window

PS: To format your code, select it and click on "code" in the upper menu.
0
 

Author Comment

by:svillardi
ID: 39670556
No.  This isn't it....lol  But I am trying to understand the affects of moving things around.  Can you help me to build it rather than building it for me?

It must have to do with the order of the SELECT fields and the joins, but I can't figure it out.  Partly because the totals of the average salary keep changing....

Columns:  Department Name, Manager_ID, Mgr Last Name, Avg_Dept_Salary

I need to understand the "rules" of putting it together.

Thanks for your help!!!
0
 

Accepted Solution

by:
svillardi earned 0 total points
ID: 39670620
I GOT IT!!!!

SELECT d.department_name, d.manager_id, m.last_name,  TRUNC(AVG(e.SALARY))
FROM employees e 
JOIN departments d ON e.department_id = d.department_id
JOIN employees m ON d.manager_id = m.employee_id
GROUP BY d.department_name, d.manager_id, m.last_name:

Open in new window


But I still don't really get it....
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 500 total points
ID: 39671383
Maybe if we explain why the others didn't work that will help you understand.

SELECT d.department_name, m.manager_id, m.last_name, TRUNC(AVG(m.salary),0)
FROM departments d JOIN employees m
ON d.department_id = m.department_id 
JOIN employees e
ON (m.employee_id = e.manager_id)
GROUP BY d.department_name, m.manager_id, m.last_name

Open in new window


This does not work because you are not displaying the manager's last name, you are displaying the employee's last name.  The manager's last name is in the table you have aliased as e.  The TRUNC is not necessary.  You should be seeing records with the manager's id but the employees last name.

SELECT d.department_name, w.last_name AS "Manager", AVG(w.salary) 
FROM employees w JOIN employees m
ON (w.manager_id = m.employee_id)
JOIN departments d
on (m.department_id = d.department_id)
GROUP BY d.department_name, w.last_name

Open in new window


This does not work because you are averaging the manager's salary mulitple times.  The salary you are seeing should be the managers salary.

The other queries you have listed are just parts of these queries.

Your final query that works, really doesn't need the TRUNC on the AVG.

The order of the joins does not matter as long as all the joins are correct.  The optimizer will determine it's own order in which to access the tables.
0
 

Author Comment

by:svillardi
ID: 39671525
Some more confusing parts--  
Am I joining A to B to C, or,  A to B and A to C on my answer?  Also, if I have 2 FK does it matter which one I choose to join it on?  If so why?  I think understanding how the join works is what's really frustrating me.

Also my correct answer doesn't match the assignment 100%.  There are two rows (out of which are reversed, and I don't think there is anything I can do about it.  There are 6 different combinations of GROUP BY, and only the one I wrote comes close.   I don't even understand what criteria the GROUP BY uses to determine the order.

The bold and italicized rows are reversed in the assignment:

DEPARTMENT_NAME MANAGER_ID MANAGER_NAME AVG_DEPT_SALARY
Administration 200 Whalen 4400
IT 103 Hunold 6400
Marketing 201 Hartstein 9500
Shipping 124 Mourgos 3500
Accounting 205 Higgins 10150
Executive 100 King 19333

Sales 149 Zlotkey 10033
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39671989
If you are interested in what order your tables are joined, you need to look at the explain plan.  There are numerous articles here on how to generate the plan.

A GROUP BY does not guarantee any type of order.  If  you are looking for a specific order then you must include a ORDER BY clause.
0
 

Author Comment

by:svillardi
ID: 39672140
I think the problem is in the joins.  I've tried all combinations, and this one is the only one which is close:

SELECT d.department_name, d.manager_id, m.last_name AS "MANAGER_NAME",  ROUND(AVG(e.SALARY))AS "AVG_DEPT_SALARY"
FROM employees e 
JOIN departments d ON e.department_id = d.department_id
JOIN employees m ON d.manager_id = m.employee_id
GROUP BY d.department_name, d.manager_id, m.last_name;

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 500 total points
ID: 39672583
This should work just the same:
SELECT d.department_name, d.manager_id, m.last_name AS "MANAGER_NAME",  ROUND(AVG(e.SALARY))AS "AVG_DEPT_SALARY"
FROM departments d 
JOIN employees e ON e.department_id = d.department_id
JOIN employees m ON d.manager_id = m.employee_id
GROUP BY d.department_name, d.manager_id, m.last_name;

Open in new window

As should this:
SELECT d.department_name, d.manager_id, m.last_name AS "MANAGER_NAME",  ROUND(AVG(e.SALARY))AS "AVG_DEPT_SALARY"
FROM employees m
JOIN departments d on m.employee_id = d.manager_id
JOIN employees e on d.department_id = e.department_id
GROUP BY d.department_name, d.manager_id, m.last_name;

Open in new window

The order of the joins makes no difference as long as the joins are all correct.
0
 

Author Comment

by:svillardi
ID: 39672634
OK, thanks.  So why would the results be SLIGHTLY different?  I have 2 rows reversed out of 7.

I can't change any of the SELECT of GROUP BY because I'm tied into those columns.

I can only think that the assignment results are wrong.
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 500 total points
ID: 39672890
The order of the results can ONLY be controlled by an ORDER BY clause.  That is the only way.  There is no guaranteed order otherwise.  If you ran the same query 2 times in a row, you will get the same set of rows returned, but they could be in an entirely different order.

The results of those queries are not different.  They are the same.  They are in a different order, but it is the same set of results.
0
 

Author Comment

by:svillardi
ID: 39673443
So, are you saying that the order is CORRECT?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39674291
I am saying that there is no order.
0
 

Author Comment

by:svillardi
ID: 39674310
OK, we're going back and forth a bit.  I get your point, that the only way to control order is with an ORDER BY clause.

The problem is that the result listed as the answer in the problem is different than the result l came up with -- on 2 rows.  They have the same information, but not the same order.  All other rows are where they are supposed to be.

How is that possible?

All other problems have the EXACT same result.  We are being graded on that.
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 500 total points
ID: 39674324
Differences in the plan, differences in buffering, differences in caching, differences in the way the rows were inserted into the table, differences in block order on disk, etc.  There are many things that will affect order that you have absolutely no control over.

If you need the rows in a specific order, then you need to determine what column(s) determined the order of the results you are trying to match and add an ORDER BY to your query to match that ordering.
0
 

Author Comment

by:svillardi
ID: 39674353
Which means that the query is correct.  Right?  (Please say yes... LOL)
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39674375
I did say yes.  3 posts ago.

See post 39672890

The results of those queries are not different.  They are the same.  They are in a different order, but it is the same set of results.
0
 

Author Comment

by:svillardi
ID: 39674470
I am sorry but I didn't understand that the post was saying that the answer was correct. Only that all three queries produced the same results.   Much thanks for your patience and discussion.
0
 

Author Closing Comment

by:svillardi
ID: 39695003
I found the solution by process of elimination.  The others here helped me to understand the solution I came up with.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now