Example SQL Queries

##Quick Intro

SQL is a standard language for accessing databases. We use SQL queries to retrieve information that we have stored previously in our databases (download databases here). Although SQL format and syntax seems complicated, it is pretty easy to understand once you have a basic grasp of what is going on. A tutorial can be found here and a good cheat sheet here.

The keywords used in the examples below:

SELECT ... FROM  |  LEFT JOIN ... ON   |   WHERE   |   NULL  |  INNER JOIN ... ON  |  ORDER BY ... ASC/DESC  |  AND ##Examples

An unpacking of each company involved in a PEL either as an license holder, operator, or minority interest. This would include details on company headquarters, principals, shareholders/directors, links to further information.

**SELECT** * **FROM** license_holdings **LEFT JOIN** licenses **ON** (license_holdings.license_holding_license_id = licenses.license_id) **LEFT JOIN** companies **ON** (license_holdings.license_holding_company_id = companies.company_id) **WHERE** license_holdings.license_holding_end_date IS NULL; **SELECT** * **FROM** operators **LEFT JOIN** licenses **ON** (operators.operator_license_id = licenses.license_id) **LEFT JOIN** companies **ON** (operators.operator_company_id = companies.company_id) **WHERE** operators.operator_end_date **IS NULL**; **SELECT** * **FROM** roles **LEFT JOIN** people **ON** (people.person_id = roles.role_person_id) **LEFT JOIN** companies **ON** (roles.role_company_id = companies.company_id);

-- Some means of making cross links i.e. when a name is linked to two or more companies or has been linked to other companies in the past

**SELECT** l.*, people.* **FROM** roles l **INNER JOIN** roles r **ON** (l.role_company_id != r.role_company_id **AND** l.role_person_id = r.role_person_id) **LEFT JOIN** people **ON** (l.role_person_id = people.person_id)

-- Information on historic changes of ownership among these companies (going back to 2008 at least) in terms of people but also shareholdings (percentages). Information should be viewable/accessible from various starting points i.e.. PEL, licence block, company, individual, Namibian or foreign etc.

**SELECT** * **FROM** company_holdings; -- Number of licenses in which NAMCOR has a share **SELECT** * **FROM** licenses **INNER JOIN** license_holdings **ON** (license_holdings.license_holding_license_id = licenses.license_id) **WHERE** license_holdings.license_holding_company_id = 42;

-- Most transparent PELs (i.e where public info is available).

**SELECT** * **FROM** companies **LEFT JOIN** jurisdictions **ON** (companies.company_jurisdiction_id = jurisdictions.jurisdiction_id) **LEFT JOIN** tax_havens **ON** (jurisdictions.jurisdiction_tax_haven_id = tax_havens.tax_haven_id) **ORDER BY** companies.company_untraceable ASC, tax_havens.tax_haven_secrecy_score **ASC**

-- Most secretive (Namibian companies that can't be traced at the Registrar of Companies here and those international ones based in offshore tax havens like the Bermudas, Virgin Islands etc)

**SELECT** * **FROM** companies **LEFT JOIN** jurisdictions **ON** (companies.company_jurisdiction_id = jurisdictions.jurisdiction_id) **LEFT JOIN** tax_havens **ON** (jurisdictions.jurisdiction_tax_haven_id = tax_havens.tax_haven_id) **ORDER BY** companies.company_untraceable **DESC**, tax_havens.tax_haven_secrecy_score **DESC**

-- Timeline of a activity in terms of granting of licences and transfers of ownership

**SELECT** * **FROM** transfers;