6月11日学习进度

一.所花时间

1h

二.代码量

60行

三.博客量

1篇

四.了解到的知识点

第五章

3.(1)create view v1 as select s.sno,s.sname,s.sdept,c.cno,c.cname,c.credit from Student s join SC on s.sno = SC.sno join Course c on c.cno = SC.cno;

(2)create view v2 as select s.sno,s,sname,c.cname,SC.grade from Student s join SC on s.sno = SC.sno join Course c on c.cno = SC.cno;

(3)create view v3 as select sno,count(*) as 选课门数 from SC group by sno;

(4)create view v4 as select SC.sno,sum(c.credit) as 总学分 from SC join Course c on c.cno = SC.cno where grade >= 60 group by SC.sno;

(5)create view v5 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 SC.cno = c.cno where s.sdept = ‘计算机系’ and c.cname = ‘Java’ order by grade desc;

4.(1)select sname,cname,grade from v2 where grade >= 90;

(2)select * from v3 where 选课门数 >= 3;

(3)select sname,选课门数 from v3 join Student s on s.sno =v3.sno where s.sdept = ‘计算机系’ and v3.选课门数 >= 3;

(4)select v4.sno,s.sname,s,sdept,v4.总学分from v4 join Student s on s.sno = v4.sno where  v4.总学分 >= 10;

5.alter view v4 as select SC.sno,sum(c.credit) as 总学分,sum(SC.*) as 选课门数 from SC join Course c on c.cno = SC.cno group by SC.sno;

6.alter view v5 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 SC.cno = c.cno where c.cname = ‘Java’ order by grade desc;