DbSchema Database Designer

DbSchema | SQL Views Explained

Publish on DbSchema Blog >>>

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


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.


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

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:

SELECT employee_details.employee_id, employee_details.name, employee_details.address, salary.salary
FROM employee_details, salary
WHERE employee_details.name = salary.name;


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

INSERT INTO view_name
VALUES (value_1, value_2, value_3)


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

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.

Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.

Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.

Query Builder alt >

Query Builder

Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.

SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries. The editor is autocompletion-enabled. More.

Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model. More.

Dark Theme alt >

Dark Theme

Configurable styles & dark theme. More.

Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.

DbSchema can be downloaded for free. No registration is required.