SQL practice

Question 1: Can you get all data about movies?

SELECT * FROM movies;

Question 2: How do you get all data about directors?

SELECT * FROM directors;

Question 3: Check how many movies are present in iMDB.

SELECT COUNT(id) FROM movies;

Question 4: Find these 3 directors: James Cameron; Luc Besson; John Woo

SELECT * FROM directors WHERE name=”James Cameron” OR name=”Luc Besson” OR name=”John Woo”;

Question 5: Find all directors with name starting with Steven.

SELECT * FROM directors WHERE name LIKE “Steven%”;

Question 6: Count female directors.

SELECT COUNT(gender) FROM directors WHERE gender=1;

Question 7: Find the name of the 10th first women directors?

SELECT * FROM directors

ORDER BY ID DESC

LIMIT 10

Question 8: What are the 3 most popular movies?

SELECT * FROM movies

ORDER BY popularity DESC

LIMIT 3

Question 9: What are the 3 most bankable movies?

SELECT * FROM movies

ORDER BY revenue DESC

LIMIT 3

Question 10: What is the most awarded average vote since the January 1st, 2000?

SELECT vote_average FROM movies

GROUP BY vote_average

ORDER BY count(vote_average) DESC

LIMIT 1

Question 11: Which movie(s) were directed by Brenda Chapman?

SELECT * FROM movies WHERE director_id=(SELECT id FROM directors WHERE name='Brenda Chapman')

Question 11 (join):

SELECT * FROM movies

INNER JOIN directors ON movies.director_id=directors.id WHERE directors.name='Brenda Chapman'

Question 12: Whose director made the most movies?

SELECT * FROM directors WHERE id=(SELECT director_id FROM movies)

GROUP BY id

ORDER BY count(id) DESC

LIMIT 1

Question 12 (join):

SELECT * FROM directors

INNER JOIN movies ON movies.director_id=movies.director_id

GROUP BY directors.id

ORDER BY count(directors.id) DESC

LIMIT 1

 

Question 13: Whose director is the most bankable?

SELECT * FROM directors

INNER JOIN movies ON movies.director_id=directors.id

ORDER BY movies.revenue DESC

LIMIT 1