BD MovieStream - exemplos SQL

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

Eduardo R. B. Marques, DCC/FCUP

Referência:

Sub-consultas

IN / NOT IN

Ids de filmes com algum "stream":

SELECT  MovieId FROM MOVIE
WHERE MovieId IN
(SELECT  MovieId FROM STREAM);

Ids de filmes sem nenhum "stream": SELECT MovieId FROM MOVIE WHERE MovieId NOT IN (SELECT MovieId FROM STREAM);

EXISTS / NOT EXISTS

5 filmes sem streams:

SELECT Title 
FROM MOVIE M
WHERE NOT EXISTS   
  (SELECT * FROM STREAM
   WHERE MovieId = M.MovieId)
ORDER BY Title
LIMIT 5;

Consulta equivalente usando NOT IN:

SELECT Title 
FROM MOVIE
WHERE MovieId NOT IN   
  (SELECT MovieId FROM STREAM)
ORDER BY Title
LIMIT 5;

Uso de sub-consultas com 1 resultado

1

Inserção de um registo de stream pelo cliente 'Aaron Selby' do filme Batman:

INSERT INTO STREAM(CustomerId, MovieId, StreamDate, Charge) 
VALUES   
( 
  (SELECT CustomerId FROM CUSTOMER WHERE Name = 'Aaron Selby'),
  (SELECT MovieId FROM MOVIE WHERE Title='Batman'),
  '2020-03-24 11:24:30', 
  2.50 
);

2

Consulta do registo inserido:

SELECT *  
FROM STREAM  
WHERE 
  CustomerId = 
    (SELECT CustomerId FROM CUSTOMER WHERE Name = 'Aaron Selby')
AND 
  MovieId = 
    (SELECT MovieId FROM MOVIE WHERE Title='Batman');

Resultado:

+----------+---------+------------+---------------------+--------+
| StreamId | MovieId | CustomerId | StreamDate          | Charge |
+----------+---------+------------+---------------------+--------+
|    10162 |     248 |        375 | 2020-03-24 11:24:30 |   2.50 |
+----------+---------+------------+---------------------+--------+

Várias sub-consultas imbricadas

1

Contagem dos "streams" feitos por clientes oriundos de países na região 'Africa' (4 tabelas envolvidas):

SELECT COUNT(*) AS N FROM STREAM WHERE CustomerId IN (
   SELECT CustomerId FROM CUSTOMER WHERE Country IN (
     SELECT Name FROM COUNTRY WHERE RegionId = (
       SELECT RegionId FROM REGION WHERE Name='Africa')));

Resultado:

+------+
| N    |
+------+
| 1374 |
+------+

2

Remoção de "streams" nas condições anteriores:

DELETE FROM STREAM WHERE CustomerId IN (
   SELECT CustomerId FROM CUSTOMER WHERE Country IN (
     SELECT Name FROM COUNTRY WHERE RegionId = (
       SELECT RegionId FROM REGION WHERE Name='Africa')));

Resultado:

Query OK, 1374 rows affected [...]

Consultas em múltiplas tabelas

Produto cartesiano simples

Consulta de todos as combinações (nome de país, nome de região) :

SELECT COUNTRY.Name AS CName, 
       REGION.Name AS RName
FROM COUNTRY, REGION;

Resultado (fragmento):

+------------------+-----------------+
| CName            | RName           |
+------------------+-----------------+
| Anguilla         | Other countries |
| Anguilla         | America         |
| Anguilla         | Asia            |
| Anguilla         | Europe          |
| Anguilla         | Africa          |
| French Polynesia | Other countries |
| French Polynesia | America         |
| French Polynesia | Asia            |
| French Polynesia | Europe          |
| French Polynesia | Africa          |
. . .

Consulta com condição de filtragem (uma "junção natural")

Consulta de todos os pares (nome de país, nome da região correspondente ao país):

SELECT COUNTRY.Name AS CName, 
       REGION.Name AS RName
FROM COUNTRY, REGION
WHERE COUNTRY.RegionId = REGION.RegionId;

Resultado (fragmento):

+----------------------------------+-----------------+ 
| CName                            | RName           |
+----------------------------------+-----------------+
| Anguilla                         | Other countries |
| French Polynesia                 | Other countries |
| Greenland                        | Other countries |
| Nauru                            | Other countries |
| New Zealand                      | Other countries |
| Reunion                          | Other countries |
| Saint Vincent and the Grenadines | Other countries |
| Tonga                            | Other countries |
| Tuvalu                           | Other countries |
| Virgin Islands                   | Other countries |
| American Samoa                   | America         |
| Argentina                        | America         |
| Bolivia                          | America         |
| Brazil                           | America         |
. . .

Consultas "reflexivas" sobre a mesma tabela

1

Consulta de nomes de supervisores e correspondentes funcionários supervisionados:

SELECT S1.Name AS Supervisor, 
       S2.Name AS Supervised
FROM STAFF S1, STAFF S2 
WHERE S1.StaffId = S2.Supervisor
ORDER BY Supervisor, Supervised;

Resultado:

+-----------------+-------------------+
| Supervisor      | Supervised        |
+-----------------+-------------------+
| António Mota    | Felícia Antunes   |
| António Mota    | Gabriela Silva    |
| António Mota    | Gastão Pinto      |
| Augusto Sousa   | Alexandra Romeu   |
| Augusto Sousa   | Fábio Cruz        |
| Eva Mendes      | Maria Silva       |
| Eva Mendes      | Pedro Simões      |
| João Pinto      | António Mota      |
| João Pinto      | Augusto Sousa     |
| João Pinto      | José Santos       |
| João Pinto      | Xavier Semedo     |
| João Santorini  | Joana Moreira     |
| José Santos     | Filipa Magalhães  |
| José Santos     | João Santorini    |
| Xavier Semedo   | Eva Mendes        |
| Xavier Semedo   | Filipa Mendes     |
+-----------------+-------------------+

2

Contagem de supervisionados por supervisor:

SELECT S1.Name AS Supervisor, 
       COUNT(*) AS N
FROM  STAFF S1, STAFF S2 
WHERE S1.StaffId = S2.Supervisor
GROUP BY Supervisor
ORDER BY Supervisor;

Resultado:

+-----------------+---+
| Supervisor      | N |
+-----------------+---+
| António Mota    | 3 |
| Augusto Sousa   | 2 |
| Eva Mendes      | 2 |
| João Pinto      | 4 |
| João Santorini  | 1 |
| José Santos     | 2 |
| Xavier Semedo   | 2 |
+-----------------+---+
7 rows in set (0.00 sec)

Consultas sobre 3 tabelas

Obtém nome de actores, título do filme em que participou, e ano do filme tais que o nome do autor começa por 'Brad' e o filme tenha sido realizado em 2010 ou anos posteriores.

SELECT 
       A.Name AS Name,
       M.Title AS Title,
       M.Year AS Year
FROM   
       ACTOR A, 
       MOVIE_ACTOR MA, 
       MOVIE M
WHERE 
       A.ActorId = MA.ActorId
  AND 
       MA.MovieId = M.MovieId
  AND
       A.Name LIKE 'Brad%'
  AND
       M.Year >= 2010
ORDER BY 
       Name, Year, Title;

Resultado:

+----------------+----------------------------+------+
| Name           | Title                      | Year |
+----------------+----------------------------+------+
| Brad Pitt      | Megamind                   | 2010 |
| Brad Pitt      | Moneyball                  | 2011 |
| Brad Pitt      | 12 Years a Slave           | 2013 |
| Brad Pitt      | World War Z                | 2013 |
| Brad Pitt      | Fury                       | 2014 |
| Brad Pitt      | The Big Short              | 2015 |
| Bradley Cooper | The A-Team                 | 2010 |
| Bradley Cooper | Limitless                  | 2011 |
| Bradley Cooper | The Hangover Part II       | 2011 |
| Bradley Cooper | Silver Linings Playbook    | 2012 |
| Bradley Cooper | The Place Beyond the Pines | 2012 |
| Bradley Cooper | American Hustle            | 2013 |
| Bradley Cooper | The Hangover Part III      | 2013 |
| Bradley Cooper | American Sniper            | 2014 |
| Bradley Cooper | Guardians of the Galaxy    | 2014 |
+----------------+----------------------------+------+
15 rows in set (0.00 sec)