How to use ALIASES in Oracle correctly?

Navigation
2

I'm trying to select multiple columns from a single table, and to make it easier to see the data and make it easier to separate the columns, I've decided to use aliases.

But the problem is that when I run the query, it returns me the following error:

  

ORA-00923: keyword FROM not found where expected

My code looks like this: 

SELECT MAX(salary) as 'Maximo Salário' , 
MIN(salary) as 'Minimo Salário', 
SUM(salary) as 'Soma de Todos Salários', 
AVG(salary) as 'Média Salarial' FROM employees

According to Oracle's own documentation, a column of a table with aliases is selected as follows: 

column_name AS alias_name

So, what am I doing wrong here? Should there be any WHERE in this SELECT ? But it is not obligatory to have it, only in cases where it is necessary to actually use it.

How can I correctly use aliases in this SELECT ?

    
asked by anonymous 06.11.2017 / 20:33

2 answers

4

Names must be enclosed in double quotes, not simple. 

SELECT MAX(salary) as "Maximo Salário" , 
MIN(salary) as "Minimo Salário", 
SUM(salary) as "Soma de Todos Salários", 
AVG(salary) as "Média Salarial" FROM employees
    
06.11.2017 / 20:39
1

Supplemented with some points:

Using AS when used to name columns is optional, example : 

SELECT 
      MAX(salary) "Maximo Salário" , 
      MIN(salary) "Minimo Salário", 
      SUM(salary) "Soma de Todos Salários", 
      AVG(salary) "Média Salarial" 
FROM employees

If the alias to be chosen does not have more than one word, you can refrain from using the double quotes, example

a>: 

SELECT 
  MAX(salary) MaximoSalario , 
  MIN(salary) MinimoSalario, 
  SUM(salary) SomaDeTodosSalarios, 
  AVG(salary) MediaSalarial 
FROM employees

There is a possibility to reference an alias in ORDER BY , example : 

SELECT 
  MAX(salary) "Maximo Salario" , 
  MIN(salary) MinimoSalario, 
  SUM(salary) SomaDeTodosSalarios, 
  AVG(salary) MediaSalarial,
  dep
FROM employees
group by dep
order by "Maximo Salario"

Another useful use of AS and the construction of a QUERY that uses SUB QUERY , example : 

WITH 
   avg_sal AS (
      SELECT AVG(salary) avg FROM employees),
   emp_dep AS (
      SELECT name, salary FROM employees where dep = 10)


SELECT * FROM emp_dep where salary > (select avg from avg_sal)


Comments:

  • Using double quotes, Oracle is case-sensitive.
  • If you do not use double quotation marks, Oracle will interpret everything as upper case.
  • Avoid using the same identifier for two columns.
  • Each version of Oracle has a specific limit for an identifier.

In this example , internally Oracle will set the identified MAXIMO to both columns if you try to use this you will receive an error: ORA-00960 : 

SELECT 
  MAX(salary) "MAXIMO" , 
  MIN(salary) maximo
FROM employees
order by maximo
    
15.12.2017 / 12:52
Why are the pull-left and pull-right classes no longer working in Bootstrap 4? Validating Value