Online College Courses for Credit

+
VIEW to Provide a Subset

VIEW to Provide a Subset

Rating:
(0)
Author: Sophia Tutorial
Description:

Given a query that shows a useful report to regularly view a subset of data from a table, create a view for that query.

(more)
See More
Tutorial

what's covered
This tutorial explores using views to provide a useful report on a subset of data in two parts:
  1. Introduction
  2. VIEW Examples

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. Since we can query a view, the view can be a complex query so we would not have to enter in the entire complex query each time. In addition, like a table, you can grant permission to users through a view that contains specific data that certain users are allowed to see. For example, if you have an employee table that has personal information that others should not be able to see like the salary, you could create a view that does not include the salary and allow others 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;

File:11468-2410-1.png

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


SELECT * 
FROM employee_report;

File:11469-2410-2.png

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

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;

File:11470-2410-3.png

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). If anything changes in the customer table, since the view does not store any data, the updates would be reflected in the view immediately if we queried the view.


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.

summary
Using the VIEW allows us to create a named query to query from to simplify SELECT statements.

Source: Authored by Vincent Tran