MainMenu

Home Java Overview Maven Tutorials

Sunday 15 January 2017

Index and View in SQL

Create Index with example

An index can be created to fetch the data more quickly.
An index is not visible to users.
Indexes are special lookup tables that the database search engine can use to speed up data retrieval.

Query :

Single-column Index Syntax


CREATE INDEX index_name
ON table_name (column_name)
Consider the below table (Employee)& add index in this table on column E_id
E_idName
01Chandan
02Adhiraj

Create Index Query


CREATE INDEX E_index
ON Employee (E_id)

Now an index will be created on column E_id in above table.

Composite-column Index Syntax


CREATE INDEX index_name
on table_name (column1, column2);

Unique Index Syntax


CREATE UNIQUE INDEX index_name
on table_name (column_name);

Index can also be dropped by DROP keword :


DROP INDEX index_name;

View with example

A VIEW is used to create a virtual table, which shows his presence when invoked by name.
A VIEW can contain all rows of a table.
A VIEW can be created from one or many tables.
A VIEW restrict access to the data such that a user can only see and sometimes can modify only what they need.
KEYWORD "CREATE VIEW AS SELECT" is used to create view.

Example :

Consider the below table :

Employee-
E_idNameAge
01Chandan26
02Adhiraj27
03Sara28

Query to create view
CREATE VIEW cs AS
Select Emp_id, Name
From Employee

Now a virtual table with records will be created, now write the query to fetch the record of this virtual table :

Query :
Select *
From cs

Result :
E_idName
01Chandan
02Adhiraj
03Sara

View can also be dropped by DROP keword :


DROP View view_name;

In our above example view can be dropped


DROP VIEW cs;

UPDATE the VIEW :


Method 1: Existing view will be replaced and new view will be added.

Query :
CREATE OR REPLACE VIEW csc AS
Select Emp_id, Name, Age
From Employee

Query to create view with "WITH CHECK OPTION"
With check option ensure that all UPDATE and INSERTs satisfy the condition(s) in the view definition.
Query :
CREATE VIEW cs AS
Select Emp_id, Name
From Employee,
Where Emp_id IS NOT NULL,
WITH CHECK OPTION;
NOW NUll Values are restricted in E_id column.
UPDATE the reocrd in view table
UPDATE cs
Set Name = "Akshat"
WHERE E_id = 01

Query :
Select *
From cs

Result :
E_idName
01Akshat
02Adhiraj
03Sara
Thanks :)

Tag: What is view in SQL with example?, What is index in sql with example.

No comments:

Post a Comment