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

Practical #4: International Exchange Students

Introduction

We provide you with two copies of the data: one in an .ods file that you can open with Libreoffice Calc, and a second copy in .sql files that you must use to create the database. Note for example the different ways the data is structured in each approach.

You are asked to answer each question twice: once using the spreadsheet and once with a SQL query. Work through one question at a time, alternating between the two technologies. You are free to use advanced Calc features (functions, formulas, filters, …).

Spreadsheet Setup

Download the file BD_excel.ods, which contains the data “Students going abroad”, with the command:

curl http://www.dequidt.me/uploads/gba/BD_excel.ods -o BD_excel.ods

SQL Setup

The relation definitions and the “Students going abroad” data are in the files BD_relationsEtudiants.sql and BD_donneesEtudiants.sql respectively. Run the following commands, replacing yourLogin with your postgres account name.

To download the files to the current directory, use the curl command:

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

Set the environment variable that specifies the postgres server to use:

export PGHOST=serveur-etu.polytech-lille.fr

Configure the query display in postgres to allow viewing large amounts of data:

export PAGER='less -SFMX'

Create the database with your Polytech Lille login:

createdb -U yourLogin yourLoginStudents
Password: postgres

Then connect to your students database on the database server:

psql -U yourLogin yourLoginStudents
Password: postgres

You can now create the relations (i.e. the tables) and insert the data using the SQL files you downloaded:

\i BD_relationsEtudiants.sql
\i BD_donneesEtudiants.sql

Finally you can list the relations with the command:

\d

SQL Queries

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

SELECT * FROM etudiant ;

Next, to get an idea of the database contents, try a more complex query (you can copy-paste the queries below). Pay attention to join conditions, composite primary keys, and attributes with the same name that require disambiguation (e.g. the attribute dept appears in two different relations). Note carefully which tables are listed in each query. Navigate with the arrow keys.

SELECT  *
FROM    etudiant, formation, effectue, stage, pays, entreprise
WHERE   stage.etudiant=numEt  AND entreprise=numEn AND pays=numPa
  AND   stage.formation=numFo AND stage.dept=formation.dept
  AND   effectue.stage=numSt  AND effectue.etudiant=numEt ;

You can use the following query, which reproduces the Excel spreadsheet (joining 8 of the 9 SQL tables). Look at the chosen columns, the duration calculations, and the ordering of results by student number.

SELECT  stage.dept, numfo, CONCAT(anneedeb || '-' || anneefin) AS annee, numEt,
        Etudiant.civilite, nomEt, prenomEt, datedeb, datefin,
        (datefin::timestamp - datedeb::timestamp) AS duree_en_jours,
        job, fax AS fax_signe_le, convention AS convention_signee_le,
        nomPa, nomEn, Contact.civilite, Contact.fonction, ville,
        sujet, remuneration
FROM    etudiant, formation, effectue, stage, pays, entreprise, suit, contact
WHERE   stage.etudiant     = numEt
AND     stage.entreprise   = numEn
AND     pays               = numPa
AND     stage.formation    = numFo
AND     stage.dept         = formation.dept
AND     effectue.stage     = numSt
AND     effectue.etudiant  = numEt
AND     formation.numFo    = suit.formation
AND     formation.dept     = suit.dept
AND     suit.etudiant      = numEt
AND     contact.entreprise = numEn
AND     contact.stage      = stage.numSt
ORDER BY numet ;

Exercises

Difficulty: Rx

Answer the following questions using Libreoffice Calc and by writing SQL queries:

  1. Which students went abroad for their company internship?
  2. How many students went to China in 2012?
  3. What are the first and last names of students who went abroad more than once?
  4. How many students went abroad more than once?
  5. Where did Remi Carton go?
  6. Which American organisations have hosted students from Polytech?
  7. What are the names and countries of companies that have hosted more than one student?
  8. What is the average remuneration of students?
  9. Which students went to English-speaking countries (i.e. United States, Australia, Ireland, United Kingdom, Canada)?
  10. Which department sent the most students abroad?
  11. What is Charlotte Capelle’s internship topic?
  12. Who are the contacts at INRS?
  13. During what period did Justine Vanneste complete her internship?
  14. What were the location and internship topic for students who went to Poland?
  15. Which student received the highest remuneration during their internship abroad?