SQL-Basics: Relations

As the Junior Data Scientist of Knight Industries we created a table to keep track of all our operatives: SQL-Basics: Create, Read, Update, Delete

Devon asks us to keep track of our operations aka missions as well. For the first implementation let us assume that a mission has one operative and an operative can participate in multiple missions. that’s what we call an 1 to many relationship.

  +------------+                 +----------+
  |            |1   active in   n|          |
  | operative  +-----------------+ missions |
  |            |                 |          |
  +------------+                 +----------+

Missions have an id, a code name like “Phantom Liberty”, an operative id and a total cost.

To say that the operative id is equivalent to the id in the operative column we use the FOREIGN KEY constraint.

So let’s create the new table in our database:

CREATE TABLE missions 
(id INTEGER, code_name TEXT, operative_id INTEGER, total_cost DECIMAL(15,2), PRIMARY KEY(id), FOREIGN KEY (operative_id) REFERENCES operatives(id));
INSERT INTO missions VALUES (1, "Knight of the Phoenix", 1, 6246382.43);
INSERT INTO missions VALUES (2, "Deadly Maneuvers", 1, 4568893.53);

Our first join

SELECT missions.code_name, operatives.name FROM missions LEFT JOIN operatives ON missions.operative_id = operatives.id;

More on joins https://dsin.files.wordpress.com/2013/03/sqljoins_cheatsheet.png