CREATE DATABASE [NetQuiz]  ON (NAME = N'NetQuiz_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\NetQuiz_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'NetQuiz_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\NetQuiz_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'NetQuiz', N'autoclose', N'true'
GO

exec sp_dboption N'NetQuiz', N'bulkcopy', N'false'
GO

exec sp_dboption N'NetQuiz', N'trunc. log', N'true'
GO

exec sp_dboption N'NetQuiz', N'torn page detection', N'true'
GO

exec sp_dboption N'NetQuiz', N'read only', N'false'
GO

exec sp_dboption N'NetQuiz', N'dbo use', N'false'
GO

exec sp_dboption N'NetQuiz', N'single', N'false'
GO

exec sp_dboption N'NetQuiz', N'autoshrink', N'true'
GO

exec sp_dboption N'NetQuiz', N'ANSI null default', N'false'
GO

exec sp_dboption N'NetQuiz', N'recursive triggers', N'false'
GO

exec sp_dboption N'NetQuiz', N'ANSI nulls', N'false'
GO

exec sp_dboption N'NetQuiz', N'concat null yields null', N'false'
GO

exec sp_dboption N'NetQuiz', N'cursor close on commit', N'false'
GO

exec sp_dboption N'NetQuiz', N'default to local cursor', N'false'
GO

exec sp_dboption N'NetQuiz', N'quoted identifier', N'false'
GO

exec sp_dboption N'NetQuiz', N'ANSI warnings', N'false'
GO

exec sp_dboption N'NetQuiz', N'auto create statistics', N'true'
GO

exec sp_dboption N'NetQuiz', N'auto update statistics', N'true'
GO

use [NetQuiz]
GO

CREATE TABLE [dbo].[tbl_admin] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[username] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[password] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_options] (
	[Option_id] [int] IDENTITY (1, 1) NOT NULL ,
	[Question_id] [int] NOT NULL ,
	[Option_Value] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[is_answer] [bit] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_questions] (
	[Question_id] [int] IDENTITY (1, 1) NOT NULL ,
	[Question] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_admin] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbl_admin] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tbl_options] WITH NOCHECK ADD 
	CONSTRAINT [DF_tbl_options_is_answer] DEFAULT (0) FOR [is_answer],
	CONSTRAINT [PK_tbl_options] PRIMARY KEY  CLUSTERED 
	(
		[Option_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tbl_questions] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbl_questions] PRIMARY KEY  CLUSTERED 
	(
		[Question_id]
	)  ON [PRIMARY] 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_GetQuestionCount

AS

	SELECT COUNT(Question_ID) FROM Tbl_Questions
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_SelectAllQuestions

AS

	SELECT * FROM tbl_questions
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_SelectOptionsByID
(
	@question_id int
)
AS
	SELECT * FROM tbl_options WHERE question_id = @question_id
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_createQuestion
(
	@Question varchar(255),
	@Option1 varchar(50),
	@Option2 varchar(50),
	@Option3 varchar(50),
	@Option4 varchar(50),
	@isAnswer int,
	@ReturnValue int output
)
AS
	DECLARE @Qid int
	SET @Qid = 0
	SET @ReturnValue = 0

		INSERT INTO tbl_questions VALUES ( @Question )
		SET SET @Qid = @@IDENTITY
	
		IF (@isAnswer = 1)
			BEGIN
				INSERT INTO tbl_options ( Question_Id, Option_value, is_answer)	VALUES (@Qid, @Option1,1)
			END
		ELSE
			BEGIN
				INSERT INTO tbl_options ( Question_Id, Option_value, is_answer)	VALUES (@Qid, @Option1,0)
			END
		IF (@isAnswer = 2)
			BEGIN
				INSERT INTO tbl_options ( Question_Id, Option_value, is_answer)	VALUES (@Qid, @Option2,1)
			END
		ELSE
			BEGIN
				INSERT INTO tbl_options ( Question_Id, Option_value, is_answer)	VALUES (@Qid, @Option2,0)
			END
		IF (@isAnswer = 3)
			BEGIN
				INSERT INTO tbl_options ( Question_Id, Option_value, is_answer)	VALUES (@Qid, @Option3,1)
			END
		ELSE
			BEGIN
				INSERT INTO tbl_options ( Question_Id, Option_value, is_answer)	VALUES (@Qid, @Option3,0)
			END
		IF (@isAnswer = 4)
			BEGIN
				INSERT INTO tbl_options ( Question_Id, Option_value, is_answer)	VALUES (@Qid, @Option4,1)
			END
		ELSE
			BEGIN
				INSERT INTO tbl_options ( Question_Id, Option_value, is_answer)	VALUES (@Qid, @Option4,0)
			END

		SET @ReturnValue = 1
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_deleteQuestionAndOptions
(
	@qid int,
	@ReturnValue int output
)
AS
	SET @ReturnValue = 0
		
	IF EXISTS(SELECT Question_ID FROM  tbl_questions WHERE Question_ID =  @qid)
		BEGIN
			DELETE FROM tbl_questions WHERE Question_ID =  @qid
			SET @ReturnValue = 1
		END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_getCorrectAnswers

AS

	SELECT * FROM tbl_options WHERE is_Answer = 1
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_getOptionDetailsByID
(
	@qid int
)
AS
	
	SELECT * FROM tbl_options WHERE question_id = @qid
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_getQuestionDetailsByID
(
	@qid int
)
AS
	
	SELECT * FROM tbl_questions WHERE question_id = @qid
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_updateQuestionDetails
(
	@qid int,
	@Question varchar(255),
	@Option1 varchar(50),
	@Option2 varchar(50),
	@Option3 varchar(50),
	@Option4 varchar(50),
	@Option1ID int,
	@Option2ID int,
	@Option3ID int,
	@Option4ID int,
	@isAnswer int,
	@ReturnValue int out
)
AS

	SET @ReturnValue = 0

		UPDATE tbl_questions SET Question = @Question WHERE question_id = @qid
		
		IF (@isAnswer = 1)
			BEGIN
				UPDATE tbl_options SET Option_value = @Option1, is_answer = 1 WHERE Option_ID = @Option1ID
				UPDATE tbl_options SET Option_value = @Option2, is_answer = 0 WHERE Option_ID = @Option2ID
				UPDATE tbl_options SET Option_value = @Option3, is_answer = 0 WHERE Option_ID = @Option3ID
				UPDATE tbl_options SET Option_value = @Option4, is_answer = 0 WHERE Option_ID = @Option4ID
			END
		
		IF (@isAnswer = 2)
			BEGIN
				UPDATE tbl_options SET Option_value = @Option1, is_answer = 0 WHERE Option_ID = @Option1ID
				UPDATE tbl_options SET Option_value = @Option2, is_answer = 1 WHERE Option_ID = @Option2ID
				UPDATE tbl_options SET Option_value = @Option3, is_answer = 0 WHERE Option_ID = @Option3ID
				UPDATE tbl_options SET Option_value = @Option4, is_answer = 0 WHERE Option_ID = @Option4ID
			END

		IF (@isAnswer = 3)
			BEGIN
				UPDATE tbl_options SET Option_value = @Option1, is_answer = 0 WHERE Option_ID = @Option1ID
				UPDATE tbl_options SET Option_value = @Option2, is_answer = 0 WHERE Option_ID = @Option2ID
				UPDATE tbl_options SET Option_value = @Option3, is_answer = 1 WHERE Option_ID = @Option3ID
				UPDATE tbl_options SET Option_value = @Option4, is_answer = 0 WHERE Option_ID = @Option4ID
			END
		
		IF (@isAnswer = 4)
			BEGIN
				UPDATE tbl_options SET Option_value = @Option1, is_answer = 0 WHERE Option_ID = @Option1ID
				UPDATE tbl_options SET Option_value = @Option2, is_answer = 0 WHERE Option_ID = @Option2ID
				UPDATE tbl_options SET Option_value = @Option3, is_answer = 0 WHERE Option_ID = @Option3ID
				UPDATE tbl_options SET Option_value = @Option4, is_answer = 1 WHERE Option_ID = @Option4ID
			END
		

		SET @ReturnValue = 1
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_validateAdmin
(
	@username varchar(25),
	@Password varchar(25),
	@ReturnValue int output
)
AS
	SET @ReturnValue = 0

	IF EXISTS(SELECT [ID] FROM tbl_admin WHERE Username = @username AND [Password] = @Password )
		BEGIN
			SET @ReturnValue = 1
		END
	ELSE
		BEGIN
			SET @ReturnValue = 2
		END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE TRIGGER AutoDelete ON [dbo].[tbl_questions] 
FOR DELETE 
	
	
AS
	DECLARE @qid int
	SET @qid = (SELECT Question_id FROM deleted)
	DELETE FROM tbl_options WHERE question_id = @qid
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO