第五章：
3.（1）Create view t1(sno,sname,sdept,cno,cname,credit)
As
Select s.sno ,s.sname,s.dept,c.cno,c.cname,c.credit
From student s join course c on s.sno=c.sno
(2)Create view t2(sno,sname,cname,grade)
As
Select s.sno,s.sname,c.cname,c.grade
From student s join course c on s.sno=c.cno
（3）Create view t3(sno,選課門數)
As
Select s.sno ,count(*) 選課門數
From student s LEFT OUTER join sc on s.sno=sc.sno
group by s.sno
（4）Create view t4 (sno,總學分)
As
Select sno , sum(credit)
From sc
Where grade>=60
Group by sno
（5）Create view t5(sno,sname,VB考試成績)
As
Select top 1 with ties s.sno,s.sname ,sc.grade
From student s join sc on s.sno=sc.sno join course c on c.cno=sc.cno
Where s.dept=’計算機系’and cname=’vb’
Order by sc.grade DESC
4.（1）Select sname,cname,grade
from t2
where grade>=90
（2）Select sno,選課門數 from t3 where 選課門數 >3
（3）Select sno,選課門數
from t3 join student on t3.sno=student.so
where sdept=’計算機系’and 選課門數 >3
（4）Select sno,sname,sdept,總學分
From t4 join student on student.sno=t4.sno
Where 總學分 >10
（5）Select sname,sage,sdept,總學分
From t4 join student on student.sno=t4.sno
Where 總學分 >10 and age >=20
5.lter view t4 (sno,總學分，總的選課門數)
As
Select sno , sum(credit)， count(*) 選課門數
From sc join course on sc.cno=course.cno
Where grade>=60
Group by sno
6.Alter view t5(sno,sname,sdept,VB考試成績)
As
Select top 1 with ties s.sno,s.sname ,s.sdept ,sc.grade
From student s join sc on s.sno=sc.sno join course c on c.cno=sc.cno
Where cname=’vb’
Order by sc.grade DESC