• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

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
0
egxis
Asked:
egxis
  • 19
  • 18
  • 2
  • +1
1 Solution
 
RiteshShahCommented:
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()
0
 
RiteshShahCommented:
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()
0
 
egxisAuthor Commented:
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
RiteshShahCommented:
I am still not clear about your goal, can you please be more elaborate? some example data would be great.
0
 
egxisAuthor Commented:
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.
0
 
egxisAuthor Commented:
Files attached.

exam-stucture.zip
0
 
RiteshShahCommented:
I guess the data you want, won't be possible with SELECT statement only, it may need dynamic query creation.
0
 
egxisAuthor Commented:
Really - not possible as nested queries or similar?
0
 
RiteshShahCommented:
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

0
 
RiteshShahCommented:
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
0
 
egxisAuthor Commented:
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
0
 
egxisAuthor Commented:
The last query gave an error:

Invalid column name 'r'.

0
 
RiteshShahCommented:
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
0
 
RiteshShahCommented:
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
0
 
egxisAuthor Commented:
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.
0
 
RiteshShahCommented:
have you used my query in post # 24374153?
0
 
egxisAuthor Commented:
Yep, used query from and get same results - see attached image.


20090513.png
0
 
RiteshShahCommented:
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

Open in new window

0
 
egxisAuthor Commented:
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
0
 
RiteshShahCommented:
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

Open in new window

0
 
RiteshShahCommented:
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

Open in new window

0
 
egxisAuthor Commented:
Getting an error:
Invalid column name 'description'.

At a glance, II can't see where you are referring to description incorrectly.
0
 
egxisAuthor Commented:
changed t.description to t.descrip

See screenshot
20090513-3.png
0
 
RiteshShahCommented:
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

Open in new window

0
 
egxisAuthor Commented:
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
0
 
RiteshShahCommented:
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

Open in new window

0
 
egxisAuthor Commented:
Not quite... :(

20090513-5.png
0
 
RiteshShahCommented:
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

Open in new window

0
 
egxisAuthor Commented:
Changed code to below.

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

Open in new window

0
 
egxisAuthor Commented:
screenshot

20090513-6.png
0
 
alchemyst999Commented:
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.
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

Open in new window

0
 
RiteshShahCommented:
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 

Open in new window

0
 
RiteshShahCommented:
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 

Open in new window

0
 
egxisAuthor Commented:
@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.
0
 
RiteshShahCommented:
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 

Open in new window

0
 
alchemyst999Commented:
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

Open in new window

0
 
RiteshShahCommented:
was this worked?



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 

Open in new window

0
 
Anthony PerkinsCommented:
>>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.
0
 
egxisAuthor Commented:
@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
0
 
egxisAuthor Commented:
bit of trial and error :)
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 19
  • 18
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now