CREATE TABLE Movie (
    mID NUMBER PRIMARY KEY,
    title TEXT,
    year NUMBER,
    director TEXT
);
CREATE TABLE Reviewer (
    rID NUMBER PRIMARY KEY,
    name TEXT
);
CREATE TABLE Rating (
    rID NUMBER,
    mID NUMBER,
    stars NUMBER,
    ratingDate TIME,
    FOREIGN KEY ("rID") REFERENCES Reviewer("rID"),
    FOREIGN KEY ("mID") REFERENCES Movie("mID")
);

Question: Find the titles of all movies directed by steven spielberg.
SQL: SELECT title FROM Movie WHERE director = 'Steven Spielberg'

Question: What are the names of all movies directed by Steven Spielberg?
SQL: SELECT title FROM Movie WHERE director = 'Steven Spielberg'

Question: What is the name of the movie produced after 2000 and directed by James Cameron?
SQL: SELECT title FROM Movie WHERE director = 'James Cameron' AND YEAR  >  2000

Question: What are the titles of all movies that James Cameron directed after 2000?
SQL: SELECT title FROM Movie WHERE director = 'James Cameron' AND YEAR  >  2000

Question: How many movies were made before 2000?
SQL: SELECT count(*) FROM Movie WHERE YEAR  <  2000

Question: How many movies were made before 2000?
SQL: SELECT count(*) FROM Movie WHERE YEAR  <  2000

Question: Who is the director of movie Avatar?
SQL: SELECT director FROM Movie WHERE title  = 'Avatar'

Question: Who directed Avatar?
SQL: SELECT director FROM Movie WHERE title  = 'Avatar'

Question: How many reviewers listed?
SQL: SELECT count(*) FROM Reviewer

Question: How many reviewers are there?
SQL: SELECT count(*) FROM Reviewer

Question: What is the id of the reviewer whose name has substring “Mike”?
SQL: SELECT rID FROM Reviewer WHERE name LIKE "%Mike%"

Question: What is the id of the reviewer whose name includes the word "Mike"?
SQL: SELECT rID FROM Reviewer WHERE name LIKE "%Mike%"

Question: What is the reviewer id of Daniel Lewis?
SQL: SELECT rID FROM Reviewer WHERE name  =  "Daniel Lewis"

Question: What is the id of the reviewer named Daniel Lewis?
SQL: SELECT rID FROM Reviewer WHERE name  =  "Daniel Lewis"

Question: What is the total number of ratings that has more than 3 stars?
SQL: SELECT count(*) FROM Rating WHERE stars  >  3

Question: How many movie ratings have more than 3 stars?
SQL: SELECT count(*) FROM Rating WHERE stars  >  3

Question: What is the lowest and highest rating star?
SQL: SELECT max(stars) ,  min(stars) FROM Rating

Question: What is the maximum and mininum number of stars a rating can receive?
SQL: SELECT max(stars) ,  min(stars) FROM Rating

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

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

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

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

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

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

Question: Find the titles of all movies that have no ratings.
SQL: SELECT title FROM Movie WHERE mID NOT IN (SELECT mID FROM Rating)

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)

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"

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"

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)

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)

Question: What is the name of the most recent movie?
SQL: SELECT title FROM Movie WHERE YEAR  =  (SELECT max(YEAR) FROM Movie)

Question: What is the title of the newest movie?
SQL: SELECT title FROM Movie WHERE YEAR  =  (SELECT max(YEAR) FROM Movie)

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)

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)

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")

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")

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")

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")

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

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

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

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