Frage zu Oracle/SQL-Übungsaufgabe

[BattleRoyale]

hi!
ich versuche mich atm in oracle/sql und habe probs mit einer übungsaufgabe.
mich würde interessieren wie ihr die lösen würdet. die musterlösung (siehe unten) wirft bei mir ein paar fragen auf.
da ja mehrere wege zum ziel führen würde mich interessieren wie ihr die (ggf. simpler) lösen würdet.

vom lernen her bin ich gerade bei dem bereich "group/having" etc.

die aufgabe:
Geben Sie den Bus aus, der am meisten Fahrten gefahren ist (fahrzeug_id, Anzahl_fahrten)! (ERM siehe unten)



Musterlösung mit fragen von mir. (bestimmt totale noob fragen :-) )

SELECT b.fahrzeug_id, COUNT(*)
FROM busse b, einsatzplan e
WHERE e.fahrzeug_id = b.fahrzeug_id
GROUP BY b.fahrzeug_id
HAVING COUNT(*) >= ALL(SELECT COUNT(*) FROM einsatzplan e2 GROUP BY e2.fahrzeug_id)

meine fragen:
warum wird die tabelle "busse" mit einbezogen? alle erforderlichen daten stehen doch in der tabelle "einsatzplan".

kann mir jemand mal schritt für schritt erläutern was hier bei der "having" klausel passiert?
having ansich ist mir ja klar. aber dieses konstrukt hier ist mir zu hoch.

[Sephiroth]

siehe https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Comparison-Conditions.html#GUID-828576BF-E606-4EA6-B94B-BFF48B67F927__CJAGAABC zum all operator

also quasi: das mit anzahl >= allen anzahlen aus dem subquery ... und somit das mit der höchsten.

[BattleRoyale]

>= all nicht nicht das prob (glaube ich :biggrin:)
mir will nicht in den kopf was er bei den beiden "counts" in der having klausel zählt. ;(

ich will gerne verstehen was da schritt für schritt passiert.
da ist bei mir der groschen noch nicht gefallen.

[Gast]

HAVING COUNT(*) >= gibt einfach den eintrag mit dem höchsten wert im querry ALL(SELECT COUNT(*) FROM einsatzplan e2 GROUP BY e2.fahrzeug_id) aus.

[Sephiroth]

in "HAVING COUNT(*) " bezieht sich das count auf das ergebnis der aggregationsfunktion aus dem select - anders ausgedrückt auf den wert der in der zeile stünde; da wird also nicht nochmal gezählt. das was da angegeben wird muss entweder eine aggregationsfunktion aus der select-klausel sein (gleicher ausdruck) oder eine spalte aus der group-by-klausel.

das count aus dem "all", sprich der rechten seite der having-klausel, ist ein eigenständiges subquery (kannste auch separat ausführen). da werden dann nur die anzahl der fahrten eines fahrzeugs selektiert, also z.b. drei zeilen mit 10, 7 und 3. und das "all" sagt dann: hey, nimm doch bitte alle bei denen die anzahl der fahrten >= 10 und >= 7 und >= 3 ist.

p.s.
schau dir den explain plan im sql developer an.

p.p.s.
ja, der join mit "busse" kann weg.

[BattleRoyale]

VIELEN DANK!

das count bei having bezieht sich auf das count aus der ergebnistabelle von group by.
ahhh okok das macht sinn :-)

langsam macht es klick!
wenn der join raus ist ist es auch schonmal viel übersichtlicher.

soweit klar. ABER eine frage hätte ich noch.
das ">=ALL" vergleicht also folgende tabellen miteinander (siehe anhang).

>=ALL zeigt all das an was true zurückgibt. richtig?
muss ich mir das so vorstellen das er alle miteinander vergleicht (siehe tabellen):
12 >= 12 true
12 >= 24 false also fällt links die 12 raus.
---
24 >= gibt bei 32 ein false zurück und fällt auch raus.
---
14 >= 12 true
14 >= 24 false fällt also raus
---
.
.
bei 32 ist dann bei jeden vergleich true und darum das ergebnis.

[Sephiroth]

>=ALL zeigt all das an was true zurückgibt. richtig?
muss ich mir das so vorstellen das er alle miteinander vergleicht (siehe tabellen):
12 >= 12 true
12 >= 24 false also fällt links die 12 raus.
---
24 >= gibt bei 32 ein false zurück und fällt auch raus.
---
14 >= 12 true
14 >= 24 false fällt also raus
---
.
.
bei 32 ist dann bei jeden vergleich true und darum das ergebnis.
ja genau, wobei man besser sagen sollte "überall true" oder "keines false" liefert.

und im schlechtesten fall hättest du n² vergleichsoperationen (n boolesche ausdrücke mit je n konjunktionen), weil jeder wert links mit allen werten rechts verglichen werden muss.

[Matrix316]

Diese Musterlösung sieht mir auch etwas kompliziert aus. ;)

[Sumpfmolch]

Ich hätte da vermutlich irgendwas in die Richtung

select fahrzeug_id, count(Fahrt_id)
from Einsatzplan
group by fahrzeug_id
order by count(Fahrt_id) desc
limit 1

hingeworfen...

[BattleRoyale]

ich hätte nochmal eine aufgabe bei der ich irgendwie nicht zu potte komme.
ggf. kann mir wer helfen? :biggrin::biggrin:

[Sumpfmolch]

Gibt ja immer verschiedene Wege, ich würde vermutlich zwei über UNION zusammengefasste Queries machen, damit ich eine Tabelle mit nur zwei Spalten "Mannschaft", "Punkte" habe. Da kann ich dann nach Mannschaft gruppieren und die Punkte aufsummieren. Da hat man dann gleich die Ligatabelle fertig...

[BattleRoyale]

soweit ein guter tipp

schaut dann so aus.

SELECT Heiimmannschaft AS Mannschaft, Heimpunkte AS Punkte
from spiel
UNION ALL
SELECT Gastmannschaft, Gastpunkte
from spiel;

dann hab ich das ergebnis von anhang 1.
aber ab wie dann weiter? group by geht nicht (oder ich kanns nicht).

ggf. was mit sum(punkte) where Mannschaft = 'Bayern Münchne'????????? aber wie baue ich das da sein???

[nairune]

SELECT
SUM(
CASE WHEN Heimmannschaft = 'Bayern München' THEN ISNULL(Heimpunkte, 0) ELSE 0 END
+
CASE WHEN Gastmannschaft = 'Bayern München' THEN ISNULL(Gastpunkte, 0) ELSE 0 END
) AS Punkte
FROM Spiel

Bei so einer dämlichen Formulierung der Aufgabenstellung hätte ich aber einfach geschrieben:
SELECT 10

[BattleRoyale]

Bei so einer dämlichen Formulierung der Aufgabenstellung hätte ich aber einfach geschrieben:
SELECT 10

die idee hatte ich auch :biggrin::biggrin::biggrin::biggrin:

hmmm mit deiner lösung kann ich leider nichts anfangen. "case" ist mir föllig unbekannt (aber das bedeutet nix. ggf. kommt das im buch noch). das ist dann wohl PL/SQL. das hatte ich noch nicht. :-(

[nairune]

Das ist Standard in jedem SQL Datenbanksystem und kann man googlen... letzteres zu lernen ist das 1x1 in der Softwareentwicklung.
Die Schlüsselwörter sagen doch bereits recht gut, was er tut:

CASE WHEN Heimmannschaft = 'Bayern München' THEN ISNULL(Heimpunkte, 0) ELSE 0 END
Wenn Heimmannschaft Bayern ist, nimmt er die Heimpunkte (null-Prüfung nur der Vollständigkeit halber, in deinem Fall nicht notwendig), ansonsten 0 Punkte, da wir ja nicht die Punkte anderer Mannschaften zählen wollen.
Dasselbe noch mal, wenn Bayern die Gastmannschaft war. Es wird zwar addiert, in jeder Zeile sollte aber eine Seite 0 sein, da Bayern ja nie sowohl Gast- als auch Heimmannschaft war.

Ohne das SUM() ist die Ausgabe also:
1 (aus 1 + 0)
3 (0 + 3)
3 (3 + 0)
3 (0 + 3)

SUM() aggregiert alles in eine Zahl, das Ergebnis sollte 10 lauten.

[Sephiroth]

SELECT
SUM(
CASE WHEN Heimmannschaft = 'Bayern München' THEN ISNULL(Heimpunkte, 0) ELSE 0 END
+
CASE WHEN Gastmannschaft = 'Bayern München' THEN ISNULL(Gastpunkte, 0) ELSE 0 END
) AS Punkte
FROM Spiel

Bei so einer dämlichen Formulierung der Aufgabenstellung hätte ich aber einfach geschrieben:
SELECT 10
naja, ineffizient weil es über alle zeilen geht. dann lieber so
https://www.db-fiddle.com/f/8Fm8j5C21aJJpJfcwtYSoN/0
https://livesql.oracle.com/ erfordert leider einen oracle account, daher in mysql.


create table spiele(
m1 varchar(20) not null,
m2 varchar(20) not null,
t1 TINYINT not null default 0,
t2 TINYINT not null default 0
);
insert into spiele(m1,m2,t1,t2) values('M','D',1,1),('D','M',0,3),('M','S',3,0),('S','M',0,3);

select
sum(
case m1 when 'M' then t1 else t2 end
) punkte
from spiele where
m1='M' or m2='M'
;


die idee hatte ich auch :biggrin::biggrin::biggrin::biggrin:

hmmm mit deiner lösung kann ich leider nichts anfangen. "case" ist mir föllig unbekannt (aber das bedeutet nix. ggf. kommt das im buch noch). das ist dann wohl PL/SQL. das hatte ich noch nicht. :-(
case gibt es auch in SQL (https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CASE-Expressions.html#GUID-CA29B333-572B-4E1D-BA64-851FABDBAE96)

für die punkte aller mannschaften erscheint mir auch Sumpfmolchs antwort #11 die beste zu sein.

SELECT Heiimmannschaft AS Mannschaft, Heimpunkte AS Punkte
from spiel
UNION ALL
SELECT Gastmannschaft, Gastpunkte
from spiel;

dann hab ich das ergebnis von anhang 1.
aber ab wie dann weiter? group by geht nicht (oder ich kanns nicht).

ggf. was mit sum(punkte) where Mannschaft = 'Bayern Münchne'????????? aber wie baue ich das da sein???
der ansatz ist richtig, jetzt nur noch daran erinnern, dass man auch ein sub-query benutzen kann.

select sum(punkte) from(
-- dein union von oben hier
) where Mannschaft = 'Bayern München'

[BattleRoyale]

JAAAAAA!
es ist so simpel......
DANKE!

[BattleRoyale]

ich mal wieder :-)

hätte nun mal eine allgemeine fragen.

wenn ich eine spalte mit dem datentyp "date" festlege

CREATE TABLE xyz (abc date);

dann ist das darumsformat ja standardmäßig "yyyy.mm.dd".

wenn ich jetzt mit insert into diese tabelle befüllen will ABER das datum mit "dd.MM.yyyy" einschreiben will......wie mach ich das?

[Rockhount]

CONVERT(datetime, date_as_string, 104)

siehe
https://www.w3schools.com/sql/func_sqlserver_convert.asp

[Sephiroth]

ich mal wieder :-)

hätte nun mal eine allgemeine fragen.

wenn ich eine spalte mit dem datentyp "date" festlege

CREATE TABLE xyz (abc date);

dann ist das darumsformat ja standardmäßig "yyyy.mm.dd".

wenn ich jetzt mit insert into diese tabelle befüllen will ABER das datum mit "dd.MM.yyyy" einschreiben will......wie mach ich das?
mit to_Date (https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TO_DATE.html#GUID-D226FA7C-F7AD-41A0-BB1D-BD8EF9440118) + das entsprechende format (https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Format-Models.html#GUID-EAB212CF-C525-4ED8-9D3F-C76D08EEBC7A)