视图的建立（sql）

1. 定义计算机系学生基本情况视图V_Computer；

CREATE VIEW V_COMPUTER

AS

SELECT *

FROM student

WHEREsdept='is'

 

 

2. 将Student Course 和sc表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G

CREATE VIEW V_S_C_G

AS

SELECT

student.sno,student.sname,course.cno,course.cname,grade

FROMstudent,course,sc

 

WHEREstudent.sno=sc.sno

 

3. 将各系学生人数，平均年龄定义为视图V_NUM_AVG

create view V_NUM_AVG(number,avg,sdept)

    as

    select count(sno)deptnum,avg(sage)age,sdept

    from student

    group by sdept

 

4. 将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G

create view V_AVG_S_G(学号,姓名,选修门数,平均成绩)

as

selectstudent.sno ,sname,count(*),avg(grade)

fromstudent,course,sc

wherestudent.sno=sc.sno and sc.cno=course.cno

group by student.sno,sname

 

5. 各门课程的选修人数及平均成绩定义为视图V_AVG_C_G

CREATE VIEW V_AVG_C_G(课程号,平均成绩,选修人数)

AS

SELECTSC.CNO,AVG(GRADE),COUNT(CNO)

FROMSC

GROUP BY CNO