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 the id and builder of the railway that are associated with the most trains.
assistant
SELECT T2.Railway_ID , T1.Builder FROM railway AS T1 JOIN train AS T2 ON T1.Railway_ID = T2.Railway_ID GROUP BY T2.Railway_ID ORDER BY COUNT(*) DESC LIMIT 1
human
List the builders of railways in ascending alphabetical order.
assistant
SELECT Builder FROM railway ORDER BY Builder ASC
human
Show different builders of railways, along with the corresponding number of railways using each builder.
assistant
SELECT Builder , COUNT(*) FROM railway GROUP BY Builder
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 the most common builder of railways.
assistant
SELECT Builder FROM railway GROUP BY Builder ORDER BY COUNT(*) DESC LIMIT 1
human
Show the builder of railways associated with the trains named 'Andaman Exp'.
assistant
SELECT T1.Builder FROM railway AS T1 JOIN train AS T2 ON T1.Railway_ID = T2.Railway_ID WHERE T2.Name = "Andaman Exp" ;