Joined: 24 Jul 2007
|Posted: Tue Jul 24, 2007 11:40 pm Post subject: Querying random entries
|I'm a high school teacher trying to implement a simple database of questions for a science team I coach. The competition is much akin to Jeapordy, and covers a huge swath of possible topics. We've accumulated many thousands of student-written questions over the years, but they're only marginally useful in paper form. This is my first experience working with a database of any kind, and it may be the only one I ever use, but I have done a fair amount of programming in other contexts.
It was very easy to set up a table with the pertinent data types, and to create a form for entering questions. After poking around on the web and reading a zillion SQL introductory tutorials, I've also written a simple query that will successfully pull out a single random question:
SELECT * FROM "Questions"
WHERE "Tossup" = 1
ORDER BY Rand()
Half of the questions in the competition are "tossups", and students who successfully answer a tossup get a "bonus" question in the same subject. So here's where I'm stuck:
1) I'd really like to be able to select a random bonus to go with the tossup, in the same subject (there are 7 subjects, stored in a text field named "Subject"). But I am not sure how I would refer to the subject of the randomly selected tossup in writing the second SELECT (would I make the first one a view?).
2) I've been unable to make two consecutive SELECTs work in the same query at all, even when either works independently. Is it possible? How?
3) My current scheme selects a question at random from the entire database, which will weight the selection towards subjects in which I have more questions. I would prefer a system that first randomly picked a subject, with a 1/7 chance of each one (which is the part I don't know how to implement), and then randomly picked a tossup and a bonus in that subject (which would be easy to implement, I'd just add a WHERE)
4) I would be a really happy camper if I could generate a report with 25 pairs of matched, randomly chosen tossup and bonus questions, since that's the actual competition format. I can't figure out how to make that happen unless they're part of a single query, which I'm not sure is even possible (see #2).
Sorry if the questions are stupid! I suppose an answer to #3 may implicitly solve #1. Thanks in advance for any help.