SQL Grundkurs 2: Aufgaben und Lösungen

Lösung zu Beispiel 15 mit MS SQL Server

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
In diesem Film finden Microsoft-SQL-Server-Anwender die Erläuterung der Lösung zu Beispiel 15. Den Tipp, wie Sie eine eigene Funktion programmieren, sollten Sie nicht verpassen!

Transkript

In diesem Video zeige ich Ihnen, wie Sie das 15. Beispiel nämlich, welche Mitarbeiter sind jünger als der Durchschnitt ihre Abteilung, mit dem Microsoft SQL Server lösen. Bereits in der Angabe habe ich Ihnen gezeigt, wie wir hier das Alter berechnen können. Das machen wir mit dieser Funktion, indem wir die Differenz in Jahren berechnen. Das sind aber ganze Jahre und berücksichtigen nicht, ob jemand in diesem Jahr schon Geburtstag gehabt hat. Deshalb haben wir hier diesen Korrektur- faktor ergänzt, um gegebenenfalls hier noch einzugreifen. Hier haben wir unter Umständen noch die Ungenauigkeit falls es ein Schaltjahr ist, in dem wir uns befinden. Wenn Sie es ganz genau machen möchten, können Sie ab der Version 2012 auf diesen Korrekturfaktor ausweichen, nämlich um nicht den Tag im Jahr, sondern das explizite Datum zu verweisen. Mit der Funktion DATEFROMPARTS kann ich einfach für irgendein Jahr, das ich annehme, in dem Fall nehme ich ein Schaltjahr, um ganz sicher zu gehen, das alle Datumswerte möglich sind, bilde ich dieses mit dem Monat und dem Tag. Das heißt, diese Funktion DATEFROMPARTS benötigt hier ein Jahr, einen Monat und einen Tag und wenn das gleich ist, wie das selbe für das Geburtsdatum. Und wenn das kleiner ist, dann brauche ich einen Korrekturfaktor. Also diese Variante mit DATEFROMPARTS vergleicht auf ein Jahr nivelliert das aktuelle Datum mit dem Geburtsdatum, um festzustellen, ob es schon vorgekommen ist. Damit fällt die Ungenauigkeit beim Schaltjahr, die wir mit der oberen Variante unter Umständen haben, weg, Um nun zu lösen, die Aufgabenstellung wer ist jünger als das Durchschnittsalter, müssen wir in einer Unterabfrage das Durchschnittsalter berechnen. Jetzt haben wir natürlich diesen Berechnungsausdruck für das Alter hier mehrmals in unserer Anweisung. Das heißt, die schaut hier sehr monströs und unübersichtlich aus vielleicht macht es Sinn, wenn Sie das Beispiel auch mit dem Beispielscript vergleichen und nicht nur unbedingt hier mit der Darstellung am Bildschirm. Das hier ist die Unterabfrage. Hier berechne ich das Durchschnittsalter. Beim Microsoft SQL Server haben wir noch ein kleines Problem bei der Berechnung des Durschschnittsalters, nämlich, wenn ich die Average Funktion für diesen Ausdruck verwende, liefert mir das einen ganzen Wert, der unter Umständen gerundet wird. Das heißt, das Durchschnittsalter könnte ungenau sein, weil Mittelwert auch einen Integer liefert. Ich werde ihnen gleich in einer Erweiterung zeigen, wie wir dieses Problem umgehen beziehungsweise lösen. Bleiben wir mal bei der Unterabfrage. Die berechnet das durchschnittliche Alter aber wir wollen es ja immer nur für eine Abteilung. Deshalb müssen wir hier in der Unterabfrage auf das Abteilungskürzel in der Hauptabfrage filtern. Da die Hauptabfrage auf der selben Tabelle basiert wie die Unterabfrage müssen wir unterschiedliche Aliasnamen vergeben, damit wir hier unterscheiden können. In der Hauptabfrage habe ich deshalb ha und in der Unterabfrage ua für diese Personaltabelle als Alias definiert. Wenn ich also in der Unterabfrage sage WHERE ua.abtlg hießt das, die Abteilung in der Unterabfrage muss gleich sein wie ha.abtlg, die Abteilung in der Hauptabfrage. Damit liefern wir für jeden Mitarbeiter das richtige Durchschnittsalter aus seiner Abteilung. Ich habe jetzt hier diesen langen Ausdruck einmal verwendet zur Altersberechnung, um das im Ergebnis auszugeben, einmal hier, um das Alter für das Filtern zu berechnen, weil dieses berechnete Alter muss ja kleiner sein, als das Durchschnittsalter. Und dann ein drittes Mal hier, für die Berechnung. und wenn ich diese ganze Anweisung ausführe bekommen wir einmal heraus, wie viele sind kleiner. Das ist jetzt hier ungenau, wir bekommen hier 9 genauer genommen müssten wir jetzt mit dem aktuellen Datum, das kann sich bei ihnen ja verschieben, wenn Sie das Beispiel zu einem späteren Zeitpunkt ausführen, 11 Werte herauskommen. Das liegt, wie schon erwähnt, daran, Dass das Durchschnittsalter selber etwas ungenau hier berechnet wird. Das liegt daran, das es als Integer Wert gerundet wird. Kommt jetzt zum Beispiel rechnerisch als Durchschnittsalter 30,9, würde es, weil es ein Integer ist, 31 liefern, und damit die Ungenauigkeit. Um das zu verhindern, bauen wir noch eine kleine Ergänzung in die Unterabfrage ein. Die ist ohnehin schon sehr unübersichtlich und monströs aber es hilft leider nicht, und zwar mit der CAST Funktion wandeln wir das berechnete Datum in einen Datentyp um, der Kommastellen aufweisen kann, zum Beispiel AS real. und dann erst bilden wir den Mittelwert. und damit bekommen wir wirklich sauber als Durchschnittsalter zum Beispiel einen Kommawert heraus und nur durch diese kleine Modifikation bekommen wir ein anderes Ergebnis. 9 Zeilen hatten wir vorhin. Ich führe nun die korrigierte Variante aus und jetzt bekommen wir zwei Zeilen mehr. Das ist das selbe Ergebnis, wie Sie auch in den beiden anderen Lösungsvideos für Oracle und MYSQL bekommen. Jetzt werden Sie zu Recht sagen: Um Gottes Willen, diesen umfangreichen Ausdruck hier drei mal kopiert und der könnte bei anderen Beispielen noch umfangreicher sein, kann man das nicht irgendwie vereinfachen? Ja, kann man. Ein kleiner Tipp an dieser Stelle: Viele Datenbankmanagementsysteme bieten die Möglichkeit, eigene Funktionen zu programmieren. Das ist zwar nicht mehr Standard SQL und keine standardisierte Variante deshalb ist die Syntax auch unterschiedlich bei den einzelnen Datenbankmanagementsystemen aber man kann diese Funktionalität durchaus einmal nutzen und das möchte ich Ihnen hier zeigen. Beim Microsoft SQL Server wird so eine Funktion mit der Anweisung CREATE FUNCTION gefolgt vom Namen für diese Funktion gebildet. Ich habe sie hier fn_alter genannt. Außerdem muss ich dieser Funktion als Parameter das Geburtsdatum übergeben. Deswegen habe ich hier eine Variable, die beginnen hier beim Microsoft SQL Server immer mit einem Klammeraffen, gebdat definiert, vom Datentyp date. Außerdem mit RETURNS int definiert, dass der Rückgabewert ein Integer Wert sein wird. Denn das Alter ist ja ein ganzzahliger Wert. Hinter dem Schlüsselwort AS kommt der eigentliche Berechnungsteil, der zwischen ein BEGIN und END gesetzt wird. Mit der Anweisung RETURN wird ein Wert zurückgegeben. Jetzt habe ich hier noch eine Variable deklariert, DECLARE, die habe ich @alter genannt Integer, und habe jetzt hier dieser Variable genau mit dem Ausdruck, den wir vorher verwendet haben, hier das Alter berechnet und zugewiesen. Und zwar einerseits mit dem Systemdatum und dem übergebenen Geburtsdatum, das sich in dieser Variable befindet. Das heißt, das Alter wird jetzt mit dem aktuellen Datum und dem übergebenen Geburtsdatum in dieser Variable gebildet,# dann in der Variable Alter gespeichert und der Inhalt dieser variable Alter am Ende mit der RETURN Anweisung zurückgegeben. Um nun diese Funktion in der Datenbank zu erzeugen, führe ich diese Anweisung nun aus. Kriege nun die Anweisung erfolgreich abgeschlossen und es gibt jetzt diese Funktion. Sollte ich sie später einmal nicht mehr benötigen, könnte ich sie mit der Anweisung DROP FUNCTION wieder löschen. Sie werden überrascht sein, wie übersichtlich das selbe Ergebnis wie vorhin nun mit dieser Funktion aussieht. Ob sie es glauben oder nicht. Das ist die selbe Anweisung wie früher nur dass wir hier jetzt drei mal an Stelle des ursprünglichen Monsterausdrucks direkt die neu erstellte Funktion einsetzen können. Das heißt hier mit der Funktion berechnen wir das Alter in der SELECT Klausel, dann hier einmal in der WHERE Klausel für den Vergleich und in der Unterabfrage, auch hier ändere ich das Ergebnis, caste ich es in einen Real wegen den Kommastellen, wie vorhin, kann ich das mit der Funktion Alter berechnen. Und nun führe ich diese Anweisung aus und bekomme das exakt selber Ergebnis. Also es ist sehr praktisch. Immer dann, wenn Sie komplexe Ausdrücke immer wieder benötigen, können Sie erwägen, ob Sie das nicht in eine benutzerdefinierte Funktion verpacken und diese in der Datenbank erstellen. Prinzipiell für unser Beispiel benötigen wir diese korrelierte Unterabfrage. Korreliert heißt sie, weil wir in der Unterabfrage auf einen Wert der Hauptabfrage zugreifen. Wir filtern nämlich hier nach der Abteilung in der Hauptabfrage. Somit haben wir in der Unterabfrage in Abhängigkeit von der Abteilung für den Datensatz der gerade in der Hauptabfrage geprüft wird, das Durschschnittsalter berechnet. Und wenn dieses größer, beziehungsweise das Alter kleiner als dieses ist, dann ist jemand jünger und wird im Ergebnis angezeigt.

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!