DbSchema | SQL Views Explained

---

Design • 29-Feb-2020

by DbSchema Team

---

What are SQL Views

SQL Views are virtual tables with data derived from one or more base tables. A view can have rows and columns, just like a real table in the database. The view can derive all the rows from a base table or just some of them, based on a certain condition.

For example, views can be useful when, due to security reasons, we want to make public only a part of the table’s data.

How to create Views in SQL

• Creating a View from a single table

The SQL syntax for creating a view from a single table goes like this:

 CREATE VIEW view_name AS
 SELECT column
 FROM table_name
 WHERE condition;

Let’s consider that we have the next __employee_details` table in the database:

employee_id	name	address
1	Robin	Vienna
2	Cindy	Amsterdam
3	Lauren	Los Angeles
4	Tino	Bucharest
5	Jack	Frankfurt

Now I will create a view in which I want to add only the __name and __address columns for the employees with an __employee_id` smaller than 4.

CREATE VIEW employee_sm AS
SELECT name, address
FROM employee_details
WHERE employee_id < 4;

The new view will look like this:

name	address
Robin	Vienna
Cindy	Amsterdam
Lauren	Los Angeles

• Creating a View from multiple tables

Let’s add another table named __salary`:

id	name	salary
1	Robin	10.000
2	Cindy	15.000
3	Lauren	12.000
4	Tino	13.000
5	Jack	15.000

Now I will create a view that will include the employee name, address and salary. For this, I have to derive data from both __employee_details table and __salary table:

 CREATE VIEW employee_salary AS
     SELECT employee_details_name, employee_details.address, salary.salary
     FROM employee_details, salary
     WHERE employee_details.name = salary.name; 

The result will look like this:

name	address	salary
Robin	Vienna	10.000
Cindy	Amsterdam	15.000
Lauren	Los Angeles	12.000
Tino	Bucharest	13.000
Jack	Frankfurt	15.000

UPDATING VIEWS

A view must meet some conditions before it can be updated:

• The view definition has to be simple, without any aggregate functions such as SUM, AVG, MAX, MIN, COUNT;
• The view should not have any grouping, DISTINCT or JOIN clauses;
• The view should have only NOT NULL values.

CREATE OR UPDATE

We can use this query to add or delete fields from a certain view:

CREATE OR REPLACE VIEW view_name AS
SELECT column_1,coulmn_2, ...
FROM table_name
WHERE condition;

We can update a view by using the CREATE OR UPDATE syntax. Let’s add the __employee_id column to the __employee_salary view:

CREATE OR REPLACE VIEW employee_salary AS
SELECT employee_details.employee_id, employee_details.name, employee_details.address, salary.salary
FROM employee_details, salary
WHERE employee_details.name = salary.name;

INSERT

To insert a row in a view, execute the next statement:

INSERT INTO view_name
VALUES (value_1, value_2, value_3)

DELETE

To delete a row, the next statement is required:

DELETE FROM view_name
WHERE condition;

For example, we can delete from the __employee_details`, the employee with the name Tino:

 DELETE FROM employee_details
 WHERE name = Tino; 

Using DbSchema

In DbSchema, you can easily create views:

1.Right-click on a layout and choose ‘Create View’;

2.Choose a name and fill the details for the view. Before creating the view, you can check if the query is valid by pressing Validate Query;

3.After validating the query, the view will appear in the layout

Try DbSchema