CROSS JOIN

=

JOIN

=

ON Day>=Int_day

UNION

=

UNION ALL

=

LEFT(string, number of characters)

RIGHT(string, number of characters)

LENGTH(string)

TRIM(leading, 'A' FROM string)

TRIM(trailing, 'A' FROM string)

TRIM(both, 'A' FROM string)

POSITION('A' IN string)

STRPOS(string,'A')

SUBSTR(string, starting position, number of characters)

CONCAT(string1,string2)

SELECT date,
       (SUBSTR(date, 7, 4) || '-' || LEFT(date, 2) ||
        '-' || SUBSTR(date, 4, 2))::DATE AS cleaned_date
  FROM tab

'02/03/2017'->2017-02-03

SELECT cleaned_date,
       EXTRACT(YEAR   FROM cleaned_date) AS year,
       EXTRACT(MONTH  FROM cleaned_date) AS month,
       EXTRACT(DAY    FROM cleaned_date) AS day,
       EXTRACT(HOUR   FROM cleaned_date) AS hour,
       EXTRACT(MINUTE FROM cleaned_date) AS minute,
       EXTRACT(SECOND FROM cleaned_date) AS second,
       EXTRACT(DECADE FROM cleaned_date) AS decade,
       EXTRACT(DOW    FROM cleaned_date) AS day_of_week
  FROM tab

SELECT cleaned_date,
       DATE_TRUNC(YEAR   , cleaned_date) AS year,
       DATE_TRUNC(MONTH  , cleaned_date) AS month,
       DATE_TRUNC(WEEK   , cleaned_date) AS week,
       DATE_TRUNC(DAY    , cleaned_date) AS day,
       DATE_TRUNC(HOUR   , cleaned_date) AS hour,
       DATE_TRUNC(MINUTE , cleaned_date) AS minute,
       DATE_TRUNC(SECOND , cleaned_date) AS second,
       DATE_TRUNC(DECADE , cleaned_date) AS decade
  FROM tab

SELECT customerNumber,
       checkNumber,
       amount
FROM payments
WHERE amount = (
                 SELECT MAX(amount) 
                 FROM payments
                );

SELECT
 MAX(items),
 MIN(items),
 FLOOR(AVG(items))
FROM
 (
 SELECT
 orderNumber,
 COUNT(orderNumber) AS items
 FROM
 orderdetails
 GROUP BY
 orderNumber
 ) AS lineitems;