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 ki_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 ki_missions VALUES (1, "Knight of the Phoenix", 1, 6246382.43);
INSERT INTO ki_missions VALUES (2, "Deadly Maneuvers", 1, 4568893.53);
Our first join
SELECT ki_missions.code_name, ki_operatives.name FROM ki_missions LEFT JOIN ki_operatives ON ki_missions.operative_id = ki_operatives.id;
More on joins https://dsin.files.wordpress.com/2013/03/sqljoins_cheatsheet.png
Next chapter: SQL-Functions – SQL-Basics 3
