Chapter 3: SQL

SQL stands for Structured Query Language.

See, programming languages are called languages because that is what they literally are. The language spoken by people from France is French. The language spoken by people from Spain is Spanish. So to effectively communicate with a French speaker, one would have to learn French.

To effectively communicate with databases, we use SQL.

Much like regular language, programming languages have their own "grammar" we call syntax. For example, in Western countries, when we write our full name down, we order it like "Given name, Surname" - or "Peng Hong Tey". But, as some of us may know in Eastern countries, it's the other way around - "郑 炳 泓" (Directly translated as "Tey Peng Hong"). This type of "convention" or "rule" we have to follow, you could say is the syntax of Western English vs Chinese.

Just like regular languages, programming languages can also be "translated" between one another. Here is an example: Translate the English phrase "Hello".

// English
Hello

// Chinese
你好

//========//

// Python
print("Hello")

// Java
System.out.println("Hello");

If we start thinking about programming languages not as a fancy math nerd skill that only people with high IQs have, and start thinking of them like regular languages used to communicate with computers, it becomes less scary.

The SQL Syntax

This is the Oxford English Dictionary for SQL. There are numerous ways to write the same sentence in English using different words, sentence structure, grammar, depending on where you are from, what your culture is etc.

Luckily, in SQL, there is literally a whopping total of SIX main ones to know. There are some other ones, but trust me it's still way less than any other human language.

How to use SQL to actually create a table from our ER diagrams

Let's grab our tables from the previous Chapter for the Netflix movies.

Movie table

"Entity" name = "movie"

Contains "attributes": "movie_id", "title", "year".

TABLE name = movie

COLUMNs to include = movie_id, title,year.

Visually, it looks like:

To create this table in SQL:

CREATE TABLE movie (
movie_id,
title,
year,
);

Running this will make you FAIL THE COURSE. The above example is WRONG. DEAD. WRONG. (Twice over, actually - the missing datatypes below, AND that trailing comma after year, right before the closing bracket. SQL wants no comma after the last column.)

The main reason: unlike humans, computers are not able to think for themselves (yet.... I know you're reading this Claude). It doesn't know what a "movie_id" is, or what the word "title" even means. So, we have to define those for them, which is where datatypes come in.

Aight that is all you need to fix that SQL code:

CREATE TABLE movie (
movie_id INT,
title VARCHAR(100),
year INT
);

There is your technically valid SQL syntax. There is your grammatically correct sentence. If you want to check if you have done it correctly, run DESCRIBE movie. The computer will then tell you what datatype you set for each column, its name, and other constraints.

Also, whenever you finish a sentence in English, you have to put a full stop. Whenever you finish a command in SQL, you end it with a );. Well, technically the equivalent to a full-stop is just ; but personally I always forget the close bracket and it looks like a sad face.

You don't wanna make SQL sad.

Sad SQL is bad SQL. );

Let's go ahead and complete the other tables while we're at it.

CREATE TABLE genre (
genre_id INT,
genre_name VARCHAR(50)
);

CREATE TABLE movie_genre (
genre_index INT,
movie_id INT,
genre_id INT
);

"But wait, Eric, you forgot to mark the Primary Keys! How am I supposed to uniquely identify a specific row without getting confused now?"

Did you read the title of this guide? It clearly says "SQL explained by an idiot". If you didn't expect that I don't know what you're expecting. Luckily, SQL has a solution. ALTER TABLE.

It's always good practice when coding or modelling anything to write down exactly what we wanna do in English first, then translate to the computer language.

"I want to make genre_id the primary key of the genre table, and movie_id the primary key of the movie table, and for movie_genre, the movie_id and genre_id are foreign keys and the genre_index is the primary key."

OK bet, to bolt the keys on after the fact, use ALTER TABLE.

ALTER TABLE genre
ADD PRIMARY KEY (genre_id);
--Command ONE runs after the first ;

ALTER TABLE movie
ADD PRIMARY KEY (movie_id);
--Command TWO runs after the second ;

ALTER TABLE movie_genre
ADD PRIMARY KEY (genre_index),
ADD FOREIGN KEY (genre_id) REFERENCES genre(genre_id),
ADD FOREIGN KEY (movie_id) REFERENCES movie(movie_id);
--Command THREE runs after the last ;

The reason why it's important to know WHEN each command has gone through is because if you have a typo in the second ALTER TABLE, so you edit the typo and run the whole thing again, ALTER TABLE genre runs as well. In this case it will give you an error saying genre_id is already a primary key, but what happens when you're adding or deleting things?

Of course, if you and I had more experience and correctly followed the ERD -> SQL guideline, the one-shot script is this:

-- Attempt 2: Creating the tables without forgetting keys and constraints
CREATE TABLE movie (
movie_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL,  
year INT
);

CREATE TABLE genre (
genre_id INT AUTO_INCREMENT PRIMARY KEY,
genre_name VARCHAR(50) UNIQUE
);

CREATE TABLE movie_genre (
genre_index INT AUTO_INCREMENT PRIMARY KEY,
movie_id INT,
genre_id INT,
FOREIGN KEY (movie_id) REFERENCES movie(movie_id),
FOREIGN KEY (genre_id) REFERENCES genre(genre_id),
UNIQUE (movie_id, genre_id)  
);

A couple of new words to learn: UNIQUE means it will return an error if someone attempts to enter a second row with the EXACT same value - so no two genres can share a genre_name, and no movie can be given the same genre twice. And, NOT NULL specifies the computer to return an error if someone attempts to enter nothing into the field.

AUTO_INCREMENT is useful when generating key values. It does exactly what it says. The first entry will be value 1, second 2 and so on - so you never have to supply or remember ID numbers yourself.

NOTE: This chapter is written in MySQL. I'm pretty sure the SQL lab we use is ANSI-style (different dialect) with a weird mix of standard MySQL in there, so three things differ there:

• Strings ALWAYS use 'single quotes',
• AUTO_INCREMENT and DESCRIBE are MySQL words that may not exist on some platforms (I tested it, it does on ours),
• The lecture declares keys inline at CREATE TABLE time (movie_id INT PRIMARY KEY) rather than bolting them on afterwards with ALTER TABLE. Inline-at-CREATE is the best-practice way (it's what Attempt 2 above does) - ALTER TABLE is for changing a table that already exists.

How to USE the tables. Actually.

SQL is a language designed for tables. We don't need fancy words for what it needs to do. Boil it down to the pure basics we just need to know:

• What table we're dealing with
• Are we inserting/retrieving/deleting data?
• What specific data are we doing that to.

In SQL, this is done with big chunky words SELECT,DELETE, UPDATE, INSERT (plus SET, which is not a command of its own - it's the part of UPDATE that does the changing).

Before we write any code, start in English.

"I want to add a new movie called 'The Odyssey' to the movies database, set its release year to 2025, and its genre to Action and Fantasy".

So, what we're doing is:

• Dealing with the table movie
• Inserting an entry to it, where the title is "The Odyssey", released in "2025".
• Assign the genre_name "Action" and "Fantasy" to it.

INSERT INTO movie (title,year) VALUES ('The Odyssey',2025);

-- Insert actual genre_name values into the genre table

INSERT INTO genre (genre_name) VALUES ('Action'),
('Adventure'),
('Horror');

INSERT INTO movie_genre (movie_id, genre_id) VALUES (1,1);
-- For movie with movie_id=1 (The Odyssey), its genre_id=1 (Action)
-- Let's also add the 'Fantasy' genre to the `genre` catalogue/table.

INSERT INTO genre (genre_name) VALUES ('Fantasy');
-- Because of AUTO_INCREMENT, we don't have to worry about genre_id.
-- Now apply new genre 'Fantasy' to The Odyssey's entry in movie_genre.

INSERT INTO movie_genre (movie_id, genre_id) VALUES (1,4);
--Where the `4` represents genre_id=4 which is 'Fantasy'

Following the same logic/structure, we can pretty much do anything.

"Change the genre of The Odyssey to be Action/Adventure instead of Action/Fantasy"

SELECT * FROM movie_genre;
--Shows me The Odyssey's genre rows so I can pick the right one to change.
-- In SQL * just means ALL.

UPDATE movie_genre SET genre_id = 2 WHERE genre_index = 2;
-- genre_index 2 is the row holding (movie 1, Fantasy); genre_id 2 is 'Adventure'.
-- Why do you think I didn't use WHERE movie_id = 1?

Why not WHERE movie_id = 1?

Because The Odyssey has TWO rows in movie_genre (Action and Fantasy), and movie_id = 1 matches BOTH. The update would turn both genres into Adventure - clobbering Action, and then trying to store the "Odyssey is Adventure" fact twice (which our UNIQUE (movie_id, genre_id) rule would reject with an error). WHERE targets rows; make sure it targets ONLY the rows you mean.

WHERE is your bread and butter to specify- well- where in the table you're making the change.

"I want to see all the movies that were released in 2025"

SELECT * FROM movie
WHERE year=2025;

-- "Select all entries in the table movie where the year it was released = 2025."
-- Literally English.

"I want to delete movies that were released on or before 2005"

DELETE FROM movie WHERE year <= 2005;
-- No WHERE = deletes EVERY row in the table. There is no "are you sure?" popup.

The Dreaded JOIN

"I want to list ALL movies that are considered the HORROR genre, released before the year 2015."

Run through our checklist:

• What table are we using? Movies? Genre? Both? More?
• What are we trying to do? List stuff, so use SELECT
• What specifically are we trying to list? - Name of the movie, the genre, and its release date.

You can see the problem here is we have to do some magic with MULTIPLE different tables.

• The name of the movie lives in movie
• The release year of the movie lives in movie
• The genre name of the movie lives in genre
• What genre belongs to what movie lives in movie_genre

You COULD technically just go:

SELECT * FROM movie WHERE year < 2015;

Then, manually list all the movie_id entries, and match them to the movie_genre table using the key from genre table. But good engineers are lazy engineers.

This is where we need to use JOIN. It joins the columns from different tables together, so we can display information much cleaner.

SELECT movie.title, genre.genre_name, movie.year
FROM movie
JOIN movie_genre ON movie.movie_id = movie_genre.movie_id
JOIN genre ON movie_genre.genre_id = genre.genre_id
WHERE movie.year < 2015 AND genre.genre_name = 'Horror';

-- In English --
-- List the title, genre and release year of movies --
-- Start with the movie table --
-- Link movie to movie_genre table using the foreign key --
-- Link genre to movie_genre table using the foreign key --
-- Establish the conditions --

So, JOIN is used to create a temporary big combo table so you can query all the columns you need at once. You can ALSO do this:

SELECT movie.title, genre.genre_name, movie.year
FROM movie, movie_genre, genre
WHERE movie.movie_id = movie_genre.movie_id
  AND genre.genre_id = movie_genre.genre_id
  AND genre.genre_name = 'Horror'
  AND movie.year < 2015;

Which will return the EXACT same result. They are both valid ways of doing the SAME thing - the comma version is just the old-school way of writing joins, where the join conditions live in the WHERE clause mixed in with the actual filters.