Skip to main content
Databases
Databases
GBA
Rx 2h Practical #2: SQL Queries - "We Know the Song"

Practical #3: SQL Queries - “We Know the Song” (continued)

Practical Setup

This practical is a continuation of the previous one and operates on the same data. If you have already created the yourLoginSongs database (replacing yourLogin with your Polytech Lille Linux login), you can type the following commands. Otherwise you need to redo part 3 of Practical #2.

export PGHOST=serveur-etu.polytech-lille.fr
export PAGER='less -SFMX'
psql -U yourLogin yourLoginSongs

Inserting and Updating Data, and Altering Tables

Difficulty: Rx
  1. How many songs are in the database?
  2. Check whether the song Mistral Gagnant is in the database. If not, add this song, released in 1985.
  3. Add Renaud to the list of singers.
  4. Try to add to the database the information that Renaud is the performer of the song Mistral Gagnant. Explain the error that is displayed.
  5. What new piece of information is needed? Look it up and modify the database so that the previous error disappears.
  6. We want to add the song Petite Marie, sung by Cabrel, from the album ‘Murs de poussière’, released in 1977. Which pieces of information are already in the database?
  7. Perform all the necessary operations to add this song correctly.
  8. Perform all the necessary operations to add any missing information correctly (e.g. song, album, performer).
  9. Modify the database so that all category labels appear in uppercase. (Use UPDATE.)
  10. Rename the column libelle to libelle_majuscules. (Use ALTER.)
  11. Add a ‘date of birth’ attribute to the singer table.
  12. Update the information for singers Renaud and Cabrel by adding their dates of birth.