This article will learn about views in databases and the way we create, update, drop, and use them, whether in Oracle or Microsoft SQL Server database. Views, in general, are a snapshot of the data from one or multiple tables with selected columns.
Create a View
Assume we have a table “Employees” containing four columns: Id, Name, Department, and Salary.
Creating view Syntax
CREATE VIEW ViewName AS SELECT col1, col2, ... FROM table_name WHERE condition;
Example of Creating a View
CREATE VIEW IT_DepartmentEmployees AS SELECT ID, Name FROM Employees WHERE Department = 'IT'
In the view above, it will display the ID and Name of employees from the IT department. Notice that the view doesn’t contain the salary columns, which means that someone using this view will not see this information.
Update a View
You can update a view if you need to add or remove a column, add a table, or change the where clause.
Updating a view Syntax
CREATE OR REPLACE VIEW ViewName AS SELECT col1, col2, ... FROM table_name WHERE condition;
Example of Updating a View
CREATE VIEW IT_DepartmentEmployees AS SELECT ID, Name, FROM Employees WHERE Department = 'IT' AND Salary >50000
Dropping a view
A view can be dropped if no longer needed.
Dropping a view Syntax
DROP VIEW view_name;
Example of Dropping a View
DROP VIEW IT_DepartmentEmployees
SQL Views rules
Just like most things in the programming world, SQL views creation and usage come with some rules, which I listed a few of them below:
- Views do not accept Parameters.
- You cannot create views on Temporary Tables.
- The tables used in the view must be within the same Database.
- Views cannot contain ORDER BY unless specifying TOP or FOR XML.
- The SQL inside the views should be valid. (Surprised?)
- Views can contain User-Defined Functions, Built-in Functions, Subqueries, and Calls to Stored Procedures.
- A view must have Execution Permission on other objects, such as a Stored Procedure if it calls it from its body.
- Views cannot contain INSERT, DELETE, or UPDATE Statements.
Why are Views great?
In views, data can be viewed only by whoever has permission to do so. Also, some sensitive data from a table can be omitted when creating a view. For example, your employees should not have access to social security data, so creating a View without the SSN column can achieve this goal.
Data Grid Manipulation
Some tables may contain too many columns, making it harder to see the essential information without scrolling left and right. A view is simply the way the data is presented by only showing the most important columns to a specific user. Also, columns in a view can be reordered, making it easy to put desired columns first, without the need of altering the table.
A view does not contain INSERT, UPDATE, or DELETE statements, making it relatively more manageable than other database objects.
Separation of Concepts
A View gives access to data but not the table structures; for security reasons, you may not want the person who creates reports for your company to gain knowledge about your database table structures.