knowledge

Xpeho knowledge database

View project on GitHub

Structured Query Language

writing in progress

Brief history

Structured Query Language is a programming language useful in handling structured data and is a standard of ANSI since 1986. Please read the Wikipedia page for further information.

Transaction

Transaction is a statement to control process and data manipulation and permit to validate or invalidate the data processing. The acceptance of the transaction is a commit and the reject of the transation is a rollback . The best practice is to not use autocommit .

Comparission Operators

Operator Description Exemple
= equal name = ‘my name’
<> not equal name <> ‘my name’
!= not equal name != ‘my name’
> superior age > 18
>= superior or equal age >= 18
< inferior age < 18
<= inferior or equal age <= 18
IN is in a list of firstname IN (‘Charles’, ‘Elisabeth’)
BETWEEN is between two values age BETWEEN 13 and 19
LIKE is like something
% replace many letters
? replace one letter		 fistname LIKE ‘%sab?th’
IS NULL is null firstname IS NULL
IS NOT NULL is nul null firstname IS NOT NULL

Logic Operators (also named Boolean Operators)

Operator Description Exemple
NOT boolean NOT NOT a
OR boolean OR a OR b
NOT OR boolean NOT OR NOT a OR b
AND boolean AND a AND b
NOT AND boolean NOT AND NOT a AND b
XOR boolean Exclusive OR
doesn’t exists		 (a AND NOT b) OR (b AND NOT a)

More information about Boolean Algebra

SELECT

The SELECT instruction performs to get information from the database.

  SELECT T.column0, T.column1, ..., T.columnN FROM tableName T

CONDITION

The WHERE instruction performs add some conditions to SELECT some information from the database.

  SELECT T.column0, T.column1, ..., T.columnN FROM tableName T
    WHERE T.column0 like '%data%'

INSERT

The INSERT instruction performs adds data in the database.

  INSERT INTO tableName(column0, T.column1, ..., T.columnN)
    VALUES ('data0', 'data1', ..., 'dataN')

UPDATE

The UPDATE instruction performs modify data in the database.

  UPDATE tableName
    SET column1='data1', ..., columnN='dataN'
    WHERE column0='data0'

DELETE

The DELETE instruction performs remove data in the database.

  DELETE FROM tableName
    WHERE column0='data0'

MERGE

The MERGE instruction performs a massive insert or update operation very fastly in opposition to the amount of data that will be processed.

JOINS

The JOINs instructions performs to associate different tables of the database. T here are many joins that shoud be seen like mathematic assembly.

INNER JOIN

  SELECT A.*, B.* FROM tableA A
    INNER JOIN  tableB B ON A.key=B.key

logo

LEFT JOIN

  SELECT A.*, B.* FROM tableA A
    LEFT JOIN  tableB B ON A.key=B.key

logo

LEFT OUTER JOIN

  SELECT A.*, B.* FROM tableA A
    LEFT OUTER JOIN  tableB B ON A.key=B.key

logo

RIGHT JOIN

  SELECT A.*, B.* FROM tableA A
    RIGHT JOIN  tableB B ON A.key=B.key

logo

RIGHT OUTER JOIN

  SELECT A.*, B.* FROM tableA A
    RIGHT OUTER JOIN  tableB B ON A.key=B.key

logo

FULL OUTER JOIN

  SELECT A.*, B.* FROM tableA A
    FULL OUTER JOIN  tableB B ON A.key=B.key

logo

  SELECT A.*, B.* FROM tableA A
    FULL OUTER JOIN  tableB B ON A.key=B.key
    WHERE A.key IS NULL OR B.key IS NULL

logo

GROUPING

The GROUP BY instruction performs to use some aggregate function like: avg, count, min, max, … you have to use grouping.

Here we count the number of person with the sage age.

    SELECT age, count(userId)
    FROM t_user
    GROUP BY age

CONDITION IN GROUP

To add a condition on grouped data

    SELECT age, count(userId) as nbPerson
    FROM t_user
    GROUP BY age
    HAVING age >= 18

RANKING

The RANK OVER … PARTITION BY instruction performs to add another processing in the same query.

Here we compute the rank of personne with the sage age (maximun first).

    SELECT age, count(userId),
         RANK() OVER (
         PARTITION BY age
          ORDER BY count(userId) DESC) AS age_rank
    FROM t_user
    GROUP BY age

TRUNCATE

The truncate command erase all the content of a table and it shoudn’t be rollbacked.

  TRUNCATE TABLE tableName

WITH

The WITH instruction is used to split complex business request to have a easier and most readable request.


  -- this query permits to set in a same row, the current and the previous stock (lag function)
  with update_diff as (
   SELECT 
    sku, avail_stock, update_date,
    -- stock from the previous row
    lag(avail_stock) over(partition by sku order by  sku, updatedDate)  as prev_stock

   FROM T_STORE

   ORDER BY  
   -- order is mandatory to apply "lag" function (SQL-2022 standard)
    sku, updatedDate
  )

  -- sample request implements a basic count for sku that the stock had evolve
  SELECT count(*)
  FROM
     update_diff
  WHERE 
    avail_stock <> prev_stock

Functions

PostgreSQL official documentation for functions

French SQL Course