Create a view in sql that collects employee and salary data

Question

Create a view in sql that collects employee and salary data

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I am doing a job for the school, in which they ask me to make a view that collects the following data:

ID of the employee, name and surname, salary, maximum salary and% of the current salary with respect to the maximum (I do not understand the latter very well)

I have decided to go in parts and before creating the view, I wanted to make the corresponding selection. I got here:

select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, max(SALARY), SALARY % max(salary) 
from EMPLOYEES 
group by EMPLOYEE_ID;

Obviously the identifiers would be missing, but the sql developer throws me the following error:

ORA-00911: invalid character 00911. 00000 - "invalid character" * Cause: identifiers may not start with any ASCII character other than letters and numbers. $ # _ are also allowed after the first character Identifiers enclosed by doublequotes may contain any character other than a doublequote. Alternative quotes (q '# ... #') can not use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual. * Action: Error in the line: 1, column: 72

I do not understand why, since I do not have identifiers anywhere ... I've had them, but I've been removing them to rule out errors, but before this error I was throwing an error on issues of grouping data, which does not I'm able to recover since only this one throws me now ...

My question is:

How can I solve the error that throws me? And I would also like know if my query is well done, if not, I would like that someone could tell me how would be the correct way to do it, since I have a bit forgotten the subject of BD, although with a brief soda can I get it out.

sql oracle oracle-sqldeveloper sqldeveloper

asked by THR4SH3RP0L0 15.10.2017 в 11:37

source

2 answers

Does copied code from workbench not work for me on phpmyadmin? Problem filtering arrangements in JS

score 0 · Answer 1

This part is the one that is incorrect:

SALARY % max(salary)

The synaxis is CAMPO o (funcion) <espacio o as > ALIAS . For example:

max(salary) salario_maximo

or

max(salary) as salario_maximo

I recommend you use "pje" instead of the "%" symbol in the field names. % has a proper meaning in sql and can become a problem at some point.

score 0 · Answer 2

What I can understand is that what is sought is: Obtain the salary of all employees and the ratio in percentage with the highest salary of all employees.

Well, this means that you have to build at least two independent queries, one to get all the employees and their salaries and another to get the maximum salary.

You can do it in many ways, the fastest is to integrate both in the following way:

SELECT  EMPLOYEE_ID, 
        FIRST_NAME, 
        LAST_NAME, 
        SALARY,
        SALARY / (SELECT MAX(SALARY) FROM EMPLOYEES) * 100
        FROM EMPLOYEES 
        GROUP by EMPLOYEE_ID;

With (SELECT MAX(SALARY) FROM EMPLOYEES) you get the highest salary of all and then applying it to each SALARY you get the percentage that represents the highest.