SQL Oracle Fragen zu Select-Abfragen

[raven16]

Hallo,

ich muss in Oracle ein paar Aufgaben lösen und komme dort nicht weiter...

Ich habe 3 Tabellen: buchung, mahnung und gaeste

meine erste Aufgabe wäre:
Welche Gäste aus Deutschland (Kundennummer, Name und Land ausgeben) haben Buchungen vorgenommen, obwohl noch Mahnungen ausstehen.

Rein logisch würde ich gucken welche kundennummer in mahnung vorhanden sind und in buchung eingetragen sind und die herausfiltern die aus Deutschland kommen

Die Spalten die Dazu benötigt werden sind mahnung.kundennr, gaeste.kundennr, buchung.kundennr und gaeste.land

Hab schon ne Abfrage probiert, aber da kommen zu viele unerklärliche Ergebnisse heraus weil der einfach alle Übereinstimmungen aus allen Tabellen nimmt...

select g.kundennr, g.name, g.land from t5_1_gaeste g, t5_1_mahnung m,t5_1_buchung b
where g.land='D'
and g.kundennr = m.kundennr
and g.kundennr = b.kundennr
and m.kundennr = b.kundennr
order by g.name

 

[wahli]

Reicht ein "distinct"?

Ergänzung (26. Juli 2011)

und die letzte Bediegung müsstest du dir sparen können:
and m.kundennr = b.kundennr

Wenn a=c
und a=b
dann ist automatisch b=c

 

[kaikuwe]

select gaeste.kundennummer, gaeste.name, gaeste.land
from t5_1_gaeste gaeste, t5_1_mahnung mahnung, t5_1_buchung
where gaeste.land = 'Deutschland'
and gaeste.kundennummer = mahnung.kundennummer
and mahnung.kundennummer = buchung.kundennummer
order by gaeste.kundennummer

evtl. nen distinct oder group by dazu

Und natürlich nur unter der Bedingung, das eine Kundennummer in "Mahnung" auch heißt, dass eine Mahnung aussteht. Und das eine Kundennummer in "Buchung" auch heißt das gebucht wurd.

 

[MistaJack]

Wie wärs denn hiermit?

select g.kundennr, g.name, g.land
from t5_1_gaeste g
where g.land='D'
and exists ( select 1 from t5_1_buchung where kundennr = g.kundennr)
and exists ( select 1 from t5_1_mahnung where kundennr = g.kundennr)
order by g.name

Alle Gäste, zu denen sowohl Buchungen als auch Mahnungen existieren.

 

[raven16]

Reicht ein "distinct"?

Ergänzung (26. Juli 2011)

und die letzte Bediegung müsstest du dir sparen können:
and m.kundennr = b.kundennr

Wenn a=c
und a=b
dann ist automatisch b=c

Jow genau das distinct war es, des kannte ich bis jetzt gar nicht . Ist das denn die eleganteste Lösung die doppelten wegzustreichen oder gibt es da noch eine andere bessere Möglichkeit?

Und ja das b=c ist ein wenig verpeilt gewesen

 

[kaikuwe]

Jow genau das distinct war es, des kannte ich bis jetzt gar nicht . Ist das denn die eleganteste Lösung die doppelten wegzustreichen oder gibt es da noch eine andere bessere Möglichkeit?

Und ja das b=c ist ein wenig verpeilt gewesen

"group by" gibts noch

 

[raven16]

Hey nochmal,

ich habe noch eine Frage zu einer anderen Select-Abfrage:

Und zwar soll ich jetzt Personen selektieren,
die in jedem Jahr, in dem Buchungen eingetragen wurden in die Datenbank, eine Buchung gemacht haben

Mein Problem dabei ist, dass ich nicht weiß wie ich den Where-clause formulieren soll

ich kann ja nicht sagen
where jahr = '2005'
and jahr = '2006'
and jahr = '2007'
...

das wär ja viel zu umständlich wenn die Jahresspanne zu groß wird
gibt es da ne leichtere methode?

 

[kaikuwe]

where jahr in (x,y,z) z.B.

between gibts auch


Evtl. hilft das, ansonsten musst du deine Frage mal etwas genauer formulieren.

 

[yxcv]

Hallo raven16

deine Abfrage zur ersten Frage kann noch optimiert werden.
Beispielsweise sollten Tabellen immer per JOIN verbunden werden.
Mit WHERE kommst du zwar auf's gleiche Ergebnis,
es ist aber immer langsamer (im Optimalfall gleich schnell) als ein JOIN.

Probiers Mal aus:

SELECT DISTINCT 
	 g.kundennr
	,g.name
	,g.land 
FROM t5_1_gaeste g
INNER JOIN  t5_1_mahnung m
		ON g.kundennr = m.kundennr
INNER JOIN t5_1_buchung b
		ON g.kundennr = b.kundennr
where g.land='D'
order by g.name

Zu deiner neuen Fragestellung.
Hier ein Lösungsweg, der jedoch für TSQL (MS SQL) geschrieben ist.
Möglicherweise musst es deshalb geringfügig für das eigenwillige Oracle anpassen.

Alle Spalten (außer KdNr) im SELECT können entfernt/auskommentiert werden
und zeigen dir nur die Teilergebnisse in der HAVING Abfrage.

SELECT
	 KdNr
	,COUNT(distinct YEAR(Datum)) AS anzBuchJahre
	,MIN(YEAR(Datum)) AS KdSeit
	,MAX(YEAR(Datum)) AS KdBis
	,DiffMinMaxJahre = MAX(YEAR(Datum)) - MIN(YEAR(Datum)) + 1
FROM Buchungen
GROUP BY KdNr
HAVING --anzBuchJahre = DiffMinMaxJahre
	COUNT(distinct YEAR(Datum)) = (MAX(YEAR(Datum)) - MIN(YEAR(Datum)) + 1)

 

[raven16]

Danke für die Antworten

aber

1. Was ist jetzt daran besser wenn man inner join verwendet?
2. Ich bin nur leihe und das 2. sieht sehr kompliziert aus -->Den Anfang verstehe ich noch aber nachher den Part mit dem having das ist schon sehr kompliziert für mich

wäre also schön wenn du das vllt. noch ein wenig erklären könntest

 

[Black_Panter]

Zu 1. Ob man INNER, OUTER, LEFT,... JOIN verwendet besagt nur welche Schnittmenge benutzt wird.
JOIN ist wie oben schon erwähnt des Weiteren schneller als wenn man mit WHERE überprüft. Der Hintergrund ist meines Wissens nach, dass beim JOIN nur die Datensätze zurückgegeben werden bei denen die JOIN-Bedingung passt und bei WHERE werden alle Tabellen zurückgegeben und dann erst aussortiert.

Was genau findest du am 2. kompliziert? Die ersten 6 Zeilen sind nur die Spalten die ausgegeben werden. COUNT(), MIN() und MAX sind Fuinktionen und sollten selbsterklärend sein.
"FROM Buchungen": sollte klar sein.
"GROUP BY KdNr": alle Ergebnisse mit der gleichen KdNr werden zusammengefasst
"HAVING ...": IM prinzip wie WHERE, nur dass nicht über die einzelnen Zeilen itteriert wird, sondern über die einzelnen Gruppen.

@Rest: Falls ich grad was falsches erzählt habe, bitte berichtigen

 

[yxcv]

1. Was ist jetzt daran besser wenn man inner join verwendet?

Wie bereits geschrieben ist es schneller und benötigt weniger Ressourcen (RAM+CPU).
Beim JOIN weiß die Datenbank schon vor der Ausführung wie die Tabellen miteinander zu verbinden sind
und kann dementsprechend interne Optimierungen vornehmen.

Beim Verbinden per WHERE wird ein Kreuzprodukt (jede Kombination) von allen Zeilen und Tabellen erstellt und erst danach auf übereinstimmende Werte verglichen.
Viele Datenbank optimieren (umwandlung als JOIN) dies, sodass der Benutzer glauben könnte es sei egal.
Bei Abfragen mit wenigen tausend Datensätzen ist der Unterschied klein,
bei Millionen Zeilen sind Ausführungsdauer und Ressourcenunterschiede teilweise extrem groß.

2. Ich bin nur leihe und das 2. sieht sehr kompliziert aus -->Den Anfang verstehe ich noch aber nachher den Part mit dem having das ist schon sehr kompliziert

Hab doch auch hier schon geschrieben, dass du die Teilergebnisse im SELECT angezeigt bekommst.
Die Spaltennamen hab ich glaube auch nicht sooo schlecht gewählt.

Das HAVING prüft ob: anzBuchJahre = DiffMinMaxJahre
Dabei ist anzBuchJahre, die Zahl der unterschiedlichen Jahre in dem ein Kunde bestellt hat.
Das Jahr für ein Datum wir bei TSQL mit YEAR(Datum) ermittelt.
Weil innerhalb eines Jahres mehrere Buchungen pro Kunde vorliegen können,
soll jedes Jahr nur einmal gezählt werden. Dies geht mit COUNT(distinct YEAR(Datum))

DiffMinMaxJahre ermittelt die Anzahl der Jahre zwischen der ersten und letzten Buchung.
Das Jahr der letzten Buchung Max(YEAR(Datum))
und Jahr der ersten Buchung: MIN(YEAR(Datum))
Das "+ 1" ist nötig, weil das Min bzw. Max Jahr selbst mitgezählt werden muss.