egxis
asked on
Using SQL to select random questions for an exam
I would like a single SQL query which will return all sections for an exam id, all sections related to the exam id, and 5 random questions and their related options.
I have 4 related tables in a SQL DB, as follows:
exam
=======
id
description
section
=======
id
exam_id
description
question
=======
id
section_id
text
options
======
id
question_id
text
is_answer
I have 4 related tables in a SQL DB, as follows:
exam
=======
id
description
section
=======
id
exam_id
description
question
=======
id
section_id
text
options
======
id
question_id
text
is_answer
select top 5 s.description, q.text,o.text from section s join exam e on s.exam_id=e.id join question q on s.id=q.section_id join options o on q.id=o.question_id order by newid()
with WHERE clause
select top 5 s.description, q.text,o.text from section s join exam e on s.exam_id=e.id join question q on s.id=q.section_id join options o on q.id=o.question_id order
where e.id=5
by newid()
select top 5 s.description, q.text,o.text from section s join exam e on s.exam_id=e.id join question q on s.id=q.section_id join options o on q.id=o.question_id order
where e.id=5
by newid()
ASKER
It's a good start but I think a minor misunderstanding.
exam -< section -< question -< options
I want to return all sections [not only 5] linked to an exam, but only 5 random questions related to the current section [with all related options per question].
Thanks.
exam -< section -< question -< options
I want to return all sections [not only 5] linked to an exam, but only 5 random questions related to the current section [with all related options per question].
Thanks.
I am still not clear about your goal, can you please be more elaborate? some example data would be great.
ASKER
Ok - please find data attached. I created this in SQL Server, but I want it to be portable to other SQL DBs.
Basically, this is an exam paper.
It has numerous sections relating to the exam.
Each section needs to consist of 5 randomly selected questions [from a pool of possible questions for that exam].
For the 5 [or less] randomly selected questions, select all related options, i.e. multiple choice options.
Basically, this is an exam paper.
It has numerous sections relating to the exam.
Each section needs to consist of 5 randomly selected questions [from a pool of possible questions for that exam].
For the 5 [or less] randomly selected questions, select all related options, i.e. multiple choice options.
ASKER
I guess the data you want, won't be possible with SELECT statement only, it may need dynamic query creation.
ASKER
Really - not possible as nested queries or similar?
well, I can't check this, but have a try.
select s.description, q.text,o.text, rank() over (partition by s.description,q.text order by newid()) as r
from section s join exam e on s.exam_id=e.id join question q on s.id=q.section_id
join options o on q.id=o.question_id
where e.id=2
select s.description, q.text,o.text, rank() over (partition by s.description,q.text order by newid()) as r
from section s join exam e on s.exam_id=e.id join question q on s.id=q.section_id
join options o on q.id=o.question_id
where e.id=2
sorry, try this. forgot to keep "R" in WHERE clause.
select s.description, q.text,o.text, rank() over (partition by s.description,q.text order by newid()) as r
from section s join exam e on s.exam_id=e.id join question q on s.id=q.section_id
join options o on q.id=o.question_id
where e.id=2 and r<6
select s.description, q.text,o.text, rank() over (partition by s.description,q.text order by newid()) as r
from section s join exam e on s.exam_id=e.id join question q on s.id=q.section_id
join options o on q.id=o.question_id
where e.id=2 and r<6
ASKER
Almost there.
Except it only returns 5 questions in total [from 2 sections], rather than 5 [or less] random questions per section.
eg the same data has.
1 exam [id=2] has 3 sections [e2section1, e2section2, e2section3]
all sections need to be returned, even if they have no related questions
5 [or less] random questions and all their options relating to the section must be returned
Except it only returns 5 questions in total [from 2 sections], rather than 5 [or less] random questions per section.
eg the same data has.
1 exam [id=2] has 3 sections [e2section1, e2section2, e2section3]
all sections need to be returned, even if they have no related questions
5 [or less] random questions and all their options relating to the section must be returned
ASKER
The last query gave an error:
Invalid column name 'r'.
Invalid column name 'r'.
still not getting your second from last post, even, have a look.
select s.description, q.text,o.text, rank() over (partition by s.description,q.text order by newid()) as r
from section s left outer join exam e on s.exam_id=e.id join question q on s.id=q.section_id
join options o on q.id=o.question_id
where e.id=2
select s.description, q.text,o.text, rank() over (partition by s.description,q.text order by newid()) as r
from section s left outer join exam e on s.exam_id=e.id join question q on s.id=q.section_id
join options o on q.id=o.question_id
where e.id=2
and what about this?
select descript,quest,ans,r
from
(
select s.description as descript, q.text as quest,o.text as ans, rank() over (partition by s.description,q.text order by newid()) as r
from section s left outer join exam e on s.exam_id=e.id join question q on s.id=q.section_id
join options o on q.id=o.question_id
where e.id=2
) as t
where r<6
select descript,quest,ans,r
from
(
select s.description as descript, q.text as quest,o.text as ans, rank() over (partition by s.description,q.text order by newid()) as r
from section s left outer join exam e on s.exam_id=e.id join question q on s.id=q.section_id
join options o on q.id=o.question_id
where e.id=2
) as t
where r<6
ASKER
Ok, the result I get with the most recent query is:
5 questions and their related options. 3 for section1 and 2 for section2.
I should get 5 for section 1, 5 for section2, 0 for section3, and the questions are not randomized.
5 questions and their related options. 3 for section1 and 2 for section2.
I should get 5 for section 1, 5 for section2, 0 for section3, and the questions are not randomized.
have you used my query in post # 24374153?
ASKER
can you please check this one? actually I don't have table so can't check from my end.
select descript,quest,ans,r
from
(
select s.description as descript, q.text as quest,o.text as ans, rank() over (partition by s.description order by newid()) as r
from section s left outer join exam e on s.exam_id=e.id join question q on s.id=q.section_id
join options o on q.id=o.question_id
where e.id=2
) as t
where r<6
ASKER
Hmm, a little bit of loss on this query.
To clarify, I'd expect to see:
e2section1
- 1qx [5 random] and all related options
e2section2
- 2qx [5 random] and all related options
e2section3
- zero rows
20090513-2.png
To clarify, I'd expect to see:
e2section1
- 1qx [5 random] and all related options
e2section2
- 2qx [5 random] and all related options
e2section3
- zero rows
20090513-2.png
I know this is not what you want, but please send me full screen shot of the data after running the query I am giving below.
select t.descrip, q.text quest,o.text as ans from question q join options o on q.id=o.question_id
right outer join
(
select distinct s.id as id, s.description as descrip from
section s left outer join exam e on s.exam_id=e.id where e.id=2
) t
on q.section_id=t.id
and the screen shot of this query will help more ....
select t.descrip, q.text quest,o.text as ans, rank() over (partition by t.description order by newid()) as r
from question q join options o on q.id=o.question_id
right outer join
(
select distinct s.id as id, s.description as descrip from
section s left outer join exam e on s.exam_id=e.id where e.id=2
) as t
on q.section_id=t.id
ASKER
Getting an error:
Invalid column name 'description'.
At a glance, II can't see where you are referring to description incorrectly.
Invalid column name 'description'.
At a glance, II can't see where you are referring to description incorrectly.
ASKER
now, have a look, you may need to change field name again.
select descri,quest,ans from
(
select t.descrip as descri, q.text as quest,o.text as ans, rank() over (partition by t.description order by newid()) as r
from question q join options o on q.id=o.question_id
right outer join
(
select distinct s.id as id, s.description as descrip from
section s left outer join exam e on s.exam_id=e.id where e.id=2
) as t
on q.section_id=t.id
) as outerMost
where outerMost.r<6
ASKER
Fixed code.
Getting there, just need 5 unique, random questions per section.
Then return the related options for each question.
This is almost like a punch card system :)
20090513-4.png
Getting there, just need 5 unique, random questions per section.
Then return the related options for each question.
This is almost like a punch card system :)
20090513-4.png
now, this should give you final result, I guess.
select o2.descri,o2.quest,a.text from
(
select descri,quest,qid from
(
select t.descrip as descri, q.text as quest,q.ID as qid, rank() over (partition by t.description order by newid()) as r
from question q
right outer join
(
select distinct s.id as id, s.description as descrip from
section s left outer join exam e on s.exam_id=e.id where e.id=2
) as t
on q.section_id=t.id
) outer1
where outer1.r<5
) as o2
join options as a on o2.qid=a.question_id
ASKER
may be answer is creating problem, what is outcome of this one?
select descri,quest,qid,r from
(
select t.descrip as descri, q.text as quest,q.ID as qid, rank() over (partition by t.description order by newid()) as r
from question q
right outer join
(
select distinct s.id as id, s.description as descrip from
section s left outer join exam e on s.exam_id=e.id where e.id=2
) as t
on q.section_id=t.id
) outer1
where outer1.r<5
ASKER
Changed code to below.
It looks good, just need to drill down the answers.
It looks good, just need to drill down the answers.
select descri,quest,qid,r from
(
select t.descrip as descri, q.text as quest,q.ID as qid, rank() over (partition by t.descrip order by newid()) as r
from question q
right outer join
(
select distinct s.id as id, s.description as descrip from
section s left outer join exam e on s.exam_id=e.id where e.id=2
) as t
on q.section_id=t.id
) outer1
where outer1.r<6
ASKER
GIve this a try:
Havent tested it out, so there may be spelling errors and such like, but the underlying principle should work (I use something similar currently).
Al.
Havent tested it out, so there may be spelling errors and such like, but the underlying principle should work (I use something similar currently).
Al.
DROP table #t_question;
SELECT section_id, id, text, identity(int, 1, 1) as auto_id
INTO #t_question
FROM question
ORDER BY section_id, NEWID();
SELECT s.exam_id, s.id as s_sect_id, s.description as s_description, sqo.q_id, sqo.q_text, sqo.opt_id, sqo.opt_text, sqo.is_answer
FROM section as s LEFT JOIN
(SELECT tq.section_id, tq.id as q_id, tq.text as q_text, o.id as opt_id, o.text as opt_text, o.is_answer
FROM #t_question tq INNER JOIN
(SELECT section_id, min(auto_id) as min_auto_id
FROM #t_question
GROUP BY section_id) tqi
ON tq.section_id = tqi.section_id and tq.auto_id < tqi.min_auto_id + 5 LEFT JOIN
options As o ON tq.id = o.question_id) As sqo
ON s.id = sqo.section_id
from you last screen shot, I assume you are getting perfect question, now all we need to do is, get answer, here is the script for that.
select o2.descri,o2.quest,op.text as ans
from
(
select descri,quest,qid,r from
(
select t.descrip as descri, q.text as quest,q.ID as qid, rank() over (partition by t.descrip order by newid()) as r
from question q
right outer join
(
select distinct s.id as id, s.description as descrip from
section s left outer join exam e on s.exam_id=e.id where e.id=2
) as t
on q.section_id=t.id
) outer1
where outer1.r<6
) o2
left outer join
options o on q.id=o.question_id
had mistake in last join, look at this.
select o2.descri,o2.quest,op.text as ans
from
(
select descri,quest,qid,r from
(
select t.descrip as descri, q.text as quest,q.ID as qid, rank() over (partition by t.descrip order by newid()) as r
from question q
right outer join
(
select distinct s.id as id, s.description as descrip from
section s left outer join exam e on s.exam_id=e.id where e.id=2
) as t
on q.section_id=t.id
) outer1
where outer1.r<6
) o2
left outer join
options o on o2.qid=o.question_id
ASKER
@RiteshShah:
24376179 - The multi-part identifier "op.text" could not be bound.
@alchemyst999:
Cannot add identity column, using the SELECT INTO statement, to table '#t_question', which already has column 'id' that inherits the identity property.
24376179 - The multi-part identifier "op.text" could not be bound.
@alchemyst999:
Cannot add identity column, using the SELECT INTO statement, to table '#t_question', which already has column 'id' that inherits the identity property.
it should be o.text
select o2.descri,o2.quest,o.text as ans
from
(
select descri,quest,qid,r from
(
select t.descrip as descri, q.text as quest,q.ID as qid, rank() over (partition by t.descrip order by newid()) as r
from question q
right outer join
(
select distinct s.id as id, s.description as descrip from
section s left outer join exam e on s.exam_id=e.id where e.id=2
) as t
on q.section_id=t.id
) outer1
where outer1.r<6
) o2
left outer join
options o on o2.qid=o.question_id
Give this a try
DROP table #t_question;
SELECT section_id, id as q_id, text, identity(int, 1, 1) as auto_id
INTO #t_question
FROM question
ORDER BY section_id, NEWID();
SELECT s.exam_id, s.id as s_sect_id, s.description as s_description, sqo.q_id, sqo.q_text, sqo.opt_id, sqo.opt_text, sqo.is_answer
FROM section as s LEFT JOIN
(SELECT tq.section_id, tq.q_id as q_id, tq.text as q_text, o.id as opt_id, o.text as opt_text, o.is_answer
FROM #t_question tq INNER JOIN
(SELECT section_id, min(auto_id) as min_auto_id
FROM #t_question
GROUP BY section_id) tqi
ON tq.section_id = tqi.section_id and tq.auto_id < tqi.min_auto_id + 5 LEFT JOIN
options As o ON tq.q_id = o.question_id) As sqo
ON s.id = sqo.section_id
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>I created this in SQL Server, but I want it to be portable to other SQL DBs.<<
As you can see that is not likely to happen. As it is, I trust you are using SQL Server 2005 or SQL Server 2008.
As you can see that is not likely to happen. As it is, I trust you are using SQL Server 2005 or SQL Server 2008.
ASKER
@RiteshShah:
This one works fine, but as acperkins indicated, it doesn't work in MySQL?
@alchemyst999:
Still no dice - getting the following error:
Cannot add identity column, using the SELECT INTO statement, to table '#t_question', which already has column 'q_id' that inherits the identity property.
20090513-sqlsvr.png
20090513-mysql.png
This one works fine, but as acperkins indicated, it doesn't work in MySQL?
@alchemyst999:
Still no dice - getting the following error:
Cannot add identity column, using the SELECT INTO statement, to table '#t_question', which already has column 'q_id' that inherits the identity property.
20090513-sqlsvr.png
20090513-mysql.png
ASKER
bit of trial and error :)