select * From Studentselect * From Courseselect * from SC--INSERT INTO SC (Sno, Cno, Grade)--VALUES (‘1513032087‘, ‘7‘, 99);--(1)找出至少选修X课和Y课的学生学号（P1）-- 执行P1，输出X=数据库，Y=数据结构GOCreate Proc SelectLessonToSno @X varchar(40), @Y varchar(40)ASbeginselect SC.Snofrom SC, Coursewhere SC.Cno = Course.Cnoand SC.Cno in (Select SC.Cno               From SC, Course               Where SC.Cno = Course.Cno and                 Cname in (@X, @Y)               )ENDEXEC SelectLessonToSno @X =  ‘数据库‘, @Y =  ‘数据结构‘--(2)找出至少选修X老师讲的一门课的学生姓名(P2)--执行P2，X=程老师GOCreate Proc TnameToSname @Tname varchar(40)ASBEGINSelect distinct SnameFrom Student,Course,SCwhere Student.Sno = SC.Sno and     Course.Cno = SC.Cno and     SC.Sno in (select Sno               from SC, Course               where SC.Cno = Course.Cno and                    Course.Tname = @Tname               )ENDEXEC TnameToSname @Tname = ‘程老师‘        --(3)查询X号课程得最高分的学生的学号(P3)--执行P3，X=数据库对应的课程号GOCreate Proc maxofCno @X varchar(10)ASBEGINselect SnoFrom SCWhere SC.Cno = @X and Grade =                       (select MAX(Grade)                       From SC                       Group by Cno                       Having Cno = @X                       )ENDEXEC maxofCno @X = ‘5‘--(4)X课程得最高分的学生的姓名、性别、所在系(P4)--执行P4，X=数据库GOCreate Proc LessonToStudentInfo @X varchar(40)ASBEGINSelect Sname, Ssex, SdeptFrom Student, SC, CourseWhere Student.Sno = SC.Sno and SC.Cno = Course.Cno and    Course.Cname = @X and Grade =                         (                        Select MAX(Grade)                        from SC, Course                        where SC.Cno = Course.Cno and                            Course.Cname = @X                        )ENDEXEC LessonToStudentInfo @X = ‘数据库‘--(5)取出没有选修X课程的学生姓名和年龄(P5)--执行P5，X=数据库GOCreate Proc SelectNoLessonToStudentInfo @X varchar(40)ASBEGINSelect Sname, SageFrom Student, SC, Coursewhere Student.Sno = SC.Sno and       Course.Cno = SC.Cno and        SC.Sno not in (                  Select Sno                   from SC, Course                  where SC.Cno = Course.Cno and Course.Cname = @X                  )ENDEXEC SelectNoLessonToStudentInfo @X = ‘数据库‘--(6)求选修课程名为X的学生的平均年龄(P6)--执行P6，X=数据库GOCreate Proc LessonToStudentAge @X varchar(40) = ‘数据库‘ --默认值ASBEGINSelect AVG(Sage)From Student, Course, SCWhere Student.Sno = SC.Sno and       Course.Cno = SC.Cno and      Course.Cname = @XENDEXEC LessonToStudentAge @X = ‘数据库‘--(7)求X老师讲的每门课的学生平均成绩(P7)--执行P7，X=程老师GOCreate Proc LessonToAvage @X varchar(40)ASBEGINSelect SC.Cno, AVG(Grade) AS 平均分From Course, SCWhere Course.Cno = SC.Cno and      Course.Tname = @XGroup by SC.CnoENDExec LessonToAvage @X = ‘程老师‘