Am 14. September 2017 haben wir eine überarbeitete Fassung unserer Datenschutzrichtlinie veröffentlicht. Wenn Sie video2brain.com weiterhin nutzen, erklären Sie sich mit diesem überarbeiteten Dokument einverstanden. Bitte lesen Sie es deshalb sorgfältig durch.

SQL Grundkurs 2: Aufgaben und Lösungen

Lösung zu Beispiel 15 mit MySQL

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
MySQL-Anwender finden die Erläuterung der Lösung zu Beispiel 15 in diesem Video. Wie Sie eine eigene Funktion programmieren können, ist als Tipp dabei!

Transkript

In diesem Video zeige ich Ihnen, wie Sie das 15. Beispiel mit MYSQL umsetzen können. Hier geht es darum: Welche Mitarbeiter sind jünger als der Durchscnitt Ihrer Abteilung? Wie Sie unter MYSQL das Alter berechnen können, indem Sie die Jahre voneinander abziehen und dann die Tage im Jahr vergleichen mit einem Korrekturfaktor, habe ich Ihnen vorweg schon gezeigt. Das heißt, diesen Ausdruck hier: YEAR von NOW minus YEAR von gebdatum minus und dann CASE, die Bedingung für den Korrekturfaktor, liefert Ihnen das korrekte Alter. Wie kommen wir jetzt auf das Durchschnittsalter. Das Durchschnittsalter können wir jetzt hier in einer Unterabfrage berechnen. In dieser Unterabfrage, Sie sehen, das ist jetzt sehr unförmig diese Lösung, deshalb macht es auch Sinn, wenn Sie sich das im Beispielskript ansehen, nicht nur hier am Bildschirm, sehen Sie jetzt genau hier in diese Average Funktion diesen Ausdruck hineinkopiert. Das ist der Ausdruck für die Berechnung des Alters. SELECT Average, dieses Alter, FROM wawi.personal WHERE Abteilung ist gleich Abteilung. So, worum geht es jetzt hier? Was wir jetzt brauchen ist eine korrelierte, man sagt auch synchronisierte Unterabfrage. Denn in jeder Abteilung gibt es ein anderes Durchschnittsalter. Das heißt, wir müssen hier in der Unterabfrage auf die Abteilung in der Hauptabfrage filtern. Da die Hauptabfrage und die Unterabfrage beide auf der Personaltabelle beruhen, müssen wir jetzt ähnlich wie bei einem JOIN mit unterschiedlichen Aliasnamen agieren. Ich habe hier ua für Unterabfrage und hier oben in der FROM Klausel ha für die Tabelle in der Hauptabfrage verwendet. Und auch hier brauche ich wieder den Ausdruck für das Geburtsdatum, denn das berechnete Geburtsdatum für den Mitarbeiter muss ja kleiner sein als der Mittelwert in der Abteilung. Denn wenn ein Datum kleiner ist, dann ist es später und dann ist jemand jünger. Wir wollen ja all jene haben, die jünger sind als der Durchschnitt der Abteilung. Das sit natürlich sehr unförmig aber das ist bei diesem Beispiel nun mal so. Damit ich jetzt also in der Unterabfrage nur jene angezeigt bekomme, die jünger sind, muss ich jetzt hier auf die Hauptabfrage filtern. Ich kann jetzt diese Anweisung alleine gar nicht markieren und ausführen, denn alleine würde hier ha zu einem Fehler führen, denn ha ist unbekannt. Die Kombination funktioniert nur gemeinsam mit der Hauptabfrage. Das heißt in der Hauptabfrage habe ich all die Spalten, Abteilung und diesen Monsterausdruck für das Berechnen des Alters, und dann habe ich hier in der WHERE Klausel noch einmal diesen Ausdruck für das Alter und der muss kleiner sein. Und in der Unterabfrage ein drittes Mal diesen Monsterausdruck hier, um ihn zu verwenden und um dann darauf zuzugreifen. Ich bilde den Mittelwert und den aber nur für die gerade geprüfte Abteilung. Am Ende sortiere ich das Ganze noch nach Abteilung und dem Alter. Nachdem ALTER ja ein reservierter Begriff ist, so wie SELECT und FROM, muss ich das Alter hier als Aliasname unter doppelte Hochkommas setzen. Und wenn ich jetzt diese Anweisung ausführe, dann bekommen wir jetzt dieses Ergebnis hier. Hier haben wir all jene, ich habe es hier nach Abteilung. Das heißt, im Controlling ist nur der Bernhard Holzmann jünger als das Durchschnittsalter, im Einkauf diese beiden mit entsprechendem Alter, in der Finanzbuchhaltung nur der eine, und so weiter. Und so setzt sich das fort. Und damit bekommen wir jene Trefferanzahl heraus, Wie viele sind es hier? 11 Zeilen bekommen wir hier zurück, die jünger sind als das Durchschnittsalter ihrer Abteilung. Jetzt werden Sie zu Recht sagen: Das ist aber sehr umständlich. Wenn ich das jetzt in dieser Form hier so verwende und diesen langen Ausdruck immer kopiere. Vor allem ist gerade die Berechnung des Alters, die könnte ich vielleicht noch optimieren und irgendwie herausnehmen. Ich möchte Ihnen an dieser Stelle einen kleinen Tipp bieten: Nämlich, wie könnte ich meine eigene Funktion für das Alter, es gibt ja keine fertige Funktion, aber wie könnte ich mir hier eine eigene Funktion programmieren? Sie können, und das ist jetzt nicht mehr Standard SQL sondern schon eine Erweiterung zu SQL, eigene Funktionen ergänzen. Das ist ähnlich wie mit CREATE TABLE in der Data Definition Language mit CREATE FUNCTION Ich habe diese Funktion fn_alter genannt, um hier mit dieser Funktion das Alter zu berechnen. Das ist jetzt hier so eine Spezialität im Editor von MYSQL, nachdem ich innerhalb dieser Funktion hier immer Semikolon benötige, damit das im Editor sauber übernommen wird muss ich jetzt hier mit der Anweisung DELIMITER irgendetwas anders verwenden, zum Beispiel hier zwei mal Slash, DELIMITER // Sonst würde ich hier am Ende an dieser Stelle auch ein Semikolon verwenden, das würde nicht funktionieren. Und damit das System weiß, dass diese Gesamtanweisung hier endet, mit einem Semikolon in der Mitte, deshalb verwende ich hier eine anderen Delimiter. Diese Funktion verwende ich jetzt, fn_alter mit einem Übergabewert. Nämlich eine Variable gebdat, bei der ich vom Datentyp Date verwende. Diese Funktion, mit RETURNS definiere ich das, gibt mir einen integer Wert zurück. Und dann, die eigentliche Berechnung der Funktion, muss ich zwischen BEGIN und ein END setzen. Mit RETURN wird das Ergebnis zurückgegeben. Was ich hier deklariere ist eine so genannte Variable. Ich habe sie v_alter genannt. Alter alleine ist ja ein schlechter Name für eine Variable, weil ja alter ein reservierter Begriff ist. Ebenso mit integer. Nun gebe ich dieser Funktion beim Aufruf dann das Geburtsdatum und diese verwendet die gleiche Funktion für die Berechnung wie vorhin. Die Differenz der Jahre und der Korrekturfaktor. Und schreibt das Ergebnis hier in diese variable Alter zurück. Und danach wird das Ergebnis ausgegeben. Also mit dieser Anweisung hier kann ich mir jetzt in meiner Datenbank, in meinem Schema, diese Funktion erzeugen. Ich führe das jetzt hier raus und ich sehe jetzt hier CREATE FUNCTION hat funktioniert. Das heißt, es gibt jetzt diese Funktion Sollte ich sie irgendwann einmal nicht mehr benötigen, könnte ich mit der Anweisung DROP FUNCTION diese löschen. Was kann ich jetzt hier machen? Ob Sie es glauben oder nicht. Diese kleine Anweisung hier ist exakt das Selbe hier wie die Monsteranweisung hier, vorhin, die wir verwendet haben. Der große Vorteil ist aber, dass ich an Stelle dieses Mega-Ausdrucks hier nur diese kurze Anweisung, nämlich die Funktion Alter und der übergebe ich als Parameter das Geburtsdatum, verwende. Das heißt, an Stelle des langen habe ich hier einmal diesen kurzen Ausdruck und das Gleiche hier. Auch hier verwende ich die Funktion Alter und auch hier in der Unterabfrage gemeinsam mit Average, kann ich diese einsetzen. Das heißt, dasselbe Ergebnis habe ich hier wie vorhin mit dieser kleinen Anweisung. Ich blende hier unten das aus damit wir das Ergebnis besser sehen. Ich habe hier das selbe Ergebnis wie vorhin aber in einer wesentlich kürzeren und übersichtlicheren Form. Also immer dann, wenn Sie komplexe Ausdrücke haben, die Sie sehr häufig wieder verwenden möchten, können Sie die unter MYSQL in eine so genannte Function packen. Achten Sie allerdings darauf, dass Sie hier im Editor damit das funktioniert, einen anderen Delimiter definieren damit Sie hinter dem END nach der Function diesen einsetzen. Mit der Anweisung DELIMITER ; setzen wir anschließend wieder auf den Standard-Delimiter zurück. In einer Unterabfrage haben wir also gesehen, können wir auf eine Hauptabfrage verweisen. Wichtig ist, wenn die Haupt- und die Unterabfrage auf die selbe Tabelle beziehen, das wir unterschiedliche Aliasnamen für diese beiden definieren, damit das System weiß, auf welche der beiden wir jetzt referenzieren. Auf diese Art und Weise bilden wir das Durchschnittsalter und filtern hier in der WHERE Klausel für jeden nach dem Durchschnittsalter der eigenen Abteilung. Und so bekommen wir diese 11 Zeilen hier heraus.

SQL Grundkurs 2: Aufgaben und Lösungen

Vertiefen Sie Ihre SQL-Kenntnisse. In diesem Workshop erhalten Sie zahlreiche Beispielaufgaben, die Sie selbst lösen können. Anschließend zeigt Ihnen der Trainer die Lösung.

7 Std. 41 min (61 Videos)
Derzeit sind keine Feedbacks vorhanden...
 

Dieses Training setzt SQL-Kenntnisse voraus, wie sie beispielsweise in „SQL lernen und anwenden“ vermittelt werden.

Dieser Online-Kurs ist als Download und als Streaming-Video verfügbar. Die gute Nachricht: Sie müssen sich nicht entscheiden - sobald Sie das Training erwerben, erhalten Sie Zugang zu beiden Optionen!

Der Download ermöglicht Ihnen die Offline-Nutzung des Trainings und bietet die Vorteile einer benutzerfreundlichen Abspielumgebung. Wenn Sie an verschiedenen Computern arbeiten, oder nicht den ganzen Kurs auf einmal herunterladen möchten, loggen Sie sich auf dieser Seite ein, um alle Videos des Trainings als Streaming-Video anzusehen.

Wir hoffen, dass Sie viel Freude und Erfolg mit diesem Video-Training haben werden. Falls Sie irgendwelche Fragen haben, zögern Sie nicht uns zu kontaktieren!