Retrieve Data Using SQL

In this part of the course, we will be focusing on querying a database using SQL.

However, before we can query it, it would behoove us to know how to create a table, and how to implement keys.

Let’s create an entity table, which will be populated with information about offshore companies contained in the Panama Papers.

CREATE TABLE entity (    id INTEGER,    name TEXT NOT NULL,    jurisdiction TEXT,    jurisdiction_description TEXT,    company_type TEXT,    address_id INTEGER,    incorporation_date DATE,    inactivation_date DATE,    status TEXT,    service_provider TEXT,    country_codes TEXT,    countries TEXT,    source TEXT,    PRIMARY KEY(id),    FOREIGN KEY(address_id) REFERENCES address(id))

It’s fairly straightforward : we use CREATE TABLE, followed by the name of the table to be created. We open a parenthesis and list the table’s columns, separating them with a comma. For each column, we specify a name and type.

Note that the keywordsNOT NULL are used to ensure that the name value be populated every time we insert a new row. This means we will never add an offshore company without specifying its name!

We have also provided the foreign and primary keys of the entity table, in the following clauses:

• PRIMARY KEY 

• FOREIGN KEY ... REFERENCES ... 

For the foreign key, what we have written here means that the address_id column of the entity table references the id column of the address table.

Now that we have created the structure of our table, let’s insert a row.

INSERT INTO entity (id, name, jurisdiction, jurisdiction_description, incorporation_date) VALUES (0, 'random company', 'IMG', 'Imaginary Country', '2020-01-01');

Here we use  INSERT INTO  to specify the table to be populated. Then, between parentheses, we specify the columns we wish to populate (id, name, etc.). Finally, we add the values to be inserted after the keyword  VALUES  : these values must be written in the same order as the columns.

It is also possible to populate a table from a CSV file. A CSV file presents tabular data in exactly the same form as the table of a database; if you are not familiar with this format, please take a look here. ;)

However, each RDBMS has its own way of importing data from such files. :'( Some use SQL syntax; for others, it’s a bit more complicated. It will depend on which RDBMS you are using...

Summary

• To create a table, writeCREATE TABLE.

• To insert data, writeINSERT INTO (...) VALUES (...).