human
Schema:
railway (Railway_ID, Railway, Builder, Built, Wheels, Location, ObjectNumber)
train (Train_ID, Train_Num, Name, From, Arrival, Railway_ID)
manager (Manager_ID, Name, Country, Working_year_starts, Age, Level)
railway_manage (Railway_ID, Manager_ID, From_Year)
Please answer with SQL.
assistant
Sure, I understand.
human
Show id and location of railways that are associated with more than one train.
assistant
SELECT T2.Railway_ID , T1.Location FROM railway AS T1 JOIN train AS T2 ON T1.Railway_ID = T2.Railway_ID GROUP BY T2.Railway_ID HAVING COUNT(*) > 1
human
List the object number of railways that do not have any trains.
assistant
SELECT ObjectNumber FROM railway WHERE Railway_ID NOT IN (SELECT Railway_ID FROM train)
human
Show different locations of railways along with the corresponding number of railways at each location.
assistant
SELECT LOCATION , COUNT(*) FROM railway GROUP BY LOCATION
human
List the wheels and locations of the railways.
assistant
SELECT Wheels , LOCATION FROM railway
human
Show the locations that have more than one railways.
assistant
SELECT LOCATION FROM railway GROUP BY LOCATION HAVING COUNT(*) > 1
human
How many railways are there?
assistant
SELECT COUNT( * ) FROM railway ;