- RobertKrah.de

SQL
Der select-Befehl
Mengenoperatoren
Mit den Ausdrücken Union und Intersect
lassen sich die Vereinigungs- und die
Schnittmege aus zwei Datenbanktabellen
ermitteln.
Betrachten Sie dazu die beiden Datenbanktabellen:
Fußball
Name
Es gibt Spieler, die in beiden Sportarten mitMeyer
spielen, und andere, die nur jeweils entweder
Hansmann
in der einen oder in der anderen Sportart
Peters
spielen.
Huber
Damit die Beispiele Sinn machen, soll z. B.
Szilinski
jemand namns Peters in der Tabelle Fußball
dieselbe Person sein wie Peters in der Tabel- Vorfelder
Lohmann
le Handball.
Die Vereinigungsmenge erhält man mit
union:
SELECT NAME FROM HandBALL
UNION
SELECT NAME FROM FussBALL;
Wenn Sie statt union schreiben:
union all
dann werden die doppelt vorkommenden
Namen nicht eliminiert.
NAME
Hansmann
Huber
Johannson
Lohmann
Meyer
Müller
Peters
Szilinski
Vorfelder
Die Schnittmenge, also die Anzeige der Spieler, die in beiden Sportarten spielen, erhält
man mit Intersection.
NAME
Meyer
Peters
Szilinski
Lohmann
Allerdings akzeptiert Access dies nicht!
SELECT NAME FROM HandBALL
Intersect
SELECT NAME FROM FussBALL
Handball
Name
Meyer
Müller
Peters
Johannson
Szilinski
Lohmann
Aggregat-Funktionen
Diese Funktionen werden auch Set-Funktionen genannt. Mit ihnen werden Auswertungen
wie Summe, Mittelwert usw. vorgenommen.
a) count
Die einfachste Auswertung dieser Art erhält man mit dem Befehl count (=zählen). Beispiel,
bezogen auf die Tabelle Preis:
SELECT count (*)
Das Ergebnis ist 7, weil sich Expr1000
7
in der Tabelle 7 Datensätze
FROM Preis;
befinden.
ANZAHL
Besser sieht es aus, wenn man einen SELECT Count(*) AS ANZAHL
7
FROM Preis;
Aliasnamen verwendet
Auch hier ist natürlich die Angabe von SELECT Count(*) AS ANZAHL
FROM Preis
Bedingungen mit where möglich
WHERE Grosshandel>0.5
1
ANZAHL
3
SQL
Der select-Befehl
b) sum
Mit diesem Befehl bildet man die Summe von Feldinhalten. Mit dem folgenden Beispiel wird
die Summe der Grosshandelspreise gebildet
Summe_Grosshandel
3,09
select sum (Grosshandel) as Summe_Grosshandel
from preis
c) min
Das Minimum von Feldinhalten wird mit der Funktion min ermittelt:
Expr1000
-28
SELECT min (Tieftemp)
FROM Temperatur;
auch hier ist es meistens übersichtlicher, wenn mit einem Aliasnamen gearbeitet wird:
SELECT min (Tieftemp) as "Tiefste Temperatur"
FROM Temperatur;
"Tiefste Temperatur"
-28
d) max
Das Maximum ist entsprechend max:
SELECT max (Hochtemp) as "Höchste Temperatur"
FROM Temperatur;
"Höchste Temperatur"
48
e) avg
Das Wort Mittelwert heißt auf englisch "average", deshalb heißt die Funktion avg
SELECT avg (Hochtemp) as "Durchschnitt der
Höchstwerte"
FROM Temperatur;
"Durchschnitt der Höchstwerte"
41,3333333333333
Ubungsaufgaben
Alle Aggregat-Funktionen lassen sich mit where-Klauseln verbinden. Verwenden Sie für die
folgenden Übungsbeispiele die Tabelle Getränke:
1)
Zählen Sie in der Getränke-Tabelle, von wievielen Artikeln mehr als 45 Stück auf Lager
sind. Die Zählung soll überschrieben werden mit über 45 auf Lager
2)
Bilden Sie den Mittelwert der Einkaufspreise von den Artikeln, die vom Lieferanten Maisel bezogen werden. Verwenden Sie als Überschrift Mittelwert der EKpreise von Maisel
3)
Das Maximum im Bestand der Artikel, die als 0.7-Liter-Flaschen gehandelt werden
4)
Die Summe des Bestands der Spirituosen, deren Einkaufspreis zwischen 8,00 und
12,00 liegt
5)
Das Minimum des Bestands der Artikel, die kein Bier sind.
6)
wie Aufg. 5), aber zusätzliche Bedingung: Die Getränke sollen nicht vom Lieferanten
Müller KG sein.
Noch einige Aufgaben zur Tabelle Mieter:
a) den Mittelwert der Mietkosten der Düsseldorfer Mieter, überschrieben: "Mittelwert D-dorf"
b) die Summe der Mietkosten der Privatpersonen
c) das Minimum der Mietkosten bei den Mietern, deren Postleitzahl mit 0, 1, 2, 3 oder 4
beginnt, überschrieben mit "PLZ unter 50000"
d) die Summe der Mietkosten von den Leuten, deren Nachname mit Sch beginnt.
2
SQL
Der select-Befehl
Lösungen zu den Aufgaben S. 2
1) SELECT count (*) as 'über_45 auf Lager'
FROM Getränke
where Bestand >45
2) SELECT avg (EKPREIS) as 'Mittelwert der EKpreise von Maisel'
FROM Getränke
where Lieferant ="Maisel"
3) SELECT max (Bestand) as 'Höchstzahl von Einzelflaschenl'
FROM Getränke
where packung ="0.7"
4) SELECT sum (Bestand) as 'Summe'
FROM Getränke
where Art ="Spirituosen" and ekpreis between 8 and 12
5) SELECT min (Bestand) as 'Minimal'
FROM Getränke
where Art<>"Bier"
6) SELECT min (Bestand) as 'Minimal'
FROM Getränke
where Art<>"Bier" and not Lieferant ="Müller KG"
a) SELECT avg (Mietkosten) as "Mittelwert D-dorf"
FROM Mieter
where Ort="Düsseldorf";
b) SELECT sum (Mietkosten)
FROM Mieter
where Anrede <>"Firma"
c) select min (Mietkosten) as "PLZ unter 50000"
FROM Mieter
where PLZ <"5"
d) select sum (Mietkosten)
FROM Mieter
where Nachname like "Sch*"
Select-Befehl mit Gruppierung
mit dem Zusatz: "group by" lassen sich Gruppen von Ergebnissen bilden, z. B. so:
select Ort, sum (Mietkosten) as "Summe Mietkosten"
Ort
"Summe Mietkosten"
Aachen
0
Berlin
-104,75
Düsseldorf
10750
Frankfurt a. M.
-265
Frankfurt/Oder
210,5
Hamburg
-5423,25
Köln
0
München
-45,75
Stuttgart
0
Ulm
2110,9
from Mieter
group by ort
Statt der ursprünglich 15 Zeilen der Tabelle
Mieter werden nur noch 10 Zeilen angezeigt,
weil die Mietkosten von Mietern aus gleichen
Orten summiert wurden.
wichtig ist für eine sinnvolle Ausgabe, dass man "Summe Mietkosten"
0
in der ersten Zeile auch das Feld aufführt, nach
-104,75
dem gruppiert wird (hier: Ort), denn sonst weiß
10750
man in der Ausgabe nicht, um was es sich han-265
delt:
3
SQL
Der select-Befehl
Aufgabe: Erstellen Sie eine SQL-Abfrage zur
1
Tabelle Mieter, die das folgene Ergebnis erzielt:
gruppiert nach dem Feld Anrede
(also getrennt für Firmen, Frauen
und Männer) soll der Mittelwert der
Mietkosten ermittelt werden.
anrede "Mittelwert der Mietkosten"
Firma
-2869,375
Frau
2433,75
Herr
359,6
Auch mit dem count-Befehl läßt sich in Gruppierungen arbeiten:
SELECT Anrede, count (*) as Anzahl
FROM Mieter
group by anrede;
Anrede Anzahl
Firma
2
Frau
4
Herr
9
Vorsicht: Diese Abfrage führt
zu einer Fehlermeldung:
SELECT Anrede, count (*)
FROM Mieter
group by Ort;
Es ist nicht möglich, nach einem Feld zu gruppieren, das nicht dem select-Ausdruck entspricht.
Aufgabe: Zählen Sie die Anzahl Mieter in den einzelnen
Orten:
2
Aufgabe: Ermitteln Sie den kleinsten Wert (Minimum) der
Mietkosten, nach Orten gruppiert
3
ort Anzahl Mieter
Aachen
1
Berlin
4
Düsseldorf
2
Frankfurt a. M.
2
Frankfurt/Oder
1
Hamburg
1
Köln
1
München
1
Stuttgart
1
Ulm
1
ort "kleinster Wert"
Aachen
0
Berlin
-315,5
Düsseldorf
750
Frankfurt a. M.
-385
Frankfurt/Oder
210,5
Hamburg
-5423,25
Köln
0
München
-45,75
Stuttgart
0
Ulm
2110,9
Öffnen Sie jetzt die Datenbank Getränke.
Lieferant "Anzahl Getränkesorten"
Bolz GmbH
5
zahl der Getränkesorten (durch Zählen
Maisel
4
der Artikel) ermittelt werden:
Müller KG
6
Schnaps & Co
1
Aufgabe: Gruppiert nach Lieferanten soll die An4
Aufgabe: Gruppiert nach Packung soll das Minimum im Bestand herauskommen:
5
art "kleinster Einkaufspreis"
Aufgabe: Der geringste Einkaufspreis in-
4
6
packung "geringste Bestandsmenge"
0.7
18
Einweg 6*0.33
35
Kasten 0.5*20
30
Kasten 0.7*12
25
SQL
Der select-Befehl
nerhalb einer Getränke-Art:
Bier
Spirituosen
Tonic
Wasser
3,85
5,48
1,21
2,86
Aufgabe: Gruppierung nach Lieferanten
und dem Mittelwert vom Verkaufspreis
7
Lieferant "mittlerer Verkaufspreis"
Bolz GmbH
8,682
Maisel
6,68
Müller KG
9,93666666666667
Schnaps & Co
16
Aufgabe: Summe des Bestands, gruppiert
Lieferant "Stück auf Lager"
Bolz GmbH
185
Maisel
185
Müller KG
234
Schnaps & Co
28
nach Lieferant:
8
Aufgabe: Zählung der Artikel je nach
Packungseinheit
9
packung "wieviel Einheiten?"
0.7
7
Einweg 6*0.33
1
Kasten 0.5*20
6
Kasten 0.7*12
2
(Lösungen 1-9 auf der nächsten Seite)
Die Having-Klausel
Einschränkende Bedingungen mittels where-Klausel haben wir bereits besprochen. Das
folgende Beispiel summiert - zunächst ohne Einschränkung – die Mietkosten der MieterTabelle, gruppiert nach Anrede, also getrennt nach Firmen, Frauen und Männern.
SELECT Anrede, sum(mietkosten) as "Mietkosten"
FROM Mieter
group by Anrede
Anrede "Mietkosten"
Firma
-5738,75
Frau
9735
Herr
3236,4
Wenn wir dasselbe Ergenis, jedoch ohne Firmen, erhalten wollen, dann lautet die SQLAbfrage:
SELECT Anrede, sum(mietkosten) as "Mietkosten"
FROM Mieter
where Anrede <>"Firma"
group by Anrede
Anrede "Mietkosten"
Frau
9735
Herr
3236,4
Die where-Klausel lässt sich also zusammen mit Gruppierungen verwenden, allerdings
nicht, wenn innerhalb der where-Klausel Aggregatfunktionen (sum, avg, min, max, count)
verwendet werden.
Sehen Sie dazu als Beispiel die folgende Abfrage an:
SELECT Anrede, sum(mietkosten) AS "Mietkosten"
FROM Mieter
GROUP BY Anrede
where sum(mietkosten)>5000;
Wenn eine rechnerische Auswertung Teil einer Bedingung sein soll, funktioniert die whereKlausel nicht mehr; stattdessen müssen Sie in dem Fall mit having arbeiten.
SELECT Anrede, sum(mietkosten) AS ["Mietkosten"]
FROM Mieter
GROUP BY Anrede
having sum(mietkosten)>5000;
5
Anrede "Mietkosten"
Frau
9735
SQL
Der select-Befehl
Lösungen S. 4/5:
1
2
3
4
5
6
7
8
9
select anrede, avg (Mietkosten) as "Mittelwert der Mietkosten"
from Mieter
group by anrede;
SELECT ort, count (ort) as "Anzahl Mieter"
FROM Mieter
group by Ort;
SELECT ort, Min(Mietkosten) as "kleinster Wert"
FROM Mieter
group by Ort;
SELECT Lieferant, count (*) as "Anzahl Getränkesorten"
FROM Getränke
group by Lieferant;
SELECT packung, min(bestand) as "geringste Bestandsmenge"
FROM Getränke
group by packung;
SELECT art, min(ekpreis) as "kleinster Einkaufspreis"
FROM Getränke
group by art;
SELECT Lieferant, avg(VKpreis) as "mittlerer Verkaufspreis"
FROM Getränke
group by Lieferant;
SELECT Lieferant, sum(bestand) as "Stück auf Lager"
FROM Getränke
group by Lieferant;
SELECT packung,count(*) as "wieviel Einheiten?"
FROM Getränke
group by packung;
Fortsetzung Having-Klausel:
Sehen Sie sich dazu die Tabelle Abteilung an:
Name
Groß
Klein
Peters
Müller
Gardner
Lindemann
Kussnacht
Gernegroß
Hünzer
Klinger
Abteilung
Forschung
Marketing
Personal
Forschung
Marketing
Personal
Forschung
Marketing
Personal
Forschung
Gehalt
2.670,00 €
2.890,00 €
2.230,00 €
2.700,00 €
2.480,00 €
2.200,00 €
2.780,00 €
2.300,00 €
3.100,00 €
4.270,00 €
Abschluss
Ausbildung
FH
FH
Uni
Uni
Ausbildung
FH
Ausbildung
Uni
Uni
UrKrank_Tag
Datum Be- VerheiraEinstelldatum
laubstae
förderung
tet
ge
32
10
01.01.90
01.06.96
Ja
30
12
01.01.93
Nein
28
5
01.01.97
Nein
30
0
01.04.95
01.06.99
Ja
30
7
01.07.97
01.03.00
Ja
28
5
01.01.98
Ja
28
0
01.01.93
01.03.96
Nein
30
20
01.03.92
01.06.98
Ja
29
11
01.01.96
01.01.00
Nein
30
10
01.01.95
01.01.99
Ja
SELECT Abteilung, sum(gehalt) as "Summe der Gehälter"
FROM Abteilung
group by abteilung;
Aufgabe 1: Welche SQL-Abfrage lässt nur die Abteilung mit
einer Gehaltssumme über 8000 herauskommen?
Aufgabe 2: Bilden Sie die Gehaltssumme aller verheirateten bzw. nicht verheirateten Kollegen
Aufgabe 3: Zeigen Sie den Mittelwert der Krank_tage , grup-
6
Abteilung Gehaltssumme
Forschung
12420
Marketing
7670
Personal
7530
Abteilung Gehaltssumme
Forschung
12420
verheiratet Gehaltssumme
Ja
16620
Nein
11000
abschluss
Kranktage
SQL
Der select-Befehl
piert nach Abschluss, für die Kollegen an, bei
denen der Mittelwert in der Abschlussgruppe über
10 Krankentage liegt:
Ausbildung 11,6666666666667
Aufgabe 4: Ermitteln Sie das höchste Gehalt in
abteilung "höchstes Gehalt"
Forschung
4270
Marketing
2890
Personal
3100
jeder Abteilung:
Aufgabe 5: Wie Aufgabe 4, aber nur für die Abteilungen, wo
abteilung "höchstes Gehalt"
Forschung
4270
Personal
3100
das höchste Gehalt über 3000 € liegt:
Aufgabe 6: In welcher Abteilung liegt das Maximum der
Urlaubstage über 30?
abteilung "am meisten Urlaub"
Forschung
32
Aufgabe 7: Wie hoch ist die Summe der "krankgefeierten" Tage in
der Abteilung, in der – wie bei Aufg. 6) das Maximum
der Urlaubstage über 30 liegt?
Aufgabe 8: Gefragt sind Name und Abteilung von den Mitarbeitern,
die schon einmal befördert wurden
Abteilung befördert wurden
Aufgabe 10: Nur die Anzahl beförderter Kollegen aus der Abteilung
Aufgabe 11: Name und Datum der Beförderung von den
Kollegen, bei denen zwischen Einstellung und
Beförderung weniger als 4 Jahre vergingen
Aufgabe 12: Abteilung und durchschnittl. Urlaubstage der
Abteilung Marketing
Aufgabe 13: Abschluss und Gehalt der Angestellten mit
Uni-Abschluss
Aufgabe 14: Das geringste Gehalt in jeder nach Abschluss
gebildeten Gruppe
Name
Groß
Müller
Gardner
Kussnacht
Gernegroß
Hünzer
Klinger
befördert Abteilung
2 Marketing
name Datum_Beförderung
Gardner
01.03.00
Kussnacht
01.03.96
abteilung "Durchschnitt Urlaub"
Marketing
30
abschluss
Uni
Uni
Uni
Uni
Gehalt
2.700,00 €
2.480,00 €
3.100,00 €
4.270,00 €
abschluss Expr1001
Ausbildung
2200
FH
2230
Uni
2480
Aufgabe 15: Das geringste Gehalt in der Gruppe, die FH-Abschluss hat
Lösungen zu den Aufgaben
7
Abteilung
Forschung
Forschung
Marketing
Forschung
Marketing
Personal
Forschung
befördert Abteilung
4 Forschung
2 Marketing
1 Personal
Aufgabe 9: Jetzt soll gezählt werden, wie viele Kollegen je nach
Marketing
abteilung "Krank"
Forschung
20
abschluss Expr1001
FH
2230
SQL
Der select-Befehl
1. SELECT [Abteilung], sum([Gehalt]) AS Gehaltssumme
FROM Abteilung
GROUP BY [Abteilung]
having sum(gehalt) >8000
2. SELECT verheiratet, sum([Gehalt]) AS Gehaltssumme
FROM Abteilung
GROUP BY verheiratet
3. SELECT abschluss, avg(krank_tage) as Kranktage
FROM Abteilung
GROUP BY Abschluss
having avg(krank_tage)>10
4. SELECT abteilung, max(gehalt) as "höchstes Gehalt"
FROM Abteilung
GROUP BY Abteilung
5. wie 4) letzte Zeile: having max(gehalt) >3000
6. SELECT abteilung, max(urlaubstage) as "am meisten Urlaub"
FROM Abteilung
GROUP BY Abteilung
having max(Urlaubstage)>30
7. SELECT abteilung, sum(krank_tage) as "Krank"
FROM Abteilung
GROUP BY Abteilung
having max(Urlaubstage)>30
8. SELECT Name, Abteilung
FROM Abteilung
where Datum_beförderung is not Null;
9. SELECT count(*) as befördert, Abteilung
FROM Abteilung
where Datum_beförderung is not Null
group by abteilung;
10. SELECT count(*) as befördert, Abteilung
FROM Abteilung
where Datum_beförderung is not Null
group by abteilung
having Abteilung="Marketing"
11. SELECT name,Datum_Beförderung
FROM Abteilung
where Datum_beförderung - Einstelldatum <365*4
12. SELECT abteilung, avg(Urlaubstage) as "Durchschnitt Urlaub"
FROM Abteilung
group by abteilung
having abteilung="Marketing"
13. SELECT abschluss, Gehalt
FROM Abteilung
where Abschluss="Uni"
14. SELECT abschluss, min(Gehalt)
FROM Abteilung
group by abschluss
15. SELECT abschluss, min(Gehalt)
FROM Abteilung
group by abschluss
having abschluss="FH"
8