In this blog post I will walk through basic SQL queries using the database described in my previous post.
SELECT *
FROM directors
LIMIT 5
- This query selects all (*) columns from the directors table and return the first 5 rows.
SELECT COUNT(*)
FROM directors
- This query aggregates the whole table (*) to return the total row count.
- MIN, MAX, AVG (minimum, maximum, average) can also be aggregated
SELECT directors.name
FROM directors
ORDER BY directors.name ASC
- This query selects just the column name from the directors table and returns all rows sorted in ascending alphabetical order.
SELECT title
FROM movies
WHERE UPPER(title like '% LOVE %')
- This query selects all movies which contain the word 'love' in their title and returns them.
- UPPER converts the title to uppercase before comparing it to the '% LOVE %' regex. This way the query is case-agnostic and will return titles containing 'Love', 'love', etc.
SELECT movies.title
FROM movies
WHERE movies.minutes > 500
- This query selects all movies longer than 500 minutes long.
- As you can see, WHERE can filter based on <,>,= comparisons too not just string pattern matches.
SELECT movies.title, movies.genres, directors.name
From movies
JOIN directors ON movies.director_id = directors.id
- This query returns the list of movies with their genres and directors name
- JOIN combines column data from 2 or more tables based on given key, in this case joining columns where 'movie' foreign key matches 'directors' primary key, thus matching up movies with their respectice directors.
- You must use the fully qualified name (tablename.columnname) with JOIN so the query knows which column comes from which table
Thanks for reading!