Home » SQL & PL/SQL » SQL & PL/SQL » Incorrect work of the sql query with JOIN. (Oracle, 11g, Win 7)
Incorrect work of the sql query with JOIN. [message #676513] |
Thu, 13 June 2019 09:37 |
|
orajav
Messages: 23 Registered: June 2019
|
Junior Member |
|
|
Hello!
The SQL query should print the name and surname of employees who, taking into account the commission, receive the maximum
for their position salary or more, their current position,
total salary with commission
and a list of positions where they can go with an increase in salary
indicating the minimum and maximum salary for this position.
Sort results by current salary with commissions,
the size of the commission, the attractiveness (size of the maximum salary) of the new position.
All sorting - from large to small. Values with Null output after the rest.
When moving to a new position, the salary is set at the minimum threshold for this position.
Moving to another position with an increase in salary means
that the minimum wage in a new position should be higher
than the current salary (excluding commission).
SELECT t1.FIRST_NAME as FIRST_NAME, t1.LAST_NAME as LAST_NAME,
t1.job_title as curr_job,
t1.salary + NVL(t1.COMMISSION_PCT,0) as curr_total_salary,
t2.job_title as new_job, t2.MIN_SALARY as min_sal_new_job,
t2.MAX_SALARY as max_sal_new_job
FROM (
SELECT e1.EMPLOYEE_ID, e1.FIRST_NAME, e1.LAST_NAME, j.job_title,
e1.salary, e1.COMMISSION_PCT
from employees e1 join jobs j
on(e1.job_id = j.job_id)
where (e1.salary + NVL(e1.COMMISSION_PCT,0)) >= j.MAX_SALARY) t1
JOIN (
SELECT e2.EMPLOYEE_ID,
j2.job_title, j2.MIN_SALARY,e2.salary, j2.MAX_SALARY
from employees e2 join jobs j2
on(e2.job_id = j2.job_id)
where j2.MIN_SALARY > e2.salary
) t2
on (t2.EMPLOYEE_ID = t1.EMPLOYEE_ID)
order by curr_total_salary, t1.COMMISSION_PCT, t2.MAX_SALARY desc nulls last;
|
|
|
|
|
|
|
Re: Incorrect work of the sql query with JOIN. [message #676519 is a reply to message #676516] |
Thu, 13 June 2019 11:17 |
|
orajav
Messages: 23 Registered: June 2019
|
Junior Member |
|
|
I corrected sql query, but still returns empty values:
SELECT t1.FIRST_NAME as FIRST_NAME, t1.LAST_NAME as LAST_NAME,
t1.job_title as curr_job,
(t1.salary + (t1.salary * NVL(t1.COMMISSION_PCT,0)/100)) as curr_total_salary,
t2.job_title as new_job, t2.MIN_SALARY as min_sal_new_job,
t2.MAX_SALARY as max_sal_new_job
FROM (
SELECT e1.EMPLOYEE_ID, e1.FIRST_NAME, e1.LAST_NAME, j.job_title,
e1.salary, e1.COMMISSION_PCT
from employees e1 join jobs j
on(e1.job_id = j.job_id)
where (e1.salary + (e1.salary * NVL(e1.COMMISSION_PCT,0)/100)) >= j.MAX_SALARY) t1
JOIN (
SELECT e2.EMPLOYEE_ID,
j2.job_title, j2.MIN_SALARY,e2.salary, j2.MAX_SALARY
from employees e2 join jobs j2
on(e2.job_id = j2.job_id)
where j2.MIN_SALARY > e2.salary
) t2
on (t2.EMPLOYEE_ID = t1.EMPLOYEE_ID)
order by curr_total_salary, t1.COMMISSION_PCT, t2.MAX_SALARY desc nulls last;
|
|
|
|
Re: Incorrect work of the sql query with JOIN. [message #676522 is a reply to message #676519] |
Thu, 13 June 2019 11:48 |
John Watson
Messages: 8937 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I still don't understand your salary and commission calculation. I have never heard of commission being a percentage of salary, it is always a percentage of sales. Should you not be joining to oe.orders to compute the commission per year based on the order_total values?
|
|
|
|
|
|
|
|
|
Re: Incorrect work of the sql query with JOIN. [message #676540 is a reply to message #676538] |
Fri, 14 June 2019 02:23 |
|
orajav
Messages: 23 Registered: June 2019
|
Junior Member |
|
|
Hello!
I changed the condition, but the result is empty. See screen
SELECT t1.FIRST_NAME as FIRST_NAME, t1.LAST_NAME as LAST_NAME,
t1.job_title as curr_job,
(t1.salary + (t1.salary * NVL(t1.COMMISSION_PCT,0)/100)) as curr_total_salary,
t2.job_title as new_job, t2.MIN_SALARY as min_sal_new_job,
t2.MAX_SALARY as max_sal_new_job
FROM (
SELECT e1.job_id, e1.EMPLOYEE_ID, e1.FIRST_NAME, e1.LAST_NAME, j.job_title,
e1.salary, e1.COMMISSION_PCT
from employees e1 join jobs j
on(e1.job_id = j.job_id)
where (e1.salary + (e1.salary * NVL(e1.COMMISSION_PCT,0)/100)) >= j.MAX_SALARY) t1
JOIN (
SELECT e2.job_id,e2.EMPLOYEE_ID,
j2.job_title, j2.MIN_SALARY,e2.salary, j2.MAX_SALARY
from employees e2 join jobs j2
on(e2.job_id = j2.job_id)
) t2
on (t2.EMPLOYEE_ID = t1.EMPLOYEE_ID)
where t1.job_id <> t2.job_id
order by curr_total_salary, t1.COMMISSION_PCT, t2.MAX_SALARY desc nulls last;
|
|
|
|
|
Re: Incorrect work of the sql query with JOIN. [message #676544 is a reply to message #676542] |
Fri, 14 June 2019 04:37 |
|
Michel Cadot
Messages: 68658 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> col first_name format a10
SQL> col last_name format a10
SQL> col job_title format a10
SQL> col "NEW JOB" format a30
SQL> with
2 selected_emp as (
3 -- The SQL query should print the name and surname of employees, their current position,
4 -- total salary with commission
5 select e.FIRST_NAME, e.LAST_NAME, e.SALARY*(1+nvl(e.COMMISSION_PCT/100,0)) total_sal,
6 e.SALARY, e.COMMISSION_PCT,
7 j.JOB_TITLE
8 from employees e, jobs j
9 where j.JOB_ID = e.JOB_ID
10 -- employees who, taking into account the commission,
11 -- receive the maximum for their position salary or more
12 and e.SALARY*(1+nvl(e.COMMISSION_PCT/100,0)) >= J.MAX_SALARY
13 )
14 select -- The SQL query should print the name and surname of employees, their current position,
15 -- total salary with commission...
16 e.FIRST_NAME, e.LAST_NAME, e.JOB_TITLE, e.total_sal,
17 -- ... a list of positions indicating the minimum and maximum salary for this position
18 j.JOB_TITLE "NEW JOB", j.MIN_SALARY, j.MAX_SALARY
19 from selected_emp e, jobs j
20 -- positions where they can go with an increase in salary
21 where j.MIN_SALARY > e.SALARY
22 -- Sort results by current salary with commissions,
23 -- the size of the commission, the attractiveness (size of the maximum salary) of the new position.
24 -- All sorting - from large to small. Values with Null output after the rest.
25 order by e.total_sal desc nulls last, e.COMMISSION_PCT desc nulls last, j.MAX_SALARY desc nulls last
26 /
FIRST_NAME LAST_NAME JOB_TITLE TOTAL_SAL NEW JOB MIN_SALARY MAX_SALARY
---------- ---------- ---------- ---------- ------------------------------ ---------- ----------
Daniel Faviet Accountant 9000 President 20080 40000
Daniel Faviet Accountant 9000 Administration Vice President 15000 30000
Daniel Faviet Accountant 9000 Sales Manager 10000 20080
3 rows selected.
|
|
|
|
Goto Forum:
Current Time: Sat Jun 15 16:07:10 CDT 2024
|