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: 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

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

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

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

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

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

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

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

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

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"

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"

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

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

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

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

Question: Find the ids of reviewers who didn't only give 4 star.
SQL: SELECT rID FROM Rating WHERE stars != 4

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

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

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

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

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

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

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

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

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