SQL语句（十九）——存储过程（练习）
时间：2021-07-01 10:21:17
帮助过：5人阅读
* 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 = ‘程老师‘
SQL语句（十九）——存储过程（练习）
标签：info distinct 学生 默认值 int begin pre logs char
