Comandos básicos SQL-II

No decorrer do dia-a-dia precisamos testar comandos DDL, DML em nosso SGBD e muitas vezes podemos não dispor de um ambiente de homologação para tais tarefas. Dessa necessidade surgiram os bancos de dados de exemplos para suprir essas necessidades. Estes bancos também são muito utilizados para estudo para quem deseja se aprofundar e aprender mais sobre a linguagem SQL, como é este caso. Para estas queries utilizei diversas listas que encontrei na internet em vários lugares. Como não me recordo da fonte vou enumerá-los aqui como se fosse uma lista de exercícios mesmo para ficar mais fácil o enunciado e a resolução.

O database utilizado para estas queries é o Sakila e essas são as suas tabelas:

MariaDB [sakila]> show tables;
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
23 rows in set (0.00 sec)

Vamos as queries!

1) Os 5 países com o maior número de clientes;

MariaDB [sakila]> select a.country as PAIS, count(a.country) as QTDADE from customer_list a group by a.country order by QTDADE desc limit 5;
+---------------+--------+
| PAIS          | QTDADE |
+---------------+--------+
| India         |     60 |
| China         |     53 |
| United States |     36 |
| Japan         |     31 |
| Mexico        |     30 |
+---------------+--------+
5 rows in set (0.00 sec)

2) Todos os clientes que alugaram filmes de terror;

MariaDB [sakila]> select a.first_name as NOME, a.last_name as SOBRENOME, g.name as CATEGORIA from customer a 
inner join rental c on c.customer_id=a.customer_id 
inner join inventory d on c.inventory_id=d.inventory_id  
inner join film e on d.film_id=e.film_id 
inner join film_category f on e.film_id=f.film_id 
inner join category g on f.category_id=g.category_id 
where g.name='Horror' limit 5;

+---------+-----------+-----------+
| NOME    | SOBRENOME | CATEGORIA |
+---------+-----------+-----------+
| PENNY   | NEAL      | Horror    |
| BRANDON | HUEY      | Horror    |
| LUCILLE | HOLMES    | Horror    |
| KATHRYN | COLEMAN   | Horror    |
| AMBER   | DIXON     | Horror    |
+---------+-----------+-----------+
5 rows in set (0.00 sec)

3) Todos os clientes que alugaram mais de 3 filmes de terror;

MariaDB [sakila]> select a.first_name as NOME, a.last_name as SOBRENOME, count(a.customer_id) as QTDADE, g.name as CATEGORIA from customer a inner join rental c on c.customer_id=a.customer_id  inner join inventory d on c.inventory_id=d.inventory_id inner join film e on d.film_id=e.film_id  inner join film_category f on e.film_id=f.film_id inner join category g on f.category_id=g.category_id where g.name='Horror' group by a.customer_id  having QTDADE > 3 order by a.first_name asc limit 5;

+--------+-----------+--------+-----------+
| NOME   | SOBRENOME | QTDADE | CATEGORIA |
+--------+-----------+--------+-----------+
| ANA    | BRADLEY   |      5 | Horror    |
| ANDREA | HENDERSON |      4 | Horror    |
| BERTHA | FERGUSON  |      4 | Horror    |
| BYRON  | BOX       |      4 | Horror    |
| CARL   | ARTIS     |      4 | Horror    |
+--------+-----------+--------+-----------+
5 rows in set (0.00 sec)

4) Todos os filmes de um determinado ator;

MariaDB [sakila]> select a.first_name as NOME, a.last_name as SOBRENOME, c.title as FILME from actor a  inner join film_actor b  on a.actor_id=b.actor_id  inner join film c  on b.film_id=c.film_id  where a.first_name like 'PENELOPE' and a.last_name='GUINESS' limit 5;

+----------+-----------+-----------------------+
| NOME     | SOBRENOME | FILME                 |
+----------+-----------+-----------------------+
| PENELOPE | GUINESS   | ACADEMY DINOSAUR      |
| PENELOPE | GUINESS   | ANACONDA CONFESSIONS  |
| PENELOPE | GUINESS   | ANGELS LIFE           |
| PENELOPE | GUINESS   | BULWORTH COMMANDMENTS |
| PENELOPE | GUINESS   | CHEAPER CLYDE         |
+----------+-----------+-----------------------+
5 rows in set (0.00 sec)

5) Todos os filmes alugados por um cliente;

MariaDB [sakila]> select a.first_name as NOME, a.last_name as SOBRENOME, d.title as FILME from customer a   inner join rental b on a.customer_id=b.customer_id  inner join inventory c on b.inventory_id=c.inventory_id  inner join film d on c.film_id=d.film_id   where a.first_name='SANDRA' and a.last_name='MARTIN' limit 5;

+--------+-----------+---------------------+
| NOME   | SOBRENOME | FILME               |
+--------+-----------+---------------------+
| SANDRA | MARTIN    | FEUD FROGMEN        |
| SANDRA | MARTIN    | HALLOWEEN NUTS      |
| SANDRA | MARTIN    | MOSQUITO ARMAGEDDON |
| SANDRA | MARTIN    | EGG IGBY            |
| SANDRA | MARTIN    | SWEDEN SHINING      |
+--------+-----------+---------------------+
5 rows in set (0.00 sec)

6) Todos os nomes de idiomas (languages) ordenado;

MariaDB [sakila]> select a.name as IDIOMA from language a order by a.name asc;
+----------+
| IDIOMA   |
+----------+
| English  |
| French   |
| German   |
| Italian  |
| Japanese |
| Mandarin |
+----------+
6 rows in set (0.00 sec)

7) O nome completo (first, last) de atores (actors) with “SON” no sobrenome, ordenado pelo primeiro nome;

MariaDB [sakila]> select a.first_name as NOME, a.last_name as SOBRENOME from actor a where a.last_name like '%son%' order by a.first_name asc;
+-----------+-----------+
| NOME      | SOBRENOME |
+-----------+-----------+
| ALBERT    | JOHANSSON |
| ANGELA    | HUDSON    |
| BETTE     | NICHOLSON |
| CHRISTIAN | NEESON    |
| JAYNE     | NEESON    |
| MATTHEW   | JOHANSSON |
| MERYL     | GIBSON    |
| RAY       | JOHANSSON |
| WILL      | WILSON    |
+-----------+-----------+
9 rows in set (0.00 sec)

8) Criar uma lista de categorias e o numero de filmes de cada categoria;

MariaDB [sakila]> select a.name as CATEGORIA, count(a.name) as QTDADE from category a inner join film_category b on a.category_id=b.category_id group by a.name order by QTDADE desc limit 5;
+-------------+--------+
| CATEGORIA   | QTDADE |
+-------------+--------+
| Sports      |     74 |
| Foreign     |     73 |
| Family      |     69 |
| Documentary |     68 |
| Animation   |     66 |
+-------------+--------+
5 rows in set (0.00 sec)

9) Criar uma lista de atores e o numero de filmes (qtade) de cada ator;

MariaDB [sakila]> select a.first_name as NOME, a.last_name as SOBRENOME, count(c.title) as QTDADE_DE_FILMES from actor a inner join film_actor b on a.actor_id=b.actor_id inner join film c on c.film_id=b.film_id group by NOME order by a.first_name asc limit 5;
+--------+-----------+------------------+
| NOME   | SOBRENOME | QTDADE_DE_FILMES |
+--------+-----------+------------------+
| ADAM   | GRANT     |               40 |
| AL     | GARLAND   |               26 |
| ALAN   | DREYFUSS  |               27 |
| ALBERT | NOLTE     |               64 |
| ALEC   | WAYNE     |               29 |
+--------+-----------+------------------+
5 rows in set (0.01 sec)

10) – Qtdade de filmes ingleses (English) que possuem a categoria “Documentary”;

MariaDB [sakila]> select b.name as IDIOMA, count(a.title) as QTDADE from film a inner join language b on a.language_id=b.language_id inner join film_category as c on a.film_id=c.film_id inner join category as d on c.category_id=d.category_id where b.name='English' and d.name='Documentary';
+---------+--------+
| IDIOMA  | QTDADE |
+---------+--------+
| English |     68 |
+---------+--------+
1 row in set (0.00 sec)

Done.