SQL语句（十九）——存储过程（练习）

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(40), @Y varchar(40)
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(40)
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(10)
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 = '5'


--(4)X课程得最高分的学生的姓名、性别、所在系(P4)
--执行P4，X=数据库
GO
Create Proc LessonToStudentInfo @X varchar(40)
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(40)
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(40) = '数据库' --默认值
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(40)
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 = '程老师'