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’;

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;

view dialog

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

view in layout

Try DbSchema