SQL

Installing MySQL

https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-22-04

Installing PostgreSQL

https://www.postgresql.org/download/linux/ubuntu/

To quickly get a testing mysql or PostgreSql environment you can also use dockers:

# PostgreSQL
# Create and Run an instance of postgreSQL image latest version
sudo docker run --name dock-postgres -e POSTGRES_PASSWORD=password -d postgres
# Get an interactive PostgreSQL shell by attaching to the container and running the psql command
sudo docker exec -it dock-postgres psql -U postgres -W

# MySQL
# Create and Run an instance of mysql image latest version
sudo docker run --name dock-mysql -e MYSQL_ROOT_PASSWORD=password -d mysql:latest
# Get an interactive MySQL shell by attaching to the container and running the mysql command
sudo docker exec -it dock-mysql mysql -u root -p
# Then once you get mysql prompt create a temporary database
# CREATE DATABASE testdb;
# use testdb;

Creating a Table

As an example, we’ll create a table based on the following data:

Table name: Persons

Person_ID

First Name

Surname

Birth date

Weight

Height

Earthling

1

Robert

Dubouchard

19/12/1975

89.1

134

True

2

Sarah

Dupont

14/01/1991

69.3

175.4

True

3

Xioului

Wukikoku

02/11/2003

NULL

165.9

False

4

Xiao

Li

15/03/1987

39.7

123.8

False

5

Robert

Amomo

13/12/1998

123.2

210.3

True

6

Jeanne

Klopens

19/11/1985

94.6

192.9

False

7

Ludovic

Perudo

09/09/1999

46.2

135.8

True

8

Bryan

Baboulo

12/10/2005

51.9

145.7

True

9

Hadia

Wuxiko

02/09/2001

71.2

174.3

False

10

Youcef

Vicompte

31/01/1990

NULL

93.3

True

11

Jade

Haynak

08/04/1970

91.3

190.4

False

12

Mehdi

Mezizi

05/06/1996

24.8

75.7

False

13

Claire

Suniza

23/08/1973

37.7

112.1

False

14

Robert

Blanchard

30/07/1978

43.8

156.6

True

15

Hadia

Emopolo

29/03/1956

66.6

169.9

False

16

Xiao

Quezara

26/10/2000

60.7

147.1

True

Let’s also consider this table containing additional data for some of the people:

Table name: Persons_Comments

Comment_ID

Person_ID

Comment

1

2

Not a very nice person

2

2

Likes to read with the head upside down

3

4

Don’t talk about piano with her !

4

4

Such a lovely Martian

5

4

Goes to pee every 30 minutes

6

6

Do not trust this girl

7

12

Likes to drink wine mixed with kombucha

8

13

A child in an adult body

9

14

Doesn’t like to be bothered while eating

10

14

A cute weirdo

11

25

This person does’t exist

12

27

I already like the person with this ID even if he/she doesn’t exist

Creating the tables

CREATE TABLE Persons (
    PERSON_ID       INTEGER PRIMARY KEY generated by default as identity,
    "First Name"    VARCHAR(255) NOT NULL,
    Surname         VARCHAR(255),
    "Birth date"    DATE,
    Weight          FLOAT,
    Height          FLOAT,
    Earthling       BOOL DEFAULT TRUE
);
CREATE TABLE Persons_Comments (
    COMMENT_ID      INTEGER PRIMARY KEY generated by default as identity,
    PERSON_ID       INTEGER,
    Comment         text
);
--To add a constraint on PERSON_ID to be sure it matchs an existing PERSON_ID in the Persons table.
--FOREIGN KEY (PERSON_ID) REFERENCES Persons(PERSON_ID)

You can find more info about data types available here:

Important

FOREIGN KEY : This constraint will prevent you from creating values with an reference key that doesn’t exist. Before to delete a parent row, you will first need to delete all references in childs tables.

Important

Not a good practice to set column names with space inside.

We added a field containing a primary key auto with auto incremented value.

It’s a better practice to set a dedicaded column as a primary key but you can also use the combination of multiple column as a primary key. Example below:

CREATE TABLE Persons (
    "First Name"    VARCHAR(255) NOT NULL,
    Surname         VARCHAR(255),
    "Birth date"    DATE,
    Weight          FLOAT,
    Height          FLOAT,
    Earthling       BOOL DEFAULT TRUE,
    PRIMARY KEY     ("First Name", Surname)
);

Data Manipulation: INSERT, UPDATE, DELETE

INSERT

INSERT INTO Persons (
    "First Name",
    Surname,
    "Birth date",
    Weight,
    Height,
    Earthling
)
VALUES
    ('Robert',  'Dubouchard', '1975-12-19',  89.1,    134,     True),
    ('Sarah',   'Dupont',     '1991-01-14',  69.3,    175.4,   True),
    ('Xioului', 'Wukikoku',   '2003-11-02',  54.8,    165.9,   False),
    ('Xiao',    'Li',         '1987-03-15',  39.7,    123.8,   False),
    ('Robert',  'Amomo',      '1998-12-13',  123.2,   210.3,   True),
    ('Jeanne',  'Klopens',    '1985-11-19',  94.6,    192.9,   False),
    ('Ludovic', 'Perudo',     '1999-09-09',  46.2,    135.8,   True),
    ('Bryan',   'Baboulo',    '2005-10-12',  51.9,    145.7,   True),
    ('Hadia',   'Wuxiko',     '2001-09-02',  71.2,    174.3,   False),
    ('Youcef',  'Vicompte',   '1990-01-31',  34.0,    93.3,    True),
    ('Jade',    'Haynak',     '1970-04-08',  91.3,    190.4,   False),
    ('Mehdi',   'Mezizi',     '1996-06-05',  24.8,    75.7,    False),
    ('Claire',  'Suniza',     '1973-08-23',  37.7,    112.1,   False),
    ('Robert',  'Blanchard',  '1978-07-30',  43.8,    156.6,   True),
    ('Hadia',   'Emopolo',    '1956-03-29',  66.6,    169.9,   False),
    ('Xiao',    'Quezara',    '2000-10-26',  60.7,    147.1,   True);

INSERT INTO Persons_Comments (
    PERSON_ID,
    Comment
)
VALUES
    ((SELECT PERSON_ID FROM Persons WHERE "First Name" = 'Sarah'   AND Surname = 'Dupont'),    'Not a very nice person'),
    ((SELECT PERSON_ID FROM Persons WHERE "First Name" = 'Sarah'   AND Surname = 'Dupont'),    'Likes to read with the head upside down'),
    ((SELECT PERSON_ID FROM Persons WHERE "First Name" = 'Xiao'    AND Surname = 'Li'),        'Don''t talk about piano with her !'),
    ((SELECT PERSON_ID FROM Persons WHERE "First Name" = 'Xiao'    AND Surname = 'Li'),        'Such a lovely Martian'),
    ((SELECT PERSON_ID FROM Persons WHERE "First Name" = 'Xiao'    AND Surname = 'Li'),        'Goes to pee every 30 minutes'),
    ((SELECT PERSON_ID FROM Persons WHERE "First Name" = 'Jade'    AND Surname = 'Haynak'),    'Do not trust this girl'),
    ((SELECT PERSON_ID FROM Persons WHERE "First Name" = 'Mehdi'   AND Surname = 'Mezizi'),    'Likes to drink wine mixed with kombucha'),
    ((SELECT PERSON_ID FROM Persons WHERE "First Name" = 'Claire'  AND Surname = 'Suniza'),    'A child in an adult body'),
    ((SELECT PERSON_ID FROM Persons WHERE "First Name" = 'Robert'  AND Surname = 'Blanchard'), 'Doesn''t like to be bothered while eating'),
    ((SELECT PERSON_ID FROM Persons WHERE "First Name" = 'Robert'  AND Surname = 'Blanchard'), 'A cute weirdo'),
    (25, 'This person does''t exist'),
    (27, 'I already like the person with this ID even if he/she doesn''t exist');

Important

Character constants need single quotes ! (Single quotes are escaped by doubling them up)

UPDATE

UPDATE Persons_Comments
SET comment = 'Such a lovely Martian, am I in love ?'
WHERE Comment_ID = 4;

DELETE

DELETE FROM Persons_Comments
WHERE Comment_ID = 4;

Example using SELECT FROM, WHERE, LIKE, ORDER BY

SELECT: This is the primary command used to retrieve data from a database. WHERE: To extract specific data from a database. ORDER BY: To sort data using ASC or DESC keyword. LIKE: To match a pattern

SELECT "First Name", Surname, "Birth date"
FROM Persons
WHERE Earthling = True AND "First Name" LIKE '%o%'
ORDER BY "Birth date" ASC;

Output:

First Name

Surname

Birth date

Robert

Dubouchard

1975-12-19

Robert

Blanchard

1978-07-30

Youcef

Vicompte

1990-01-31

Robert

Amomo

1998-12-13

Ludovic

Perudo

1999-09-09

Xiao

Quezara

2000-10-26

Example using built-in functions and subquery

SQL has a variety of built-in functions that are commonly used in queries. Some of the most important main built-in functions in SQL are:

  • COUNT(): Used to count the number of rows that match a specific condition.

  • SUM(): Used to calculate the sum of a column.

  • AVG(): Used to calculate the average of a column.

  • MIN(): Used to find the minimum value of a column.

  • MAX(): Used to find the maximum value of a column.

  • ROUND(): Used to round a number to a specified number of decimal places.

  • CONCAT(): Used to concatenate two or more strings together.

  • UPPER(): Used to convert a string to uppercase.

  • LOWER(): Used to convert a string to lowercase.

  • SUBSTRING(): Used to extract a substring from a string.

  • DATE(): Used to retrieve the current date.

  • DATEPART(): Used to extract a specific part of a date, such as the year or month.

You can find a more exhaustive list of built in functions here

Example:

SELECT COUNT(*) FROM Persons
WHERE Height > (SELECT AVG(Height) FROM Persons);

Output: 8

Example joining tables

SQL allows you to join two or more tables to retrieve data that is spread across multiple tables.

Inner Join

Returns only the matching rows from both tables.

SELECT Persons.*, Persons_Comments.Comment FROM Persons
INNER JOIN Persons_Comments
ON Persons.PERSON_ID = Persons_Comments.PERSON_ID;

Output:

PERSON_ID

First Name

Surname

Birth date

Weight

Height

Earthling

Comment

2

Sarah

Dupont

1991-01-14

69.3

175.4

1

Not a very nice person

2

Sarah

Dupont

1991-01-14

69.3

175.4

1

Likes to read with the head upside down

4

Xiao

Li

1987-03-15

39.7

123.8

0

Don’t talk about piano with her !

4

Xiao

Li

1987-03-15

39.7

123.8

0

Such a lovely Martian

4

Xiao

Li

1987-03-15

39.7

123.8

0

Goes to pee every 30 minutes

11

Jade

Haynak

1970-04-08

91.3

190.4

0

Do not trust this girl

12

Mehdi

Mezizi

1996-06-05

24.8

75.7

0

Likes to drink wine mixed with kombucha

13

Claire

Suniza

1973-08-23

37.7

112.1

0

A child in an adult body

14

Robert

Blanchard

1978-07-30

43.8

156.6

1

Doesn’t like to be bothered while eating

14

Robert

Blanchard

1978-07-30

43.8

156.6

1

A cute weirdo

Right [OUTER] Join

Returns all the rows from the right table and matching rows from the left table.

SELECT Persons.*, Persons_Comments.Comment FROM Persons
RIGHT JOIN Persons_Comments
ON Persons.PERSON_ID = Persons_Comments.PERSON_ID;

Output:

PERSON_ID

First Name

Surname

Birth date

Weight

Height

Earthling

Comment

2

Sarah

Dupont

1991-01-14

69.3

175.4

1

Not a very nice person

2

Sarah

Dupont

1991-01-14

69.3

175.4

1

Likes to read with the head upside down

4

Xiao

Li

1987-03-15

39.7

123.8

0

Don’t talk about piano with her !

4

Xiao

Li

1987-03-15

39.7

123.8

0

Such a lovely Martian

4

Xiao

Li

1987-03-15

39.7

123.8

0

Goes to pee every 30 minutes

11

Jade

Haynak

1970-04-08

91.3

190.4

0

Do not trust this girl

12

Mehdi

Mezizi

1996-06-05

24.8

75.7

0

Likes to drink wine mixed with kombucha

13

Claire

Suniza

1973-08-23

37.7

112.1

0

A child in an adult body

14

Robert

Blanchard

1978-07-30

43.8

156.6

1

Doesn’t like to be bothered while eating

14

Robert

Blanchard

1978-07-30

43.8

156.6

1

A cute weirdo

NULL

NULL

NULL

NULL

NULL

NULL

NULL

This Person does’t exist

NULL

NULL

NULL

NULL

NULL

NULL

NULL

I already like the person with this ID even if he/she doesn’t exist

Note

Note that PERSON_ID is NULL for the 2 comments with non existing persons.

You can solve that problem by using CASE statement:

SELECT
    CASE
        WHEN Persons.PERSON_ID IS NULL THEN Persons_Comments.PERSON_ID
        WHEN Persons_Comments.PERSON_ID IS NULL THEN Persons.PERSON_ID
        WHEN Persons.PERSON_ID = Persons_Comments.PERSON_ID THEN Persons.PERSON_ID
        ELSE -1
    END AS PERSON_ID,
    "First Name",
    Surname,
    "Birth date",
    Weight,
    Height,
    Earthling,
    Persons_Comments.Comment
FROM Persons
RIGHT JOIN Persons_Comments
ON Persons.PERSON_ID = Persons_Comments.PERSON_ID;

Output:

PERSON_ID

First Name

Surname

Birth date

Weight

Height

Earthling

Comment

2

Sarah

Dupont

1991-01-14

69.3

175.4

1

Not a very nice person

2

Sarah

Dupont

1991-01-14

69.3

175.4

1

Likes to read with the head upside down

4

Xiao

Li

1987-03-15

39.7

123.8

0

Don’t talk about piano with her !

4

Xiao

Li

1987-03-15

39.7

123.8

0

Such a lovely Martian

4

Xiao

Li

1987-03-15

39.7

123.8

0

Goes to pee every 30 minutes

11

Jade

Haynak

1970-04-08

91.3

190.4

0

Do not trust this girl

12

Mehdi

Mezizi

1996-06-05

24.8

75.7

0

Likes to drink wine mixed with kombucha

13

Claire

Suniza

1973-08-23

37.7

112.1

0

A child in an adult body

14

Robert

Blanchard

1978-07-30

43.8

156.6

1

Doesn’t like to be bothered while eating

14

Robert

Blanchard

1978-07-30

43.8

156.6

1

A cute weirdo

25

NULL

NULL

NULL

NULL

NULL

NULL

This Person does’t exist

27

NULL

NULL

NULL

NULL

NULL

NULL

I already like the person with this ID even if he/she doesn’t exist

Left [OUTER] Join

Returns all the rows from the left table and matching rows from the right table.

SELECT Persons.*, Persons_Comments.COMMENT_ID , Persons_Comments.Comment FROM Persons
LEFT JOIN Persons_Comments
ON Persons.PERSON_ID = Persons_Comments.PERSON_ID;

PERSON_ID

First Name

Surname

Birth date

Weight

Height

Earthling

COMMENT_ID

Comment

1

Robert

Dubouchard

1975-12-19

89.1

134

1

NULL

NULL

2

Sarah

Dupont

1991-01-14

69.3

175.4

1

2

Likes to read with the head upside down

2

Sarah

Dupont

1991-01-14

69.3

175.4

1

1

Not a very nice person

3

Xioului

Wukikoku

2003-11-02

54.8

165.9

0

NULL

NULL

4

Xiao

Li

1987-03-15

39.7

123.8

0

5

Goes to pee every 30 minutes

4

Xiao

Li

1987-03-15

39.7

123.8

0

4

Such a lovely Martian

4

Xiao

Li

1987-03-15

39.7

123.8

0

3

Don’t talk about piano with her !

5

Robert

Amomo

1998-12-13

123.2

210.3

1

NULL

NULL

6

Jeanne

Klopens

1985-11-19

94.6

192.9

0

NULL

NULL

7

Ludovic

Perudo

1999-09-09

46.2

135.8

1

NULL

NULL

8

Bryan

Baboulo

2005-10-12

51.9

145.7

1

NULL

NULL

9

Hadia

Wuxiko

2001-09-02

71.2

174.3

0

NULL

NULL

10

Youcef

Vicompte

1990-01-31

34

93.3

1

NULL

NULL

11

Jade

Haynak

1970-04-08

91.3

190.4

0

6

Do not trust this girl

12

Mehdi

Mezizi

1996-06-05

24.8

75.7

0

7

Likes to drink wine mixed with kombucha

13

Claire

Suniza

1973-08-23

37.7

112.1

0

8

A child in an adult body

14

Robert

Blanchard

1978-07-30

43.8

156.6

1

10

A cute weirdo

14

Robert

Blanchard

1978-07-30

43.8

156.6

1

9

Doesn’t like to be bothered while eating

15

Hadia

Emopolo

1956-03-29

66.6

169.9

0

NULL

NULL

16

Xiao

Quezara

2000-10-26

60.7

147.1

1

NULL

NULL

If you don’t want to create multiple rows for each comment on the same person, you can use the GROUP_CONCAT method in Mysql or the array_agg in psql as below:

SELECT
    Persons.*,
    array_agg(Persons_Comments.COMMENT_ID) AS comments_ids,
    array_agg(Persons_Comments.Comment) AS comments
FROM Persons
LEFT JOIN Persons_Comments
ON Persons.PERSON_ID = Persons_Comments.PERSON_ID
GROUP BY Persons.PERSON_ID;

PERSON_ID

First Name

Surname

Birth date

Weight

Height

Earthling

comments_ids

comments

1

Robert

Dubouchard

1975-12-19

89.1

134

1

NULL

NULL

2

Sarah

Dupont

1991-01-14

69.3

175.4

1

2,1

Likes to read with the head upside down,Not a very nice person

3

Xioului

Wukikoku

2003-11-02

54.8

165.9

0

NULL

NULL

4

Xiao

Li

1987-03-15

39.7

123.8

0

5,4,3

Goes to pee every 30 minutes,Such a lovely Martian,Don’t talk about piano with her !

5

Robert

Amomo

1998-12-13

123.2

210.3

1

NULL

NULL

6

Jeanne

Klopens

1985-11-19

94.6

192.9

0

NULL

NULL

7

Ludovic

Perudo

1999-09-09

46.2

135.8

1

NULL

NULL

8

Bryan

Baboulo

2005-10-12

51.9

145.7

1

NULL

NULL

9

Hadia

Wuxiko

2001-09-02

71.2

174.3

0

NULL

NULL

10

Youcef

Vicompte

1990-01-31

34

93.3

1

NULL

NULL

11

Jade

Haynak

1970-04-08

91.3

190.4

0

6

Do not trust this girl

12

Mehdi

Mezizi

1996-06-05

24.8

75.7

0

7

Likes to drink wine mixed with kombucha

13

Claire

Suniza

1973-08-23

37.7

112.1

0

8

A child in an adult body

14

Robert

Blanchard

1978-07-30

43.8

156.6

1

10,9

A cute weirdo,Doesn’t like to be bothered while eating

15

Hadia

Emopolo

1956-03-29

66.6

169.9

0

NULL

NULL

16

Xiao

Quezara

2000-10-26

60.7

147.1

1

NULL

NULL

Full Outer Join

Returns all the rows from both tables, including non-matching rows.

SELECT * FROM Persons
FULL OUTER JOIN Persons_Comments
ON Persons.PERSON_ID = Persons_Comments.PERSON_ID;

Output:

PERSON_ID

First Name

Surname

Birth date

Weight

Height

Earthling

COMMENT_ID

PERSON_ID

Comment

1

Robert

Dubouchard

1975-12-19

89.1

134

1

NULL

NULL

NULL

2

Sarah

Dupont

1991-01-14

69.3

175.4

1

2

2

Likes to read with the head upside down

2

Sarah

Dupont

1991-01-14

69.3

175.4

1

1

2

Not a very nice person

3

Xioului

Wukikoku

2003-11-02

54.8

165.9

0

NULL

NULL

NULL

4

Xiao

Li

1987-03-15

39.7

123.8

0

5

4

Goes to pee every 30 minutes

4

Xiao

Li

1987-03-15

39.7

123.8

0

4

4

Such a lovely Martian

4

Xiao

Li

1987-03-15

39.7

123.8

0

3

4

Don’t talk about piano with her !

5

Robert

Amomo

1998-12-13

123.2

210.3

1

NULL

NULL

NULL

6

Jeanne

Klopens

1985-11-19

94.6

192.9

0

NULL

NULL

NULL

7

Ludovic

Perudo

1999-09-09

46.2

135.8

1

NULL

NULL

NULL

8

Bryan

Baboulo

2005-10-12

51.9

145.7

1

NULL

NULL

NULL

9

Hadia

Wuxiko

2001-09-02

71.2

174.3

0

NULL

NULL

NULL

10

Youcef

Vicompte

1990-01-31

34

93.3

1

NULL

NULL

NULL

11

Jade

Haynak

1970-04-08

91.3

190.4

0

6

11

Do not trust this girl

12

Mehdi

Mezizi

1996-06-05

24.8

75.7

0

7

12

Likes to drink wine mixed with kombucha

13

Claire

Suniza

1973-08-23

37.7

112.1

0

8

13

A child in an adult body

14

Robert

Blanchard

1978-07-30

43.8

156.6

1

10

14

A cute weirdo

14

Robert

Blanchard

1978-07-30

43.8

156.6

1

9

14

Doesn’t like to be bothered while eating

15

Hadia

Emopolo

1956-03-29

66.6

169.9

0

NULL

NULL

NULL

16

Xiao

Quezara

2000-10-26

60.7

147.1

1

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

11

25

This Person does’t exist

NULL

NULL

NULL

NULL

NULL

NULL

NULL

12

27

I already like the person with this ID even if he/she doesn’t exist

Example Grouping data

Grouping data: SQL provides an easy way to aggregate data by grouping it based on specific criteria. You should understand how to use the GROUP BY clause to group data.

SELECT Earthling, COUNT(*), AVG(Height) as mean_height
FROM Persons
GROUP BY Earthling;

Output:

Earthling

COUNT(*)

mean_height

1

8

149.7750015258789

0

8

150.62499713897705

Example using UNION

Suppose you have two tables called “employees” and “customers” with the following data:

Table: employees

employee_id

employee_name

department

1

John

Sales

2

Jane

HR

3

Bob

Marketing

Table: customers

customer_id

customer_name

city

1

Acme Corp

New York

2

XYZ Corp

Chicago

3

ABC Inc

Dallas

If you want to combine the results of these two tables into a single table, you can use the UNION command like this:

CREATE TABLE employees (employee_id INT PRIMARY KEY, employee_name VARCHAR(255), department VARCHAR(255));
CREATE TABLE customers (customer_id INT PRIMARY KEY, customer_name VARCHAR(255), city VARCHAR(255));
INSERT INTO employees VALUES (1, 'John', 'Sales'), (2, 'Jane', 'HR'), (3, 'Bob', 'Marketing');
INSERT INTO customers VALUES (1, 'Acme Corp', 'New York'), (2, 'XYZ Corp', 'Chicago'), (3, 'ABC Inc', 'Dallas');

SELECT employee_name as name, department as category, 'employee' as source
FROM employees
UNION
SELECT customer_name as name, city as category, 'customer' as source
FROM customers;

This will give you the following result.

name

category

source

John

Sales

employee

Jane

HR

employee

Bob

Marketing

employee

Acme Corp

New York

customer

XYZ Corp

Chicago

customer

ABC Inc

Dallas

customer

Note

Note that in the SELECT statement, we’re using aliases to make the column names in both tables match. We’re also adding a new column called “source” to differentiate between the data from the two tables. The UNION command removes duplicate rows, so the resulting table only contains distinct rows.

Views

Views are virtual tables that can be used to simplify complex queries or restrict access to certain data.

Indexes

Indexes are data structures that can be used to speed up data retrieval to improve query performance.

Useful commands

Get the list of databases

\list

Get the name of the current database in use

SELECT current_database();

Create a new database

CREATE DATABASE testdb;

Use a specific database

\connect testdb

List tables in the current database

\dt

Delete/Remove/Drop a table

DROP TABLE tablename;