human
Schema:
mountain (Mountain_ID, Name, Height, Prominence, Range, Country)
climber (Climber_ID, Name, Country, Time, Points, Mountain_ID)
Please answer with SQL.
assistant
Sure, I understand.
human
What is the name of the highest mountain?
assistant
SELECT Name FROM mountain ORDER BY Height DESC LIMIT 1
human
Please show the countries and the number of climbers from each country.
assistant
SELECT Country , COUNT(*) FROM climber GROUP BY Country
human
Show the countries that have mountains with height more than 5600 stories and mountains with height less than 5200.
assistant
SELECT Country FROM mountain WHERE Height > 5600 INTERSECT SELECT Country FROM mountain WHERE Height < 5200
human
What are the countries of mountains with height bigger than 5000?
assistant
SELECT Country FROM mountain WHERE Height > 5000
human
How many distinct countries are the climbers from?
assistant
SELECT COUNT(DISTINCT Country) FROM climber
human
Which countries have more than one mountain?
assistant
SELECT Country FROM mountain GROUP BY Country HAVING COUNT( * ) > 1 ;