lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What is the id of the reviewer whose name has substring “Mike”?

### SQL
SELECT rID FROM Reviewer WHERE name LIKE "%Mike%"
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What is the id of the reviewer whose name includes the word "Mike"?

### SQL
SELECT rID FROM Reviewer WHERE name LIKE "%Mike%"
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What is the reviewer id of Daniel Lewis?

### SQL
SELECT rID FROM Reviewer WHERE name  =  "Daniel Lewis"
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What is the id of the reviewer named Daniel Lewis?

### SQL
SELECT rID FROM Reviewer WHERE name  =  "Daniel Lewis"
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What is the total number of ratings that has more than 3 stars?

### SQL
SELECT count(*) FROM Rating WHERE stars  >  3
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
How many movie ratings have more than 3 stars?

### SQL
SELECT count(*) FROM Rating WHERE stars  >  3
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What is the lowest and highest rating star?

### SQL
SELECT max(stars) ,  min(stars) FROM Rating
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What is the maximum and mininum number of stars a rating can receive?

### SQL
SELECT max(stars) ,  min(stars) FROM Rating
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
Find all years that have a movie that received a rating of 4 or 5, and sort them in increasing order of year.

### SQL
SELECT DISTINCT YEAR FROM Movie AS T1 JOIN Rating AS T2 ON T1.mID  =  T2.mID WHERE T2.stars  >=  4 ORDER BY T1.year
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
In what years did a movie receive a 4 or 5 star rating, and list the years from oldest to most recently?

### SQL
SELECT DISTINCT YEAR FROM Movie AS T1 JOIN Rating AS T2 ON T1.mID  =  T2.mID WHERE T2.stars  >=  4 ORDER BY T1.year
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the names of directors who directed movies with 5 star rating? Also return the title of these movies.

### SQL
SELECT T1.director ,  T1.title FROM Movie AS T1 JOIN Rating AS T2 ON T1.mID  =  T2.mID WHERE T2.stars  =  5
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the names of the directors who created a movie with a 5 star rating, and what was the name of those movies?

### SQL
SELECT T1.director ,  T1.title FROM Movie AS T1 JOIN Rating AS T2 ON T1.mID  =  T2.mID WHERE T2.stars  =  5
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What is the average rating star for each reviewer?

### SQL
SELECT T2.name ,  avg(T1.stars) FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID GROUP BY T2.name
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What is the average number of stars that each reviewer awards for a movie?

### SQL
SELECT T2.name ,  avg(T1.stars) FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID GROUP BY T2.name
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
Find the titles of all movies that have no ratings.

### SQL
SELECT title FROM Movie WHERE mID NOT IN (SELECT mID FROM Rating)
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the titles of all movies that have not been rated?

### SQL
SELECT title FROM Movie WHERE mID NOT IN (SELECT mID FROM Rating)
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
Find the names of all reviewers who have ratings with a NULL value for the date.

### SQL
SELECT DISTINCT name FROM Reviewer AS T1 JOIN Rating AS T2 ON T1.rID  =  T2.rID WHERE ratingDate  =  "null"
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the different names of all reviewers whose ratings do not have a date field?

### SQL
SELECT DISTINCT name FROM Reviewer AS T1 JOIN Rating AS T2 ON T1.rID  =  T2.rID WHERE ratingDate  =  "null"
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What is the average rating stars and title for the oldest movie?

### SQL
SELECT avg(T1.stars) ,  T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID WHERE T2.year  =  (SELECT min(YEAR) FROM Movie)
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
For the oldest movie listed, what is its average rating and title?

### SQL
SELECT avg(T1.stars) ,  T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID WHERE T2.year  =  (SELECT min(YEAR) FROM Movie)
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What is the name of the most recent movie?

### SQL
SELECT title FROM Movie WHERE YEAR  =  (SELECT max(YEAR) FROM Movie)
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What is the title of the newest movie?

### SQL
SELECT title FROM Movie WHERE YEAR  =  (SELECT max(YEAR) FROM Movie)
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What is the maximum stars and year for the most recent movie?

### SQL
SELECT max(T1.stars) ,  T2.year FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID WHERE T2.year  =  (SELECT max(YEAR) FROM Movie)
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What is highest rating for the most recent movie and when was it released?

### SQL
SELECT max(T1.stars) ,  T2.year FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID WHERE T2.year  =  (SELECT max(YEAR) FROM Movie)
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What is the names of movies whose created year is after all movies directed by Steven Spielberg?

### SQL
SELECT title FROM Movie WHERE YEAR  >  (SELECT max(YEAR) FROM Movie WHERE director  =  "Steven Spielberg")
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the names of all movies that were created after the most recent Steven Spielberg film?

### SQL
SELECT title FROM Movie WHERE YEAR  >  (SELECT max(YEAR) FROM Movie WHERE director  =  "Steven Spielberg")
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the titles and directors of the movies whose star is greater than the average stars of the movies directed by James Cameron?

### SQL
SELECT T2.title ,  T2.director FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID WHERE T1.stars  >  (SELECT avg(T1.stars) FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID WHERE T2.director  =  "James Cameron")
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the titles and directors of all movies that have a rating higher than the average James Cameron film rating?

### SQL
SELECT T2.title ,  T2.director FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID WHERE T1.stars  >  (SELECT avg(T1.stars) FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID WHERE T2.director  =  "James Cameron")
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
Return reviewer name, movie title, stars, and ratingDate. And sort the data first by reviewer name, then by movie title, and lastly by number of stars.

### SQL
SELECT T3.name ,  T2.title ,  T1.stars ,  T1.ratingDate FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID JOIN Reviewer AS T3 ON T1.rID  =  T3.rID ORDER BY T3.name ,  T2.title ,  T1.stars
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What is the reviewer name, film title, movie rating, and rating date  for every movie ordered by reviewer name, movie title, then finally rating?

### SQL
SELECT T3.name ,  T2.title ,  T1.stars ,  T1.ratingDate FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID JOIN Reviewer AS T3 ON T1.rID  =  T3.rID ORDER BY T3.name ,  T2.title ,  T1.stars
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
Find the names of all reviewers who have contributed three or more ratings.

### SQL
SELECT T2.name FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID GROUP BY T1.rID HAVING COUNT(*)  >=  3
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the names of all reviewers that have rated 3 or more movies?

### SQL
SELECT T2.name FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID GROUP BY T1.rID HAVING COUNT(*)  >=  3
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
Find the names of all reviewers who rated Gone with the Wind.

### SQL
SELECT DISTINCT T3.name FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID JOIN Reviewer AS T3 ON T1.rID  =  T3.rID WHERE T2.title  =  'Gone with the Wind'
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the names of all the different reviewers who rates Gone with the Wind?

### SQL
SELECT DISTINCT T3.name FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID JOIN Reviewer AS T3 ON T1.rID  =  T3.rID WHERE T2.title  =  'Gone with the Wind'
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
Find the names of all directors whose movies are rated by Sarah Martinez.

### SQL
SELECT DISTINCT T2.director FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID JOIN Reviewer AS T3 ON T1.rID  =  T3.rID WHERE T3.name  =  'Sarah Martinez'
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the names of all directors whose movies have been reviewed by Sarah Martinez?

### SQL
SELECT DISTINCT T2.director FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID JOIN Reviewer AS T3 ON T1.rID  =  T3.rID WHERE T3.name  =  'Sarah Martinez'
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
For any rating where the name of reviewer is the same as the director of the movie, return the reviewer name, movie title, and number of stars.

### SQL
SELECT DISTINCT T3.name ,  T2.title ,  T1.stars FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID JOIN Reviewer AS T3 ON T1.rID  =  T3.rID WHERE T2.director  =  T3.name
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the different reviewer names, movie titles, and stars for every rating where the reviewer had the same name as the director?

### SQL
SELECT DISTINCT T3.name ,  T2.title ,  T1.stars FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID JOIN Reviewer AS T3 ON T1.rID  =  T3.rID WHERE T2.director  =  T3.name
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
Return all reviewer names and movie names together in a single list.

### SQL
SELECT name FROM Reviewer UNION SELECT title FROM Movie
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the names of all the reviewers and movie names?

### SQL
SELECT name FROM Reviewer UNION SELECT title FROM Movie
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
Find the titles of all movies not reviewed by Chris Jackson.

### SQL
SELECT DISTINCT title FROM Movie EXCEPT SELECT T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID JOIN Reviewer AS T3 ON T1.rID  =  T3.rID WHERE T3.name  =  'Chris Jackson'
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the titles of all movies that were not reviewed by Chris Jackson?

### SQL
SELECT DISTINCT title FROM Movie EXCEPT SELECT T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID JOIN Reviewer AS T3 ON T1.rID  =  T3.rID WHERE T3.name  =  'Chris Jackson'
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
For all directors who directed more than one movie, return the titles of all movies directed by them, along with the director name. Sort by director name, then movie title.

### SQL
SELECT T1.title ,  T1.director FROM Movie AS T1 JOIN Movie AS T2 ON T1.director  =  T2.director WHERE T1.title != T2.title ORDER BY T1.director ,  T1.title
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
For all directors who have directed more than one movie, what movies have they directed and what are their names?

### SQL
SELECT T1.title ,  T1.director FROM Movie AS T1 JOIN Movie AS T2 ON T1.director  =  T2.director WHERE T1.title != T2.title ORDER BY T1.director ,  T1.title
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
For directors who had more than one movie, return the titles and produced years of all movies directed by them.

### SQL
SELECT T1.title ,  T1.year FROM Movie AS T1 JOIN Movie AS T2 ON T1.director  =  T2.director WHERE T1.title != T2.title
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
For each director who directed more than one movie, what are the titles and dates of release for all those movies?

### SQL
SELECT T1.title ,  T1.year FROM Movie AS T1 JOIN Movie AS T2 ON T1.director  =  T2.director WHERE T1.title != T2.title
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the names of the directors who made exactly one movie?

### SQL
SELECT director FROM Movie GROUP BY director HAVING count(*)  =  1
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the names of all directors who made one movie?

### SQL
SELECT director FROM Movie GROUP BY director HAVING count(*)  =  1
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the names of the directors who made exactly one movie excluding director NULL?

### SQL
SELECT director FROM Movie WHERE director != "null" GROUP BY director HAVING count(*)  =  1
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the names of all directors who have made one movie except for the director named NULL?

### SQL
SELECT director FROM Movie WHERE director != "null" GROUP BY director HAVING count(*)  =  1
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
How many movie reviews does each director get?

### SQL
SELECT count(*) ,  T1.director FROM Movie AS T1 JOIN Rating AS T2 ON T1.mID  =  T2.mID GROUP BY T1.director
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
For each director, how many reviews have they received?

### SQL
SELECT count(*) ,  T1.director FROM Movie AS T1 JOIN Rating AS T2 ON T1.mID  =  T2.mID GROUP BY T1.director
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
Find the movies with the highest average rating. Return the movie titles and average rating.

### SQL
SELECT T2.title ,  avg(T1.stars) FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID GROUP BY T1.mID ORDER BY avg(T1.stars) DESC LIMIT 1
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the movie titles with the highest average rating and what are those ratings?

### SQL
SELECT T2.title ,  avg(T1.stars) FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID GROUP BY T1.mID ORDER BY avg(T1.stars) DESC LIMIT 1
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the movie titles and average rating of the movies with the lowest average rating?

### SQL
SELECT T2.title ,  avg(T1.stars) FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID GROUP BY T1.mID ORDER BY avg(T1.stars) LIMIT 1
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the titles and average ratings for all movies that have the lowest average rating?

### SQL
SELECT T2.title ,  avg(T1.stars) FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID GROUP BY T1.mID ORDER BY avg(T1.stars) LIMIT 1
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the names and years of the movies that has the top 3 highest rating star?

### SQL
SELECT T2.title ,  T2.year FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID ORDER BY T1.stars DESC LIMIT 3
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the names and years released for the movies with the top 3 highest ratings?

### SQL
SELECT T2.title ,  T2.year FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID ORDER BY T1.stars DESC LIMIT 3
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
For each director, return the director's name together with the title of the movie they directed that received the highest rating among all of their movies, and the value of that rating. Ignore movies whose director is NULL.

### SQL
SELECT T2.title ,  T1.stars ,  T2.director ,  max(T1.stars) FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID WHERE director != "null" GROUP BY director
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
For each director, what are the titles and ratings for all the movies they reviewed?

### SQL
SELECT T2.title ,  T1.stars ,  T2.director ,  max(T1.stars) FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID WHERE director != "null" GROUP BY director
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
Find the title and star rating of the movie that got the least rating star for each reviewer.

### SQL
SELECT T2.title ,  T1.rID ,  T1.stars ,  min(T1.stars) FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID GROUP BY T1.rID
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
For each reviewer id, what is the title and rating for the movie with the smallest rating?

### SQL
SELECT T2.title ,  T1.rID ,  T1.stars ,  min(T1.stars) FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID GROUP BY T1.rID
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
Find the title and score of the movie with the lowest rating among all movies directed by each director.

### SQL
SELECT T2.title ,  T1.stars ,  T2.director ,  min(T1.stars) FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID GROUP BY T2.director
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
For each director, what is the title and score of their most poorly rated movie?

### SQL
SELECT T2.title ,  T1.stars ,  T2.director ,  min(T1.stars) FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID GROUP BY T2.director
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What is the name of the movie that is rated by most of times?

### SQL
SELECT T2.title ,  T1.mID FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID GROUP BY T1.mID ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What is the name of the movie that has been reviewed the most?

### SQL
SELECT T2.title ,  T1.mID FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID GROUP BY T1.mID ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the titles of all movies that have rating star is between 3 and 5?

### SQL
SELECT T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID WHERE T1.stars BETWEEN 3 AND 5
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the titles of all movies that have between 3 and 5 stars?

### SQL
SELECT T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID WHERE T1.stars BETWEEN 3 AND 5
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
Find the names of reviewers who had given higher than 3 star ratings.

### SQL
SELECT T2.name FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID WHERE T1.stars  >  3
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the names of the reviewers who have rated a movie more than 3 stars before?

### SQL
SELECT T2.name FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID WHERE T1.stars  >  3
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
Find the average rating star for each movie that are not reviewed by Brittany Harris.

### SQL
SELECT mID ,  avg(stars) FROM Rating WHERE mID NOT IN (SELECT T1.mID FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID WHERE T2.name  =  "Brittany Harris") GROUP BY mID
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What is the average rating for each movie that has never been reviewed by Brittany Harris?

### SQL
SELECT mID ,  avg(stars) FROM Rating WHERE mID NOT IN (SELECT T1.mID FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID WHERE T2.name  =  "Brittany Harris") GROUP BY mID
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the ids of the movies that are not reviewed by Brittany Harris.

### SQL
SELECT mID FROM Rating EXCEPT SELECT T1.mID FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID WHERE T2.name  =  "Brittany Harris"
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the ids of all moviest hat have not been reviewed by Britanny Harris?

### SQL
SELECT mID FROM Rating EXCEPT SELECT T1.mID FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID WHERE T2.name  =  "Brittany Harris"
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
Find the average rating star for each movie that received at least 2 ratings.

### SQL
SELECT mID ,  avg(stars) FROM Rating GROUP BY mID HAVING count(*)  >=  2
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
For each movie that received more than 3 reviews, what is the average rating?

### SQL
SELECT mID ,  avg(stars) FROM Rating GROUP BY mID HAVING count(*)  >=  2
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
find the ids of reviewers who did not give 4 star.

### SQL
SELECT rID FROM Rating EXCEPT SELECT rID FROM Rating WHERE stars  =  4
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the ids of all reviewers who did not give 4 stars?

### SQL
SELECT rID FROM Rating EXCEPT SELECT rID FROM Rating WHERE stars  =  4
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
Find the ids of reviewers who didn't only give 4 star.

### SQL
SELECT rID FROM Rating WHERE stars != 4
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the ids of all reviewers who have not given 4 stars at least once?

### SQL
SELECT rID FROM Rating WHERE stars != 4
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are names of the movies that are either made after 2000 or reviewed by Brittany Harris?

### SQL
SELECT DISTINCT T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID JOIN Reviewer AS T3 ON T1.rID  =  T3.rID WHERE T3.name  =  'Brittany Harris' OR T2.year  >  2000
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the names of all movies that were made after 2000 or reviewed by Brittany Harris?

### SQL
SELECT DISTINCT T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID JOIN Reviewer AS T3 ON T1.rID  =  T3.rID WHERE T3.name  =  'Brittany Harris' OR T2.year  >  2000
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are names of the movies that are either made before 1980 or directed by James Cameron?

### SQL
SELECT title FROM Movie WHERE director  =  "James Cameron" OR YEAR   <  1980
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the names of all movies made before 1980 or had James Cameron as the director?

### SQL
SELECT title FROM Movie WHERE director  =  "James Cameron" OR YEAR   <  1980
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the names of reviewers who had rated 3 star and 4 star?

### SQL
SELECT T2.name FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID WHERE T1.stars  =  3 INTERSECT SELECT T2.name FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID WHERE T1.stars  =  4
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the names of all reviewers that have given 3 or 4 stars for reviews?

### SQL
SELECT T2.name FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID WHERE T1.stars  =  3 INTERSECT SELECT T2.name FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID WHERE T1.stars  =  4
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the names of movies that get 3 star and 4 star?

### SQL
SELECT T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID WHERE T1.stars  =  3 INTERSECT SELECT T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID WHERE T1.stars  =  4
### Database Schema
CREATE TABLE Movie(
	mID int primary key, 
	title text, 
	year int, 
	director text
)

CREATE TABLE Reviewer(
	rID int primary key, 
	name text)

CREATE TABLE Rating(
	rID int, 
	mID int, 
	stars int, 
	ratingDate date,
	FOREIGN KEY (mID) references Movie(mID),
	FOREIGN KEY (rID) references Reviewer(rID)
)

### Question
What are the names of all movies that received 3 or 4 stars?

### SQL
SELECT T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID WHERE T1.stars  =  3 INTERSECT SELECT T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID WHERE T1.stars  =  4