SQL Views – Tutorial with Examples

SQL views are virtual tables that allow you to access data from one or more tables in a database as if they were a single table. Views provide a way to simplify complex data structures and improve the readability and security of your queries.

Creating a View

A view is created using the CREATE VIEW statement. The syntax for creating a view is as follows:

CREATE VIEW view_name AS
SELECT column_name1, column_name2, ...
FROM table_name
WHERE condition;

For example, let’s consider a table named employees with the following data:

id name department salary
1 John Doe Sales 50000
2 Jane Doe Marketing 60000
3 Jim Smith IT 65000

We can create a view that shows only the names and salaries of employees in the IT department:

CREATE VIEW it_employees AS
SELECT name, salary
FROM employees
WHERE department = 'IT';

Now, we can query the it_employees view just like a regular table:

SELECT *
FROM it_employees;

/* Output:
   name       salary
   ---------- ----------
   Jim Smith  65000
*/

Updating a View

Views are typically read-only, meaning that you can only query data from them and not modify the data. However, some databases allow you to update data through a view if certain conditions are met, such as having a single table underlying the view and not having any aggregate functions in the SELECT statement.

The syntax for updating a view is as follows:

UPDATE view_name
SET column_name1 = value1, column_name2 = value2, ...
WHERE condition;

For example, let’s say we want to give a 10% raise to the IT employee with the name “Jim Smith”. We can update the view as follows:

UPDATE it_employees
SET salary = salary * 1.10
WHERE name = 'Jim Smith';

This will update the underlying employees table as well:

SELECT * FROM employees
WHERE department = 'IT';

/* Output:
id name department salary
-- ---- ---------- ------
3 Jim Smith IT 71500
*/

Deleting a View

To delete a view, you use the DROP VIEW statement. The syntax is as follows:

DROP VIEW view_name;

For example, if we no longer need the it_employees view, we can delete it as follows:

DROP VIEW it_employees;

Conclusion

SQL views are a useful tool for organizing and simplifying data structures in your database. They provide a way to encapsulate complex data structures and improve the readability and security of your queries. Just remember that views are typically read-only and that updating a view may not be possible in all databases.

Leave a Reply

Your email address will not be published. Required fields are marked *