View in SQL

View in SQL

A view in SQL is a virtual table created from the results of a SELECT query. Views simplify complex queries, encapsulate logic, and provide an abstraction layer over the underlying tables. They can be used to present data in a more meaningful or secure manner than querying the underlying tables directly. Here’s a quick rundown of how to create and use views in SQL:

Creating a View:

The CREATE VIEW statement is used to create a view. The fundamental syntax is as follows:

CREATE VIEW view_name AS SELECT column_name_1, column_name_2, … FROM table_name WHERE condition;

For example, let us create a new table called studentdata using the syntax shown below.

create table studentdata( sno int, sname varchar(100), slocaction varchar(100), smothertongue varchar(100));

Insert some records as well using the syntax shown below.

insert into studentdata values(1, ‘RAM’, ‘CHENNAI’, ‘TAMIL’) insert into studentdata values(2, ‘RAJ’, ‘CHENNAI’, ‘HINDI’) insert into studentdata values(3, ‘RAVI’, ‘HYDERABAD’, ‘TELUGU’) insert into studentdata values(4, ‘RAJESH’, ‘HYDERABAD’, ‘TAMIL’) insert into studentdata values(5, ‘RAJIV’, ‘CHENNAI’, ‘TELUGU’)

Select query syntax can be used to display the table records.

SELECT * FROM studentdata

Create a view using the syntax shown below.

CREATE VIEW TN AS SELECT * FROM studentdata WHERE slocaction = ‘CHENNAI’

Using a View:

After you’ve created a view, you can query it like a regular table with the following syntax:

SELECT * FROM view_name

With reference to the given example,

SELECT * FROM TN

Updating a View:

You can use the ALTER VIEW statement to update views:

ALTER VIEW view_name AS SELECT column_name_1, column_name_2, … FROM table_name WHERE condition;

In Microsoft SQL Server, you can use the CREATE OR ALTER VIEW statement to replace an existing view or create a new one. This is similar to the CREATE OR REPLACE VIEW syntax found in other database systems. Using the preceding example:

ALTER VIEW TN AS SELECT * FROM studentdata WHERE smothertongue = ‘TAMIL’

Dropping a View:                

The DROP VIEW statement is used to remove a view:

DROP VIEW employee_view;

Thinking back to the earlier illustration:

DROP VIEW TN

 

Multiple Views:

Views enable you to create a virtual table in a relational database based on the results of a SELECT query. You can use JOIN operations in your SELECT statement to combine data from multiple tables in a view. Here’s a general guide to creating a view with multiple tables:

Consider the following scenario: you have two tables: customers and orders. The customers table contains information about customers, and the orders table contains information about the orders that these customers have placed.

— Sample customers table CREATE TABLE customers (     CustomerId INT PRIMARY KEY,     CustomerName VARCHAR(50),     Email VARCHAR(50) ); — Sample orders table CREATE TABLE orders (     OrderId INT PRIMARY KEY,     CustomerId INT,     OrderDate DATE,     TotalAmount DECIMAL(10, 2),     FOREIGN KEY (CustomerId) REFERENCES customers(CustomerId) );

Let us also insert some sample records into both tables using the insert query:

INSERT INTO customers VALUES (1, ‘RAM’, ‘ram@mail.com’) INSERT INTO customers VALUES (2, ‘RAJ’, ‘raj@mail.com’) INSERT INTO customers VALUES (3, ‘RAVI’, ‘ravi@mail.com’) INSERT INTO orders VALUES (101, 1, ‘2023/11/02’, 10000) INSERT INTO orders VALUES (102, 2, ‘2023/11/03’, 12000)

Check it out with the following select query:

SELECT * FROM customers SELECT * FROM orders

Let’s now create a view that combines data from both tables:

CREATE VIEW CustomerOrderView AS SELECT     c.CustomerId,     c.CustomerName,     c.Email,     o.OrderId,     o.OrderDate,     o.TotalAmount FROM     customers c JOIN     orders o ON c.CustomerId = o.CustomerId;

The CustomerOrderView view is created in this example by selecting specific columns from both the customers and orders tables. The JOIN clause is used to join rows from both tables using the CustomerId column in common.

After you’ve created the view, you can query it like any other table:

SELECT * FROM CustomerOrderView

Remember that the exact syntax can change based on the database management system (SQL, PostgreSQL, MySQL, etc.) you are using. Depending on the system you are working with, modify the SQL statements as necessary.

Social tagging: database management system > dotnet and sql job support from India > learn c# dotnet > Learn SQL