April 22, 2012 at 7:58 am
.Netter (4/22/2012)
There will only be one Exam which is the one in question at the moment, When users come in for an interview they will sit this exam!
Ah, now things make sense. I thought this was for an educational institute.
If there is only one exam, there will only ever be one exam and there will never be more than 36 questions, your original design is sufficient. If this was a full-blown exam system for an educational institute, then you would need a far more complex design than you have.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2012 at 8:05 am
Im sorry i should of gone in to more detail would of saved the confusion,
Iv looked at the sample data in the post you gave me but im confused im not strong in SQL but i am planning on going on a course next month to build my experience.
How can i get my Sample Data Across to you?
April 22, 2012 at 8:17 am
After following the example i got this as an output
SELECT [Question1],[7]
SELECT [Question2],[16]
SELECT [Question3],[24]
SELECT [Question4],[2]
SELECT [Question5],[N]
SELECT [Question6],[5]
SELECT [Question7],[4]
SELECT [Question8],[4]
SELECT [Question9],[15]
SELECT [Question10],[2]
SELECT [Question11],[a]
SELECT [Question12],[140]
SELECT [Question13],[3]
SELECT [Question14],[131]
SELECT [Question15],[111]
SELECT [Question16],[2]
SELECT [Question17],[2]
SELECT [Question18],[5]
SELECT [Question19],[5]
SELECT [Question20],[4]
SELECT [Question21],[2]
SELECT [Question22],[5]
SELECT [Question23],[16]
SELECT [Question24],[3]
SELECT [Question25],[H]
SELECT [Question26],[1]
SELECT [Question27],[0]
SELECT [Question28],[3]
SELECT [Question29],[9]
SELECT [Question30],[1]
SELECT [Question31],
SELECT [Question32],[140]
SELECT [Question33],[2]
SELECT [Question34],[5]
SELECT [Question35],[2]
SELECT [Question36],[25]
April 22, 2012 at 9:04 am
No, that's not going to work. Run that and you'll see. Read carefully over the article, it does show you how to pull sample data.
I'll write up an 8 question example, you can extrapolate to your large one.
CREATE TABLE Exam_CorrectAnswers (
QuestionNumber INT NOT NULL PRIMARY KEY,
CorrectAnswer VARCHAR(50)
);
CREATE TABLE User_ExamResults (
UserName VARCHAR(20) NOT NULL,
QuestionNumber INT NOT NULL FOREIGN KEY REFERENCES Exam_CorrectAnswers (QuestionNumber),
UserAnswer VARCHAR(50),
CONSTRAINT pk_ExamResults PRIMARY KEY (UserName,QuestionNumber)
)
INSERT INTO Exam_CorrectAnswers (QuestionNumber, CorrectAnswer)
VALUES
(1,'7'),
(2,'16'),
(3,'24'),
(4,'2'),
(5,'N'),
(6,'5'),
(7,'4'),
(8,'4');
INSERT INTO User_ExamResults (UserName, QuestionNumber, UserAnswer)
VALUES
('Mark',1,'7'),
('Mark',2,'5'),
('Mark',3,'24'),
('Mark',4,'2'),
('Mark',5,'Y'),
('Mark',6,'5'),
('Mark',7,'2'),
('Mark',8,'4');
INSERT INTO User_ExamResults (UserName, QuestionNumber, UserAnswer)
VALUES
('Jeremiah',1,NULL),
('Jeremiah',2,'16'),
('Jeremiah',3,'4'),
('Jeremiah',4,'1'),
('Jeremiah',5,'N'),
('Jeremiah',6,'9'),
('Jeremiah',7,'0'),
('Jeremiah',8,'4');
Right, two candidates, two sets of results. As an aside, if this was going to be for a proper application, not just a rough data storage, we'd keep the user details in another table.
To get the number of correct answers for each:
SELECT UserName ,
SUM(CASE WHEN UserAnswer = CorrectAnswer THEN 1
ELSE 0
END) AS TotalCorrect
FROM dbo.User_ExamResults AS er
INNER JOIN dbo.Exam_CorrectAnswers AS ea ON er.QuestionNumber = ea.QuestionNumber
GROUP BY UserName
p.s. the query and the table designs will work no matter how many questions there are. I just didn't feel like typing up 26 lines for each insert.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2012 at 9:35 am
Thank you for the information i really do appreciate it, one more question though i have the insert statement which will put all the informatio in to the examResults table which is as follows
INSERT INTO [OnlineT20Exam].[dbo].[ExamResults]
(UserID ,Question1 ,Question2 ,Question3 ,Question4 ,Question5 ,Question6 ,Question7 ,Question8
,Question9 ,Question10 ,Question11 ,Question12 ,Question13 ,Question14 ,Question15 ,Question16
,Question17 ,Question18 ,Question19 ,Question20 ,Question21 ,Question22 ,Question23 ,Question24
,Question25 ,Question26 ,Question27 ,Question28 ,Question29 ,Question30 ,Question31 ,Question32
,Question33 ,Question34 ,Question35 ,Question36 ,Result)
VALUES
(@UserID, @Question1, @Question2, @Question3, @Question4, @Question5, @Question6, @Question7, @Question8,
@Question9, @Question10, @Question11, @Question12, @Question13, @Question14, @Question15, @Question16,
@Question17, @Question18, @Question19, @Question20, @Question21, @Question22, @Question23, @Question24,
@Question25, @Question26, @Question27, @Question28, @Question29, @Question30, @Question31, @Question32,
@Question33, @Question34, @Question35, @Question36, @Result)
END
iv tried putting the example you have given me after the values but it throws an error so why i have decided to do is put the snippet you gave me in a function then call it after the insert would that be ok?
CREATE FUNCTION CalculateUserScore
(@UserID int)
RETURNS int
AS
BEGIN
SELECT @user-id,
SUM(CASE WHEN UserAnswer = CorrectAnswer THEN 1
ELSE 0
END) AS TotalCorrect
FROM dbo.User_ExamResults AS er
INNER JOIN dbo.Exam_CorrectAnswers AS ea ON er.QuestionNumber = ea.QuestionNumber
GROUP BY UserName
-- Return the result of the function
RETURN TotalCorrect
END
So ill pass the userid in, then do the select on the UserID would this be ideal or would it reduce the performance?
April 22, 2012 at 9:49 am
Why do you need a function at all?
btw, that function will throw errors and not do what you want even if it did compile. To filter requires a where clause.
Msg 444, Level 16, State 2, Procedure CalculateUserScore, Line 6
Select statements included within a function cannot return data to a client.
Msg 207, Level 16, State 1, Procedure CalculateUserScore, Line 15
Invalid column name 'TotalCorrect'.
Your insert is for the old design, not the new.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2012 at 10:01 am
I was under the impression i was keeping the ExamResults (Where the users answer go) the same after what you mentioned a few posts back, where you said the design will be sufficient?
So shall i change the ExamResults to just have two columns im confused :-/
April 22, 2012 at 10:11 am
Either change them both or change neither, changing one and not the other makes the query close to impossible and makes no sense at all.
The sample query I gave you assumed (based on the sample data you posted) that you had changed the table design.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2012 at 10:21 am
Ok iv changed it now so there both basically identical,
But my select statement has red lines under it, am i correct in saying you cant have a select statement within an insert method?
ALTER PROCEDURE [dbo].[UserFinishedExam]
@user-id int,
@Question1 varchar(15),
@Question2 varchar(15),
@Question3 varchar(15),
@Question4 varchar(15),
@Question5 varchar(15),
@Question6 varchar(15),
@Question7 varchar(15),
@Question8 varchar(15),
@Question9 varchar(15),
@Question10 varchar(15),
@Question11 varchar(15),
@Question12 varchar(15),
@Question13 varchar(15),
@Question14 varchar(15),
@Question15 varchar(15),
@Question16 varchar(15),
@Question17 varchar(15),
@Question18 varchar(15),
@Question19 varchar(15),
@Question20 varchar(15),
@Question21 varchar(15),
@Question22 varchar(15),
@Question23 varchar(15),
@Question24 varchar(15),
@Question25 varchar(15),
@Question26 varchar(15),
@Question27 varchar(15),
@Question28 varchar(15),
@Question29 varchar(15),
@Question30 varchar(15),
@Question31 varchar(15),
@Question32 varchar(15),
@Question33 varchar(15),
@Question34 varchar(15),
@Question35 varchar(15),
@Question36 varchar(15),
@Result int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO [OnlineT20Exam].[dbo].[ExamResults]
(UserID ,QuestionNumber ,UserAnswer)
VALUES
(@UserID, 1, @question1),
(@UserID, 2, @question2),
(@UserID, 3, @question3),
(@UserID, 4, @question4),
(@UserID, 5, @question5),
(@UserID, 6, @question6),
(@UserID, 7, @question7),
(@UserID, 8, @question8),
(@UserID, 9, @question9),
(@UserID, 10, @question10),
(@UserID, 11, @question11),
(@UserID, 12, @question12),
(@UserID, 13, @question13),
(@UserID, 14, @question14),
(@UserID, 15, @question15),
(@UserID, 16, @question16),
(@UserID, 17, @question17),
(@UserID, 18, @question18),
(@UserID, 19, @question19),
(@UserID, 20, @question20),
(@UserID, 21, @question21),
(@UserID, 22, @question22),
(@UserID, 23, @question23),
(@UserID, 24, @question24),
(@UserID, 25, @question25),
(@UserID, 26, @question26),
(@UserID, 27, @question27),
(@UserID, 28, @question28),
(@UserID, 29, @question29),
(@UserID, 30, @question30),
(@UserID, 31, @question31),
(@UserID, 32, @question32),
(@UserID, 33, @question33),
(@UserID, 34, @question34),
(@UserID, 35, @question35),
(@UserID, 36, @question36);
SELECT UserID,
SUM(CASE WHEN UserAnswer = CorrectAnswer THEN 1
ELSE 0
END) AS TotalCorrect
FROM dbo.User_ExamResults AS er
INNER JOIN dbo.Exam_CorrectAnswers AS ea ON er.QuestionNumber = ea.QuestionNumber
GROUP BY UserName
END
April 22, 2012 at 10:34 am
You don't have a select within an insert. You have an insert followed by a select.
That said, you haven't changed the table names in the select to match your tables. You've left them with the names I made up.
Run the alter proc. If there are errors it will fail. If it fails post the errors. The red lines often appear if the intellisense is out of date (which it always is after a table has been created or altered)
p.s. You might want a where clause in that select to filter on just the user you inserted data for. As it stands, you'll get back everyone.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2012 at 11:00 am
Right iv changed everything you have mentioned, surprised i missed it, its been a long day... but never mind, right anyway iv changed the procedure now all the red lines have gone and it compiles fine
ALTER PROCEDURE [dbo].[UserFinishedExam]
@user-id int,
@Question1 varchar(15),
@Question2 varchar(15),
@Question3 varchar(15),
@Question4 varchar(15),
@Question5 varchar(15),
@Question6 varchar(15),
@Question7 varchar(15),
@Question8 varchar(15),
@Question9 varchar(15),
@Question10 varchar(15),
@Question11 varchar(15),
@Question12 varchar(15),
@Question13 varchar(15),
@Question14 varchar(15),
@Question15 varchar(15),
@Question16 varchar(15),
@Question17 varchar(15),
@Question18 varchar(15),
@Question19 varchar(15),
@Question20 varchar(15),
@Question21 varchar(15),
@Question22 varchar(15),
@Question23 varchar(15),
@Question24 varchar(15),
@Question25 varchar(15),
@Question26 varchar(15),
@Question27 varchar(15),
@Question28 varchar(15),
@Question29 varchar(15),
@Question30 varchar(15),
@Question31 varchar(15),
@Question32 varchar(15),
@Question33 varchar(15),
@Question34 varchar(15),
@Question35 varchar(15),
@Question36 varchar(15)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO [OnlineT20Exam].[dbo].[ExamResults]
(UserID ,QuestionNumber ,UserAnswer)
VALUES
(@UserID, 1, @question1),
(@UserID, 2, @question2),
(@UserID, 3, @question3),
(@UserID, 4, @question4),
(@UserID, 5, @question5),
(@UserID, 6, @question6),
(@UserID, 7, @question7),
(@UserID, 8, @question8),
(@UserID, 9, @question9),
(@UserID, 10, @question10),
(@UserID, 11, @question11),
(@UserID, 12, @question12),
(@UserID, 13, @question13),
(@UserID, 14, @question14),
(@UserID, 15, @question15),
(@UserID, 16, @question16),
(@UserID, 17, @question17),
(@UserID, 18, @question18),
(@UserID, 19, @question19),
(@UserID, 20, @question20),
(@UserID, 21, @question21),
(@UserID, 22, @question22),
(@UserID, 23, @question23),
(@UserID, 24, @question24),
(@UserID, 25, @question25),
(@UserID, 26, @question26),
(@UserID, 27, @question27),
(@UserID, 28, @question28),
(@UserID, 29, @question29),
(@UserID, 30, @question30),
(@UserID, 31, @question31),
(@UserID, 32, @question32),
(@UserID, 33, @question33),
(@UserID, 34, @question34),
(@UserID, 35, @question35),
(@UserID, 36, @question36);
SELECT @user-id,
SUM(CASE WHEN UserAnswer = Answer THEN 1
ELSE 0
END) AS TotalCorrect
FROM dbo.ExamResults AS er
INNER JOIN dbo.Correct_ExamAnswers AS ea ON er.QuestionNumber = ea.Question
Where er.UserID = @user-id
END
This is my Correct_ExamAmswer table
CREATE TABLE [dbo].[Correct_ExamAnswers](
[Question] [varchar](15) NULL,
[Answer] [varchar](15) NULL
) ON [PRIMARY]
This is my ExamResults table
CREATE TABLE [dbo].[ExamResults](
[UserID] [int] NOT NULL,
[QuestionNumber] [int] NOT NULL,
[UserAnswer] [varchar](15) NULL,
[Result] [int] NULL,
When i Execute the procedure i get this error
'Msg 2627, Level 14, State 1, Procedure UserFinishedExam, Line 50
Violation of PRIMARY KEY constraint 'PK_ExamResult'. Cannot insert duplicate key in object 'dbo.ExamResults'.
The statement has been terminated.
(1 row(s) affected)'
Iv checked the ExamResults table and there is nothing in it!???
April 22, 2012 at 11:19 am
Well given that neither table you posted even has a primary key, I'm going to assume you left something out...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2012 at 11:22 am
Sorry iv just sorted that issue out its now populating the table with the information 🙂
But it falls over with the part you provided
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value 'Question1' to data type int.
SELECT er.UserID,
SUM(CASE WHEN UserAnswer = Answer THEN 1
ELSE 0
END) AS TotalCorrect
FROM dbo.ExamResults AS er
INNER JOIN dbo.Correct_ExamAnswers AS ea ON er.QuestionNumber = ea.Question
Where er.UserID = @user-id
group by er.UserID
April 22, 2012 at 11:30 am
Sorry iv sorted that issue out now! i had a field which was an int and the other which was varchar,
It now runs with no errors but it returns nothing?
April 22, 2012 at 11:37 am
As a wild guess (because you've provided no data), you have '1' in one table and 'Question1' in the other?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply