数据库期末考源代码

USE master;
IF DB_ID('EduBase') IS NOT NULL
	BEGIN
		ALTER DATABASE EduBase
			SET SINGLE_USER
			WITH ROLLBACK IMMEDIATE;
		DROP DATABASE EduBase;
	END
CREATE DATABASE EduBase							
	ON						
		(NAME='DataFile_1'					
		,FILENAME='E:\EduBase\DataFile_1.mdf')					
	LOG ON						
		(NAME='LogFile_1'					
		,FILENAME='E:\EduBase\LogFile_1.ldf');	
		
CREATE TABLE tb_Department
	(No
		INT
		NOT NULL
		PRIMARY KEY
	,Name
		VARCHAR(20)
		NOT NULL)
INSERT tb_Department  
	(No,Name)
	VALUES
	(1,'管理学院')
	,(2,'中医学院');
	
CREATE TABLE tb_Major
	(No
		INT
		NOT NULL
		PRIMARY KEY
	,Name
		VARCHAR(20)
		NOT NULL
	,DepartmentNo
		INT
		NOT NULL)
INSERT tb_Major
	(No,Name,DepartmentNo)
	VALUES
	(1,'卫管',1)
	,(2,'信管',1)
	,(3,'中医',2)
	,(4,'临床',2);
	
CREATE TABLE tb_Class
	(No
		INT
		NOT NULL
		PRIMARY KEY
	,Name
		VARCHAR(20)
		NOT NULL
	,MajorNo
		INT
		NOT NULL)
		
INSERT tb_Class
	(No,Name,MajorNo)
	VALUES
	(1,'17卫管',1)
	,(2,'17信管',2)
	,(3,'17中医',3)
	,(4,'17临床',4);	
	
CREATE TABLE tb_Student
	(No
		CHAR(10)
		NOT NULL
		PRIMARY KEY
	,Name
		VARCHAR(20)
		NOT NULL
	,Gender
		VARCHAR(4)
		NOT NULL
	,BirthDate
		DATE
		NULL
	,ClassNo
		INT
		NULL
		FOREIGN KEY 
			REFERENCES tb_Class(No)
	,Speciality
		VARCHAR(100)
		NULL
	);
	
INSERT tb_Student
	(No,Name,Gender,BirthDate,ClassNo,Speciality)
	VALUES
	('3170707023','何少平','男','1999-05-07',2,'敲代码')
	,('3170707024','杨颜霞','女','1997-04-07',2,'吃货')
	,('3170707026','宋务琦','男','1998-03-03',2,NULL) 
	,('3170707027','李广生','男','1998-09-15',1,NULL)
	,('3170707028','薛朝鹏','男','1998-09-18',1,NULL)
	,('3170707029','李雅婷','女','1998-04-03',1,NULL) 
	,('3170707031','林荷秀','女','1997-06-28',3,NULL)
	,('3170707032','任可欣','女','1998-09-18',3,NULL)
	,('3170707033','王昭晖','女','1998-09-19',3,NULL) 
	,('3170707034','张洁','女','1998-09-11',4,NULL)
	,('3170707036','林晨艺','女','1998-09-12',4,NULL)
	,('3170707038','许婷婷','女','1998-09-13',4,NULL) 	
	
----完整备份作业
CREATE PROCEDURE usp_Full
AS
BEGIN
 DECLARE
@FullBkDesc VARCHAR(255);  
 SELECT  
 @FullBkDesc = 'Weekly full backup for '+CONVERT(VARCHAR,GETDATE(),120);
 BACKUP DATABASE EduBase
  TO DISK='E:\EduBase\Full\Fullbackup.bak'
 WITH  
   DESCRIPTION = @FullBkDesc;
END					
						
EXEC msdb.dbo.sp_add_job								
		@job_name = 'jb_EB_FullWeekly'	
														
EXEC msdb.dbo.sp_add_jobstep								
		@job_name = 'jb_EB_FullWe