Skip to content Skip to main navigation Skip to footer

Managing Views

Objectives

This lesson covers the following objectives:

  • Create and execute a SQL statement that removes a view
  • Create and execute a query using an inline view
  • Create and execute a top-n-analysis query

Purpose

  • Learning to create and replace views wouldn’t be complete unless you also knew how to remove them.
  • Views are created for specific purposes.
  • When the view is no longer needed or needs to be modified, the means exist to make the necessary changes.
  • If an employee who had access to financial information leaves the company, you probably don’t want his view to remain accessible.
  • In this lesson, you will learn how to delete a view, create an inline view, and construct a SELECT statement to produce a sorted list of data.

Deleting a View

  • Because a view contains no data of its own, removing it does not affect the data in the underlying tables.
  • If the view was used to INSERT, UPDATE, or DELETE data in the past, those changes to the base tables remain.
  • Deleting a view simply removes the view definition from the database.
  • Remember, views are stored as SELECT statements in the data dictionary.
  • Only the creator or users with the DROP ANY VIEW privilege can remove a view.
  • The SQL syntax to remove a view is:
DROP VIEW viewname;

Inline Views

  • Inline views are also referred to as subqueries in the FROM clause.
  • You insert a subquery in the FROM clause just as if the subquery was a table name.
  • Inline views are commonly used to simplify complex queries by removing join operations and condensing several queries into one.
  • As shown in the example below, the FROM clause contains a SELECT statement that retrieves data much like any SELECT statement.
  • The data returned by the subquery is given an alias (d), which is then used in conjunction with the main query to return selected columns from both query sources.
Inline Views

This inline view finds the highest salary for each department, and the query then displays the name of the employee with that salary.

An inline view must have an alias (in this example, “d”) because it functions like a table name in the FROM clause, and “SELECT department_id, max(salary) …” is not a valid table name!

TOP-N-ANALYSIS

  • Top-n-analysis is a SQL operation used to rank results.
  • The use of top-n-analysis is useful when you want to retrieve the top 5 records, or top-n records, of a result set returned by a query.
SELECT ROWNUM AS "Longest employed", last_name, hire_date
FROM employees
WHERE ROWNUM <=5
ORDER BY hire_date;
TOP-N-ANALYSIS

The results of this query are not however what you would expect. The reason for this is that the ORDER BY clause always executes last, so the rows are ordered after they are given a number. The next slide demonstrates how to resolve this issue.

  • The top-n-analysis query uses an inline view (a subquery) to return a result set.
  • You can use ROWNUM in your query to assign a row number to the result set.
  • The main query then uses ROWNUM to order the data and return the top five.
SELECT ROWNUM AS "Longest employed", last_name, hire_date
FROM (SELECT last_name, hire_date
FROM employees
ORDER BY hire_date)
WHERE ROWNUM <=5;

By using an inline view, the ORDER BY clause is performed before the ROWNUM is added.

  • In the example above, the inline view first selects the list of last_names and hire_dates of the employees:
(SELECT last_name, hire_date FROM employees...
  • Then the inline view orders the years from oldest to newest.
...ORDER BY hire_date)
  • The outer query WHERE clause is used to restrict the number of rows returned and must use a < or <= operator.
TOP-N-ANALYSIS

Terminology

Key terms used in this lesson included:

  • DROP VIEW
  • INLINE VIEW
  • TOP-N ANALYSIS
  • ROWNUM

Summary

In this lesson, you should have learned how to:

  • Create and execute a SQL statement that removes a view
  • Create and execute a query using an inline view
  • Create and execute a top-n-analysis query

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *