SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
DECLARE @Show bit = 1
CREATE TABLE [tbl_Courses](
[CourseID] [int] IDENTITY(11,1) NOT NULL
CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED ([CourseID]),
[Course] [nvarchar](50) NOT NULL
CONSTRAINT [UQ_Course] UNIQUE NONCLUSTERED ([Course])
)
CREATE TABLE tbl_Users (
UserID int not null identity(222,1) CONSTRAINT PK_User PRIMARY KEY (UserID),
[Name] nvarchar(20),
Student bit CONSTRAINT DF_Student DEFAULT (1),
Teacher bit CONSTRAINT DF_Teacher DEFAULT (0)
)
CREATE TABLE tbl_CourseStudents (
CourseStudentID int not null identity(333,1)
CONSTRAINT PK_CourseStudent PRIMARY KEY (CourseStudentID),
UserID int CONSTRAINT FK_CourseStudents_U FOREIGN KEY ([UserID])
REFERENCES tbl_Users([UserID]),
[CourseID] [int] CONSTRAINT FK_CourseStudents_C FOREIGN KEY ([CourseID])
REFERENCES tbl_Courses([CourseID])
)
CREATE TABLE [dbo].[tbl_Quizes](
[QuizID] [int] IDENTITY(4444,1) NOT NULL
CONSTRAINT [PK_Quiz] PRIMARY KEY CLUSTERED ([QuizID]),
[Quiz] [nvarchar](50) NOT NULL
CONSTRAINT [UQ_Quiz] UNIQUE NONCLUSTERED ([Quiz]),
[CourseID] [int] NOT NULL
CONSTRAINT FK_Quiz_Course FOREIGN KEY ([CourseID])
REFERENCES tbl_Courses([CourseID])
)
CREATE TABLE [dbo].[tbl_Questions](
[QuestionID] [int] IDENTITY(55555,1) NOT NULL
CONSTRAINT [PK_Question] PRIMARY KEY CLUSTERED ([QuestionID]),
[Question] [nvarchar](50) NOT NULL
CONSTRAINT [UQ_Question] UNIQUE NONCLUSTERED ([Question]),
[QuizID] [int] NOT NULL
CONSTRAINT FK_Question_Quiz FOREIGN KEY ([QuizID])
REFERENCES tbl_Quizes([QuizID]),
[Points] tinyint --how much is answering this question worth?
)
CREATE TABLE [dbo].[tbl_Choices](
[QuestionID] [int] NOT NULL
CONSTRAINT FK_Choice_Question FOREIGN KEY ([QuestionID])
REFERENCES tbl_Questions([QuestionID]),
[ChoiceID] [int] IDENTITY(666666,1) NOT NULL
CONSTRAINT [PK_Choice] PRIMARY KEY CLUSTERED ([ChoiceID]),
[Choice] [nvarchar](50) NOT NULL
CONSTRAINT [UQ_Choice] UNIQUE NONCLUSTERED ([QuestionID], [Choice]),
[Correct] bit NULL
)
CREATE TABLE tbl_Responses(
[UserID] int
CONSTRAINT FK_Response_User_U FOREIGN KEY (UserID)
REFERENCES tbl_Users(UserID),
[QuestionID] int not null
CONSTRAINT FK_User_Question_A FOREIGN KEY ([QuestionID])
REFERENCES [dbo].[tbl_Questions]([QuestionID]),
[ChoiceID] int null,
[Response] nvarchar(max) null, -- text response
[Points] smallint -- tinyint only goes up to 255
)
GO
CREATE PROCEDURE StudentsInCourse @CourseID int
AS
SELECT c.CourseID, Course, u.UserID, [Name] AS [Student]
FROM tbl_CourseStudents cs
INNER JOIN tbl_Courses c ON cs.CourseID = c.CourseID
INNER JOIN tbl_Users u ON cs.UserID = u.UserID
WHERE Student = 1 AND cs.CourseID = @CourseID
RETURN @@ROWCOUNT
GO
CREATE PROCEDURE StudentAnswer_Set @UserID int, @QuestionID int, @ChoiceID int
AS
SET NOCOUNT ON
if not exists(select 1 from dbo.tbl_Responses
where UserID = @UserID AND QuestionID = @QuestionID)
insert into dbo.tbl_Responses
(UserID, QuestionID, ChoiceID)
values
(@UserID, @QuestionID, @ChoiceID)
else
UPDATE dbo.tbl_Responses
SET ChoiceID = @ChoiceID
WHERE UserID = @UserID AND QuestionID = @QuestionID
RETURN 0
RETURN @@ROWCOUNT
GO
CREATE PROCEDURE StudentQuizResponses_Get
@UserID int,
@QuizID int
AS
DECLARE @Name varchar(20)
SELECT @Name = Name
FROM tbl_Users
WHERE UserID = @UserID
SELECT
c.Course,
z.QuizID,
z.Quiz
FROM tbl_Quizes z
INNER JOIN tbl_Courses c on z.CourseID = c.CourseID
WHERE z.QuizID = @QuizID
SELECT
u.UserID,
u.Name,
q.QuestionID,
q.Question,
c.Choice AS [Student Choice],
[Correct Choice] = (SELECT Choice
FROM tbl_Choices
WHERE QuestionID = q.QuestionID
AND Correct = 1),
CASE WHEN r.ChoiceID = (SELECT ChoiceID
FROM tbl_Choices
WHERE QuestionID = q.QuestionID
AND Correct = 1)
THEN (SELECT [Points]
FROM tbl_Questions
WHERE QuestionID = q.QuestionID )
ELSE 0
END AS [Points]
FROM
tbl_Users u INNER JOIN
tbl_Responses r ON r.UserID = u.UserID
INNER JOIN tbl_Questions q ON q.QuestionID = r.QuestionID
INNER JOIN tbl_Choices c ON c.ChoiceID = r.ChoiceID
WHERE u.UserID = @UserID AND QuizID = @QuizID
UNION
SELECT
@UserID,
@Name,
q.QuestionID,
q.Question,
'',
Choice,
0
FROM
tbl_Questions q
INNER JOIN tbl_Choices c ON c.QuestionID = q.QuestionID
INNER JOIN tbl_Quizes z ON q.QuizID = z.QuizID
WHERE c.Correct=1
AND z.QuizID = @QuizID
AND c.QuestionID NOT IN (
SELECT q.QuestionID
FROM tbl_Questions q
LEFT OUTER JOIN tbl_Responses r ON r.QuestionID = q.QuestionID
WHERE QuizID = @QuizID AND UserID = @UserID)
RETURN 0
GO
DECLARE
@CourseID int,
@QuizID int,
@QuestionID int,
@ChoiceID int,
@Show bit = 1
INSERT tbl_Users (Name)
VALUES ('Pascal'), ('Bernoulli'), ('Arostitle'), ('Heinz Ketchupberg'), ('Fritz Rommel'),
('Abdul'), ('Achmed'), ('Sadik'), ('Machmoud')
INSERT [tbl_Courses] (Course)
SELECT 'Koranic Studies';
SET @CourseID = SCOPE_IDENTITY()
INSERT into tbl_CourseStudents (UserID, CourseID)
VALUES
(225, @CourseID),
(227, @CourseID),
(228, @CourseID),
(229, @CourseID),
(230, @CourseID)
-- Sadik is the Professor
UPDATE tbl_Users SET Teacher=1, Student=0 WHERE UserID = 229
IF (@Show = 1)
EXEC StudentsInCourse @CourseID
INSERT [tbl_Courses] (Course)
SELECT 'German';
SET @CourseID = SCOPE_IDENTITY()
INSERT into tbl_CourseStudents (UserID, CourseID)
VALUES
(225, @CourseID),
(226, @CourseID)
IF (@Show = 1)
EXEC StudentsInCourse @CourseID
INSERT [tbl_Quizes]
([CourseID], [Quiz])
VALUES
(@CourseID, 'Pop Quiz 1: Accusative, Nomative, Dative')
SET @QuizID = SCOPE_IDENTITY()
INSERT [tbl_Questions]
( [QuizID], [Question], Points )
VALUES
( @QuizID, 'Wie heißt du?', 1)
INSERT [tbl_Questions]
( [QuizID], Question, [Points] )
VALUES
( @QuizID, 'Ich bin ein __________', 1)
INSERT [tbl_Questions]
( [QuizID], [Question], [Points] )
VALUES
( @QuizID, 'Does an Audi or Porsche handle best?', 5)
INSERT [tbl_Courses] (Course)
SELECT 'Algebra'; SET @CourseID = SCOPE_IDENTITY()
INSERT into tbl_CourseStudents (UserID, CourseID)
VALUES
(222, @CourseID),
(223, @CourseID),
(224, @CourseID)
IF (@Show = 1)
EXEC StudentsInCourse @CourseID
INSERT [tbl_Quizes]
([CourseID], [Quiz])
VALUES
(@CourseID, 'Test 1: Reciprocals and Squares')
SET @QuizID = SCOPE_IDENTITY()
IF (@Show = 1)
SELECT c.CourseID, q.QuizID, q.Quiz
FROM tbl_Quizes q
INNER JOIN tbl_Courses c
ON q.CourseID = c.CourseID
WHERE QuizID = @QuizID
INSERT [tbl_Questions]
( [QuizID], [Question], Points )
VALUES
( @QuizID, 'What is the square root of 81?', 5)
SET @QuestionID = SCOPE_IDENTITY()
IF (@Show = 1)
SELECT c.CourseID, q.QuizID, ask.QuestionID, ask.Question, ask.Points
FROM tbl_Quizes q
INNER JOIN tbl_Courses c
ON q.CourseID = c.CourseID
INNER JOIN tbl_Questions ask
ON q.QuizID = ask.QuizID
WHERE q.CourseID = @CourseID
-- use HTML superscript tag <sup> to indicate squared
INSERT INTO tbl_Choices
( [QuestionID], [Choice] )
VALUES
(@QuestionID, '3<sup>2</sup>' )
INSERT INTO tbl_Choices
( QuestionID, [Choice] )
VALUES
(@QuestionID, '9')
INSERT INTO tbl_Choices
( QuestionID, [Choice] )
VALUES
(@QuestionID, '1/3 x 3<sup>3</sup>')
INSERT INTO tbl_Choices
( QuestionID, [Choice], [Correct] )
VALUES
(@QuestionID, 'All of the Above', 1)
INSERT INTO tbl_Choices
( QuestionID, [Choice], [Correct] )
VALUES
(@QuestionID, 'None of the Above', 0)
IF (@Show = 1)
SELECT c.CourseID, q.QuizID, ask.QuestionID, which.ChoiceID, which.Choice, which.Correct
FROM tbl_Quizes q
INNER JOIN tbl_Courses c
ON q.CourseID = c.CourseID
INNER JOIN tbl_Questions ask
ON q.QuizID = ask.QuizID
INNER JOIN tbl_Choices which
ON ask.QuestionID = which.QuestionID
INSERT [tbl_Questions]
( [QuizID], [Question], [Points] )
VALUES
( @QuizID, 'What is the reciprocal of one-half?', 5)
SET @QuestionID = SCOPE_IDENTITY()
IF (@Show = 1)
SELECT c.CourseID, q.QuizID, ask.QuestionID, ask.Question
FROM tbl_Quizes q
INNER JOIN tbl_Courses c
ON q.CourseID = c.CourseID
INNER JOIN tbl_Questions ask
ON q.QuizID = ask.QuizID
WHERE QuestionID = @QuestionID
INSERT INTO tbl_Choices
( QuestionID, [Choice] )
VALUES
(@QuestionID, '1/4')
INSERT INTO tbl_Choices
( QuestionID, [Choice], [Correct] )
VALUES
(@QuestionID, '2', 1)
INSERT INTO tbl_Choices
( [QuestionID], Choice )
VALUES
(@QuestionID, '4')
INSERT INTO tbl_Choices
( QuestionID, [Choice], Correct )
VALUES
(@QuestionID, 'None of the Above', NULL)
IF (@Show = 1)
SELECT c.CourseID, q.QuizID, ask.QuestionID, which.ChoiceID, which.Choice, which.Correct
FROM tbl_Quizes q
INNER JOIN tbl_Courses c
ON q.CourseID = c.CourseID
INNER JOIN tbl_Questions ask
ON q.QuizID = ask.QuizID
INNER JOIN tbl_Choices which
ON ask.QuestionID = which.QuestionID
WHERE ask.QuestionID = @QuestionID
/* Use AJAX to Asynchronously Post Choice Selected to database */
-- Here Pascal (UserID = 222)
-- answers the Square Root of 81 (QuestionID = 55558)
-- with Choice All of the Above (ChoiceID = 4)
EXEC StudentAnswer_Set @UserID = 222, @QuestionID = 55558, @ChoiceID = 666669
-- reciprocal of 1/2
EXEC StudentAnswer_Set @UserID = 222, @QuestionID = 55559, @ChoiceID = 666672
-- show test results
EXEC StudentQuizResponses_Get @UserID = 222, @QuizID = 4445
--Bernoulli
EXEC StudentAnswer_Set @UserID = 223, @QuestionID = 55559, @ChoiceID = 666671
EXEC StudentQuizResponses_Get @UserID = 223, @QuizID = 4445
GO
DROP TABLE dbo.tbl_Responses
GO
DROP TABLE dbo.tbl_Choices
GO
DROP TABLE dbo.tbl_Questions
GO
DROP TABLE dbo.tbl_Quizes
GO
DROP TABLE tbl_CourseStudents
GO
DROP TABLE dbo.tbl_Users
GO
DROP TABLE dbo.tbl_Courses
GO
DROP PROCEDURE dbo.StudentsInCourse
GO
DROP PROCEDURE dbo.StudentAnswer_Set
GO
DROP PROCEDURE StudentQuizResponses_Get
![](\"images/loading.gif\")
");
Posts
