第五章：
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
标签：sname,join,sno,数据库,student,sc,Select From： https://www.cnblogs.com/yindantong/p/18248345