Com abordar aquest problema

Aquest és un repte final: cada pregunta combina dues o tres tècniques que ja coneixes (funcions de data, càlculs, GROUP BY/HAVING i subconsultes). La clau és descompondre: primer pensa quin valor intermedi necessites (una mitjana, un any...) i després com fer-lo servir dins la consulta principal.

La sintaxi que necessites

SELECT col, expressio
FROM taula t
WHERE col > (SELECT AVG(col) FROM taula WHERE altra_col = t.altra_col)
GROUP BY col_grup
HAVING funcio_agregada(col) > (SELECT funcio_agregada(col) FROM taula)

• CAST(strftime('%Y', data) AS INTEGER): treu l'any d'una data com a número, per poder fer-hi restes.
• Una subconsulta correlacionada fa servir un àlies de la consulta exterior (t.altra_col): es calcula de nou per a cada fila.
• Una subconsulta sense correlació retorna un sol valor global (per exemple, la mitjana de tota la taula).
• HAVING filtra grups després d'agrupar; WHERE filtra files abans d'agrupar.

Pregunta per pregunta

Pregunta 1. Un càlcul fila a fila: no cal agrupar. L'enunciat ja et dona la funció per obtenir l'any; només l'has de fer servir dins d'una resta. Recorda que l'ordre de les columnes compta.

Pregunta 2. Cada empleat es compara amb la mitjana del seu propi càrrec, no amb la mitjana global. Per això la subconsulta ha de saber de quin empleat s'està parlant: dona un àlies a la taula exterior i fes-lo servir dins la subconsulta.

Pregunta 3. Primer agrupa per càrrec i calcula la mitjana de cada grup; després filtra els grups. El valor amb què compares és la mitjana de tota la taula, que obtens amb una subconsulta dins del HAVING. Una funció d'agregació no pot anar al WHERE.

Pregunta 4. Una sola fila amb un sol valor: és una resta entre dues funcions d'agregació aplicades a la mateixa expressió. Pensa quina dona l'any més recent i quina el més antic.

Un exemple semblant

Diferència entre el salari més alt i el més baix de l'empresa:

SELECT MAX(Salary) - MIN(Salary) FROM Employees

Càrrecs amb més empleats que la tercera part de la plantilla (subconsulta dins del HAVING):

SELECT JobTitle, COUNT(*)
FROM Employees
GROUP BY JobTitle
HAVING COUNT(*) > (SELECT COUNT(*) FROM Employees) / 3.0

Subconsulta correlacionada sobre una taula imaginària de productes: els que són més barats que la mitjana de la seva categoria.

SELECT Nom, Preu
FROM Productes p
WHERE Preu < (SELECT AVG(Preu) FROM Productes WHERE Categoria = p.Categoria)

Paranys habituals:

• Comparar amb la mitjana global quan es demana la del grup (o al revés).
• Posar AVG(...) al WHERE: les agregacions van al HAVING o dins d'una subconsulta.
• Restar dates com a text: primer converteix l'any a enter amb CAST.
• Afegir columnes de més: el jutge compara el nombre de columnes.