Aulas práticas - Ficha 6

Bases de Dados (CC2005), Dep. Ciência de Computadores, FCUP

Eduardo R. B. Marques, DCC/FCUP

Objectivos: resolução de exercícios versando Álgebra Relacional e SQL no servidor Mooshak.

Referências:

1

Escreva uma instrução SELECT para obter os registos correspondentes à seguinte sequência de operações em álgebra relacional para a BD MovieStream:

\[ \begin{array}{@{}l@{}} R_0 \leftarrow \sigma_{\:{\rm Duration} \:\ge\: 180\:} ({\rm MOVIE}) \\ R_1 \leftarrow \pi_{\:{\rm Title, Year}\:} (R_0) \end{array} \]

Resultado esperado (a ordem é irrelevante, não precisa de usar ORDER BY):

+-----------------------------------------------+------+
| Lawrence of Arabia                            | 1962 |
| Gone with the Wind                            | 1939 |
| Shichinin no samurai                          | 1954 |
| The Wolf of Wall Street                       | 2013 |
| The Lord of the Rings: The Return of the King | 2003 |
| Pearl Harbor                                  | 2001 |
| The Deer Hunter                               | 1978 |
| Schindler's List                              | 1993 |
| Once Upon a Time in America                   | 1984 |
| Grindhouse                                    | 2007 |
| The Hateful Eight                             | 2015 |
| Ben-Hur                                       | 1959 |
| Gandhi                                        | 1982 |
| The Godfather: Part II                        | 1974 |
| King Kong                                     | 2005 |
| The Green Mile                                | 1999 |
| Titanic                                       | 1997 |
| Dances with Wolves                            | 1990 |
| Magnolia                                      | 1999 |
+-----------------------------------------------+------+
19 rows in set (0.00 sec)

2

Escreva uma instrução SELECT para obter os registos correspondentes à seguinte sequência de operações em álgebra relacional para a BD MovieStream:

\[ \begin{array}{@{}l@{}} R_0 \leftarrow \pi_{\:{\rm Name, City, Country}\:} ({\rm CUSTOMER}) \\ R_1 \leftarrow \sigma_{\: {\rm Name}\: {\rm LIKE} \: {\rm `Ch\%`} \:} (R_0) \end{array} \]

Resultado esperado (a ordem é irrelevante, não precisa de usar ORDER BY):

+-------------------+---------------------+------------------+
| Name              | City                | Country          |
+-------------------+---------------------+------------------+
| Christine Roberts | Faaa                | French Polynesia |
| Cheryl Murphy     | Mysore              | India            |
| Christina Ramirez | al-Hawiya           | Saudi Arabia     |
| Charlotte Hunter  | guas Lindas de Gois | Brazil           |
| Charlene Alvarez  | Zanzibar            | Tanzania         |
| Christy Vargas    | Datong              | China            |
| Charles Kowalski  | Sungai Petani       | Malaysia         |
| Christopher Greco | Brescia             | Italy            |
| Chris Brothers    | Gijn                | Spain            |
| Chad Carbone      | Katihar             | India            |
| Charlie Bess      | Baiyin              | China            |
| Chester Benner    | Suihua              | China            |
| Christian Jung    | Amroha              | India            |
+-------------------+---------------------+------------------+
13 rows in set (0.00 sec)

3

Escreva uma instrução SELECT para obter os registos correspondentes à seguinte sequência de operações em álgebra relacional para a BD MovieStream:

\[ \begin{array}{@{}l@{}} R_0 \leftarrow \sigma_{\:{\rm Year(StreamDate)} \:=\: 2018\:} ({\rm STREAM}) \\ R_1 \leftarrow \mathcal{F}_{\: {\rm SUM(Charge)} \:} (R_0) \end{array} \]

Resultado esperado:

+-------------+
|    55774.00 |
+-------------+
1 row in set (0.01 sec)

4

Escreva uma instrução SELECT para obter os registos correspondentes à seguinte sequência de operações em álgebra relacional para a BD MovieStream:

\[ \begin{array}{@{}l@{}} R \leftarrow {}_{ {\rm Year(StreamDate)} }\mathcal{F}_{\: {\rm COUNT(*)},\: {\rm SUM(Charge)} \:} ({\rm STREAM}) \end{array} \]

Resultado esperado (a ordem é irrelevante, não precisa de usar ORDER BY):

+------------------+----------+-------------+
|             2016 |        2 |       14.75 |
|             2017 |     3057 |    23944.75 |
|             2018 |     7100 |    55774.00 |
|             2019 |        2 |       14.25 |
+------------------+----------+-------------+
4 rows in set (0.01 sec)

5

Escreva uma instrução SELECT para obter os registos correspondentes à seguinte sequência de operações em álgebra relacional para a BD MovieStream:

\[ \begin{array}{@{}l@{}} R_0 \leftarrow \sigma_{\:{\rm Duration} \:\ge\: 120\:} ({\rm MOVIE}) \\ R_1 \leftarrow {}_{ {\rm Year} }\mathcal{F}_{\: {\rm N} \:=\: {\rm COUNT(*)} \:} (R_0) \\ R_2 \leftarrow \sigma_{\:{\rm N} \:\ge\: 20\:} (R_1) \end{array} \]

Resultado esperado (a ordem é irrelevante, não precisa de usar ORDER BY):

+------+----+
| 2007 | 21 |
| 2012 | 22 |
| 2013 | 24 |
| 2014 | 21 |
| 2015 | 26 |
+------+----+
5 rows in set (0.00 sec)

6

Escreva uma instrução SELECT para obter os registos correspondentes à seguinte sequência de operações em álgebra relacional para a BD MovieStream:

\[ \begin{array}{@{}l@{}} R_0 \leftarrow \sigma_{\:{\rm Supervisor}\: {\rm IS}\: {\rm NULL} \:} ({\rm STAFF}) \\ R_1 \leftarrow \pi_{\: {\rm StaffId } \:}(R_0) \\ R_2 \leftarrow \pi_{\: {\rm Manager } \:}({\rm DEPARTMENT}) \\ R_3 \leftarrow R_1 \cup R_2 \end{array} \]

Dica: Considere o uso de UNION para a união de resultados de duas consultas

  SELECT ... 
UNION 
  SELECT ...  

Resultado esperado (a ordem é irrelevante, não precisa de usar ORDER BY):

+---------+
|       1 |
|       2 |
|       7 |
|      11 |
|      14 |
+---------+
5 rows in set (0.00 sec)

7

Escreva uma instrução SELECT para obter os registos correspondentes à seguinte sequência de operações em álgebra relacional para a BD MovieStream:

\[ \begin{array}{@{}l@{}} R_0 \leftarrow {\rm DEPARTMENT} \bowtie_{\:{\rm Manager} \: = \: {\rm StaffId}\:} {\rm STAFF} \\ R_1 \leftarrow \pi_{\: {\rm DepId}, \:{\rm DEPARTMENT.Name}, \:{\rm Manager}, \:{\rm STAFF.Name}\:} (R_0) \end{array} \]

Resultado esperado (a ordem é irrelevante, não precisa de usar ORDER BY):

+-------+------------------+---------+---------------+
|     1 | IT               |       2 | Xavier Semedo |
|     2 | Public Relations |       7 | José Santos   |
|     3 | Finance          |      11 | Augusto Sousa |
|     4 | Customer         |      14 | António Mota  |
+-------+------------------+---------+---------------+
4 rows in set (0.00 sec)

8

Escreva uma instrução SELECT para obter os registos correspondentes à seguinte sequência de operações em álgebra relacional para a BD MovieStream:

\[ \begin{array}{@{}l@{}} R_0 \leftarrow {\rm MOVIE} \ast {\rm MOVIE\_ACTOR} \\ R_1 \leftarrow R_0 \ast {\rm ACTOR} \\ R_2 \leftarrow \sigma_{\: {\rm Title}\: \: {\rm LIKE} \: {\rm `\%Superman\%`} \:}(R_1) \\ R_3 \leftarrow \pi_{\:{\rm Title}, \:{\rm Name} \:} (R_2) \end{array} \]

Resultado esperado (a ordem é irrelevante, não precisa de usar ORDER BY):

+------------------------------------+-----------------+
| Batman v Superman: Dawn of Justice | Henry Cavill    |
| Batman v Superman: Dawn of Justice | Jesse Eisenberg |
| Batman v Superman: Dawn of Justice | Amy Adams       |
| Batman v Superman: Dawn of Justice | Ben Affleck     |
| Superman Returns                   | Kate Bosworth   |
| Superman Returns                   | Kevin Spacey    |
| Superman Returns                   | James Marsden   |
| Superman Returns                   | Brandon Routh   |
+------------------------------------+-----------------+
8 rows in set (0.00 sec)

9

Escreva uma instrução SELECT para obter os registos correspondentes à seguinte sequência de operações em álgebra relacional para a BD MovieStream:

\[ \begin{array}{@{}l@{}} R_0 \leftarrow {\rm MOVIE} \ast {\rm STREAM} \\ R_1 \leftarrow \sigma_{\: {\rm Duration}\: \ge\: 140 \:\wedge\: {\rm Year}\: = \: {\rm YEAR(StreamDate)}\: -\:1\:}(R_0) \\ R_2 \leftarrow \pi_{\:{\rm Title}, \:{\rm Duration}, \: {\rm Year}, \: {\rm StreamDate} \:} (R_1) \end{array} \]

Resultado esperado (a ordem é irrelevante, não precisa de usar ORDER BY):

+------------------------------------+----------+------+---------------------+
| Batman v Superman: Dawn of Justice |      151 | 2016 | 2017-09-18 19:04:00 |
| Batman v Superman: Dawn of Justice |      151 | 2016 | 2017-10-24 12:44:00 |
| Batman v Superman: Dawn of Justice |      151 | 2016 | 2017-11-09 07:24:00 |
| Batman v Superman: Dawn of Justice |      151 | 2016 | 2017-11-25 16:01:00 |
| Batman v Superman: Dawn of Justice |      151 | 2016 | 2017-12-02 01:23:00 |
| X-Men: Apocalypse                  |      144 | 2016 | 2017-01-17 07:22:00 |
| X-Men: Apocalypse                  |      144 | 2016 | 2017-03-30 21:27:00 |
| X-Men: Apocalypse                  |      144 | 2016 | 2017-05-01 17:48:00 |
| X-Men: Apocalypse                  |      144 | 2016 | 2017-11-04 06:52:00 |
| Captain America: Civil War         |      147 | 2016 | 2017-07-20 02:44:00 |
| Captain America: Civil War         |      147 | 2016 | 2017-07-25 15:18:00 |
| Captain America: Civil War         |      147 | 2016 | 2017-09-20 10:15:00 |
| Captain America: Civil War         |      147 | 2016 | 2017-10-12 21:15:00 |
| Captain America: Civil War         |      147 | 2016 | 2017-12-04 07:42:00 |
+------------------------------------+----------+------+---------------------+
14 rows in set (0.00 sec)

10

Escreva uma instrução SELECT para obter os registos correspondentes à seguinte sequência de operações em álgebra relacional para a BD MovieStream:

\[ \begin{array}{@{}l@{}} R_0 \leftarrow {\rm REGION} \ast_{\:{\rm RegionId}} {\rm COUNTRY} \\ R_1 \leftarrow R_0 \bowtie_{\: {\rm COUNTRY.Name}\: = \:{\rm CUSTOMER.Country}} {\rm CUSTOMER} \\ R_2 \leftarrow {}_{{\rm REGION.Name}} \mathcal{F}_{\: {\rm N} \: = \: {\rm COUNT(*)} \:} (R_1) \end{array} \]

Resultado esperado (a ordem é irrelevante, não precisa de usar ORDER BY):

+-----------------+----------+
| Africa          |       63 |
| America         |      148 |
| Asia            |      252 |
| Europe          |      125 |
| Other countries |       11 |
+-----------------+----------+

11

Use uma instrução SELECT para obter o nome de cada região (REGION.Name) e o nome do respectivo gestor (STAFF.Name). Observe que a ligação entre as tabelas REGION e STAFF é estabelecida pela chave externa REGION.RegionManager.

Os resultados devem ser ordenados pelo nome da região.

Resultado esperado:

+-----------------+------------------+
| Africa          | Felícia Antunes  |
| America         | Gabriela Silva   |
| Asia            | Felícia Antunes  |
| Europe          | Gastão Pinto     |
| Other countries | Gastão Pinto     |
+-----------------+------------------+
5 rows in set (0.02 sec)

12

Use uma instrução SELECT para obter o nome de cada região (REGION.Name) e o número de países em cada região. Observe que a ligação entre as tabelas COUNTRY e REGION é estabelecida pela chave externa COUNTRY.RegionId.

Os resultados devem ser ordenados de forma crescente pelo nº de países em cada região.

Resultado esperado:

+-----------------+----+
| Other countries | 10 |
| America         | 16 |
| Africa          | 21 |
| Asia            | 30 |
| Europe          | 31 |
+-----------------+----+

13

Use uma instrução SELECT para obter (1) os nomes de clientes oriundos do país 'United States' e (2) e o valor máximo cobrado por "streams" no ano de 2018 a cada um desses clientes, ordenados de forma decrescente pelo valor máximo cobrado (primeiro) e por ordem alfabética do nome de cliente (para valores máximos cobrados iguais).

Nota: a consulta só precisa de considerar as tabelas CUSTOMER e STREAM, já que o nome do país de um cliente já é dado em CUSTOMER.Country (chave externa para COUNTRY.Name).

Resultado esperado:

+-------------------+-------+
| Betty White       | 10.75 |
| Bryan Hardison    | 10.75 |
| Eva Ramos         | 10.75 |
| Jacob Lance       | 10.75 |
| Scott Shelley     | 10.75 |
| Ana Bradley       | 10.50 |
| Ashley Richardson | 10.50 |
| Carole Barnett    | 10.50 |
| Diana Alexander   | 10.50 |
| Jennifer Davis    | 10.50 |
| Renee Lane        | 10.50 |
| Thomas Grigsby    | 10.25 |
| Brandy Graves     | 10.00 |
| Rene Mcalister    |  9.75 |
| Valerie Black     |  9.75 |
| Veronica Stone    |  9.50 |
| Kristin Johnston  |  9.25 |
| Richard Mccrary   |  9.25 |
| Caroline Bowman   |  9.00 |
| Joan Cooper       |  9.00 |
| Victor Barkley    |  9.00 |
| Bill Gavin        |  8.50 |
| Clinton Buford    |  8.00 |
| Karl Seal         |  7.25 |
| Patricia Johnson  |  7.00 |
| Wilma Richards    |  7.00 |
| Zachary Hite      |  7.00 |
| Shelly Watts      |  6.50 |
| Ian Still         |  6.00 |
| Nathaniel Adam    |  5.50 |
+-------------------+-------+

14

Use uma instrução SELECT para obter os títulos de todos os filmes em que entra o actor 'Tom Cruise'. Os resultados devem ser ordenados pelo título do filme.

Nota: está em causa uma "junção natural" das tabelas ACTOR, MOVIE_ACTOR, e MOVIE via campos ActorId e MovieId.

Resultado esperado:

+----------------------------------------------------+
| A Few Good Men                                     |
| Collateral                                         |
| Edge of Tomorrow                                   |
| Eyes Wide Shut                                     |
| Interview with the Vampire: The Vampire Chronicles |
| Jack Reacher                                       |
| Jerry Maguire                                      |
| Knight and Day                                     |
| Magnolia                                           |
| Minority Report                                    |
| Mission: Impossible                                |
| Mission: Impossible - Ghost Protocol               |
| Mission: Impossible - Rogue Nation                 |
| Mission: Impossible II                             |
| Mission: Impossible III                            |
| Oblivion                                           |
| Rain Man                                           |
| The Last Samurai                                   |
| Top Gun                                            |
| Valkyrie                                           |
| Vanilla Sky                                        |
| War of the Worlds                                  |
+----------------------------------------------------+
22 rows in set (0.00 sec)

15

Use uma instrução SELECT para obter os nomes dos 20 actores com mais filmes na base de dados, e para cada actor o correspondente número de filmes em que entra.

Os resultados devem ser ordenados primeiro pelo número de filmes de forma decrescente e depois pelo nome do actor.

Resultado esperado:

+-------------------+----+
| Johnny Depp       | 23 |
| Brad Pitt         | 22 |
| Tom Cruise        | 22 |
| Tom Hanks         | 20 |
| Robert De Niro    | 18 |
| Leonardo DiCaprio | 17 |
| Liam Neeson       | 17 |
| Bruce Willis      | 16 |
| Matt Damon        | 16 |
| Will Smith        | 16 |
| Denzel Washington | 15 |
| Jim Carrey        | 15 |
| Morgan Freeman    | 15 |
| Christian Bale    | 14 |
| Hugh Jackman      | 14 |
| Mark Wahlberg     | 14 |
| Robert Downey Jr. | 14 |
| Cameron Diaz      | 13 |
| George Clooney    | 13 |
| Jake Gyllenhaal   | 13 |
+-------------------+----+

16

Use uma instrução SELECT para obter os nomes de clientes que tenham feito "streams" às 20:00 ou a uma hora mais tardia de filmes com uma duração de 180 ou mais minutos.´ Os resultados devem ser ordenados pelo nome do cliente e não devem conter nomes repetidos.

Resultado esperado:

+--------------------+
| Andrea Henderson   |
| Brenda Wright      |
| Carlos Coughlin    |
| Charlene Alvarez   |
| Charlie Bess       |
| Donna Thompson     |
| Ella Oliver        |
| Erika Pena         |
| Ethel Webb         |
| Eugene Culpepper   |
| Felix Gaffney      |
| Jeff East          |
| Joel Francisco     |
| Kelly Torres       |
| Lawrence Lawton    |
| Lynn Payne         |
| Maureen Little     |
| Megan Palmer       |
| Melanie Armstrong  |
| Peggy Myers        |
| Rodney Moeller     |
| Ron Deluca         |
| Ross Grey          |
| Russell Brinson    |
| Sharon Robinson    |
| Sherry Marshall    |
| Stacy Cunningham   |
| Stella Moreno      |
| Stephanie Mitchell |
| Terrence Gunderson |
| Vicki Fields       |
+--------------------+
31 rows in set (0.00 sec)