Shulou(Shulou.com)06/01 Report--

1. Create a view

(1) General format:

Create view View name

[with encryption]

As

Select statement

[with check option]

(2) define a single source table view:

Establish a view of the student number, name, gender and age of the students in the information management department

Create view is_student (studentid,studentname,sex,birth)

As

Select studentid,studentname,sex,getdate ()-birth

From student

Where sdept = 'Department of Information Management'

(3) define the view of multi-source table:

Establish a view of the student numbers, names and grades of students taking C001 courses in the information management department

Create view V_IS_S1 (studentid,studentname,grade)

As

Select s.studentid,studentname,grade

From student s

Join grade g on g.studentid = s.studentid

Where sdept = 'Department of Information Management' and g.courseid = 'C001'

(4) define a new view on an existing view:

Create a view of the student number, name and age of the students under the age of 20 in the Information Management Department on the view created on question (2).

Create view is_student_sage (studentid,studentname,birth)

As

Select studentid,studentname,getdate ()-birth

From is_student

Where getdate ()-birth > 20

(5) View with expression

Define a view of a student's year of birth, including the student number, name, and year of birth

Create view BT_s (studentid,studentname,birth)

As

Select studentid,studentname,getdate ()-birth

From student

(6) views with grouped statistics:

Define a view of each student's student number and grade point average

Create view Sigg (studentid,grade)

As

Select studentid,avg (grade)

From grade

Group by studentid

2. Modify the view:

Alter view View name

As

Query statement

Revised to count each student's test scores and the total number of courses selected.

Alter view Sigg (studentid,grade,count_coursename)

As

Select studentid,avg (grade), count (*)

From grade

Group by studentid

3. Delete the view:

Drop view View name

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.