Aktuelle Version vom 16. Mai 2020, 09:09 Uhr

Bevor du mit Abfragen angänst, schau dir diesen Video zur Wiederholung des bisher Besprochenen an.

Abfragen

Welche Informationen benötigen bestimmte Personengruppen (z.B. Abteilungsleitung, Kunden, Kassierer(in) … aus der Datenbank „Einkauf“?

Welche Einkäufe wurden am 18.03.2006 getätigt?
Welche Waren wurden überhaupt gekauft?
Wie hoch ist der Preis jedes einzelnen gekauften Artikels?
Welche Kosmetikartikel gibt es?
…

In welcher Form sollen die Antworten ausgegeben werden?

Man hat eine Tabelle mit vielen eingetragenen Werten.
Man hat eine Fragen.
Man will eine Antwort.

Dies erfolgt mit Hilfe von Datenbanken, indem man für eine Tabelle eine Abfrage startet und die Antwort als Ergebnistabelle erhält.

Der Aufbau der Ergebnistabelle wird durch zwei Grundverfahren festgelegt:

Eine Selektion bedeutet eine Auswahl von Datensätzen (Zeilen) in der Datentabelle mit bestimmten Eigenschaften (z.B. Sparte = ’Lebensmittel’)

Bei dieser Selektion interessen welche Lebensmittel eingekauft wurden. Es werden also alle Datensätze in der bei Sparte "Lebensmittel" steht ausgegeben.

Eine Projektion wählt bestimmte Spalten in der Datentabelle aus (z. B. Kunde und Warenbezeichnung)

Bei dieser Projektion werden nur die Spalten Kunde und Warenbezeichnung ausgegeben, wenn einen interessiert was welcher Kunde gekauft hat.

Eine Abfragefuntkion besteht aus drei Teilen und ist immer gleich aufgebaut.

Merke Eine Abfrage startet immer mit SELECT. Dahinter stehen die Spalten, die einen interessieren (Projektion). Danach kommt WHERE mit der Tabelle in der man nachschaut. Und zum Schluss WHERE mit den Bedingungen, die man wissen will (Selektion).

1. Beispiel:
SELECT *
FROM einkauf
WHERE Geschäft = ‘Imagi‘

In OpenOfficeBase schreibt man dies in einer Zeile

Beachte dabei:
Nach SELECT bedeutet *, dass alle Spalten, also ein vollständiger Datensatz ausgegeben wird. Man kann hier auch nur einzelne Spalten, die einen interessieren schreiben.
Nach WHERE steht die Tabelle einkauf, die wir erzeugt hatten und in der alle unsere Datensätze stehen.
Geschäft = 'Imagi' nach WHERE ist die Bedingung, dass nur Einkäufe im Geschäft 'Imagi' ausgegeben werden sollen. Dabei ist der Geschäftsname in einfache Anführungszeichen (OpenOffice! Das kann in anderen SQL-Systemen anders sein) zu schreiben.

In OpenOffice Base startet man eine Abfrage, indem man in der linken Spalte Abfragen auswählt und dann in der Mitte Abfrage in SQL-Ansicht erstellen ... anklicken.

Dann öffnet sich ein neues Fenster in das man die Abfrage schreibt.

2. Beispiel:
In das gerade geöffnete Fenster schreibt man: SELECT Warenbezeichnung, Preis FROM einkauf WHERE Preis > 5

Um die Abfrage zu starten tippe auf die F5-Taste oder klicke auf der Seite oben bei den Icons auf "Abfrage aisführen (F5)".
Du erhältst dann dieses Bild:

In dieser Abfrage erkennt man schön den Unterschied von Selektion und Projektion.

Nach SELECT stehen die Spalten der Projektion (Es werden die Spalten Warenbezeichnung und Preis ausgewählt) und nach WHERE steht die Selektion, die Bedingung zur Auswahl der Datensätze (Es interessieren nur die Datensätze mit Preis über 5€).

Man kann in diesem Beispiel die Abfrage so ändern, dass man noch in der Selektion ORDER BY Preis ASC hinzufügt.

In der Ergebnistabelle werden die Datensätze nun nach aufsteigendem Preis ausgegeben.
Schreibt man ORDER BY Preis DESC, dann werden die Datensätze sortiert nach absteigendem Preis ausgegeben.

Merke Mit dem Zusatz ORDER BY Preis oder ORDER BY Preis ASC wird die Ergebnistabelle nach der genannten Spalte aufsteigend geordnet. Der Zusatz ORDER BY Preis DESC ordnet in absteigender Reihenfolge.

  Aufgabe 1

Ändere die Abfrage so ab, dass in der Selektion (hinter WHERE) folgendes steht
a) Warenbezeichnung = ’Kinokarte’
b) NOT(Sparte = ’Unterhaltung‘)
c)(Kaufdatum = ‘2007-08-26‘) AND (Zahlungsart<> ’bar’)
d)(Sparte = ’Kleider’) OR (Sparte = ’Sport’)

  Aufgabe 2

Stelle Fragen, die durch die Ergebnistabellen der Aufgabe 1 beantwortet werden.

[Lösung anzeigen][Lösung ausblenden]

Macht man diese Abfrage: SELECT Warenbezeichnung FROM einkauf ORDER BY Warenbezeichnung ASC;
dann erhält man eine Ausgabetabelle mit vielen Einträgen wie Banane, Backwaren, ...
Um nun diese vielen gleichen Einträge in der Ergebnistabelle zu vermeiden schreibt man nach SELECT DISTINCT.
Mit der Abfrage SELECT DISTINCT Warenbezeichnung FROM einkauf ORDER BY Warenbezeichnung ASC; werden in der Ergebnistabelle Banane, Backwaren, ... nur einmal aufgeführt.

Merke Redundante Anzeigen vermeiden: SELECT DISTINCT … unterbindet gleiche Zeilen in der Ergebnistabelle!

  Aufgabe 3

Erstelle eine Abfrage und überprüfe mit der Ergebnistabelle, ob deine Abfrage die richtige Antwort liefert.

1. Liste die Bezeichnungen aller gekauften Waren mit ihren Preisen auf.
   
2. Liste alle Kosmetikartikelbezeichnungen auf.
   
3. Gib eine Liste aller Waren in alphabetischer Reihenfolge aus.
   
4. Liste die Daten aller Einkäufe vom 18.3.2006 auf.
   
5. Welche Produkte hat Nicole Gütling eingekauft?
   
6. Welche Lebensmittel wurden vor dem 1.1.2007 gekauft?
   
7. Welche Waren, die billiger als 20 € waren, wurden nicht bar bezahlt?
   
8. Liste alle Waren auf, die in der Zeit vom 13.9.07 bis 20.9.07 gekauft und bar bezahlt wurden.
   
9. Welches Produkt war am teuersten?
   
10. Welche Waren wurden bei „Brutto Nürnberg“ gekauft? Liste sie in alphabetischer Reihenfolge auf.
    
11. Welche Kunden haben am 5.9.2007 bei „Brutto Nürnberg“ gekauft?
    
12. Martina Groß und Oliver Gross wohnen im gleichen Haus. Liste alle Waren auf, die sie gekauft haben.
    
13. Liste alle Waren jeweils mit dem zugehörigen Preis und Geschäft auf, die von männlichen Kunden für mehr als 20 € gekauft wurden.
    Die Liste soll nach den Preisen sortiert sein. Lebensmittel sollen ausgeschlossen werden.
    
14. Wer hat am meisten Geld ausgegeben?
    

[Lösung anzeigen][Lösung ausblenden]

Schaue nicht gleich die Lösungen an, sondern probiere es zuerst selbst. Betrachte deine Ergebnistabelle und überlege, ob diese die gestellte Frage beantwortet.
Beachte bei der Selektion wird ein Text in der Bedingung in einfachen Anführungszeichen (über der rechten Hochstelltaste ist als hochgestellt ')geschrieben .

  Aufgabe 4

Suche im Verzeichnis der Stadtbücherei Rothenburg https://rothenburg.internetopac.de/index.asp nach Bücher über Athen.
Welche Angaben werden im Suchergebnis angezeigt?
Was ist hier anders als bei einer Abfrage in SQL?
Suche nach Büchern von unserem künftigen Oberbürgermeister Markus Naser.

[Lösung anzeigen][Lösung ausblenden]

Hier ist alles über Abfragen noch einmal zusammengefasst:

  Aufgabe 5

a) Muss die Spalte, nach der geordnet wird, in der SELECT-Klausel vorkommen?
b) Kann auch nach mehreren Spalten geordnet werden?
c) Welche Auswirkung hat die Reihenfolge der angegebenen Sortierspalten?

[Lösung anzeigen][Lösung ausblenden]

Aggregatfunktionen

Wir wollen Daten nicht nur auswählen, sondern auch auswerten. Man macht dies mit Aggregatsfunktionen.

Beispiel:

• Wie viel kostet die teuerste Ware?
• Wie viele Waren wurden mit Karte bezahlt?
• Wie viel hat Chantal Amberg insgesamt ausgegeben?

Zahlen auswerten kennst du aus der Mathematik. Dort hat man mathematische Funktionen, die einem dabei helfen. Du hast sie auch schon in der Tabellenkalkulation kennengelernt. Zum Beispiel:
SUMME(Zahl1; Zahl2; …)
MITTELWERT(Zahl1; Zahl2; …)
MAX(Zahl1; Zahl2; …)
MIN(Zahl1; Zahl2; …)
ANZAHL(Zahl1; Zahl2; …)

Diese Funktionen gibt es auch in SQL.

Beachte, dass bei den ersten 4 Funktionen nach der Funktion in runden Klammern die Spalte angegeben wird, während bei der Anzahl der Datensätze nach COUNT in runden Klammern * steht, da alle Datensätze gezählt werden. Das * kennst du bereits aus den Abfragen, wenn der gesamte Datensatz (Zeile) ausgegeben werden sollte, dann machte man * nach SELECT.

  Aufgabe 6

Schaue dir dieses Video zu den Aggregatfunktionen an.

Als nächstes betrachten wir Beispiele aus unserer Datenbank einkauf.

• SELECT MAX( Preis ) FROM einkauf
• SELECT COUNT( * ) AS Anzahl FROM einkauf WHERE Zahlungsart = 'Karte‘‚
• SELECT Kunde, COUNT( * ) AS Anzahl FROM einkauf WHERE Zahlungsart = 'bar' GROUP BY Kunde
• SELECT Sparte, SUM(Preis ) AS Summe FROM einkauf WHERE Kaufdatum = '2007-08-26' GROUP BY Sparte ORDER BY SUM( Preis ) DESC

Was passiert bei diesen Abfragen?

Bei der Abfrage SELECT MAX(Preis) FROM einkauf wird in der Tabelle einkauf in der Spalte Preis der Maximalwert gesucht.

Der gefundene Maximalwert wird in der Ergebnistabelle ausgegeben.

Die Ergebnistabelle ist hier eine Tabelle mit einer Zeile. Der Spaltenbezeichner ist hierbei automatisch erstellt.

Bei der Abfrage SELECT COUNT( * ) AS Anzahl FROM einkauf WHERE Zahlungsart = 'Karte‘ wird in der Tabelle einkauf in der Spalte Zahlungsart die Anzahl der Einträge "Karte" gezählt.

In der Ergebnistabelle wird die Auswertung der Aggregatfunktion, also die Anzahl der Kartenzahlungen ausgegeben.

Beachte, dass hier die Bezeichnung in der Ergebnistabelle durch Angabe von AS Anzahl den Spaltenbezeichner Anzahl erhält.

Bei der Abfrage SELECT Kunde, COUNT( * ) AS Anzahl FROM einkauf WHERE Zahlungsart = 'bar' GROUP BY Kunde wird in der Tabelle einkauf in der Spalte Zahlungsart zuerst die Zahlungsart "bar" ausgewählt und nach Kunden sortiert.

Auswertung der Bedingung Zahlungsart = ’bar’, Zusammenfassung in Gruppen nach Kunden

Danach werden für jeden Kunden die Anzahl der Barzahlungen berechnet und ausgegeben.

Ausgabe des Kunden und Auswertung der Aggregatfunktion für jede Gruppe

Merke: WICHTIG: Stehen nach SELECT Aggragatfunktionen, dann dürfen unter SELECT außer den Aggregatfunktionen nur Spalten erscheinen, die unter GROUP BY aufgeführt sind!

  Aufgabe 7

Schaue dir dieses Video an, in dem die Gruppierung ausführlich erklärt wird.

In der Abfrage SELECT Sparte, SUM(Preis ) AS Summe FROM einkauf WHERE Kaufdatum = '2007-08-26' GROUP BY Sparte ORDER BY SUM( Preis ) DESC werden zuerst in der Tabelle einkauf die Datensätze mit den Kaufdatum 26.8.2007 ausgewählt.

Auswahl der Datensätze mit Kaufdatum 26.08.2007

Danach werden Gruppen der gleichen Sparte gebildet und die Summenfunktion darauf angewendet.

Bildung von Gruppen gleicher Sparte, Anwendung der SUM-Funktion auf diese Gruppen

Zum Schluss wiwrd noch absteigend nach dem Summenbetrag sortiert.

Absteigendes (DESC) Sortieren nach den entstandenen Summenwerten

  Aufgabe 8

Welche Fragestellungen werden mit den vier Beispielen beantwortet?

[Lösung anzeigen][Lösung ausblenden]

Eine Zusammenfassung erhältst du in diesem Video:

  Aufgabe 9

Beantworte die folgenden Aufgaben mit SQL-Abfragen. Notiere dir den SQL-Abfragetext.
Achte besonders beim Datum auf die korrekte SQL-Schreibweise!

1. Wie viel kostet das teuerste Produkt?
2. Wie viele Waren der Sparte „Lebensmittel“ wurden gekauft?
3. Wie viele Waren wurden bei Imagi gekauft?
4. Wie viel Geld wurde für Sportartikel ausgegeben?
5. Wie viele Waren, die mehr als 10 € kosten, wurden am 2.9.2007 gekauft?
6. Wie viele Waren wurden bei Imagi am 5.9.2007 gekauft?
7. Vergleiche den Gesamtumsatz bei Kartenzahlung mit dem Gesamtumsatz bei Barzahlung.
8. Wie viel kostete ein Kosmetikartikel durchschnittlich?
9. Wie viel Geld haben am 5.9.2007 die männlichen Kunden ausgegeben?
10. Liste in alphabetischer Reihenfolge alle Kunden mit der Summe ihrer Ausgaben auf.
11. Wie viel Geld haben insgesamt die männlichen Kunden und und wie viel insgesamt die weiblichen Kunden ausgegeben?
12. Liste alle Geschäfte sortiert nach mittlerem Preisniveau auf.
13. Liste alle Sparten auf. Es soll für jede Sparte der gesamte Umsatz am 6.9.2007 ausgegeben werden. Die Liste soll nach dem Gesamtumsatz sortiert sein, beginnend mit der umsatzstärksten Sparte.
14. Liste für jeden Tag die Summe der Preise aller gekauften Waren auf.
15. Wer hat das teuerste Produkt gekauft?
16. An welchem Tag waren die Einnahmen insgesamt am größten/kleinsten?

[Lösung anzeigen][Lösung ausblenden]

1. 649,90€
2. 285
3. Imagi: 101
4. 509,75€
5. 6
6. 9
7. bar: 6306,80€ - Karte: 2281,02€
8. 9,05€
9. 194,19€
10.
11. männliche Kunden: 3696,70€ - weibliche Kunden: 4892,92€
12.
13.
14.
15. Nina Hofer
16.

Weitere Abfragen an einem neuen Beispiel

Wir haben nun eine neue Datenbank "buch". Dazu wollen wir nun wieder Abfragen erstellen und die Ergebnistabelle anschauen.

  Aufgabe 10

a) Welche Bücher sind vom Autor Karl-August Aare?
b) Gib von den Büchern des Autors Karl-August Aare Titel und Erscheinungsjahr an.
c) Gib den gesamten Datenbestand aus.
d) Gib alle Verlage ohne Mehrfachnennung an.
e) Gib alle Titel, die teurer als 50€ sind an.
f) Eigentlich haben alle Bücher eine ISBN. Dies kann aber nur bei geringem Datenbestand direkt erkannt werden!
Gib alle Bücher mit fehlernder ISBN an.
g) Was kostet das teuerste Buch? Welchen Titel hat es?
h) Wie teuer ist der Bestand aller Bücher vom Verlag Kohlrabe?
i) Was ist der Durchschnittspreis der Bücher vom Verlag Zeisig?

[Lösung anzeigen][Lösung ausblenden]

  Aufgabe 11

Buch S. 88 / 1
Buch S. 88 / 2
Buch S. 88 / 3

[Lösung anzeigen][Lösung ausblenden]