SQL (Structured Query Language, also known as sequel) is a programming language that is designed to create and manage databases. SQL is built on a basis of RDBMS (Relational Database Management System) and has a lot of extension database systems like MariaDB, MySQL, Oracle and PostgreSQL to name a few.

It is very useful when dealing with databases that have connections or relationships between them. So instead of having a database with just one big table full of data, you can make different tables and just connect them by a key column (a column that is the same in both of the tables). This way your data is more manageable and structured (example 1). Later you can write queries to get the data you need and analyse or visualise it. You can also have a website show up-to-date data from an SQL database.

Example 1. Two tables connected by a key column – customer_id.

How does it work?

First of all, you need to create a database and some tables and load your data into them. Once you have your database the way you like it, you can write queries to call out the data. Some of the most used commands for those occasions are (taken from the example):

• CREATE DATABASE Shop; – creates a new database;
• CREATE TABLE Customers (customer_id INT, first_name VARCHAR(150), last_name VARCHAR(150),  email VARCHAR(150));  – creates a new table;
• INSERT INTO Customers (customer_id, first_name, last_name, email) VALUES (1, ‘Mary’, ‘Smith’, ‘marysmith@gmail.com’); – adds data to existing table;
• SELECT * FROM Customers; – gives all the data from the table.

Because SQL is often used when there are multiple connected tables, it is very common to join tables together in a query to get the needed data. There are many ways you can join your tables (example 2):

• (INNER) JOIN – returns data that have matching values in both tables;
• LEFT JOIN – returns data from the left table and the matching values from the right table;
• RIGHT JOIN – returns data from the right table and the matching values from the left table;
• FULL JOIN – returns data that are a match from either one of the tables.

Example 2. Types of join commands.

W3Schools: https://www.w3schools.com/sql/sql_join.asp.

Let’s use LEFT JOIN for this example. As you can see in the picture below, the values of the table are given to us as described earlier: to the data from the left table (in our case table that is mentioned first – Customers) is added matching data from the right table (table named Orders).

Query:

SELECT * FROM Customers

      LEFT JOIN Orders ON Orders.customer_id = Customers.customer_id;

Result – joined tables:

The nice thing about SQL is that you can filter data within the database without actually changing the database. Let’s look at the example with the previous database. If you only need data about customers, who have ordered more than 3 things, you can write a query like this:

Query:

SELECT * FROM Customers

      LEFT JOIN Orders ON Orders.customer_id = Customers.customer_id

      WHERE amount > ’3’;

Result – filtered data:

If you want help on SQL or just more information, don’t hesitate to contact us by clicking the button below.