SQL Tutorial: Building a Movie Database 🎬📚

Welcome to this comprehensive guide on SQL (Structured Query Language)! In this tutorial, we'll build a Movie Database to help you understand the basics and beyond of SQL. By the end of this lesson, you'll be equipped to manage and query data in various real-world scenarios. Let's dive in! 🐳

What is SQL? 💡

SQL is a language used to communicate with databases. It allows us to create, modify, and query databases efficiently. In simpler terms, SQL is like speaking the database's language to perform various tasks.

Setting Up Our Movie Database 📝

First, let's create our Movie Database by setting up tables for Movies, Actors, and Genres.

sql

CREATE DATABASE MovieDB;
USE MovieDB;

CREATE TABLE Movies (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    release_year INT,
    genre_id INT,
    director VARCHAR(255)
);

CREATE TABLE Actors (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    birth_year INT,
    nationality VARCHAR(255)
);

CREATE TABLE Genres (
    id INT PRIMARY KEY,
    genre VARCHAR(255)
);

📝 Note: We've created three tables (Movies, Actors, Genres) with respective columns and data types.

Inserting Data 📝

Now, let's add some data to our tables.

sql

INSERT INTO Movies (id, title, release_year, genre_id, director)
VALUES (1, 'The Godfather', 1972, 1, 'Francis Ford Coppola');

INSERT INTO Actors (id, name, birth_year, nationality)
VALUES (1, 'Al Pacino', 1940, 'USA');

INSERT INTO Genres (id, genre)
VALUES (1, 'Crime');

📝 Note: We've inserted records into each table, associating the correct genre ID with 'The Godfather.'

Queries 📝

Now that we have data, let's learn how to query it.

Finding All Movies 💡

sql

SELECT * FROM Movies;

Finding Movies by Genre 💡

sql

SELECT * FROM Movies WHERE genre_id = 1;

Finding Movies by Director 💡

sql

SELECT * FROM Movies WHERE director = 'Francis Ford Coppola';

📝 Note: You can filter data by various conditions in SQL.

Joining Tables 💡

Let's join our tables to get more specific data.

sql

SELECT Movies.title, Actors.name AS actor
FROM Movies
JOIN Actors ON Movies.id = Actors.id;

📝 Note: The JOIN keyword allows us to combine data from multiple tables based on a common column.

Advanced Queries 💡

Finding Movies by Release Year Range 💡

sql

SELECT * FROM Movies WHERE release_year BETWEEN 1970 AND 1980;

Counting Movies by Genre 💡

sql

SELECT genre, COUNT(*) as movie_count
FROM Movies
JOIN Genres ON Movies.genre_id = Genres.id
GROUP BY genre;

📝 Note: COUNT() function counts the number of records, and GROUP BY groups the results by the specified column.

Quiz 📝

That's it for today! We've covered the basics of SQL, including creating tables, inserting data, and running queries. Stay tuned for more advanced topics in future lessons! 🚀