select练习

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