CODEWITHSUNDEEP
sqlselectsqlplus
Jason Li
Jason Li

Reputation: 3

FAIL to SELECT data from table that created by select statement in SQLPLUS

This is my sql statement

it is used to "Find the average salary for each job title. Show the job title and the amount of those that have the average salary amounts 3000 or higher." 

CREATE TABLE avgSal AS (
SELECT job, AVG(sal) AS "sal" FROM Emp GROUP BY job);

SELECT * FROM avgSal WHERE sal>3000;

then sqlplus say

ORA-00904: "SAL": invalid identifier

After I enter the second sql statement

Can someone give me some help? /(T_T)\

And this is my first time to use stackoverflow (-,-)/

Upvotes: 0

Views: 87

Answers (1)

user330315
user330315

Reputation:

You quoted the column name "sal" and therefor it is case-sensitive.

You must quote the column when referencing it:

SELECT * 
FROM avgSal 
WHERE "sal" > 3000;

In general it's better to avoid quoted identifiers.

More details about identifiers can be found in the manual:
http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements008.htm#SQLRF00223

But you don't really need to create a table in order to achieve what you want:

select *
from (
  SELECT job, 
         avg(sal) AS avg_sal 
  FROM Emp 
  GROUP BY job
) t
where avg_sal > 3000;

Another option is to use a common table expression:

with avgSal as (
  SELECT job, 
         avg(sal) AS avg_salary
  FROM Emp 
  GROUP BY job
) 
select *
form avgSal
where avg_salary > 3000;

Upvotes: 1

Related Questions