<div class="page"> <div class="cover text-center"> <img class="mx-auto" src=/itb/images/logo_mislata.png alt="logo"> # Queries Music 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 - Use the corresponding folder inside the __music/queries/__ folder. - Example: __music/queries/basic/__ - The name of each file is __query\_#{number}.sql__, where `#{number}` is the id of the query with 2 digits. - Example: __/music/queries/basic/query_01.sql__ ## Music database Using <a href="/itb/DAM-BD/UD4/exercicis/01_ddl_music.html" target="_blank" lang="en">the music database</a>: __Files:__ - <a href="/itb/DAM-BD/files/en/music/schema/create_schema_music.sql" download="create_schema_music.sql">create_schema_music.sql</a> - <a href="/itb/DAM-BD/files/en/music/data/insert_data_music.sql" download="insert_data_music.sql">insert_data_music.sql</a> <div class="mermaid"> erDiagram BAND { int id PK varchar30 name date date varchar30 country } COMPANY { int id PK varchar30 name varchar30 address varchar10 fax varchar10 phone } ALBUM }o--|| BAND : "" ALBUM }o--|| COMPANY : "" ALBUM { int id PK varchar30 name date date int band FK int company FK } SONG { int id PK varchar30 title int length } SONG_ALBUM ||--|{ ALBUM : "" SONG_ALBUM ||--|{ SONG : "" SONG_ALBUM { int song FK int album FK } ARTIST { varchar10 dni PK varchar30 name } FAN_CLUB }|--|| BAND : "" FAN_CLUB { int id PK varchar30 name varchar30 address int num int band FK } BAND_COMPONENT ||--|{ ARTIST : "" BAND_COMPONENT ||--|{ BAND : "" BAND_COMPONENT { varchar10 dni FK int band FK varchar10 role } </div> ## Queries using one single relation Use the folder __/music/queries/basic/__ 1. How many albums are there? ``` +--------+ | albums | +--------+ | 18 | +--------+ ``` 2. Show the names of the non-Spanish bands. ``` +----------------------+ | name | +----------------------+ | U2 | | Simple Minds | | Mike + The Mechanics | | Genesis | +----------------------+ ``` 3. Show the title of the songs that are more than 5 minutes long. ``` +---------------+ | title | +---------------+ | 7 Deadly Sins | | Lemon | | So Cruel | | Zooropa | +---------------+ ``` 4. Obtain the different roles that can be performed in a band. ``` +----------+ | role | +----------+ | keyboard | | bass | | voice | | guitar | | drums | +----------+ ``` 5. Obtain the name of the fan clubs and their size (number of members). The list must be sorted into ascending order according to the club size. ``` +-------------------+--------------+ | name | member_count | +-------------------+--------------+ | FanMike | 11 | | Implicado | 25 | | Bonoculture | 129 | | Waterfront | 234 | | Presuntos | 237 | | Ché U2 | 239 | | Los Culpables | 355 | | Jardín Botánico | 357 | | Troglominds | 999 | | The best mind | 1413 | | u2foryou | 1700 | | Mentes Fuertes | 1984 | | Zoomanía | 2508 | | Machines | 7789 | | Futuristas | 9850 | | Fanaticgens | 12002 | | Genefans | 23412 | +-------------------+--------------+ ``` 6. Show the name and address of the clubs with more than 500 members. ``` +----------------+----------------------+ | name | address | +----------------+----------------------+ | Zoomanía | 33, Abbey Road | | u2foryou | 23, 11th Street | | Troglominds | C/Lepe 22 | | Mentes Fuertes | Ramón y Cajal 14 | | The best mind | 24, Homeround | | Genefans | C/Visitación 34 | | Fanaticgens | Av. H. Dominicos 155 | | Futuristas | C/Alboraya 10 | | Machines | Calle 3, Lab 3 | +----------------+----------------------+ ``` ## Queries using more than one relation Use the folder __/music/queries/join/__ 1. Obtain the name and address of the fan clubs of Spanish bands, and the name of the band which they are fans of. ``` +-------------------+-----------------------+----------------------+ | name | address | band | +-------------------+-----------------------+----------------------+ | Presuntos | C/Albacete 12, bajo | Presuntos Implicados | | Implicado | Torrejón de Ardoz 12 | Presuntos Implicados | | Los Culpables | C/Maria Cristina 67 | Presuntos Implicados | | Futuristas | C/Alboraya 10 | Radio Futura | | Jardín Botánico | 203,Valencia 46004 | Radio Futura | +-------------------+-----------------------+----------------------+ ``` 2. Obtain the names of the artists that are member of any Spanish band. ``` +-------------------+ | name | +-------------------+ | J.L. Giménez | | Soledad Giménez | | Nacho Maño | | Carlos Torero | | Santiago Auserón | | Luis Auserón | | Enrique Sierra | +-------------------+ ``` 3. Obtain the name of the albums that contain some song that is more than 5 minutes long. ``` +----------------------+ | name | +----------------------+ | Zooropa | | Achtung baby | | Good news F.N. world | +----------------------+ ``` 4. Obtain the title of the songs that have the same title that the album in which the song appears. ``` +----------------------+ | title | +----------------------+ | October | | Zooropa | | The unforgettable fi | | Once Upon A Time | | Living years | | Word of mouth | | Invisible touch | | De sol a sol | | Ser de agua | | Alma de blues | +----------------------+ ``` 5. Show the name and address of the companies which have recorded a album whose title begins with 'A'. ``` +--------+------------+ | name | address | +--------+------------+ | Island | 67, JB St. | | WEA | L Hoyos 42 | +--------+------------+ ``` 6. Show title of the songs of Spanish bands with a length of 4 minutes. ``` +-------------------+ | title | +-------------------+ | De sol a sol | | Sed de amor | | Al atardecer | | Barbara del campo | | Sentir tu calor | | Alma de blues | | Encadenada | | Río Po | | La ley | | Semilla negra | | Tormenta de arena | | 37 grados | +-------------------+ ``` ## Queries with subqueries Use the folder __/music/queries/subqueries/__ 1. Show the name of the records recorded by the oldest band. ``` ``` 2. Obtain the name of the records which have been recorded by bands with a fan club greater than 5,000 (more than 5,000 members) ``` ``` 3. Show the name of the club/s with the greatest number of fans. Do also indicate its number of fans. ``` ``` 4. Show the title of the longest songs indicating also their length. ``` ``` ## Queries with GROUP BY Use the folder __/music/queries/group_by/__ 1. Obtain the names of the Spanish bands and the total amount of their fans. ``` ``` 2. Obtain the name and number of components of any band with more than 2 members. ``` ``` 3. Obtain the number of records of each band. ``` ``` 4. Obtain the number of songs recorded by each company and the company address. ``` ``` ## 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.