Darstellung von Kursen und Kursleitern

Hallo,

ich hab grade den Artikel "You get what you ask for" auf
WorseThanFailure[1] gelesen. In diesem erhält ein Kandidat im Zuge eines
Bewerbungsverfahrens einige Aufgaben[2] gestellt, und ich habe versucht
diese Aufgaben mal selbst zu lösen.


Besonders interessiert mich die Aufgabe 2, die wie folgt gestellt ist:

Gegeben sind zwei Tabellen, die der Kurse:
> select * from courses;
+-----------+------+
| course_no | name |
+-----------+------+
| 1 | C0 |
| 2 | C1 |
| 3 | C2 |
+-----------+------+
3 rows in set (0.00 sec)

und die der Kursleiter:

> select * from instructors;
+-----------+------+
| course_no | name |
+-----------+------+
| 1 | EY |
| 2 | ED |
| 2 | NW |
| 3 | ba |
| 3 | bb |
| 3 | bc |
+-----------+------+
6 rows in set (0.00 sec)


Gefordert ist ein SQL-Statement, welches eine Tabelle liefert, die die
Kurse mit den Kursleitern in Verbindung bringt, und folgendermaßen
aufgebaut ist:
- Drei Felder: Kursnummer, Leiter1, Leiter2
- Wenn es genau einen Kursleiter gibt, dessen Name in Leiter1, Leiter2 =
NULL
- Wenn es genau zwei Kursleiter gibt, deren Namen in den einzelnen
Feldern alphabetisch sortiert
- Wenn es mehr als zwei Kursleiter gibt, 'Committee' in Leiter1, Leiter2
= NULL.


Ich bin bei meinen Versuchen, so eine Ausgabe zu erzeugen leider nicht
besonders weit gekommen... Wie könnte man sowas lösen?


Viele Grüße,
Michael


[1] http://worsethanfailure.com/Articles/You-Get-What-You-Ask-Fo r.aspx
[2] http://img.worsethanfailure.com/images/200707/writtenTest.do c

--
Testscript für RegEchsen:
http://diesundas.funzt-halt.net/regextest.php

Re: Darstellung von Kursen und Kursleitern

Michael Ziegler wrote:

> Gegeben sind zwei Tabellen, die der Kurse:
> > select * from courses;
> +-----------+------+
> | course_no | name |
> +-----------+------+
> | 1 | C0 |
> | 2 | C1 |
> | 3 | C2 |
> +-----------+------+
> 3 rows in set (0.00 sec)
>
> und die der Kursleiter:
>
> > select * from instructors;
> +-----------+------+
> | course_no | name |
> +-----------+------+
> | 1 | EY |
> | 2 | ED |
> | 2 | NW |
> | 3 | ba |
> | 3 | bb |
> | 3 | bc |
> +-----------+------+
> 6 rows in set (0.00 sec)
>
>
> Gefordert ist ein SQL-Statement, welches eine Tabelle liefert, die die
> Kurse mit den Kursleitern in Verbindung bringt, und folgendermaßen
> aufgebaut ist:
> - Drei Felder: Kursnummer, Leiter1, Leiter2
> - Wenn es genau einen Kursleiter gibt, dessen Name in Leiter1, Leiter2 =
> NULL
> - Wenn es genau zwei Kursleiter gibt, deren Namen in den einzelnen
> Feldern alphabetisch sortiert
> - Wenn es mehr als zwei Kursleiter gibt, 'Committee' in Leiter1, Leiter2
> = NULL.

Typische Frage, typische Antwort :-)

SELECT
c.course_no,
CASE WHEN COUNT(*) > 2 THEN 'Committee' ELSE MIN(i.name) END,
CASE WHEN COUNT(*) = 2 THEN MAX(i.name) END
FROM courses AS c JOIN instructors AS i
ON i.course_no = c.course_no
GROUP BY c.course_no;

Dieter

Re: Darstellung von Kursen und Kursleitern

Dieter Noeth wrote:
> Typische Frage, typische Antwort :-)
>
> SELECT
> c.course_no,
> CASE WHEN COUNT(*) > 2 THEN 'Committee' ELSE MIN(i.name) END,
> CASE WHEN COUNT(*) = 2 THEN MAX(i.name) END
> FROM courses AS c JOIN instructors AS i
> ON i.course_no = c.course_no
> GROUP BY c.course_no;

Ok, ich glaub ich sollte mich langsam echt mal mit diesen Case-Dingern
anfreunden :)

Vielen Dank!

Michael

--
Testscript für RegEchsen:
http://diesundas.funzt-halt.net/regextest.php

Re: Darstellung von Kursen und Kursleitern

Die Loesung fuer den Einsatz mit CASE ist ja bereits gepostet worden.
Wenn man ein RDBMS ohne CASE hat, geht auch folgendes (natuerlich nicht
so performant!)

mysql> SELECT a.course_no
-> ,b.name Instructor1
-> ,NULL Instructor2
-> FROM courses a
-> ,instructors b
-> WHERE a.course_no = b.course_no
-> GROUP BY course_no
-> HAVING COUNT(*) = 1
-> UNION
-> SELECT a.course_no
-> ,MIN(b.name) Instructor1
-> ,MAX(b.name) Instructor2
-> FROM courses a
-> ,instructors b
-> WHERE a.course_no = b.course_no
-> GROUP BY course_no
-> HAVING COUNT(*) = 2
-> UNION
-> SELECT a.course_no
-> ,'Committee' Instructor1
-> ,NULL Instructor2
-> FROM courses a
-> ,instructors b
-> WHERE a.course_no = b.course_no
-> GROUP BY course_no
-> HAVING COUNT(*) > 2;
+-----------+-------------+-------------+
| course_no | Instructor1 | Instructor2 |
+-----------+-------------+-------------+
| 1 | EY | NULL |
| 2 | ED | NW |
| 3 | Committee | NULL |
+-----------+-------------+-------------+
3 rows in set (0.00 sec)

mysql>

--
Oliver Lehmann
http://www.pofo.de/
http://wishlist.ans-netz.de/