SELECT *
FROM Player p1, Player p2
WHERE p1.weight > avg(p2.weight)

Result: misuse of aggregate function avg()

SELECT *
FROM Player
WHERE weight > (SELECT AVG(weight)
                FROM Player)