06b_AB_Abfragen_SQL

Informatik Klasse 11/12
Abfragen mit Gruppierungen und Funktionen
GRUPPIERUNG
Damit werden Datensätze mit gleichen Eigenschaften zusammengefasst.
Gruppierungen eignen sich gut für statistische Auswertungen.
SELECT ORT
FROM MITARBEITER
GROUP BY ORT;
SELECT ORT
FROM MITARBEITER
WHERE ort LIKE 'b%'
GROUP BY ORT;
Funktionen SUM, MIN, MAX, AVG, COUNT in Abfragen
(Aggregatfunktionen )
SUM, MIN, MAX, AVG, COUNT - eignen sich für statistische Auswertungen, um
z.B. Anzahl der Datensätze, Durchschnitts- oder Maximalwerte zu ermitteln.
SELECT COUNT(ID) AS Anzahl, AVG(preis) AS Durchschnittspreis,
MIN(preis) AS Preis_Minimum, MAX(preis) AS Preis_Maximum,
SUM(stueck) AS Gesamtstückzahl
FROM LAGER;
Anzahl Preis_Durchschnitt Preis_Minimum Preis_Maximum Gesamtteilezahl
33
11,2081817207914
0,99
35,8
3800
SELECT COUNT(id) FROM MITARBEITER;
SELECT ORT, COUNT(name) FROM MITARBEITER;
 Fehler!
In Abfragen können entweder nur Aggregatfunktionen ODER nur
Datenfelder angegeben werden.
Eine Mischung verursacht eine Fehlermeldung. Lösung: gruppierte Abfragen
 d.h. jedes Datenfeld, welches ohne Aggregatfunktion angegeben wird, muss
gruppiert werden!!!!
SELECT ORT, COUNT(*) FROM MITARBEITER GROUP BY ORT;
 count(*) zählt allgemein die Anzahl der Datensätze!
Ort
count(*)
Nichtstandardisierte Funktionen
Bad Lausick
7
Die meisten DBMS stellen eine Vielzahl von mathematischen
Funktionen und Funktionen für Zeichenketten zur Verfügung:
ABS(zahl), ROUND(zahl,stellen), SIN(zahl), SQRT(zahl)
LENGTH(string), LOWER(string), UPPER(string)
Belin
1
481352991
Informatik * Dresden * Meißen
Berlin
12
Castrop-Rauxel
3
...
...
1[3]
Informatik Klasse 11/12
Abfragen mit Gruppierungen und Funktionen
WHERE Klausel für Gruppen: HAVING
Soll das Ergebnis einer Gruppierung "verfeinert" werden, d. h ist eine weitere
Bedingung notwendig, so benötigt man die SQL-Klausel Having. Damit werden
die Datensätze in gruppierten Abfragen weiter eingeschränkt.
 Die HAVING-Klausel ist als ’WHERE-Klausel’ für Gruppen von Zeilen zu
verstehen.
SELECT ort, COUNT(name)
FROM MITARBEITER
GROUP BY ort
HAVING COUNT(name)>=10;
 Gibt nur Orte aus, aus denen 10 oder mehr Mitarbeiter kommen.
SELECT name, COUNT(name)
FROM MITARBEITER
GROUP BY name
HAVING COUNT(name)>1;
 Gibt nur Namen aus, die mindestens zweimal vorkommen.
Übungsaufgaben mit schule09 (eine Tabelle)
a) Wie viele Schüler kommen aus dem jeweiligen Wohnort? Es soll der Wohnort und die
entsprechende Anzahl ausgegeben werden. (73)
b) Wie viele Schüler kommen aus Meißen? (74 Schüler)
c) Aus welchem Ort kommt nur EIN Schüler? (31)
d) Wie viele Schüler besuchen die Klasse 10b? (8 Schüler)
Einfache Datenabfragen mit firma (DQL)
a) Ermitteln Sie alle auftretenden Familiennamen aus der Tabelle MITARBEITER. Filtern
Sie mehrfach auftretende Namen heraus. (90)
b) Ermitteln Sie alle Mitarbeiter (id, name), deren Familienname auf mann endet. (8)
c) Ermitteln Sie alle Orte, die mit K beginnen. Filtern Sie mehrfach auftretende Namen
heraus. (3)
d) Erstellen Sie eine Datenabfrage, um die Vornamen und Familiennamen aller
Mitarbeiter zu ermitteln. Begrenzen Sie das Ergebnis auf 15 Ergebnisse.
e) Erweitern Sie die Abfrage, damit Sie zusätzlich die Postleitzahl und den Ort der
Mitarbeiter zu erhalten.
f) Definieren Sie für alle Felder sinnvolle Ersatznamen.
g) Ändern Sie die Abfrage, sodass Sie nur Mitarbeiter aus Hamburg und Berlin erhalten.
Verwenden Sie dazu die IN-Anweisung. Zusätzlich sollen die Mitarbeiter älter als 30
sein. Heben Sie die Begrenzung auf 15 Datensätze auf. (18)
h) Lassen Sie die Abfrage nach Familiennamen der Mitarbeiter sortieren.
i) Ermitteln Sie alle Artikel (id, preis, stueck) aus der Tabelle LAGER, welche zwischen 5
und 15 kosten oder noch mehr als 100 Mal vorhanden sind. (25)
j) Ermitteln Sie für jeden Artikel den Bruttopreis, indem Sie zu dem in der Tabelle
gespeicherten Preis 16% Mehrwertsteuer hinzufügen.
k) Gruppieren Sie die Tabelle anhand der Stückzahl, und ermitteln Sie in einer Abfrage
die Anzahl der Artikel, die mit einer bestimmten Stückzahl vorhanden sind. (20)
z.B.: In der Stückzahl 100 liegen 3 verschiedene Artikel vor.
l) Schränken Sie die Abfrage auf Stückzahlen mit weniger als 10 Artikeln ein. (5)
m) Sortieren Sie das Abfrageergebnis absteigend nach der Stückzahl.
n) Schränken Sie die Abfrage aus k) auf Stückzahlen ein, zu denen mindestens 3 Artikel
gehören. (4)
481352991
Informatik * Dresden * Meißen
2[3]
Informatik Klasse 11/12
Abfragen mit Gruppierungen und Funktionen
Funktionen in Abfragen mit bibliothek
a) Erstellen Sie jeweils eine Abfrage, um die Anzahl der Datensätze in der
Tabelle Buecher und der Tabelle Leser zu ermitteln.
b) Gruppieren Sie eine Anfrage über die Tabelle Verleih so, dass Ihnen angezeigt wird,
wie viele Bücher die einzelnen Leser (Lesernummer) ausgeliehen haben.
c) Sortieren Sie die Abfrage absteigend nach den Lesernummern.
d) Beschränken Sie die Abfrage auf die Leser, die mehr als ein Buch ausgeliehen haben.
e) Ermitteln Sie die ISBN-Nummer und den gerundeten Preis der vorhandenen Bücher!
f) Ermitteln Sie den Gesamtwert aller Bücher!
481352991
Informatik * Dresden * Meißen
3[3]