Hilfe bei der Erstellung von Abfragen (Lernaufgaben)

[bjoekeldude]

Hallo!
Bin grade dabei ein wenig SQL für eine Klausur zu lernen. Glaube auch dass ich das eigentlich ganz gut hinbekommen, könnte aber ein wenig Hilfe gebrauchen.
Habe anbei die Aufgaben und meine Lösungen, kann mir jemand vielleicht sagen ob das so richtig ist?

sql.jpg

Hier meine Abfragen:

SELECT p.persNr, p.nachname, p.vorname, SUM(f.zuschlag), COUNT(f.fmNr)
FROM Person p
LEFT OUTER JOIN Familienmitglieder f
ON p.persNr=f.persNr
GROUP BY p.persNr;

SELECT p.persNr, p.nachname, a.abtNr
FROM Person p
WHERE p.vpersNr = p.persNr
INNER JOIN Abteilung a
ON p.persNr = a.persNr
GROUP BY a.abtNr;

SELECT p.persNr, p.nachname
FROM Person p, Familienmitglieder f
WHERE p.persNr NOT IN f.persNr;

SELECT a.abtNr
from Abteilung a, Person p
WHERE SUM ((p.persNr=a.persNr)=>2);

SELECT nachname, grundgehalt
FROM Person
WHERE grundgehalt <
(SELECT AVG(grundgehalt));

Bin über jede Hilfe dankbar

[Ralf Jansen]

Hast du eine Möglichkeit die SQLs auch an einer tatssächlich vorhanden Datenbank auszuprobieren? Das wäre essentiell wichtig um das auch tatsächlich zu lernen. So kannst du ja nur hoffen das es stimmt.
Und wir machen auch nicht mehr als Blindflug was wir beim drüberschauen sehen.

1.) Du must nach allen Feldern gruppieren die keine Aggregatfunktion darstellen. Also nicht nur nach persNR sondern auch nach Nachname und Vorname.

2.) Ein Join hängt immer an einer anderen Tabelle (also linke Tabelle join rechte Tabelle) du hast aber die where Clausel dazwischen. Die gehört hinter den join Ausdruck (hinter allen wenn mehrere vorhanden sind) aber vor den GROUP BY.

3.) Das sieht merkwürdig aus. Würde eher
[Highlight=SQL]SELECT p.persNr, p.nachname
FROM Person p
WHERE p.persNr NOT IN (select persNr from Familienmitglieder);[/Highlight]
schreiben.

4.) Hier gibt es viele Möglichkeiten. Klassischerweise würde man hier eine Having Klausel verwenden.
Etwa

[Highlight=SQL]SELECT A.abtNr, count(*)
FROM Abteilung a
INNER JOIN Person p ON a.abtNr = p.abtNr
GROUP BY A.abtNR
HAVING count(*) > 2[/Highlight]

5.) Hier hast du im Subselect die Angabe der Tabelle vergessen. Die grundgehalt Spalte muß da ja auch irgendwo her kommen.

[bjoekeldude]

Danke für deine Antwort Ralf!

Leider habe ich nicht eben die Möglichkeit das auszuprobieren. Denke das ganze zu installieren und dann die DB einzurichten ist ein wenig viel Aufwand dafür. Dazu kommt dass ich solche Sachen in der Klausur auch ohne Hilfsmittel lösen können muss

Korrektur zu 1)
SELECT p.persNr, p.nachname, p.vorname, SUM(f.zuschlag), COUNT(f.fmNr)
FROM Person p
LEFT OUTER JOIN Familienmitglieder f
ON p.persNr=f.persNr
GROUP BY p.persNr, p.nachname, p.vorname;

Welcher Sinn steckt denn dahinter dass ich auch noch nach den anderen Attributen gruppiere? Das leuchtet mir noch nicht ganz ein.

zu 2)
Ich hatte gedacht, ich könnte so eine Liste von Daten bekommen, die schon eine Eigenschaft (Sie sind selbst ihr Vorgesetzter, folglich Abteilungsleiter) und diese im Anschluss joinen, ist das nur ne dumme Konstruktion oder ist das gar nicht möglich?

zu 3)
Versteh deine Lösung und denke auch dass meine falsch war.

zu 4)
Was zählt der denn dann nun mit COUNT(*)?

Korrektur zu 5)
SELECT nachname, grundgehalt
FROM Person
WHERE grundgehalt <
(SELECT AVG(grundgehalt) FROM Person);

Der Fehler leuchtet mir auch ein.


Gruß, Stephan

[defo]

Du musst mindestens*1 nach allem gruppieren, was Du nicht aggregierst, aber in der Ausgabe drin ist.
Ich glaub das ist nicht ANSI konform, aber Quasi Standard bei den verschiednen RDBMS Anbietern.
MySQL ist da eine Ausnahme, funktioniert leider nicht richtig.


Er zählt im Having das gleich, wie an anderer stelle, wo count(*) eingesetzt wird. Hier die Anzahl der eindeutigen AbtNr.

Ausprobieren geht relativ gut mit sqlfiddle.com online im web. Auch wenn Du es in einer Klausur nicht nutzen kannst, ein bisschen reinschnuppern tut dem Verständnis sicher ganz gut und übt (bei NULL Setupaufwand)


*1, idR genau nach den nicht aggregierten Ausgaben, es geht auch mehr, aber das ist etwas aus der Welt

[Ralf Jansen]

1.) Weil es (zumindest theoretisch der group By weiß nix von der uniqueness einzelner Gruppierungsbestandteile) mehrere Personen zu einer persNr geben kann. Wenn du nur sagst gib mir eindeutige PersNr (im Group By) aber dazu auch Name und Nachname (in der Select Liste) ist möglich das mehrere Namen und Nachnamen zu einer PersNr zu bekommen (Defo und Ralf haben beide PersNr 1). Also erzwingt der GROUP BY auch diese Felder hinzuzufügen damit für alle im SELECT genannten Felder auch nur einen zutreffenden gruppierten Datensatz als Ergebnis gibt. Im Group by kannst du nie weniger Felder als in der Selectliste haben (exclusiv der Aggregatfunktionen) nur mehr. Das erklärt sich übrigens leicht wenn man sich das in einer laufenden DB ansieht. Erklären ist schwer.

2.) genau das passiert auch nur kannst du die where Bedingung nicht an dieser Stelle schreiben. Du solltest dir SQL nicht so vorstellen das das TopDown ausgeführt wird. SQL beschreibt keinen Algorithmus sondern definiert nur das gewünschte Ergebnis. In welcher Reihenfolge das tatsächlich dann ausgeführt wird ist die freie Entscheidung der Datenbank es muß halt nur deine Ergebniswunsch erfüllt werden. Also auch wenn die Bedingung im Where nach dem join kommt könnte sich die DB überlegen erst die Filterung im Where durchzuführen und dann zu joinen. Und je nach dem wie die Tabellen gefüllt sind (Datenbank führen darüber Statistiken) kann sich die Reihenfolge in dem das tatsächlich ausgeführt wird von jetzt auf gleich ändern wenn sie, bei gleichem Ergebnis, einen anderen Weg führ besser hält.

4.) Jede Zeile mit gleichen Werten also hier nur die abtNr. Wenn du eine DB zum ausprobieren hättest könntest du das SQL mal mit und ohne Count(*) probieren daran würdest du sofort erkennen was da gezählt wird

[bjoekeldude]

Super großen Dank!
Ihr habt mir wirklich sehr geholfen!

Gruß Stephan

[Wernfried]

Leider habe ich nicht eben die Möglichkeit das auszuprobieren. Denke das ganze zu installieren und dann die DB einzurichten ist ein wenig viel Aufwand dafür. Dazu kommt dass ich solche Sachen in der Klausur auch ohne Hilfsmittel lösen können muss

Auf dieser Webseite kann man kostenlos mit SQL rumspielen: http://sqlfiddle.com/

Gruss