Practical #2: SQL Queries - “We Know the Song”

Practical Setup

Retrieving the Data

In a terminal, type the following command:

curl http://www.dequidt.me/uploads/gba/chansons.sql -o chansons.sql

Open the file with a text editor, such as kate or gedit (avoid libreoffice or openoffice if possible).

Conceptual Schema

Reconstruct a conceptual schema from the chansons.sql file, which defines the creation of relations and the insertion of data for our database.

You can draw your schema on paper or using the Modelio software:

modelio

Database Initialisation

Next we will build a songs database using the chansons.sql file. From the terminal opened previously (i.e. for retrieving the data or launching modelio), type the following commands, replacing yourLogin with your Polytech Lille login (e.g. jdequidt); the default password is postgres:

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

Then connect to your songs database on the student server:

psql -U yourLogin yourLoginSongs

You can now create the relations (i.e. the tables) and insert the data using our SQL file:

\i chansons.sql

Finally you can list the relations with the \d command and list all databases on the server with \l.

SQL Queries

Your database is ready; you can now interact with your data. For example, to list all tuples in the CHANSON table, use the following query:

SELECT * FROM chanson;

For the following questions, it is recommended to write your queries in a file (opened with kate or gedit) saved in sql format (e.g. solutionSongs.sql) and to copy-paste the queries into the terminal as you go.

1. Songs whose title starts with m.
2. The label of category 2.
3. The category numbers of the albums present in the database. (Avoid duplicates.)
4. The number of distinct categories that the albums in the database belong to. Give a meaningful label to the result column.
5. The list of people whose surname and first name are both known. Display the result in a single column containing surname and first name. Use the string concatenation operator ||. Display everything in uppercase (using the upper operator). Find a meaningful column label.
6. The list of Springsteen albums, with the number of songs on each album.
7. The list of albums that have more than 3 songs.
8. The list of performers on the album sol en si.
9. The members of the group Téléphone.
10. The list of songs with the number of their performers.
11. The titles and years of Souchon’s most recent songs (i.e. relative to all of Souchon’s songs).
12. The list of songs released before 1995 whose title contains la.
13. The list of pairs of singers. Avoid identical pairs and symmetric pairs (e.g. (chamfort, souchon) and (souchon, chamfort)).
14. The list of pairs of distinct names not known as groups. Avoid symmetries.
15. The list of albums whose title is the same as one of their songs.

Optional Queries

We strongly encourage you to continue with the following SQL queries to improve your skills:

1. Songs whose title contains mens.
2. Titles of albums classified in the rock category.
3. Titles of songs released before 1990.
4. Titles of albums released between 1990 and 1995, sorted in alphabetical order.
5. Number of albums per category.
6. Average number of songs per album.
7. The list of rock performers.
8. The number of songs performed by Alain Souchon.
9. Titles and years of songs performed by Bruce Springsteen.
10. The list of names of singers who are not performers.
11. Titles and years of the most recent songs (i.e. from the most recent year).
12. Use the UNION operator to display the list of rock or rap performers.
13. Use a query with a subquery to obtain the names that are not performers (this time we want the names, not just the references).
14. The list of albums whose year is earlier than the years of all albums performed by springsteen.
15. The list of albums whose year is earlier than at least one album performed by springsteen.