SELECT Bezeichnung, Lieferant

SQL-Befehle in Abfragen: Join
Verbindung von Tabellen (Join)
Die bisher betrachteten Beispiele haben sich jeweils nur auf eine Tabelle bezogen. Im Folgenden geht es um Select-Abfragen, die ihre Ergebnisse aus verschiedenen Tabellen beziehen.
Eine wichtige Anforderung an relationale Datenbanken ist die Zerlegung der Informationen in
verschiedene Tabellen. Dazu dient der Prozess der Normalisierung (1.-3. Normalstufe).
Durch Abfragen werden die Informationen, die auf verschiedene Tabellen verteilt sind, zusammengeführt.
Bei der Arbeit mit Access haben Sie gesehen, wie die Verbindung der Tabellen durch Beziehungen erfolgt (1:1 und 1:n-Beziehungen); die Tabellen werden über Primär- und Fremdschlüssel miteinander verbunden.
Dass es Primär- und Fremdschlüssel in den Tabellen gibt, ist auch für Abfragen mittels SQL
Vorbedingung. Die Verbindung der Tabellen muss aber durch entsprechende Befehle erfolgen, bzw. innerhalb des Select-Befehls muss dem Datenbanksystem mitgeteilt werden, welche Verbindung zwischen den Tabellen besteht - SQL "weiß" ja nichts vom Beziehungsfenster in Access, in welchem wir bisher die Beziehungen hergestellt und grafisch dokumentiert
haben.
Wenn Tabellen miteinander in Beziehung gesetzt werden, dann gibt es oft dieselben Feldnamen in Tabelle A und Tabelle B. Auch wenn es keine Anforderung an die Verbindung von
Tabellen durch Primär- und Fremdschlüssel ist, dass die Felder, über die man die Verbindung herstellt, den selben Namen haben, dann macht man es oft der Übersichtlichkeit halber.
Um klar zu machen, um welches Feld aus welcher Tabelle es sich handelt, muss dann entweder immer der komplette Tabellenname zusätzlich angegeben werden, abgetrennt durch
einen Punkt, oder (besser) man verwendet Aliasnamen für die Tabellen. In der Regel verfährt man dabei so, dass man den Tabellennamen durch einen Buchstaben abkürzt, also
z. B. die Tabelle Artikel durch A. (Beachten Sie den Unterschied: Die Aliasnamen, die wir
bisher verwendet hatten, bezogen sich auf Spaltenüberschriften).
Das folgende Beispiel zeigt dies, zunächst noch nicht auf die Verbindung von Tabellen bezogen. Sie sehen dabei an der linken Spalte mit Alias, dass der Aliasname A für die Tabelle
Artikel erst in der zweiten Zeile vergeben wird, nachdem man ihn schon der ersten Zeile
verwenden konnte!
ohne Aliasname
Verwendung von Aliasname für Tabelle
SELECT Artikel.Bezeichnung
from Artikel;
SELECT A.Bezeichnung
from Artikel A;
Die Verwendung von Aliasnamen spart dann auch Zeit: Die Tabellen bekommen einen Buchstaben als Alias zugewiesen. Dann kann immer mit dem Buchstaben auf die Tabelle verwiesen werden, wenn gesagt werden muss, aus welcher Tabelle ein Feld stammt. Vor allem bei
Abfragen, die aus vier, fünf oder mehr Zeilen bestehen (mit where- und having-Klauseln
usw.), in denen immer wieder die Feldnamen angesprochen werden müssen, ist dies wichtig.
Die folgenden Beispiele beziehen sich auf die Datenbank Firma, die mit Access erstellt wurde. Testen Sie dazu zunächst den sogenannten Cross Join, in dem jedes Feld der einen
Tabelle mit der anderen Tabelle verbunden wird (kartesisches Produkt). Das Ergebnis macht
tatsächlich keinen Sinn, weil alles mit jedem verbunden wird:
SELECT A.Bezeichnung, L.Lieferant
from Artikel A,Lieferant L;
usw., 36 Zeilen bei 12 Artikeln und drei Lieferanten
jeder Lieferant steht bei jedem Artikel!
1
Bezeichnung
Lieferant
Haube für Tastatur
Meyer OHG
Haube für Tastatur Computer-Versand
Haube für Tastatur
Schmitz AG
Disketten 3 1/2 DD
Meyer OHG
Disketten 3 1/2 DD Computer-Versand
Disketten 3 1/2 DD
Schmitz AG
SQL-Befehle in Abfragen: Join
Wenn die Gleichsetzung über das Feld, welches in beiden Tabellen vorkommt (Lieferantennummer), gemacht wird, dann ist ein sinnvolles Ergebnis zu sehen. Man nennt es Equi Join:
Bezeichnung
Lieferant
Haube für Tastatur
Meyer OHG
from Artikel A, Lieferant L
Disketten 3 1/2 DD
Meyer OHG
where A.Lieferantennummer=L.LiefNr;
Maus seriell
Meyer OHG
Monitor VGA
Meyer OHG
Centronics-Kabel
Meyer OHG
mit der where-Klausel wird die Gleichsetzung
Disketten 5 1/4 HD
Computer-Versand
erzielt:
Tastatur deutsch
Computer-Versand
Die Zeilen werden angezeigt, wo die LiefnumDisketten 3 1/2 HD
Computer-Versand
mer der Tabelle Lieferant gleich der Lieferan- Motherboard ASUS A7 V3
Schmitz AG
tennummer der Tabelle Artikel ist
Disketten 5 1/4 DD
Schmitz AG
Farbband AX 35
Schmitz AG
Farbband AX 55
Schmitz AG
SELECT A.Bezeichnung, L.Lieferant
Vorsicht: Die einschränkende Bedingung durch die where-Klausel reicht nicht aus, wo nach
bestimmten Daten gesucht wird. Angenommen, Sie wollen die Artikelbezeichnung und den
Lieferantennamen anzeigen, und zwar nur für die Artikel, die von Lieferant Meyer OHG geliefert werden.
Bezeichnung
Haube für Tastatur
Disketten 3 1/2 DD
Disketten 5 1/4 HD
Motherboard ASUS A7 V3
Maus seriell
Die Einschränkung durch die where-Klausel reicht
Disketten 5 1/4 DD
nicht aus. Es wird das Cross Join gebildet, und bei
Tastatur deutsch
Monitor VGA
jedem Artikel wird Meyer OHG angezeigt
Farbband
AX 35
es sind alle Artikel, bei jedem steht Meyer OHG!
Farbband AX 55
Disketten 3 1/2 HD
Centronics-Kabel
SELECT A.Bezeichnung, L.Lieferant
from Artikel A, Lieferant L
where L.Lieferant="Meyer OHG";
Lieferant
Meyer OHG
Meyer OHG
Meyer OHG
Meyer OHG
Meyer OHG
Meyer OHG
Meyer OHG
Meyer OHG
Meyer OHG
Meyer OHG
Meyer OHG
Meyer OHG
So ist es richtig: Equi Join, dann mit and die Einschränkung auf den einen Lieferanten:
SELECT A.Bezeichnung, L.Lieferant
from Artikel A, Lieferant L
where A.Lieferantennummer=L.LiefNr
and L.Lieferant="Meyer OHG";
Bezeichnung
Lieferant
Haube für Tastatur Meyer OHG
Disketten 3 1/2 DD Meyer OHG
Maus seriell
Meyer OHG
Monitor VGA
Meyer OHG
Centronics-Kabel Meyer OHG
Genauso suchen Sie auch nach einem ganz bestimmten Artikel, für den Daten aus den beiden Tabellen benötigt werden:
SELECT A.Bezeichnung, L.Lieferant
from Artikel A ,Lieferant L
where A.Lieferantennummer=L.LiefNr
and A.Bezeichnung="Haube für Tastatur";
Bezeichnung
Lieferant
Haube für Tastatur Meyer OHG
Die folgende Abfrage sucht nach den Artikelnummern von dem Lieferanten, der im Ort
Sprockhövel ansässig ist:
SELECT A.Artikelnummer,L.Lieferant
from Artikel A, Lieferant L
where A.Lieferantennummer=L.LiefNr
and L.Ort="Sprockhövel"
2
Artikelnummer
129
206
282
391
639
Lieferant
Meyer OHG
Meyer OHG
Meyer OHG
Meyer OHG
Meyer OHG
SQL-Befehle in Abfragen: Join
Die allgemeine Form des Equi Join lautet also:
2 beliebige Felder
in zwei Tabellen
in denen ein Feld (Feld2) gleiche Einträge hat
SELECT Tabelle1.Feld1, Tabelle2.Feld3
FROM Tabelle1, Tabelle2
WHERE Tabelle1.Feld2 = Tabelle2.Feld2
Die Felder, die in der ersten Zeile mit Select ausgewählt werden, müssen nicht unbedingt
aus beiden Tabellen stammen, dann aber ein Teil der Bedingung – sonst läge gar kein Join
vor. Sehen Sie dazu folgendes Beispiel:
Aufgabe: Wie lauten Artikelnummer und Bezeichnung von den Artikeln, deren Lieferantennummer 25 ist und von denen mehr als 100 Stück vorrätig sind?
Artikelnummer Bezeichnung
SELECT A.Artikelnummer,A.Bezeichnung
206 Disketten 3 1/2 DD
from Artikel A, Lieferant L
208 Disketten 5 1/4 HD
where A.Lieferantennummer=L.LiefNr
331 Disketten 5 1/4 DD
and L.LiefNR=25
352 Tastatur deutsch
and A.Menge>100
452 Farbband AX 35
463 Farbband AX 55
530 Disketten 3 1/2 HD
Die Verbindung kann sich
natürlich über mehr als zwei
Tabellen erstrecken.
Durch eine SQL-Abfrage
sollen
Artikelbezeichnung
und Name des Kunden von
den Verkaufsdaten angezeigt
werden, wo der Kunde namens Hartfiel etwas gekauft
hat. Dazu muss das Beziehungsmodell der Datenbank
herangezogen werden:
Sie sehen dabei, dass die
Verbindung von Kunde zu
Artikel über die beiden Tabellen Verkauf und Verkaufdetails läuft.
Deshalb müssen in der Abfrage alle Verbindungen aufgeführt werden:
SELECT A.Bezeichnung, K.Name, VD.Menge
from Artikel A, Kunde K, Verkauf Vk,
VerkaufDetails VD
where Vk.Kundennummer = K.Kundennummer
and Vk.VerkaufNr = VD.VerkaufNr
and Vd.Artikelnummer=A.Artikelnummer
and K.Name="Hartfiel"
3
Bezeichnung Name Menge
Centronics-Kabel Hartfiel
50
SQL-Befehle in Abfragen: Join
Versuchen Sie nun, die folgenden Beispiele zur Datenbank Firma zu lösen:
1. Wie lautet die Adresse des Lieferanten, der den Artikel mit der Nummer 129 liefert?
Lieferant
Straße PLZ
Ort
Meyer OHG Haßlinghauser Straße 89 43320 Sprockhövel
2. Wer hat nach dem 15.08.01 etwas bestellt?
(Vorsicht: in der SQL-Version, die in Access zur Anwendung kommt, muss das Datum in der Form #8/15/2001# (Monat/Tag/Jahr) angegeben werden)
Name
Grieswelle
Hartfiel
Schäfer
Schäfer
Bestelldatum
12.11.01
30.09.01
25.09.01
13.10.01
3. An welchem Datum hat der Kunde mit der Nummer 10903 etwas bestellt?
Bestelldatum
13.05.01
20.05.01
30.06.01
4. Welcher Kunde hat Artikel mit einem Einzelverkaufspreis über 100 Euro bestellt?
Name
Handke
Hillmann
5. Der Mindestbestand und die Artikelbezeichnung von den Artikeln, die Meyer OHG liefert, soll aufgelistet werden.
Mindestbestand
10
15
20
50
10
Bezeichnung
Haube für Tastatur
Disketten 3 1/2 DD
Maus seriell
Monitor VGA
Centronics-Kabel
6. Welcher Lieferant liefert welche bruchempfindlichen Artikel?
Lieferant
Schmitz AG
Meyer OHG
Meyer OHG
Bezeichnung
Motherboard ASUS A7 V3
Monitor VGA
Centronics-Kabel
Centronics-Kabel sind normalerweise nicht
bruchempfindlich, aber so sind sie in der
Tabelle gespeichert!
7. Name und Anschrift des Kunden, der ein Farbband gekauft hat (Achtung: Es gibt
zwei Sorten Farbbänder)
Name
Grieswelle
Handke
Hillmann
Schäfer
Straße
Uferweg 9
Wörterweg 19
Kölner Straße 80
Bergstraße 7
PLZ
50983
80702
40217
60321
Ort
Köln
München
Düsseldorf
Frankfurt
8. Artikelbezeichnung, Name des Kunden, gekaufte Menge und Gesamtpreis (Verkaufspreis mal Menge sowie die Mehrwertsteuer von 16 % von den Verkaufsvorgängen, bei denen Nettopreis mal verkaufte Menge über 2000 Euro lag.
Bezeichnung
Name
Menge
Motherboard ASUS A7 V3
Motherboard ASUS A7 V3
Maus seriell
Hillmann
Handke
Schäfer
10
3
30
4
Gesamtprei
s
9800
2940
2250
MWSt
1568
470,4
360
SQL-Befehle in Abfragen: Join
Bezeichnung
Name
Menge
Centronics-Kabel
Grieswelle
100
Gesamtprei
s
2327
MWSt
372,32
Lösungen zu den Aufgaben S. 24:
1.
SELECT L.Lieferant, L.Straße, L.PLZ, L.Ort
from Lieferant L, Artikel A
where A.Lieferantennummer=L.LiefNr
and A.Artikelnummer=129;
2.
SELECT distinct K.Name, VK.Bestelldatum
FROM Kunde AS K, Verkauf AS VK, Verkaufdetails AS VD
WHERE K.Kundennummer=VK.Kundennummer
AND VK.VerkaufNr=VD.VerkaufNr
AND VK.Bestelldatum>#8/15/2001#;
3.
SELECT Vk.Bestelldatum
from Kunde K, Verkauf VK
where K.Kundennummer=VK.Kundennummer
and K.Kundennummer=10903;
4.
SELECT distinct K.Name
FROM Artikel A, Kunde K, Verkauf Vk, VerkaufDetails VD
WHERE Vk.Kundennummer=K.Kundennummer
And Vk.VerkaufNr=VD.VerkaufNr
And Vd.Artikelnummer=A.Artikelnummer
and A.Einkaufspreis>100;
5.
SELECT A.Mindestbestand, A.Bezeichnung
FROM Lieferant L, Artikel A
WHERE A.Lieferantennummer=L.LiefNr
And L.Lieferant="Meyer OHG";
6.
SELECT L.Lieferant, A.Bezeichnung
FROM Lieferant L, Artikel A, Lager LG
WHERE A.Lieferantennummer=L.LiefNr
and LG.Artikelnummer=A.Artikelnummer
And LG.bruchempfindlich=-1;
7.
Select distinct K.Name, K.Straße, K.PLZ, K.Ort
from Kunde K, Artikel A, Verkauf VK, Verkaufdetails VD
where K.Kundennummer= VK.Kundennummer
and Vk.Verkaufnr=VD.Verkaufnr
and Vd.Artikelnummer=A.Artikelnummer
and A.Bezeichnung like "Farbband*";
8.
Select A.Bezeichnung, K.Name, vd.Menge, A.Verkaufspreis*VD.Menge as
Gesamtpreis, A.Verkaufspreis*VD.Menge*0.16 as MWSt
from Kunde K, Artikel A, Verkauf VK, Verkaufdetails VD
where K.Kundennummer= VK.Kundennummer
and Vk.Verkaufnr=VD.Verkaufnr
5
SQL-Befehle in Abfragen: Join
and Vd.Artikelnummer=A.Artikelnummer
and VD.Menge*A.Verkaufspreis>2000;
Verbindung von Tabellen: Outer Join
Die Select-Abfrage über verbundene Tabellen ist bisher so erfolgt, wie es der Tabellenverbindung mit relationaler Integrität entspricht. Die SQL-Syntax nach ANSI-Standard sieht darüber hinaus auch den Outer Join vor, womit sich Daten erfragen lassen, die teilweise sogar
außerhalb dessen liegen, was in relationaler Integrität verbundene Tabellen zulassen.
Aufgabe: Erstellen Sie in Access eine neue Datenbank namens Join. Erfassen Sie diese
beiden kleinen Tabellen namens Kunde und Bestellung.
Vergleichen Sie die Daten in folgenden beiden Tabellen. Es gibt ein Feld BestellNr, über
welches eine Verbindung zwischen den Tabellen hergestellt werden kann. Aber es gibt nicht
pro Kunde eine Bestellung (z. B. die Kunden Hilmayer und Jagomir mit den Bestellnummern
127 und 145 sind ohne Bestellung), und umgekehrt ist für die Bestellung 112, 114 und 121
kein Kunde vorhanden.
Der erste Fall ist in einer relationalen Datenbank noch nicht zu beanstanden: Es muss möglich sein, Kunden aufzunehmen, die im Moment noch keine Bestellung aufgegeben haben.
Aber der umgekehrte Fall ist nicht verantwortbar im Sinne der Eindeutigkeit und der Kongruenz zwischen den Daten: Es darf kein „verwaiste“ Bestellung geben, von der man nicht
mehr weiß, welcher Kunde sie aufgegeben hat. Wenn relationale Integrität in der Beziehung
zwischen den Tabellen vorliegt, dann ist es auch gar nicht möglich, diese Daten aufzunehmen.
Falls Sie aber Daten in einer Datenbank antreffen, zwischen denen die Verhältnisse nicht so
eindeutig liegen, wie es eigentlich sein sollte, dann muss es zumindest möglich sein, die Datensätze herauszusuchen, denen ein Pendant in der anderen Tabelle fehlt.
In einer SQL-Abfrage ist die Verbindung zwischen den Tabellen über ein Equi Join (s. S. 19
dieses Skripts) folgende:
1.
Name Bestellnr
Schmitz
120
Manz
125
SELECT ku.Name, Be.Bestellnr
from Kunde as ku, Bestellung as be
where ku.bestellnr=be.bestellnr;
So sind zunächst nur die Daten zu finden, bei denen es eine Übereinstimmung in beiden
Tabellen über das gemeinsame Feld Bestellnr gibt.
Um auch die Daten in der Kundentabelle herauszufinden, für die keine Bestellung vorliegt –
oder umgekehrt, um die Bestellungen zu finden, für die kein Kunde bekannt ist – wird das
Outer Join benötigt.
6
SQL-Befehle in Abfragen: Join
Die Bedeutung des Outer Join kann man sich so vorstellen: Welche Daten liegen außerhalb
(=outer) dessen, was in einer voll übereinstimmenden Beziehung zwischen den Tabellen
vorkommt. Dabei unterscheidet man zwischen dem
Left outer Join und
Right outer join links bzw. rechts außerhalb liegende Verbindung
Links und rechts kann man sich dabei durchaus räumlich vorstellen: In der Abbildung vorige
Seite oben steht die Tabelle Kunde links. „Left outer Join“ heißt dann:
Stelle eine Verbindung her, bei der die Daten der links stehenden Tabelle Kunde voll einbezogen werden (also auch Kunden ohne Bestellung). Natürlich hätten die Tabellen in der Abbildung auch vertauscht sein können, es kommt tatsächlich darauf an, welche Tabelle zuerst
erwähnt in der Abfrage genannt wird.
In der ANSI-Syntax bedeutet:
• LEFT OUTER JOIN
Schließt sämtliche Zeilen aus der ersten Tabelle und nur die zutreffenden Zeilen aus
der zweiten Tabelle ein
• RIGHT OUTER JOIN
Schließt sämtliche Zeilen aus der zweiten Tabelle und nur die zutreffenden Zeilen
aus der ersten Tabelle ein
• FULL OUTER JOIN (funktioniert nicht in Access)
Schließt sämtliche nicht zutreffenden Zeilen aus beiden Tabellen ein
Im Abfrage-Entwurfsbildschirm von Access sehen Sie die möglichen Verknüpfungen so beschrieben:
Equi Join
Left outer Join
Right outer Join
Aufgabe: Erstellen Sie eine Abfrage in der SQL-Ansicht des Abfragefensters. Erstellen Sie
die left outer join-Abfrage:
Ergebnis:
Name Bestellnr
Schmitz
120
Müller
Manz
125
Hilmayer
Jagomir
SELECT ku.Name, Be.Bestellnr
from Kunde as ku
left outer join Bestellung as be
on ku.bestellnr=be.bestellnr;
Im Gegensatz dazu liefert die right outer Join-Abfrage folgendes Ergebnis:
Name Bestellnr
112
SELECT ku.Name, Be.Bestellnr
from Kunde as ku
7
SQL-Befehle in Abfragen: Join
right outer join Bestellung as be
on ku.bestellnr=be.bestellnr;
Schmitz
Manz
114
120
121
125
Was mit "rechts" und was mit "links" angesprochen wird, hängt nur davon ab, was man in
welcher Reihenfolge anspricht. In der folgenden Abfrage ist die Tabelle Bestellung links:
SELECT Be.Bestellnr, ku.Name
FROM Bestellung AS be left outer JOIN kunde as ku
ON ku.bestellnr=be.bestellnr;
Bestellnr Name
112
114
120 Schmitz
121
125
Manz
Sehen Sie sich an und testen Sie, was geschieht, wenn man nur die Wörter "left" und "right"
vertauscht:
SELECT ku.Name, Be.Bestellnr
FROM Kunde AS ku LEFT JOIN Bestellung AS be
ON ku.bestellnr=be.bestellnr;
Bestellnr Name
120
Schmitz
Müller
125
Manz
Hilmayer
Jagomir
Aufgabe: Öffnen Sie die Datenbank Biblio2.
Gegenüber der vorher verwendeten Datenbank Biblio sind hier einige Änderungen durchgeführt:
⇒ Aus dem Haffmanns Verlag (Nr 9) und aus dem Verlag Milson & Boons gibt es keine
Bücher
⇒
Die Bücher mit der Kattalognummer 108 und 109 haben keinen Datensatz in der Tabelle
Katalog. Außerdem haben Sie keinen Eintrag als Verlag (Feld VerlagNr) in der Tabelle
Bücher.
⇒
Das Buch Die Firma von John Grisham ist mit Verlag Nr 11 aufgenommen, für den es
keine Entsprechung in der Verlagstabelle gibt (Verlag 11 existiert nicht).
⇒
In der Tabelle Buchgruppe gibt es eine KategorieNr 4 (Horror), dafür ist kein Buch eingetragen.
1)
Führen Sie zunächst einen Cross Join (Verbindung
jedes Datensatzes mit jedem Datensatz aus der
anderen Tabelle) durch. Es sollen das Feld Verlag
aus der Tabelle Verlag und das Feld Titel aus der
Tabelle Bücher angesprochen werden.
Das Ergebnis macht tatsächlich keinen Sinn, weil die
Zuordnung von Buch zu Verlag nicht deutlich wird.
8
SQL-Befehle in Abfragen: Join
2.
Führen Sie die Equi-Join-Abfrage durch (Tabellen Verlag und Titel) .
Man soll dadurch sehen könnnen, welches Buch
von welchem Verlag geliefert wird.
Blättern Sie nach unten, um zu sehen, dass das Buch
"Die Firma" von John Grisham nicht erscheint.
Einschränkung auf einen Verlag:
3.
Titel
Ergänzen Sie jetzt die Abfrage so, dass nur die verlag
Bücher aus dem Rowohlt Verlag (Nr 2) ausgewählt Rowohlt Leichen leben länger
Rowohlt Mord auf der Reeperbahn
werden.
Left outer Join:
4.
Machen Sie jetzt die Abfrage, bei der man alle Titel
Bücher mit dem dazugehörigen Verlag sieht, SQL Server 7.0
Flash 5.0
auch die Bücher ohne Verlag:
Verlag
Erkenntnis und Interesse Suhrkamp
...
...
Die Firma
left outer Join – Verlag links
5.
Vertauschen Sie rechts und links, indem Sie Verlag verlag
nach links nehmen und auch die Verlage ohne Bücher ....
Fischer
im Ergebnis sehen:
Titel
....
Außenseiter
Haffmanns
Milson & Boons
right outer join: Verlag rechts
6.
Machen Sie die Abfrage mit demselben Ergebnis, aber
Titel
so, dass Sie die Titel wieder links sehen:
....
Verlag
.....
Außenseiter Fischer
Haffmanns
Milson & Boons
Gruppe katalognummer
....
...
Krimi
768
Krimi
720
Horror
7.
Verbinden Sie in einer Abfrage die Tabellen Buchgruppe
und Katalog, sodass man auch die Kategorie "Horror"
sieht, für die keine Bücher vorhanden sind:
8.
Verlag
Abfrage in der Verbindung Verlag – Titel, aber nur für Titel
Volkswirtschaftslehre Fischer
die Verlage Fischer und Haffmanns:
Außenseiter
9.
Titel und Verlag von den Büchern, die
entweder von Christie geschrieben wurden
oder von denen der verlag Milson & Boons
ist:
9
Titel
Mord im Orientexpress
Hercule Poirot's Weihnachten
Poirot's letzter Fall
Fischer
Haffmanns
Verlag
Scherz
Scherz
Scherz
Milson & Boons
SQL-Befehle in Abfragen: Join
Lösungen S. 25 - 26:
1. SELECT ve.verlag, bu.Titel
from verlag ve, bücher bu;
2.
SELECT ve.verlag, bu.Titel
FROM verlag AS ve, bücher AS bu
where bu.verlagnr=ve.verlagnr
3.
SELECT ve.verlag, bu.Titel
FROM verlag AS ve, bücher AS bu
where bu.verlagnr=ve.verlagnr
and ve.verlagnr=2;
4. SELECT bu.Titel, ve.Verlag
FROM Bücher AS bu LEFT outer JOIN Verlag AS ve
ON bu.VerlagNr=ve.VerlagNr;
5. SELECT ve.verlag, bu.Titel
FROM Verlag AS ve LEFT outer JOIN Bücher AS bu
ON Ve.VerlagNr=Bu.VerlagNr;
6. SELECT bu.Titel, ve.Verlag
FROM Bücher AS bu RIGHT outer JOIN Verlag AS ve
ON bu.VerlagNr=ve.VerlagNr;
7. SELECT bg.Gruppe, ka.katalognummer
FROM Buchgruppe AS bg LEFT outer JOIN Katalog AS ka
ON bg.kategorienr=ka.kategorienr;
8. SELECT bu.Titel, ve.Verlag
FROM Bücher AS bu RIGHT JOIN Verlag AS ve ON bu.VerlagNr=ve.VerlagNr
where ve.verlag="Fischer" or ve.verlag="Haffmanns";
9. SELECT bu.Titel, ve.Verlag
FROM Bücher AS bu RIGHT JOIN Verlag AS ve
ON bu.VerlagNr=ve.VerlagNr
where ve.Verlag="Milson & Boons" or bu.Autor="Christie";
10
SQL-Befehle in Abfragen: Join
Unterabfrage aufbauen1
Unterabfragen (auch subquery genannt) sind geschachtelte Abfragen mit select. Sie können
in einer oder über mehrere Tabellen durchgeführt werden
a) Unterabfragen über mehrere Tabellen
"Vereinfacht ausgedrückt verbindet eine Unterabfrage die Ergebnismenge der einen Abfrage
mit einer anderen. Die allgemeine Syntax lautet:
SELECT *
FROM Tabelle1
WHERE Tabelle1.Eine_Spalte =
(SELECT Eine_Andere_Spalte
FROM Tabelle2
WHERE Eine_Andere_Spalte = Ein_Wert)
Da die zweite Abfrage in der ersten eingebaut ist, spricht man auch von einer verschachtelten Abfrage. "
Testen Sie dies mit der Datenbank Firma2003.mdb
In beiden Tabellen, Lieferant und Artikel, ist das Feld Liefnr vorhanden. Wenn man nun die
Artikel suchen will, die vom Lieferanten Meyer OHG geleifert werden, dann geht das – statt
wie bisher gelöst über den Equi Join auch über eine Unterabfrage:
SELECT * from artikel
where liefnr=
(select liefnr from lieferant
where lieferant="Meyer OHG");
b) Unterabfragen in einer Tabelle
Wichtiger als die zuerst gezeigte Abfrageform sind Unterabfragen in einer Tabelle, weil man
mit ihnen Ergebnisse erzielen kann, die man auf andere Art nicht erzielt hätte.
Am Beispiel der Tabelle Abteilung, Datenbank: SQL-Beispiele, soll dies gezeigt werden:
Zunächst eine Abfrage, die noch nichts direkt mit der subquery zu tun hat, die aber dazu
dient, zur Problematik hinzuführen:
Es soll der Mittelwert des Gehalts aus der Tabelle ermittelt werden:
SELECT Avg(gehalt) AS Mittelwert_Gehalt
FROM abteilung;
1
aus: SQL in 21 Tagen, Kap. 7
11
SQL-Befehle in Abfragen: Join
Jetzt soll außerdem nach Abschlüssen (Ausbildung, Uni oder FH) gruppiert werden. Damit
das Ergebnis besser lesbar ist, soll der Mittelwert gerundet werden.
SELECT abschluss, round(Avg(gehalt),2) AS Mittelwert_Gehalt
FROM abteilung
group by abschluss;
Nach der Gruppierung kann bekanntlich nicht mit where gearbeitet werden, wohl aber mit
der having-Klausel, wenn eine Bedingung für die Ausgabe gebildet wird:
SELECT abschluss, round(Avg(gehalt),2) AS Mittelwert_Gehalt
FROM abteilung
group by abschluss
having avg(gehalt)<3000;
Mit der Unterabfrage kann man nun herausbekommen, wie der Mittelwert des Gehalts ist,
bezogen auf die Abteilung, in der sich das kleinste Gehalt befindet:
SELECT round(Avg(gehalt),2) AS Mittelwert_Gehalt
FROM abteilung
where gehalt=(select min(gehalt) from abteilung);
Dabei ist aber nicht sichtbar, welche Abteilung es ist. Lösen wir solch ein Problem wieder
über eine Subquery:
Frage: In welcher Abteilung findet sich das höchste Gehalt?
SELECT abteilung
FROM abteilung
where gehalt=(select max(gehalt) from abteilung);
Zusätzlich stellen wir die Frage: wie hoch ist das höchste Gehalt, und in welcher Abteilung finden wir es?
SELECT abteilung, gehalt
FROM abteilung
where gehalt=(select max(gehalt) from abteilung);
Aufgaben:
1) Wie heißt der Angestellte mit den meisten Krank_Tagen?
name
Gernegroß
2) In welcher Abteilung findet sich der Angestellte mit den
meisten Krank_Tagen, wie viele sind es, und wie heißt er?
name krank_tage abteilung
Gernegroß
20 Marketing
3) Wie ist der Abschluss (Ausbildung, FH oder Uni) dessen,
der die meisten Urlaubstage hat?
abschluss
Ausbildung
4) Wer bezieht das höchste Gehalt?
name
Klinger
12
SQL-Befehle in Abfragen: Join
zu 1)
SELECT name
FROM abteilung
where krank_tage=(select max(krank_tage) from abteilung);
zu 2)
SELECT abteilung,name, krank_tage
FROM abteilung
where krank_tage=(select max(krank_tage) from abteilung);
zu 3)
SELECT abschluss
FROM abteilung
where Urlaubstage=(select max(urlaubstage) from abteilung);
zu 4)
SELECT name
FROM abteilung
where gehalt=(select max(gehalt) from abteilung);
weitere Übungen:
wie viele Leute sind verheiratet?
verheiratet
6
Wer hat das höchste Gehalt aus der Abteilung Personal???
13
SQL-Befehle in Abfragen: Join
SELECT ve.verlag, bu.Titel
FROM Verlag as ve left JOIN Bücher as bu
ON Ve.VerlagNr = Bu.VerlagNr;
Im „normalen“ Join erscheint der Verlag nicht
SELECT ve.verlag, bu.Titel
FROM verlag AS ve, bücher AS bu
where bu.verlagnr=ve.verlagnr
verlag
....
Diogenes
Fischer
Fischer
Titel
.....
Der tiefe Schlaf
Volkswirtschaftslehre
Außenseiter
verlag
....
Diogenes
Fischer
Fischer
Haffmanns
Titel
.....
Der tiefe Schlaf
Volkswirtschaftslehre
Außenseiter
1.1 Left Outer Join
SELECT ve.verlag, bu.Titel
FROM Verlag AS ve
left outer join Bücher AS bu
ON Ve.VerlagNr=Bu.VerlagNr;
1.2 Right Outer JOIN
verlag
Titel
Diogenes
Der tiefe Schlaf
Fischer Volkswirtschaftslehre
Fischer
Außenseiter
Die Firma
SELECT ve.verlag, bu.Titel
FROM Verlag AS ve
right outer JOIN Bücher AS bu
ON Ve.VerlagNr=Bu.VerlagNr;
Umgekehrte Reihenfolge:
Titel
verlag
Der tiefe Schlaf
Diogenes
Volkswirtschaftslehre Fischer
Außenseiter
Fischer
Haffmanns
SELECT bu.Titel, ve.verlag
FROM Bücher AS bu
right outer JOIN
Verlag AS ve
ON Ve.VerlagNr=Bu.VerlagNr;
Full outer Join geht nicht in Access:
SELECT bu.Titel, ve.verlag
FROM Bücher AS bu
full outer JOIN Verlag AS ve
ON Ve.VerlagNr=Bu.VerlagNr;
14
SQL-Befehle in Abfragen: Join
Lösungen:
10. SELECT ve.verlag, bu.Titel
from verlag ve, bücher bu;
11. SELECT ve.verlag, bu.Titel
FROM verlag AS ve, bücher AS bu
where bu.verlagnr=ve.verlagnr
12. SELECT ve.verlag, bu.Titel
FROM verlag AS ve, bücher AS bu
where bu.verlagnr=ve.verlagnr
and ve.verlagnr=2;
13. SELECT bu.Titel, ve.Verlag
FROM Bücher AS bu LEFT JOIN Verlag AS ve
ON bu.VerlagNr=ve.VerlagNr;
14. SELECT ve.verlag, bu.Titel
FROM Verlag AS ve LEFT JOIN Bücher AS bu
ON Ve.VerlagNr=Bu.VerlagNr;
15. SELECT bu.Titel, ve.Verlag
FROM Bücher AS bu RIGHT outer
ON bu.VerlagNr=ve.VerlagNr;
JOIN Verlag AS ve
16. SELECT bg.Gruppe, ka.katalognummer
FROM Buchgruppe AS bg LEFT JOIN Katalog AS ka
ON bg.kategorienr=ka.kategorienr;
15