CREATE DATABASE 在線考試系統(tǒng)
ON PRIMARY
(NAME =examol_dat,
   FILENAME ='e:\mssql\data\examoll.mdf',
   SIZE = 10,
   MAXSIZE =50,
   FILEGROWTH =5)
LOG ON 
(NAME=examol_log,
   FILENAME ='e:\mssql\log\examoll.ldf',
   SIZE = 10MB,
   MAXSIZE =25MB,
   FILEGROWTH =5MB) 

use 在線考試系統(tǒng)

CREATE TABLE 考生信息表(
    考生號 CHAR(8) NOT NULL PRIMARY KEY CHECK(ISNUMERIC(考生號)=1),
    姓名 CHAR(10),
    班級 VARCHAR(8) CHECK(ISNUMERIC(班級)=1),
    系別 VARCHAR(10),
    分?jǐn)?shù) FLOAT NULL )

CREATE TABLE 題庫表1(
    出題時間 DATETIME,
    考試時間 DATETIME,
    題庫號 int,
    科目 varchar(10)primary key,
    題號 INT CHECK(ISNUMERIC(題號)=1),
    題型 CHAR(10),
    題目 VARCHAR(100) NOT NULL,
    此題分?jǐn)?shù) FLOAT CHECK(ISNUMERIC(此題分?jǐn)?shù))=1), 
    正確答案 CHAR(10))

CREATE TABLE 題庫表2(
    出題時間 DATETIME,
    考試時間 DATETIME,
    題庫號 int,
    科目 varchar(10)primary key,
    題號 INT CHECK(ISNUMERIC(題號)=1),
    題型 CHAR(10),
    題目 VARCHAR(100) NOT NULL,
    此題分?jǐn)?shù) FLOAT CHECK(ISNUMERIC(此題分?jǐn)?shù))=1), 
    正確答案 CHAR(10))

CREATE TABLE 考試試題表(
    題庫號 int,
    科目 varchar(10),
    題號 INT CHECK(ISNUMERIC(題號)=1),
    題型 CHAR(10),
    題目 VARCHAR(100) NOT NULL,
    此題分?jǐn)?shù) FLOAT CHECK(ISNUMERIC(此題分?jǐn)?shù))=1),
    考生答案 CHAR(10))


ALTER TABLE 題庫表1 ADD 考生答案 CHAR(10)
ALTER TABLE 題庫表2 ADD 考生答案 CHAR(10)
ALTER TABLE 考生信息表 ADD 考試總時間 INT
ALTER TABLE 考生信息表 DROP COLUMN 考試總時間  

INSERT INTO 考生信息表(考生號,姓名,班級,系別)values('05112301','ding','051123','軟件')
INSERT INTO 考生信息表(考生號,姓名,班級,系別)values('05112302','dong','051123','軟件')
INSERT INTO 考生信息表(考生號,姓名,班級,系別)values('05112303','fan','051123','軟件')
INSERT INTO 考生信息表(考生號,姓名,班級,系別)values('05112304','hu','051123','軟件')
INSERT INTO 考生信息表(考生號,姓名,班級,系別)values('05112305','huang','051123','軟件')
INSERT INTO 考生信息表(考生號,姓名,班級,系別)values('05112306','jiang','051123','軟件')
INSERT INTO 考生信息表(考生號,姓名,班級,系別)values('05112307','jin','051123','軟件')
INSERT INTO 考生信息表(考生號,姓名,班級,系別)values('05112308','li','051123','軟件')
INSERT INTO 考生信息表(考生號,姓名,班級,系別)values('05112320','zhang','051123','軟件')

create view v_s1 as
select  考生號,姓名,班級,系別,分?jǐn)?shù)
from 考生信息表
select *
from v_s1

create view v_s2 as
select  科目,題號,題型,題目,此題分?jǐn)?shù),正確答案
from 題庫表1
select *
from v_s2

create view v_s3 as
select  科目,題號,題型,題目,此題分?jǐn)?shù),正確答案
from 題庫表2
select *
from v_s3

create view v_s4 as
select  科目,題號,題型,題目,此題分?jǐn)?shù),考生答案
from 考試試題表
select *
from v_s4


create trigger NEWSTUDENT
on 考生信息表
for insert 
as print'插入一個考生信息！'

insert into 考生信息表(考生號,姓名,班級,系別)values('05112313','din','051123','軟件')

create trigger NEWSUBJECT1
on 題庫表1
for insert 
as print'插入一個新題目！'

create trigger NEWSUBJECT2
on 題庫表2
for insert 
as print'插入一個新題目！'

create trigger 考生號管理
on 考生信息表 for insert
as 
if not exists(select*from 考生信息表 where 考生號=(select 考生號 from inserted))
begin
  raiserror('不能重復(fù)學(xué)號！',1,1)
  rollback transaction
end

create trigger 題號管理1
on 題庫表1 for insert
as 
if not exists(select*from 題庫信息 where 題號=(select 題號 from inserted))
begin
  raiserror('不能重復(fù)題號！',1,1)
  rollback transaction
end

create trigger 題號管理2
on 題庫表2 for insert
as 
if not exists(select*from 題庫信息 where 題號=(select 題號 from inserted))
begin
  raiserror('不能重復(fù)題號！',1,1)
  rollback transaction
end



/*------------------------------------------------------------------------------------*/

CREATE PROCedure 考生信息核對_登陸;1
(@number int=null)
as
IF @number IS NULL
BEGIN
   PRINT'您必須填寫考生號，否則無法進(jìn)入在線考試系統(tǒng)!'
   RETURN 13
END
IF NOT EXISTS(SELECT * FROM 考生信息表 WHERE 考生號=@number)
BEGIN
   PRINT'無此考生！'
   RETURN -103
END
IF EXISTS (SELECT * FROM 考生信息表 WHERE 考生號=@number)
BEGIN
   PRINT'順利進(jìn)入在線考試系統(tǒng)！' 
END

execute 考生信息核對_登陸;1 05112316

execute 考生信息核對_登陸;1 05112320
/*--------------------------------------------------------------------------------*/
create procedure 選擇科目;1
(@sub varchar(10)=null)
as
if @sub=null
begin
print '請輸入您要考試的科目!'
return 13
end
if not exists (select * from 題庫1  
				where 科目=@sub)
begin
print'請輸入正確的考試科目!'
return -103
end
update  考試試題表 set 科目=(@sub)
execute 試題發(fā)配 @sub

begin
print '選擇成功...現(xiàn)在開始考試!'
select top 1 題號,科目,題型,題目 from 考試試題表 where 考生答案 is null  order by 題型,題號
return 14
end
return 0
/*----------------------------------------------------------------------------------*/
create procedure 試題發(fā)配;1
(@sub2 varchar(10)=null)
as
declare @i int,@j int,@k int
set @k=1
set @i=1
while(@i<=15)
begin
set @j=(select top 1 題號 from 題庫1 
		where 科目=@sub2 
		order by newid())
if  not exists (select*from 考試試題表 where 題號=@j  )
begin
insert into 考試試題表(題庫號,題號,科目,題型,題目,此題分?jǐn)?shù),考生答案)
 select 題庫號,題號,科目,題型,題目,此題分?jǐn)?shù),考生答案  from 題庫1 where 題號=@j
update 考試試題表 
set 題庫號 =@k
set @i=@i+1
end
else
continue
end

declare @m int,@n int
set @m=1

while(@m<=15)
begin
set @n=(select top 1 試題號 from 題庫2 
			where 科目=@sub2 
			order by newid())
if  not exists (select*from 考試試題表 where 題號=@n  )
begin
insert into 考試試題表(題庫號,題號,科目,題型,題目,此題分?jǐn)?shù),考生答案)
 select 題庫號,題號,科目,題型,題目,此題分?jǐn)?shù),考生答案  from 題庫2  where   題號=@n
update 考試試題表 
set 題庫號 =@k
set @m=@m+1
end
else
continue
end
set @k=@k+1
return 0

/*---------------------------------------------------------------------------------*/

CREATE PROCedure 考生信息;1
AS
IF EXISTS(SELECT * FROM 考生信息表 WHERE 考生號='05112320')
BEGIN 
PRINT '該考生已經(jīng)進(jìn)入考試系統(tǒng)，不能退出再次進(jìn)入系統(tǒng)!'
RETURN 13
END

execute 考生信息;1 

/*------------------------------------------------------------------------------------*/

CREATE PROCedure 考試時間管理;1
(@timer1 DATETIME=NULL,@timer2 DATETIME=NULL)
AS
IF EXISTS(SELECT * FROM 考生信息表 WHERE 考試總時間<= DATEDIFF(HOUR,@timer1,@timer2))
BEGIN
PRINT'時間已經(jīng)到了，必須交卷。不能再做啦！'
RETURN -103
END

execute 考試時間管理;1 '10:00:00.000','12:00:00.000 '

/*------------------------------------------------------------------------------------*/
CREATE PROCedure 考卷管理;1
AS
IF EXISTS(SELECT * FROM 考試試題表)
BEGIN
PRINT '該考生已經(jīng)得到考卷，不能重發(fā)!'
RETURN 13
END
PRINT '打開考試試題，可以開始答卷啦！'
execute 考卷管理;1

/*------------------------------------------------------------------------------------*/
create procedure 答題要求;1
 as 
if exists ( select* from  考試試題表 where 考生答案 != 'A' or 考生答案 != 'B'or 考生答案 != 'C' or 考生答案 != 'D' )
begin
  raiserror('請重新選擇ABCD之一',1,1)
  
end
execute 答題要求;1
/*--------------------------------------------------------------------------------*/
create procedure 答題;1
(@ans varchar(5)=null)
as 
begin
update 試卷
set 考生作答答案=@ans
where 題號=(select top 1 題號 from 考試試題表 where 考生答案 is null  order by 題型,題號)
select 題號,科目,題型,題目,考生答案 from 考試試題表 where 考生答案 is not null order by 題型,題號
select top 1 題號,科目,題型,題目 from 考試試題表  where 考生答案 is null order by 題型,題號
end
return 0 
/*---------------------------------------------------------------------------------*/
CREATE PROCedure 評分系統(tǒng);1
AS
DECLARE @i INT,@totalmarks INT
SELECT @i=1
SELECT @totalmarks=0
WHILE(@i<=100)
BEGIN
IF EXISTS(SELECT * FROM 考試試題表 WHERE 題號=@i)
BEGIN 
IF EXISTS(SELECT * FROM 題庫信息表 WHERE 題號=@i and 正確答案 IN(SELECT 考生答案 FROM 考試試題表 WHERE 題號=@i))
BEGIN
SELECT @totalmarks=@totalmarks+2
END
end
SELECT @i=@i+1
END
PRINT'此學(xué)生的總分為:'
PRINT @totalmarks
UPDATE 考生信息表
SET 分?jǐn)?shù)=@totalmarks
WHERE 考生號='05112301'
SELECT * FROM 考生信息表

execute 評分系統(tǒng);1


--備份整個數(shù)據(jù)庫
create PROCEDURE 在線考試系統(tǒng)back_up;1
as
backup database 在線考試系統(tǒng)
to disk='e:\mssql\db在線考試.bak'



--增量備份
backup database 在線考試系統(tǒng)
to disk='e:\mssql\add_db在線考試.bak'
with differential

execute  在線考試系統(tǒng)back_up;1


--恢復(fù)數(shù)據(jù)庫，存儲過程
create PROCEDURE db_recovery;1
as

--全備份的恢復(fù)
restore database 在線考試系統(tǒng)
from disk='e:\mssql\db在線考試.bak'
with norecovery


--增量備份恢復(fù)
restore database 在線考試系統(tǒng)
from disk='e:\mssql\add_db在線考試.bak'
with norecovery

execute db_recovery;1

   
/*-------------------------------------------------------------------------------------------*/