+91-90427 10472

Dot net training in Chennai

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.

Maria academy

View in SQL

Recent Posts

Categories