SQL

Introduction to SQL Views

By Asha S.Posted on
Time to Read:-words

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.

More like this:

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
[adinserter block=”2″]

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?

Security

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 how 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 altering the table.

Consistency

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.

Post Views: 577