SQL view syntax and simple example

Syntax for creating views on SQL Server:

Create View

 

[< Database_name >.] [< Owner >.]

 

View_name [(column [,... n])]

 

[< View_attribute >[,... N]

 

As

 

Select_statement

[With check option]

 

< View_attribute >: =

 

{ENCRYPTION | SCHEMABINDING |

 

VIEW_METADATA}

Example 1: Create S_view1 that displays student ID, name, gender, and other information older than 20 years old

Create view S_view1

 

As

 

Select sno, sname, sex from s where age>20

Example 2: Create v_score1. The source of the basic table is S, C, SC. The selected fields are sno and sname in Table S, and the cname in Table C and score in Table SC; the data to be queried is the score of the students whose student number is 20030001.

Use s

 

Create view v_score1

 

As

 

Select s. sno, s. sname, c. cname, SC. score

 

From s, c, SC

 

Where s. sno = SC. sno and c. cno = SC. cno and sno = "20030001"

Execute the above program in the query analyzer to generate the view v_score1. To view data in the view, enter the following statement in the query Analyzer:

Select * from v_score1.