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 Go to next message
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 #676514 is a reply to message #676513] Thu, 13 June 2019 09:40 Go to previous messageGo to next message
John Watson
Messages: 7953
Registered: January 2010
Location: Global Village
Senior Member
Are you sure that you can add SALARY to COMMISSION_PCT and get a meaningful result?
Re: Incorrect work of the sql query with JOIN. [message #676515 is a reply to message #676514] Thu, 13 June 2019 09:58 Go to previous messageGo to next message
orajav
Messages: 23
Registered: June 2019
Junior Member
Yes. The following query returns 1 record:
 SELECT e1.EMPLOYEE_ID, e1.FIRST_NAME, e1.LAST_NAME, j.job_title,
 e1.salary + NVL(e1.COMMISSION_PCT,0),j.MAX_SALARY
  from employees e1  join jobs j
  on(e1.job_id = j.job_id)
 where (e1.salary + NVL(e1.COMMISSION_PCT,0)) >= j.MAX_SALARY
Re: Incorrect work of the sql query with JOIN. [message #676516 is a reply to message #676515] Thu, 13 June 2019 10:36 Go to previous messageGo to next message
BlackSwan
Messages: 26564
Registered: January 2009
Location: SoCal
Senior Member
orajav wrote on Thu, 13 June 2019 07:58
Yes. The following query returns 1 record:
 SELECT e1.EMPLOYEE_ID, e1.FIRST_NAME, e1.LAST_NAME, j.job_title,
 e1.salary + NVL(e1.COMMISSION_PCT,0),j.MAX_SALARY
  from employees e1  join jobs j
  on(e1.job_id = j.job_id)
 where (e1.salary + NVL(e1.COMMISSION_PCT,0)) >= j.MAX_SALARY
The fact that SELECT above returns 1 row in and of itself does NOT necessitate the returned row is CORRECT.
Assume SALARY=500 & COMMISSION_PCT=10; therefore combined total = "510" (which is incorrect value)
IMO the correct sum is 500 + 0.10*500 = 550
Re: Incorrect work of the sql query with JOIN. [message #676518 is a reply to message #676516] Thu, 13 June 2019 10:52 Go to previous messageGo to next message
orajav
Messages: 23
Registered: June 2019
Junior Member
Got it. I will fix.
Re: Incorrect work of the sql query with JOIN. [message #676519 is a reply to message #676516] Thu, 13 June 2019 11:17 Go to previous messageGo to next message
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 #676520 is a reply to message #676519] Thu, 13 June 2019 11:35 Go to previous messageGo to next message
Michel Cadot
Messages: 66469
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Did you check that each subquery is correct?
For me "j2.MIN_SALARY > e2.salary" should (functionally) return no rows unless data are wrong.

Re: Incorrect work of the sql query with JOIN. [message #676522 is a reply to message #676519] Thu, 13 June 2019 11:48 Go to previous messageGo to next message
John Watson
Messages: 7953
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 #676524 is a reply to message #676520] Thu, 13 June 2019 11:52 Go to previous messageGo to next message
orajav
Messages: 23
Registered: June 2019
Junior Member
Subquery t2 did not return any data:
https://www.screencast.com/t/zvdMgAuyTEmw
Re: Incorrect work of the sql query with JOIN. [message #676525 is a reply to message #676524] Thu, 13 June 2019 11:54 Go to previous messageGo to next message
BlackSwan
Messages: 26564
Registered: January 2009
Location: SoCal
Senior Member
orajav wrote on Thu, 13 June 2019 09:52
Subquery t2 did not return any data:
https://www.screencast.com/t/zvdMgAuyTEmw
once AGAIN, when is MIN_SALARY > SALARY?
Re: Incorrect work of the sql query with JOIN. [message #676528 is a reply to message #676525] Thu, 13 June 2019 12:44 Go to previous messageGo to next message
orajav
Messages: 23
Registered: June 2019
Junior Member
This condition should ensure the transition to another position with an increase in wages means,
what is the minimum salary for the new position must be greater than,
than current salary (excluding commissions).
Re: Incorrect work of the sql query with JOIN. [message #676530 is a reply to message #676528] Thu, 13 June 2019 14:46 Go to previous messageGo to next message
Michel Cadot
Messages: 66469
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So the condition "e2.job_id = j2.job_id" is wrong.

Re: Incorrect work of the sql query with JOIN. [message #676531 is a reply to message #676530] Thu, 13 June 2019 15:07 Go to previous messageGo to next message
orajav
Messages: 23
Registered: June 2019
Junior Member
Why? it's a join of tables.
Re: Incorrect work of the sql query with JOIN. [message #676538 is a reply to message #676531] Thu, 13 June 2019 23:58 Go to previous messageGo to next message
Michel Cadot
Messages: 66469
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
transition to another position
another job <> current job

Re: Incorrect work of the sql query with JOIN. [message #676540 is a reply to message #676538] Fri, 14 June 2019 02:23 Go to previous messageGo to next message
orajav
Messages: 23
Registered: June 2019
Junior Member
Hello!
I changed the condition, but the result is empty. See screen https://www.screencast.com/t/dqQKvhq6REL

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 #676541 is a reply to message #676540] Fri, 14 June 2019 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 66469
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Your specifications are not clear.

Clarify this: " a list of positions where they can go with an increase in salary".
Does this mean that the minimum salary in the new position is greater than the current salary of the employee? with or without the commission?

[Updated on: Fri, 14 June 2019 03:28]

Report message to a moderator

Re: Incorrect work of the sql query with JOIN. [message #676542 is a reply to message #676541] Fri, 14 June 2019 03:30 Go to previous messageGo to next message
orajav
Messages: 23
Registered: June 2019
Junior Member

Yes. This is a list of jobs where they can go with an increase in salary".
The minimum salary in the new job is greater than the current salary of the employee ( without the commission).
Re: Incorrect work of the sql query with JOIN. [message #676544 is a reply to message #676542] Fri, 14 June 2019 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 66469
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.
Re: Incorrect work of the sql query with JOIN. [message #676545 is a reply to message #676544] Fri, 14 June 2019 05:50 Go to previous message
orajav
Messages: 23
Registered: June 2019
Junior Member
Thank you!
Previous Topic: Using the Sql query to find employees who were recruited on the same day or on neighboring days.
Next Topic: How to Create variable out of Table Type Variable!
Goto Forum:
  


Current Time: Tue Jul 16 17:21:51 CDT 2019