lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Find papers whose second author has last name "Turon" and is affiliated with an institution in the country "USA".

### SQL
SELECT t3.title FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid JOIN inst AS t4 ON t2.instid  =  t4.instid WHERE t4.country  =  "USA" AND t2.authorder  =  2 AND t1.lname  =  "Turon"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Find the titles of papers whose first author is affiliated with an institution in the country "Japan" and has last name "Ohori"?

### SQL
SELECT t3.title FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid JOIN inst AS t4 ON t2.instid  =  t4.instid WHERE t4.country  =  "Japan" AND t2.authorder  =  1 AND t1.lname  =  "Ohori"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Which papers' first author is affiliated with an institution in the country "Japan" and has last name "Ohori"? Give me the titles of the papers.

### SQL
SELECT t3.title FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid JOIN inst AS t4 ON t2.instid  =  t4.instid WHERE t4.country  =  "Japan" AND t2.authorder  =  1 AND t1.lname  =  "Ohori"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
What is the last name of the author that has published the most papers?

### SQL
SELECT t1.lname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid GROUP BY t1.fname ,  t1.lname ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Which author has written the most papers? Find his or her last name.

### SQL
SELECT t1.lname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid GROUP BY t1.fname ,  t1.lname ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Retrieve the country that has published the most papers.

### SQL
SELECT t1.country FROM inst AS t1 JOIN authorship AS t2 ON t1.instid  =  t2.instid JOIN papers AS t3 ON t2.paperid  =  t3.paperid GROUP BY t1.country ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Find the country that the most papers are affiliated with.

### SQL
SELECT t1.country FROM inst AS t1 JOIN authorship AS t2 ON t1.instid  =  t2.instid JOIN papers AS t3 ON t2.paperid  =  t3.paperid GROUP BY t1.country ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Find the name of the organization that has published the largest number of papers.

### SQL
SELECT t1.name FROM inst AS t1 JOIN authorship AS t2 ON t1.instid  =  t2.instid JOIN papers AS t3 ON t2.paperid  =  t3.paperid GROUP BY t1.name ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Which institution has the most papers? Find the name of the institution.

### SQL
SELECT t1.name FROM inst AS t1 JOIN authorship AS t2 ON t1.instid  =  t2.instid JOIN papers AS t3 ON t2.paperid  =  t3.paperid GROUP BY t1.name ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Find the titles of the papers that contain the word "ML".

### SQL
SELECT title FROM papers WHERE title LIKE "%ML%"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Which papers have the substring "ML" in their titles? Return the titles of the papers.

### SQL
SELECT title FROM papers WHERE title LIKE "%ML%"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Which paper's title contains the word "Database"?

### SQL
SELECT title FROM papers WHERE title LIKE "%Database%"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Which papers have the substring "Database" in their titles? Show the titles of the papers.

### SQL
SELECT title FROM papers WHERE title LIKE "%Database%"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Find the first names of all the authors who have written a paper with title containing the word "Functional".

### SQL
SELECT t1.fname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE t3.title LIKE "%Functional%"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Who has written a paper that has the word "Functional" in its title? Return the first names of the authors.

### SQL
SELECT t1.fname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE t3.title LIKE "%Functional%"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Find the last names of all the authors that have written a paper with title containing the word "Monadic".

### SQL
SELECT t1.lname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE t3.title LIKE "%Monadic%"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Which authors have written a paper with title containing the word "Monadic"? Return their last names.

### SQL
SELECT t1.lname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE t3.title LIKE "%Monadic%"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Retrieve the title of the paper that has the largest number of authors.

### SQL
SELECT t2.title FROM authorship AS t1 JOIN papers AS t2 ON t1.paperid  =  t2.paperid WHERE t1.authorder  =  (SELECT max(authorder) FROM authorship)
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Which paper has the most authors? Give me the paper title.

### SQL
SELECT t2.title FROM authorship AS t1 JOIN papers AS t2 ON t1.paperid  =  t2.paperid WHERE t1.authorder  =  (SELECT max(authorder) FROM authorship)
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
What is the first name of the author with last name "Ueno"?

### SQL
SELECT fname FROM authors WHERE lname  =  "Ueno"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Which authors have last name "Ueno"? List their first names.

### SQL
SELECT fname FROM authors WHERE lname  =  "Ueno"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Find the last name of the author with first name "Amal".

### SQL
SELECT lname FROM authors WHERE fname  =  "Amal"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Which authors have first name "Amal"? List their last names.

### SQL
SELECT lname FROM authors WHERE fname  =  "Amal"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Find the first names of all the authors ordered in alphabetical order.

### SQL
SELECT fname FROM authors ORDER BY fname
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Sort the first names of all the authors in alphabetical order.

### SQL
SELECT fname FROM authors ORDER BY fname
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Retrieve all the last names of authors in alphabetical order.

### SQL
SELECT lname FROM authors ORDER BY lname
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Give me a list of all the last names of authors sorted in alphabetical order

### SQL
SELECT lname FROM authors ORDER BY lname
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Retrieve all the first and last names of authors in the alphabetical order of last names.

### SQL
SELECT fname ,  lname FROM authors ORDER BY lname
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Sort the list of all the first and last names of authors in alphabetical order of the last names.

### SQL
SELECT fname ,  lname FROM authors ORDER BY lname
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
How many bank branches are there?

### SQL
SELECT count(*) FROM bank
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
Count the number of bank branches.

### SQL
SELECT count(*) FROM bank
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
How many customers are there?

### SQL
SELECT sum(no_of_customers) FROM bank
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
What is the total number of customers across banks?

### SQL
SELECT sum(no_of_customers) FROM bank
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
Find the number of customers in the banks at New York City.

### SQL
SELECT sum(no_of_customers) FROM bank WHERE city  =  'New York City'
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
What is the total number of customers who use banks in New York City?

### SQL
SELECT sum(no_of_customers) FROM bank WHERE city  =  'New York City'
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
Find the average number of customers in all banks of Utah state.

### SQL
SELECT avg(no_of_customers) FROM bank WHERE state  =  'Utah'
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
What is the average number of customers across banks in the state of Utah?

### SQL
SELECT avg(no_of_customers) FROM bank WHERE state  =  'Utah'
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
Find the average number of customers cross all banks.

### SQL
SELECT avg(no_of_customers) FROM bank
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
What is the average number of bank customers?

### SQL
SELECT avg(no_of_customers) FROM bank
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
Find the city and state of the bank branch named morningside.

### SQL
SELECT city ,  state FROM bank WHERE bname  =  'morningside'
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
What city and state is the bank with the name morningside in?

### SQL
SELECT city ,  state FROM bank WHERE bname  =  'morningside'
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
Find the branch names of banks in the New York state.

### SQL
SELECT bname FROM bank WHERE state  =  'New York'
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
What are the names of banks in the state of New York?

### SQL
SELECT bname FROM bank WHERE state  =  'New York'
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
List the name of all customers sorted by their account balance in ascending order.

### SQL
SELECT cust_name FROM customer ORDER BY acc_bal
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
What are the names of all customers, ordered by account balance?

### SQL
SELECT cust_name FROM customer ORDER BY acc_bal
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
List the name of all different customers who have some loan sorted by their total loan amount.

### SQL
SELECT T1.cust_name FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id  =  T2.cust_id GROUP BY T1.cust_name ORDER BY sum(T2.amount)
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
What are the names of the different customers who have taken out a loan, ordered by the total amount that they have taken?

### SQL
SELECT T1.cust_name FROM customer AS T1 JOIN loan AS T2 ON T1.cust_id  =  T2.cust_id GROUP BY T1.cust_name ORDER BY sum(T2.amount)
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
Find the state, account type, and credit score of the customer whose number of loan is 0.

### SQL
SELECT state ,  acc_type ,  credit_score FROM customer WHERE no_of_loans  =  0
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
What are the states, account types, and credit scores for customers who have 0 loans?

### SQL
SELECT state ,  acc_type ,  credit_score FROM customer WHERE no_of_loans  =  0
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
Find the number of different cities which banks are located at.

### SQL
SELECT count(DISTINCT city) FROM bank
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
In how many different cities are banks located?

### SQL
SELECT count(DISTINCT city) FROM bank
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
Find the number of different states which banks are located at.

### SQL
SELECT count(DISTINCT state) FROM bank
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
In how many different states are banks located?

### SQL
SELECT count(DISTINCT state) FROM bank
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
How many distinct types of accounts are there?

### SQL
SELECT count(DISTINCT acc_type) FROM customer
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
Count the number of different account types.

### SQL
SELECT count(DISTINCT acc_type) FROM customer
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
Find the name and account balance of the customer whose name includes the letter ‘a’.

### SQL
SELECT cust_name ,  acc_bal FROM customer WHERE cust_name LIKE '%a%'
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
What are the names and account balances of customers with the letter a in their names?

### SQL
SELECT cust_name ,  acc_bal FROM customer WHERE cust_name LIKE '%a%'
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
Find the total account balance of each customer from Utah or Texas.

### SQL
SELECT sum(acc_bal) FROM customer WHERE state  =  'Utah' OR state  =  'Texas'
### Database Schema
CREATE TABLE bank (
branch_ID int PRIMARY KEY,
bname varchar(20),
no_of_customers int,
city varchar(10),
state varchar(20))

CREATE TABLE customer (
cust_ID varchar(3) PRIMARY KEY,
cust_name varchar(20),
acc_type char(1),
acc_bal int,
no_of_loans int,
credit_score int,
branch_ID int,
state varchar(20),
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID))

CREATE TABLE loan (
loan_ID varchar(3) PRIMARY KEY,
loan_type varchar(15),
cust_ID varchar(3),
branch_ID varchar(3),
amount int,
FOREIGN KEY(branch_ID) REFERENCES bank(branch_ID),
FOREIGN KEY(Cust_ID) REFERENCES customer(Cust_ID))

### Question
What are the total account balances for each customer from Utah or Texas?

### SQL
SELECT sum(acc_bal) FROM customer WHERE state  =  'Utah' OR state  =  'Texas'