ShubhamS32 · February 17, 2023 13:54
diff --git a/Movie_Queries_1.sql b/Movie_Queries_1.sql
 # Write a query in SQL to find the name of all reviewers who have rated their ratings with a NULL value.
 select rev_name from reviewer , rating
 where reviewer.rev_id = rating.rev_id
 and rev_stars IS NULL;

 #Write a query in SQL to list the first and last names of all the actors who were cast in the 
 #movie 'Annie Hall', and the roles they played in that production.
 select a.act_fname ,a.act_lname , mc.role
 from actor a , movie_cast mc , movie m 
 where m.mov_id = mc.mov_id 
 and mc.act_id = a.act_id 
 and m.mov_title=  'Annie Hall'

 #Write a query in SQL to find the name of movie and director (first and last names) who directed a 
 #movie that casted a role for 'Eyes Wide Shut'.
 SELECT d.dir_fname, d.dir_lname, m.mov_title  
 FROM  director d, movie m , movie_cast mc , movie_direction md
 where 
 and m.mov_id=md.mov_id
 and md.dir_id = d.dir_id
 and mc.mov_id = m.mov_id
 m.mov_title  = 'Eyes Wide Shut'

 #Write a query in SQL to find the name of movie and director (first and last names) 
 #who directed a movie that casted a role as Sean Maguire
 select m.title , d.dir_fname , d.dir_lname 
 from movies m , director d , movie_direction md , movie_cast mc
 where m.mov_id = md.mov_id
 and md.dir_id =d.dir_id
 and mc.mov_id = m.mov_id
 and mc.role like '%Sean Maguire%'


 #Write a query in SQL to list all the actors who acted in a movie before 1990 and also in a movie after 2000
 SELECT a.act_fname, a.act_lname, c.mov_title, c.mov_year  
 FROM actor a, movie_cast b, movie c  
 WHERE a.act_id=b.act_id  
 AND b.mov_id=c.mov_id  
 AND c.mov_year NOT BETWEEN 1990 and 2000;

 #Write a query in SQL to list first and last name of all the directors with number of genres movies the 
 #directed with genres name, and arranged the result alphabetically with the first and last name of the director.

 SELECT d.dir_fname,d.dir_lname ,g.gen_title,count(g.gen_title)
 from director d , movie_direction md , genres g , movie_genres mg
 where d.dir_id=md.dir_id
 and g.gen_id = mg.gen_id
 and mg.mov_id = md.mov_id
 group by d.dir_fname , d.dir_lname, g.gen_title
 order by 1 , 2 asc ;

 #Write a query in SQL to list all the movies with year and genres

 select m.mov_title , m.mov_year, g.gen_title
 from movie m , genres g , movie_genres mg
 where m.mov_id =mg.mov_id
 and g.gen_id =mg.gen_id

 #Write a query in SQL to list all the movies with year, genres, and name of the director.

 select m.mov_title , m.mov_year , g.gen_title , d.dir_fname , d.dir_lname
 from movie m , genres g , movie_genres mg , director d , movie_direction md
 where m.mov_id = mg.mov_id
 and g.gen_id = mg.gen_id
 and d.dir_id = md.dir_id
 and md.mov_id = m.mov_id;

 #Write a query in SQL to list all the movies with title, year, date of release, movie duration, 
 #and first and last name of the director which released before 1st january 1989,
 #and sort the result set according to release date from highest date to lowest

 select m.mov_title , m.mov_year , m.mov_time , m.mov_dt_rel ,  d.dir_fname , d.dir_lname
 from movie m , director d , movie_direction md
 where m.mov_id = md.mov_id
 and d.dir_id = md.dir_id
 and mov_dt_rel <'01/01/1989' 
 order by 4 desc;


 #Write a query in SQL to compute a report which contain the genres of those movies with
 #their average time and number of movies for each genres.

 select g.gen_title ,  avg(m.mov_time) , count (g.gen_title)
 from genres g  , movie m , movie_genres mg
 where g.gen_id = mg.gen_id
 and mg.mov_id = m.mov_id
 group by g.gen_title;

 #Write a query in SQL to find those lowest duration movies along with the year, director's name,
 #actor's name and his/her role in that production.

 select m.mov_title , min(m.mov_time) , m.mov_year ,  d.dir_fname , d.dir_lname , a.act_fname, a.act_lname, mc.role
 from movie m , director d , actor a , movie_cast mc , movie_direction md 
 where m.mov_id = mc.mov_id
 and md.mov_id = m.mov_id
 and d.dir_id = md.dir_id
 and mc.act_id = a.act_id 

 or

 select m.mov_title , q.Mini, m.mov_year ,  d.dir_fname , d.dir_lname , a.act_fname, a.act_lname, mc.role
 from movie m , director d , actor a , movie_cast mc , movie_direction md  , (select min(m.mov_time) as Mini from movie m) as q
 where m.mov_id = mc.mov_id
 and md.mov_id = m.mov_id
 and d.dir_id = md.dir_id
 and mc.act_id = a.act_id 
 and m.mov_time =q.Mini
 ;
 #Write a query in SQL to find all the years which produced a movie that received a rating of 3 or 4,
 #and sort the result in increasing order.

 select distinct m.year from movie m , rating r
 where m.mov_id = r.mov_id
 and r.rev_stars in (3 ,4)
 ORDER BY m.mov_year;

 #Write a query in SQL to return the reviewer name, movie title, and stars in an order that 
 #reviewer name will come first, then by movie title, and lastly by number of stars.


 select rw.rev_name,m.mov_title , r.rev_stars
 from movie m , rating r ,reviewer rw
 where
 m.mov_id = r.mov_id
 and rw.rev_id = r.rev_id
 order by 1 ,2 ,3

 #Write a query in SQL to find movie title and number of stars for each movie 
 #that has at least one rating and find the highest number of stars that movie received and sort the result by movie title

 select m.mov_title , max(r.rev_stars)
 from movie r , rating r
 where m.mov_id = r.mov_id
 group by m.mov_title
 order by 1 asc;

 #Write a query in SQL to find the directors first and last name together with the title of the movie(s) 
 #they directed and received the rating

 select d.dir_fname , d.dir_lname , m.mov_title , r.rev_stars
 from director d , movie m , movie_direction md 
 where m.mov_id = md.mov_id
 and r.mov_id = m.mov_id
 and d.dir_id = md.dir_id;


 #Write a query in SQL to find the movie title, actor first and last name, and the 
 #role for those movies where one or more actors acted in two or more movies

 select m.mov_title ,  a.act_fname, a.act_lname , mc.role
 from movie m , actor a, movie_cast mc
 where m.mov_id = mc.mov_id
 and a.act_id = mc.act_id
 and a.act_id in (select act_id from movie_cast  
                group by act_id 
                 having count(*)>=2
                );

 #Write a query in SQL to find the first and last name of a director 
 #and the movie he or she directed, and the actress appeared which first name was Claire 
 #and last name was Danes along with her role in that movie.

 select d.dir_fname , d.dir_lname , m.mov_title , a.act_fname,a.act_lname,mc.role
 from director d , movie m , movie_direction md , movie_cast mc , actor a
 where d.dir_id = md.dir_id
 and m.mov_id = md.mov_id
 and mc.mov_id = m.mov_id
 and mc.act_id = a.act_id
 and a.act_fname like '%Claire%'
 and a.act_lname like '%Danes%';

 #Write a query in SQL to find the first and last name of an actor with their role in 
 #the movie which was also directed by themselve.

 select m.mov_title , a.act_fname,a.act_lname , mc.role
 from movie m , actor a , movie_direction md , director d , movie_cast mc
 where m.mov_id = md.mov_id
 and a.act_id = mc.act_id
 and mc.mov_id = m.mov_id
 and md.dir_id = d.dir_id
 and a.act_fname =d.dir_fname
 and a.act_lname =d.dir_lname;

 #Write a query in SQL to find the cast list for the movie Chinatown.

 select a.act_fname , a.act_lname 
 from actor a , movie_cast mc , movie m
 where mc.act_id = a.act_id
 and mc.mov_id = m.mov_id
 and m.mov_title='Chinatown';

 #Write a query in SQL to find the movie in which the actor appeared whose first and last name are 'Harrison' and 'Ford'

 select m.mov_title
 from movie m , actor a , movie_cast mc
 where m.mov_id = mc.mov_id
 and a.act_id = mc.act_id
 and a.act_fname ='Harrison'
 and a.act_lname='Ford'

 #Write a query in SQL to find the highest-rated movie, and report its title, year, rating, and releasing country

 select m.mov_title , m.mov_year , m.mov_rel_country , max(r.rev_stars)
 from movie m , rating r 
 where m.mov_id =r.mov_id
 group by m.mov_title , m.mov_year , m.mov_rel_country;


 select m.mov_title , m.mov_year , m.mov_rel_country , r.rev_stars
 from movie m , rating r
 where m.mov_id =r.mov_id
 and r.rev_stars = (select max(rev_stars) from rating);

 #Write a query in SQL to list all the information of the actors who played a role in the movie 'Annie Hall'

 select a.act_fname , a.act_lname
 from actor a , movie m ,movie_cast mc
 where a.act_id =mc.act_id
 and m.mov_id = mc.mov_id
 and m.mov_title = 'Annie Hall';

 #Write a query in SQL to find the name of the director (first and last names) who directed a movie that 
 #casted a role for 'Eyes Wide Shut'. (using subquery)

 select d.dir_fname , d.dir_lname 
 from director d , movie_direction md
 where d.dir_id = md.dir_id
 and md.mov_id in (select mov_id from movie where mov_title = 'Eyes Wide Shut')

 #Write a query in SQL to list all the movies which released in the country other than UK. 

 select m.mov_title from movie m
 where m.mov_rel_country not in ('UK');
	# Write a query in SQL to find the name of all reviewers who have rated their ratings with a NULL value.
	select rev_name from reviewer , rating
	where reviewer.rev_id = rating.rev_id
	and rev_stars IS NULL;

	#Write a query in SQL to list the first and last names of all the actors who were cast in the
	#movie 'Annie Hall', and the roles they played in that production.
	select a.act_fname ,a.act_lname , mc.role
	from actor a , movie_cast mc , movie m
	where m.mov_id = mc.mov_id
	and mc.act_id = a.act_id
	and m.mov_title= 'Annie Hall'

	#Write a query in SQL to find the name of movie and director (first and last names) who directed a
	#movie that casted a role for 'Eyes Wide Shut'.
	SELECT d.dir_fname, d.dir_lname, m.mov_title
	FROM director d, movie m , movie_cast mc , movie_direction md
	where
	and m.mov_id=md.mov_id
	and md.dir_id = d.dir_id
	and mc.mov_id = m.mov_id
	m.mov_title = 'Eyes Wide Shut'

	#Write a query in SQL to find the name of movie and director (first and last names)
	#who directed a movie that casted a role as Sean Maguire
	select m.title , d.dir_fname , d.dir_lname
	from movies m , director d , movie_direction md , movie_cast mc
	where m.mov_id = md.mov_id
	and md.dir_id =d.dir_id
	and mc.mov_id = m.mov_id
	and mc.role like '%Sean Maguire%'


	#Write a query in SQL to list all the actors who acted in a movie before 1990 and also in a movie after 2000
	SELECT a.act_fname, a.act_lname, c.mov_title, c.mov_year
	FROM actor a, movie_cast b, movie c
	WHERE a.act_id=b.act_id
	AND b.mov_id=c.mov_id
	AND c.mov_year NOT BETWEEN 1990 and 2000;

	#Write a query in SQL to list first and last name of all the directors with number of genres movies the
	#directed with genres name, and arranged the result alphabetically with the first and last name of the director.

	SELECT d.dir_fname,d.dir_lname ,g.gen_title,count(g.gen_title)
	from director d , movie_direction md , genres g , movie_genres mg
	where d.dir_id=md.dir_id
	and g.gen_id = mg.gen_id
	and mg.mov_id = md.mov_id
	group by d.dir_fname , d.dir_lname, g.gen_title
	order by 1 , 2 asc ;

	#Write a query in SQL to list all the movies with year and genres

	select m.mov_title , m.mov_year, g.gen_title
	from movie m , genres g , movie_genres mg
	where m.mov_id =mg.mov_id
	and g.gen_id =mg.gen_id

	#Write a query in SQL to list all the movies with year, genres, and name of the director.

	select m.mov_title , m.mov_year , g.gen_title , d.dir_fname , d.dir_lname
	from movie m , genres g , movie_genres mg , director d , movie_direction md
	where m.mov_id = mg.mov_id
	and g.gen_id = mg.gen_id
	and d.dir_id = md.dir_id
	and md.mov_id = m.mov_id;

	#Write a query in SQL to list all the movies with title, year, date of release, movie duration,
	#and first and last name of the director which released before 1st january 1989,
	#and sort the result set according to release date from highest date to lowest

	select m.mov_title , m.mov_year , m.mov_time , m.mov_dt_rel , d.dir_fname , d.dir_lname
	from movie m , director d , movie_direction md
	where m.mov_id = md.mov_id
	and d.dir_id = md.dir_id
	and mov_dt_rel <'01/01/1989'
	order by 4 desc;


	#Write a query in SQL to compute a report which contain the genres of those movies with
	#their average time and number of movies for each genres.

	select g.gen_title , avg(m.mov_time) , count (g.gen_title)
	from genres g , movie m , movie_genres mg
	where g.gen_id = mg.gen_id
	and mg.mov_id = m.mov_id
	group by g.gen_title;

	#Write a query in SQL to find those lowest duration movies along with the year, director's name,
	#actor's name and his/her role in that production.

	select m.mov_title , min(m.mov_time) , m.mov_year , d.dir_fname , d.dir_lname , a.act_fname, a.act_lname, mc.role
	from movie m , director d , actor a , movie_cast mc , movie_direction md
	where m.mov_id = mc.mov_id
	and md.mov_id = m.mov_id
	and d.dir_id = md.dir_id
	and mc.act_id = a.act_id

	or

	select m.mov_title , q.Mini, m.mov_year , d.dir_fname , d.dir_lname , a.act_fname, a.act_lname, mc.role
	from movie m , director d , actor a , movie_cast mc , movie_direction md , (select min(m.mov_time) as Mini from movie m) as q
	where m.mov_id = mc.mov_id
	and md.mov_id = m.mov_id
	and d.dir_id = md.dir_id
	and mc.act_id = a.act_id
	and m.mov_time =q.Mini
	;
	#Write a query in SQL to find all the years which produced a movie that received a rating of 3 or 4,
	#and sort the result in increasing order.

	select distinct m.year from movie m , rating r
	where m.mov_id = r.mov_id
	and r.rev_stars in (3 ,4)
	ORDER BY m.mov_year;

	#Write a query in SQL to return the reviewer name, movie title, and stars in an order that
	#reviewer name will come first, then by movie title, and lastly by number of stars.


	select rw.rev_name,m.mov_title , r.rev_stars
	from movie m , rating r ,reviewer rw
	where
	m.mov_id = r.mov_id
	and rw.rev_id = r.rev_id
	order by 1 ,2 ,3

	#Write a query in SQL to find movie title and number of stars for each movie
	#that has at least one rating and find the highest number of stars that movie received and sort the result by movie title

	select m.mov_title , max(r.rev_stars)
	from movie r , rating r
	where m.mov_id = r.mov_id
	group by m.mov_title
	order by 1 asc;

	#Write a query in SQL to find the directors first and last name together with the title of the movie(s)
	#they directed and received the rating

	select d.dir_fname , d.dir_lname , m.mov_title , r.rev_stars
	from director d , movie m , movie_direction md
	where m.mov_id = md.mov_id
	and r.mov_id = m.mov_id
	and d.dir_id = md.dir_id;


	#Write a query in SQL to find the movie title, actor first and last name, and the
	#role for those movies where one or more actors acted in two or more movies

	select m.mov_title , a.act_fname, a.act_lname , mc.role
	from movie m , actor a, movie_cast mc
	where m.mov_id = mc.mov_id
	and a.act_id = mc.act_id
	and a.act_id in (select act_id from movie_cast
	group by act_id
	having count(*)>=2
	);

	#Write a query in SQL to find the first and last name of a director
	#and the movie he or she directed, and the actress appeared which first name was Claire
	#and last name was Danes along with her role in that movie.

	select d.dir_fname , d.dir_lname , m.mov_title , a.act_fname,a.act_lname,mc.role
	from director d , movie m , movie_direction md , movie_cast mc , actor a
	where d.dir_id = md.dir_id
	and m.mov_id = md.mov_id
	and mc.mov_id = m.mov_id
	and mc.act_id = a.act_id
	and a.act_fname like '%Claire%'
	and a.act_lname like '%Danes%';

	#Write a query in SQL to find the first and last name of an actor with their role in
	#the movie which was also directed by themselve.

	select m.mov_title , a.act_fname,a.act_lname , mc.role
	from movie m , actor a , movie_direction md , director d , movie_cast mc
	where m.mov_id = md.mov_id
	and a.act_id = mc.act_id
	and mc.mov_id = m.mov_id
	and md.dir_id = d.dir_id
	and a.act_fname =d.dir_fname
	and a.act_lname =d.dir_lname;

	#Write a query in SQL to find the cast list for the movie Chinatown.

	select a.act_fname , a.act_lname
	from actor a , movie_cast mc , movie m
	where mc.act_id = a.act_id
	and mc.mov_id = m.mov_id
	and m.mov_title='Chinatown';

	#Write a query in SQL to find the movie in which the actor appeared whose first and last name are 'Harrison' and 'Ford'

	select m.mov_title
	from movie m , actor a , movie_cast mc
	where m.mov_id = mc.mov_id
	and a.act_id = mc.act_id
	and a.act_fname ='Harrison'
	and a.act_lname='Ford'

	#Write a query in SQL to find the highest-rated movie, and report its title, year, rating, and releasing country

	select m.mov_title , m.mov_year , m.mov_rel_country , max(r.rev_stars)
	from movie m , rating r
	where m.mov_id =r.mov_id
	group by m.mov_title , m.mov_year , m.mov_rel_country;


	select m.mov_title , m.mov_year , m.mov_rel_country , r.rev_stars
	from movie m , rating r
	where m.mov_id =r.mov_id
	and r.rev_stars = (select max(rev_stars) from rating);

	#Write a query in SQL to list all the information of the actors who played a role in the movie 'Annie Hall'

	select a.act_fname , a.act_lname
	from actor a , movie m ,movie_cast mc
	where a.act_id =mc.act_id
	and m.mov_id = mc.mov_id
	and m.mov_title = 'Annie Hall';

	#Write a query in SQL to find the name of the director (first and last names) who directed a movie that
	#casted a role for 'Eyes Wide Shut'. (using subquery)

	select d.dir_fname , d.dir_lname
	from director d , movie_direction md
	where d.dir_id = md.dir_id
	and md.mov_id in (select mov_id from movie where mov_title = 'Eyes Wide Shut')

	#Write a query in SQL to list all the movies which released in the country other than UK.

	select m.mov_title from movie m
	where m.mov_rel_country not in ('UK');