• Sie befinden sich hier: Startseite > SQL-Backstube > Quizfragen auswerten

Die SQL-Backstube

Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.

18.07.2010: Quizfragen auswerten

Problemstellung

Umfragen und Quiz mit mehreren Fragen auszuwerten ist manchmal gar nicht so einfach. Im folgenden Beispiel werden eine Reihe von Fragen und deren Antworten geprüft. Nur die Teilnehmer, die alle Fragen richtig beantwortet haben, sollen aufgelistet werden.

Getestet mit folgenden Datenbanken:

* MySQL 5.1.30
* IBM DB2 9.1

Datenbank-Tabellen

Die Datenbank-Tabellen hierfür sehen wie folgt aus:

Listing 1:

1. /**
2. * Teilnehmer der Umfrage bzw. des Quiz
3. */
4. CREATE TABLE teilnehmer (
5. t_id INT NOT NULL,
6. name VARCHAR(100) NOT NULL,
7. PRIMARY KEY (t_id)
8. );
10. /**
11. * Die Fragen und die richtige Antwort
12. */
13. CREATE TABLE fragen (
14. f_id INT NOT NULL,
15. frage VARCHAR(200) NOT NULL,
16. richtig INT NOT NULL,
17. PRIMARY KEY (f_id)
18. );
20. /**
21. * Die Antworten der Teilnehmer
22. */
23. CREATE TABLE teilnehmer_antwort (
24. ta_id INT NOT NULL,
25. t_id INT NOT NULL,
26. f_id INT NOT NULL,
27. antwort INT NOT NULL,
29. PRIMARY KEY (ta_id),
31. CONSTRAINT fk_teilnehmer
32. FOREIGN KEY (t_id)
33. REFERENCES teilnehmer (t_id),
35. CONSTRAINT fk_fragen
36. FOREIGN KEY (f_id)
37. REFERENCES fragen (f_id)
38. );

Konzept

Ein Teilnehmer muss sich registrieren (Tabelle teilnehmer) und kann dann die Fragen (per ankreuzen) beantworten. Die Antworten werden in der Tabelle teilnehmer_antwort gesammelt. Zu jeder Frage gibt es nur eine korrekte Antwort. Die Fragen und die richtigen Antworten (Nr) sind in der Tabelle fragen gespeichert. Die Tabelle mit allen Antworten des Quiz (also die richtigen und die falschen, unter denen der Teilnehmer wählen kann) wird für die Auswertung nicht benötigt.

Testdaten

Für das nachfolgende Beispiel legen wir uns ein paar Testdaten an. Es werden drei Teilnehmer, drei Fragen, sowie die neun Antworten der Teilnehmer in die Datenbank eingelesen.

Listing 2:

1. /**
2. * Einfügen der drei Teilehmer
3. */
4. INSERT INTO teilnehmer (t_id, name ) VALUES
5. (1, 'Teilnehmer-1'),
6. (2, 'Teilnehmer-2'),
7. (3, 'Teilnehmer-3');
9. /**
10. * Einfügen der drei Fragen
11. */
12. INSERT INTO fragen (f_id, frage, richtig) VALUES
13. (1, 'Das ist die Frage 1', 1),
14. (2, 'Das ist die Frage 2', 2),
15. (3, 'Das ist die Frage 3', 2);
17. /**
18. * Einfügen der neun Antworten der Teilnehmer
19. */
20. INSERT INTO teilnehmer_antwort (ta_id, t_id, f_id, antwort) VALUES
21. (1, 1, 1, 1),
22. (2, 1, 2, 2),
23. (3, 1, 3, 2),
24. (4, 2, 1, 1),
25. (5, 2, 2, 2),
26. (6, 2, 3, 4),
27. (7, 3, 1, 2),
28. (8, 3, 2, 2),
29. (9, 3, 3, 3);

SQL - Abfrage

Nun machen wir uns daran auszuwerten, welcher Teilnehmer alle Fragen korrekt beantwortet hat. Die SQL-Abfrage hierfür sieht so aus:

Listing 3:

1. /**
2. * Die Teilnehmer ermitteln, die alle Fragen korrekt beantwortet haben
3. */
4. SELECT t.name
5. FROM teilnehmer t
6. JOIN (SELECT t_id, COUNT(*) AS anzahl
7. FROM teilnehmer_antwort ta
8. JOIN fragen f
9. ON ta.f_id = f.f_id
10. AND ta.antwort = f.richtig
11. GROUP BY t_id
12. ) richtig
13. ON richtig.t_id = t.t_id
14. JOIN ( SELECT COUNT(*) AS anzahl FROM fragen) fragen
15. ON richtig.anzahl = fragen.anzahl;
17. +--------------+
18. | name |
19. +--------------+
20. | Teilnehmer-1 |
21. +--------------+
22. 1 row in set (0.00 sec)

Der etwas komplexe Query ermittelt in mehreren Teilschritten das gewünschte Ergebnis. Der erste Sub-Select richtig liefert die Anzahl der richtigen Antworten pro Teilnehmer. Der zweite Sub-Select fragen zählt die Anzahl der notwendigen korrekten Antworten für dieses Quiz. Wenn jetzt diese beiden Werte richtig.anzahl = fragen.anzahl übereinstimmen, hat der Teilnehmer die richtige Menge an korrekten Antworten angekreuzt und wird als Ergebnis der Abfrage ausgegeben. In unserem Beispiel trifft dies auf den Teilnehmer Teilnehmer-1 zu.

https://twiedmann.de