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

Beispiel 1: Tabelle erstellen – mit Lösung für MS SQL Server

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Erstellen Sie eine neue Tabelle wawi.adressen. Diese soll es ermöglichen, für Kunden abweichende Adressen zu speichern. Zusätzlich sind eine ganze Reihe weiterer Vorgaben zu berücksichtigen.

Transkript

In diesem Beispiel möchten wir eine neue Tabelle "wawi.adressen" erstellen. Diese soll die Möglichkeit bieten, zusätzlich zu unserer Kundentabelle, Sie sehen hier einen kleinen Auszug aus der Kundentabelle, Adressen zu verwalten. Die Idee ist, dass ich bei einer Bestellung zum Beispiel eine unterschiedliche Adresse oder auch eine Lieferadresse einmal werde angeben können. Zusätzlich zu der Adresse die hier schon in der Kundentabelle gespeichert ist, sollen also zusätzliche erfasst werden können. Diese neue Tabelle "Adressen" sollte folgende Struktur aufweisen: eine Adressnummer als Primärschlüssel, die sinnvollerweise automatisch vergeben wird bei der Erfassung, dann als Fremdschlüssel die Spalte "Kunde", die eben auf die Kundennummer, die Primärschlüsselspalte in der Kundentabelle verweist, dann eine Spalte "name1", "name2", "name3", wo es eben interessant wäre, die ersten drei Adresszeilen einzutragen. Also wir wollen hier nicht den Vornamen, Nachnamen und den Titel unterscheiden, wie wir es beim Kunden selber machen, sondern einfach in Adresszeilen und das ist eben "name1", "name2", "name3". Natürlich brauchen wir auch hier ein Länderkürzel, dann die Postleitzahl und einen Ort, dann die Straße inklusive Hausnummer. Außerdem möchten wir verspeichern, wann die Anlage erfolgt ist, das, ab wann der Datensatz erfasst wurde. Sinnvollerweise werden wir hier auch automatisch das Datum beim Einfügen vergeben und ob die Adresse noch aktuell ist. Standardmäßig wollen wir "1" für "aktuell", "0" würde für "nicht aktuell" stehen, auch hier vergeben. Welche Datentypen Sie für die einzelnen Spalten verwenden möchten, können Sie sich selber aussuchen, achten Sie aber dabei darauf, dass die Kundenspalte, hier Spalte mit dem Namen "kunde", ein Fremdschlüssel darstellt und natürlich den Datentyp der referenzierten Primärschlüsselspalte enthalten muss. Ich werfe hier einen Blick hinein in die Struktur. Sie sehen zum Beispiel hier, beim Microsoft SQL Server hat diese Spalte den Datentyp "integer". Wenn Sie das Beispiel in MySQL oder Oracle umsetzen, müssen Sie dort auch eruieren, welcher Datentyp hierfür verwendet worden ist. Beim Microsoft SQL Server können Sie die automatische Vergabe der Adressnummer über eine Identity bei MySQL mit einem Auto-Increment und bei Oracle über eine Sequence umsetzen. Die Sequence muss dann separat noch erzeugt werden. Über ein Check-Constraint soll sichergestellt werden, dass die Postleitzahl mindestens vierstellig ausgeprägt ist. Ich kenne also kein Land, wo es eine kürzere Postleitzahl gibt. Verwende ich diese Spalte ausschließlich als "NOT NULL" definiert, könnte ich hier auch zum Beispiel eine Postleitzahl angeben, die nur einer, zwei oder drei Stellen besteht. Das möchten wir über einen Check-Constraint sicherstellen. Das gilt natürlich leider nicht für die Anwender von MySQL, weil hier Check-Constraints im Moment noch nicht unterstützt werden. Wenn Sie diese Tabelle erstellt haben, fügen Sie anschließend ein paar Testdatensätze ein, um das Ergebnis zu prüfen. (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 oder das Ergebnis. und fangen wir mit dem Microsoft SQL Server an. Wenn wir eine Tabelle erstellen, benötigen wir auch zusätzlich Constraints Constraints – Einschränkungen – wie zum Beispiel Fremdschlüssel für eine Beziehung, auch der Primärschlüssel oder in unserem Beispiel ein Check-Constraint für die Postleitzahl. Prinzipiell haben wir drei Möglichkeiten, wann oder wie wir einen Constraint definieren. Wir können es auf Spaltenebene, das heißt, gleichzeitig mit der Spalte definieren. Ich werde im Anschluss noch die zwei anderen Varianten zeigen, das entweder auf Tabellenebene, das heißt, am Ende der Definition der Tabelle zu definieren, oder die dritte Variante ist, überhaupt zuerst einmal die Tabelle ohne die Constraints zu erstellen und anschließend mit einer Alter-Table-Anweisung diese zu ergänzen. Starten wir hier mit der ersten Variante. Mit "CREATE TABLE wawi.adressen" erzeugen wir diese Tabelle. Ich habe hier die Spalten definiert wie nach unserer Vorgabe "adrnr", Adressnummer, "kunde", "name1, 2, 3", "land", "plz", "ort" und "strasse", "anlage", "aktuell". Folgende Datentypen habe ich zum Beispiel hier beim Microsoft SQL Server gewählt. "int" für die Adressnummer und, damit sie automatisch vergeben wird, den Zusatz "IDENTITY". Das heißt, ohne weiteren Zusatz wird die Identität mit einem Einser beginnend, also einem Startwert von "1" und einer Schrittweite von "1", definiert, also 1, 2, 3, 4, 5, und so weiter durchnummeriert. Außerdem habe ich hier über "CONSTRAINT" "pk" gefolgt vom Namen der Tabelle, also "pk_adressen PRIMARY KEY", den Primärschlüssel definiert. Da ich es hier auf Spaltenebene definiert habe, muss ich nicht mehr zusätzlich angeben, dass die Adressnummer der Primärschlüssel ist. Das wird hier dadurch automatisch definiert. Es gibt dann noch die abgekürzte Form, dass ich überhaupt diesen Zusatz "CONSTRAINT pk_adressen", also den Constraint-Namen weglasse, und nur direkt über die verkürzte Form einen Constraint definiere. Das hat allerdings den Nachteil, dass in den Systemtabellen, die Constraints, die mit einem automatisch definierten Namen angelegt werden, wenn ich sie später ändern, löschen oder sonst etwas möchte, das gewisse Nachteile hat. Also, wenn man das sauber macht, vergeben wir explizit hinter dem Schlüsselwort "CONSTRAINT" den Namen für ein solches. Dass der Primärschlüssel automatisch vergeben werden soll, kann ich wahlweise angeben, aber "NOT NULL" kann ich weglassen, wenn ich einen Primärschlüssel in derselben Anweisung definiere. Da passiert das automatisch. Die Spalte "kunde" mussten wir als "int" definieren, weil der Primärschlüssel, auf den wir verweisen, ebenfalls diesen Datentyp hier verwendet. Über "NOT NULL" lege ich fest, dass ich immer einen Kunden für die Adresse eingeben muss, das würde ja bei einem Foreign Key nicht automatisch passieren. Und hier die Syntax für den Fremdschlüssel: "CONSTRAINT", Name für den Fremdschlüssel: "fk_", Ausgangstabelle, "_" Zieltabelle, das ist die typische Variante, "FOREIGN KEY", also Fremdschlüssel, "REFERENCES" verweist auf die Zieltabelle und die Zielspalte. Danach die drei Spaltennamen 1–3, die ich mit einer maximalen Länge von 100 Zeichen definiert habe als "varchar". Wenn Sie so international sein wollen, dass Sie auch bestimmte Sonderzeichen, slawische, oder überhaupt chinesische, japanische und andere Zeichen verwenden wollen, müssten Sie hier den "nvarchar", den national character, verwenden. Zumindest eine Adresszeile müssen wir vergeben, deshalb hier das "NOT NULL". Name 2 und 3 habe ich nicht als "NOT NULL" definiert. Das Länderkennzeichen bis zu drei Zeichen, Postleitzahl habe ich jetzt hier bis zu acht Zeichen definiert, könnte natürlich auch länger sein, wenn spezifische Länder benötigt werden. Auch die Postleitzahl muss erfasst werden, und dann habe ich jetzt hier das Check-Constraint definiert. "ck" für check, "_adressen", Name der Tabelle und am Schluss noch die betroffene Spalte, also "plz CHECK". Das ist eine Möglichkeit, dass die Länge mindestens vier Zeichen betragen muss, Name der Spalte "plz LIKE"" und dann hier jetzt genau 4 Unterstriche, das heißt, exakt vier Zeichen und ein Prozentzeichen. Das heißt eben mindestens vier Zeichen wieviele anschließend noch kommen, wäre egal. Mehr als acht können es natürlich ohnehin nicht sein. Bezeichnung des Ortes zum Beispiel maximal 50 Stellen, ebenso die Straße und dann das Datum der Anlage. Da habe ich als Datentyp zum Beispiel ein "smalldatetime" verwendet, und über einen Defaultwert definiert, dass er vergeben werden muss. Das, was wir hier sehen, ist jetzt eine Besonderheit für den Microsoft SQL Server. Standardwerte, Defaultwerte, werden nicht als Spalteneigenschaft, sondern als Constraint implementiert. Deshalb gleiche Syntax, wie bei einem Primary Key, Foreign Key oder Check-Constraint, nämlich "CONSTRAINT", "df" für default, "_adressen_anlage". Ich kann das natürlich auch weglassen, und auch nur "DEFAULT SYSDATETIME" hier hinschreiben, dann habe ich eben die kurze Syntax und der Name wird automatisch vergeben. Es ist halt nicht so schön, wie wenn ich es selber bewerkstellige. Das Gleiche bei "aktuell" als "bit", das heißt, der kann wahr oder falsch sein, "DEFAULT 1" hier über einen Constraint definiert. Das heißt, der Standardwert beträgt hier "1" und "NOT NULL". Der Wert muss vergeben werden. Ich führe nun einmal diese Anweisung erstmalig aus und ich habe jetzt damit hier diese Tabelle erstellt. Ich kann Constraints wahlweise natürlich auch auf Tabellenebene definieren. Um Ihnen das zu zeigen, lösche ich die soeben erzeugte Tabelle noch einmal und habe hier die zweite Variante. Was ist hier anders? Alle Constraints – mit Ausnahme der Defaultwerte, für die funktioniert das nicht – habe ich an das Ende der Anweisung verschoben. Das heißt, beim Anschluss an die letzte Spalte, das ist "aktuell", da habe ich noch einmal ein Komma ergänzt, und danach die drei Constraints, der Primärschlüssel das Check-Constraint und der Fremdschlüssel. Die Syntax ist eigentlich fast ident, wie vorhin, Was ist der Unterschied? Zum Beispiel beim Primärschlüssel und Fremdschlüssel ersichtlich dass ich hier zusätzlich, nachdem ich sie nicht auf Spaltenebene definiert habe, noch mit angeben muss, welche Spalte betroffen ist. Das würde sonst nicht funktionieren. Das heißt, ich muss jetzt hier, zum Beispiel beim Primärschlüssel, angeben, dass die Adressnummer dieser ist. Und auch hier beim Fremdschlüssel dass die Spalte "kunde" betroffen ist. Immer dann, wenn der Fremd- oder Primärschlüssel sich auf mehrere Spalten bezieht, müssen wir sogar diese Variante mit der Definition auf Tabellenebene verwenden. Die zuerst gezeigte Variante ist nur möglich, wenn Constraints sich immer nur auf eine Spalte alleine beziehen. Auch diese Anweisung führe ich jetzt einmal aus und habe damit diese Tabelle erzeugt. Ich möchte Ihnen aber natürlich auch die dritte Variante nicht vorenthalten und deshalb lösche ich jetzt diese Tabelle noch ein zweites Mal. In der dritten Variante vergebe ich hier überhaupt keinen Constraint. Das heißt, Sie sehen genau die gleiche Definition der Tabelle, aber sogar die Defaultwerte habe ich hier beim SQL-Server noch weggelassen. Ich führe diese Anweisung aus und habe jetzt eine Tabelle ohne jegliches Constraint, kein Primärschlüssel, kein Fremdschlüssel, Standardwert, nichts ist hier vergeben. Nun kann ich das nachholen, indem ich mit einer Alter-Table-Anweisung nachträglich das erledige. Eigentlich relativ einfach. Die Grundanweisung lautet: "ALTER TABLE", Name der Tabelle, in unserem Fall "wawi.adressen" und "ADD", füge hinzu. Und das, was wir danach hinschreiben, ist die exakt selbe Syntax, wie wenn wir ein Constraint auf Tabellenebene definiert hätten. Das heißt einfach nur, "ALTER TABLE", Name der Tabelle, "ADD" und die Definition. Und damit habe ich jetzt zum Beispiel hier den Primärschlüssel ergänzt. Mit dieser Anweisung füge ich das Check-Constraint für die Postleitzahl hinzu, mit der dritten Anweisung den Fremdschlüssel, der auf die Kundentabelle verweist. Und auch die Defaults könnte ich auf diese Art und Weise nachträglich ergänzen. Was muss ich jetzt hier von der Syntax beim Microsoft SQL Server beachten? Dass hier im Anschluss noch am Ende noch "FOR" und der Name der Spalte, also "FOR anlage" oder hier "FOR aktuell", ergänzt werden muss, damit ich das machen kann. Damit führe ich die zwei Anweisungen hier gleich gemeinsam aus und habe jetzt diese Standardwerte definiert. In allen drei Fällen habe ich eine idente Tabelle erzeugt und zum Abschluss füge ich in diese Tabelle noch mit dieser Anweisung drei Testdatensätze ein. Diese beziehen sich auf unterschiedliche Kundennummern. Zwei beziehen sich hier auf dieselbe Kundennummer, nämlich 136, und die dritte Adresse auf die Kundennummer 132 als Fremdschlüssel. Beim Microsoft SQL Server werden Identity-Spalten, also in unserem Fall die Adressnummern, ja beim "INSERT" einfach ignoriert. Noch ist die Tabelle leer, wir haben sie ja gerade erzeugt, es ist noch nichts enthalten. Und nun füge ich diese drei Datensätze ein. Das ist nun das Ergebnis: Diese drei Zeilen haben wir in die Tabelle eingefügt. Zur Kontrolle geben wir uns nicht nur die Adressen am Ende aus, sondern auch kombiniert, in dem Fall mit den Kunden. Das heißt ich habe hier Nachname, Vorname und Kundennummer plus die wichtigsten Informationen aus der Adresstabelle verwendet und diese beiden über Kundennummer und die Spalte "kunde" gejoint. Damit sehen wir das Ergebnis, dass hier für Kundennummer 136, die Petra Deutschmann, diese zwei Adressen und für die Ursula Sauber mit der Kundennummer 132 diese Adressen hier erfasst worden sind. Wenn Sie eine Tabelle erstellen, dann können Sie also ein Constraint bereits beim Erstellen entweder auf Spaltenebene direkt bei einer Spalte angeben, auf Tabellenebene am Ende der Definition der Tabelle oder anschließend nach dem Erstellen der Tabelle über eine Alter-Table-Anweisung ergänzen. Achten Sie dabei darauf beim Microsoft SQL Server, dass diese Anweisung das Ergänzen des Primärschlüssels im Nachhinein nur dann erfolgt, wenn zu diesem Zeitpunkt die Spalte entweder als Identity oder explizit als not null definiert worden ist, sonst muss das vorher noch passieren. Ich kann einen Primärschlüssel nicht aus einer Spalte machen, die NULL-Werte noch zulässt. Das funktioniert eben nur, wenn es innerhalb derselben Anweisung hier passiert, dann brauche ich das nicht zu definieren. Wenn Sie das Ganze wiederholen und noch weiter testen wollen, können Sie mit der Anweisung "DROP TABLE" gefolgt vom Namen der Tabelle, die Sie auch wieder löschen.

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!