Movie Database SQL
This database contains tables for movie information such as actors, movies and director. It also has related tables such as language and genre.
Example queries:
Ask for a list of all the actor names that appear in a movie. Sort the results by first name and last name. Count how many times each actor appears in the same movie as Kevin Bacon.
Actors
The movie actors table stores records for the people who act in movies. Each actor has a first name, last name, and birth year. The database also has a table that lists all the movies an actor has appeared in.
The movies table has a many-to-many relationship with the director table. This is because a movie can have multiple directors. To establish this link, the movie_id field in the movies table is linked to the director_id field in the directors table via a foreign key.
The movie_cast table has a similar many-to-many relationship with the directors table, because a movie can have multiple actors. The movie_cast table also has a cast_order field, which indicates which role an actor plays in a particular movie.
Movies
Movies are a form of visual communication that use moving images and sound to tell stories or inform (help people learn). They have been a major part of contemporary culture for over a century, engaging audiences worldwide with their characters and visuals. They have many effects, including causing people to laugh, cry, or feel afraid.
There are also many different genres of movies. Each has its own personality, and is influenced by the actors and directors who create them. Some viewers only watch movies that fall within a particular genre, while others enjoy watching all types of films.
The movies table contains records for each movie, including a description and a year of release. It also has a foreign key that references the id in the movies_directors table. This is a standard many-to-many relationship, and you will often see it in databases. Movies also have a rating, which is based on the number of votes received on IMDb.
Directors
Directors are responsible for the creative vision of the film and often have relationships with all departments. They may also be influential in a movie’s box office success, as people are more likely to go see it if they trust the director’s style and reputation. Directors work closely with the actors, producers, and casting directors during pre-production and also work on set with the director of photography and the first assistant director.
Directors also have a great impact on a movie’s final product, and some directors have enjoyed fame as virtual national treasures whose movies bring cultural glory to their countries. Examples include Mizoguchi Kenji and Kurosawa Akira of Japan, Satyajit Ray of India, Federico Fellini of Italy, and Luis Bunuel of Spain.
The movie_crew table stores all crew members for each movie. It contains two columns: movie_id and person_id. These two tables are linked by a foreign key, called director_id. The SELECT statement below shows how to list all the movies directed by someone with director_id equal to that value.
People
There is a movie_cast table that stores records for people in a movie. The record in this table includes the person’s name and cast_order. The cast_order is used to indicate the order in which they appear in a movie. There is also a movie_languages table that captures the original and spoken languages in a movie, as well as a movie_genres table to record the genre of the movie.
There’s even a movies_suggested_by table that stores suggested movies for each director. The database also has a ratings table to capture ratings for movies.
To query the database, you’ll use the id column in each table. For example, if you want to know which movies Tom Cruise has acted in, then you’d query the actors table and look for his id number. To get more complex results, you’d use a join between the tables. For instance, you might join the actors table with the directors table by using the director_id field in both tables as a foreign key.