SQL Views

by

The SQL Views

A virtual table can be created in SQL using the CREATE VIEW statement and the results of a SELECT query.

Although a view is not a physical table, subsequent SQL queries may treat it as one.

By exposing only specific columns or rows to users or applications, views offer a mechanism to manage access to the data, simplify complex searches, and abstract the underlying table structure.

What is view in sql in other words?

A view is a virtual table that is filled with information from a query. A view is like a table in that it has both named fields and rows of data. A view is not a set of data values that are kept in a database unless it is sorted.

Here, we can create a view using the CREATE VIEW statement.

Different types of SQL views

They are main three types of sql views.

  • Simple View
  • Complex View
  • Materialized View

Syntax for SQL CREATE VIEW Statement

There is only one table in a simple view, and there are no functions or aggregates in it.

Simple sql View Example:

To Create a Simple View in sql
CREATE VIEW SimpleView AS
SELECT column1, column2, columnN,….
FROM Table1
WHERE condition;

Let’s explain all sql views with examples:

Simple View:

Let’s say we have an employee table, and we want to create a view that shows some specific columns like names and salaries of employees with salaries greater than 50,000.

Now, you can query the high_employees view as if it were a table:

SELECT * FROM high_employees;

There is only one table in a simple view, and there are no functions or aggregates in it.

Simple sql View Example:

To Create a Simple View in sql
CREATE VIEW SimpleView AS
SELECT column1, column2, columnN,….
FROM Table1
WHERE condition;

Joining Tables with a View:

Views can be used to simplify complex joins. For example, if you have two tables, orders and order_details, and you want to create a view that combines them to show order details along with customer information,

CREATE VIEW order_details_with_customer AS
SELECT o.order_id, o.order_date, c.customer_name, od.product_id, od.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_details od ON o.order_id = od.order_id;

Complex Views

Using joins, functions, or aggregations, the complex views are create from more than one table.

Complex view example in sql:

To Create a Complex View in sql
CREATE VIEW ComplexView AS
SELECT tab1.column1, tab2.column2
FROM Table1 tab1
JOIN Table2 tab2 ON tab1.id = tab2.id
WHERE condition;

Materialized Views

Materialized views store data physically and need refreshing to update their content.

Materialized sql view Example:

To Create a Materialized View in sql
CREATE MATERIALIZED VIEW MaterializedView AS
SELECT column1, column2
FROM Table1
WHERE condition;

To Refreshing Materialized View
REFRESH MATERIALIZED VIEW MaterializedView;

Indexed Views (SQL Server)

Indexed views are precomputed results stored in the database as indexes.

Indexed sql server view Example:

To Create an Indexed View in sql
CREATE VIEW IndexedView WITH SCHEMABINDING AS
SELECT tab1.column1, SUM(tab2.column2) AS Total
FROM Table1 tab1
JOIN Table2 tab2 ON tab1.id = tab2.id
GROUP BY tab1.column1;

To Create an Index on the View in sql:
CREATE UNIQUE CLUSTERED INDEX IX_IndexedName ON IndexedName(column1);

Updatable Views

Updatable views allow modification of underlying data through the view.

Updatable sql view Example (To create an Updatable View in sql)
CREATE VIEW UpdatableView AS
SELECT column1, column2
FROM Table1
WHERE condition;

To update Data through View
UPDATE UpdatableView
SET column2 = ‘New Value’
WHERE column1 = ‘Some Condition’;

These various types of views in SQL offer different functionalities, catering to specific data manipulation and accessibility needs within database management systems.

Aggregated View:

Views can also include aggregated data. Let’s say you have a table named sales and you want to create a view that shows total sales revenue for each product:

CREATE VIEW product_sales AS
SELECT product_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY product_id;

Nested Views:

Views can be based on other views. For instance, you can create a view based on the product_sales view created earlier:

CREATE VIEW top_selling_products AS
SELECT product_id, total_sales
FROM product_sales
WHERE total_sales >= 10,000;

SQL Dropping a View

To drop or delete the view, just mention the name of the view.

DROP VIEW view_name;

Security and Access Control:

Views can be used to control access to sensitive data by limiting what users or applications can see. For example, you have the ability to design a view that conceals the remaining items in a table while revealing just a portion of the columns or rows included inside it.

CREATE VIEW sensitive_data AS
SELECT user_id, credit_card_number
FROM users;

NOTE: Views do not store data themselves; they are just saved queries that retrieve data from the underlying tables when queried. Views provide an abstraction layer over the data, making it easier to work with complex database structures and enhancing security by limiting access to specific data subsets.

How to modify or alter a view in SQL?

Views can be altered using the ALTER VIEW statement to modify the view’s query definition.

To alter view in sql

ALTER VIEW viewname AS
SELECT EmployeeID, FirstName, LastName, Department, Salary
FROM Emp;

To drop or delete a view in SQL?

To drop or delete with Example (Dropping a view named ‘Emp’)
Syntax: Drop view veiw_name;

DROP VIEW Emp;

To Grant Permissions on a View in sql

Suppose you want to grant SELECT permission on the view_name view to a user named Scott.

GRANT SELECT ON view_name TO scott;

To Revoke Permissions on a View in sql:
Now, revoke the previously granted SELECT permission from Scott on the view_name view:

REVOKE SELECT ON view_name FROM Scott;

How to check if a view exists in a SQL database?

To check if a view exists:

Checking if the view ‘Emp’ exists
IF EXISTS (SELECT * FROM sys.views WHERE name = ‘Emp’)
SELECT ‘View exists’ AS Result;
ELSE
SELECT ‘View does not exist’ AS Result;

What is the purpose of using views in SQL?

SQL Views are useful for various purposes:

  • Simplify complex queries.
  • Hide sensitive data.
  • Provide a customized presentation of data.
  • Enhance security by controlling data access.
  • Reduce redundancy and ease data retrieval.

What are the advantages of using materialized views in SQL?

  • Enhanced query performance.
  • Offline access to data without accessing underlying tables.
  • Reduced computational load by storing precomputed results.

FAQ

What is the use of views in SQL?

A view is like a table in that it has rows and sections. There may be more than one real table in the database that a view links to. You can view and show the data as if it were coming from a single table by adding SQL statements and functions. With the CREATE VIEW line, a view is made.

What is the difference between a view and a table?

A view is an item in a database that lets you make a reasonable subset of data from one or more tables. There is something called a table in a database that holds the data in it. The view is based on the table. The table is a separate piece of information.

Why use views vs. tables?

Tables are great for changing and searching data because they are flexible, keep data safe, and don’t change it. But they can be hard to handle, and if they aren’t standardized properly, data can be duplicated. On the other hand, views make it easier to reach data, make it easier to read data, and speed up queries.

What is a view and what is its type?

A view is like a window through which data from tables can be seen or changed. It does not hold any data of its own. The table that a view is built on is known as the BASE Table. In SQL, views come in two different types: Simple view and Complex views. In a simple view, there can only be one base table.

Is the view faster than the table?

Speed up. It might be faster to get information that is stored in a database table. You can quickly get to the information you need once you open the app. Because you have to run a query first, getting to data in a view may take longer.

Can we insert data into the view?

If you have the Insert permission on a view that only shows one table, you can add data through it. In order to do this, the basic SELECT command can only choose from one table and can’t have any of the following parts: Keyword that is different. GROUP BY Clause.

What are a simple view and a complex view in SQL?

A simple view is one that is built on a single table and doesn’t have any functions or the GROUP BY clause. A complex view is made up of several tables that are GROUP BY phrases and functions.

Leave a Comment