VIEW to Provide a Subset

1. Introduction

A view is a named query that allows us to present the data in a database in a different way. When we create a view, we are basically creating a query and then assigning it a name. Then we can query the view for simplicity. These named queries that we create do not store any data directly, unless they are materialized views. Those are special types of views that store the data physically and refresh the data from the base tables that the views are dependent on.

2. VIEW Examples

A view can help simplify the complexity of a query, as we will see in the upcoming tutorials, by allowing us to bypass entering in the complex query each time. In addition, like a table, you can grant permission to users through a view that contains specific data. For example, if you have an employee table that has sensitive personal information like salary, you could create a view that does not include the salary and allow users to query that view rather than the base table.

The statement would look like the following:

CREATE VIEW <viewname>
AS
SELECT <statement>;

For example, if we wanted to only have a list of the employee names and who they report to, we could create a view like:

CREATE VIEW employee_report
AS
SELECT first_name, last_name, employee_id, reports_to
FROM employee;

To query from this view, we would create a SELECT statement as if it were querying from a table:

SELECT * 
FROM employee_report;

If this were a query that we ran often, it would simplify having to write out the query each time.

If different employees wanted to query their customer list (through the support_rep_id), we could create a view for each:

CREATE VIEW customer_contact_3
AS
SELECT first_name, last_name, email 
FROM customer
WHERE support_rep_id = 3;

Instead of having to query that entire customer list and filtering out the data each time, the support_id_rep equal to 3 can query the customer_contact_3 view directly, like:

SELECT * 
FROM customer_contact_3;

Otherwise, they would have to enter in the entire SELECT statement each time:

SELECT first_name, last_name, email 
FROM customer
WHERE support_rep_id = 3;

In this case, the view filters the columns (first_name, last_name, and email) as well as the rows (those that have the support_rep_id equal to 3). Since the view does not store any data, if anything changes in the customer table, the updates would be reflected in the view immediately when queried.

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.