Link to home
Start Free TrialLog in
Avatar of egxis
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
Avatar of RiteshShah
RiteshShah
Flag of India image

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()
Avatar of egxis
egxis

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.
I am still not clear about your goal, can you please be more elaborate? some example data would be great.
Avatar of egxis

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.
Avatar of egxis

ASKER

Files attached.

exam-stucture.zip
I guess the data you want, won't be possible with SELECT statement only, it may need dynamic query creation.
Avatar of egxis

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

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
Avatar of egxis

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
Avatar of egxis

ASKER

The last query gave an error:

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
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
Avatar of egxis

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.
have you used my query in post # 24374153?
Avatar of egxis

ASKER

Yep, used query from and get same results - see attached image.


20090513.png
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

Avatar of egxis

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
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

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

Avatar of egxis

ASKER

Getting an error:
Invalid column name 'description'.

At a glance, II can't see where you are referring to description incorrectly.
Avatar of egxis

ASKER

changed t.description to t.descrip

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

Avatar of egxis

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
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

Avatar of egxis

ASKER

Not quite... :(

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

Avatar of egxis

ASKER

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

Avatar of egxis

ASKER

screenshot

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

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

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

Avatar of egxis

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.
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

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

ASKER CERTIFIED SOLUTION
Avatar of RiteshShah
RiteshShah
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Anthony Perkins
>>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.
Avatar of egxis

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
Avatar of egxis

ASKER

bit of trial and error :)