NBAmine/xlangai-spider-test-with-context huggingface dataset:
Database schema:
CREATE TABLE institution (
  Institution_ID NUMBER PRIMARY KEY,
  Name TEXT,
  Team TEXT,
  City TEXT,
  Province TEXT,
  Founded NUMBER,
  Affiliation TEXT,
  Enrollment NUMBER,
  Endowment TEXT,
  Stadium TEXT,
  Capacity NUMBER
);
CREATE TABLE Championship (
  Institution_ID NUMBER PRIMARY KEY,
  Nickname TEXT,
  Joined NUMBER,
  Number_of_Championships NUMBER,
  FOREIGN KEY (Institution_ID) REFERENCES institution(Institution_ID)
);

Question: Give the stadium of the institution which is the greatest enrollment.
SQL: SELECT Stadium FROM institution ORDER BY Enrollment DESC LIMIT 1

Question: List the names of institutions in ascending order of founded year.
SQL: SELECT Name FROM institution ORDER BY Founded ASC

Question: What are the names of institutions, ordered descending by their number of championships?
SQL: SELECT T2.Name FROM championship AS T1 JOIN institution AS T2 ON T1.Institution_ID  =  T2.Institution_ID ORDER BY T1.Number_of_Championships DESC

Question: Return the maximum and minimum enrollment across all institutions.
SQL: SELECT max(Enrollment) ,  min(Enrollment) FROM institution

Question: How many institutions are there?
SQL: SELECT count(*) FROM institution

Question: In which years were more than one institution founded?
SQL: SELECT Founded ,  COUNT(*) FROM institution GROUP BY Founded HAVING COUNT(*)  >  1

Question: How many institutions are there for each type of affiliation?
SQL: SELECT Affiliation ,  COUNT(*) FROM institution GROUP BY Affiliation

Question: Return the affiliations of instituions that are not in the city of Vancouver.
SQL: SELECT Affiliation FROM institution WHERE City != "Vancouver"
Database schema:
CREATE TABLE district (
  District_ID NUMBER PRIMARY KEY,
  Name TEXT,
  Area_km NUMBER,
  Population NUMBER,
  Density_km NUMBER,
  Government_website TEXT
);
CREATE TABLE spokesman (
  Spokesman_ID NUMBER PRIMARY KEY,
  Name TEXT,
  Age NUMBER,
  Speach_title TEXT,
  Rank_position NUMBER,
  Points NUMBER
);
CREATE TABLE spokesman_district (
  Spokesman_ID NUMBER PRIMARY KEY,
  District_ID NUMBER,
  Start_year TEXT,
  FOREIGN KEY (District_ID) REFERENCES district(District_ID),
  FOREIGN KEY (Spokesman_ID) REFERENCES spokesman(Spokesman_ID)
);

Question: Find the number of districts which have no spokesmen.
SQL: SELECT count(*) FROM district WHERE district_id NOT IN (SELECT district_id FROM spokesman_district)

Question: List the ids, names, and government websites of all districts sorted by population.
SQL: SELECT name ,  Government_website ,  district_id FROM district ORDER BY Population

Question: Find the names of the districts which have had both spokesman with rank position 1 and 2.
SQL: SELECT t3.name FROM spokesman AS t1 JOIN spokesman_district AS t2 ON t1.Spokesman_ID  =  t2.Spokesman_ID JOIN district AS t3 ON t3.district_id  =  t2.district_id WHERE t1.rank_position  =  1 INTERSECT SELECT t3.name FROM spokesman AS t1 JOIN spokesman_district AS t2 ON t1.Spokesman_ID  =  t2.Spokesman_ID JOIN district AS t3 ON t3.district_id  =  t2.district_id WHERE t1.rank_position  =  2

Question: Find the names of districts whose government links use a 'gov' domain.
SQL: SELECT name FROM district WHERE Government_website LIKE "%gov%"

Question: Who is the oldest spokesman?
SQL: SELECT name FROM spokesman ORDER BY age DESC LIMIT 1

Question: What is the total population of the districts whose areas are in the top 3?
SQL: SELECT sum(population) FROM district ORDER BY area_km DESC LIMIT 3
Database schema:
CREATE TABLE building (
  Building_ID NUMBER PRIMARY KEY,
  Region_ID NUMBER,
  Name TEXT,
  Address TEXT,
  Number_of_Stories NUMBER,
  Completed_Year NUMBER,
  FOREIGN KEY (Region_ID) REFERENCES region(Region_ID)
);
CREATE TABLE region (
  Region_ID NUMBER PRIMARY KEY,
  Name TEXT,
  Capital TEXT,
  Area NUMBER,
  Population NUMBER
);

Question: Show the completed years shared by buildings with more than 20 stories and buildings with less than 15 stories.
SQL: SELECT Completed_Year FROM building WHERE Number_of_Stories  >  20 INTERSECT SELECT Completed_Year FROM building WHERE Number_of_Stories  <  15

Question: Show addresses of buildings and the capitals of regions they are in.
SQL: SELECT T1.Address ,  T2.Capital FROM building AS T1 JOIN region AS T2 ON T1.Region_ID  =  T2.Region_ID

Question: In which year did the most building constructions complete?
SQL: SELECT Completed_Year FROM building GROUP BY Completed_Year ORDER BY COUNT(*) DESC LIMIT 1

Question: Sort buildings in descending order of the number of stories, and return their completion years.
SQL: SELECT Completed_Year FROM building ORDER BY Number_of_Stories DESC

Question: What are the names of the 5 largest regions in terms of area?
SQL: SELECT Name FROM region ORDER BY Area DESC LIMIT 5

Question: What is the list of building names, sorted by the number of stories of each building in ascending order?
SQL: SELECT Name FROM building ORDER BY Number_of_Stories ASC

Question: Which region has the largest population? Give me the capital of the region.
SQL: SELECT Capital FROM region ORDER BY Population DESC LIMIT 1

Question: In which years did both buildings with more than 20 stories and buildings with less than 15 stories were completed?
SQL: SELECT Completed_Year FROM building WHERE Number_of_Stories  >  20 INTERSECT SELECT Completed_Year FROM building WHERE Number_of_Stories  <  15

Question: List the addresses of buildings in descending order of building completion year.
SQL: SELECT Address FROM building ORDER BY Completed_Year DESC

Question: What are the names of regions in which there are no buildings?
SQL: SELECT Name FROM region WHERE Region_ID NOT IN (SELECT Region_ID FROM building)

Question: How many buildings are there?
SQL: SELECT count(*) FROM building

Question: Return the number of stories for each building in the region named "Abruzzo".
SQL: SELECT T1.Number_of_Stories FROM building AS T1 JOIN region AS T2 ON T1.Region_ID  =  T2.Region_ID WHERE T2.Name  =  "Abruzzo"

Question: Among the buildings not completed in 1980, what is the maximum number of stories?
SQL: SELECT max(Number_of_Stories) FROM building WHERE Completed_Year != "1980"
Database schema:
CREATE TABLE customer (
  Customer_ID NUMBER PRIMARY KEY,
  Name TEXT,
  Nationality TEXT,
  Card_Credit NUMBER,
  Level_of_Membership NUMBER
);
CREATE TABLE branch (
  Branch_ID NUMBER PRIMARY KEY,
  Manager TEXT,
  Years_opened NUMBER,
  Location_of_office TEXT
);
CREATE TABLE customer_order (
  Customer_ID NUMBER PRIMARY KEY,
  Branch_ID NUMBER,
  Dish_Name TEXT,
  Quantity NUMBER,
  FOREIGN KEY (Branch_ID) REFERENCES branch(Branch_ID),
  FOREIGN KEY (Customer_ID) REFERENCES customer(Customer_ID)
);

Question: Find the customer with the highest membership level and return his or her card credit.
SQL: SELECT Card_Credit FROM customer ORDER BY Level_of_Membership DESC LIMIT 1

Question: Show different nationalities of customers, along with the number of customers of each nationality.
SQL: SELECT Nationality ,  COUNT(*) FROM customer GROUP BY Nationality

Question: What is the total quantities of dishes ordered by each customer ? List the customer name and the total quantity .
SQL: select t1.name ,  sum(t2.quantity) from customer as t1 join customer_order as t2 on t1.customer_id  =  t2.customer_id group by t1.name

Question: For each order, return the customer name and the dish name.
SQL: SELECT T1.Name ,  T2.Dish_Name FROM customer AS T1 JOIN customer_order AS T2 ON T1.Customer_ID  =  T2.Customer_ID

Question: Which customers do not have any order? Give me the customer names.
SQL: SELECT name FROM customer WHERE Customer_ID NOT IN (SELECT Customer_ID FROM customer_order)

Question: Find the average card credit customers whose membership level is above 1.
SQL: SELECT avg(Card_Credit) FROM customer WHERE Level_of_Membership  >  1

Question: Show distinct managers of branches.
SQL: SELECT DISTINCT Manager FROM branch
Database schema:
CREATE TABLE languages (
  id NUMBER PRIMARY KEY,
  name TEXT
);
CREATE TABLE countries (
  id NUMBER PRIMARY KEY,
  name TEXT,
  overall_score NUMBER,
  justice_score NUMBER,
  health_score NUMBER,
  education_score NUMBER,
  economics_score NUMBER,
  politics_score NUMBER
);
CREATE TABLE official_languages (
  language_id NUMBER PRIMARY KEY,
  country_id NUMBER,
  FOREIGN KEY (country_id) REFERENCES countries(id),
  FOREIGN KEY (language_id) REFERENCES languages(id)
);

Question: Show the average overall scores of countries whose official language is "English".
SQL: SELECT avg(T1.overall_score) FROM countries AS T1 JOIN official_languages AS T2 ON T1.id  =  T2.country_id JOIN languages AS T3 ON T2.language_id  =  T3.id WHERE T3.name  =  "English"

Question: What is the name of the country with the highest politics score?
SQL: SELECT name FROM countries ORDER BY politics_score DESC LIMIT 1

Question: Show the official languages sorted in descending order by the average overall scores among countries speaking them.
SQL: SELECT T3.name FROM countries AS T1 JOIN official_languages AS T2 ON T1.id  =  T2.country_id JOIN languages AS T3 ON T2.language_id  =  T3.id GROUP BY T3.id ORDER BY avg(T1.overall_score) DESC

Question: What are the maximum and minimum health scores among countries that are not "Norway".
SQL: SELECT max(health_score) ,  min(health_score) FROM countries WHERE name != "Norway"

Question: How many different official languages are there?
SQL: SELECT count(DISTINCT language_id) FROM official_languages

Question: Show the names of languages that are the official language for both countries with overall score greater than 95 and countries with overall score less than than 90.
SQL: SELECT T3.name FROM countries AS T1 JOIN official_languages AS T2 ON T1.id  =  T2.country_id JOIN languages AS T3 ON T2.language_id  =  T3.id WHERE T1.overall_score  >  95 INTERSECT SELECT T3.name FROM countries AS T1 JOIN official_languages AS T2 ON T1.id  =  T2.country_id JOIN languages AS T3 ON T2.language_id  =  T3.id WHERE T1.overall_score  <  90

Question: What are the names of the countries, ordered descending by education score?
SQL: SELECT name FROM countries ORDER BY education_score DESC
Database schema:
CREATE TABLE book (
  Book_ID NUMBER PRIMARY KEY,
  Title TEXT,
  Type TEXT,
  Pages NUMBER,
  Chapters NUMBER,
  Audio TEXT,
  Release TEXT
);
CREATE TABLE review (
  Review_ID NUMBER PRIMARY KEY,
  Book_ID NUMBER,
  Rating NUMBER,
  Readers_in_Million NUMBER,
  Rank NUMBER,
  FOREIGN KEY (Book_ID) REFERENCES book(Book_ID)
);

Question: What are the titles of books that are not "Poet"?
SQL: SELECT Title FROM book WHERE TYPE != "Poet"

Question: List the titles of books in descending order of pages.
SQL: SELECT Title FROM book ORDER BY Pages DESC

Question: What are the type and title of the books that are not rated?
SQL: SELECT TYPE ,  title FROM book EXCEPT SELECT T1.type ,  T1.title FROM book AS T1 JOIN review AS T2 ON T1.Book_ID  =  T2.Book_ID;

Question: What is the rank of the book with the smallest number of pages?
SQL: SELECT T2.Rank FROM book AS T1 JOIN review AS T2 ON T1.Book_ID  =  T2.Book_ID ORDER BY T1.Pages ASC LIMIT 1
Database schema:
CREATE TABLE headphone (
  Headphone_ID NUMBER PRIMARY KEY,
  Model TEXT,
  Class TEXT,
  Driver-matched_dB NUMBER,
  Construction TEXT,
  Earpads TEXT,
  Price NUMBER
);
CREATE TABLE store (
  Store_ID NUMBER PRIMARY KEY,
  Name TEXT,
  Neighborhood TEXT,
  Parking TEXT,
  Date_Opened TEXT
);
CREATE TABLE stock (
  Store_ID NUMBER PRIMARY KEY,
  Headphone_ID NUMBER,
  Quantity NUMBER,
  FOREIGN KEY (Headphone_ID) REFERENCES headphone(Headphone_ID),
  FOREIGN KEY (Store_ID) REFERENCES store(Store_ID)
);

Question: Find all earpads that do not use plastic construction.
SQL: SELECT earpads FROM headphone EXCEPT SELECT earpads FROM headphone WHERE construction  =  'Plastic'

Question: Which earpads never use plastic construction?
SQL: SELECT earpads FROM headphone EXCEPT SELECT earpads FROM headphone WHERE construction  =  'Plastic'

Question: Find the headphone classes that contain both headphones using "Bowls" earpads and headphones using "Comfort Pads" earpads.
SQL: SELECT CLASS FROM headphone WHERE earpads  =  'Bowls' INTERSECT SELECT CLASS FROM headphone WHERE earpads  =  'Comfort Pads'

Question: Which headphone models do not have any stock in any store?
SQL: SELECT model FROM headphone WHERE headphone_id NOT IN (SELECT headphone_id FROM stock)

Question: Count the number of different earpads.
SQL: SELECT count(DISTINCT earpads) FROM headphone

Question: Give me a list of store names, sorted by store open date.
SQL: SELECT name FROM store ORDER BY date_opened

Question: Find the headphone model whose total quantity in stock is the largest.
SQL: SELECT t1.model FROM headphone AS t1 JOIN stock AS t2 ON t1.headphone_id  =  t2.headphone_id GROUP BY t1.model ORDER BY sum(t2.quantity) DESC LIMIT 1

Question: Which headphone classes have both headphones with "Bowls" and headphones with "Comfort Pads" earpads?
SQL: SELECT CLASS FROM headphone WHERE earpads  =  'Bowls' INTERSECT SELECT CLASS FROM headphone WHERE earpads  =  'Comfort Pads'

Question: Which stores are located in the "Tarzana" neighborhood? Return their names and parking information.
SQL: SELECT name ,  parking FROM store WHERE neighborhood  =  'Tarzana'

Question: Find the headphone models whose price is below the average price.
SQL: SELECT model FROM headphone WHERE price  <  (SELECT avg(price) FROM headphone)

Question: Which stores do not have any headphones in stock? Give me the store names.
SQL: SELECT name FROM store WHERE store_id NOT IN (SELECT store_id FROM stock)
Database schema:
CREATE TABLE country (
  Country_Id NUMBER PRIMARY KEY,
  Country TEXT,
  Capital TEXT,
  Official_native_language TEXT,
  Regoin TEXT
);
CREATE TABLE team (
  Team_ID NUMBER PRIMARY KEY,
  Team TEXT,
  Make TEXT,
  Manager TEXT,
  Sponsor TEXT,
  Car_Owner TEXT
);
CREATE TABLE driver (
  Driver_ID NUMBER PRIMARY KEY,
  Driver TEXT,
  Country NUMBER,
  Age NUMBER,
  Car_# NUMBER,
  Make TEXT,
  Points TEXT,
  Laps NUMBER,
  Winnings TEXT
);
CREATE TABLE team_driver (
  Team_ID NUMBER PRIMARY KEY,
  Driver_ID NUMBER,
  FOREIGN KEY (Driver_ID) REFERENCES driver(Driver_ID),
  FOREIGN KEY (Team_ID) REFERENCES team(Team_ID)
);

Question: Find the number of drivers whose points are greater than 150 for each make.
SQL: SELECT make ,  count(*) FROM driver WHERE points  >  150 GROUP BY make

Question: Find the team with two or more drivers and return the the manager and car owner of the team.
SQL: SELECT t1.manager ,  t1.car_owner FROM team AS t1 JOIN team_driver AS t2 ON t1.team_id  =  t2.team_id GROUP BY t2.team_id HAVING count(*)  >=  2

Question: How many drivers are there?
SQL: SELECT count(*) FROM driver

Question: Which countries do not have any drivers?
SQL: SELECT country FROM country WHERE country_id NOT IN (SELECT country FROM driver)

Question: Find the make of the team whose car owner is "Buddy Arrington".
SQL: SELECT Make FROM team WHERE Car_Owner  =  "Buddy Arrington"

Question: Which make has more than one team?
SQL: SELECT make FROM team GROUP BY team HAVING count(*)  >  1
Database schema:
CREATE TABLE author (
  Author_ID NUMBER PRIMARY KEY,
  Name TEXT,
  Age NUMBER,
  Gender TEXT
);
CREATE TABLE press (
  Press_ID NUMBER PRIMARY KEY,
  Name TEXT,
  Month_Profits_billion NUMBER,
  Year_Profits_billion NUMBER
);
CREATE TABLE book (
  Book_ID NUMBER PRIMARY KEY,
  Title TEXT,
  Book_Series TEXT,
  Author_ID NUMBER,
  Press_ID NUMBER,
  Sale_Amount TEXT,
  Release_date TEXT,
  FOREIGN KEY (Press_ID) REFERENCES press(Press_ID),
  FOREIGN KEY (Author_ID) REFERENCES author(Author_ID)
);

Question: list all the names of press in descending order of the profit of the year.
SQL: SELECT name FROM press ORDER BY Year_Profits_billion DESC

Question: What are the names of the publishers that made more than 15 billion profits each year or 1 billion each month?
SQL: SELECT name FROM press WHERE Year_Profits_billion  >  15 OR Month_Profits_billion  >  1

Question: Find the title, author name, and publisher name for the top 3 best sales books.
SQL: SELECT t1.name ,  t2.title ,  t3.name FROM author AS t1 JOIN book AS t2 ON t1.author_id  =  t2.author_id JOIN press AS t3 ON t2.press_id  =  t3.press_id ORDER BY t2.sale_amount DESC LIMIT 3

Question: Find the titles and publish dates of the top 5 best sale books.
SQL: SELECT title ,  release_date FROM book ORDER BY sale_amount DESC LIMIT 5

Question: What is the name of the author of best selling book?
SQL: SELECT t1.name FROM author AS t1 JOIN book AS t2 ON t1.author_id  =  t2.author_id ORDER BY t2.sale_amount DESC LIMIT 1

Question: How many books does each book series have? Return the counts and book series.
SQL: SELECT count(*) ,  book_series FROM book GROUP BY book_series

Question: What are the name and total book sale amount of each press?
SQL: SELECT sum(t1.sale_amount) ,  t2.name FROM book AS t1 JOIN press AS t2 ON t1.press_id  =  t2.press_id GROUP BY t1.press_id

Question: Which authors publish books in both "MM" and "LT" series? Give me the author names.
SQL: SELECT t1.name FROM author AS t1 JOIN book AS t2 ON t1.author_id  =  t2.author_id WHERE t2.book_series  =  'MM' INTERSECT SELECT t1.name FROM author AS t1 JOIN book AS t2 ON t1.author_id  =  t2.author_id WHERE t2.book_series  =  'LT'

Question: What are the names of the press that makes the highest monthly profit or the lowest monthly profit?
SQL: SELECT name FROM press WHERE Month_Profits_billion  =  (SELECT min(Month_Profits_billion) FROM press) OR Month_Profits_billion  =  (SELECT max(Month_Profits_billion) FROM press)

Question: Find the name and total book sale amount of each press.
SQL: SELECT sum(t1.sale_amount) ,  t2.name FROM book AS t1 JOIN press AS t2 ON t1.press_id  =  t2.press_id GROUP BY t1.press_id

Question: List all book titles in the order of their release date from the most recent to the past.
SQL: SELECT title FROM book ORDER BY release_date DESC

Question: Find the number of books that are sold more than 1000 for each publisher. List the press name as well.
SQL: SELECT count(*) ,  t2.name FROM book AS t1 JOIN press AS t2 ON t1.press_id  =  t2.press_id WHERE sale_amount  >  1000 GROUP BY t2.name
Database schema:
CREATE TABLE Client (
  IdClient TEXT PRIMARY KEY,
  Name TEXT,
  Address TEXT,
  NumCC TEXT
);
CREATE TABLE Orders (
  IdOrder TEXT PRIMARY KEY,
  IdClient TEXT,
  DateOrder TIME,
  DateExped TIME
);
CREATE TABLE Author (
  idAuthor NUMBER PRIMARY KEY,
  Name TEXT
);
CREATE TABLE Book (
  ISBN TEXT PRIMARY KEY,
  Title TEXT,
  Author TEXT,
  PurchasePrice NUMBER,
  SalePrice NUMBER
);
CREATE TABLE Author_Book (
  ISBN TEXT PRIMARY KEY,
  Author NUMBER,
  FOREIGN KEY (ISBN) REFERENCES Book(ISBN)
);
CREATE TABLE Books_Order (
  ISBN TEXT PRIMARY KEY,
  IdOrder TEXT,
  amount NUMBER,
  FOREIGN KEY (IdOrder) REFERENCES Orders(IdOrder),
  FOREIGN KEY (ISBN) REFERENCES Book(ISBN)
);

Question: Show the book title and purchase price of the book that has had the greatest amount in orders.
SQL: SELECT T2.title ,  T2.PurchasePrice FROM Books_Order AS T1 JOIN BOOk AS T2 ON T1.isbn  =  T2.isbn GROUP BY T1.isbn ORDER BY sum(amount) DESC LIMIT 1

Question: What are the order ids for orders that include both Pride and Prejudice and The Little Prince?
SQL: SELECT idOrder FROM Book AS T1 JOIN Books_Order AS T2 ON T1.isbn  =  T2.isbn WHERE T1.title  =  "Pride and Prejudice" INTERSECT SELECT idOrder FROM Book AS T1 JOIN Books_Order AS T2 ON T1.isbn  =  T2.isbn WHERE T1.title  =  "The Little Prince"

Question: Show all client names who have orders for "Pride and Prejudice".
SQL: SELECT T3.name FROM Orders AS T1 JOIN Books_Order AS T2 ON T1.idOrder  =  T2.idOrder JOIN Client AS T3 ON T1.idClient  =  T3.idClient JOIN Book AS T4 ON T4.isbn  =  T2.isbn WHERE T4.title  =  "Pride and Prejudice"

Question: Count the number of books.
SQL: SELECT count(*) FROM Book

Question: What are the names and addressed of all clients?
SQL: SELECT name ,  address FROM Client

Question: How many books do we have?
SQL: SELECT count(*) FROM Book

Question: What are the titles of books with the highest purchase price across all books ?
SQL: select title from book order by purchaseprice  desc limit 1

Question: Give the maximum and minimum sale price of books.
SQL: SELECT max(saleprice) ,  min(saleprice) FROM Book
Database schema:
CREATE TABLE Customers (
  id NUMBER PRIMARY KEY,
  name TEXT,
  age NUMBER,
  membership_credit NUMBER
);
CREATE TABLE Discount (
  id NUMBER PRIMARY KEY,
  name TEXT,
  membership_credit NUMBER
);
CREATE TABLE Vehicles (
  id NUMBER PRIMARY KEY,
  name TEXT,
  Model_year NUMBER,
  Type_of_powertrain TEXT,
  Combined_fuel_economy_rate NUMBER,
  City_fuel_economy_rate NUMBER,
  Highway_fuel_economy_rate NUMBER,
  Cost_per_25_miles NUMBER,
  Annual_fuel_cost NUMBER,
  Notes TEXT
);
CREATE TABLE Renting_history (
  id NUMBER PRIMARY KEY,
  customer_id NUMBER,
  discount_id NUMBER,
  vehicles_id NUMBER,
  total_hours NUMBER,
  FOREIGN KEY (discount_id) REFERENCES Discount(id),
  FOREIGN KEY (vehicles_id) REFERENCES Vehicles(id),
  FOREIGN KEY (customer_id) REFERENCES Customers(id)
);

Question: Show names for all vehicles in descending order of model year.
SQL: SELECT name FROM vehicles ORDER BY model_year DESC

Question: Count the number of vehicles.
SQL: SELECT count(*) FROM vehicles

Question: What is the average age for customers with a membership credit above the average?
SQL: SELECT avg(age) FROM customers WHERE membership_credit  >  (SELECT avg(membership_credit) FROM customers)

Question: List all distinct types of powertrain of vehicles.
SQL: SELECT DISTINCT type_of_powertrain FROM vehicles

Question: What are the different types of powertrains?
SQL: SELECT DISTINCT type_of_powertrain FROM vehicles

Question: Show the name of vehicles with no renting history.
SQL: SELECT name FROM vehicles WHERE id NOT IN (SELECT vehicles_id FROM renting_history)

Question: Show all information for all discounts.
SQL: SELECT * FROM discount

Question: What are the names, ages, and membership credits for all customers?
SQL: SELECT name ,  age ,  membership_credit FROM customers

Question: What are the names and model years for vehicles that have a city fuel economy rate less than or equal to its highway fuel economy rate?
SQL: SELECT name ,  model_year FROM vehicles WHERE city_fuel_economy_rate  <=  highway_fuel_economy_rate

Question: Show the name, age, membership credit for all customers?
SQL: SELECT name ,  age ,  membership_credit FROM customers
Database schema:
CREATE TABLE vehicle (
  Vehicle_ID NUMBER PRIMARY KEY,
  Model TEXT,
  Build_Year TEXT,
  Top_Speed NUMBER,
  Power NUMBER,
  Builder TEXT,
  Total_Production TEXT
);
CREATE TABLE driver (
  Driver_ID NUMBER PRIMARY KEY,
  Name TEXT,
  Citizenship TEXT,
  Racing_Series TEXT
);
CREATE TABLE vehicle_driver (
  Driver_ID NUMBER PRIMARY KEY,
  Vehicle_ID NUMBER,
  FOREIGN KEY (Vehicle_ID) REFERENCES vehicle(Vehicle_ID),
  FOREIGN KEY (Driver_ID) REFERENCES driver(Driver_ID)
);

Question: Show all driver names in the alphabetical order.
SQL: SELECT name FROM driver ORDER BY name

Question: Return the average top speed across all vehicles.
SQL: SELECT avg(top_speed) FROM vehicle

Question: Return the names of drivers with citizenship from the United States.
SQL: SELECT name FROM driver WHERE citizenship  =  'United States'

Question: Which car models have total production larger than 100 or top speed higher than 150?
SQL: SELECT model FROM vehicle WHERE total_production  >  100 OR top_speed  >  150

Question: Return the names of drivers who have driven vehicles with power over 5000.
SQL: SELECT DISTINCT T1.Name FROM driver AS T1 JOIN vehicle_driver AS T2 ON T1.driver_id  =  T2.driver_id JOIN vehicle AS T3 ON T2.vehicle_id  =  T3.vehicle_id WHERE T3.power  >  5000

Question: How many drivers have driven vehicles built in 2012?
SQL: SELECT count(DISTINCT T1.driver_id) FROM vehicle_driver AS T1 JOIN vehicle AS T2 ON T1.vehicle_id  =  T2.vehicle_id WHERE T2.build_year  =  2012

Question: What are the model names and build year of the cars with 'DJ' in its model name?
SQL: SELECT model ,  build_year FROM vehicle WHERE model LIKE '%DJ%'

Question: What is the model of the vehicle with maximum top speed whose power is higher than 6000?
SQL: SELECT model FROM vehicle WHERE power  >  6000 ORDER BY top_speed DESC LIMIT 1
Database schema:
CREATE TABLE club (
  Club_ID NUMBER PRIMARY KEY,
  Name TEXT,
  Manager TEXT,
  Captain TEXT,
  Manufacturer TEXT,
  Sponsor TEXT
);
CREATE TABLE player (
  Player_ID NUMBER PRIMARY KEY,
  Name TEXT,
  Country TEXT,
  Earnings NUMBER,
  Events_number NUMBER,
  Wins_count NUMBER,
  Club_ID NUMBER,
  FOREIGN KEY (Club_ID) REFERENCES club(Club_ID)
);

Question: How many clubs are there?
SQL: SELECT count(*) FROM club

Question: From which countries are players who make more than 1200000 from?
SQL: SELECT DISTINCT Country FROM player WHERE Earnings  >  1200000

Question: Return the managers and captains of all clubs.
SQL: SELECT Manager ,  Captain FROM club

Question: What are the earnings of players from either of the countries of Australia or Zimbabwe?
SQL: SELECT Earnings FROM player WHERE Country  =  "Australia" OR Country  =  "Zimbabwe"

Question: Show names of clubs in descending order of average earnings of players belonging.
SQL: SELECT T1.Name FROM club AS T1 JOIN player AS T2 ON T1.Club_ID  =  T2.Club_ID GROUP BY T1.Club_ID ORDER BY avg(T2.Earnings) DESC

Question: Please show the most common manufacturer of clubs.
SQL: SELECT Manufacturer FROM club GROUP BY Manufacturer ORDER BY COUNT(*) DESC LIMIT 1

Question: Show names of players from the club with manager "Sam Allardyce".
SQL: SELECT T2.Name FROM club AS T1 JOIN player AS T2 ON T1.Club_ID  =  T2.Club_ID WHERE T1.Manager  =  "Sam Allardyce"

Question: Return the names of players in order of count of wins, ascending.
SQL: SELECT Name FROM player ORDER BY Wins_count ASC

Question: Of players who have more than 2 wins, what is the country of the player who makes the most?
SQL: SELECT Country FROM player WHERE Wins_count  >  2 ORDER BY Earnings DESC LIMIT 1
Database schema:
CREATE TABLE Student (
  StuID NUMBER PRIMARY KEY,
  LName TEXT,
  Fname TEXT,
  Age NUMBER,
  Sex TEXT,
  Major NUMBER,
  Advisor NUMBER,
  city_code TEXT,
  FOREIGN KEY (city_code) REFERENCES City(city_code)
);
CREATE TABLE Direct_distance (
  city1_code TEXT,
  city2_code TEXT,
  distance NUMBER,
  FOREIGN KEY (city2_code) REFERENCES City(city_code),
  FOREIGN KEY (city1_code) REFERENCES City(city_code)
);
CREATE TABLE City (
  city_code TEXT PRIMARY KEY,
  city_name TEXT,
  state TEXT,
  country TEXT,
  latitude NUMBER,
  longitude NUMBER
);

Question: Which state has most number of cities?
SQL: SELECT state FROM City GROUP BY state ORDER BY count(*) DESC LIMIT 1

Question: Which country has fewest number of cities?
SQL: SELECT country FROM City GROUP BY country ORDER BY count(*) ASC LIMIT 1

Question: Show me the city code of two cities with a distance less than 1000.
SQL: SELECT city1_code ,  city2_code FROM Direct_distance WHERE distance  <  1000

Question: What the the student ids for students not living in the USA?
SQL: SELECT StuID FROM Student EXCEPT SELECT StuID FROM City AS T1 JOIN Student AS T2 ON T1.city_code  =  T2.city_code WHERE country  =  "USA"

Question: Show names for all USA city ordered by latitude.
SQL: SELECT city_name FROM City WHERE country  =  "USA" ORDER BY latitude

Question: How many cities are in Canada?
SQL: SELECT count(*) FROM City WHERE country  =  "CANADA"

Question: What are the names of cities with at least three students?
SQL: SELECT T1.city_name FROM City AS T1 JOIN Student AS T2 ON T1.city_code  =  T2.city_code GROUP BY T1.city_code HAVING count(*)  >=  3

Question: Show the first name and the last name for students living in state MD.
SQL: SELECT T2.Fname ,  T2.Lname FROM City AS T1 JOIN Student AS T2 ON T1.city_code  =  T2.city_code WHERE T1.state  =  "MD"

Question: Give the average, minimum, and maximum distances between two cities.
SQL: SELECT avg(distance) ,  min(distance) ,  max(distance) FROM Direct_distance

Question: What latitude and longitude correspond to Baltimore?
SQL: SELECT latitude ,  longitude FROM City WHERE city_name  =  "Baltimore"

Question: Show ids for all female (sex is F) students living in state PA.
SQL: SELECT StuID FROM City AS T1 JOIN Student AS T2 ON T1.city_code  =  T2.city_code WHERE T1.state  =  "PA"  AND T2.sex  =  'F'

Question: How many cities are in each state?
SQL: SELECT state ,  count(*) FROM City GROUP BY state

Question: Show all city codes and the total distance to all other cities.
SQL: SELECT city1_code ,  sum(distance) FROM Direct_distance GROUP BY city1_code

Question: Count the number of cities.
SQL: SELECT count(*) FROM City

Question: Give the country with the fewest number of cities.
SQL: SELECT country FROM City GROUP BY country ORDER BY count(*) ASC LIMIT 1

Question: Show names for all cities where at least three students live.
SQL: SELECT T1.city_name FROM City AS T1 JOIN Student AS T2 ON T1.city_code  =  T2.city_code GROUP BY T1.city_code HAVING count(*)  >=  3

Question: How many countries do we have?
SQL: SELECT count(DISTINCT country) FROM City
Database schema:
CREATE TABLE city_channel (
  ID NUMBER PRIMARY KEY,
  City TEXT,
  Station_name TEXT,
  Owned_Since NUMBER,
  Affiliation TEXT
);
CREATE TABLE radio (
  Radio_ID NUMBER PRIMARY KEY,
  Transmitter TEXT,
  Radio_MHz TEXT,
  2FM_MHz TEXT,
  RnaG_MHz TEXT,
  Lyric_FM_MHz TEXT,
  ERP_kW TEXT
);
CREATE TABLE tv_show (
  tv_show_ID NUMBER PRIMARY KEY,
  tv_show_name TEXT,
  Sub_tittle TEXT,
  Next_show_name TEXT,
  Original_Airdate TEXT
);
CREATE TABLE city_channel_radio (
  City_channel_ID NUMBER PRIMARY KEY,
  Radio_ID NUMBER,
  Is_online OTHERS,
  FOREIGN KEY (Radio_ID) REFERENCES radio(Radio_ID),
  FOREIGN KEY (City_channel_ID) REFERENCES city_channel(ID)
);
CREATE TABLE city_channel_tv_show (
  City_channel_ID NUMBER PRIMARY KEY,
  tv_show_ID NUMBER,
  Is_online OTHERS,
  Is_free OTHERS,
  FOREIGN KEY (tv_show_ID) REFERENCES tv_show(tv_show_ID),
  FOREIGN KEY (City_channel_ID) REFERENCES city_channel(ID)
);

Question: What are the names and original air dates of tv shows?
SQL: SELECT tv_show_name ,  Original_Airdate FROM tv_show

Question: List the transmitters of radios in ascending order of erp kw .
SQL: select transmitter from radio order by erp_kw asc
Database schema:
CREATE TABLE member (
  Member_ID NUMBER PRIMARY KEY,
  Name TEXT,
  Nationality TEXT,
  Age NUMBER
);
CREATE TABLE club (
  Club_ID NUMBER PRIMARY KEY,
  Overall_Ranking NUMBER,
  Team_Leader TEXT,
  Club_Name TEXT
);
CREATE TABLE club_leader (
  Club_ID NUMBER PRIMARY KEY,
  Member_ID NUMBER,
  Year_Join TEXT,
  FOREIGN KEY (Member_ID) REFERENCES member(Member_ID),
  FOREIGN KEY (Club_ID) REFERENCES club(Club_ID)
);

Question: Which club name contains the string 'state'?
SQL: SELECT club_name FROM club WHERE club_name LIKE '%state%'

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

Question: How many members are there?
SQL: SELECT count(*) FROM member

Question: Show the name of the leader of the club named "Houston".
SQL: SELECT T3.Name FROM club_leader AS T1 JOIN club AS T2 ON T1.Club_ID  =  T2.Club_ID JOIN member AS T3 ON T1.Member_ID  =  T3.Member_ID WHERE T2.Club_Name  =  "Houston"

Question: Show the names of club leaders that joined their club before 2018.
SQL: SELECT T3.Name ,  T2.Club_Name FROM club_leader AS T1 JOIN club AS T2 ON T1.Club_ID  =  T2.Club_ID JOIN member AS T3 ON T1.Member_ID  =  T3.Member_ID WHERE T1.Year_Join  <  2018

Question: List the names of members that are not club leaders.
SQL: SELECT Name FROM member WHERE Member_ID NOT IN (SELECT Member_ID FROM club_leader)
Database schema:
CREATE TABLE Staff (
  staff_id NUMBER PRIMARY KEY,
  staff_details TEXT
);
CREATE TABLE Ref_Staff_Roles (
  staff_role_code TEXT PRIMARY KEY,
  staff_role_description TEXT
);
CREATE TABLE Process_Outcomes (
  process_outcome_code TEXT PRIMARY KEY,
  process_outcome_description TEXT
);
CREATE TABLE Process_Status (
  process_status_code TEXT PRIMARY KEY,
  process_status_description TEXT
);
CREATE TABLE Authors (
  author_name TEXT PRIMARY KEY,
  other_details TEXT
);
CREATE TABLE Documents (
  document_id NUMBER PRIMARY KEY,
  author_name TEXT,
  document_name TEXT,
  document_description TEXT,
  other_details TEXT,
  FOREIGN KEY (author_name) REFERENCES Authors(author_name)
);
CREATE TABLE Business_Processes (
  process_id NUMBER PRIMARY KEY,
  next_process_id NUMBER,
  process_name TEXT,
  process_description TEXT,
  other_details TEXT
);
CREATE TABLE Documents_Processes (
  document_id NUMBER PRIMARY KEY,
  process_id NUMBER,
  process_outcome_code TEXT,
  process_status_code TEXT,
  FOREIGN KEY (process_status_code) REFERENCES Process_Status(process_status_code),
  FOREIGN KEY (process_outcome_code) REFERENCES Process_Outcomes(process_outcome_code),
  FOREIGN KEY (process_id) REFERENCES Business_Processes(process_id),
  FOREIGN KEY (document_id) REFERENCES Documents(document_id)
);
CREATE TABLE Staff_in_Processes (
  document_id NUMBER PRIMARY KEY,
  process_id NUMBER,
  staff_id NUMBER,
  staff_role_code TEXT,
  date_from TIME,
  date_to TIME,
  other_details TEXT,
  FOREIGN KEY (staff_role_code) REFERENCES Ref_Staff_Roles(staff_role_code),
  FOREIGN KEY (document_id) REFERENCES Documents_Processes(document_id),
  FOREIGN KEY (process_id) REFERENCES Documents_Processes(process_id),
  FOREIGN KEY (staff_id) REFERENCES Staff(staff_id)
);

Question: Show all document ids without a process.
SQL: SELECT document_id FROM Documents EXCEPT SELECT document_id FROM Documents_processes

Question: Show all staff ids and the number of document processes for each staff.
SQL: SELECT staff_id ,  count(*) FROM Staff_in_processes GROUP BY staff_id

Question: Show the other details for the author Addison Denesik.
SQL: SELECT other_details FROM Authors WHERE author_name  =  "Addison Denesik"

Question: List the codes and descriptions for all process status.
SQL: SELECT process_status_code ,  process_status_description FROM Process_status

Question: What is the process name for the next process of the process with id 9?
SQL: SELECT process_name FROM Business_processes WHERE process_id  =  (SELECT next_process_id FROM Business_processes WHERE process_id  =  9)

Question: List the codes and descriptions for all staff roles.
SQL: SELECT staff_role_code ,  staff_role_description FROM Ref_staff_roles

Question: Show the number of process outcomes.
SQL: SELECT count(*) FROM Process_outcomes

Question: Show the next process id, process name, process description for process with id 9.
SQL: SELECT next_process_id ,  process_name ,  process_description FROM Business_processes WHERE process_id  =  9

Question: List all process ids with a document.
SQL: SELECT DISTINCT process_id FROM Documents_processes
Database schema:
CREATE TABLE conference (
  Conference_ID NUMBER PRIMARY KEY,
  Conference_Name TEXT,
  Year NUMBER,
  Location TEXT
);
CREATE TABLE institution (
  Institution_ID NUMBER PRIMARY KEY,
  Institution_Name TEXT,
  Location TEXT,
  Founded NUMBER
);
CREATE TABLE staff (
  staff_ID NUMBER PRIMARY KEY,
  name TEXT,
  Age NUMBER,
  Nationality TEXT,
  Institution_ID NUMBER,
  FOREIGN KEY (Institution_ID) REFERENCES institution(Institution_ID)
);
CREATE TABLE conference_participation (
  Conference_ID NUMBER,
  staff_ID NUMBER PRIMARY KEY,
  role TEXT,
  FOREIGN KEY (Conference_ID) REFERENCES conference(Conference_ID),
  FOREIGN KEY (staff_ID) REFERENCES staff(staff_ID)
);

Question: What are the names and locations of the universities that did not have any staff participating in any conferences in 2004?
SQL: SELECT T1.Institution_Name ,  T1.location FROM institution AS T1 JOIN staff AS T2 ON T1.institution_id  =  T2.institution_id WHERE T2.staff_id NOT IN (SELECT T4.staff_id FROM Conference AS T3 JOIN Conference_participation AS T4 ON T3.conference_id  =  T4.conference_id WHERE T3.year  =  2004)

Question: What is the total number of conferences?
SQL: SELECT count(*) FROM conference

Question: What are the names, years, and locations of all conferences?
SQL: SELECT conference_name ,  YEAR ,  LOCATION FROM conference

Question: What are the names of all staff members who are older than average?
SQL: SELECT name FROM staff WHERE age  >  (SELECT avg(age) FROM staff)

Question: What are the names, locations, and founding years for all institutions?
SQL: SELECT institution_name ,  LOCATION ,  founded FROM institution

Question: Show all staff names who have been both speaker and sponsor in some conference.
SQL: SELECT T1.name FROM staff AS T1 JOIN conference_participation AS T2 ON T1.staff_id  =  T2.staff_id WHERE T2.role  =  'Speaker' INTERSECT SELECT T1.name FROM staff AS T1 JOIN conference_participation AS T2 ON T1.staff_id  =  T2.staff_id WHERE T2.role  =  'Sponsor'

Question: What are the names of the staff members who have been both a speaker and a sponsor at some conference?
SQL: SELECT T1.name FROM staff AS T1 JOIN conference_participation AS T2 ON T1.staff_id  =  T2.staff_id WHERE T2.role  =  'Speaker' INTERSECT SELECT T1.name FROM staff AS T1 JOIN conference_participation AS T2 ON T1.staff_id  =  T2.staff_id WHERE T2.role  =  'Sponsor'

Question: For each institution id , how many staff members does each institution have that was founded after 1800 ? return their names .
SQL: select t1.institution_name ,  count(*) from institution as t1 join staff as t2 on t1.institution_id  =  t2.institution_id where t1.founded  >  1800 group by t2.institution_id
Database schema:
CREATE TABLE participants (
  id NUMBER PRIMARY KEY,
  name TEXT,
  popularity NUMBER
);
CREATE TABLE songs (
  id NUMBER PRIMARY KEY,
  language TEXT,
  original_artist TEXT,
  name TEXT,
  english_translation TEXT
);
CREATE TABLE performance_score (
  participant_id NUMBER PRIMARY KEY,
  songs_id NUMBER,
  voice_sound_quality NUMBER,
  rhythm_tempo NUMBER,
  stage_presence NUMBER,
  FOREIGN KEY (songs_id) REFERENCES songs(id),
  FOREIGN KEY (participant_id) REFERENCES participants(id)
);

Question: List the name of the songs in ascending, lexicographical order.
SQL: SELECT name FROM songs ORDER BY name

Question: What are the id and name of the song sung by the most participants?
SQL: SELECT T1.id ,  T1.name FROM songs AS T1 JOIN performance_score AS T2 ON T1.id  =  T2.songs_id GROUP BY T1.id ORDER BY count(*) DESC LIMIT 1

Question: How many songs listed are not performed?
SQL: SELECT count(*) FROM songs WHERE id NOT IN ( SELECT songs_id FROM performance_score );

Question: What are the names and original artists of the song whose English translation is 'All the streets of love'?
SQL: SELECT name ,  original_artist FROM songs WHERE english_translation  =  'All the streets of love'

Question: What are the distinct names of the participants who have sung a song in 'English'?
SQL: SELECT DISTINCT T1.name FROM participants AS T1 JOIN performance_score AS T2 ON T2.participant_id  =  T1.id JOIN songs AS T3 ON T3.id  =  T2.songs_id WHERE T3.language  =  'English'

Question: What are the distinct stage presence scores for all the songs that are in language 'English' ?
SQL: SELECT DISTINCT T2.stage_presence FROM songs AS T1 JOIN performance_score AS T2 ON T1.id  =  T2.songs_id WHERE T1.language  =  'English'

Question: How many songs contain "Love" in their names?
SQL: SELECT count(*) FROM songs WHERE name LIKE "%Love%"

Question: What are the average rhythm scores for the songs in each different language?
SQL: SELECT avg(T2.rhythm_tempo) ,  T1.language FROM songs AS T1 JOIN performance_score AS T2 ON T2.songs_id  =  T1.id GROUP BY T1.language

Question: What are the id, language and original artist of the songs whose name is not 'Love'?
SQL: SELECT id ,  LANGUAGE ,  original_artist FROM songs WHERE name != 'Love'
Database schema:
CREATE TABLE channel (
  Channel_ID NUMBER PRIMARY KEY,
  Name TEXT,
  Analogue_terrestrial_channel TEXT,
  Digital_terrestrial_channel TEXT,
  Internet TEXT
);
CREATE TABLE director (
  Director_ID NUMBER PRIMARY KEY,
  Name TEXT,
  Age NUMBER
);
CREATE TABLE program (
  Program_ID NUMBER PRIMARY KEY,
  Start_Year NUMBER,
  Title TEXT,
  Director_ID NUMBER,
  Channel_ID NUMBER,
  FOREIGN KEY (Channel_ID) REFERENCES channel(Channel_ID),
  FOREIGN KEY (Director_ID) REFERENCES director(Director_ID)
);
CREATE TABLE director_admin (
  Director_ID NUMBER PRIMARY KEY,
  Channel_ID NUMBER,
  Is_first_director OTHERS,
  FOREIGN KEY (Channel_ID) REFERENCES channel(Channel_ID),
  FOREIGN KEY (Director_ID) REFERENCES director(Director_ID)
);

Question: Find the name and website link of the channels that have more than one program.
SQL: SELECT t1.name ,  t1.internet FROM channel AS t1 JOIN program AS t2 ON t1.channel_id  =  t2.channel_id GROUP BY t1.channel_id HAVING count(*)  >  1

Question: How many channels have the word 'bbc' in their internet link?
SQL: SELECT count(*) FROM channel WHERE internet LIKE "%bbc%"

Question: Find the name and age of the director who is in charge of the most programs?
SQL: SELECT t2.name ,  t2.age FROM program AS t1 JOIN director AS t2 ON t1.director_id  =  t2.director_id GROUP BY t1.director_id ORDER BY count(*) DESC LIMIT 1

Question: Return the title of the program that began most recently.
SQL: SELECT title FROM program ORDER BY start_year DESC LIMIT 1

Question: What is the name of the director who is in the "Dracula" program?
SQL: SELECT t2.name FROM program AS t1 JOIN director AS t2 ON t1.director_id  =  t2.director_id WHERE t1.title  =  'Dracula'

Question: Find the name of the directors whose age is between 30 and 60.
SQL: SELECT name FROM director WHERE age BETWEEN 30 AND 60

Question: Find the name and internet web of the channel that is directed by the most directors.
SQL: SELECT t1.name ,  t1.internet FROM channel AS t1 JOIN director_admin AS t2 ON t1.channel_id  =  t2.channel_id GROUP BY t1.channel_id ORDER BY count(*) DESC LIMIT 1

Question: Return the names of directors who are older than the average age.
SQL: SELECT name FROM director WHERE age  >  (SELECT avg(age) FROM director)
Database schema:
CREATE TABLE Movies (
  Code NUMBER PRIMARY KEY,
  Title TEXT,
  Rating TEXT
);
CREATE TABLE MovieTheaters (
  Code NUMBER PRIMARY KEY,
  Name TEXT,
  Movie NUMBER,
  FOREIGN KEY (Movie) REFERENCES Movies(Code)
);

Question: Find the name of all movies that are not played in Odeon theater.
SQL: SELECT title FROM movies EXCEPT SELECT T1.title FROM movies AS T1 JOIN movietheaters AS T2 ON T1.code  =  T2.movie WHERE T2.name  =  'Odeon'

Question: What are the names of the movie theaters that are playing 'G' or 'PG' rated movies?
SQL: SELECT title FROM movies WHERE rating  =  'G' OR rating  =  'PG'

Question: Find the name of the theater that is playing the most number of movies.
SQL: SELECT name FROM movietheaters GROUP BY name ORDER BY count(*) DESC LIMIT 1

Question: How many distinct movies are on in theaters?
SQL: SELECT count(DISTINCT T1.code) FROM movies AS T1 JOIN movietheaters AS T2 ON T1.code  =  T2.movie

Question: Find the titles of all movies sorted by their ratings.
SQL: SELECT title FROM movies ORDER BY rating

Question: Show the titles of movies not currently being shown in any theaters.
SQL: SELECT Title FROM Movies WHERE Code NOT IN (SELECT Movie FROM MovieTheaters WHERE Movie != 'null')

Question: Select the title of all movies.
SQL: SELECT title FROM movies

Question: What is all the information about the unrated movies?
SQL: SELECT * FROM movies WHERE rating  =  'null'

Question: How many movies are playing in theaters?
SQL: SELECT count(*) FROM movies AS T1 JOIN movietheaters AS T2 ON T1.code  =  T2.movie

Question: What are the names of every movie that is not playing at the Odeon theater?
SQL: SELECT title FROM movies EXCEPT SELECT T1.title FROM movies AS T1 JOIN movietheaters AS T2 ON T1.code  =  T2.movie WHERE T2.name  =  'Odeon'

Question: Find the name of the movie theaters that are playing the movies whose rating is ‘G’.
SQL: SELECT T2.name FROM movies AS T1 JOIN movietheaters AS T2 ON T1.code  =  T2.movie WHERE T1.rating  =  'G'

Question: What are names of movies that have a 'G' ratings?
SQL: SELECT title FROM movies WHERE rating  =  'G'

Question: Find the title of the movie that is played in the Odeon theater.
SQL: SELECT T1.title FROM movies AS T1 JOIN movietheaters AS T2 ON T1.code  =  T2.movie WHERE T2.name  =  'Odeon'

Question: What are the names of movies that do not have any ratings?
SQL: SELECT title FROM movies WHERE rating  =  'null'

Question: Find the name of the movie that is on in both Odeon and Imperial theaters.
SQL: SELECT T1.title FROM movies AS T1 JOIN movietheaters AS T2 ON T1.code  =  T2.movie WHERE T2.name  =  'Odeon' INTERSECT SELECT T1.title FROM movies AS T1 JOIN movietheaters AS T2 ON T1.code  =  T2.movie WHERE T2.name  =  'Imperial'

Question: Find the names of movies that are played in any theater and the name of the corresponding theater.
SQL: SELECT T1.title ,  T2.name FROM movies AS T1 JOIN movietheaters AS T2 ON T1.code  =  T2.movie

Question: What are the different movie ratings?
SQL: SELECT DISTINCT rating FROM movies
Database schema:
CREATE TABLE driver (
  Driver_ID NUMBER PRIMARY KEY,
  Driver_Name TEXT,
  Entrant TEXT,
  Constructor TEXT,
  Chassis TEXT,
  Engine TEXT,
  Age NUMBER
);
CREATE TABLE race (
  Road NUMBER PRIMARY KEY,
  Driver_ID NUMBER,
  Race_Name TEXT,
  Pole_Position TEXT,
  Fastest_Lap TEXT,
  Winning_driver TEXT,
  Winning_team TEXT,
  Report TEXT,
  FOREIGN KEY (Driver_ID) REFERENCES driver(Driver_ID)
);

Question: Return the different constructors of drivers, ordered by age ascending.
SQL: SELECT DISTINCT CONSTRUCTOR FROM driver ORDER BY Age ASC

Question: List the names of drivers that do not participate in any race.
SQL: SELECT Driver_Name FROM driver WHERE Driver_ID NOT IN (SELECT Driver_ID FROM race)

Question: Please show the age of the driver who participated in the most number of races.
SQL: SELECT T1.Age FROM driver AS T1 JOIN race AS T2 ON T1.Driver_ID  =  T2.Driver_ID GROUP BY T1.Driver_ID ORDER BY COUNT(*) DESC LIMIT 1

Question: Please show the names and ages of the drivers who participated in at least two races.
SQL: SELECT T1.Driver_Name ,  T1.Age FROM driver AS T1 JOIN race AS T2 ON T1.Driver_ID  =  T2.Driver_ID GROUP BY T1.Driver_ID HAVING COUNT(*)  >=  2

Question: Count the number of different engines used by drivers who had an age either over 30 or under 20.
SQL: SELECT count(DISTINCT Engine) FROM driver WHERE Age  >  30 OR Age  <  20

Question: How many races did each driver participate in?
SQL: SELECT T1.Driver_Name ,  COUNT(*) FROM driver AS T1 JOIN race AS T2 ON T1.Driver_ID  =  T2.Driver_ID GROUP BY T1.Driver_ID

Question: What are the names of drivers, ordered descending alphabetically?
SQL: SELECT Driver_Name FROM driver ORDER BY Driver_Name DESC

Question: Which teams won more than 1 race?
SQL: SELECT Winning_team FROM race GROUP BY Winning_team HAVING count(*)  >  1

Question: Please list the names of races with drivers aged 26 or older participating.
SQL: SELECT T2.Race_Name FROM driver AS T1 JOIN race AS T2 ON T1.Driver_ID  =  T2.Driver_ID WHERE T1.Age  >=  26
Database schema:
CREATE TABLE bike (
  id NUMBER PRIMARY KEY,
  product_name TEXT,
  weight NUMBER,
  price NUMBER,
  material TEXT
);
CREATE TABLE cyclist (
  id NUMBER PRIMARY KEY,
  heat NUMBER,
  name TEXT,
  nation TEXT,
  result NUMBER
);
CREATE TABLE cyclists_own_bikes (
  cyclist_id NUMBER PRIMARY KEY,
  bike_id NUMBER,
  purchase_year NUMBER,
  FOREIGN KEY (bike_id) REFERENCES bike(id),
  FOREIGN KEY (cyclist_id) REFERENCES cyclist(id)
);

Question: How many different levels of heat are there for the cyclists?
SQL: SELECT count(DISTINCT heat) FROM cyclist

Question: What is the average price of the bikes made of material 'Carbon CC'?
SQL: SELECT avg(price) FROM bike WHERE material  =  'Carbon CC'

Question: List the product names and weights of the bikes in ascending order of price.
SQL: SELECT product_name ,  weight FROM bike ORDER BY price ASC

Question: What are the name and result of the cyclists not from 'Russia' ?
SQL: SELECT name ,  RESULT FROM cyclist WHERE nation != 'Russia'

Question: Show the name, nation and result for the cyclists who did not purchase any racing bike.
SQL: SELECT name ,  nation ,  RESULT FROM cyclist EXCEPT SELECT T1.name ,  T1.nation ,  T1.result FROM cyclist AS T1 JOIN cyclists_own_bikes AS T2 ON T1.id  =  T2.cyclist_id

Question: What are the ids and names of racing bikes that are purchased by at least 4 cyclists?
SQL: SELECT T1.id ,  T1.product_name FROM bike AS T1 JOIN cyclists_own_bikes AS T2 ON T1.id  =  T2.bike_id GROUP BY T1.id HAVING count(*)  >=  4