Database: railway

CREATE TABLE "railway" (
"Railway_ID" int,
"Railway" text,
"Builder" text,
"Built" text,
"Wheels" text,
"Location" text,
"ObjectNumber" text,
PRIMARY KEY ("Railway_ID")
);
CREATE TABLE "train" (
"Train_ID" int,
"Train_Num" text,
"Name" text,
"From" text,
"Arrival" text,
"Railway_ID" int,
PRIMARY KEY ("Train_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES `railway`("Railway_ID")
);
CREATE TABLE "manager" (
"Manager_ID" int,
"Name" text,
"Country" text,
"Working_year_starts" text,
"Age" int,
"Level" int,
PRIMARY KEY ("Manager_ID")
);
CREATE TABLE "railway_manage" (
"Railway_ID" int,
"Manager_ID" int,
"From_Year" text,
PRIMARY KEY ("Railway_ID","Manager_ID"),
FOREIGN KEY ("Manager_ID") REFERENCES "manager"("Manager_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES "railway"("Railway_ID")
);

Question: How many railways are there?
SQL: SELECT count(*) FROM railway

Question: List the builders of railways in ascending alphabetical order.
SQL: SELECT Builder FROM railway ORDER BY Builder ASC

Question: List the wheels and locations of the railways.
SQL: SELECT Wheels ,  LOCATION FROM railway

Question: What is the maximum level of managers in countries that are not "Australia"?
SQL: SELECT max(LEVEL) FROM manager WHERE Country != "Australia	"

Question: What is the average age for all managers?
SQL: SELECT avg(Age) FROM manager

Question: What are the names of managers in ascending order of level?
SQL: SELECT Name FROM manager ORDER BY LEVEL ASC

Question: What are the names and arrival times of trains?
SQL: SELECT Name ,  Arrival FROM train

Question: What is the name of the oldest manager?
SQL: SELECT Name FROM manager ORDER BY Age DESC LIMIT 1

Question: Show the names of trains and locations of railways they are in.
SQL: SELECT T2.Name ,  T1.Location FROM railway AS T1 JOIN train AS T2 ON T1.Railway_ID  =  T2.Railway_ID

Question: Show the builder of railways associated with the trains named "Andaman Exp".
SQL: SELECT T1.Builder FROM railway AS T1 JOIN train AS T2 ON T1.Railway_ID  =  T2.Railway_ID WHERE T2.Name  =  "Andaman Exp"

Question: Show id and location of railways that are associated with more than one train.
SQL: 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

Question: Show the id and builder of the railway that are associated with the most trains.
SQL: 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

Question: Show different builders of railways, along with the corresponding number of railways using each builder.
SQL: SELECT Builder ,  COUNT(*) FROM railway GROUP BY Builder

Question: Show the most common builder of railways.
SQL: SELECT Builder FROM railway GROUP BY Builder ORDER BY COUNT(*) DESC LIMIT 1

Question: Show different locations of railways along with the corresponding number of railways at each location.
SQL: SELECT LOCATION ,  COUNT(*) FROM railway GROUP BY LOCATION

Question: Show the locations that have more than one railways.
SQL: SELECT LOCATION FROM railway GROUP BY LOCATION HAVING COUNT(*)  >  1

Question: List the object number of railways that do not have any trains.
SQL: SELECT ObjectNumber FROM railway WHERE Railway_ID NOT IN (SELECT Railway_ID FROM train)

Question: Show the countries that have both managers of age above 50 and managers of age below 46.
SQL: SELECT Country FROM manager WHERE Age  >  50 INTERSECT SELECT Country FROM manager WHERE Age  <  46

Question: Show the distinct countries of managers.
SQL: SELECT DISTINCT Country FROM manager

Question: Show the working years of managers in descending order of their level.
SQL: SELECT Working_year_starts FROM manager ORDER BY LEVEL DESC

Question: Show the countries that have managers of age above 50 or below 46.
SQL: SELECT Country FROM manager WHERE Age  >  50 OR Age  <  46