Good evening, guys, with a doubt maybe a beast, but it came in a job test.
For the following two issues, consider the following database:
TIME (id, name, state)
CHAMPIONSHIP (id, name, year_performance)
RESULT (id_time, id_name, points)
Do a SQL query that returns the list of teams that are have qualified for Libertadores in the last 5 years. The first 6 Championship teams qualify for the Libertadores. The results should appear in descending order, sorted by year of completion of the tournament and final classification of the team.
I solved this, however using 5 UNION :
(select * from resultado r where r.id_campeonato = ( select id from campeonato where ano_realizacao = YEAR(now())-2) order by pontos desc limit 6)
union
(select * from resultado r where r.id_campeonato = ( select id from campeonato where ano_realizacao = YEAR(now())-1) order by pontos desc limit 6)
union
(select * from resultado r where r.id_campeonato = ( select id from campeonato where ano_realizacao = YEAR(now())-3) order by pontos desc limit 6)
union
(select * from resultado r where r.id_campeonato = ( select id from campeonato where ano_realizacao = YEAR(now())-4) order by pontos desc limit 6)
union
(select * from resultado r where r.id_campeonato = ( select id from campeonato where ano_realizacao = YEAR(now())-5) order by pontos desc limit 6)
Do a SQL query that returns the number of teams downgraded by state for the past 30 years. The last 4 places of the championship are downgraded every year.
Would you do it without having to put 30 UNION ?