Com abordar aquest problema

Repte final de la base de dades de cavallers i dracs: cal combinar JOIN (també d'una taula amb ella mateixa), condicions entre columnes de taules diferents, GROUP BY/HAVING i ORDER BY ... LIMIT. Abans d'escriure, decideix quines taules necessites i per quina columna es relacionen (Dragons.KilledBy apunta a Knights.Nickname).

La sintaxi que necessites

SELECT a.col, b.col, COUNT(*), SUM(b.num)
FROM taulaA a
INNER JOIN taulaB b ON b.clau_forana = a.clau
WHERE a.col_x = b.col_y
GROUP BY a.col
HAVING COUNT(*) >= n
ORDER BY SUM(b.num) DESC
LIMIT 1

• Els àlies (a, b) són imprescindibles quan dues taules tenen columnes amb el mateix nom, i obligatoris en una autocombinació (la mateixa taula dues vegades).
• El WHERE pot comparar una columna d'una taula amb una columna de l'altra.
• ORDER BY ... DESC LIMIT 1 es queda amb la fila del valor més gran.

Pregunta per pregunta

Pregunta 1. Uneix dracs i cavallers per la relació "qui el va matar", i després afegeix una condició que compari una data de cada taula. Les dues taules tenen una columna DeathDate: sense àlies, SQLite no sabrà de quina parles.

Pregunta 2. Autocombinació: la taula Knights apareix dues vegades amb dos àlies diferents. La condició de la unió és que comparteixin cognom, i a més cal la condició que l'enunciat suggereix per no obtenir cada parella dues vegades ni un cavaller emparellat amb ell mateix. Prova què passa si fas servir <> en lloc de <.

Pregunta 3. Uneix, agrupa per cavaller i calcula dues agregacions. El filtre "3 o més" s'aplica sobre el recompte de cada grup, així que va al HAVING, no al WHERE.

Pregunta 4. Calcula la suma per cavaller i queda't només amb la més gran: ordena de més gran a més petit i limita el resultat. Comprova que no ordenes pel camp equivocat.

Un exemple semblant

Parelles de dracs del mateix color (autocombinació sobre Dragons):

SELECT a.Name, b.Name
FROM Dragons a
INNER JOIN Dragons b ON a.Color = b.Color AND a.Name < b.Name

Colors amb almenys dos dracs, amb el nombre de dracs i la mitjana de víctimes:

SELECT Color, COUNT(*), AVG(KnownVictims)
FROM Dragons
GROUP BY Color
HAVING COUNT(*) >= 2

El color que té més dracs:

SELECT Color, COUNT(*) FROM Dragons GROUP BY Color ORDER BY COUNT(*) DESC LIMIT 1

Paranys habituals:

• Escriure DeathDate sense àlies quan hi ha dues taules que la tenen.
• En una autocombinació, oblidar la condició que evita parelles repetides o d'un element amb ell mateix.
• Posar un filtre sobre COUNT(*) al WHERE en lloc del HAVING.
• Ordenar en ascendent (ASC, el valor per defecte) quan vols el màxim.