-- create a table
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL
);
-- insert some values
INSERT INTO students VALUES (1, 'Ryan', 'M');
INSERT INTO students VALUES (2, 'Joanna', 'F');
-- fetch some values
SELECT * FROM students WHERE gender = 'F';

-- 1. From the following tables, write a SQL query to find the actors who played a role in the movie 'Annie Hall'. Return all the fields of actor table.
SELECT * FROM actor WHERE act_id IN(SELECT act_id FROM movie_cast Where mov_id IN(SELECT mov_id FROM movie where mov_title='Annie Hall'));

-- 2. From the following tables, write a SQL query to find the director who directed a movie that casted a role for 'Eyes Wide Shut'. Return director first name, last name.
SELECT dir_fname, dir_lname
FROM  director
WHERE dir_id in (
SELECT dir_id 
FROM movie_direction
WHERE mov_id in(
SELECT mov_id 
FROM movie_cast WHERE role = ANY (
SELECT role 
FROM movie_cast 
WHERE mov_id IN (
SELECT  mov_id 
FROM movie 
WHERE mov_title='Eyes Wide Shut'))));

-- 3. From the following table, write a SQL query to find those movies, which released in the country besides UK. Return movie title, movie year, movie time, date of release, releasing country.
SELECT mov_title,mov_year,mov_time,mov_dt_rel as date_of_realease, mov_re_contry as releasing_country FROM movie WHERE mov_rel_country <> 'UK';

-- 4. From the following tables, write a SQL query to find those movies where reviewer is unknown. Return movie title, year, release date, director first name, last name, actor first name, last name.
SELECT mov_title, mov_year, mov_dt_rel, dir_fname, dir_lname, 
       act_fname, act_lname
	   FROM movie a, movie_direction b, director c, 
                rating d, reviewer e, actor f, movie_cast g
	   WHERE a.mov_id=b.mov_id
AND  b.dir_id=c.dir_id 
 AND a.mov_id=d.mov_id 
  AND  d.rev_id=e.rev_id 
   AND  a.mov_id=g.mov_id 
    AND g.act_id=f.act_id 
	 AND e.rev_name IS NULL;
	 
--5. From the following tables, write a SQL query to find those movies directed by the director whose first name is 'Woddy' and last name is 'Allen'. Return movie title.
SELECT mov_title from movies WHERE mov_id=(SELECT mov_id from direction WHERE dir_id=(SELECT dir_id FROM director WHERE dir_fname='Woody' AND dir_lname='Allen') ;
-- 6. From the following tables, write a SQL query to find those years, which produced at least one movie and that, received a rating of more than three stars. Sort the result-set in ascending order by movie year. Return movie year.
SELECT DISTINCT mov_year 
FROM movie 
WHERE mov_id IN (
SELECT mov_id 
FROM rating 
WHERE rev_stars>3) 
ORDER BY mov_year;

-- 7. From the following table, write a SQL query to find those movies, which have no ratings. Return movie title.

SELECT DISTINCT mov_title 
FROM movie 
WHERE mov_id IN (
SELECT mov_id 
FROM movie 
WHERE mov_id NOT IN (
SELECT mov_id FROM Rating));

-- 8. From the following tables, write a SQL query to find those reviewers who have rated nothing for some movies. Return reviewer name
SELECT rev_name FROM reviewer WHERE rev_id IN(SELECT rev_id FROM Rating WHERE rev_stars Is Null);

-- 9. From the following tables, write a SQL query to find those movies, which reviewed by a reviewer and got a rating. Sort the result-set in ascending order by reviewer name, movie title, review Stars. Return reviewer name, movie title, review Stars.
SELECT rev_name, mov_title, rev_stars 
 FROM reviewer, rating, movie 
  WHERE reviewer.rev_id=rating.rev_id 
   AND movie.mov_id=rating.mov_id 
     AND reviewer.rev_name IS NOT NULL 
       AND rating.rev_stars IS NOT NULL
ORDER BY rev_name, mov_title, rev_stars;

-- 10. From the following tables, write a SQL query to find those reviewers who rated more than one movie. Group the result set on reviewer’s name, movie title. Return reviewer’s name, movie title.
SELECT rev_name, mov_title 
FROM reviewer, movie, rating, rating r2
WHERE rating.mov_id=movie.mov_id 
  AND reviewer.rev_id=rating.rev_ID 
    AND rating.rev_id = r2.rev_id 
GROUP BY rev_name, mov_title HAVING count(*) > 1;

11. From the following tables, write a SQL query to find those movies, which have received highest number of stars. Group the result set on movie title and sorts the result-set in ascending order by movie title. Return movie title and maximum number of review stars.
SELECT mov_title, MAX(rev_stars)
FROM movie, rating 
WHERE movie.mov_id=rating.mov_id 
AND rating.rev_stars IS NOT NULL
GROUP BY  mov_title
ORDER BY mov_title;

12. From the following table, write a SQL query to find all reviewers who rated the movie ‘American Beauty’. Return reviewer name.
SELECT DISTINCT reviewer.rev_name
FROM reviewer, rating, movie
WHERE reviewer.rev_id = rating.rev_id
AND movie.mov_id = rating.mov_id
AND movie.mov_title = 'American Beauty';

13. From the following tables, write a SQL query to find the movies, which have reviewed by any reviewer body except by 'Paul Monks'. Return movie title.
SELECT movie.mov_title
FROM movie 
WHERE movie.mov_id IN(
SELECT mov_id 
FROM rating 
WHERE rev_id NOT IN (
SELECT rev_id 
FROM reviewer 
WHERE rev_name='Paul Monks'));

--  4. From the following tables, write a SQL query to find the lowest rated movies. Return reviewer name, movie title, and number of stars for those movies.
SELECT reviewer.rev_name, movie.mov_title, rating.rev_stars
FROM reviewer, movie, rating
WHERE rating.rev_stars = (
SELECT MIN(rating.rev_stars)
FROM rating
)
AND rating.rev_id = reviewer.rev_id
AND rating.mov_id = movie.mov_id;

-- 15. From the following tables, write a SQL query to find the movies directed by 'James Cameron'. Return movie title.
SELECT mov_title
FROM movie
WHERE mov_id IN (
SELECT mov_id 
FROM movie_direction 
WHERE dir_id IN (
SELECT dir_id 
FROM director 
WHERE dir_fname = 'James' AND dir_lname='Cameron'
));

-- 16. Write a query in SQL to find the name of those movies where one or more actors acted in two or more movies.
SELECT mov_title 
FROM movie 
WHERE mov_id IN (
SELECT mov_id 
FROM movie_cast 
WHERE act_id IN (
SELECT act_id 
FROM actor 
WHERE act_id IN (
SELECT act_id 
FROM movie_cast GROUP BY act_id 
HAVING COUNT(act_id)>1)));