Oracle SQL output wrong

Question

I am not being able to produce the desired results of this question:

Display alphabetically the full name, job, salary, department number for each employee who earns less than the best paid unionized employee

unionized employees are not managers presidents of vice presidents And do not work in SALES or MARKETING departments
Full name should be displayed as Firstname Lastname and should have the heading Employee.
Salary should be left-padded with the = symbol till the width of 12 characters. It should have an alias Salary.
salary is formatted as a currency amount incl. thousand separator, but no decimals
Limit the width of the Employees name to 25 characters.

The output lines should look like this sample line:

Jonathon Taylor     SA_REP  ==== $8,600     80

My query so far:

select last_name || ', ' || first_name "full name", job_id, salary, department_id
from employees   
where salary < ANY
          (SELECT MAX(salary)
          from employees e join departments d
          where e.job_id <> 'AC_MGR' OR 'AD_VP' OR 'AD_PRES'
          AND d.department_name <> 'SALES' OR 'MARKETING'
          GROUP BY department_id)

If someone can help me out and point me in the right direction of how to get the proper output, that would be greatly appreciated. Thank you!

Parens around the ORs. Actually that expression isn't even valid: you want e.job not in ('AC_MGR', 'AD_VP', 'AD_PRES'). Same with department_name. And agreed that you don't need the GROUP BY either. And once your subquery returns a single row the ANY will no longer be necessary. — shawnt00
– shawnt00, Commented Jun 21, 2016 at 4:53
ORA-00905: missing keyword 00905. 00000 - "missing keyword" *Cause: *Action: Error at Line: 6 Column: 15 — 13design
– 13design, Commented Jun 21, 2016 at 4:58

APC · Accepted Answer · 2016-06-21 07:04:32Z

1

SELECT substr(first_name || ' ' || last_name, 1, 25) as NAME
      , job_id 
      , lpad( to_char(salary, '$999,999.99' ), 12, '=') as SALARY
FROM employees
WHERE salary <
(
    SELECT MAX(salary)
    FROM employees e INNER JOIN departments d
        ON e.department_id = d.department_id
    WHERE e.job_id NOT IN ('AC_MGR', 'AD_VP', 'AD_PRES') AND
          d.department_name NOT IN ('SALES', 'MARKETING')
)

Some problems from your original query which I fixed:

Fixed the concatenation in SELECT to match your desired output
Added a JOIN condition, since your original query is doing a CROSS JOIN (which you probably don't want)
Replaced the OR conditions in the WHERE with WHERE ... NOT IN
Removed GROUP BY from the subquery, since it didn't serve any purpose

edited Jun 21, 2016 at 7:04

APC

147k20 gold badges180 silver badges289 bronze badges

answered Jun 21, 2016 at 5:06

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

13design Over a year ago

how would you insert the thousandths seperator ? i.e $8,777. also limit the name to 25 chars. Thanks

Tim Biegeleisen Over a year ago

@13design What do you mean by limit the "name" to 25 characters? You do realize that the full name is a concatenation of the first and last name columns?

APC Over a year ago

@TimBiegeleisen - the OP is referring to the formatting requirements included in the question. Ironically those requirements were obfuscated by poor formatting choices in the question text, Fixed now.

mmo2112 · Accepted Answer · 2016-06-23 02:21:11Z

0

This is HR schema, right brother? You need inner join Employees and Departments with both department_id are matched. You can use WITH syntax for your query:

with temp as (
select last_name || ', ' || first_name as "Full name",
job_id, salary as slr, d.department_id
from employees e inner join departments d on e.department_id=d.department_id
where e.job_id NOT in ('AC_MGR','AD_VP','AD_PRES')
and d.department_name NOT in ('SALES', 'MARKETING')
)
select * from temp where slr <= (select max(slr) from temp);

edited Jun 23, 2016 at 2:21

answered Jun 21, 2016 at 5:14

mmo2112

1510 bronze badges

Collectives™ on Stack Overflow

Oracle SQL output wrong

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related