SQL Structured Query Language, is a language designed to allow both technical and non-technical users query, manipulate, and transform data from a relational database.

Some popular SQL databases:

  • SQLite
  • MySQL
  • Postgres
  • Oracle
  • Microsoft SQL Server

Relational database:

Represents a collection of related ( two-dimensional) tables.Each of the tables are similar to an Excel spreadsheet, with a fixed number of named columns and any number of rows of data.

  • DDL (Data Definition Language) refers to the CREATE, ALTER and DROP statements DDL allows to add / modify / delete the logical structures which contain the data or which allow users to access / maintain the data (databases, tables, keys, views…). DDL is about “metadata”.
  • DML (Data Manipulation Language) refers to the INSERT, UPDATE and DELETE statements DML allows to add / modify / delete data itself.
  • DQL (Data Query Language) refers to the SELECT, SHOW and HELP statements (queries) SELECT is the main DQL instruction. It retrieves data you need. SHOW retrieves infos about the metadata. HELP… is for people who need help.
  • DCL (Data Control Language) refers to the GRANT and REVOKE statements DCL is used to grant / revoke permissions on databases and their contents. DCL is simple, but MySQL’s permissions are rather complex. DCL is about security.
  • DTL (Data Transaction Language) refers to the START TRANSACTION, SAVEPOINT, COMMIT and ROLLBACK [TO SAVEPOINT] statements DTL is used to manage transactions (operations which include more instructions none of which can be executed if one of them fails).

Some SQL queries:

  • SELECT:
SELECT column, another_column, …
FROM mytable;
  • WHERE (constrain):
SELECT column, another_column, …
FROM mytable
WHERE condition
AND/OR another_condition
AND/OR …;

SELECT DISTINCT:

SELECT DISTINCT column, another_column, …
FROM mytable
WHERE condition(s);

SELECT column, another_column, …
FROM mytable
WHERE column LIKE condition(s);

SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC
LIMIT num_limit OFFSET num_offset;

Multi-table queries with JOINs

Tables that share information about a single entity need to have a primary key that identifies that entity uniquely across the database. One common primary key type is an auto-incrementing integer (because they are space efficient), but it can also be a string, hashed value, so long as it is unique.

Using the JOIN clause in a query, we can combine row data across two separate tables using this unique key. The first of the joins that we will introduce is the INNER JOIN.

SELECT column, another_column, …
FROM mytable
INNER JOIN another_table
 ON mytable.id = another_table.id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

s