Begleitmaterial zum Unterricht M100 «Abfragen»

Daten auswerten

1. Aufgabenblock

Benutzen Sie für diese Übung die folgende LibreOffice-DB: Firma_1.odb
Beantworten Sie die folgenden Fragen, die sich auf die drei Tabellen TPerson, TOrt und TRechnung beziehen. Notieren Sie die Antworten.

  1. Wie heisst die Person mit dem Nachnamen "Neubert" mit Vornamen?
  2. Welche Postleitzahl hat der Ort Mumpf?
  3. Wie lauten die Nachnamen von allen Personen, die mit Vornamen Horst heissen?
  4. Welche Personen sind vor dem 01.01.1965 geboren?
  5. Aus welchem Ort kommt Bianca Heling?
  6. Welche Personen kommen aus Mumpf?
  7. Welche Personen kommen aus dem Kanton Jura?
  8. Wie hoch ist der Rechnungsbetrag von "Jörg Mäder"?
  9. Aus welchem Kanton kommt die Person mit der Rechnung Nummer 8?
  10. Aus welchen Kantonen kommen Personen mit Rechnungen über 8000.- sFr.?

2. Aufgabenblock

Benutzen Sie für diese Übung die folgende LibreOffice-DB: Firma_2.odb
Beantworten Sie die folgenden Fragen und notieren Sie die Antworten bzw. Abfragen.

  1. Wie viele Datensätze enthält die Tabelle tbl_Orte?
  2. Erstellen Sie eine Abfrage, die nur die Ortsnamen und die Kantone anzeigt.
  3. Sortieren Sie das Ergebnis von Frage 2 nach den Ortsnamen – kontrollieren sie was unter „aufsteigend“ und unter „absteigend“ verstanden wird.
  4. Erstellen Sie eine Abfrage mit allen Attributen der Tabelle tbl_Orte, aber nur mit den Orten aus dem Kanton Aargau. Wie viele Datensätze erhalten Sie?
  5. Blenden Sie bei dem Ergebnis der Frage 4. den Kanton aus.
  6. Als nächstes suchen Sie von den Datensätzen, die aus dem Kanton Aargau sind noch diejenigen heraus, deren Postleitzahl kleiner als 5000 ist.
  7. Wir schränken die Auswahl weiter ein und suchen die Aargauer Gemeinden, deren Postleitzahl grösser oder gleich 4800 ist und kleiner als 5000.
  8. Zusätzlich zum Punkt 7 wollen wir noch alle Aargauer Orte aufgelistet haben, deren Postleitzahl grösser als 8900 ist (NUR die aus dem Aargau!).
  9. Wir suchen alle Orte, deren Bezeichnung mit „Me“ beginnt.
  10. Suchen Sie alle Orte, die auf „wil“ enden.
  11. Wie viele Orte enden auf „wil“ oder „kon“?
  12. Welche Orte beginnen mit dem Buchstaben D und enden auf „kon“?
  13. Die Postleitzahlen sind hier als Texte gespeichert. Wir möchten alle Orte, die eine volle tausender Postleitzahl haben (1000, 2000, usw.).

3. Aufgabenblock

Benutzen Sie für diese Übung die folgende LibreOffice-DB: Firma_3.odb
Bevor Sie mit der Beantwortung der Fragen beginnen, machen Sie sich darüber klar, wie die Tabellen miteinander verknüpft sind. Zeichnen Sie ein Schema der fünf Tabellen mit den jeweiligen Attributen, über die sie verknüpft sind. Beantworten Sie nun die folgenden Fragen und notieren Sie die Antworten bzw. Abfragen.

  1. Wie viele Positionen enthält die Rechnung Nummer 1 vom 01.03.2002?
  2. Wie viele Teile (Stück über alle Artikel) enthält Rechnung Nummer 2?
  3. Wie oft wurde der Artikel Nummer 2 verkauft?
  4. Wie viel Umsatz wurde mit dem Artikel Nummer 2 erzielt?
  5. Wie lautet der Gesamtbetrag bei Rechnung Nummer 4?
  6. Ermitteln Sie das Gewicht der Lieferung von Rechnung Nummer 4
  7. Wie hoch ist der Gesamtumsatz von dem Kunden Horst Kolesnik?
  8. Erstellen Sie die Abfragen für den dritten Eintrag in TRechnung (ID-Rechnung = 3), die als Basis für eine Rechnung (Bericht) dienen können: a. Rechnungsdatum und Empfänger, b. Rechnungspositionen und c. Totalbetrag (siehe Bild unten)

4. Aufgabenblock

Benutzen Sie für diese Übung die folgende LibreOffice-DB: Firma_4.odb
Bevor Sie die Abfragen erstellen, sollten Sie sich die Tabellen und die Beziehungen anschauen, um zu wissen, wo Sie welche Informationen finden. Die vorliegende Datenbank ist aus einer produktiven Umgebung kopiert und stark vereinfacht worden. Ausserdem sind die Daten verfälscht.
Notieren Sie jeweils die Antwort der Frage und speichern Sie die Abfragen in der Datenbank ab.

Im folgenden einfache Abfragen über eine Tabelle:

  1. In welchem Ort wohnt eine Person mit dem Nachnamen "Dra"?
  2. Sie haben eine Nachricht für eine Person, deren Nachname die Buchstabenkombination "ap" enthält und die in Luzern wohnt. Wie lautet der Vorname?
  3. Wie viele Personen hatten ihre Ankunft im Juni 1999?
  4. Wie viele Personen hatten ihre Ankunft oder Abreise im Juni 1999?

    Im folgenden einfache Abfragen über mehrere Tabellen:

  5. Welches ist die Ankunft von "Paul Müller"?
  6. Wie lauten die Namen der Personen, die mehr als 30 Tage Aufenthalt hatten?
  7. Erstellen Sie eine Liste mit allen Personen, die eine Ankunft im Jahre 2000 hatten und aus einem Ort mit einer 8000-er Postleitzahl kommen. Notieren Sie die Anzahl der Datensätze.

    Bemerkungen für die folgenden Fragen: Der Rabatt ist in dieser Datenbank als Faktor (eine Zahl zwischen 0 und 1) angegeben. Dies entspricht dem Rabattsatz in Prozent geteilt durch 100. Die Frage nach den grössten Beträgen können Sie ohne Funktion beantworten: Absteigend sortieren und den ersten Wert nehmen. Die Mehrwertsteuer können Sie hierbei ignorieren.
  8. Wer hat den grössten Nettobetrag pro Position ausgegeben? (Formel angeben!)

    Im folgenden Berechnungen mit dem Datentyp Zahl:

  9. Berechnen Sie pro Datensatz der Tabelle "Positionen" den Bruttobetrag (Anzahl * Preis). Welches ist der grösste Totalbetrag? (Formel angeben!)
  10. Berechnen Sie pro Datensatz der Tabelle "Positionen" den Rabattbetrag (Beispiel: 10% entspricht 0.1). Welches ist der grösste Rabattbetrag? (Formel angeben!)
  11. Berechnen Sie pro Datensatz der Tabelle "Positionen" den Nettobetrag (Bruttobetrag – Rabattbetrag) Welches ist der grösste Nettobetrag? (Formel angeben!)

    Im folgenden Filtern mit dem Datentyp Text:

  12. Erstellen Sie in einer Abfrage eine neue Spalte. Geben Sie in dieser Spalte Vorname und Nachname durch Komma getrennt aus. (Formel angeben!)
  13. Erstellen Sie in einer Abfrage eine neue Spalte. Gestalten Sie die Ausgabe gemäss folgendem Beispiel: Peter Muster aus Uster. (Formel angeben!)
  14. Erstellen Sie in einer Abfrage eine neue Spalte. Geben Sie den ersten Buchstaben des Vornamens und den Nachnamen aus. z.B: P. Muster. (Formel angeben!)

    Im folgenden Filtern mit dem Datentyp Datum:

  15. Berechnen Sie mit einer Abfrage über die Tabelle "Buchung" die Aufenthaltsdauer (Abreise - Ankunft). (Formel angeben! Tipp: Siehe Fachartikel)
  16. Geben Sie in einer Abfrage über die Tabelle "Buchung" nur das Jahr der Ankunft aus. Verwenden Sie die Year(date)-Funktion. (Formel angeben!)

    Im folgenden Abfragen mit Berechnungen (Aggregatsfunktionen):

  17. Wer hat den grössten Nettobetrag pro Buchung? (machen Sie sich den Unterschied zwischen "pro Buchung" und "pro Position" klar!)
  18. Welcher Mitarbeiter gab am meisten Rabatt?
  19. Wieviel Kunden wurden von Esther Müller bedient?
  20. Erstellen Sie eine Liste der Nettoumsätze pro Quartal. Erste Kolonne: Jahr. Zweite Kolonne: Quartal 1-4. Dritte Kolonne: Nettoumsatz.

5. Aufgabenblock

Benutzen Sie für diese Übung die folgende LibreOffice-DB: Firma_5.odb
Fassen Sie alle Resultate in einem Dokument zusammen, das Sie auf Ihr OneDrive-M100-Verzeichnis hochladen.

  1. Abfragen: (eine Tabelle, einfaches Kriterium)
    a) Für welches Erzeugnis (Erzeugnisbeschreibung) wurde ein Betrag von SFr. 1150.00 verrechnet?

    b) In wie vielen Datensätzen der Tabelle "TInverkehrbringer" taucht das Wort "markt" im Feld "Inverkehrbringer" auf?

  2. Abfragen: (eine Tabelle, verknüpfte Kriterien)
    a) In der Tabelle "TMangelcode_History" werden alle Mängel zu den Erzeugnissen erfasst. Notieren Sie alle Primärschlüssel der Mängel, die zwischen dem 8.09.1999 und dem 14.09.1999 erfasst wurden (inklusive der beiden Randdaten).

    b) In der Tabelle "TInverkehrbringer" sind die Firmen erfasst, die die Erzeugnisse verkauft haben. In wie vielen Datensätzen kommt im Feld "Inverkehrbringer" der Ausdruck "Jumbo" vor, aber nicht der Ausdruck "markt".
    Tipp: Sie können jedes Kriterium ins Gegenteil kehren, wenn Sie den Operator "Nicht" vor das Kriterium setzen. z. B: Nicht Wie 'Hans*' => alle Datensätze die nicht mit "Hans" beginnen.

  3. Abfragen: (mehrere Tabellen, einfache Kriterien)
    a) Wie lautet der "MangelCodeText" der Mängel, die zwischen dem 8.09.1999 und dem 14.09.1999 erfasst wurden (inklusive der beiden Randdaten)?

    b) Wie heisst die Handelsmarke des Erzeugnisses, bei dem für einen Arbeitsschritt 10 Stunden erfasst wurden?

  4. Abfragen: (mehrere Tabellen, verknüpfte Kriterien)
    a) Wie lauten die Typenbezeichnungen für die Stichsägen ("Erzeugnisbeschreibung") der Handelsmarken "Festo" und "Bosch"?
    Tipp: Feld "Typenbezeichnung" aus Tabelle "TErzeugnisse"

    b) Wie heissen die Handelmarken der Erzeugnisse, für die Eduardo de Anta als Sachbearbeiter zuständig ist und für die ein Verrechnungsbetrag von SFr. 750.- erfasst wurde?

  5. Abfragen: (berechnete Felder)
    a) Erstellen Sie eine Abfrage, die als Resultat die untenstehende (siehe Bild 1) Tabelle ausgibt. Notieren Sie, wie Sie das neue Feld "Produkt" definieren.

    b) Wie können Sie mit einer Abfrage (siehe Bild 2) die Kosten pro Arbeitsschritt berechnen, wenn pro Stunde SFr. 125.- verrechnet werden? Notieren Sie, wie Sie das neue Feld "Kosten" definieren.

  6. Abfragen: (mit Aggregats-/Guppierungsfunktionen)
    a) Es soll abgefragt werden, für welche Handelsmarke insgesamt am meisten Arbeitszeit eingegeben wurde. Dabei spielt es keine Rolle welche Erzeugnisse betroffen sind. Notieren Sie die Handelsmarken mit den drei höchsten summierten Arbeitszeiten (siehe Bild 3).

    b) Nennen Sie die Beschreibung des Erzeugnisses für das 37-mal ein Arbeitsschritt erfasst wurde.

Musterlösungen

Die Lösungen zum 3. Aufgabenblock finden Sie hier: Firma_3LSG.odb

Die Lösungen zum 4. Aufgabenblock finden Sie hier: Firma_4LSG.odb