SQL Grundkurs 2: Aufgaben und Lösungen

Beispiel 2: Tabellen mit Beziehungen – mit Lösung für MS SQL Server

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Erstellen Sie zwei Tabellen. Stellen Sie die Beziehungen zu den bereits vorhandenen Tabellen wawi.adressen, wawi.status, wawi.kunden und wawi.personal her. Ebenso wird eine Beziehung zwischen den beiden neuen Tabellen selbst benötigt.

Transkript

In diesem Beispiel möchten wir nun zwei Tabellen erstellen und diese auch gleich mit Testdaten befüllen. Konkret geht es um die Tabellenrechnungen und Rechnungspositionen, die wir in unserem Schema "wawi" erstellen möchten. Diese zwei sollen die Struktur aufweisen, wie wir sie hier sehen. Die Rechnungstabelle soll aus einer Rechnungsnummer bestehen, aus einem Rechnungsdatum. Dieses Datum soll verpflichtend von Eingabe sein und automatisch mit dem aktuellen Datum vorbelegt werden. Die Spalte "kunde" ist ein Fremdschlüssel zur Kundentabelle, und darf deshalb auch nur hier Werte aus dieser enthalten. Zusätzlich die Spalten "rech_adresse" und "lief_adresse" im optional, eine von der Kundenadresse abweichende Rechnungs- oder Lieferadresse anzugeben. "NULL" hier bedeutet, dass bei dieser Bestellung keine andere Rechnungsadresse eingegeben wird, sondern die Standardadresse vom Kunden verwendet wird. "lief_adresse" ist die Lieferadresse, die hier verwendet werden soll. Das ist ein Fremdschlüssel zur vorhin erzeugten Tabelle "wawi.adressen". Dasselbe gilt prinzipiell auch für die Rechnungsadresse, auch hier brauchen wir einen Fremdschlüssel, der zur Adressentabelle zeigt. Optional kann ein Rabatt hier vergeben werden, in dem Fall sind das fünf Prozent, standardmäßig soll dieser mit null vorbelegt werden. Als Bearbeiter muss ein Mitarbeiter, typischerweise wird er aus der Abteilung Verkauf kommen, fungieren. Dessen Personalnummer wird in der Spalte "bearbeiter" eingetragen. Auch das ist ein Fremdschlüssel und deshalb müssen wir vom Datentyp auch achten beziehungsweise nachsehen, welcher hier für die Personalnummer vergeben ist. Der "status" ist ebenso ein Fremdschlüssel zur Tabelle "Status", der hier verwendet werden kann. Status ist momentan in drei Ausprägungen hier enthalten, nämlich "1" – erfasst, 2 – abgeschlossen, 9 – storniert. Für Rechnungen macht es Sinn, zum Beispiel einen eigenen Status für "bezahlt" zu ergänzen, das mache ich jetzt hier gleich mit der "INSERT"-Anweisung Dann haben wir auch einen Status "3", den wir für Rechnungen benötigen, der eben lautet "bezahlt". Damit nur einer dieser Status zugeordnet werden kann, brauchen wir auch hier einen Fremdschlüssel, der darauf verweist. Die Bemerkung ist optional und kann natürlich auch leer gelassen werden. Zusätzlich benötigen wir zu den Rechnungen auch Rechnungspositionen. Diese bekommen einen zusammengesetzten Primärschlüssel, der aus Rechnungsnummer und aus einer Positionsnummer besteht. Die Positionsnummer soll pro Rechnung immer mit "1" beginnen, "2, 3, 4" und so weiter, bei der nächsten Rechnung wieder mit "1". Die Rechnungsnummer alleine ist natürlich auch ein Fremdschlüssel, der auf die zuvor erstellte Rechnungstabelle verweist. Hinter der Spalte "artikel" verbirgt sich ein Fremdschlüssel zur Artikeltabelle, der muss verpflichtend eingegeben werden und das entspricht der Artikelnummer. Die "bezeichnung" wird quasi beim Einfügen in Form der Artikelbezeichnung übernommen. Auch diese wird hier gespeichert, weil ich ja den Text auch verändern kann, genau so, wie ich das zum Beispiel bei einem Preis machen kann. Hier habe ich zum Beispiel zugeschrieben zur "Angelgarnitur zum Sonderpreis". Dieser Zusatz findet sich in der Artikeltabelle nicht. Dann brauchen wir natürlich eine "menge", einen "preis", da wird natürlich beim Einfügen der Standardpreis von der Artikeltabelle übernommen werden, aber wir können natürlich editieren, und der Mehrwertsteuersatz. Beide müssen hier gespeichert werden, denn sie können sich später einmal verändern. Wenn Sie diese zwei Tabellen erstellt haben, fügen Sie auch entsprechende Testdatensätze ein. Sie können diese Informationen, die Sie hier einfügen auch gleich mit einem Select, wenn Sie möchten, aus der Artikeltabelle auslesen. Zusätzlich möchten wir noch einen Check-Constraint erstellen, mit dem wir sicher sein können, dass der Verkaufspreis nicht negativ sein kann. "menge" könnte zum Beispiel beim Umtausch negativ sein, aber dann darf der Preis nicht auch negativ sein, denn Minus mal Minus gibt Plus, der Preis muss immer positiv sein. maximal darf er null sein, wen etwas gratis ist, zum Beispiel eine Beigabe, oder eine Zugabe, aber er darf nicht negativ sein. Bei MySQL müssen wir darauf verzichten, dass Check-Constrait umzusetzen, da wie erwähnt das dort momentan noch nicht verfügbar ist. (Musik läuft) 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. Betrachten wir uns nun die Lösung der Aufgabenstellung. Ich starte mit dem Microsoft SQL Server. "CREATE TABLE" und hier habe ich den Namen der Tabelle ergänzt. Wenn Sie nicht möchten, dass hier manchmal ein Fehler steht, kann es auch sinnvoll sein hier beim SQL Server ein "GO" unter Umständen hineinzuschreiben, da der Editor möchte, dass im Batch das die einzige Anweisung ist. Das ist aber jetzt nicht unbedingt so wichtig. Ich habe hier "renr", "datum", "kunde", "rech_adresse" "lief_adresse", so wie wir es definiert haben. Rechnungsnummer habe ich als integer verwendet und hier gleich als Primärschlüssel definiert. Das Rechnungsdatum ist "date" diesmal ohne Zeitangabe, da ich sie nicht benötige, als "NOT NULL" und mit dem Defaultwert, den wir beim Microsoft SQL Server als Constraint ausprägen. "SYSDATETIME" liefert mir das aktuelle Datum. "kunde" wird ein Fremdschlüssel zur Kundentabelle, deshalb "integer" als Datentyp, da das der Datentyp für die Kundennnummer ist. Das Gleiche gilt für Rechnungs- und Lieferadresse, die wir ja im vorigen Beispiel über die Tabelle "Adressen" ermöglicht haben, jeweils als integer. "rabatt" habe ich als "numeric" oder Dezimal, hätten wir auch nehmen können, definiert. "(4, 1)" heißt drei vor Komma, eine Nachkommastelle. Mehr als das benötigen wir nicht, weil mehr als hundert Prozent Rabatt ja nicht möglich sind. Defaultmäßig vergeben wir hier "0" als Standardwert. "bearbeiter" als "int NOT NULL" auch das wird der Fremdschlüssel werden und die Personalnummer in der Personaltabelle ist beim Microsoft SQL Server auch als integer definiert. Der "status" als "tinyint", der letzte Fremdschlüssel hier in der Riege, wird "DEFAULT" mit "1" vorbelegt und auch als "NOT NULL" definiert. Die Bemerkung kann hier bis zu 500 Zeichen lang sein, ist aber nicht als "NOT NULL" definiert denn ich muss ja keine Bemerkung eingeben. So ich führe diese Anweisungen aus und habe jetzt diese Tabelle erstellt. Ich habe hier die Variante gewählt, die Fremdschlüssel mit den entsprechenden Anweisungen und die Check-Constraints im Anschluss zu erstellen. Hier einmal, dass der Rabatt nur als Wert von 0–100 definiert sein kann. das ist noch so eine Zusatzaufgabe, die ich hier eingebaut habe die ich hier jetzt gleich einmal erledige Dann hier der Fremdschlüssel zur Kundentabelle, "kunde" verweist auf "wawi.kunden(kdnr)" dann hier der Fremdschlüssel für die Rechnungs- und Lieferadressen das heißt, zwei Fremdschlüssel die auf dieselbe Tabelle, aber von unterschiedlichen Spalten verweisen. Das ist natürlich möglich, wir müssen nur aufpassen, dass ich hier jetzt nicht. so wie sonst gewohnt. sage, "fk", Ausgangs- und Zieltabelle, das wäre zweimal die Gleiche, deshalb habe ich hier statt Adresse auch wirklich ausgeschrieben Rechnungs- und Lieferadresse, um hier unterschiedliche Constraint-Namen zu bekommen. Ich führe die Anweisung aus und dann hier noch der Fremdschlüssel, gleiche Syntax zur Personaltabelle, eben der "bearbeiter", der auf die Personalnummer verweist, und last, but not least hier noch den Status. Eine ganze Reihe an Constraints, die wir hier zu ergänzen haben. Jetzt habe ich einmal die erste Tabelle erzeugt und wir können jetzt gleich zu den Rechnungspositionen übergehen. Die Tabelle "Rechnungspositionen" besteht aus einer Rechnungsnummer, die denselben Datentyp haben, wie in der Rechnungstabelle, in dem Fall integer, aufweisen muss. Für die Positionsnummer habe ich kein integer definiert, das ist ein Wert von 0 bis 255 beim Microsoft SQL Server, das wird wohl ausreichend sein. "artikel int" weil die Artikelnummer in der Artikeltabelle auch als solche hier definiert ist. Die "bezeichnung" und die "menge" auch als ganzzahlige Werte, Das integer mit einem Standardwert "1", das habe ich hier ergänzt, weil ich sage, in vielen Fällen wird genau eine Menge "1" gekauft und dann muss ich diese hier gar nicht mehr verändern. "preis" habe ich hier als "smallmoney" definiert, der muss auch definiert sein, und die Mehrwertsteuer. Ich habe hier die Constraints auf Tabellenebene definiert, das ist zum Beispiel für den Primärschlüssel notwendig, auf Spaltenebene ginge es nicht, weil es ein zusammengesetzter Primärschlüssel aus Rechnungsnummer und Positionsnummer ist, den wir hier verwenden. Dann die zwei Fremdschlüssel zur Rechnungs- und Artikeltabelle und noch abschließend einen Check-Constraint das sicherstellt, dass der Preis hier nicht negativ sein kann. Und auch diese Tabelle erzeuge ich mit dieser Anweisung. Jetzt haben wir die zwei Tabellen und jetzt kann ich noch einen Testdatensatz einfügen. Um einen Verkäufer auszusuchen, werfe ich kurz einen Blick in die Personaltabelle, wer in der Abteilung "VK" arbeitet, das sind eben diese Personen hier, Ich nehme zum Beispiel die Maria Neumann mit der Personalnummer "657" für meine Rechnung, also ich füge eine Rechnungsnummer ein, vergebe in dem Fall die erste meiner Rechnungsnummern, dann "kunde", da nehme ich die Kundennummer "136". Das müssen aufgrund der Fremdschlüssel alles gültige Werte sein. Rechnungsadresse brauche ich keine eigene. Lieferadresse, da nehme ich die Lieferadresse mit der Nummer "1", die haben wir im vorigen Beispiel eingefügt. "rabatt" fünf Prozent und eben "bearbeiter" diese Nummer. Die Tabelle ist leer. Schauen wir noch hinein, das haben wir gerade erstellt, füge diese Zeile ein, und nun sehen wir das Ergebnis. Nachdem ich hier eine automatische Transaktion beim SQL-Server habe, brauche ich jetzt hier kein extra "COMMIT". Ich habe keine Transaktion mit "BEGIN TRANSACTION" gestartet. Und diese Zeile habe ich jetzt hier schon einmal eingefügt. Um den Artikel mit der Nummer "1040" einzufügen, habe ich gleich dieses "SELECT" hier gewählt, ich führe es mal alleine aus, um zu sehen, was wir einfügen werden. Hier habe ich fix die vorher definierte Rechnungsnummer verwendet, "1" als Positionsnummer, "artnr", "bezeichnung", und habe hier mit "+ zum Sonderpreis" diesen Text hier zur normalen Bezeichnung ergänzt. Zwei Stück in den Verkauspreis und die Mehrwertsteuer. Das ist jetzt schon der Sonderpreis, der in der Tabelle hier steht. Und mit dieser Anweisung füge ich diese Zeile ein und nun habe ich auch die entsprechende Rechnungsposition hier eingefügt. Sie haben also hier gesehen, wie Sie zwei Tabellen mit einer ganzen Menge an Fremdschlüsseln hier mit dem Microsoft SQL Server erstellen können. In der ersten Variante habe ich die Fremdschlüssel im Anschluss an das Erstellen der Tabelle mit einer Alter-Table-Anweisung ergänzt. Im zweiten Fall habe ich direkt mit derselben Anweisung dieses auf Tabellenebene erledigt. Beim Einfügen kann ich entweder direkt über die Values-Klausel arbeiten oder, zur Wiederholung, habe ich hier auch wieder meinen "SELECT" verwendet, um eine Rechnungsposition einzufügen. Dazu haben wir aus der Artikeltabelle entsprechende Werte ausgelesen.

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!