Karn Wong's Docs

Appearance

SQL ​

Basic syntax ​

SELECT, FROM, LIMIT ​

sql
SELECT
  *
FROM
  crm
LIMIT
  10;

WHERE ​

sql
SELECT
  *
FROM
  crm
WHERE
  register_date > '2020-01-01'
LIMIT
  10;

AND ​

sql
SELECT
  *
FROM
  crm
WHERE
  register_date > '2020-01-01'
  AND is_priority = true
LIMIT
  10;

IN ​

sql
SELECT
  user_id,
  last_activity,
  segment
FROM
  crm
WHERE
  segment in ('foo', 'bar');

ORDER BY ​

sql
SELECT
  *
FROM
  crm
WHERE
  register_date > '2020-01-01'
ORDER BY
  register_date
LIMIT
  10;

DISTINCT ​

sql
SELECT DISTINCT
  (segment)
FROM
  crm;

JOIN (+ inner, left, right) ​

sql
SELECT
  *
FROM
  products as p
  JOIN stock as s ON p.product_id = s.product_id
LIMIT
  10;

GROUP BY, AVG, COUNT, SUM, MIN, MAX, AS ​

sql
SELECT
  segment,
  AVG(stayonpage) AS avg_stayonpage
FROM
  crm
GROUP BY
  segment
LIMIT
  10;

Transformations ​

sql
-- cast string to datetime
TO_TIMESTAMP(date_created,'YYYY-MM-DD HH:MI:SS')

-- cast datetime to strong
to_char(timestamp_column, 'YYYY-MM')

-- timedelta
WHERE date_created < (NOW() - '3 months'::interval)::TEXT

CRUD ​

sql
-- rename table
ALTER TABLE OG_NAME
rename TO NEW_NAME;

CTE ​

A common table expression is a temporary result set which you can reference within another SQL statement including SELECT, INSERT, UPDATE or DELETE.

sql
WITH
  cte_film AS (
    SELECT
      film_id,
      title,
      (
        CASE
          WHEN length < 30 THEN 'Short'
          WHEN length < 90 THEN 'Medium'
          ELSE 'Long'
        END
      ) length
    FROM
      film
  )
SELECT
  film_id,
  title,
  length
FROM
  cte_film
WHERE
  length = 'Long'
ORDER BY
  title;

Resources ​