SQL Views: How to work with Views in SQL

Views in SQL Server

A view in SQL is a virtual table that is created based on a SELECT statement. It is not a permanent table and data is not stored in the database, but the view can be used to SELECT, INSERT, UPDATE, DELETE, and INDEX data just like a regular table.

Here are some basic examples of how to work with views in SQL:

types of views in sql

Creating a view:

CREATE VIEW view_name ASSELECT column1, column2
FROM table_name
WHERE condition;

Updating data in a view:

UPDATE view_name
SET column1 = value1, column2 = value2
WHERE condition;

Deleting data from a view:

DELETE FROM view_name
WHERE condition;

Inserting data into a view:

INSERT INTO view_name (column1, column2)
VALUES (value1, value2);

Indexing a view:

CREATE INDEX index_name ON view_name (column1, column2);

Here is an example of creating a view, inserting data into the view, and then selecting data from the view:

• — Create a view that shows the total cost and number of items for each order

CREATE VIEW order_summary ASSELECT order_id, SUM(price * quantity) AS total_cost, SUM(quantity) AS total_items
FROM orders
GROUP BY order_id;

• — Insert a new order into the orders table

INSERT INTO orders (order_id, item, price, quantity)
VALUES (100, 'Apples', 0.99, 10), (100, 'Bananas', 0.59, 15), (100, 'Oranges', 0.79, 5);

• — Select data from the view

SELECT * FROM order_summar;

The output of the SELECT statement would be:

examples of working with views in SQL:

Updating data in a view:

• — Create a view that shows the total cost and number of items for each order

CREATE VIEW order_summary ASSELECT order_id, SUM(price * quantity) AS total_cost, SUM(quantity) AS total_items
FROM orders
GROUP BY order_id;

• — Update the quantity of an item in the orders table

UPDATE orders
SET quantity = 20
WHERE order_id = 100 AND item = 'Apples';

• — The total cost and number of items in the view will be updated to reflect the change in the orders table

SELECT * FROM order_summary

The output of the SELECT statement would be:

Deleting data from a view:

• — Create a view that shows the total cost and number of items for each order

CREATE VIEW order_summary ASSELECT order_id, SUM(price * quantity) AS total_cost, SUM(quantity) AS total_items
FROM orders
GROUP BY order_id;

• — Delete an item from the orders table

DELETE FROM orders
WHERE order_id = 100 AND item = 'Bananas';

• — The total cost and number of items in the view will be updated to reflect the change in the orders table

SELECT * FROM order_summar

The output of the SELECT statement would be:

Note: The ability to update, delete, and insert data into a view may depend on the underlying tables and the SELECT statement used to create the view. Some databases may not allow these operations on views if the SELECT statement does not meet certain criteria (e.g. does not contain a unique key).