SELECT Statements
This YouTube video was created by Steve Griffith.
Basic SELECT Statements
The SELECT statement is used to retrieve information from one or more database tables. It starts with the SELECT keyword followed by a list of columns to return from the specified table or tables. Columns are separate by a comma. The asterisk (*) can be used to return all columns from the specified table or tables.
NOTE
SQL is a case-insensitive language, meaning all keywords, statements and clause maybe in any casing. However, it is common practice to write them in all caps.
# Getting all movies with all columns
SELECT * FROM `movies`;
# Getting all movies with `movie_title` and `director` columns
SELECT `movie_title`, `director` FROM `movies`;
Filtering SELECT Statements
It is often the case that we will want to retrieve a specific row or rows from a table.
This filtered result can be accomplished using the WHERE clause. The WHERE clause is followed by a condition that is tested against each row. If the row meets that condition, it will be returned.
# Getting all movies with an `movie_id` greater than 10
SELECT `movie_id`, `movie_title`, `director`
FROM `movies`
WHERE `movie_id` > 10;
# Getting the movie with a `movie_id` of 17
SELECT `movie_id`, `movie_title`, `director`
FROM `movies`
WHERE `movie_id` = 17;
# Getting the movie with the `movie_title` of "Labyrinth"
SELECT `movie_id`, `movie_title`, `director`
FROM `movies`
WHERE `movie_title` = "Labyrinth";
Using Wildcards and LIKE
It is possible to search for only part of a string using wildcards and the LIKE operator. A wildcard serves as a placeholder for one or more characters in a string. The following are the
| Symbol | Description | Example |
|---|---|---|
% | Represents zero or more characters | h%t finds hot, hat, hit and heat |
_ | Represents a single character | h_t finds hot, hat, hit, but not heat |
# Getting all movies whose `movie_title` contains the letter 'a'
SELECT `movie_id`, `movie_title`, `director`
FROM `movies`
WHERE `movie_title` LIKE "%a%";
# Getting all movies whose `movie_title` starts with the letter 'G'
SELECT `movie_id`, `movie_title`, `director`
FROM `movies`
WHERE `movie_title` LIKE "G%";
# Getting all movies whose `movie_title` ends with the letter 't'
SELECT `movie_id`, `movie_title`, `director`
FROM `movies`
WHERE `movie_title` LIKE "%t";
Using Multiple Filters
The AND and OR operators are used to filter records based on more than one condition. The AND operator displays a record if all the conditions separated by AND are TRUE. The OR operator displays a record if any of the conditions separated by OR is TRUE.
# Getting all movies whose `movie_title` ends with the letter 't'
# AND `director` starts with "Luc"
SELECT `movie_id`, `movie_title`, `director`
FROM `movies`
WHERE `movie_title` LIKE "%t"
AND `director` LIKE "Luc%";
# Getting all movies whose `director` starts with "Luc" OR "Dean"
SELECT `movie_id`, `movie_title`, `director`
FROM `movies`
WHERE `director` LIKE "Luc%"
OR `director` LIKE "Dean%";
Sorting SELECT Statements
It is possible to sort the results using the ORDER BY clause. The ORDER BY clause is followed by the list of columns to sort. Columns can be sorted in ascending (ASC) or descending order (DESC) with ascending being the default.
# Getting all movies sorted by the `year`
SELECT `movie_id`, `movie_title`, `director`, `year`
FROM `movies`
ORDER BY `year`;
# Getting all movies sorted by `movie_title` in reverse alphabetical
SELECT `movie_id`, `movie_title`, `director`, `year`
FROM `movies`
ORDER BY `movie_title` DESC;
To sort multiple columns, place a comma between each column. The rows will be sorted in the order of the columns.
# Getting all movies sorted by the `year`, then by movie_title
SELECT `movie_id`, `movie_title`, `director`, `year`
FROM `movies`
ORDER BY `year`, `movie_title`;