Load random records from db upon conditions

[Nitin Daphale]

In my Online Exam project, user creates a test,
Test contains different different subjects,
each and every subject having percent easy,percent medium,percent hard no of questions.
Test also have total no of questions. All Subject's total no of questions must equal to Test's total no of questions.

No worries if my above conditions are unable to understand.
I just want the most smart way to do it.

If it should do with multiple queries and check that query results in program and again do query if required?

or

Is it possible with procedures? if so give me sample.

or

Any other way?

[kvprajapati]

First of all create a collection of questions of different subjects and then after randomly pick some questions.

[Nitin Daphale]

First of all create a collection of questions of different subjects and then after randomly pick some questions.

You mean first get all questions from DB
and then randomly select from collection at code and not at DB?
Is it feasible if I have 1000 of records and need to select only 30?

or You are suggesting to do all that in a procedure?

[kvprajapati]

>You mean first get all questions from DB

Nope! Fetch all rows which belongs to selected subjects.

>You are suggesting to do all that in a procedure?

I'm not sure! Can you show us table design? How many tables are there?

[Nitin Daphale]

Test,QuestionBank,Subject,Questions each having separate tables.
As you said am already fetching rows for the subject but if it does not satisfied the conditions for the test how many times I will query and compare the result in program?

Question Table containing questions having subjectid,QuestionBankid as foreign key from Subject Table and QuestionBank respectively.
Each Test constructed with only one QuestionBank and it is again stored in another table called 'Interview'.

I know how to link all this tables and get questions for the test.
My concern is, there is no guarantee that questions are present in db according to difficulty level(Easy/Medium/Hard) for that subject. If no of easy questions for a subject are not there, it should get from other subject.Even after that subject not having that no of questions it should compromise with difficulty level and anyhow get randomly same no of questions(specified in test) each and every time.

IF it is possible by procedure then please send the sample code.

if not suggest the other way.