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_idnameaddress
1RobinVienna
2CindyAmsterdam
3LaurenLos Angeles
4TinoBucharest
5JackFrankfurt

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:

nameaddress
RobinVienna
CindyAmsterdam
LaurenLos Angeles

  • Creating a View from multiple tables

Let's add another table named salary:

idnamesalary
1Robin10.000
2Cindy15.000
3Lauren12.000
4Tino13.000
5Jack15.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:

nameaddresssalary
RobinVienna10.000
CindyAmsterdam15.000
LaurenLos Angeles12.000
TinoBucharest13.000
JackFrankfurt15.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_idcolumn 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

DbSchema Database Design Tool

The Art of Keeping the Data Together
Desktop app for
DbSchema ER Diagram Features Overview
Visual Design & Schema Diagram

➤ Create and manage your database schema visually through a user-friendly graphical interface.

➤ Easily arrange tables, columns, and foreign keys to simplify complex database structures, ensuring clarity and accessibility.

GIT & Collaboration
Version Control & Collaboration

➤ Manage schema changes through version control with built-in Git integration, ensuring every update is tracked and backed up.

➤ Collaborate efficiently with your team to maintain data integrity and streamline your workflow for accurate, consistent results.

Data Explorer & Query Builder
Relational Data & Query Builder

➤ Seamlessly navigate and visually explore your database, inspecting tables and their relationships.

➤ Build complex SQL queries using an intuitive drag-and-drop interface, providing instant results for quick, actionable insights.

Interactive Documentation & Reporting
HTML5 Documentation & Reporting

➤ Generate HTML5 documentation that provides an interactive view of your database schema.

➤ Include comments for columns, use tags for better organization, and create visually reports.