|
>
= Datenbank in Tabellenform / besteht aus Tabellen
Schüler
SQL
stuctured query language
(strukturierte Abfrage Sprache)
select bestimmt, welche Felder anzuzeigen sind.
from wählt aus der Datenbank eine Tabelle aus
where gibt die Bedingung an, die die Datensätze erfüllen müssen
Bsp: Alle Zunamen aus der Tabelle Schüler, die älter als 16 sind.
select Zn
from schüler
where A>16
Bsp: Alle Zunamen aus der Tabelle Schüler, die älter als 16 sind und \'Josef\' heißen.
select Zn
from schüler
where A>16 and Vn=\'Josef\'
Bsp:
I) Alle Zunamen aus der Tabelle Piloten, deren Flugstunden mehr als 1000 beträgt.
select Zn
from pilot
where Fst>1000
II) Alle Zunamen aus der Tabelle Piloten, die weniger als 500 Flugstunden haben
und \'Franz\' heißen.
select Zn
from pilot
where Fst2000
bilden eines KARTESISCHEN PRODUKTS
from S, V
Datensatz 2, 3 unnötig, deshalb:
where S.Verein=V.Verein JOIN
Bsp:
(from SF, PF)
I) Alle Stew. und Piloten mit gleicher Flugzeugnummer und mit älterer Stew. (als Pilot).
select SF.Stew, PF.Pilot
from SF, PF
where (SF.Flugzeug=PF.Flugzeug) and (SF.A>PF.A)
Bsp:
I) Autotypen (und Land) die aus England kommen.
select A.Autotyp, S.Land
from A, S
where A.Stadt=S.Stadt and S.Land=\'England\'
II) Alle Autotypen die aus einer französischen Kleinstadt (EW(0.1*EWL)
SYNONYM
select L, S
from Landesschulinspektor L, Schu... S
where L.xxxx S.xxxx
Bsp: Alle Mitarbeiter samt Stellvertreter.
select M.Zun, S.Zun
from Mitarbeiter M, Mitarbeiter S EQUIJOIN (Verknüpfung innerhalb einer Tabelle)
where M.Stv=S.MNr
Bsp:
I) Kind mit Großvater.
select M1.Kind, M2.Vater
from Mensch M1, Mensch M2
where M1.Vater=M2.Kind
II) Geschwister.
select M1.Kind, M2.Kind
from Mensch M1, Mensch M2
where M1.Vater=M2.Vater and M1.Kind not M2.Kind
Gruppenfunktionen (nur in select):
15, 5, 25
sum 45 Summe
max 25 Maximalwert
min 5 Minimalwert
avg 15 Durchschnitt (=sum/count)
count 3 Anzahl
Bsp:
Schüler
S# Vn Zn A Kl Schnitt
I) Alter des ältersten Schülers der 3HBc.
select max(A)
from Schüler
where Kl=\'3HBc\'
II) Zunamen aller 17-jährigen der 3HBc.
select Zn
from Schüler
where Kl=\'3HBc\' and A=17
III) Durchschnittsalter der Schüler aus der 3HBc.
select avg(A)
from Schüler
where Kl=\'3HBc\'
IV) größte Schülernummer der 3HBc.
select max(S#)
from Schüler
where Kl=\'3HBc\'
V) Zunamen der ältesten Schüler der 3HBc.
select Zn
from Schüler
where A= (select max(A)
from Schüler SUBSELECT
where Kl=\'3HBc\')
Bsp:
I) Maximaleinkommen.
select max(Einkommen)
from Konzern
II) Mitarbeiter mit dem maximalsten Einkommen.
select MName
from Konzern
where Einkommen= (select max(Einkommen)
from Konzern)
III) Mitarbeiter mit Land.
select MName, Land
from Konzern, Ort
where Konzern.Unternehmen=Ort.Unternehmen
GRUPPENFUNKTION
macht aus Gruppe von Werten EINEN Wert.
MAIER
MÜLLER DISTINCT MAIER
MAIER MÜLLER
ROTH ROTH
ROTH
select distinct ZUNAME
KEINE KLAMMERN
Bsp: Welche Klassen gibt es an der Schule?
select distinct Kl
from Schule
I) Alter der Ältesten der Schule.
select max(A)
from Schule
where Geschlecht=\'weiblich\'
II) Wie heißen die 19-jährigen an der Schule?
select Zun
from Schule
where A=19
III) Wie heißen die ältesten Schüler der Schule?
select Zun
from Schüler
where A= (select max(A)
from Schüler)
IV) Welche Schüler sind älter als der Schuldurchschnitt?
select Zun
from Schüler
where A> (select avg(A)
from Schüler)
V) Welche Schüler der 3HBb sind jünger als der Jüngste der 4HBb?
select Zun
from Schüler
where Kl=\'3HBb\' and A< (select min(A)
from Schüler
where Kl=\'4HBb\')
Bsp:
select Zun
from Schüler
where Kl=\'4HBa\' and
A< (select avg(A)
from Schüler
where Kl=\'4HBa\')
a) Was ergibt dieses select-Statement?
Alle Schüler der 4HBa, die jünger sind als ihr Klassendurchschnitt.
b) Was ergibt es, wenn der rote Teil weggelassen wird?
Alle Schüler der 4HBa, die jünger sind als der Schuldurchschnitt.
c) Was ergibt es, wenn nur der blaue Teil fehlt?
Alle Schüler der Schule, die jünger sind als der Durchschnitt der 4HBa.
Bsp: Schüler (Name, Alter, Größe, Klasse)
Ort (Klasse, Stock)
I) Wie groß ist der größte Schüler der Schule?
select max(Größe)
from Schüler
II) Wie heißt er?
select Name
from Schüler
where Größe= (select max(Größe)
from Schüler)
III) Was passiert, wenn mehrere Schüler mit 2,03m am größten sind?
Abfrage1 ergibt 2,03
Abfrage2 ergibt die Namen dieser Schüler
Es werden also alle 2,03m große Schüler (und nicht bloss einer davon) ausgegeben.
IV) In welcher Klasse sitzt der größte Schüler der Schule?
select Klasse
from Schüler
where Größe=(select max(Größe)
from Schüler)
KÖNNEN NATÜRLICH AUCH MEHRERE SEIN!
V) In welchem Stock/welchen Stöcken sitzen 1,77m größe Schüler?
select distinct Stock
from Schüler, Ort
where Ort.Klasse=Schüler.Klasse and Größe=\'1,77\'
VI) In welchem Stock/welchen Stöcken sitzen die größten der Schule?
select distinct Stock
from Schüler, Ort
where Ort.Klasse=Schüler.Klasse
and Größe= (select max(Größe)
from Schüler)
VII) Angenommen, alle Schüler sind verschieden groß. Wie groß ist der Zweitgrößte?
select max(Größe)
from Schüler
where Größe18
group by Klasse
Bsp: Liste aller Altersgruppen in den fünften Klassen mit Durchschnittsgröße.
select Alter, avg(Größe)
from Schüler
where Klasse like (\'5*\') oder Klasse in (\'5HBa\', \'5HBb\', \'5HBc\')
group by Alter
Bsp: Liste aller Stockwerke mit Durchschnittsalter, Schülerzahl und Größe des größten Schülers.
select Stock, avg(Alter), count(Name), max(Größe)
from Schüler, Ort
where Schüler.Klasse=Ort.Klasse
group by Stock
Bsp: Liste aller Klassen und deren Durchschnittsalter, die über 20 Schüler haben.
select Klasse, avg(Alter)
from Schüler
group by Klasse
having count(*)>20 having == where
WHERE: KEINE GRUPPENFUNKTIONEN!
HAVING: SCHON!
Bsp: Altersklassen (nur die, in denen die Schüler im Durchschnitt größer 1,80 sind) mit Schülerzahl.
select Alter, count(*)
from Schüler
group by Alter
having avg(Größe)>1,80
Bsp: Alter sortiert ausgeben.
select Name
from Schüler
order by Alter (desc) Alter aufsteigend sortiert
Bsp:
I) Name aller Piloten, mit maximaler Flugstundenanzahl.
select Name
from Pilot
where Fstd= (select max(Fstd)
from Pilot)
II) Name des Piloten mit Copilot.
select P1.Name, P2.Name
from Pilot P1, Pilot P2
where P1.CNr=P2.PNr
III) Alle Flugzeugtypen, dessen Piloten Berger heißen.
select Typ
from Pilot, KF
where Pilot.PNr=KF.PNr and Name=\'Berger\'
IV) Namen der Piloten, die eine B747 fliegen können.
select Name
from Pilot, KF
where Pilot.PNr=KF.PNr and Typ=\'B747\'
V) Flugzeugtyp mit erfahrensten Pilot.
select Typ, Name
from Pilot, KF
where Pilot.PNr=KF.PNr
group by Typ
having max(FStd)
VI) Zu jedem Flugzeugtyp die maximalste Flugstundenanzahl.
select max(Fstd), Typ
from Pilot, KF
where Pilot.PNr=KF.PNr
group by Typ
HAVING
ist die WHERE-BEDINGUNG für Gruppen.
Bsp: Alle Klassen, in denen das Durchschnittsalter>16 Jahre ist.
select Klasse
from Schüler
group by Klasse
having avg(Alter)>16
HAVING kann nur Gruppenfunktionen enthalten,
WHERE darf keine enthalten.
Bsp: Alle Altersgruppen, in denen es mind. einen über 2 Meter großen Schüler gibt
und Größe (des größten) dieses Schülers.
select max(Größe), Alter
from Schüler
group by Alter
having max(Größe)>2.00
ORDER BY
Bsp: Gib die Schüler der 3HBc nach dem Alter geordnet 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önnen.
select Name avg(Alter) könnte auch nicht
from Schüler FALSCH !! im SELECT stehen,
order by avg(Alter) weil nicht gruppiert wird.
Bsp: 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 1HBa 14
from Schüler 1HBa 15
order by Klasse, Alter 1HBb 14
1HBb 15
Liste aller Schüler nach Klassen geordnet, innerhalb einer Klasse sind sie nach ihrem
Alter geordnet.
select Name 1HBa 14
from Schüler 1HBb 14
order by Alter, Klasse 1HBa 15
1HBb 15
Liste aller Schüler nach dem Alter geordnet. Gleichalte werden nach der Klasse geordnet.
NULL-VALUES
NULL bedeutet \"GIBT ES NICHT\"
Mitarb# Name Chef#
3 Maier 5 Müller hat keinen
5 Müller NULL Chef.
2 Huber 0
0 Berger 3 Huber hat einen Chef.
Er hat Nummer 0.
NULL ist nicht dasselbe wie 0!
Bsp: Alle Mitarbeiter ohne Chef.
select Name
from Mitarbeiter es gibt auch:
where Chef# is null ... is not null
Bsp: Alle Untergebenen von Berger.
select Name
from Mitarbeiter
where Chef#=0
select count(*) select count(Chef#)
from Mitarbeiter from Mitarbeiter
4 3
Datensätze mit NULL nicht gezählt
NULL gezählt
SQL-REIHENFOLGE
1.) BILDE KARTESISCHES PRODUKT ALLER TABELLEN
AUS DEM FROM.
2.) ENTFERNE ALLE ZEILEN, DIE DAS WHERE NICHT ERFÜLLEN.
3.) FASSE ALLE DATENSÄTZE, FÜR DIE DAS GROUP BY ATTRIBUT DENSELBEN WERT HAT IN JE EINEN ZUSAMMEN.
4.) ENTFERNE ALLE DIESER GRUPPENDATENSÄTZE,
DIE DIE HAVING BEDINGUNG NICHT ERFÜLLEN.
5.) SORTIERE DAS ERGEBNIS NACH ORDER BY.
6.) GIB NUR DIE ATTRIBUTE / GRUPPENFUNKTIONEN VON
ATTRIBUTEN AUS, DIE IM SELECT VORKOMMEN.
SUBSELECTS, DIE PAARE ERGEBEN
Bsp: Größe + Alter aller Schüler der 3HBb.
select Größe, Alter 1,87 16 1 Schüler
from Schüler 1,72 16 1 Schüler
where Klasse=\'3HBb\' 1,78 15 1 Schüler
Bsp: Welche Schüler sind genauso groß und alt wie jemand aus der 3HBb?
select Name
from Schüler
where (Größe, Alter) in ( select ...
Bsp:
I) Die Namen der Sportler mit der Disziplin Weitsprung sollen geordnet ausgegeben werden.
select Name
from Sportler
where Disziplin=\'Weitsprung\'
order by Name
II) Namen aller 100m-Läufer mit Bestleistung unter 10 sek. nach Bestleistung geordnet.
select Name
from Sportler
where Disziplin=\'100m Lauf\' and Bestleistung 40
group by Disziplin
IV) Namen aller Sportler die in Oslo dabei waren.
select Name
from Sportler, Teilnahme
where Sportler.Nr=Teilnahme.Nr and Ort=\'Oslo\'
V) Alle Orte gruppiert ausgeben, dessen Teilnehmerdurchschnittsalter größer als 30 ist und
dessen Meeting in der 52-sten Woche stattgefunden hat.
select Ort
from Sportler, Meetings, Teilnahme
where Sportler.Nr=Teilnahme.Nr and Teilnahme.Ort=Meetings.Ort and Datum=52
group by Ort
having avg(Alter)>30
VI) Namen aller Weitspringer, dessen Leistung von der Bestleistung maximal 1m abweicht.
select Name
from Sportler
where Disziplin=\'Weitsprung\' and Bestleistung+1 >(select max(Bestleistung)
from Sportler
where Disziplin=\'Weitsprung\')
Bsp:
nicht Katnr., sondern eindeutig
Schüler (S#, Zuname, Vorname, Alter, Geschlecht, Klassencode)
M/W
Klasse (Klassencode, Stock, Sprecher#)
eine S#
I) Liste aller Klassensprecher (Namen !) aus dem 2. Stock, geordnet nach dem Alter.
select Name
from Schüler, Klasse
where Schüler.S#=Sprecher.S# and Stock=2
group by Alter
-) Im Subselect.
select Name
from Schüler
where Schüler.S# in (select Sprecher.S#
from Klasse
where Stock=2
order by Alter)
II) Wieviele Klassensprecherinnen gibt es?
select count(*)
from Schüler, Klasse
where Geschecht=\'W\' and Schüler.S#=Klasse.Sprecher#
III) Wer ist um 1 Jahr älter als der Klassensprecher?
select S.Zuname
from Schüler S, Schüler Spr, Klasse K
where Spr.S#=K.Sprecher# and
S.Klassencode=K.Klassencode and
S.Alter=Spr.Alter+1
IV) Klassen mit mehr als 3 Volljährige.
select Klassencode
from Schüler
where Alter>18
group by Klaseencode
having count(*)>3
V) Klasse mit 5 gleiche Vornamen.
select distinct S1.Klassencode
from Schüler S1, Schüler S2, Schüler S3, Schüler S4, Schüler S5
where S1.Vorname=S2.Vorname and S2.Vorname=S3.Vorname and
S3.Vorname=S4.Vorname and S4.Vorname=S5.Vorname
VI) In welchen Klassen sitzen mehr als 3 Minderjährige?
select Klassencode
from Schüler
where Alter 3
VII) Liste aller Klassenschülerzahlen.
select count(*)
from Schüler
group by Klasse
VIII) Welche Klasse ist am größten?
select Klassencode
from Schüler
group by Klassencode
having count (*) = max( )
IX) Welche Nummer hat der Sprecher der größten Klasse?
select Sprecher#
from Klasse
where Klassencode = ( )
X) Wie heißt der Sprecher der größten Klasse?
select Zuname, Vorname
from Schüler
where S# = ( )
VERGLEICHE IN SQL
= (nicht == wie in C) > < >=
|
