|
1.3 Equijoin
Joint man eine Tabelle mit sich selbst so heißt das Equijoin
BEISPIEL 3.01 (Alle Menschen und Großväter sind gesucht)
Tabelle: Mensch
Kind Vater
Josef Peppi
Peppi Hans
Susi Peppi
Peter Peppi
select K.Kind, V.Vater
from Mensch K, Mensch V
where K.Vater= V.Kind
BEISPIEL 3.01 (Alle Menschen und ihre Geschwister sind gesucht)
select K.Kind, V.Vater
from Mensch K, Mensch V
where K.Vater= V.Vater and K.Kind not(V.Kind)
1.4 Gruppenfunktionen
Tabelle:
Zahl
15
5
25
max 25 count 3
min 5 sum 45
avg 15
1.5 Subselect
select ZN
from schüler
where Klasse=´3Hba´
and A=( select max(a)
from schüler
where Klasse=´3Hba´)
Der Befehl Distinct dient zur Duplicatenunterdrückung
select distinct Name
from Schüler
macht aus Tab1, Tab2
Tab1 Tab2
Maier Maier
Maier Roth
Roth
Roth
Maier
BEISPIEL 4.01 (Das Alter des ältesten der Schule ist gesucht)
select Alter
from Schüler
where Alter=( select max(Alter)
from Schüler)
BEISPIEL 4.02 (Wie heißen die 15 jährigen der Schule)
select ZN
from Schüler
where Alter=19
BEISPIEL 4.03 (Wie heißen die ältesten der Schule)
select ZN
from Schüler
where Alter=( select max(Alter)
from Schüler)
BEISPIEL 4.04 (Welche Schüler der 3Hb/b sind jünger als die S. d. 4Hb/b)
select ZN
from Schüler
where Klasse=´3Hbb/b´and
Alter=( select min(Alter)
from Schüler
where Klasse)´4Hb/b)
BEISPIEL 4.05 (Namen der Schüler die älter sind als der Druchschnitt)
select ZN
from Schüler
where Alter=( select avg(Alter)
from Schüler)
BEISPIEL 4.06 (In welcher Klasse sitzt der größte Schüler d. Schule)
select distinct Klasse
from Schüler
where Größe=( select max(Größe)
from Schüler)
BEISPIEL 4.07 (In welchem Stock/Stöcken sitzen 1,77m große Schüler)
Tabelle:Schüler Tabelle: Ort
SNr. Vorname Zuname Alter Größe Klasse Stock
3 Max Müller 16 178 4Hb/a 2
18 Peter Berger 17 156 3HB/a 2
12 Herbert Maier 16 172 4HB/c 1
13 Gunther Müller 16 189 1FL 0
5 Sigfried Gunaker 18 202 3Hak/a 3
select distinct Ort, Stock
from Schüler, Ort
where Schüler.Größe=177 and Schüler.Klasse=Ort.Klasse
BEISPIEL 4.08 (Angenommen, alle Schüler sind verschieden groß, wie groß ist der zweitgrößte?)
select ZN
from Schüler
where Größe=( select max(Größe)
from Schüler
where Größemax(8,12,13,20)
Exists(9,13);
Exists()
BEISPIEL 4.09(Alle Schüler, die so heißen wie jemand aus der 3HB/c)
select ZN
from Schüler
where Name in( select Name
from Schüler
where Klasse=3HB/c)
1.6 Group by
Tabelle: Schüler
Name Alter Größe Klasse
Maier 17 1,86 4HB/a
Müller 17 1,82 3HB/a
Gruber 18 1,9 4HB/a
Berger 18 1,7 3HB/a
Die Befehlszeile Group by Klasse ergibt folgendes
Maier 17 1,86 4HB/a
Gruber 18 1,9
Müller 17 1,82 3HB/a
Berger 18 1,7
Die Werte in den Kästchen können nicht am Bildschirm ausgegeben werden, sondern nur durch Gruppenfunktionen.
17 1,86
18 1,9
min 19 ist 17 avg 1,86 ist 1,88
20 1,9
21 1,88
BEISPIEL 5.01
select count name, klasse
from schüler
group by klasse
BEISPIEL 5.02
select max(Alter), avg(Größe), Klasse
from schüler
group by klasse
BEISPIEL 5.03(Alle Klassen, avg alter, größe der Größten)
select KLasse, avg(alter), max(größe)
from Schüler
group by Klasse
BEISPIEL 5.04(Liste aller Altersgruppen mit durchschnittlicher Größe)
select alter, avg(größe)
from Schüler
group by alter
BEISPIEL 5.05(Liste aller Klassen und Anzahl der über 18 jährigen)
select alter, count(alter)
from Schüler
where alter>18
group by alter
BEISPIEL 5.06(Liste aller Altersgruppen in d. 5.Klasse mit dschn. Größe)
select alter, avg(größe)
from Schüler
where klasse like(´5*´)
group by alter
BEISPIEL 5.07 (Liste aller Stcokwerke mit dschn. Alter, Schülerzahl u. Größe des größten Schülers)
select avg(alter), count(alter), max(größe)
from Schüler, Ort
where S.klasse= O.Klasse
group by O.Stock
1.7 Having
Im Gegensatz zu where, kann man mit having auch Gruppenfunktionen verwenden
BEISPIEL 6.01 (Liste aller Klassen, deren dschn. Alter, die mehr als 20 Schüler haben)
select Klasse, avg(alter)
from Schüler
group by Klasse
having count(*)>20
BEISPIEL 6.02(Liste aller Klassen, mit Schülerzahlen und der dsch.Größe)
select alter, count(schüler)
from Schüler
group by Alter
having avg(größe)>180
BEISPIEL 6.03(Liste aller Klassen, mit Schülerzahlen und der dsch.Größe)
select alter, count(schüler)
from Schüler
group by Alter
having avg(größe)>180
BEISPIEL 6.04(Den Piloten mit den meisten Flugstunden)
Pilot KF
Piloten# Name Copiloten# Flugstunden Piloten# Typ
12 Müller 5 16 12 DC10
14 Berger 1 17 32 Boing747
2 Maier 21 16 2 Boing747
17 Müller N 16 11 Fokker110
8 Gunaker N 18 1 DC10
select P.Name
from Pilot
where h=( select max(Flugstunden)
from Pilot)
BEISPIEL 6.05(Alle Piloten und ihre Copiloten)
select T1.Name, T2.Name
from Pilot T1, Pilot T2
where T1.CNR=T2.PNR
BEISPIEL 6.06(Welche Typen kann Berger fliegen?)
select KF.Typ
from Pilot, KF
where P.PNR=KF.PNR and Name=´Berger´
BEISPIEL 6.07(Alle Klassen, in denen avg alter >16 ist)
select Klasse
from Schüler
group by klasse
having avg(alter)>16
BEISPIEL 6.08(Alle Altersgruppen in denen mind einer über 2m großen Schüler gibt und die Größe dieses Schülers)
select Alter,max(Größe)
from Schüler
group by alter
having max(größe)>2
1.8 Order by
BEISPIEL 7.01(Gib die Schüler der 3Hbc dem Alter geordnet nach aus)
select name
from Schüler
where klasse=´3Hbc´
order by alter
Im order by Teil können die Attribute oder Gruppenfunktionen von Attributen stehen, die auch im select- Teil stehen oder stehen könnten.
select name avg(alter) könnte auch nicht
from schüler im select Teil stehen, weil
order by avg(alter) nicht gruppiert wird
BEISPIEL 7.02(Gib alle Klassen nach dem Durchschnittsalter geordnet aus)
select klasse
from Schüler
group by klasse
order by avg(alter)
Man kann im order by- Teil auch mehrere Attribute angeben
select name
from Schüler
order by klasse, alter
Liste aller Schüler nach Klassen geordnet, innerhalb einer Klasse sind sie nach ihrem Alter geordnet
select name
from Schüler
order by alter, klasse
Liste aller Schüler nach dem Alter geordnet, gleich alte werden nach der Klasse geordnet
1.9 NULL- Values
NULL bedeuted "gibt es nicht"
Mitarbeiter# Name Chef#
3
Maier 5 Müller hat keinen Chef
5 Müller NULL
2
Huber 0
0 Berger 3 Huber hat den Chef mit Nummer 0
NULL 0
BEISPIEL 8.01(Alle Mitarbeter ohne Chef)
select name
from Mitarbeiter
where Chef is NULL
Es gibt auch not NULL
BEISPIEL 8.02(Alle Untergebenen von Berger )
select count(Name) select count(Chef#)
from Mitarbeiter from Mitarbeiter
4 3
Datensätze mit Null Null wird nicht
werden mitgezählt mitgezählt
1.10 Reihenfolge der Abarbeitung
from
where
group by
having
order by
select
1.11 Subselects, die Paare ergeben
BEISPIEL 9.01(Größe+ Alter der Schüler der 3hbb)
select größe, alter
from schüler
where klasse=´3hbb´
BEISPIEL 9.02(alle S. die so groß und so alt sind wie jemand aus 3Hbb)
select name
from schüler
where (größe, alter) in (select ...
BEISPIEL 9.03(Alle Weitspringer)
Sportler
Name VName Alter Disziplin Bestleitsung Nr
Berger Gerhard 32 100m Sprint 9,91 12423
Glas Uschi 99 Schuplattln´ 126 pro sek 00000
Mercury Frederick 43 ??? ??? 00001
Polster Anton Jesus 36 talking 2 words/sec 23167
Duck Daffy 23 shaking 34 17834
select name
from sportler
where disziplin=´weitsprung´
order by name
BEISPIEL 9.04(Alle 100m Läufer unter 10sec)
select name
from sportler
where disziplin=´100m´ and bestleistung
>=
|
