Défi [Découverte de PostgreSQL]

Défi

On dispose des informations suivantes sur des étudiants :

etu
pknumsecu	knumetu	nom	prenom
1800675001066	AB3937098X	Dupont	Pierre
2820475001124	XGB67668	Durand	Anne

pknumsecu est une clé primaire qui contient exactement 13 caractères, knumetu est une chaîne de caractères d'au plus 20 caractères qui ne peut être nulle.

On dispose de plus des informations suivantes sur des cours :

cours
pkcode	fketu
NF17	1800675001066
NF26	1800675001066
NF29	1800675001066

pkcode est une chaîne de 4 caractères exactement et fketu est une clé étrangère référençant pknumsecu. Ces deux attributs forment la clé primaire.

Question

Créer dans la base de données les tables etu et cours, et y ajouter les données.

Solution

CREATE TABLE etu (
  pknumsecu CHAR(13) PRIMARY KEY,
  knumetu VARCHAR(20) UNIQUE NOT NULL,
  nom VARCHAR,
  prenom VARCHAR
);

CREATE TABLE etu (
  pknumsecu CHAR(13) PRIMARY KEY,
  knumetu VARCHAR(20) UNIQUE NOT NULL,
  nom VARCHAR,
  prenom VARCHAR
);

CREATE TABLE cours (
  pkcode CHAR(4) NOT NULL,
  fketu CHAR(13) NOT NULL,
  PRIMARY KEY (pkcode, fketu),
  FOREIGN KEY (fketu) REFERENCES etu(pknumsecu)
);

CREATE TABLE cours (
  pkcode CHAR(4) NOT NULL,
  fketu CHAR(13) NOT NULL,
  PRIMARY KEY (pkcode, fketu),
  FOREIGN KEY (fketu) REFERENCES etu(pknumsecu)
);

INSERT INTO etu (pknumsecu, knumetu, nom, prenom)
VALUES ('1800675001066', 'AB3937098X', 'Dupont', 'Pierre');
INSERT INTO etu (pknumsecu, knumetu, nom, prenom)
VALUES ('2820475001124', 'XGB67668', 'Durand', 'Anne');

INSERT INTO etu (pknumsecu, knumetu, nom, prenom)
VALUES ('1800675001066', 'AB3937098X', 'Dupont', 'Pierre');

INSERT INTO etu (pknumsecu, knumetu, nom, prenom)
VALUES ('2820475001124', 'XGB67668', 'Durand', 'Anne');

INSERT INTO cours (pkcode, fketu)
VALUES ('NF17', '1800675001066');
INSERT INTO cours (pkcode, fketu)
VALUES ('NF26', '1800675001066');
INSERT INTO cours (pkcode, fketu)
VALUES ('NF29', '1800675001066');

INSERT INTO cours (pkcode, fketu)
VALUES ('NF17', '1800675001066');

INSERT INTO cours (pkcode, fketu)
VALUES ('NF26', '1800675001066');

INSERT INTO cours (pkcode, fketu)
VALUES ('NF29', '1800675001066');

Question

Via psql, quelles sont les commandes pour accéder au catalogue et vérifier la création de la table ?

Solution

\d
\d etu
\d cours

\d
\d etu
\d cours

Question

Utiliser deux instructions SELECT pour vérifier le contenu de la table.

Solution

SELECT *
FROM etu;

SELECT *
FROM etu;

SELECT *
FROM cours;

SELECT *
FROM cours;

Nous allons à présent réinitialiser la base avec des données contenues dans un fichier.

Question

Exécuter les instructions nécessaires afin de supprimer les données existantes dans les tables et non les tables.

Vérifier que les tables sont vides.

Indice

On utilisera l'instruction DELETE du SQL.

Solution

DELETE FROM cours;
SELECT * FROM cours;
DELETE FROM etu;
SELECT * FROM etu;

DELETE FROM cours;
SELECT * FROM cours;

DELETE FROM etu;
SELECT * FROM etu;

Question

On dispose des deux fichiers de données suivants gi.csv et P2018.csv.

Téléchargez le fichier gi.csv sur votre ordinateur.

gi.csv [csv, 91 o]

Téléchargez le fichier P2018.csv sur votre ordinateur.

P2018.csv [csv, 210 o]

Quel est le type de ce fichier et quelles informations contiennent-ils ?

Indice

fr.wikipedia.org/wiki/Comma-separated_values

Solution

Ce sont des fichiers CSV qui contiennent respectivement des informations sur les étudiants et sur les cours.

Question

Exécuter le code suivant en remplaçant /tmp par l'emplacement des fichiers sur notre ordinateur.

\copy etu (pknumsecu, knumetu, nom, prenom) FROM '/tmp/gi.csv' WITH CSV DELIMITER ';' QUOTE '"'
\copy cours (fketu, pkcode) FROM '/tmp/P2018.csv' WITH CSV DELIMITER ';' QUOTE '"'

\copy etu (pknumsecu, knumetu, nom, prenom) FROM '/tmp/gi.csv' WITH CSV DELIMITER ';' QUOTE '"'
\copy cours (fketu, pkcode) FROM '/tmp/P2018.csv' WITH CSV DELIMITER ';' QUOTE '"'

Montrer que de nouvelles données ont été insérées dans les tables etu et cours.

Solution

SELECT * 
FROM etu;

SELECT * 
FROM etu;

   pknumsecu   |  knumetu   |   nom    | prenom  
---------------+------------+----------+---------
 1800675001066 | AB3937098X | Dupont   | Pierre
 2820475001124 | XGB67668   | Durand   | Anne
 1             | A          | Dupont   | Pierre
 2             | B          | Durand   | Georges
 3             | C          | Duchemin | Paul
 4             | D          | Dugenou  | Alain
 5             | E          | Dupied   | Albert

   pknumsecu   |  knumetu   |   nom    | prenom  
---------------+------------+----------+---------
 1800675001066 | AB3937098X | Dupont   | Pierre
 2820475001124 | XGB67668   | Durand   | Anne
 1             | A          | Dupont   | Pierre
 2             | B          | Durand   | Georges
 3             | C          | Duchemin | Paul
 4             | D          | Dugenou  | Alain
 5             | E          | Dupied   | Albert

SELECT * 
FROM cours;

SELECT * 
FROM cours;

 pkcode |     fketu     
--------+---------------
 NF17   | 1800675001066
 NF26   | 1800675001066
 NF29   | 1800675001066
 NF17   | 1            
 NF18   | 1            
 NF19   | 1            
 NF20   | 1            
 LA13   | 1            
 PH01   | 1            
 NF17   | 2            
 NF18   | 2            
 NF19   | 2            
 TN01   | 2            
 LA14   | 2            
 PH01   | 2            
 NF17   | 3            
 NF18   | 3            
 NF19   | 3            
 NF21   | 3            
 LA14   | 3            
 PH01   | 3 

 pkcode |     fketu     
--------+---------------
 NF17   | 1800675001066
 NF26   | 1800675001066
 NF29   | 1800675001066
 NF17   | 1            
 NF18   | 1            
 NF19   | 1            
 NF20   | 1            
 LA13   | 1            
 PH01   | 1            
 NF17   | 2            
 NF18   | 2            
 NF19   | 2            
 TN01   | 2            
 LA14   | 2            
 PH01   | 2            
 NF17   | 3            
 NF18   | 3            
 NF19   | 3            
 NF21   | 3            
 LA14   | 3            
 PH01   | 3