Solved

Using SQL to select random questions for an exam

Posted on 2009-05-13
40
335 Views
Last Modified: 2012-05-06
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
Comment
Question by:egxis
  • 19
  • 18
  • 2
  • +1
40 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24373318
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24373324
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
 

Author Comment

by:egxis
ID: 24373525
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24373547
I am still not clear about your goal, can you please be more elaborate? some example data would be great.
0
 

Author Comment

by:egxis
ID: 24373669
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
 

Author Comment

by:egxis
ID: 24373687
Files attached.

exam-stucture.zip
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24373875
I guess the data you want, won't be possible with SELECT statement only, it may need dynamic query creation.
0
 

Author Comment

by:egxis
ID: 24373932
Really - not possible as nested queries or similar?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24373947
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24374003
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
 

Author Comment

by:egxis
ID: 24374047
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
 

Author Comment

by:egxis
ID: 24374066
The last query gave an error:

Invalid column name 'r'.

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24374095
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24374153
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
 

Author Comment

by:egxis
ID: 24374178
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24374199
have you used my query in post # 24374153?
0
 

Author Comment

by:egxis
ID: 24374219
Yep, used query from and get same results - see attached image.


20090513.png
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24374291
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
 

Author Comment

by:egxis
ID: 24374373
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24374665
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24374685
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
 

Author Comment

by:egxis
ID: 24374795
Getting an error:
Invalid column name 'description'.

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

Author Comment

by:egxis
ID: 24374807
changed t.description to t.descrip

See screenshot
20090513-3.png
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24374841
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
 

Author Comment

by:egxis
ID: 24374913
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24374964
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
 

Author Comment

by:egxis
ID: 24375228
Not quite... :(

20090513-5.png
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24375260
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
 

Author Comment

by:egxis
ID: 24375329
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
 

Author Comment

by:egxis
ID: 24375336
screenshot

20090513-6.png
0
 
LVL 3

Expert Comment

by:alchemyst999
ID: 24375967
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24376144
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24376179
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
 

Author Comment

by:egxis
ID: 24376245
@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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24376253
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
 
LVL 3

Expert Comment

by:alchemyst999
ID: 24376504
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
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
ID: 24377181
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24380835
>>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
 

Author Comment

by:egxis
ID: 24382132
@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
 

Author Closing Comment

by:egxis
ID: 31580897
bit of trial and error :)
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now