human
Schema:
wrestler (Wrestler_ID, Name, Reign, Days_held, Location, Event)
Elimination (Elimination_ID, Wrestler_ID, Team, Eliminated_By, Elimination_Move, Time)
Please answer with SQL.
assistant
Sure, I understand.
human
Show times of elimination of wrestlers with days held more than 50.
assistant
SELECT T1.Time FROM elimination AS T1 JOIN wrestler AS T2 ON T1.Wrestler_ID = T2.Wrestler_ID WHERE T2.Days_held > 50
human
List the names of wrestlers and the teams in elimination in descending order of days held.
assistant
SELECT T2.Name , T1.Team FROM elimination AS T1 JOIN wrestler AS T2 ON T1.Wrestler_ID = T2.Wrestler_ID ORDER BY T2.Days_held DESC
human
Show the teams that have both wrestlers eliminated by 'Orton' and wrestlers eliminated by 'Benjamin'.
assistant
SELECT Team FROM Elimination WHERE Eliminated_By = 'Orton' INTERSECT SELECT Team FROM Elimination WHERE Eliminated_By = 'Benjamin'
human
What is the number of distinct teams that suffer elimination?
assistant
SELECT COUNT (DISTINCT team) FROM elimination
human
Show different teams in eliminations and the number of eliminations from each team.
assistant
SELECT Team , COUNT(*) FROM elimination GROUP BY Team
human
Show teams that have suffered more than three eliminations.
assistant
SELECT Team FROM elimination GROUP BY Team HAVING COUNT( * ) > 3 ;