SQL Server View

 

View is a nothing but a virtual table. View is a query statement on database using one or multiple tables .View does not store physical data , view fully virtual.

         Changes in any table used for view , data of view automatically changes. You can get data from view ,you can join view with tables, but you can not modify data of a view.View widely used for reporting purpose.  

You can use view in a function , store procedure to get arranged data quickly.

          You can create a view in two ways  

A)By interface  

B)Writing script. 

Interface and script both allowed to create ,update ,delete view.

           Here we are going to learn how a view can be created using interface.From SQL management Studio 

Create View By Interface 

 

1)Do login.

2)Select database.

3)Go to the view and right click, you will get context menu called "New View".

4)Click on "New View" , an interface will be open showing the list of tables you can use. 

5)Then close the Tables. You will see graphical interface of table will created and you can click on columns to make relation and by selection of tables. 

6)In the lower portion , you will see that automatic sql query have been prepared while selecting the columns.Click on "Save" , an confirmation window will as the name of the View , click OK.

You can create view by writing script also. 

Create View By script 

1)Go to Management Studio 

2)Open a new query window write a script for create view and press F5.

CREATE VIEW vw_student

AS SELECT a.name,

                a.date_of_birth, 

                b.height

          FROM student a inner join

                    student2 b on a.id=b.id

Below is the syntax for creating a view

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition; 

You can also delete view from both interface and script. 

To Delete a view from Interface

1)Open SQL server management Studio and login, select views you want to delete, click on delete.

2)An interface will open for confirmation of delete, click on "Ok".

 

To Delete a view from script

1)Go to Management Studio 

2)Open a new query window write a script for delete view and press  F5.

DROP VIEW vw_student

SQL Server LIKE

The LIKE operator is used in a where clause to find specified pattern of character from string or column.LIKE operator uses two type of wild card character % or _.

 % - find zero, one, or multiple characters pattern.
 _ -  find represents a single character.

Here is type , how you can use the LIKE operator.


1) Name LIKE 'a%'
Finds any values that start with a.    

2)Name  LIKE '%a%'
Finds any values that have a in any position.  

3)Name LIKE '_ab%'
Finds any values that have ab in the second and third positions.

4)Name LIKE   'a_%_%'
Finds any values that start with a and are at least 3 characters in length.

5)Name LIKE '%a'
Finds any values that end with a.

6)Name LIKE '_a%b'
Finds any values that have a in the second position and end with a b.

Here , we have created a table and inserted data to the table for example purpose.

CREATE TABLE student

(

         id INT NOT NULL,

         name VARCHAR (500) NOT NULL,

         age INT NOT NULL,

         adress VARCHAR (500) NOT NULL,

         date_of_birth DATETIME NOT NULL,

         height DECIMAL(10,2) NOT NULL

)

INSERT INTO student(id,name,age,adress,date_of_birth,height)

SELECT 1,'John1',12,'34,student street','01/08/2018',10.5

UNION

SELECT 1,'John2',13,'35,student street','02/08/2018',11

UNION

SELECT 1,'John3',14,'36,student street','03/08/2018',11.5

UNION

SELECT 1,'John4',15,'37,student street','04/08/2018',12

UNION

SELECT 1,'John5',16,'38,student street','05/08/2018',12.5

UNION

SELECT 1,'John6',17,'39,student street','06/08/2018',13.5

UNION

SELECT 1,'John7',18,'40,student street','07/08/2018',14

UNION

SELECT 1,'John8',19,'41,student street','08/08/2018',14.5

UNION

SELECT 1,'John9',20,'42,student street','09/08/2018',15

UNION

SELECT 1,'John10',21,'43,student street','10/08/2018',15.5

UNION

SELECT 1,'John11',22,'44,student street','11/08/2018',16

Example 1

SELECT name

         FROM student

                              WHERE name LIKE 'J%'

name

John1

John10

John11

John2

John3

John4

John5

John6

John7

John8

John9

Example 2

 

SELECT name

        FROM student

                          WHERE name LIKE '%h%'

 name

John1

John10

John11

John2

John3

John4

John5

John6

John7

John8

John9

Example 3

SELECT name

            FROM student

                          WHERE name LIKE '%n8'

name

John8

Example 4

 

SELECT name

FROM student

WHERE name LIKE 'j_%_%'

name

John1

John10

John11

John2

John3

John4

John5

John6

John7

John8

John9

Example 5

 

SELECT name

         FROM student

                        WHERE name LIKE '%7'

name

John7

Example 6

SELECT name

FROM student

WHERE name LIKE '_oh%6'

name

John7