Lesson 1
SQL Views Creation
Views allow you to create a stored way of looking at the information in your table. When you create a view, you create a way of querying the database table not only today, but also in the future. You can come back to a view and recall it, producing updated results. In this module, you will find out how views are created, how they apply to your query and reporting requirements, and how you can use views to make your queries easier in the future. Since views are a 1) server-element or 2) engine-based element, they can be heavily engine-dependent. There may be some slight differences in how views are implemented in your engine, and those differences will be called out whenever possible during these lessons. A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view. This is because a view is not really a table itself, but only a way to look at part of the original table.
What are SQL Views?
- Views, as the name implies, are queries used to provide users with a specific view of the data in the database. In doing so, views serve a variety of purposes. They may tie related tables together and then pull selected fields out that specific users need, or they may pull selected interesting rows of data from a table that contains other rows of no interest to specific users. They may summarize
- large data sets,
- returning averages, and
- counts.
Relational databases are sets of data in tables, which, taken alone, are rarely of any use to anyone.
Views allow the database administrator (DBA) to pull fields of interest from tables of interest and return a coherent data set useful to some specific user or application.
In this module,
- you learn how to build and save views for reuse,
- how to use views to tie related data back together,
- how to use views to narrow or limit the data that users are allowed to see, and
- how views may be used for security purposes.
SQL VIEWS
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
If a view contains the primary key and all others NOT NULL columns, the view can be used to insert datas or even override the original table constraints (by adding complementary constraints to the view). Here we will focus only on basic read-only views because this is the most common case for end-users.
SQL CREATE VIEW Syntax
The general syntax is: CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
Note: A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.
Also we begin with an example:
CREATE VIEW viwEmployee AS
SELECT cust_last_name, cust_first_name, credit_limit as buy_limit
FROM Demo_Customers
WHERE credit_limit >= 1000
WITH READ ONLY;
Results:
View created.
Column Name
Alias (if any)
Condition
cust_last_name
-
credit_limit >= 1000
cust_first_name
-
-
credit_limit
buy_limit
-
Create View Employee
Views (Virtual Tables) in SQL
In this section we introduce the concept of a view in SQL.
We show how views are specified, and then we discuss the problem of updating views and how views can be implemented by the DBMS.
- Concept of a View in SQL
A view in SQL terminology is a single table that is derived from other tables.
These other tables can be base tables or previously defined views. A view does not necessarily exist in physical form; it is considered to be a virtual table, in contrast to base tables, whose tuples are always physically stored in the database. This limits the possible update operations that can be applied to views, but it does not provide any limitations on querying a view. We can think of a view as a way of specifying a table that we need to reference frequently, even though it may not exist physically.
For example, referring to the COMPANY database in the table below we may frequently issue queries
that retrieve the employee name and the project names that the employee works on.
Rather than having to specify the join of the three tables EMPLOYEE,WORKS_ON, and PROJECT every time we issue this query, we can define a view that is specified as the result of these joins. Then we can issue queries on the view, which are specified as single-table retrievals rather than as retrievals involving two joins on three tables.
We call the EMPLOYEE,WORKS_ON, and PROJECT tables the defining tables of the view.
EMPLOYEE Fname Minit Lname Ssn Bdate
Address Sex Salary Super_ssn Dno
DEPARTMENT
Dname Dnumber Mgr_ssn Mgr_start_date
DEPT_LOCATIONS
Dnumber Dlocation
PROJECT
Pname Pnumber Plocation Dnum
WORKS_ON
Essn Pno Hours
DEPENDENT
Essn Dependent_name Sex Bdate Relationship
What are SQL Views?
- Views, as the name implies, are queries used to provide users with a specific view of the data in the database. In doing so, views serve a variety of purposes. They may tie related tables together and then pull selected fields out that specific users need, or they may pull selected interesting rows of data from a table that contains other rows of no interest to specific users. They may summarize
- large data sets,
- returning averages, and
- counts.
Relational databases are sets of data in tables, which, taken alone, are rarely of any use to anyone. Views allow the database administrator (DBA) to pull fields of interest from tables of interest and return a coherent data set useful to some specific user or application.
In this module,- you learn how to build and save views for reuse,
- how to use views to tie related data back together,
- how to use views to narrow or limit the data that users are allowed to see, and
- how views may be used for security purposes.
SQL CREATE VIEW Syntax
The general syntax is: CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition Note: A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view. Also we begin with an example:
CREATE VIEW viwEmployee AS SELECT cust_last_name, cust_first_name, credit_limit as buy_limit FROM Demo_Customers WHERE credit_limit >= 1000 WITH READ ONLY; Results: View created.
| Column Name | Alias (if any) | Condition |
|---|---|---|
| cust_last_name | - | credit_limit >= 1000 |
| cust_first_name | - | - |
| credit_limit | buy_limit | - |
Create View Employee
- Concept of a View in SQL
A view in SQL terminology is a single table that is derived from other tables.
These other tables can be base tables or previously defined views. A view does not necessarily exist in physical form; it is considered to be a virtual table, in contrast to base tables, whose tuples are always physically stored in the database. This limits the possible update operations that can be applied to views, but it does not provide any limitations on querying a view. We can think of a view as a way of specifying a table that we need to reference frequently, even though it may not exist physically.
For example, referring to the COMPANY database in the table below we may frequently issue queries that retrieve the employee name and the project names that the employee works on. Rather than having to specify the join of the three tables EMPLOYEE,WORKS_ON, and PROJECT every time we issue this query, we can define a view that is specified as the result of these joins. Then we can issue queries on the view, which are specified as single-table retrievals rather than as retrievals involving two joins on three tables.
We call the EMPLOYEE,WORKS_ON, and PROJECT tables the defining tables of the view.
EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT_LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS_ON Essn Pno Hours DEPENDENT Essn Dependent_name Sex Bdate Relationship