select * From Student

select * From Course

select * from SC

--INSERT INTO SC (Sno, Cno, Grade)

--VALUES ('1513032087', '7', 99);

--(1)找出至少选修X课和Y课的学生学号（P1）

-- 执行P1，输出X=数据库，Y=数据结构

GO

Create Proc SelectLessonToSno @X varchar(), @Y varchar()

AS

begin

select SC.Sno

from SC, Course

where SC.Cno = Course.Cno

and SC.Cno in (Select SC.Cno

               From SC, Course

               Where SC.Cno = Course.Cno and

                Cname in (@X, @Y)

               )

END

EXEC SelectLessonToSno @X =  '数据库', @Y =  '数据结构'

--(2)找出至少选修X老师讲的一门课的学生姓名(P2)

--执行P2，X=程老师

GO

Create Proc TnameToSname @Tname varchar()

AS

BEGIN

Select distinct Sname

From Student,Course,SC

where 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

               )

END

EXEC TnameToSname @Tname = '程老师'

--(3)查询X号课程得最高分的学生的学号(P3)

--执行P3，X=数据库对应的课程号

GO

Create Proc maxofCno @X varchar()

AS

BEGIN

select Sno

From SC

Where SC.Cno = @X and Grade =

                      (select MAX(Grade)

                       From SC

                       Group by Cno

                       Having Cno = @X

                       )

END

EXEC maxofCno @X = ''

--(4)X课程得最高分的学生的姓名、性别、所在系(P4)

--执行P4，X=数据库

GO

Create Proc LessonToStudentInfo @X varchar()

AS

BEGIN

Select Sname, Ssex, Sdept

From Student, SC, Course

Where 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

                        )

END

EXEC LessonToStudentInfo @X = '数据库'

--(5)取出没有选修X课程的学生姓名和年龄(P5)

--执行P5，X=数据库

GO

Create Proc SelectNoLessonToStudentInfo @X varchar()

AS

BEGIN

Select Sname, Sage

From Student, SC, Course

where 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

                  )

END

EXEC SelectNoLessonToStudentInfo @X = '数据库'

--(6)求选修课程名为X的学生的平均年龄(P6)

--执行P6，X=数据库

GO

Create Proc LessonToStudentAge @X varchar() = '数据库' --默认值

AS

BEGIN

Select AVG(Sage)

From Student, Course, SC

Where Student.Sno = SC.Sno and

      Course.Cno = SC.Cno and

      Course.Cname = @X

END

EXEC LessonToStudentAge @X = '数据库'

--(7)求X老师讲的每门课的学生平均成绩(P7)

--执行P7，X=程老师

GO

Create Proc LessonToAvage @X varchar()

AS

BEGIN

Select SC.Cno, AVG(Grade) AS 平均分

From Course, SC

Where Course.Cno = SC.Cno and

      Course.Tname = @X

Group by SC.Cno

END

Exec LessonToAvage @X = '程老师'