Aulas práticas - Ficha 7

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

(junção externa à esquerda)

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 CUSTOMER}\: \unicode{x27D5}_{\:{\rm CUSTOMER.CustomerId}\: = \:{\rm STREAM.CustomerId}\:} \: {\rm STREAM} \\ R_1 \leftarrow \sigma_{\:{\rm StreamId}\: {\rm IS}\: {\rm NULL}\: \wedge \: {\rm Country}\: = \: {\rm 'China'}\:} (R_0) \\ R_2 \leftarrow \pi_{\:{\rm Name}\:} (R_1) \end{array} \]

Explicação: A relação final obtida é a dos nomes de clientes da China que não fizeram qualquer "stream".

Nota: Se for conveniente pode empregar USING em associação a ... LEFT OUTER JOIN ... com o mesmo significado que em JOIN (indicação de atributos comuns a usar na condição de junção).

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

+-------------------+
| Ruby Washington   |
| Stacey Montgomery |
| Christy Vargas    |
| Donald Mahon      |
| Dave Gardiner     |
| Wade Delvalle     |
+-------------------+
6 rows in set (0.00 sec)

2

(junção externa à direita)

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 STREAM}\: \unicode{x27D6}_{\:{\rm STREAM.CustomerId}\: = \:{\rm CUSTOMER.CustomerId}\:} \: {\rm CUSTOMER} \\ R_1 \leftarrow \sigma_{\:{\rm StreamId}\: {\rm IS}\: {\rm NULL}\:} (R_0) \\ R_2 \leftarrow {}_{{\rm Country}\:}\mathcal{F}_{\:{\rm N}\: = \: {\rm COUNT(CustomerId)} \:} (R_1) \end{array} \]

Explicação: A relação final obtida é a de nomes de países que têm clientes que não fizeram qualquer "stream" e o número de clientes correspondentes.

Nota: Se for conveniente pode empregar USING em associação a ... RIGHT OUTER JOIN ... com o mesmo significado que em JOIN (indicação de atributos comuns a usar na condição de junção).

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

+--------------------+-------------------+
| Argentina          |                 1 |
| Brazil             |                 3 |
| China              |                 6 |
| Dominican Republic |                 1 |
| Egypt              |                 1 |
| French Guiana      |                 1 |
| Greenland          |                 1 |
| India              |                 7 |
| Indonesia          |                 4 |
| Israel             |                 2 |
| Japan              |                 3 |
| Mexico             |                 2 |
| Nepal              |                 1 |
| Netherlands        |                 1 |
| Peru               |                 1 |
| Philippines        |                 2 |
| Poland             |                 2 |
| Russia             |                 3 |
| Spain              |                 1 |
| Taiwan             |                 1 |
| Thailand           |                 1 |
| Turkey             |                 1 |
| Turkmenistan       |                 1 |
| United Kingdom     |                 1 |
| United States      |                 3 |
| Venezuela          |                 1 |
+--------------------+-------------------+
26 rows in set (0.00 sec)

3

(junção externa à esquerda)

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}\: \unicode{x27D5}_{\:{\rm MOVIE.MovieId}\: = \:{\rm STREAM.MovieId}\:} \: {\rm STREAM} \\ R_1 \leftarrow \sigma_{\:{\rm Year}\:=\:2015\:} (R_0) \\ R_2 \leftarrow {}_{{\rm Title}\:}\mathcal{F}_{\:{\rm N}\: = \:{\rm COUNT(StreamId)}\:} (R_1) \\ R_3 \leftarrow \sigma_{\:{\rm N}\:\le\:5\:} (R_2) \end{array} \]

Explicação: A relação final obtida é a de títulos de filmes do ano de 2015 com 5 ou menos streams feitos (incluindo 0).

Nota: Se for conveniente pode empregar USING em associação a ... LEFT OUTER JOIN ... com o mesmo significado que em JOIN (indicação de atributos comuns a usar na condição de junção).

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

+------------------------------------+---+
| Avengers: Age of Ultron            | 5 |
| Bridge of Spies                    | 0 |
| Focus                              | 0 |
| Mad Max: Fury Road                 | 0 |
| Minions                            | 1 |
| Mission: Impossible - Rogue Nation | 0 |
| Room                               | 0 |
| Southpaw                           | 0 |
| Spectre                            | 0 |
| Spy                                | 2 |
| Straight Outta Compton             | 5 |
| The Big Short                      | 4 |
| The Intern                         | 3 |
| The Lobster                        | 0 |
| The Man from U.N.C.L.E.            | 3 |
| Tomorrowland                       | 2 |
+------------------------------------+---+
16 rows in set (0.00 sec)

4

(junção externa)

Escreva uma instrução SELECT para obter os nomes de todos os funcionários e o correspondente nº de funcionários que supervisionam. Os resultados devem ser ordenados de forma decrescente pelo nº de funcionários supervisionados e pelo nome dos funcionários quando o nº de supervisionados é igual.

Resultado esperado:

+-------------------+---+
| João Pinto        | 4 |
| António Mota      | 3 |
| Augusto Sousa     | 2 |
| Eva Mendes        | 2 |
| José Santos       | 2 |
| Xavier Semedo     | 2 |
| João Santorini    | 1 |
| Alexandra Romeu   | 0 |
| Fábio Cruz        | 0 |
| Felícia Antunes   | 0 |
| Filipa Magalhães  | 0 |
| Filipa Mendes     | 0 |
| Gabriela Silva    | 0 |
| Gastão Pinto      | 0 |
| Joana Moreira     | 0 |
| Maria Silva       | 0 |
| Pedro Simões      | 0 |
+-------------------+---+
17 rows in set (0.00 sec)

5

Use uma instrução DELETE para remover todos os "streams" de clientes do país 'China' com valor cobrado (Charge) igual a 5.5 ou inferior.

Resultado esperado (apenas) em termos do nº de registos removidos:

Query OK, 106 rows affected (0.02 sec)

6

Use uma instrução DELETE para remover em MOVIE_ACTOR as entradas que correspondam a filmes com o actor 'Tom Cruise' e classificados com o género 'Action' (em MOVIE_GENRE / GENRE).

Resultado esperado (apensas) em termos do nº de registos removidos:

Query OK, 12 rows affected (0.01 sec)

7

Use uma instrução UPDATE para tornar inactivos (Active=FALSE) todos os clientes do país 'China' que não tenham feito qualquer "stream" de filmes.

Resultado esperado (apenas) em termos do número de registos actualizados:

Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

8

Use uma instrução UPDATE para somar 1.5 ao valor cobrado por "streams" (Charge = Charge + 1.5) associados a filmes com duração igual ou superior a 180 minutos e a clientes oriundos da região com nome 'Europe'.

Resultado esperado (apenas) em termos do número de registos actualizados:

Query OK, 41 rows affected (0.03 sec)
Rows matched: 41  Changed: 41  Warnings: 0

9

Escreva uma instrução SELECT para obter os nomes de actores que participaram em 15 filmes ou mais, e o nº de filmes correspondentes. Os resultados devem ser ordenados primeiro pelo nº de filmes de forma decrescente e depois pelo nome do actor (para um nº de filmes igual).

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 |
+-------------------+----+
13 rows in set (0.01 sec)

10

Escreva uma instrução SELECT para obter os títulos de todos os filmes em que participou 'Johnny Depp' e os nomes de outros actores que participaram nesses filmes. Os resultados devem ser ordenados primeiro por título de filme e depois por nome de actor.

Resultado esperado (fragmento):

+--------------------------------------------------------+----------------------+
| A Nightmare on Elm Street                              | Heather Langenkamp   |
| A Nightmare on Elm Street                              | John Saxon           |
| A Nightmare on Elm Street                              | Robert Englund       |
| Alice in Wonderland                                    | Anne Hathaway        |
| Alice in Wonderland                                    | Helena Bonham Carter |
| Alice in Wonderland                                    | Mia Wasikowska       |
...
| Transcendence                                          | Cillian Murphy       |
| Transcendence                                          | Morgan Freeman       |
| Transcendence                                          | Rebecca Hall         |
| What's Eating Gilbert Grape                            | Juliette Lewis       |
| What's Eating Gilbert Grape                            | Leonardo DiCaprio    |
| What's Eating Gilbert Grape                            | Mary Steenburgen     |
+--------------------------------------------------------+----------------------+
69 rows in set (0.00 sec)

11

Escreva uma instrução SELECT para obter os nomes de pares de actores que tenham actuado ambos no mesmo filme pelo menos 4 vezes, e o correspondente nº de filmes. O nome do 1º actor deve preceder alfabeticamente o nome do segundo actor (dica: use o operador < para esse efeito). Os resultados devem ser ordenados primeiro pelo nº de filmes de forma decrescente, depois pelo nome do 1º actor, e finalmente pelo nome do 2º actor.

Resultado esperado (fragmento):

+----------------------+--------------------+---+
| Daniel Radcliffe     | Rupert Grint       | 8 |
| Daniel Radcliffe     | Emma Watson        | 7 |
| Emma Watson          | Rupert Grint       | 7 |
| Helena Bonham Carter | Johnny Depp        | 5 |
| Kristen Stewart      | Robert Pattinson   | 5 |
...
| John Leguizamo       | Ray Romano         | 4 |
| Josh Hutcherson      | Liam Hemsworth     | 4 |
| Kristen Stewart      | Taylor Lautner     | 4 |
| Nick Frost           | Simon Pegg         | 4 |
| Robert Pattinson     | Taylor Lautner     | 4 |
+----------------------+--------------------+---+
21 rows in set (0.04 sec)

12

Use uma instrução UPDATE para colocar o valor cobrado a 4.5 (Charge = 4.5) para todos os "streams" associados a:

Resultado esperado (em termos do nº de registos actualizados)

Query OK, 124 rows affected  ...

13

Use uma instrução SELECT para obter a altura em que foram feitos os 15 streams mais recentes (de acordo com StreamDate em ordem decrescente), o título do filme associado a cada stream, e ainda o nome e país do cliente associado a cada stream, nas seguintes condições:

Resultado esperado:

+---------------------+---------------------------------------------+-----------------+-------------------------------+
| 2018-12-26 13:28:00 | Blow                                        | Ella Oliver     | Yemen                         |
| 2018-12-25 17:21:00 | Edward Scissorhands                         | Ellen Simpson   | Brazil                        |
| 2018-12-23 11:01:00 | Pirates of the Caribbean: At World's End    | Sam Mcduffie    | Colombia                      |
| 2018-12-22 06:24:00 | Transcendence                               | Amber Dixon     | Taiwan                        |
| 2018-12-21 04:49:00 | Finding Neverland                           | Ethel Webb      | South Africa                  |
| 2018-12-20 20:31:00 | Donnie Brasco                               | Emily Diaz      | South Korea                   |
| 2018-12-16 10:37:00 | Edward Scissorhands                         | Willard Lumpkin | Mexico                        |
| 2018-12-15 12:33:00 | Fear and Loathing in Las Vegas              | Velma Lucas     | Nigeria                       |
| 2018-12-09 01:23:00 | Pirates of the Caribbean: At World's End    | Lauren Hudson   | France                        |
| 2018-12-02 01:55:00 | Pirates of the Caribbean: On Stranger Tides | Douglas Graf    | Mexico                        |
| 2018-11-25 03:14:00 | Public Enemies                              | Ken Prewitt     | Romania                       |
| 2018-11-24 04:25:00 | The Tourist                                 | Clifford Bowens | Russia                        |
| 2018-11-23 00:10:00 | Sleepy Hollow                               | Jessica Hall    | Holy See (Vatican City State) |
| 2018-11-14 12:27:00 | Pirates of the Caribbean: At World's End    | Lloyd Dowd      | Indonesia                     |
| 2018-11-11 01:32:00 | Edward Scissorhands                         | Marcus Hidalgo  | Estonia                       |
+---------------------+---------------------------------------------+-----------------+-------------------------------+
15 rows in set  ...

14

Use uma instrução SELECT para obter todas as combinações de títulos de filmes contendo a sequência 'war' com nomes de regiões, e para cada uma dessas combinações filme-região o nº de streams feitos (possivelmente 0) para o filme por clientes oriundos de países na região.

Os resultados deverão ser ordenados primeiro por título de filme e depois pelo nome de região.

Resultado esperado (fragmento):

+----------------------------------------------------------------+-----------------+----+
| Captain America: Civil War                                     | Africa          |  3 |
| Captain America: Civil War                                     | America         |  3 |
| Captain America: Civil War                                     | Asia            |  4 |
| Captain America: Civil War                                     | Europe          |  5 |
| Captain America: Civil War                                     | Other countries |  0 |
| Edward Scissorhands                                            | Africa          |  2 |
| Edward Scissorhands                                            | America         |  6 |
...
| Warrior                                                        | Other countries |  0 |
| World War Z                                                    | Africa          |  1 |
| World War Z                                                    | America         |  1 |
| World War Z                                                    | Asia            |  6 |
| World War Z                                                    | Europe          |  1 |
| World War Z                                                    | Other countries |  0 |
+----------------------------------------------------------------+-----------------+----+ 
90 rows in set ...

15

Use uma instrução DELETE para remover todos os "streams" associados a:

Resultado esperado (em termos do nº de registos actualizados)

Query OK, 111 rows affected ...

16

Escreva uma instrução SELECT para obter o nº de "streams" por cada nome de região e "label" de género de filme, incluindo casos em que não tenha havido qualquer "stream". Os resultados devem estar ordenados primeiro pelo "label" do género do filme e depois pelo nome da região.

Resultado esperado (fragmento):

+-----------+-----------------+-----------------+
| Action    | Africa          |             502 |
| Action    | America         |             779 |
| Action    | Asia            |            1402 |
| Action    | Europe          |             812 |
| Action    | Other countries |              62 |
| Adventure | Africa          |             489 |
| Adventure | America         |             738 |
...
| Film-Noir | Africa          |               0 |
| Film-Noir | America         |               0 |
| Film-Noir | Asia            |               0 |
| Film-Noir | Europe          |               0 |
| Film-Noir | Other countries |               0 |
...
| War       | Europe          |              60 |
| War       | Other countries |               7 |
| Western   | Africa          |              13 |
| Western   | America         |              13 |
| Western   | Asia            |              37 |
| Western   | Europe          |              20 |
| Western   | Other countries |               4 |
+-----------+-----------------+-----------------+
105 rows in set (0.37 sec)