Another SQL cheat sheet
Tags: database, sql
Published: 2021-09-03 16:42:46

## Disclaimer

Be aware it's not an exhaustive list. Click items with an arrow on the left to get details.

## Concepts

{% details Structured Query Language (SQL) %}
    It's the most widely used language for managing data in relational databases. The language is easy to learn, declarative, and very convenient to manipulate datasets.
{% enddetails %}

{% details SQL for what? %}
SQL can be helpful to execute queries, create data schemes, manipulate records, and retrieve data. It's often used along with a programming language to power applications.
{% enddetails %}

{% details PostgreSQL %}
It's an open-source object-relational database management system (RDBMS).
{% enddetails %}

{% details MySQL %}
It's the most widely used relational database management system. It runs as a server, and you can use it to create databases and give their access to multiple users.
{% enddetails %}

{% details Columns vs. rows %}
In a database, All information is stored in tables. Each table is divided into rows and columns. Columns are specific attributes that allow for grouping data (e.g, id, first_name, last_name, age). Rows are records (e.g, 1001001, Kobe, Bryant, 37).
{% enddetails %}

{% details Views %}
A view is a virtual table based on the result of an SQL query. It's a pretty convenient feature that allow you to run advanced queries in pre-sorted results.
{% enddetails %}

{% details ACID vs. BASE %}
ACID means "atomicity, consistency, isolation, and durability", and BASE stands for “Basically Available, soft state and eventual consistency”. Each approach has its advantages and drawbacks depending on what you want to achieve.
{% enddetails %}

{% details NoSQL %}
The NoSQL model states that not all applications need a relational database. <a href="https://redis.io/">Redis</a>, <a href="https://www.elastic.co/elasticsearch/">Elasticsearch</a> or <a href="https://www.mongodb.com/">MongoDB</a> are practical examples of NoSQL systems. You can <a href="https://dev.to/jmau111/sql-nosql-and-beyond-19kf">read this post</a> to understand the difference with SQL.
{% enddetails %}


## Logical operators

{% details AND %}
Multiple conditions in WHERE clauses that are all verified.
{% enddetails %}

{% details OR %}
Multiple conditions in WHERE clauses where at least one condition is verified.
{% enddetails %}

{% details LIKE %}
Compare a value to similar values using wildcard operators.
{% enddetails %}

{% details BETWEEN %}
Search between max and min values.
{% enddetails %}

{% details EXISTS %}
Search if a row is in a table.
{% enddetails %}

{% details IN %}
Determine if a value is in a specified list of literal values.
{% enddetails %}

{% details NOT %}
Negate logical operator (e.g., NOT EXISTS, NOT BETWEEN, NOT IN, and so on).
{% enddetails %}

## Comparison Operators

{% details = %}
Equal.
{% enddetails %}

{% details != %}
Not equal.
{% enddetails %}

{% details <> %}
Not equal.
{% enddetails %}

{% details > %}
Greater than.
{% enddetails %}

{% details >= %}
Equal or greater than.
{% enddetails %}

{% details < %}
Lower than.
{% enddetails %}

{% details <= %}
Lower than or equal.
{% enddetails %}

{% details !> %}
Not greater than.
{% enddetails %}

{% details !< %}
Not lower than.
{% enddetails %}

## Basic commands

{% details Create database %}
```sql
CREATE DATABASE my_database;
```
{% enddetails %}

{% details Create table %}
```sql
CREATE TABLE my_table (
  my_column my_column_definition, 
  other_column other_column_definition, 
);
```
{% enddetails %}

{% details Alter table %}
```sql
ALTER TABLE my_table 
ADD my_column my_column_definition;
```
{% enddetails %}

{% details Empty table %}
```sql
TRUNCATE my_table;
```
{% enddetails %}

{% details Select data in table %}
```sql
SELECT my_column 
FROM my_table;
```
{% enddetails %}

{% details Select data conditionally %}
```sql
SELECT my_column, other_column
FROM my_table
WHERE my_column = some_value
AND other_column = other_value;
```
{% enddetails %}

{% details Insert data in table %}
```sql
INSERT INTO my_table (my_column, other_column) 
VALUES (my_value, other_value);
```
{% enddetails %}

{% details Update data in table %}
```sql
UPDATE my_table
SET my_column = my_value
WHERE other_column = other_value;
```
{% enddetails %}

{% details Drop table %}
```sql
DROP my_table;
```
{% enddetails %}

{% details Delete data table %}
```sql
DELETE FROM my_table
WHERE CONDITION;
```
{% enddetails %}

{% details Aliasing %}
```sql
SELECT my_column AS 'mycol'
FROM my_table;
```
{% enddetails %}

{% details Counting elements %}
```sql
SELECT COUNT(my_column)
FROM my_table;
```
{% enddetails %}

## A little more advanced commands

{% details Kill doublons in select %}
```sql
SELECT DISTINCT id FROM Players;
```
{% enddetails %}

{% details Sum values in column %}
```sql
SELECT SUM(salary) FROM Players;
```
{% enddetails %}

{% details Sort data %}
```sql
SELECT last_name, age FROM Players
GROUP BY age ORDER BY last_name;
```
{% enddetails %}

{% details Not equal %}
```sql
SELECT first_name, last_name FROM Players WHERE age <> 42;
```
{% enddetails %}

{% details Create view %}
```sql
CREATE VIEW my_view AS
SELECT id, first_name, last_name, age
FROM Players;
```
{% enddetails %}

{% details Select data from a view %}
```sql
SELECT * FROM my_view;
```
{% enddetails %}

{% details Drop view %}
```sql
DROP VIEW my_view;
```
{% enddetails %}

## PRIMARY KEY constraints

A primary key is unique identifier for each records in a table. Its value cannot be NULL. It's often combined with `AUTO_INCREMENT` to generate it automatically:

```SQL
CREATE TABLE `Players` (
   id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
   last_name varchar(255) NOT NULL,
   first_name varchar(255),
   age int,
);
```

## UNIQUE constraints

A UNIQUE constraint forces the use of different values in a column:

```SQL
CREATE TABLE `Players` (
    id int NOT NULL,
    last_name varchar(255) NOT NULL,
    first_name varchar(255),
    age int,
    UNIQUE (id)
);
```

## INTERSECTION

```SQL
SELECT * FROM table_A
INTERSECT
SELECT * FROM table_B
```

`INTERSECT` allows you to get results that match the first query *and* the second query, and only that.

## UNION

```SQL
SELECT * FROM table_A
UNION
SELECT * FROM table_B
```

`UNION` is a basic command that allows for combining several queries and concatenate results. In the example above, you get results that match the first query *or* the second query, *or* both.

## JOIN

You can consolidate information from several different tables with a SQL `JOIN`:

{% details INNER JOIN %}
Join two tables where a condition is true in the two tables:
```sql
SELECT *
FROM table_A
INNER JOIN table_B ON table_A.id = table_B.pid
```
{% enddetails %}

{% details LEFT JOIN %}
Return all records from the table on the left even if the condition is not true in the second table:
```sql
SELECT *
FROM table_A
LEFT JOIN table_B ON table_A.id = table_B.pid;
```
{% enddetails %}

{% details RIGHT JOIN %}
Return all records from the table on the right even if the condition is not true in the first table:
```sql
SELECT *
FROM table_A
RIGHT JOIN table_B ON table_A.id = table_B.pid;
```
{% enddetails %}

{% details FULL JOIN and FULL OUTER JOIN %}
Join two tables where a condition is true at least in one table:
```sql
SELECT *
FROM table_A
FULL JOIN table_B ON table_A.id = table_B.pid;
```
{% enddetails %}

{% details SELF JOIN %}
Join a table with itself. It's useful when there are hierarchical relationships between records:
```sql
SELECT child.id, child.name, parent.name
FROM People as child
LEFT OUTER JOIN People AS parent ON child.parent_id = parent.id
```
{% enddetails %}

{% details CROSS JOIN %}
Join each line of a table with each line of a second table:
```sql
SELECT *
FROM table_A
CROSS JOIN table_B;
```
{% enddetails %}

{% details NATURAL JOIN %}
Join two tables when they have at least one column with the same name:
```sql
SELECT *
FROM table_A
NATURAL JOIN table_B;
```
{% enddetails %}

{% details UNION JOIN %}
Join union.
{% enddetails %}

## SQL Indexes

Indexes are incredibly helpful to decrease execution time for SQL selects and WHERE clauses.

It's pretty much like the index at the end of a book where you can find all pages related to a specific topic.

Creating (or dropping) indexes does not affect data:

```SQL
CREATE INDEX my_index ON my_table;
```

You can create an index on a specific column:

```SQL
CREATE INDEX my_index ON my_table (my_column);
```

Be careful, though, indexes are not meant for small tables or frequently updated columns. Large operations involving updates and inserts can be significantly slower.