SQL Anfrage aus www.sqlzoo.net/de/

Computer: Software & Programmierung SQL & Datenbank - Programmierung
1

Hallo ihr Lieben,

da ich bald eine Klausur in Informationssysteme schreibe, in der unter anderem auch SQL abgefragt wird, habe ich mich an den Aufgaben auf der Internetseite:
http://sqlzoo.net/de/3a.htm
versucht. Leider hänge ich an den letzten zwei Aufgaben.

Bei der aufgabe 3b, habe ich folgendes eingegeben:

Select T.song, A.price/count(T.song) as Preis
from album A, track T
group by(T.album)

Mit der group by und having Klausel bin ich noch nicht sehr vertraut. :frowning:
Ich dachte mir group by sucht alle Alben raus und zu jedem Album werden von count(song) die Songs zu den einzelnen Alben gezählt. Diese werden dann nur noch durch den Albumpreis dividiert.
Nun weiß ich aber nicht wo ich die 50 Cent Abfrage einbauen soll. Das preiswerteste Album hätte ich mit „order by Preis ASC“ gesucht, allerdings weiß ich dann nicht wie ich nur das erste Element davon nehme.
Kann mir jemand ein Tipp geben was ich falsch mache?

Bei der letzten Aufgabe (3c) habe ich auch noch Probleme. Als Abfrage habe ich folgendes eingegeben:

select song, count(song) as anzahl
from album, track
where artist = ‚Richard Wagner‘ or artist = ‚Bing Crosby‘
group by (album)
order by anzahl DESC

Auch hier dachte ich mir, dass ich mit „group by album“ erst einmal jedes Album suche, und zu jedem Album, die Songs gezählt werden. wobei die where-Klausel darauf achtet, dass es nur Alben von „Richard Wagner“ oder „Bing Crosby“ sind. „order by DESC“ sorgt schließlich dafür, dass das Album mit den meisten Stücken zuerst ausgegeben wird und dann absteigend sortiert wird.
Aber was mache ich falsch?

Liebe Grüße Matthias

2

Re: SQL Anfrage aus www.sqlzoo.net/de/

  1. beim Group by schreibst Du den Tabellennamen rein, Du musst aber alle Spaltennamen reinschreiben, mit Komma getrennt.
  2. den kleinsten Wert kannst Du mit min(Wert) ermitteln.
3

Re: SQL Anfrage aus www.sqlzoo.net/de/
Hallo Brayn,

Bei der aufgabe 3b, habe ich folgendes eingegeben:

Select T.song, A.price/count(T.song) as Preis
from album A, track T
group by(T.album)

Es fehlt der Join „FROM album JOIN track ON album.asin=track.album“, mit dem die Tabellen korrekt verbunden werden. Ohne diesen Join liefert die Abfrage ein kartesisches Produkt.

Mit der group by und having Klausel bin ich noch nicht sehr
vertraut. :frowning:
Ich dachte mir group by sucht alle Alben raus und zu jedem
Album werden von count(song) die Songs zu den einzelnen Alben
gezählt. Diese werden dann nur noch durch den Albumpreis
dividiert.

Die Idee geht schon in die richtige Richtung. Obiges GROUP BY müsste eigtl. T.song enthalten. Lt. Aufgabenstellung soll aber album.title ausgegeben werden. Der Preis soll auch extra ausgegeben werden und müsste auch ins GROUP BY.

Nun weiß ich aber nicht wo ich die 50 Cent Abfrage einbauen
soll. Das preiswerteste Album hätte ich mit „order by Preis
ASC“ gesucht, allerdings weiß ich dann nicht wie ich nur das
erste Element davon nehme.
Kann mir jemand ein Tipp geben was ich falsch mache?

Die „50 Cent Abfrage“ kann erst erfolgen, wenn die Preise pro Song ermittelt wurden. D.h., das Ergebnis des GROUP BY ist einzuschränken (mittels HAVING).

Die Abfrage sähe dann wie folgt aus:

SELECT title, price, COUNT(song) as Anzahl
 FROM album JOIN track ON asin=album
GROUP BY asin, title, price
HAVING price/COUNT(song) 
album.asin sollte Teil von GROUP BY sein, um jeden Titel eindeutig anzusprechen. Es könnte nämlich sein, dass es mehrere gleichnamige Titel gibt. Diese würden bei einem "GROUP BY title" als ein Titel zusammengefasst. Das wäre natürlich ein Fehler.



> Bei der letzten Aufgabe (3c) habe ich auch noch Probleme. Als  
> Abfrage habe ich folgendes eingegeben:  
>   
> select song, count(song) as anzahl   
> from album, track   
> where artist = 'Richard Wagner' or artist = 'Bing Crosby'  
> group by (album)   
> order by anzahl DESC


Es fehlt auf jeden Fall wieder der Join (s.o.)



> Auch hier dachte ich mir, dass ich mit "group by album" erst  
> einmal jedes Album suche, und zu jedem Album, die Songs  
> gezählt werden. wobei die where-Klausel darauf achtet, dass es  
> nur Alben von "Richard Wagner" oder "Bing Crosby" sind. "order  
> by DESC" sorgt schließlich dafür, dass das Album mit den  
> meisten Stücken zuerst ausgegeben wird und dann absteigend  
> sortiert wird.  
> Aber was mache ich falsch?


Der Hinweis mit Wagner und Crosby irritiert wohl eher, da es bei der Aufgabe um alle Alben geht und Wagner bzw. Crosby nur die ersten beiden der Liste wären.
Es geht auch hier wieder um Alben, so dass album.title anstelle track.song verwendet werden sollte. Ansonsten ist der Ansatz in Ordnung. 

Die Abfrage sähe dann wie folgt aus:


    
    SELECT title, COUNT(asin) as Anzahl
    FROM album JOIN track ON asin=album
    GROUP BY asin, title
    ORDER BY Anzahl DESC



Zu album.asin als Teil von GROUP BY siehe oben.


Peter
4

Re^2: SQL Anfrage aus www.sqlzoo.net/de/
Hallo Peter,

danke für deine ausführlich erklärte Antwort, ich habe aber noch eine Frage.

Mir ist aufgefallen dass viele den Join so schreiben wie du ihn geschrieben hast, also, z.B.:

JOIN track ON asin = album

Wir haben den Join in der Übung aber immer mit:

album.asin = track.album

gemacht. Gibt es da ein Unterschied? Dann habe ich auch schon paar mal was von einem inner Join, right Join und left Join gelesen, ist das alles das gleiche?

Liebe Grüße Matthias

5

Re^3: SQL Anfrage aus www.sqlzoo.net/de/
Hallo Matthias,

prinzipiell sollte das Join-Kriterium in der Form „TabelleA.SpalteX = TabelleB.SpalteY“ angegeben werden. Häufig haben die entspr. Spalten den gleichen Namen, da es sich um Primärschlüssel(spalte) und zugehörige (meist über die Modellierung automatisch generierte) Fremdschlüssel(spalte) handelt. In dem Fall muss auch der Tabellenname angegeben werden, damit SQL das auseinanderhalten kann.

Die Angabe

JOIN track ON asin = album

ist nur deshalb möglich, da die Spalten unterschiedliche Namen haben.

[…] Dann habe ich auch schon
paar mal was von einem inner Join, right Join und left Join
gelesen, ist das alles das gleiche?

Man unterscheidet zwischen Inner-Join und Outer-Join. Beim Inner-Join werden nur die Zeilen ermittelt, bei denen das Join-Kriterium eine Übereinstimmung in beiden Tabellen findet. Der am häufigsten verwendete Fall ist der Vergleich auf Gleichheit (sog. Equi-Join). Aber es sind auch andere Vergleichsoperatoren möglich.
Der Join:

FROM album JOIN track ON album.asin = track.album

lautet vollständig:

FROM album INNER JOIN track ON album.asin = track.album

Das INNER ist optional, da SQL weiss, dass es sich in dem Fall um einen Inner-Join handelt.

Beim Outer-Join werden alle Zeilen einer Tabelle sowie korrespondierende Zeilen der anderen Tabelle ermittelt. Falls es keine korrespondierende Zeilen gibt, werden die entspr. Spaltenwerte mit NULL „aufgefüllt“. Es muss daher zusätzlich angegeben werden, welche Tabelle die sog. dominante Tabelle ist, also die Tabelle, aus der alle Zeilen geliefert werden. Dazu dienen die Klauseln LEFT, RIGHT bzw. FULL.

Beispiel:

FROM album LEFT OUTER JOIN track ON album.asin = track.album

LEFT gibt an, dass die Tabelle links vom Join-Operator die dominante Tabelle ist, also „album“. Der Join liefert demnach alle Alben und zugehörige Tracks, d.h. auch Alben, zu denen es keine Tracks gibt. Solch ein Ergebnis würde beim Inner-Join ausgeblendet werden.
Mittels RIGHT wird die Tabelle rechts vom Join-Operator die dominante Tabelle. Mit FULL werden alle Zeilen beider beteiligten Tabellen geliefert.

Der Join:

FROM album LEFT JOIN track ON album.asin = track.album

lautet vollständig:

FROM album LEFT OUTER JOIN track ON album.asin = track.album

Das OUTER ist optional, da SQL weiss, dass es bei Angabe von LEFT, RIGHT oder FULL um einen Outer-Join handelt.

Peter

1 „Gefällt mir“
6

Re^4: SQL Anfrage aus www.sqlzoo.net/de/
Hallo Peter,

danke für deine tolle Erklärung, du hast mir sehr viel geholfen.

Liebe Grüße Matthias