DbSchema | SQL Views Explained
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
