Datenbank erforschen

Laden Sie die Datenbank miniterra.db über diesen Link und speichern Sie die Datei auf Ihrem Laptop.

Die Datenbank enthält die folgenden Tabellen:

Sie sind wie folgt verbunden:

  1. [R] Erforschen Sie die Daten in SQLite Studio:
    1. Wie lautet das Länderkürzel (LNR) der Schweiz?
    2. Wie lautet das Kürzel des Kontinents (KNR) von Europa?
    3. Was ist der Anteil von Deutsch als Sprache in der Schweiz (in Tabelle «gesprochen»)?
  2. [R] Versuchen Sie folgende Informationen mit SQL Befehlen abzufragen:
    1. Die Namen aller Orte in der Schweiz und deren Einwohnerzahl
    2. Die Namen aller Orte in Europa, absteigend sortiert nach deren Einwohner-Zahl
    3. Der Anteil der Einwohner der Hauptstädte jeden Landes zu dessen gesamt-Einwohnerzahl, absteigend sortiert
      • Achtung: Einwohner steht im Land in Millionen, in Ort als ganze Zahl. Deswegen muss der Anteil so berechnet werden (Prozent):
        round(ort.Einwohner * 100.0 / land.Einwohner/1000000,2)
    4. Der Name aller Flüsse in Europa
  3. [C] Fügen Sie der Datenbank Münchenstein und die Birs hinzu:
    1. Münchenstein liegt in der Schweiz, im «Landesteil» Basel, hat 12’300 Einwohner, auf der Breite 47 und der Länge 8. Wählen Sie eine sinnvolle «ONR» (Ortsnummer), die nicht schon anderweitig vergeben ist. Wie lautet der SQL Befehl, um diese Stadt einzufügen?
    2. die Birs ist 75 Kilometer lang und mündet in den Rhein. Wie lautet der SQL Befehl, um den Wert für «ZielFNR» zu finden, und jener, um den Fluss Birs hinzuzufügen?
  4. [U] Stellen Sie sich vor, jemand würde einfach den Befehl erteilen, der «Golf von Mexiko» sollte neu «Golf von Amerika» genannt werden: Finden Sie diesen Gewässernamen in der Datenbank? Wenn Sie Ihn finden: wie lautet der SQL Befehl, um den Namen anzupassen?
  5. [D] Nun stellen wir uns vor, dass es eine Klimaveränderung gibt, die dazu führt, dass alle Nebenflüsse des Nils vollends versiegen: Entfernen Sie diese also alle aus der Datenbank. Wie lautet der SQL Befehl, um alle Nebenflüsse des Nils anzuzeigen? Was muss an diesem SQL Befehl verändert werden, um alle diese Nebenflüsse mit einem einzigen Befehl entfernt werden?
  6. In den obigen Trainingseinheiten steht jeweils vor der Frage ein Buchstabe in [eckigen Klammern]. Worauf bezieht sich dieser Buchstabe wohl?

Musterlösungen

  1. Antworten:
    1. CH
    2. EU
    3. 65%
  2. Antworten:
    1. SQL Befehl:
      select Name, Einwohner
      from ort
      where LNR = "CH"
    2. SQL Befehl:
      select ort.Name as Stadt, land.Name as Land, ort.Einwohner
      from ort, land
      where KNR = "EU" and
      ort.LNR = land.LNR
      order by ort.Einwohner DESC
    3. SQL Befehl:
      select
      ort.Name as Stadt,
      land.Name as Land,
      round(ort.Einwohner * 100.0 / land.Einwohner/1000000,2) as Anteil
      from ort, land
      where
      ort.ONR = land.HauptONR
      order by Anteil DESC
    4. SQL Befehl:
      select distinct fluss.Name as Fluss
      from fluss, stadtfluss, ort, land
      where
      fluss.FNR = stadtfluss.FNR
      and stadtfluss.ONR = ort.ONR
      and ort.LNR = land.LNR
      and land.KNR = "EU"
      order by fluss.Name ASC
  3. Antworten:
    1. Die ONR darf höchstens 6 Zeichen enthalten und muss einmalig sein. z.B. «MNCSTN» würde gehen. Dann lautet der Befehl:
      insert into ort (ONR, Name, LNR, Landesteil, Einwohner, Breite, Laenge)
      values ('MNCSTN', 'Münchenstein', 'CH', 'Basel', 12300, 47, 8)
    2. Die ZielFNR muss die FNR des Rheins sein. Diese finden wir mit diesem SQL Befehl:
      select * from fluss where Name = 'Rhein'
      Die FNR hat 3 Buchstaben und muss einmalig sein – z.B. «BRS». Dann lautet der SQL Befehl zum einfügen der Birs:
      insert into fluss (FNR, Name, ZielFNR, Laenge)
      values ('BRS', 'Birs', 'XYZ', 75)
      (wobei XYZ mit der richtigen FNR ersetzt werden muss!)
  4. Meere tauchen in der Datenbank im Datenfeld «Meer» der Tabelle «fluss» auf. Mit dem Befehl
    select * from fluss where Meer = 'Golf von Mexiko'
    tauchen tatsächlich die zwei Flüsse Mississippi River und Rio Grande auf. Es gibt nun verschiedene Wege, um diese zwei Einträge zu ändern. Ein Beispiel:
    update fluss set Meer = 'Golf von Amerika'
    where Meer = 'Golf von Mexiko'
  5. Alle Nebenflüsse des Nils f inden sich mit
    select * from fluss where ZielFNR = 'NIL'
    und um diese alle zu entfernen, muss nur das «select *» durch ein «delete» ersetzt werden:
    delete from fluss where ZielFNR = 'NIL'
  6. CRUD: alles, was mit elektronischen Daten gemacht werden kann:
    • C: Create: erstellen, speichern, einfügen
    • R: Retrieve/Read: abfragen, auslesen, lesen
    • U: Update: anpassen, ändern, aktualisieren
    • D: Delete: löschen, entfernen