SQL Grundkurs 2: Aufgaben und Lösungen

Beispiel 9: Synchronisiertes Update

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Erhöhen Sie die Gehälter für die Mitarbeiter aller Abteilungen mit Ausnahme von Einkauf und Verkauf. Dabei sollen jene, die weniger als der Durchschnitt der Abteilung verdienen, eine Erhöhung von 4%, alle anderen hingegen nur 2,5% mehr bekommen.

Transkript

In diesem Video wollen wir uns einem Beispiel für ein Update, das zusätzlich eine Unterabfrage benötigt, zuwenden. Bei diesem Beispiel geht es prinzipiell um folgende Aufgabenstellung. Wir möchten die Gehälter aller Mitarbeiter außer jenen aus den Abteilungen Einkauf und Verkauf, die haben bereits eine Erhöhung bekommen, erhöhen. Diesml soll die Erhöhung in Abhängigkeit vom Durchschnittsgehalt der Abteilung passieren. Und zwar jeweils auf das Durchschnittsgehalt der Abteilung bezogen, in der der einzelne Mitarbeiter arbeitet. All jene die weniger als der Durchschnitt der Abteilung verdienen, sollen dabei jetzt eine vierprozentige, alle anderen hingegen, eine 2,5 prozentige Erhöhung bekommen. Betrachten wir uns das kurz hier an den Daten. Im Controlling gibt es ein Durchschnittsgehalt von 2.950 Euro. "Bernhard Holzmann" liegt mit "2.800" darunter, bekommt in dem Fall eine vierprozentige Erhöhung, und die macht auf sein Gehalt gerechnet 112 Euro aus. Damit bekommt er ein neues Gehalt nach der Erhöhung von "2.912". "Mathias Prüger" hingegen, liegt mit seinen "3.100" über dem Durchschnitt der Abteilung und bekommt daher nur 2,5 Prozent, was in seinem Fall eine Erhöhung um 77,50 Euro auf "3.177,50" ausmacht. Und so soll das auch für die anderen Abteilungen gelten. Wie können Sie dieses Beispiel umsetzen? Sie müssen in einer Unterabfrage, das Durchschnittgehalt je Abteilung berechnen. Beim SQL Server als auch bei MySQL, joinen sie beim Update die zu updatende Tabelle "personal" mit dieser Unterabfrage. Und über eine Bedingung, über ein CASE in der Zuweisung prüfen Sie, ob das Durchschnittgehalt höher oder niedriger als das aktuelle Gehalt ist. Daraus soll sich der Prozentsatz für das Update ergeben. Bei Oracle funktioniert das etwas anders. Hier ist bei einem Update kein JOIN möglich. Daher verwenden wir eine korrelierte Unterabfrage, d. h. direkt bei der Zuweisung von SET verwenden wir eine Unterabfrage, in der wir auf die Abteilung in der Hauptabfrage, in dem Fall beim Update, verweisen. Ansonsten muss die Unterabfrage bei Oracle Versuchen Sie nun dieses, ich gebe zu, schon etwas komplexere Beispiel zu lösen. Wir werden uns die Lösungen für die drei Systeme im Anschluss gemeinsam ansehen. Pausieren Sie nun das Video und lösen Sie die Aufgabe. Wenn Sie damit fertig sind, fahren Sie mit dem Video fort und sehen Sie sich meine Musterlösung an. Dieses Beispiel war nicht mehr ganz einfach, betrachten wir uns nun die Lösung. Starten wir hier mit dem Microsoft SQL Server. Hier sehen wir die Unterabfrage, die uns prinzipiell die Aufgabenstellung löst: Was ist das Durchschnittsgehalt je Abteilung? Wir gruppieren also auf die Abteilung, und bilden mit "AVG" das durchschnittliche Gehalt. Außerdem vergeben wir den Aliasnamen "AS d_gehalt" für das Durchschnittsgehalt. Ich markiere diesen Anweisungsteil und führe ihn getrennt aus. damit wir dieses Ergebnis sehen. Das liefert uns also das Durchschnittsgehalt. Nun vergeben wir dieser Unterabfrage, hier wichtig ist es, einen Aliasnamen ... ich verwende auch "d" für Durchschnitt ... denn sonst würde das nicht funktionieren. Nun verwenden wir ein synchronisiertes Update, um hier im Update ... ich definiere hier ein Update auf die Tabelle "p" und nach dem SET kommt eine FROM-Klausel. Das ist die Syntax, die Sie beim Microsoft SQL Server dazu wählen müssen. Hier verwenden Sie den JOIN mit dieser Unterabfrage, und die Verbindung passiert über die Abteilung. also "ON p.abtlg", "p" ist "personal", "= d.abtlg", das ist meine Unterabfrage mit diesem Aliasnamen. Da wir definiert haben, dass die Abteilungen Einkauf und Verkauf bei dieser Erhöhung auszunehmen sind, brauchen wir eine OR-Klausel "WHERE p.abtlg NOT IN ('VK', 'EK')". Damit bekommen die Mitarbeiter dieser zwei Abteilungen keine Erhöhung mehr. In der eigentlichen Updateanweisung hier unter SET weisen wir dem Gehalt einen neuen Wert zu. Und zwar nehmen wir das alte Gehalt, "p.gehalt *" und hier nun die Bedingung. Wenn das Gehalt, das ist das Gehalt in der Personaltabelle, kleiner ist als "d_gehalt" das ist das Durchschnittsgehalt, dann "1,04", d. h. das wäre die vierprozentige Erhöhung. sonst "1,025", das wären die 2,5 Prozent. Das ist der Prozentsatz, der hier mit mal ergänzt wird. Und damit nehmen wir dieses Update vor. Bevor wir das Update ausführen, betrachten wir uns noch einmal die Ergebniswerte, wie sie herauskommen sollen. Wir merken uns die ersten zwei, "Holzmann" und "Prüger", die sollen von ihren "2.800" und "3.100" auf "2.912" und "3.177,50" erhöht werden. ich starte eine explizite Transaktion, damit ich das gegebenenfalls rückgängig machen könnte und führe nun dieses Update aus. Zwölf Zeilen sind geändert worden und ich kontrolliere das Ergebnis. Und das Ergebnis ist, wie es sein sollte, nämlich die ersten beiden haben die von uns vorher definierten Werte bekommen. "2.912" und "3.177,50". Wenn uns das Ergebnis also passt, können wir es mit COMMIT festschreiben. Wir haben also hier eine Unterabfrage gebildet, und diese mit einem Aliasnamen versehen. Diese haben wir hinter UPDATE, SET in einer FROM-Klausel, mit der ursprünglichen Tabelle, die wir ändern wollen, "p" gejoint. Damit können wir für jede Abteilung eruieren, was das Durchschnittsgehalt ist. Dieses haben wir in einer Bedingung für das Update eingebaut. So ist die Syntax beim Microsoft SQL Server. Ein wenig anders aber sehr ähnlich, funktioniert das Ganze unter MySQL. Betrachten wir uns hier das Ergebnis. Die Voraussetzung ist dieselbe. Wir sehen die ursprünglichen Werte und das gleiche Ergebnis muss herauskommen. Prinzipiell sehen wir hier die gleiche Logik. Wir sehen hier, dass wenn wir dieses Update durchführen, müssen wir hier in der Unterabfrage das Durchschnittsgehalt bilden. Dieses Durchschnittsgehalt, bilden wir über die Gruppierung je Abteilung und auch hier vergebe ich einen Aliasnamen. Auch die Bedingung für das Update selber ist dieselbe. Wo ist der Unterschied? Wir sehen, die Syntax ist ein wenig anders. Während wir beim Microsoft SQL Server ein JOIN in der FROM-Klausel verwendet haben, passiert hier der JOIN direkt hinter dem Update Wir sehen also "UPDATE wawi.personal" und vergeben hier gleich den Aliasnamen für diese Tabelle und erledigen danach gleich den JOIN d. h. hier der JOIN schaut ein wenig anders aus, und sonst haben wir dasselbe Ergebnis. Und ich führe diese Anweisung nun aus. Es ist passiert, wir bekommen hier eine Warnung, dass hier Spalten abgeschnitten worden sind. Das ist jetzt hier im weiteren Sinne nichts Schlimmes, was jetzt hier passiert ist. Ich mache das Ganze nochmal rückgängig mit einem ROLLBACK, um das Ganze noch einmal zu lösen. Und zwar ist das eigentlich nur passiert, weil hier in der Unterabfrage dieses Durchschnittsgehalt zu viele Nachkommastellen bekommen hätte. Um diese Warnung weg zu bekommen ... wie gesagt, diese Warnung wirkt sich in keiner Weise jetzt negativ auf das Ergebnis aus. Dass was wir hier machen ist nur Kosmetik, damit wir sehen, dass es auch ohne Warnmeldung funktioniert, Jetzt habe ich also in dieses Update, in die Unterabfrage das ROUND auf zwei Nachkommastellen eingefügt, und führe nun nochmal das Update aus. Und jetzt hat es ohne die Warnmeldung funktioniert. Wir kontrollieren das Ergebnis und sehen, dass hier die gewünschten Werte die wir erwartet haben, auch hier herausgekommen sind. Und wenn uns das Ergebnis so passt, dann bestätige ich nun diese Änderung mit einem COMMIT. Also wir haben gesehen unter MySQL, wenn wir ein synchronisiertes Update machen, müssen wir den JOIN direkt hinter der UPDATE-Klausel machen und im Anschluss kommt das eigentliche SET erst dran. Abschließend möchte ich Ihnen noch die Lösung mit Oracle zeigen. Auch hier ist die Ausgangssituation wieder dieselbe. Wir sehen in Controlling die zwei Ausgangswerte und die zwei Zielwerte, die wir erreichen möchten. Unter Oracle ist ein synchronisiertes Update nicht mit einem JOIN möglich. wir brauchen stattdessen eine sogenannte korrelierte Unterabfrage. Eine korrelierte Unterabfrage zeichnet sich dadurch aus, dass sie an irgendeiner Stelle, typischerweise in der WHERE-Klausel, auf eine Spalte der Hauptabfrage verweist. Wir sehen also, es gibt hier keinen JOIN, lediglich die WHERE-Klausel, die die Abteilungen Verkauf und Einkauf ausschließt. Alles was wir hier verwenden, spielt sich hier direkt in der Zuweisung ab. Was ich jetzt gemacht habe, ich habe die Unterabfrage jetzt hier so modifiziert, dass ich jetzt hier gleich das Durschnittsgehalt gebildet habe, un wenn dieses Durchschnittsgehalt ... also das CASE habe ich gleich in die Unterabfrage eingebaut ... wenn dieses Durchschnittsgehalt größer ist als das "p.gehalt" ... Sie sehen "p." ist hier der Aliasname hier für die Tabelle in der Hauptabfrage ... dann liefert mir das direkt den Wert "1,04", sonst "1,025". Das heißt dieser Wert wird hier direkt an die Hauptabfrage übergeben. Auch in der WHERE-Klausel verweisen wir, damit jeder das Durchschnittsgehalt seiner Abteilung bekommt ... das "u", "u" für Unterabfrage habe ich hier als Aliasnamen vergeben ... das "u.abtlg = p.abtlg". Damit weiß das System, dass es nicht die eigene Abteilung in der Unterabfrage meint, sondern jene in der Hauptabfrage. Und auf diese Art und Weise lösen wir bei Oracle die Aufgabenstellung für ein synchronisiertes Update. Wir rufen uns in Erinnerung: Diese Werte müssen beim Update nun herauskommen, von diesen Ausgangswerten gehen wir aus. Ich starte nun diese Anweisung und wir sehen jetzt, dass zwölf Zeilen aktualisiert wurden. Auch hier kontrollieren wir das Ergebnis und wir sehen, dass das erwartete oder erhoffte Ergebnis herausgekommen ist. deshalb bestätige ich diese Änderung auch hier mit einem COMMIT. Damit sind auch die letzten Gehaltserhöhungen vorgenommen worden. Wi haben also in diesem Beispiel gesehen, wie wir eine Unterabfrage synchronisiert für ein Update verwenden können. Unter dem Microsoft SQL Server und MySQL erledigen wir das mit einem JOIN, bei Oracle mit einer korrelierten Unterabfrage.

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!