CONSULTAS SQL

--1. Películas en las que alguno de sus actores participantes también ha sido el director de la película (titulo,
--director), ordenado por director.

SELECT titulo, director
FROM participa par, pelicula peli
WHERE par.pelicula = peli.titulo AND peli.director = par.actor
ORDER BY director;

TITULO                                   DIRECTOR
---------------------------------------- -------------------------
PULP FICTION                             QUENTIN TARANTINO
RESERVOIR DOGS                           QUENTIN TARANTINO
KILL BILL VOL.II                         QUENTIN TARANTINO
KILL BILL VOL.I                          QUENTIN TARANTINO
MANHATTAN                                WOODY ALLEN
ANNIE HALL                               WOODY ALLEN
HISTORIAS DE NUEVA YORK                  WOODY ALLEN
MARIDOS Y MUJERES                        WOODY ALLEN
 8 filas seleccionadas

--2. Lista de todos los socios, con indicación de las películas de nacionalidad argentina que han alquilado (dni, nombre, titulo, --num_copia). Para aquellos socios que no han alquilado nunca películas argentinas, rellenar la columna ‘titulo’ con tres guiones: ‘---’ y --‘num_copia’ con un 0. Ordenado por dni, titulo y num_copia.

SELECT s.dni, s.nombre,NVL(pelicula, '---') AS TITULO, NVL(copia, 0) AS NUM_COPIA
FROM socio s
     LEFT OUTER JOIN (SELECT al.socio, al.pelicula, al.copia
                      FROM alquiler al
                      WHERE al.pelicula IN (SELECT pe.titulo
                                            FROM pelicula pe
                                            WHERE pe.nacionalidad = 'ARGENTINA')) d
     ON s.dni = d.socio
ORDER BY s.dni, TITULO, NUM_COPIA;

DNI      NOMBRE                    TITULO                    NUM_COPIA
-------- ------------------------- ------------------------- ----------------------
00000000 NORA CHATA                ---                       0
00000011 LIA GUEKO                 ---                       0
00000022 ABEL HACHA                ---                       0
11111111 GINES CAMPERO             MARTIN (HACHE)            1
11111111 GINES CAMPERO             UN LUGAR EN EL MUNDO      1
22222222 JULIO GARFIO              ---                       0
33333333 EVA PUZZLE                MARTIN (HACHE)            3
44444444 HANNA ESCALA              ---                       0
55555555 RUBEN SOL                 MARTIN (HACHE)            3
66666666 AROA MIRO                 MARTIN (HACHE)            3
66666666 AROA MIRO                 UN LUGAR EN EL MUNDO      2
77777777 VICTOR PACHON             MARTIN (HACHE)            1
77777777 VICTOR PACHON             MARTIN (HACHE)            3
77777777 VICTOR PACHON             UN LUGAR EN EL MUNDO      1
88888888 CESAR SALAO               ---                       0
99999999 ARTURO VIVO               ---                       0
 16 filas seleccionadas

--3. Actores (actor) que han participado en más de dos películas del director ‘WOODY ALLEN’, ordenados
--alfabéticamente

SELECT actor
FROM participa
WHERE pelicula IN(SELECT titulo
                  FROM pelicula
                  WHERE director = 'WOODY ALLEN')
GROUP BY actor
HAVING COUNT(*)>2
ORDER BY actor;

ACTOR
-------------------------
JUDY DAVIS
MIA FARROW
WOODY ALLEN

--4. Número total de copias de aquellas películas en las que ha participado algún actor australiano (titulo, total_copias)

SELECT cp.pelicula, COUNT(cp.num_copia) TOTAL_COPIAS
FROM copia cp
WHERE cp.pelicula IN (SELECT pa.pelicula
                      FROM participa pa, actor ac
                      WHERE pa.actor = ac.nombre AND ac.nacionalidad = 'AUSTRALIA')
GROUP BY cp.pelicula;

PELICULA                  TOTAL_COPIAS
------------------------- ----------------------
CELEBRITY                 2
MARIDOS Y MUJERES         3
ALICE                     1

--5. Para cada socio indicar la nacionalidad (nombre, teléfono, nacionalidad) más común entre las
--películas que ha alquilado, las haya devuelto o no. Es decir, si un socio suele alquilar más películas
--españolas que de cualquier otra nacionalidad, debe aparecer su nombre y teléfono junto con la
--nacionalidad ‘ESPAÑA’. Ordenado por nombre de socio.

SELECT nombre, telefono, nacionalidad
FROM socio soci,  (SELECT todos.socio, todos.nacionalidad
                   FROM (SELECT socio, nacionalidad, COUNT(*) cuenta2
                         FROM alquiler alq, pelicula peli
                         WHERE alq.pelicula = peli.titulo
                         GROUP BY socio, nacionalidad) todos,
                         (SELECT socio, MAX(cuenta)abc
                          FROM(
                               SELECT socio, nacionalidad, COUNT(*) cuenta
                               FROM alquiler alq, pelicula peli
                               WHERE alq.pelicula = peli.titulo
                               GROUP BY socio, nacionalidad)
                         GROUP BY socio) mayores
                   WHERE todos.socio = mayores.socio AND mayores.abc = todos.cuenta2) resultado
WHERE soci.dni = resultado.socio
ORDER BY nombre;

NOMBRE                    TELEFONO  NACIONALIDAD
------------------------- --------- -------------------------
AROA MIRO                 555555666 EEUU
ARTURO VIVO               555333444 EEUU
CESAR SALAO               555112244 ESPAÑA
CESAR SALAO               555112244 EEUU
EVA PUZZLE                555000111 EEUU
GINES CAMPERO             555222333 EEUU
HANNA ESCALA              555999000 ESPAÑA
JULIO GARFIO              555777888 ESPAÑA
NORA CHATA                555112233 ESPAÑA
RUBEN SOL                 555000222 EEUU
VICTOR PACHON             555111222 ESPAÑA
 11 filas seleccionadas

--6.Película que más veces ha sido alquilada por el mismo socio (titulo, dni_socio).

SELECT pelis TITULO, socio DNI_SOCIO
FROM(SELECT al.socio, COUNT(al.pelicula) Alquilada, al.pelicula pelis
      FROM alquiler al
      GROUP BY al.socio, al.pelicula) d
WHERE d.Alquilada = (SELECT MAX(COUNT(al.pelicula))
                      FROM alquiler al
                      GROUP BY al.socio, al.pelicula);

TITULO                    DNI_SOCIO
------------------------- ---------
APOCALYPSE NOW            11111111

--7. Socios (dni, nombre) que han alquilado todas las películas de la actriz ‘CECILIA ROTH’

SELECT dni, nombre
FROM socio
WHERE dni IN( SELECT socio
              FROM( SELECT DISTINCT alq.socio, alq.pelicula
                    FROM participa par,alquiler alq
                    WHERE actor = 'CECILIA ROTH' AND par.pelicula = alq.pelicula)
              GROUP BY socio
              HAVING COUNT(*) = (SELECT COUNT(*)
                                 FROM participa
                                 WHERE actor = 'CECILIA ROTH'));

DNI      NOMBRE
-------- -------------------------
11111111 GINES CAMPERO
77777777 VICTOR PACHON

--8.Para cada actor  mostrar el número de ocasiones en las que ha participado en películas como protagonista y como actor secundario --(nombre, veces_prota, veces_secun), ordenado por nombre del actor.

SELECT nombre, NVL(prota, 0) VECES_PROTA, NVL(secun, 0) VECES_SECUN
FROM ((actor LEFT OUTER JOIN (SELECT pa.actor, COUNT(pa.tipo_papel) prota
                               FROM participa pa
                               WHERE pa.tipo_papel = 'PROTAGONISTA'
                               GROUP BY pa.actor) pa ON pa.actor=nombre) LEFT OUTER JOIN (SELECT par.actor, COUNT(par.tipo_papel) secun
                                                                                          FROM participa par
                                                                                          WHERE par.tipo_papel = 'SECUNDARIO'
                                                                                          GROUP BY par.actor) par ON par.actor = nombre)
ORDER BY nombre;

NOMBRE                    VECES_PROTA            VECES_SECUN
------------------------- ---------------------- ----------------------
AL PACINO                 2                      1
ALEX ANGULO               1                      0
ANA TORRENT               1                      0
ANTONIA SAN JUAN          0                      1
CANDELA PEÑA              0                      1
CARMELO GOMEZ             1                      0
CARMEN MAURA              1                      0
CECILIA ROTH              2                      1
DARYL HANNAH              0                      2
DAVID CARRADINE           0                      1
DIANE KEATON              2                      1
EDUARDO NORIEGA           1                      1
ELENA ANAYA               0                      0
EMMA SUAREZ               0                      1
EUSEBIO PONCELA           0                      1
EWAN MCGREGOR             1                      0
FEDERICO LUPPI            2                      0
FELE MARTINEZ             1                      0
HARVEY KEITEL             1                      0
JAVIER BARDEM             2                      0
JAVIER CAMARA             1                      0
JOE MANTEGNA              0                      1
JOHN TRAVOLTA             1                      0
JOSE SACRISTAN            0                      1
JUAN DIEGO BOTTO          1                      0
JUDY DAVIS                0                      1
KARRA ELEJALDE            0                      0
KENNETH BRANAGH           1                      0
LEONARDO DICAPRIO         1                      0
LUCY LIU                  0                      2
MARISA PAREDES            0                      1
MARLON BRANDO             2                      0
MARTIN SHEEN              0                      1
MIA FARROW                1                      2
MICHAEL MADSEN            1                      2
NAJWA NIMRI               0                      1
NANCHO NOVO               0                      0
PAZ VEGA                  1                      0
PENELOPE CRUZ             0                      2
QUENTIN TARANTINO         1                      0
ROBERT CARLYLE            0                      2
ROBERT DE NIRO            1                      0
ROBERT DUVALL             0                      2
SAMUEL L. JACKSON         1                      0
SANCHO GRACIA             0                      0
SANTIAGO SEGURA           0                      1
SILKE                     0                      1
TERELE PAVEZ              0                      0
TIM ROTH                  1                      0
TRISTAN ULLOA             1                      0
UMA THURMAN               2                      0
WOODY ALLEN               4                      0
 52 filas seleccionadas

--9. Socios avalistas de aquellos socios que no han devuelto alguna de las películas que tienen en alquiler
--(nom_avalista, tel_avalista, nom_socio). Para aquellos socios que no tengan avalista, mostrar la
--cadena ‘*SIN AVAL*’ en la columna nom_avalista. Ordenado por nom_socio..
SELECT NVL(avalis.NOMBRE,'*SIN AVAL*') NOM_AVALISTA, NVL(avalis.telefono,' ') TEL_AVALISTA, avalado.nombre NOM_SOCIO
FROM (socio avalado LEFT OUTER JOIN socio avalis ON avalado.avalista = avalis.dni)
WHERE avalado.dni IN(
                SELECT DISTINCT socio
                FROM alquiler
                WHERE devolucion = 'NO')
ORDER BY avalado.nombre

NOM_AVALISTA              TEL_AVALISTA NOM_SOCIO
------------------------- ------------ -------------------------
JULIO GARFIO              555777888    AROA MIRO
JULIO GARFIO              555777888    ARTURO VIVO
EVA PUZZLE                555000111    CESAR SALAO
*SIN AVAL*                             EVA PUZZLE
*SIN AVAL*                             GINES CAMPERO
JULIO GARFIO              555777888    HANNA ESCALA
*SIN AVAL*                             JULIO GARFIO
JULIO GARFIO              555777888    RUBEN SOL
EVA PUZZLE                555000111    VICTOR PACHON

 9 filas seleccionadas

--10.Socio que ha alquilado el mayor número de películas diferentes y cuántas han sido (nombre, num_peliculas).

SELECT s.nombre, d.pelisDist NUM_PELICULAS
FROM socio s, (SELECT al.socio, COUNT(DISTINCT al.pelicula) pelisDist
                FROM alquiler al
                GROUP BY al.socio) d
WHERE d.socio = s.DNI AND d.pelisDist = (SELECT MAX(COUNT(DISTINCT al.pelicula))
                                          FROM alquiler al
                                          GROUP BY al.socio)

NOMBRE                    NUM_PELICULAS
------------------------- ----------------------
GINES CAMPERO             20