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
List the names of mountains that do not have any climber.
assistant
SELECT Name FROM mountain WHERE Mountain_ID NOT IN (SELECT Mountain_ID FROM climber)
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
How many distinct countries are the climbers from?
assistant
SELECT COUNT(DISTINCT Country) FROM climber
human
What are the countries of mountains with height bigger than 5000?
assistant
SELECT Country FROM mountain WHERE Height > 5000
human
List the countries that have more than one mountain.
assistant
SELECT Country FROM mountain GROUP BY Country HAVING COUNT( * ) > 1 ;