I really want to know how the column aliases work because they drive me insane in oracle.The query I wrote makes perfect sense to me but doesn't work. SELECT e1.employee_id , e1.salary , e1.department_id , ROUND(AVG(e2.salary),2) as Avg_Sal FROM employees e JOIN employees e2 ON e2.department_id = e.department_id GROUP BY e1.employee_id , e1.salary , e1.department_id HAVING e1.salary SELECT * FROM ( SELECT employee_id , salary , department_id , ( SELECT ROUND(AVG(salary),2) FROM employees e_inner WHERE e_inner.department_id = e.department_id ) AS avg_sal FROM employees e ) as Subquery Alias WHERE salary @Quandary -- the second one is simply the OP's query nested inside a second query so the column alias can be used in the predicate.Part 3 in a series of articles on understanding and using PL/SQL Every application needs data. An application is almost always built on top of database tables. And the programs you write—whether they are in PL/SQL or another language—manipulate that data.It is, therefore, extremely important for you to be aware of the different datatypes supported by PL/SQL and how you can work with those datatypes.So such an alias is only applied to the result column after WHERE is evaluated.I like your answer Russell H but it is a bit more advanced than I am at right now.It is created with the CREATE PROCEDURE or the CREATE FUNCTION statement.It is stored in the database and can be deleted with the DROP PROCEDURE or DROP FUNCTION statement.
The declaration and exception sections are optional.
PL/SQL procedure allows you to encapsulate complex business logic and reuse it in both database layer and application layer.
The following illustrates the PL/SQL procedure’s syntax: keyword is known as procedure body.
It's not likely to have worse performance than the accepted answer, which repeats the subquery multiple times.
Overall though, the analytic answer would probably have the best performance.
Each of my PL/SQL 101 articles offers a quiz to test your knowledge of the information provided in the article.