This is a two part question ... Firstly I'm stuck on the logic, the second part is a formatting.
The question reads as such: Display all jobs and the maximum total pay (salary + commission) for each job. The table is called employee -- and the columns that are appropriate here are jobs, salary and commission (both salary and commission have the potential to be null).
select * from employee
where sum (SALARY + COMMISSION) =
(select max (SALARY + COMMISSION)
From employee)
group by job;
I can't for the life of me figure out how to even use ISNULL there too.
You must display the maximum total pay with a dollar ($) sign, a comma, and two decimal places (e.g., $1,234.56). Sort your output by the job.
Do i format it with some command - or can I simply concatenate '$' ... please help.
a few lines for the table:
7839|KING|PRESIDENT||20-NOV-01|5000||50
7596|JOST|VICE PRESIDENT|7839|04-MAY-01|4500||50
7603|CLARK|VICE PRESIDENT|7839|12-JUN-01|4000||50
the create statement:
CREATE TABLE employee
( EMPLOYEE_ID NUMBER(4) PRIMARY KEY,
EMPLOYEE_NAME VARCHAR2(20) NOT NULL,
JOB VARCHAR2(50) NOT NULL,
MANAGER_ID NUMBER(4),
HIRE_DATE DATE,
SALARY NUMBER(9, 2),
COMMISSION NUMBER(9, 2),
DEPARTMENT_ID NUMBER(4) REFERENCES department(DEPARTMENT_ID));
select job, concat('$',format(max(total_pay),2))
from (select job, (coalesce(salary,0) + coalesce(commission,0)) total_pay
from employee) t1
group by job
order by job