<div class="page"> <div class="cover text-center"> <img class="mx-auto" src=/itb/images/logo_mislata.png alt="logo"> # Consultes Cinema <div class="text-end fit-content ms-auto my-3 mt-auto pt-3"> <p><strong>Autor:</strong> Joan Puigcerver Ibáñez</p> <p><strong>Correu electrònic:</strong> j.puigcerveribanez@edu.gva.es</p> <p><strong>Curs:</strong> 2023/2024</p> </div> <div> <p class="fw-bold mb-0">Llicència: BY-NC-SA</p> <p class="d-none d-md-block">(Reconeixement - No Comercial - Compartir Igual)</p> <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.ca" target="_blank"> <img class="mx-auto" src="/itb/images/license.png" alt="Licence"/> </a> </div><!--license--> </div><!--cover--> </div><!--page--> {:toc} ## Introducció - Utilitza la carpeta corresponent dins de la carpeta __cinema/queries__. - Exemple: __cinema/queries/basic/__ - El nom de cada fitxer ha de ser __query\_#{number}.sql__, on `#{number}` es el número de la consulta amb 2 dígits. - Exemple: __/cinema/queries/basic/query_01.sql__ ## Bases de dades Cinema Utilitzant la base de dades __cinema__: __Fitxers:__ - <a href="/itb/DAM-BD/files/ca/cinema/schema/create_schema_cinema.sql" download="create_schema_cinema.sql">create_schema_cinema.sql</a> - <a href="/itb/DAM-BD/files/ca/cinema/data/insert_data_cinema.sql" download="insert_data_cinema.sql">insert_data_cinema.sql</a> <div class="mermaid"> erDiagram PAIS { type id_pais PK type nom } ACTOR }|--|| PAIS : "" ACTOR { type id_actor PK type nom type data_naixement type id_pais FK } LLIBRE { type id_llibre PK type titol type any type escriptor } PELICULA }|--|| LLIBRE : "" PELICULA { type id_pelicula PK type titol type any type duracio type director type id_llibre FK } ACTUA }|--|| ACTOR : "" ACTUA }|--|| PELICULA : "" ACTUA { type id_actor FK type id_film FK type role } GENERE { type id_genere PK type nom } PELICULA_GENERE }|--|| GENERE : "" PELICULA_GENERE }|--|| PELICULA : "" PELICULA_GENERE { type id_pelicula FK type id_genere FK } </div> ## Consultes amb una única relació ::: warning Si no s'indica el contrari, els resultats de les consultes estan ordenats pels atributs de la taula resultat, d'esquerra a dreta. ::: Utilitza la carpeta __/cinema/queries/basic/__ 1. Obté el codi dels paisos amb algun actor en ordre ascendent. ``` +---------+ | id_pais | +---------+ | ad63 | | gg74 | | hg45 | | nb12 | | rt89 | | sd53 | | sf15 | | ty11 | | we74 | | zf58 | +---------+ ``` 2. Obté el codi i el títol de les pel·licules publicades abans de l'any 1970 que no estan basades en cap llibre. Ordena les pel·licules per títol. ``` +-------------+--------------------------+ | id_pelicula | titol | +-------------+--------------------------+ | 357L | Cleopatra | | 365N | Cortina rasgada | | 332D | Dos hombres y un destino | +-------------+--------------------------+ ``` 3. Obté el codi i el nom dels actors els quals el seu nom inclou 'John'. ``` +----------+--------------+ | id_actor | nom | +----------+--------------+ | A62 | John Goodman | +----------+--------------+ ``` 4. Obté el codi i el títol de les pel·licules que duren més de 120 minuts que s'han publicat en els 80. ``` +-------------+------------------------------------+ | id_pelicula | titol | +-------------+------------------------------------+ | 365A | Indiana Jones y la última cruzada | +-------------+------------------------------------+ ``` 5. Obté el codi i el títol de les pel·licules basaden en algun llibre que han segut dirigides per un director amb el cognom 'Pakula'. ``` +-------------+----------------------+ | id_pelicula | titol | +-------------+----------------------+ | 856A | El informe pelícano | +-------------+----------------------+ ``` 6. Quantes pel·licules existeixen amb una duració major de 120 minuts i publicades en els 80? ``` +------------------+ | nombre_pelicules | +------------------+ | 1 | +------------------+ ``` 7. Quantes pel·licules estan classificades en els gèneres amb codis 'BB5', 'GG4', o 'JH6' ? ``` +------------------+ | nombre_pelicules | +------------------+ | 43 | +------------------+ ``` 8. En quin any es va publicar el llibre més antic? ``` +----------------+ | any_publicacio | +----------------+ | 1877 | +----------------+ ``` 9. Quina és la duració mitjana de les pel·licules publicades en l'any 1987? ``` +-----------------+ | duracio_mitjana | +-----------------+ | 119.5000 | +-----------------+ ``` 10. Quina és la duració total de les pel·licules dirigides per 'Steven Spielberg'? ``` +---------------+ | total_duracio | +---------------+ | 296 | +---------------+ ``` ## Consultes que utilitzen més d'una relació Utilitza la carpeta __/cinema/queries/join/__ 1. Obté el codi i el títol de les pel·lícules en les quals actua un actor amb el mateix nom que el director de la pel·lícula (ordenades per títol). ``` +-------------+-------------------------------+ | id_pelicula | titol | +-------------+-------------------------------+ | 654J | Buenas noches, y buena suerte | | 778E | Sin perdón | | 455K | The monuments men | | 118E | Un mundo perfecto | +-------------+-------------------------------+ ``` 2. Obté el codi i el títol de les pel·lícules amb el gènere 'Comèdia' (ordenades per tíol). ``` +-------------+--------------------------------+ | id_pelicula | titol | +-------------+--------------------------------+ | 258S | Cuando Harry encontró a Sally | | 369F | Desayuno con diamantes | | 456G | El chip prodigioso | | 888T | El golpe | | 548J | Jamón, Jamón | | 147D | Los búfalos de Durham | | 874G | Los picapiedra | | 789B | The mexican | +-------------+--------------------------------+ ``` 3. Obté el codi i el títol de les pel·lícules basades en un llibre publicat abans del 1950 (ordenades per títol). ``` +-------------+----------------------------+ | id_pelicula | titol | +-------------+----------------------------+ | 159A | Ana Karenina | | 123V | Anna Karenina | | 159X | Anna Karenina | | 123N | Lo que el viento se llevó | | 123S | My Fair Lady | | 258M | Un tranvía llamado deseo | +-------------+----------------------------+ ``` 4. Obté el codi i el nom dels paisos en els quals han nascut actors que actuen en pel·lícules del gènere 'Comèdia' (ordenades per nom). ``` +---------+----------+ | id_pais | nom | +---------+----------+ | ad63 | Bèlgica | | we74 | Espanya | | sf15 | USA | +---------+----------+ ``` 5. Obté el titol dels llibre dels quals no s'ha basat cap pel·lícula. ``` +---------------------------+ | titol | +---------------------------+ | La caída de los gigantes | +---------------------------+ ``` ## Consultes amb subconsultes Utilitza la carpeta __/cinema/queries/subqueries/__ 1. (El mateix que join/query01) Obté el codi i el títol de les pel·lícules en les quals actua un actor amb el mateix nom que el director de la pel·lícula (ordenades per títol). ``` +-------------+-------------------------------+ | id_pelicula | titol | +-------------+-------------------------------+ | 654J | Buenas noches, y buena suerte | | 778E | Sin perdón | | 455K | The monuments men | | 118E | Un mundo perfecto | +-------------+-------------------------------+ ``` 2. (El mateix que join/query02) Obté el codi i el títol de les pel·lícules amb el gènere 'Comèdia' (ordenades per tíol). ``` +-------------+--------------------------------+ | id_pelicula | titol | +-------------+--------------------------------+ | 258S | Cuando Harry encontró a Sally | | 369F | Desayuno con diamantes | | 456G | El chip prodigioso | | 888T | El golpe | | 548J | Jamón, Jamón | | 147D | Los búfalos de Durham | | 874G | Los picapiedra | | 789B | The mexican | +-------------+--------------------------------+ ``` 3. (El mateix que join/query03) Obté el codi i el títol de les pel·lícules basades en un llibre publicat abans del 1950 (ordenades per títol). ``` +-------------+----------------------------+ | id_pelicula | titol | +-------------+----------------------------+ | 159A | Ana Karenina | | 123V | Anna Karenina | | 159X | Anna Karenina | | 123N | Lo que el viento se llevó | | 123S | My Fair Lady | | 258M | Un tranvía llamado deseo | +-------------+----------------------------+ ``` 4. (El mateix que join/query04) Obté el codi i el nom dels països en eles quals han nascut actors que actuen en pel·lícules del gènere 'Comèdia' (ordenades per nom). ``` +---------+----------+ | id_pais | nom | +---------+----------+ | ad63 | Bélgica | | we74 | España | | sf15 | USA | +---------+----------+ ``` 5. Obté el codi i el nom dels actors nascuts abans de l'any 1950 que actuen amb el rol 'Principal' en alguna pel·lícula (ordenats per nom). ``` +----------+---------------------+ | id_actor | nom | +----------+---------------------+ | Z15 | Al Pacino | | D49 | Audrey Hepburn | | L54 | Christopher Plummer | | L59 | Clint Eastwood | | L45 | Elizabeth Taylor | | S56 | Elke Sommer | | J47 | Gene Hackman | | V88 | George Peppard | | J45 | Harrison Ford | | X45 | Julie Andrews | | J56 | Marlon Brandon | | D14 | Martin Sheen | | U88 | Morgan Freeman | | W34 | Paul Newman | | T44 | Rex Harrison | | F56 | Richard Burton | | M45 | Richard Gere | | E56 | Robert de Niro | | H45 | Robert Redford | | W32 | Sean Connery | | E45 | Susan Sarandon | | D01 | Vivien Leigh | +----------+---------------------+ ``` 6. Obté el codi, títol i autor dels llibres en el qual s'ha basat alguna pel·lícula publicada en els anys 90 (ordenats per títol). ``` +-----------+--------------------------------------------+--------------+ | id_llibre | titol | escriptor | +-----------+--------------------------------------------+--------------+ | GJ7 | Ana Karenina | Leon Tolstoi | | GJ6 | El informe pelícano | John Grisham | | UU4 | El padrino | Mario Puzo | | DF6 | Entrevista con el vampiro | Anne Rice | | LP9 | Rita Hayworth y la redención de Shawshank | Stephen King | | AR3 | Vida de este chico | Tobias Wolff | +-----------+--------------------------------------------+--------------+ ``` 7. Obté el codi, títol i autor dels llibres en els quals no s'ha basat cap pel·lícula. ``` +-----------+---------------------------+------------+ | id_llibre | titol | escriptor | +-----------+---------------------------+------------+ | FA6 | La caída de los gigantes | Ken Follet | +-----------+---------------------------+------------+ ``` 8. Obté el nom dels gèneres de les películes on no hi actua cap actor (ordenades per nom). ``` +-----------+ | nom | +-----------+ | Animació | | Aventures | | Drama | +-----------+ ``` 9. Obté el títol dels llibres en els quals s'ha basat una pel·lícula en la qual no actua cap actor nascut en el país 'USA' (ordenades per títol). ``` +----------------------------+ | titol | +----------------------------+ | Ana Karenina | | Lo que el viento se llevó | | Pigmalion | | The sound of music | +----------------------------+ ``` 10. Quantes pel·lícules del gènere 'Comèdia' hi ha amb un sol actor amb el rol 'Secondari'? ``` +------------------+ | nombre_pelicules | +------------------+ | 2 | +------------------+ ``` 11. Obté l'any de publicació de la primera pel·lícula en la qual l'actor 'Jude Law' va actuar amb el rol 'Principal'. ``` +----------------+ | any_publicacio | +----------------+ | 2001 | +----------------+ ``` 12. Obté el codi i el nom de l'actor (o actors) més vell. ``` +----------+------------------+ | id_actor | nom | +----------+------------------+ | K58 | Stanley Holloway | +----------+------------------+ ``` 13. Obté el codi, nom i data de naixement de l'actor més vell nascut en l'any 1940. ``` +----------+------------+----------------+ | id_actor | nom | data_naixement | +----------+------------+----------------+ | C89 | James Caan | 1940-03-26 | +----------+------------+----------------+ ``` 14. Obté el gènere (o gèneres) de la pel·lícula més llarga. ``` +------------+ | nom | +------------+ | Bèl·lica | | Drama | | Romanç | +------------+ ``` 15. Obté el codi i el títol del llibres en el qual s'han basat pel·lícules on actuen actors nascuts a 'Espanya' (ordenades per títol). ``` +-----------+---------------------------+ | id_llibre | titol | +-----------+---------------------------+ | ZF4 | Come, reza, ama | | PP4 | Desayuno en Tiffanys | | DF6 | Entrevista con el vampiro | +-----------+---------------------------+ ``` 16. Obté el títol de les pel·lícules que tenen més d'un gènere que han segut publicades abans de l'any 1950 (ordenades per títol). ``` +----------------------------+ | titol | +----------------------------+ | Lo que el viento se llevó | +----------------------------+ ``` 17. Obté el nombre de pel·lícules en les quals actuen menys de 4 actors. ``` +------------------+ | nombre_pelicules | +------------------+ | 68 | +------------------+ ``` 18. Obté el codi i el nom de l'actor més jove que ha participat en una pel·lícula del gènere 'Musical'. ``` +----------+---------------+ | id_actor | nom | +----------+---------------+ | S47 | Kevin Costner | +----------+---------------+ ``` 19. Obté el codi i el nom dels països amb algun actor els quals tots els actors del país han nascut en el segle XX (ordenats per nom). ``` ``` 20. Obté el codi i el nom dels actors els quals totes les seues actuacions han segut amb el rol 'Secondari'. Cal obtindre sols els actors que han participat en alguna pel·lícula. ``` ``` 21. Obté el codi i el nom dels actors que han aparegut en totes les pel·lícules dirigides per 'Guy Ritchie' (sols si el director ha dirigit almenys una pel·lícula). ``` ``` 22. Escriu una consulta per a l'enunciat anterior amb el director 'John Steel'. ``` ``` 23. Obté el codi i el títol de les pel·lícules amb una duració menor a 100 minuts en la qual tots els actors que han actuat són del mateix país. ``` ``` 24. Obté el codi, títol i any de publicació de les pel·lícules en les quals tots els actors que han actuan han nascut abans de l'any 1943 (ha d'haver actuat almenys un actor). ``` ``` 25. Obté el codi i el nom de tots els països dels quals tots els actors han actuat almenys una vegada en una pel·lícula més llarga de 120 minuts (ordenats per nom). ``` ``` ## Consultes amb agrupació Utilitza la carpeta __/cinema/queries/group_by/__ 1. Obté el codi i el tíol dels llibres dels quals s'ha basat més d'una pel·lícula (ordenats per títol). Inclou també el nombre de pel·lícules que s'han basat en cada llibre. ``` +-----------+--------------+-----------+ | id_llibre | titol | pelicules | +-----------+--------------+-----------+ | GJ7 | Ana Karenina | 3 | | UU4 | El padrino | 3 | +-----------+--------------+-----------+ ``` 2. Obté el codi i el nom dels gènere amb més de 5 pel·lícules (ordenats per nom). Inclou també el nombre de pel·lícules de cada gènere i la duració mitjana (sense decimals). ``` +-----------+-----------+------------------+-----------------+ | id_genere | nom | nombre_pelicules | duracio_mitjana | +-----------+-----------+------------------+-----------------+ | DR5 | Acció | 8 | 138 | | DF2 | Biografia | 6 | 146 | | JJ9 | Comèdia | 8 | 110 | | GG4 | Crim | 18 | 132 | | BB5 | Drama | 38 | 134 | | KK4 | Misteri | 6 | 127 | | HH2 | Romanç | 8 | 127 | +-----------+-----------+------------------+-----------------+ ``` 3. Obté el codi i el títol de les pel·lícules publicades després de l'any 2000 i a quants gèneres pertanyen (ordenades per títol). ``` +-------------+-----------------------------------+----------------+ | id_pelicula | titol | nombre_generes | +-------------+-----------------------------------+----------------+ | 159A | Ana Karenina | 1 | | 654J | Buenas noches, y buena suerte | 2 | | 145K | Camino a la perdición | 3 | | 465H | El código da Vinci | 1 | | 158S | Enemigo a las puertas | 3 | | 369J | Golpe de efecto | 2 | | 457P | Invictus | 3 | | 159U | Mi novio es un ladrón | 1 | | 326F | Mystic river | 3 | | 189G | Ocean's Thirteen | 2 | | 658G | Sherlock Holmes | 3 | | 452W | Sherlock Holmes: Juego de sombras | 3 | | 789B | The mexican | 3 | | 455K | The monuments men | 3 | +-------------+-----------------------------------+----------------+ ``` 4. Obté els directos dels quals el nom conté 'George' i que han dirigit exactament 2 pel·lícules. ``` +-----------------+-----------+ | director | pelicules | +-----------------+-----------+ | George Clooney | 2 | | George Roy Hill | 2 | +-----------------+-----------+ ``` 5. Obté el codi i el títol de les pel·lícules on han actuat actors i que han segut classificades en exactament un gènere. Indica també el nombre d'actors de cada pel·lícula. ``` +-------------+---------------------------------+--------+ | id_pelicula | titol | actors | +-------------+---------------------------------+--------+ | 159A | Ana Karenina | 2 | | 159X | Anna Karenina | 1 | | 365N | Cortina rasgada | 3 | | 465H | El código da Vinci | 1 | | 475A | Filadelfia | 3 | | 753N | La gata sobre el tejado de zinc | 2 | | 159U | Mi novio es un ladrón | 2 | | 778E | Sin perdón | 3 | | 258M | Un tranvía llamado deseo | 2 | +-------------+---------------------------------+--------+ ``` 6. Obté el codi i el nom de tots els països amb actors, indicant quants actors de cada país han actuat en almenys una pel·lícula dels anys 60. ``` +---------+-----------+--------+ | id_pais | nom | actors | +---------+-----------+--------+ | hg45 | Alemània | 1 | | rt89 | Àustria | 1 | | ad63 | Bèlgica | 1 | | gg74 | Canadà | 1 | | we74 | Espanya | 1 | | ty11 | UK | 4 | | sf15 | USA | 4 | +---------+-----------+--------+ ``` 7. Obté el codi i el nom del gènere (o gèneres) amb més pel·lícules. ``` +-----------+-------+ | id_genere | nom | +-----------+-------+ | BB5 | Drama | +-----------+-------+ ``` 8. Obté el codi el títol i el autor dels llibres més utilitzats en pel·lícules. ``` +-----------+--------------+ | id_llibre | titol | +-----------+--------------+ | GJ7 | Ana Karenina | | UU4 | El padrino | +-----------+--------------+ ``` 9. Obté el codi i el nom del país que té més actors que han participat en exactament 2 pel·lícules. ``` +---------+-----+ | id_pais | nom | +---------+-----+ | sf15 | USA | +---------+-----+ ``` 10. Obté els anys en el quals han nascut més de 3 actors, indicant quants actors han nascut en cada any. ``` +---------------+--------+ | any_naixement | actors | +---------------+--------+ | 1940 | 4 | | 1954 | 4 | +---------------+--------+ ``` 11. Obté el codi i el títol de les pel·lícules amb una duració menor de 100 minuts en les quals tots els actors que han actuat són del mateix país. ``` +-------------+--------------------------------+ | id_pelicula | titol | +-------------+--------------------------------+ | 951D | Al caer el sol | | 654J | Buenas noches, y buena suerte | | 258S | Cuando Harry encontró a Sally | | 548J | Jamón, Jamón | | 874G | Los picapiedra | +-------------+--------------------------------+ ``` 12. Obté els directors que han dirigit més de 250 minuts entre les seues pel·lícules. ``` +----------------------+--------+ | director | minuts | +----------------------+--------+ | Clint Eastwood | 541 | | Francis Ford Coppola | 690 | | Guy Ritchie | 257 | | Steven Soderbergh | 504 | | Steven Spielberg | 296 | +----------------------+--------+ ``` ## Modificació de registres Utilitza la carpeta __/cinema/queries/update/__ ::: warning Totes les consultes es realitzen a partir de la base de dades original. ::: 1. Modifica gènere 'Bèl·lica' a 'Guerra'. 2. Modifica el director de la pel·lícula 'El rey león' a 'Rob Minkoff'. 3. Augmenta en 10 la duració de totes les pel·licules dirides per 'Clint Eastwood' 4. Modifica el rol de l'actriu 'Cameron Diaz' en la pel·lícula 'Un domingo cualquiera' al mateix rol que l'actor 'Andy García' va tindre en la pel·lícula 'Ocean's eleven'. 5. Modifica l'any de publicació de la pel·lícula 'Sonrisas y lágrimas' i el llibre sobre el qual s'ha basat aquesta pel·lícula a 1955. ## Eliminació de registres Utilitza la carpeta __/cinema/queries/delete/__ ::: warning Totes les consultes es realitzen a partir de la base de dades original. ::: 1. Elimina els gèneres associats amb la pel·licula 'Un mundo perfecto'. No elimines el gènere en sí. 2. Elimina els llibres en els quals no s'ha basat cap pel·licula. 3. Elimina les actuacions de actors que no siguen espanyols de la pel·licula 'El consejero'. 4. Elimina el llibre 'El padrino', totes les pel·licules basades en aquest llibre i totes les actuacions en aquestes pel·licules. ## Bibliografia Aquest material és propietat del profesorat de "Bases de Dades i Sistemes d'Informació". Escola Tècnica Superior d'Enginyeria Informàtica. Universitat Politècnica de València. Adaptat per <a href="/about/">Joan Puigcerver</a> ## Llicència Aquest material és propietat del profesorat de "Bases de Dades i Sistemes d'Informació". Escola Tècnica Superior d'Enginyeria Informàtica. Universitat Politècnica de València. © Tots els drets reservats. No es permet la còpia ni redistribució d'aquest material.