Inline Views

Contact Us or call 1-877-932-8228
Inline Views

Inline Views

An inline view (unlike a regular view such as the emp_details_view) is not a schema object. It is simply a subquery in the FROM clause which may be referenced with an alias.

Display a count of all employees whose last name begins with 'A'.

Code Sample:

Using-Subqueries/Demos/inline_view.sql
SELECT count(*) 
FROM 
(
	SELECT *
	FROM employees
	WHERE last_name LIKE 'A%'
);

This query uses an inline view. The result is a count of the total number of records that are returned by the nested query. (In fact, any query could be replaced as the inner query and a count of the rows will be returned). In the example of nested subqueries, the outer query used here is used again with a different inner query.

Subquery in the HAVING clause

A simple subquery can be specified in the HAVING clause. It is similar in many ways to a subquery in the WHERE clause. The HAVING clause is distinguished from the WHERE clause based upon when it is evaluated and what it evaluates. The WHERE clause is evaluated before the GROUP BY clause, while the HAVING clause is evaluated after it. Filtering predicates in the WHERE clause eliminate rows from the result set when single row (scalar) expressions are evaluated. Filtering predicates in the HAVING clause eliminate rows from the result set when aggregate functions are evaluated.

Using the EMP_DETAILS_VIEW display each department name and its average salary for those departments whose average salary is greater than the average salary for all employees.

Code Sample:

Using-Subqueries/Demos/simple_subquery_having.sql
SELECT department_name, avg(salary) 
FROM emp_details_view 
GROUP BY department_name 
HAVING avg(salary) > (  
	SELECT avg(salary) 
	FROM employees
);

This query returns the departments and the average salary for each department where the average salary for the department is greater than the average salary for all employees.

Next