human
Schema:
club (Club_ID, name, Region, Start_year)
club_rank (Rank, Club_ID, Gold, Silver, Bronze, Total)
player (Player_ID, name, Position, Club_ID, Apps, Tries, Goals, Points)
competition (Competition_ID, Year, Competition_type, Country)
competition_result (Competition_ID, Club_ID_1, Club_ID_2, Score)
Please answer with SQL.
assistant
Sure, I understand.
human
List the types of competition and the number of competitions of each type.
assistant
SELECT Competition_type , COUNT(*) FROM competition GROUP BY Competition_type
human
What are the distinct years in which the competitions type is not 'Tournament'?
assistant
SELECT DISTINCT YEAR FROM competition WHERE Competition_type != 'Tournament'
human
Find the countries that have never participated in any competition with Friendly type.
assistant
SELECT country FROM competition EXCEPT SELECT country FROM competition WHERE competition_type = 'Friendly'
human
What are the types and countries of competitions?
assistant
SELECT Competition_type , Country FROM competition
human
which country did participated in the most number of Tournament competitions?
assistant
SELECT country FROM competition WHERE competition_type = 'Tournament' GROUP BY country ORDER BY count(*) DESC LIMIT 1
human
What are the countries that participated in both friendly and tournament type competitions?
assistant
SELECT country FROM competition WHERE competition_type = "Friendly" INTERSECT SELECT country FROM competition WHERE competition_type = "Tournament" ;