Sql | Teacher teaching management system（function & stored procedure & subquery and other examples）

一、设计并创建数据库（ ），保存与教师授课有关的信息，包括：教师基本信息、课程基本信息、班级信息、授课信息等，设置各表的完整性约束条件（如主键、外键等）。录入以上信息。

1、设计并创建数据库（ ）

create database DB_teacher;

2、保存与教师授课有关的信息，包括：教师基本信息、课程基本信息、班级信息、授课信息等，设置各表的完整性约束条件（如主键、外键等）

create table teacher
(
	id int not null Identity(1,1) primary key,
	tname varchar(20) not null,
	tsex varchar(2) not null,
	tphone varchar(22) not null,
	tclass varchar(20) not null,
	tteaching varchar(50) not null,
	ttime int not null
);

3、录入以上信息（手动输入）

二、创建函数（ ），根据输入课程名称和班级得出该课程的授课教师。

创建一个名叫select_teacher(
@teaching_name varchar(20),
@teaching_class varchar(20)
)函数

-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		ZeroIce
-- Create date: Name & Class
-- Description:	Enter the course name and class to find the instructor of the course
-- =============================================
CREATE FUNCTION select_teacher
(
	@teaching_name varchar(20),
	@teaching_class varchar(20)
)
RETURNS varchar(20)
AS
BEGIN
	DECLARE  @name varchar(20) 
	SELECT @name = tname from teacher WHERE tclass = @teaching_class and  tteaching = @teaching_name
	RETURN @name
END
GO

测试：

三、创建存储过程（ ），根据系名查询出该系所有总课时数超过300的教师。

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		ZeroIce
-- Create date: Dpart
-- Description:	Find out the teachers whose total class hours exceed 300 according to the department name
-- =============================================
CREATE PROCEDURE GetSumTeacher
	@Dpart VARCHAR(20)
AS
BEGIN
	SELECT tname AS '姓名' , @Dpart AS '系名' , SUM(ttime) AS '总课时' 
	FROM dbo.teacher 
	WHERE tdpart = @Dpart  
	GROUP BY  tname 
	HAVING SUM(ttime) > 300
END
GO

测试：

四、创建存储过程（ ），根据输入教师号显示该教师的授课信息，并统计教授的课程门数、班级数和总课时数等。

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		ZeroIce
-- Create date:	Phone
-- Description:	According to the input teacher number display the teacher's teaching information, and statistics the number of courses taught, classes and total class hours.
-- =============================================
CREATE PROCEDURE GetEveryTeacher
	@Phone VARCHAR(20)
AS
BEGIN
	SELECT tname AS '姓名' , tteaching AS '课程' , COUNT(*) AS '班级数' ,SUM(ttime) AS  '总课时' 
	FROM dbo.teacher  
	WHERE tphone = @Phone
	GROUP BY tteaching , tname
END
GO

测试：