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:
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)
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)
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)
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)
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)
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)
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)
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)
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)
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 | +-----------------+----------+
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)
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 | +-----------------+----+
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 | +-------------------+-------+
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)
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 | +-------------------+----+
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)