Mettre à jour la base de données quand la structure change

Introduction aux bases de données relationnelles en SQL

Timo Grossenbacher

Data Journalist

Modèle actuel de la base de données

Ne stocker que les données DISTINCT dans les nouvelles tables

SELECT COUNT(*)
FROM university_professors;

 count
 -----
 1377

SELECT COUNT(DISTINCT organization) 
FROM university_professors;

 count
 -----
 1287

INSERT des enregistrements DISTINCT DANS les nouvelles tables

INSERT INTO organizations 
SELECT DISTINCT organization, 
    organization_sector
FROM university_professors;

Output: INSERT 0 1287

INSERT INTO organizations 
SELECT organization, 
    organization_sector
FROM university_professors;

Output: INSERT 0 1377

L'instruction INSERT INTO

INSERT INTO table_name (column_a, column_b)
VALUES ("value_a", "value_b");

RENOMMER une COLUMN dans affiliations

CREATE TABLE affiliations (
 firstname text,
 lastname text,
 university_shortname text,
 function text,
 organisation text
);

ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

SUPPRIMER une COLUMN dans affiliations

CREATE TABLE affiliations (
 firstname text,
 lastname text,
 university_shortname text,
 function text,
 organization text
);

ALTER TABLE table_name
DROP COLUMN column_name;

SELECT DISTINCT firstname, lastname, 
    university_shortname 
FROM university_professors
ORDER BY lastname;

-[ RECORD 1 ]--------+-------------
firstname            | Karl
lastname             | Aberer
university_shortname | EPF
-[ RECORD 2 ]--------+-------------
firstname            | Reza Shokrollah
lastname             | Abhari
university_shortname | ETH
-[ RECORD 3 ]--------+-------------
firstname            | Georges
lastname             | Abou Jaoudé
university_shortname | EPF
(truncated)

(551 records)

SELECT DISTINCT firstname, lastname 
FROM university_professors
ORDER BY lastname;

-[ RECORD 1 ]----------------------
firstname | Karl
lastname  | Aberer
-[ RECORD 2 ]----------------------
firstname | Reza Shokrollah
lastname  | Abhari
-[ RECORD 3 ]----------------------
firstname | Georges
lastname  | Abou Jaoudé
(truncated)

(551 records)

Un professeur est identifié uniquement par firstname et lastname

Passons à la pratique !

Introduction aux bases de données relationnelles en SQL