---
tags: databases, postgresql, movies, solutions
---

# Movies Practical - Some solutions

> ### Please note: :information_source:
> These are *some* solutions to the questions, but not the only solutions.
> If you have another soloution, **double check that your solution and ours yield the same result and answer**. If it does, then _congratulations :confetti_ball:!!!_ You found another solution. It doesn't mean your solution isn't right. SQL is a very expressive and allowing language, thus it makes it possible to solve the same problem in many ways.

___

### Table of contents

[TOC]

### Q1: Show a list of all `movies` titles

SELECT title 
FROM movies;

### Q2: How many `genres` are there in the database?

SELECT count(*) 
FROM movies;

### Q3: Which movie made **the highest** revenue?

SELECT title, revenue 
FROM movie 
ORDER BY revenue DESC
LIMIT 1;

### Q4: Show a list of movies where runtime is over **200 mins**

SELECT title, runtime 
FROM movie 
WHERE runtime > 200;

### Q5: Show a list of movies that had the **top 10 biggest budgets**

SELECT * 
FROM movie
ORDER BY budget DESC
LIMIT 10;

### Q6: How many **languages** are there in the database?

SELECT count(*) 
FROM language;

### Q7: Show a list of **all the characters** in Star Wars

SELECT title, character_name 
FROM movie
LEFT JOIN movie_cast
ON movie.movie_id = movie_cast.movie_id
WHERE movie.title = 'Star Wars';

### Q8: List all of `Johnny Depps` characters

SELECT person_name, character_name 
FROM person
INNER JOIN movie_cast
ON person.person_id = movie_cast.person_id
WHERE person_name ='Johnny Depp';

### Q9: What genre(s) are the Star Wars movies?

SELECT genre_name
FROM movie
INNER JOIN movie_genres
ON movie.movie_id = movie_genres.movie_id
INNER JOIN genre
ON movie_genres.genre_id = genre.genre_id
WHERE movie.title ='Star Wars';

### Q10: How many people played the role of `Test Site Technician` in `Spider-Man 3`?

SELECT count(person_id) 
FROM movie
INNER JOIN movie_cast
ON movie.movie_id = movie_cast.movie_id
WHERE title ='Spider-Man 3' AND character_name ='Test Site Technician';