deeilna

🎲 / 95 notes

SQL 101

2 min read

> data structure
→ data ~ tables ~ spreadsheets

  • row = records
  • columns = fields
    • each col has a data type (integer, varchar (text), date, …)

> basic SQL commands

  • SELECT retrieve data from one or more tables → list the col to display
  • FROM which table, can be aliases? ?? ❓
  • AS rename final column output
  • WITH table AS query
  • INSERT add new records (rows) to a table
  • UPDATE modify existing records (rows) in a table
  • DELETE remove records (rows) from a table
  • CREATE, ALTER, DROP manage db objects like tables and schemas

> filtering and sorting

  • WHERE clause filters results based on certain conditions
  • ORDER BY sorts data by specified columns
  • GROUP BY groups rows sharing values in specified columns for agg functions (SUM, AVG, …)

> relationships and joins

  • JOIN ... ON
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
  • USING

> advanced features

  • COUNT
  • SUM, MAX, MIN, AVG

> logic, condition, …

  • OR, AND, NOT, BETWEEN, LIKE
  • IS NULL vs. IS NOT NULL
  • IN vs. NOT IN

> others?

  • LIMIT
  • CASE
CASE
WHEN ... THEN ... 
WHEN ... THEN ...
ELSE ... 
END AS varname
  • concat texts var1 || ' ' || var2
  • convert var::text
  • search substring var::text LIKE '%SUBSTRING'
  • substr the first 6 char
    • LEFT(var, 6)

Backlinks