6.2 Retrieve Data with SELECT Statements

Select ID, first name, and last name from sakila.actor

SELECT actor_id as ID, first_name as 'First Name', last_name as 'Last Name' from sakila.actor;

The output is:

+-----+-------------+--------------+
| ID  | First Name  | Last Name    |
+-----+-------------+--------------+
|   1 | PENELOPE    | GUINESS      |
|   2 | NICK        | WAHLBERG     |
|   3 | ED          | CHASE        |
|   4 | JENNIFER    | DAVIS        |

...

| 198 | MARY        | KEITEL       |
| 199 | JULIA       | FAWCETT      |
| 200 | THORA       | TEMPLE       |
+-----+-------------+--------------+
OK, 200 records retrieved in (0.0028 sec)
 

Select ID, first name, and last name from sakila.actor using WHERE

SELECT actor_id, first_name, last_name FROM sakila.actor WHERE first_name LIKE 'Cate';

The output is:

+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|      128 | CATE       | MCQUEEN   |
|      141 | CATE       | HARRIS    |
+----------+------------+-----------+
OK, 2 records retrieved in 0.991ms

Select ID, first name, and last name from sakila.actor using WHERE and a wildcard

SELECT actor_id, first_name, last_name FROM sakila.actor WHERE last_name like '%han%';

The output is:

+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|        8 | MATTHEW    | JOHANSSON |
|       64 | RAY        | JOHANSSON |
|      146 | ALBERT     | JOHANSSON |
+----------+------------+-----------+      
OK, 3 records retrieved in 0.996ms

Select count of ratings from sakila.film and group by rating

SELECT rating AS label, count(rating) AS value FROM sakila.film GROUP BY rating;

The output is:

+--------+-------+
| Rating | Count |
+--------+-------+
| PG     |   194 |
| G      |   178 |
| NC-17  |   210 |
| PG-13  |   223 |
| R      |   195 |
+--------+-------+
OK, 5 records retrieved in 1.905ms

Select language and count of languages from sakila.language and sakila.film

SELECT l.name AS Language, count(f.film_id) AS Count FROM sakila.language l, 
sakila.film f WHERE f.language_id = l.language_id 
GROUP BY f.language_id ORDER BY COUNT DESC;

The output is:

+----------+-------+
| Language | Count |
+----------+-------+
| English  |   439 |
| German   |   246 |
| Italian  |   124 |
| Japanese |    96 |
| Mandarin |    76 |
| French   |    19 |
+----------+-------+
OK, 6 records retrieved in 1.425ms