Course, Class, Students, Exams, Quizes, and Tests SQL Server Schema Example

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²' )

INSERT INTO tbl_Choices
    ( QuestionID, [Choice] )
        VALUES
    (@QuestionID, '9')

INSERT INTO tbl_Choices
    ( QuestionID, [Choice] )
        VALUES
    (@QuestionID, '1/3 x 3³')

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