My beloved SQL: practical, illustrated, introductory examples

I know that you had one of the experiences:

1. Dealing with a troublesome third-party system where the easier “solution” to some issue is hammering the database;
2. Producing some advanced report;
3. Trying to find clues on data.

If you don’t know what i’m talking about, maybe you have to worry yourself. I explain: it means that you never faced a challenging professional experience, or you’re beginning in the incredible world of IT. Anyway, here is not the place to waste time with it, let’s go ahead!

See the picture below, where we show an Entity-Relationship Diagram featured in DBeaver software (i strongly recommend using this universal database management tool – it supports various DataBase Management Systems):

Using this model, imagine we need to show all the rows of ACTOR table where the first_name field has the string “OLIV”. Easy task:

SELECT * FROM ACTOR WHERE UPPER(FIRST_NAME) LIKE '%OLIV%'

Quite good! We used the operator LIKE along with the brilliant strategy of uppercasing first_name before comparison.

Now you need to show the name of the actors and the names of the movies they acted. Too easy:

SELECT a.first_name AS ACTOR_NAME, f.title AS MOVIE_NAME
FROM actor a, film f, film_actor fa
WHERE f.film_id = fa.film_id AND a.actor_id = fa_actor_id
ORDER BY a.first_name;

You can do the same using INNER JOIN:

SELECT a.first_name AS ACTOR_NAME, f.title AS MOVIE_NAME
FROM film_actor fa
INNER JOIN film f ON f.film_id = fa.film_id
INNER JOIN actor a ON a.actor_id = fa_actor_id
ORDER BY a.first_name;