Update your database as the structure changes

Introduction to Relational Databases in SQL

Timo Grossenbacher

Data Journalist

The current database model

Only store DISTINCT data in the new tables

SELECT COUNT(*)
FROM university_professors;

 count
 -----
 1377

SELECT COUNT(DISTINCT organization) 
FROM university_professors;

 count
 -----
 1287

INSERT DISTINCT records INTO the new 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

The INSERT INTO statement

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

RENAME a COLUMN in 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 a COLUMN in 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)

A professor is uniquely identified by firstname, lastname only

Let's get to work!

Introduction to Relational Databases in SQL