Mise à jour de votre base de données si la structure change

Introduction aux bases de données relationnelles en SQL

Timo Grossenbacher

Data Journalist

Le modèle de base de données actuel

Enregistrement des données DISTINCTES dans les nouvelles tables

SELECT COUNT(*)
FROM university_professors;

 count
 -----
 1377

SELECT COUNT(DISTINCT organization) 
FROM university_professors;

 count
 -----
 1287

INSERTION d’enregistrements DISTINCTS 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

Instruction INSERT INTO

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

RENOMMER une COLONNE dans les 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;

DROP une COLUMN dans les 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é de manière unique par son prénom et son nom de famille uniquement

Passons à l’action !

Introduction aux bases de données relationnelles en SQL