<div class="page"> <div class="cover text-center"> <img class="mx-auto" src=/itb/images/logo_mislata.png alt="logo"> # Queries Cinema Database <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} ## Introduction - Upload all .sql in the __/cinema/queries__ folder in your Git repository. - Use the corresponding folder inside the __queries/__ folder. - Example: __cinema/queries/basic/__ - The name of each file is __query\_#{number}.sql__, where `#{number}` is the id of the query with 2 digits. - Example: __/cinema/queries/basic/query_01.sql__ ## Cinema Database Using the __cinema__ database: __Files:__ - <a href="/itb/DAM-BD/files/en/cinema/schema/create_schema_cinema.sql" download="create_schema_cinema.sql">create_schema_cinema.sql</a> - <a href="/itb/DAM-BD/files/en/cinema/data/insert_data_cinema.sql" download="insert_data_cinema.sql">insert_data_cinema.sql</a> <div class="mermaid"> erDiagram COUNTRY { type id PK type name } ACTOR }|--|| COUNTRY : "" ACTOR { type id PK type name type birth_date type country FK } BOOK { type id PK type title type year type writer } FILM }|--|| BOOK : "" FILM { type id PK type title type year type length type director type book FK } PERFORMS }|--|| ACTOR : "" PERFORMS }|--|| FILM : "" PERFORMS { type actor FK type film FK type role } GENRE { type id PK type name } GENRE_FILM }|--|| GENRE : "" GENRE_FILM }|--|| FILM : "" GENRE_FILM { type film FK type genre FK } </div> ## Queries using one single relation Use the folder __/cinema/queries/basic/__ 1. Obtain the code of the countries with some actor in ascending order. ``` +---------+ | country | +---------+ | ad63 | | gg74 | | hg45 | | nb12 | | rt89 | | sd53 | | sf15 | | ty11 | | we74 | | zf58 | +---------+ ``` 2. Obtain the code and the title of the movies released before 1970 which are not based on a book. Sort the movies by the title. ``` +------+--------------------------+ | id | title | +------+--------------------------+ | 357L | Cleopatra | | 365N | Cortina rasgada | | 332D | Dos hombres y un destino | +------+--------------------------+ ``` 3. Obtain the code and name of the actors which name includes "John". ``` +-----+--------------+ | id | name | +-----+--------------+ | A62 | John Goodman | +-----+--------------+ ``` 4. Obtain the code and title of the movies with a length greater than 120 minutes, released in the 80's. ``` +------+------------------------------------+ | id | title | +------+------------------------------------+ | 365A | Indiana Jones y la última cruzada | +------+------------------------------------+ ``` 5. Obtain the code and title of the movies based on a book, directed by a director with the last name 'Pakula'. ``` +------+----------------------+ | id | title | +------+----------------------+ | 856A | El informe pelícano | +------+----------------------+ ``` 6. How many movies are there with a length greater than 120 minutes released in the 80's? ``` +----------------+ | how_many_films | +----------------+ | 1 | +----------------+ ``` 7. How many movies have been classified in the genres with codes 'BB5', 'GG4', o 'JH6' ? ``` +----------------+ | how_many_films | +----------------+ | 43 | +----------------+ ``` 8. In which year was published the oldest book? ``` +------+ | year | +------+ | 1877 | +------+ ``` 9. What is the average length of the movies released in 1987? ``` +----------+ | average | +----------+ | 119.5000 | +----------+ ``` 10. What is the total length of the movies directed by 'Steven Spielberg'? ``` +--------------+ | total_length | +--------------+ | 296 | +--------------+ ``` ## Queries using more than one relation Use the folder __/cinema/queries/join/__ 1. Obtain the code and title of the movies in which act an actor with the same name as the movie director (sorted by title). ``` +------+-------------------------------+ | id | title | +------+-------------------------------+ | 654J | Buenas noches, y buena suerte | | 778E | Sin perdón | | 455K | The monuments men | | 118E | Un mundo perfecto | +------+-------------------------------+ ``` 2. Obtain the code and title of the movies of the genre 'Comedia' (sorted by title). ``` +------+--------------------------------+ | id | title | +------+--------------------------------+ | 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. Obtain the code and title of the movies based on a book published before 1950. ``` +------+----------------------------+ | id | title | +------+----------------------------+ | 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. Obtain the code and name of the countries in which were born the actors acting in movies of the genre 'Comedia' (sorted by name). ``` +------+----------+ | id | name | +------+----------+ | ad63 | Bélgica | | we74 | España | | sf15 | USA | +------+----------+ ``` ## Queries with subqueries Use the folder __/cinema/queries/subqueries/__ 1. (Same as join/query01) Obtain the code and title of the movies in which act an actor with the same name as the movie director (sorted by title). ``` +------+-------------------------------+ | id | title | +------+-------------------------------+ | 654J | Buenas noches, y buena suerte | | 778E | Sin perdón | | 455K | The monuments men | | 118E | Un mundo perfecto | +------+-------------------------------+ ``` 2. (Same as join/query02) Obtain the code and title of the movies of the genre 'Comedia' (sorted by title). ``` +------+--------------------------------+ | id | title | +------+--------------------------------+ | 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. (Same as join/query03) Obtain the code and title of the movies based on a book published before 1950. ``` +------+----------------------------+ | id | title | +------+----------------------------+ | 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. (Same as join/query04) Obtain the code and name of the countries in which were born the actors acting in movies of the genre 'Comedia' (sorted by name). ``` +------+----------+ | id | name | +------+----------+ | ad63 | Bélgica | | we74 | España | | sf15 | USA | +------+----------+ ``` 5. Obtain the code and name of the actors born before 1950 who perform the role ‘Principal’ in some movie (sorted by name). ``` +-----+---------------------+ | id | name | +-----+---------------------+ | 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. Obtain the code, title, and author of the books used in some movie released in the 90’s (sorted by title). ``` +-----+--------------------------------------------+--------------+ | id | title | writer | +-----+--------------------------------------------+--------------+ | 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. Obtain the code, title, and author of the books not used in any movie. ``` +-----+---------------------------+------------+ | id | title | writer | +-----+---------------------------+------------+ | FA6 | La caída de los gigantes | Ken Follet | +-----+---------------------------+------------+ ``` 8. Obtain the name of the genre (or genres) of the movies in which there is no actor acting (sorted by name). ``` +------------+ | name | +------------+ | Animación | | Aventuras | | Drama | +------------+ ``` 9. Obtain the title of the books used in some movie with no actors from the country called ‘USA’ (sorted by title). ``` +----------------------------+ | title | +----------------------------+ | Ana Karenina | | Lo que el viento se llevó | | Pigmalion | | The sound of music | +----------------------------+ ``` 10. How many movies of the genre ‘Comedia’ are there with only one actor playing the role 'Secundario'? ``` +----------------+ | how_many_films | +----------------+ | 2 | +----------------+ ``` 11. Obtain the release year of the first movie in which the actor named ‘Jude Law’ performed the ‘Principal’ role. ``` +------+ | year | +------+ | 2001 | +------+ ``` 12. Obtain the code and name of the oldest actor (or actors). ``` +-----+------------------+ | id | name | +-----+------------------+ | K58 | Stanley Holloway | +-----+------------------+ ``` 13. Obtain the code, name, and date of birth of the oldest actor born in 1940. ``` +-----+------------+------------+ | id | name | birth_date | +-----+------------+------------+ | C89 | James Caan | 1940-03-26 | +-----+------------+------------+ ``` 14. Obtain the genre (or genres) of the longest movie. ``` +---------+ | name | +---------+ | Bélica | | Drama | | Romance | +---------+ ``` 15. Obtain the code and title of the book used in the movies in which act actors from the country called 'España' (sorted by title). ``` +-----+---------------------------+ | id | title | +-----+---------------------------+ | ZF4 | Come, reza, ama | | PP4 | Desayuno en Tiffanys | | DF6 | Entrevista con el vampiro | +-----+---------------------------+ ``` 16. Obtain the title of the movies of more than one genre released before 1950 (sorted by title). ``` +----------------------------+ | title | +----------------------------+ | Lo que el viento se llevó | +----------------------------+ ``` 17. Obtain the number of movies with less than 4 actors. ``` +----------------+ | how_many_films | +----------------+ | 68 | +----------------+ ``` 18. Obtain the code and name of the youngest actor who has participated in a movie of the genre 'Musical'. ``` +-----+---------------+ | id | name | +-----+---------------+ | S47 | Kevin Costner | +-----+---------------+ ``` 19. Obtain the code and name of the countries with actors such that all the actors from that country were born in the XX century (sorted by name). ``` ``` 20. Obtain the code and name of the actors such that all their roles have been ‘Secundario’. We are only interested in actors who have acted in some movie. ``` ``` 21. Obtain the code and name of the actors who have appeared in all the movies directed by ‘Guy Ritchie’ (only if this director has directed at least one movie). ``` ``` 22. Write a query for the previous problem but using the director named ‘John Steel’. ``` ``` 23. Obtain the code and title of the movies with a length shorter than 100 minutes in which all the actors who have acted are from the same country. ``` ``` 24. Obtain the code, title, and year of release of the movies in which some actor has acted, but only if all the actors of that movie were born before 1943. ``` ``` 25. Obtain the code and name of all the countries if all the actors from that country have acted in at least one movie with a length greater than 120 minutes (sorted by name). ``` ``` ## Queries with group_by Use the folder __/cinema/queries/group_by/__ 1. Obtain the code and title of the book (or books) used in more than one movie. Include also how many movies have been based on that book. ``` +-----+--------------+-------+ | id | title | films | +-----+--------------+-------+ | GJ7 | Ana Karenina | 3 | | UU4 | El padrino | 3 | +-----+--------------+-------+ ``` 2. Obtain for each genre with more than 5 movies, the code and the name of the genre, including the amount of movies of that genre and the average length of all that movies. (sorted by name). You can use the ROUND function. ``` +-----+------------+----------------+----------------+ | id | name | how_many_films | average_legnth | +-----+------------+----------------+----------------+ | DR5 | Acción | 8 | 138 | | DF2 | Biografía | 6 | 146 | | JJ9 | Comedia | 8 | 110 | | GG4 | Crimen | 18 | 132 | | BB5 | Drama | 38 | 134 | | KK4 | Misterio | 6 | 127 | | HH2 | Romance | 8 | 127 | +-----+------------+----------------+----------------+ ``` 3. Obtain the code and title of the movies released after the 2000 year, and how many genres they have (if they have genre) sorted by title. ``` +------+-----------------------------------+--------+ | id | title | genres | +------+-----------------------------------+--------+ | 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. Obtain the directors who have directed two (exactly 2) movies whose name contains the string ‘George’. ``` +-----------------+-------+ | director | films | +-----------------+-------+ | George Clooney | 2 | | George Roy Hill | 2 | +-----------------+-------+ ``` 5. Obtain for each movie with some actor and classified in one (and only one) genre, the code, title and amount of actors who have acted in that movie. ``` +------+---------------------------------+--------+ | id | title | 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. Obtain the code and name of all the countries with actors, indicating how many actors from that country have acted in at least one movie from the 60’s. ``` +------+----------+--------+ | id | name | actors | +------+----------+--------+ | hg45 | Alemania | 1 | | rt89 | Austria | 1 | | ad63 | Bélgica | 1 | | gg74 | Canadá | 1 | | we74 | España | 1 | | ty11 | UK | 4 | | sf15 | USA | 4 | +------+----------+--------+ ``` 7. Obtain the code (or codes) and the genre (or genres) with most movies. ``` +-----+-------+ | id | name | +-----+-------+ | BB5 | Drama | +-----+-------+ ``` 8. Obtain the code/s, title/s and author/s of the book most used in movies. ``` +-----+--------------+ | id | title | +-----+--------------+ | GJ7 | Ana Karenina | | UU4 | El padrino | +-----+--------------+ ``` 9. Obtain the code and name of the country which has most actors who have participated in exactly 2 movies. ``` +------+------+ | id | name | +------+------+ | sf15 | USA | +------+------+ ``` 10. Obtain the year (or years) in which more than 3 actors were born, indicating how many actors were born in that year. ``` +------+--------+ | year | actors | +------+--------+ | 1940 | 4 | | 1954 | 4 | +------+--------+ ``` 11. Obtain the code and title of the movies with a length shorter than 100 minutes in which all the actors who have acted are from the same country. ``` +------+--------------------------------+ | id | title | +------+--------------------------------+ | 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. Obtain the directors who have directed more than 250 minutes (considering the length of all their movies). ``` +----------------------+---------+ | director | minutes | +----------------------+---------+ | Clint Eastwood | 541 | | Francis Ford Coppola | 690 | | Guy Ritchie | 257 | | Steven Soderbergh | 504 | | Steven Spielberg | 296 | +----------------------+---------+ ``` ## 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.