Datenbanken

Heute dreht sich alles um die vier Grundbefehle in SQL:

INSERT, SELECT, UPDATE, DELETE

ID: 9999

Name: Mannis Stübchen

Kontakt: Manfred Liebherr

Adresse: Hinteres Eck 3

Stadt: Obersdorf

PLZ: 87561

Land: Germany

Mit INSERT kannst du Daten in die Tabellen einfügen.

Dein Online Shop läuft erfolgreich und du hast drei neue Kunden akquiriert. Klicke diesen Link und ergänze die Datenbank, die du von deiner Lehrkraft bekommen hast.

ID: 10000

Name: Carne de Mamacita

Kontakt: Maria Gonzales

Adresse: Gran Via de les Corts 660

Stadt: Barcelona

PLZ: 08010 

Land: Spain

ID: 10001

Name: SKANDINAVIUM

Kontakt: Olaf Andersson

Adresse: Blasieholms 4A

Stadt: Stockholm

PLZ: 11148

Land: Schweden

Insert ohne Spaltenangabe kann Zeit sparen, funktioniert jedoch nur, wenn Daten zu allen Spalten angegeben werden.

Aufgaben

  • Trage die neuen Kunden in deine Datenbank ein. Orientiere dich dabei an dem Beispiel oben.  
  • Was passiert, wenn du nur Name und Kontakt angibst? Schaue dir dazu die Infobox unten an.
  • Was passiert, wenn du eine neue Zeile mit der ID "1" einfügen möchtest?

www.appcamps.de

INSERT INTO Customers VALUES                                                                                                                                                                  

(9999, 'Mannis Stübchen', 'Manfred Liebherr', 'Hinteres Eck 3', 'Obersdorf',                                                                                      

'87561', 'Germany' )                                                                                                                                                                                        

 

Mit INSERT kannst du Daten in die Tabellen einfügen.

Was passiert, wenn du eine neue Zeile mit der ID "1" einfügen möchtest?

Die folgende Fehlermeldung wird angezeigt:

Warum kann die Zeile nicht eingefügt werden? 

  • In der Kunden Tabelle existiert bereits eine Zeile mit der ID "1". Bei Datenbanken können Spalten sogenannte "Constraints", also Beschränkungen zugewiesen werden. Das bedeutet, dass Spalten bestimmte Eigenschaften haben. In diesem Fall, dass der Spalte "ID" immer ein eindeutiger ("unique") Wert zugewiesen werden muss. Mehr dazu in Sitzung vier. 

www.appcamps.de

Aufgabe

  • Trage die beiden MitarbeiterInnen ein. Orientiere dich dabei an dem Beispiel oben. Was fällt dir im Vergleich zu vorher auf?  
  • Schau dir die Mitarbeiter-Tabelle an: Warum musst du keine ID vergeben und es wird trotzdem eine erstellt? Was passiert, wenn du eine neue Mitarbeiterin einträgst? Lies dir dazu die Info-Box Auto-increment durch. 

Da du so viele neue Kunden gewonnen hast, brauchst du auch neue MitarbeiterInnen. Trage ihre Daten bitte in deine Datenbank  ein:

Auto-increment erstellt automatisch einen Wert z.B. für die ID, sobald eine Zeile mit Daten befüllt wird. Dieser Wert entspricht niemals einer bereits existierenden ID und wird automatisch erhöht.

Name: Anna Klug

Geburtstag: 08/06/1996

Bild: annaKlug96.pic

Notizen: Praktikantin. Studiert Informatik.

Name: Tarek Fischer

Geburtstag: 12/30/1992

Bild: tarekFischer92.pic

Notizen: Absolvent. Bachelor. Probezeit 6 Monate.

Mit INSERT kannst du Daten in die Tabellen einfügen.

www.appcamps.de

INSERT INTO Employees                                                                                                                                                                                  

 (lastname,  firstname,  birthdate,  photo,  notes)                                                                                                                                        

 VALUES  ('Klug', 'Anna', '08/06/1996', 'annaKlug96.pic', 'Praktikantin. Studiert Informatik')                                                                                                                            

 

Aufgabe

  • Aktualisiere die Notizen zu Anna und das Geburtsdatum von Tarek (rot markiert). Bei Anna muss ergänzt werden, dass sie ein 3-monatiges Praktikum macht. Außerdem wurde das Geburtsdatum von Tarek falsch eingetragen und muss korrigiert werden. Orientiere dich dabei an dem Beispiel oben. 

      Tipp: Du musst dir die passende EmployeeID raussuchen. Anna hat in dem Beispiel die ID 11.

Du hast deine neuen MitarbeiterInnen erfolgreich in die Datenbank eingetragen. Leider ist bei der Datenabfrage ein Fehler unterlaufen und es muss etwas ergänzt werden. Ändere die Daten in deiner Datenbank:

Name: Anna Klug

Geburtstag: 08/06/1996

Bild: annaKlug96.pic

Notizen: Praktikantin (3 Monate). Studiert Informatik.

Name: Tarek Fischer

Geburtstag: 11/30/1992

Bild: tarekFischer92.pic

Notizen: Absolvent. Bachelor. Probezeit 6 Monate.

Mit UPDATE kannst du Daten in den Tabellen ändern.

www.appcamps.de

UPDATE Employees                                                                                                                                                                                      

SET  Notes = 'Praktikantin (3 Monate). Studiert Informatik'                                                                                                                        WHERE  employeeid  = 11                                                                                                                                                                          

 

Aufgabe

  • Lösche die MitarbeiterInnen aus deiner Datenbank. Orientiere dich dabei an dem Beispiel oben. 
  • Schau dir die Abfrage an: Wieso kannst du dir sicher sein, über die EmployeeID auf jeden Fall deine Praktikantin Anna Klug zu löschen und nicht versehentlich einen anderen Mitarbeiter? Lies dir dazu die Info-Box zum Primärschlüssel durch. 

Es ist ein Jahr vergangen und deine Praktikantin Anna hat ihr Praktikum erfolgreich abgeschlossen. Auch Tarek wechselt zu einem anderen Online-Shop, da er nach Australien auswandert. Passe deine Datenbank entsprechend an:

Name: Anna Klug

Geburtstag: 08/06/1996

Bild: annaKlug96.pic

Notizen: Praktikantin. Studiert Informatik.

Name: Tarek Fischer

Geburtstag: 11/30/1992

Bild: tarekFischer92.pic

Notizen: Absolvent. Bachelor. Probezeit 6 Monate.

Mit DELETE kannst du Daten in den Tabellen entfernen.

Ein Primärschlüssel ist immer eindeutig. Das bedeutet, es gibt immer genau einen Wert für eine Zeile (also für jeden Mitarbeiter, für jeden Kunden etc.). Deswegen werden meistens Zahlen verwendet. Der Primärschlüssel wird häufig mit auto-increment vergeben, um die Dateneingabe zu vereinfachen.  

www.appcamps.de

DELETE   FROM Employees WHERE employeeid  = 11                                                                                                                                                                                      

                                                                                                                                                                     

Für deinen Online Shop bekommst du folgende Bestellung, die du in deine Datenbank in die Tabellen Orders und OrderDetails eintragen möchtest:

  • Ana Trujillo bestellt 34 Mal Gnocchi. Die Bestellung wird am 22. Juni 2017 aufgenommen und von Nancy Davolio bearbeitet. Der involvierte Spediteur ist "United Package".

​Hier findest du die Tabellen zu den Bestellungen (Orders) und den Bestelldetails (OrderDetails):

Aufgabe

Auf den nächsten Seiten findest du eine Erläuterung, wie die Bestellung in der Datenbank gespeichert      wird.

Beachte! In der Order-Tabelle stehen keine Mitarbeiterdaten (Employee) wie Name oder Geburtsdatum. Du findest dort lediglich die EmployeeID. Über diese ID kommst du aber an die ganzen Mitarbeiterdaten ran. Wie? Indem du in der Employee-Tabelle nach der entsprechenden EmployeeID suchst.

Diese EmployeeID nennt man auch Fremdschlüssel. So findest du die EmployeeID als Primärschlüssel in der Mitarbeitertabelle (Employees) und als Fremdschlüssel in der Tabelle zu den Bestellungen (Orders). Ein Mitarbeiter, z.B. mit der EmployeeID = 1, kann mehrere Bestellungen (Orders) verwalten. Die EmployeeID kann also mehrfach vorkommen. Durch das Verwenden der IDs kannst du also mehrere Bestellungen demselben Mitarbeiter zuweisen, ohne immer die gesamten Mitarbeiterdaten bei jeder Bestellung angeben oder ändern zu müssen. Die Daten können leichter verwaltet und bei Bedarf geändert werden.

www.appcamps.de

Für deinen Online Shop bekommst du folgende Bestellung, die du in deine Datenbank in die Tabellen Orders und OrderDetails eintragen möchtest:

  • Ana Trujillo  bestellt 34 Mal  Gnocchi. Die Bestellung wird am 22. Juni 2017 aufgenommen und von Nancy Davolio bearbeitet. Der involvierte Spediteur ist "United Package".
  • Anders ausgedrückt: Der Kunde mit der ID "2" bestellt "34 Mal" das Produkt mit der ID "56". Die Bestellung wird am "07/05/1996" aufgenommen und von dem Mitarbeiter mit der ID "1" bearbeitet. Der involvierte Spediteur hat die ID "2".

 

 

Aufgabe

In welchen Tabellen werden weitere Informationen gespeichert, damit du z.B. den Namen des Kunden oder des Mitarbeiters herausfinden kannst? Nimm dir ein paar Minuten Zeit, um das herauszufinden.

  • Woher weißt du, welche ID zu welchem Kunden gehört?
  • Woher weißt du, zu welcher ID welcher Mitarbeiter gehört?

Auf der nächsten Seite findest du mögliche Antworten. Überleg aber zuerst selbst, bevor du weiter klickst.

www.appcamps.de

Aufgaben

  • Versuche, die Beziehungen mit Pfeilen zu visualisieren.
    • Zum Beispiel  so: 

Auf der nächsten Seite findest du mögliche Antworten. Überleg aber zuerst selbst, bevor du weiter klickst.

Informationen zu den Bestellungen findest du hier:

 

 

Für Bestellungen (Orders) benötigst du Informationen aus folgenden Tabellen:

 

 

außerdem musst du eintragen:

Für Bestelldetails (OrderDetails) benötigst du Informationen aus folgenden Tabellen:

 

 

außerdem musst du eintragen:

Für Informationen zu Kategorien und Lieferanten bestellter Produkte brauchst du:

 

 

Produkte (Products)

Kunden (Customers)

Lieferanten (Suppliers)

Kategorien (Categories)

Mitarbeiter (Employees)

Bestellungen (Orders)

Bestelldetails (OrderDetails)

Spediteure (Shippers)

Bestellungen (Orders)

OrderDate

Quantity

Bestellungen (Orders)

Kunden (Customers)

www.appcamps.de

Produkte (Products)

Kunden (Customers)

Lieferanten (Suppliers)

Kategorien (Categories)

Mitarbeiter (Employees)

Bestellungen (Orders)

Bestelldetails (OrderDetails)

Spediteure (Shippers)

Bestellungen (Orders)

OrderDate

Quantity

Aufgaben

Auf der nächsten Seite geht es weiter mit dem Beispielszenario.

www.appcamps.de

Für deinen Online Shop bekommst du folgende Bestellung, die du in deine Datenbank in die Tabellen Orders und OrderDetails eintragen möchtest:

  • Ana Trujillo  bestellt 34 Mal  Gnocchi. Die Bestellung wird am 22. Juni 2017 aufgenommen und von Nancy Davolio bearbeitet. Der involvierte Spediteur ist "United Package".
  • Anders ausgedrückt: Der Kunde mit der ID "2" bestellt "34 Mal" das Produkt mit der ID "56". Die Bestellung wird am "06/22/2017" aufgenommen und von dem Mitarbeiter mit der ID "1" bearbeitet. Der involvierte Spediteur hat die ID "2".

 

 

Zurück zu der Beispielbestellung:

Aufgabe

  • Hast du eine Idee, wie deine passenden SQL-Abfragen aussehen könnten?
  • Überlege dir außerdem eine weitere Bestellung und schreibe diese auf. Du kannst dich an der Beispielbestellung orientieren.

Auf der nächsten Seite findest du mögliche Antworten. Überleg aber zuerst selbst, bevor du weiter klickst.

www.appcamps.de

Mit INSERT kannst du Daten in die Tabellen einfügen.

Wir schauen uns nun an, wie du Bestellungen in die Tabellen (Orders und OrderDetails)  eintragen kannst.

Der Kunde mit der ID "2" bestellt am 22 Juni 2017. Die Bestellung wird von dem Mitarbeiter mit der ID "1" betreut und der Spediteur hat die ID "2".

Anders ausgedrückt: Ana Trujillo bestellt 34 Mal Gnocchi. Die Bestellung wird am 22. Juni 2017 aufgenommen und von Nancy Davolio bearbeitet. Der involvierte Spediteur ist "United Package".

Bei der Bestellung mit der ID "10444" wurde 34 Mal das Produkt mit der ID "56" geordert.

Tipp: Um die richtigen Details den richtigen Bestellungen zuzuweisen, musst du die entsprechende OrderID nachschauen.

Aufgabe

  • Gib die hier aufgeführten SQL Befehle selbst ein und schaue dir die Ergebnisse an. 
  • Gib außerdem deine selbst formulierte Bestellung als SQL-Abfrage ein (sofern du es nicht schon gemacht  hast) und schau dir das Ergebnis an.

Auf den nächsten Folien findest du Übungen. Viel Spaß dabei!

www.appcamps.de

INSERT INTO Orders                                                                                  

(customerID, employeeID, orderdate, shipperID)                                  

VALUES (2 ,  1,  '06/22/2017',  2)                                                              

 

INSERT INTO OrderDetails                              

(OrderID, ProductID, Quantity)                        

VALUES (10444 ,  56,  34)                                  

Mit INSERT kannst du Daten in die Tabellen einfügen.

Wir schauen uns zunächst die Tabelle mit den Bestellungen (Orders) und den Bestelldetails (OrderDetails) an. 

 

Aufgaben

  • Gib die hier aufgeführten SQL Befehle selbst ein und schau dir die Ergebnisse an. 
  • Fügt SQL tatsächlich nur Daten ein, die du angibst? Was mit der OrderID passiert, hast du bereits auf den vorherhigen Folien gelernt (Infobox zu Auto-increment), aber was passiert mit den anderen Spalten? Schau dir dazu die Infobox über "Null" an. 

Fügt die Daten (Values) in die Spalten ein, die genannt werden. 

Fügt zwei Bestelldetails gleichzeitig in die Datenbank ein.

Null ist ein Wert, der von SQL automatisch in die Datenbank eingetragen wird, wenn keine Angabe erfolgt.

Fügt die Bestellungen ohne Spaltenangabe direkt ein.

www.appcamps.de

INSERT INTO Orders                                                                  

(OrderID, CustomerID, OrderDate)                                         

VALUES  (10446 ,  4,  '08/02/2016')                                    

 

INSERT INTO OrderDetails                                                        

(OrderID, ProductID, Quantity)                                               

VALUES  (10446 ,  33,  20  ) ,   (10446 ,  44 , 2  )                  

 

INSERT INTO Orders                                                                

VALUES    (99999 ,  12,  3, '06/18/2017', 2 )                          

Aufgaben

  • Wie kannst du dir die Ausgabe von oben (inklusive deiner Bestellung) anzeigen lassen? Erinnere dich an die letzte Sitzung. Versuche die Ausgabe zunächst selbst zu reproduzieren. Ansonsten findest du auf der nächsten Seite eine mögliche Lösung.
  • Was fällt dir bei genauerer Betrachtung der Bestellungen auf? Nimm dir etwas Zeit, um genau zu überprüfen, ob eventuell Daten fehlen, wenn die Bestellungen bearbeitet werden sollen. Auf der nächsten Seite findest du mögliche Antworten. Überleg aber zuerst selbst, bevor du weiter klickst.

Mit INSERT hast du erfolgreich Daten zu Bestellungen und den entsprechenden Bestelldetails in die Datenbank eingetragen.

Dabei ist es immer sinnvoll mit SELECT zu überprüfen, ob du die Daten auch richtig eingetragen hast.

Falls du etwas ändern willst, verwendest du UPDATE.

www.appcamps.de

SELECT * FROM Orders

 

SELECT * FROM OrderDetails

 

Aufgabe

  • Ergänze die Bestelldetails (z.B. 12x Chang-Bier (ID 2), ...) zur OrderID "99999" und überlege, welche Spalten du bei der OrderID "10446" ergänzen musst.

Auf der nächsten Seite findest du eine mögliche Lösung zu Letzterem. Überleg aber zuerst selbst, bevor du weiter klickst.

Die Überprüfung der Bestellungen und der Bestelldetails gelingt dir z.B. mit folgenden Abfragen:

 

 

 

 

 

 

 

 

 

 

Bei genauerer Betrachtung der Bestellungen und der Bestelldetails fällt auf, dass bei der Bestellung mit der ID "10446" weder ein Mitarbeiter noch ein Spediteur zugeteilt ist. Außerdem sind bei der Bestellung mit der ID "99999" keine Bestelldetails angegeben, sodass weder klar ist, welche Produkte bestellt wurden, noch in welcher Menge.  

Gibt alle Spalten mit einer OrderID größer-gleich 10444 aus.

Gibt aus der Tabelle OrderDetails alle Spalten mit einer OrderID größer-gleich 10444 aus.

www.appcamps.de

SELECT *  FROM Orders                                                               WHERE OrderID >= 10444                                                         

SELECT *  FROM OrderDetails                                             WHERE OrderID >= 10444                                                     

Mit UPDATE kannst du Daten in den Tabellen ändern.

Wir schauen uns zunächst die Tabelle mit den Bestellungen (Orders) und den Produkten (Products) an. 

 

Aufgaben

  • Gib die hier aufgeführten SQL Befehle selbst ein und schau dir die Ergebnisse an. 
  • Wie viele Preise hast du durch die zweite Abfrage verändert?


  • Probiere weitere Abfragen aus. Der SQL Spickzettel hilft dir dabei.  
  • Überprüfe jeweils mit SELECT, ob deine Änderungen wie angedacht funktioniert haben.

Ergänzt die angegeben Spalten mit den entsprechenden Daten.

Ändert den Preis für alle Zeilen auf 25, wenn der Preis größer als 25 ist.

www.appcamps.de

UPDATE Orders                                                                            

SET employeeid = 8, shipperid = 3    

WHERE orderid = 10446                                                            

UPDATE Products                                                                        

SET price = 25                                                                              

WHERE price  > 25                                                                        

Mit UPDATE kannst du Daten in den Tabellen ändern.

Wir schauen uns zunächst die Tabelle mit den Bestellungen (Orders) und den Produkten (Products) an. 

 

Aufgaben

  • Gib den ersten SQL Befehl selbst ein und schau dir die Ergebnisse an. 
  • Wie viele Preise hast du durch die Abfrage verändert?
  • Ändere auch den Preis für alle Produkte, die aktuell 20, 21, 22, 23 oder 24 kosten, auf 25.
  • Gib den zweiten SQL Befehl ein und prüfe die Ergebnisse.
  • Ändere den Mitarbeiter für alle Bestellungen, wo die Mitarbeiterin Margaret Peacock und der Spediteur United Package ist, auf Andrew Fuller.
  • Probiere weitere Abfragen aus. Der SQL Spickzettel hilft dir dabei.  
  • Überprüfe jeweils mit SELECT, ob deine Änderungen wie angedacht funktioniert haben.

Ändert den Preis für alle Zeilen auf 25.

Ändert den Preis für alle Zeilen auf 25, wenn der Preis mit 1 beginnt.

www.appcamps.de

UPDATE Products                                                                      

SET price =25                                                                              

WHERE price  LIKE    '1%'                                                          

UPDATE Products                                                                      

SET price =25                                                                              

Aufgabe

Nimm dir ein paar Minuten, um die einzelnen Tabellen durchzugehen. Welche Daten können nach einem Jahr gegebenenfalls bereinigt werden?

Folgende Fragen sollen dir dabei helfen:

  • Aus welchem Jahr stammt deine Datenbank?
  • Können eventuell Daten aus dem vergangenen Jahr gelöscht werden? 
  • Gibt es Mitarbeiter, die eventuell schon pensioniert sind?  Tipp: Es gibt einen Mitarbeiter mit der Notiz "An old chum", welcher bereits 68 Jahre alt ist. Schau dir das mal genauer an.

Auf der nächsten Seite findest du mögliche Antworten. Überleg aber zuerst selbst, bevor du weiter klickst.

Dein Online-Shop bereinigt die Datenbank ein Mal im Jahr...

www.appcamps.de

Betrachten wir die Tabelle Orders genauer:

 

 

 

 

 

... offenbar ist in den Daten aus der ursprünglichen Datenbank aktuell das Jahr 1997. Angenommen alle Bestellungen aus dem Vorjahr werden jedes Jahr nach der Inventur und Sicherstellung, dass alle Bestellungen aus dem Vorjahr ohne Verspätung abgearbeitet wurden, im März gelöscht, dann können alle Bestellungen aus dem Jahr 1996 aus der Datenbank entfernt werden.

Aufgabe

  • Mache dir Gedanken darüber, ob du in den Bestelldetails nachvollziehen kannst, welche Bestellungen gelöscht wurden.
  • Sind noch andere Tabellen betroffen?

Auf der nächsten Seite findest du mögliche Antworten. Überleg aber zuerst selbst, bevor du weiter klickst.

Achtung! Durch das Löschen der Bestellungen werden die entsprechenden Bestelldetails nicht automatisch gelöscht.

www.appcamps.de

SELECT * FROM Orders

 

Aufgabe

  • Wie kann das Problem gelöst werden? Gibt es überhaupt ein Problem?
    • Hat der Mitarbeiter Adam West noch Bestellungen im Jahr 1997 offen?
  • Überlege dir Lösungen für den Fall, dass ein Mitarbeiter nicht mehr für dich arbeitet, aber noch Bestellungen offen sind. (Tipp: Kannst du dazu vielleicht die Update-Funktion benutzen?)
  • Auf der nächsten Folien findest du Übungen. Viel Spaß mit SQL. 

In der Tabelle Orders wurden die Daten chronologisch gespeichert. Entsprechend können alle Daten zu Bestelldetails mit der OrderID < 10400 gelöscht werden.

 

Betrachten wir außerdem die Tabelle Employees genauer:

 

 

 

 

 

 

 

 

 

 

 

 

Der Mitarbeiter Adam West geht in Rente. Herr West kann also aus der Datenbank entfernt werden.

 

 

 

 

 

Achtung! Auch hier werden durch das Löschen des Mitarbeiters nicht automatisch die entsprechenden Bestellungen gelöscht.

www.appcamps.de

Mit DELETE kannst du Daten aus den Tabellen entfernen.

Wir schauen uns zunächst die Tabelle mit den Bestellungen (Orders) und den Mitarbeitern (Employees) an. 

 

Löscht alle Bestellungen aus dem Jahr 1996.

Löscht alle Bestellungen, die die Mitarbeiter mit der ID 2, 4 oder 10 bearbeiten.

Löscht alle Bestellungen.

Aufgaben

  • Gib die hier aufgeführten SQL Befehle selbst ein und schau dir die Ergebnisse an. 
    • Wie viele Bestellungen aus dem Jahr 1996 hast du gelöscht?
    • Wie viele Bestellungen hast du durch das Ausführen der zweiten Abfrage gelöscht?
  • Lösche alle Kunden aus Mexiko mit der Postleitzahl 05021.
  • Lösche die Mitarbeiter mit den IDs 2, 4 und 10.

​

  • Probiere weitere Abfragen aus. Der SQL Spickzettel hilft dir dabei.  
  • Überprüfe jeweils mit SELECT, ob deine Änderungen wie angedacht funktioniert haben.

www.appcamps.de

DELETE FROM Orders                                                               WHERE orderdate LIKE  '%1996'                                                                                                                                  

DELETE FROM Orders                                                             WHERE employeeid IN (2, 4, 10)                                                                                                                               

DELETE FROM Orders                                                                                                                                                                                           

... das Quiz startest du

HIER

Viel Spaß!

Hast du alle Aufgaben erledigt?

Super! Dann geht es jetzt weiter mit dem Quiz!  

 

www.appcamps.de