sql server 15存储过程

-- 1.创建存储过程 p_teacher_dept，用于查询指定院系的所有教师信息（输入参数 @tdept）。
create proc p_teacher_dept
@tdept VARCHAR(10)
as
select * from Teacher
where Tdept=@tdept

-- 2.执行存储过程 p_teacher_dept,查询'计算机系'教师信息。
exec p_teacher_dept '计算机系'

-- 3.创建存储过程 p_course，用于模糊查询课程名称关键字的课程信息（输入参数 @cname）。
create proc p_course
@cname VARCHAR(20)
as
select * from Course
where Cname like @cname

-- 4.执行存储过程 p_course,查询课程名称中包含'数'的课程信息。
exec p_course '%数%'

-- 5.创建存储过程 p_stu_sc，用于统计指定学生（姓名）的、
--选课门数和平均成绩（输入参数@sname,输出参数@c_count/@avg_grade）。

create proc p_stu_sc
@sname VARCHAR(10),@c_count int output,@avg_grade int output
as
    select @c_count=COUNT(*),@avg_grade=AVG(SC.Grade)from  Student S join SC on S.Sno=SC.Sno
    where Sname=@sname
    
-- 6.执行存储过程 p_stu_sc,打印输出'刘晨'同学选修课程的统计信息,形如'xx 同学，选课门数为：xx，
--平均成绩为：xx'（变量名@sname/@c_count/@avg_grade,变量赋值使用 SET命令）。
declare @sname VARCHAR(10),@c_count int ,@avg_grade int
set @sname='刘晨'
exec p_stu_sc @sname,@c_count output,@avg_grade output
print @sname+' 同学，选课门数为：'+cast(@c_count as varchar)+'，平均成绩为：'+cast(@avg_grade as varchar)

--  7.创建存储过程 p_dept_sex，用于统计指定院系和性别的学生"人数"（输入参数 @sdept/@ssex）。
-- 如果不指定参数，则返回'计算机系'的'男'学生人数。
create proc p_dept_sex
@sdept VARCHAR(10)='计算机系',@ssex CHAR(2)='男'
as
select COUNT(*) 人数 from Student
where Sdept=@sdept and Ssex=@ssex

-- 8.执行存储过程 p_dept_sex，只明确指定参数'女'，返回院系院系学生信息。
exec p_dept_sex @ssex='女'
EXEC p_dept_sex DEFAULT,'女'

--9.查看存储过程 p_stu_sc 的参数和数据类型。
sp_help p_stu_sc

--10.查看存储过程 p_stu_sc 的源代码。
sp_helptext p_stu_sc

--11.删除存储过程 p_stu_sc。

drop proc p_stu_sc

创建库输入基础数据

IF EXISTS (SELECT name FROM SYS.DATABASES WHERE name='学生选课')
    DROP DATABASE 学生选课
GO
CREATE DATABASE 学生选课
GO
USE 学生选课
-- DROP TABLE SC,course,student
-- DROP TABLE course
GO

--创建教师表
CREATE TABLE Teacher(
Tno CHAR(3) NOT NULL PRIMARY KEY,
Tname VARCHAR(10) NOT NULL,
Tsex CHAR(2),
Sbirthday DATE,
Prof VARCHAR(6),
Tdept VARCHAR(10)
)
GO

--创建学生表
CREATE TABLE Student(
Sno CHAR(5) NOT NULL PRIMARY KEY,
Sname VARCHAR(10) NOT NULL,
Ssex CHAR(2),
Sage TINYINT,
Sdept VARCHAR(10)
)
GO

--创建课程表
CREATE TABLE Course(
Cno CHAR(3) NOT NULL PRIMARY KEY,
Cname VARCHAR(20) NOT NULL,
Cpno CHAR(3),
Credit TINYINT,
Semester TINYINT,
Tno CHAR(3)
)
GO

--创建选课表
CREATE TABLE SC(
Sno CHAR(5) NOT NULL,
Cno CHAR(3) NOT NULL,
Grade INT
)
GO

-- 插入教师表数据
-- SP_HELP Teacher
INSERT INTO Teacher 
VALUES ('104','李诚','男','1958/12/2','副教授','计算机系')
,('256','张旭','男','1969/3/12','讲师','数学系')
,('125','王萍','女','1972/5/5','助教','计算机系')
,('331','刘冰','女','1977/8/14','助教','信息系')
GO    
-- SELECT * FROM Teacher

-- 插入学生表数据
-- SP_HELP Student
INSERT INTO Student 
VALUES('95001','刘超华','男',22,'计算机系')
,('95002','刘晨','女',21,'信息系')
,('95003','王敏','女',20,'数学系')
,('95004','张海','男',23,'数学系')
,('95005','陈平','男',21,'数学系')
,('95006','陈斌斌','男',28,'数学系')
,('95007','刘德虎','男',24,'数学系')
,('95008','刘宝祥','男',22,'计算机系')
,('95009','吕翠花','女',26,'计算机系')
,('95010','马盛','男',23,'数学系')
,('95011','吴霞','男',22,'计算机系')
,('95012','马伟','男',22,'数学系')
,('95013','陈冬','男',18,'信息系')
,('95014','李小鹏','男',22,'计算机系')
,('95015','王娜','女',23,'信息系')
,('95016','胡萌','女',23,'计算机系')
,('95017','徐晓兰','女',21,'计算机系')
,('95018','牛川','男',22,'信息系')
,('95019','孙晓慧','女',23,'信息系')
GO
-- SELECT * FROM Student

-- 插入课程表数据
-- SP_HELP Course
INSERT INTO Course 
VALUES('101','数据库','105',5,4,'125')
,('104','操作系统','106',1,2,'104')
,('105','数据结构','107',1,3,'104')
,('106','数据处理',NULL,1,2,'331')
,('107','C语言','106',3,1,'125')
,('108','Java',NULL,3,3,'256')
,('109','网页制作',NULL,2,5,'125')
,('110','c++',NULL,3,4,'104')
,('111','网络编程',NULL,2,5,'104')
,('201','高等数学',NULL,1,1,'256')
,('301','信息系统','101',1,3,'331')
GO
-- SELECT * FROM Course WHERE Cpno IS NULL

-- 插入选课表数据
--SP_HELP SC
INSERT INTO SC 
VALUES('95001','101',87)
,('95001','201',76)
,('95001','301',79)
,('95001','104',80)
,('95001','105',81)
,('95001','106',82)
,('95001','107',67)
,('95002','101',89)
,('95002','201',81)
,('95004','101',83)
,('95004','201',56)
,('95005','101',89)
,('95006','101',54)
,('95006','201',77)
,('95010','101',56)
,('95013','301',80)
,('95013','105',90)
,('95014','201',NULL)
,('95015','201',NULL)
GO
--SELECT * FROM SC