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
How many railways are there?
assistant
SELECT count(*) FROM railway
human
List the wheels and locations of the railways.
assistant
SELECT Wheels , LOCATION FROM railway
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
Show the names of trains and locations of railways they are in.
assistant
SELECT T2.Name , T1.Location FROM railway AS T1 JOIN train AS T2 ON T1.Railway_ID = T2.Railway_ID
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
Show the locations that have more than one railways.
assistant
SELECT LOCATION FROM railway GROUP BY LOCATION HAVING COUNT( * ) > 1 ;