simple SQL cheatsheet

cheat_sql.txt

# props to codecademy.org for great interactive intro to SQL

# CREATE
CREATE TABLE mytable...
CREATE TABLE mytable(id INTEGER PRIMARY KEY, name TEXT);
# a "foreign key" is a reference to a "primary key" in another table

# INSERT
INSERT INTO mytable ...

# SELECT, GROUP, ORDER
SELECT name FROM mytable;
SELECT * FROM mytable;
SELECT * FROM mytable GROUP BY value;
SELECT * FROM mytable GROUP BY 2; # short for 'group by second column of output'
SELECT * FROM mytable GROUP BY value,type;
SELECT * FROM mytable ORDER BY height DESC; # or ASC; multiple ORDER allowed like GROUP

# JOINS: merging rows of two tables
# inner join - result limited to records where join succeeds
SELECT * FROM mytable JOIN otherTable ON mytable.otherId = otherTable.id;
# outer (left) join - result includes records from mytable where ON has no correspondence
SELECT * FROM mytable LEFT JOIN otherTable ON mytable.otherId = otherTable.id;
# if field has same name in both tables,
SELECT * FROM mytable JOIN othertable USER commonfield;

# UNIONS: merging columns of two tables
# must select same number and type from each table
SELECT a,b FROM tableA UNION select x,y FROM tableB;
  
# filters:WHERE, LIKE, BETWEEN, LIMIT
SELECT * FROM mytable WHERE mycolumn < 21;
SELECT * FROM mytable WHERE mycolumn LIKE 'blah'; # _ for single char; % for 0 or more
SELECT * FROM mytable WHERE mycolumn BETWEEN val1 AND val2;
SELECT * FROM mytable WHERE value IS NOT NULL;



# UPDATE
UPDATE mytable SET name='John Doe' WHERE id=1;

# ALTER
ALTER TABLE mytable ADD COLUMN newField TEXT;

# DELETE
DELETE FROM mytable WHERE active IS NULL;

# Aggregating
SELECT COUNT(*) from mytable;
SELECT val, COUNT(*) from mytable GROUP BY length;
SELECT SUM(length) FROM mytable;
SELECT name,MAX(length) from mytable;
SELECT name,type,MAX(length) from mytable GROUP BY type;
others: AVG, ROUND, COUNT
SELECT DISTINCT mycolumn FROM mytable;
SELECT name, COUNT(DISTINCT order_id) FROM order_items;

# Transforms
DATETIME
DATETIME(time1, +X hours, Y minutes, Z days)
CAST(number1 AS REAL) / number2;
ROUND(number, precision)
'string1' || ' ' || 'string2';: Concatenates string1 and string 2, with a space between.
REPLACE(source_string,substring,replacement_string)

# Subquery, independent:
SELECT * FROM mytable WHERE style in ( SELECT code FROM othertable WHERE sales > 2000);
#
# Subquery, correlated (inner query runs once for each row of outer):
SELECT id FROM mytable AS x WHERE length > ( SELECT AVG(length) FROM mytable WHERE style = x.coordinate);

# UNION, with named inner query result table
SELECT avg(a.sale_price) FROM (SELECT sale_price FROM order_items UNION ALL SELECT sale_price FROM order_items_historic) AS a;
# compare INTERSECT, EXCEPT

# CASE statement
SELECT
    CASE
        WHEN elevation < 250 THEN 'Low'
        WHEN elevation BETWEEN 250 AND 1749 THEN 'Medium'
        WHEN elevation >= 1750 THEN 'High'
        ELSE 'Unknown'
    END AS elevation_tier
    , COUNT(*)
FROM airports
GROUP BY 1;
#
SELECT    state, 
    COUNT(CASE WHEN elevation >= 2000 THEN 1 ELSE NULL END) as count_high_elevation_aiports 
FROM airports 
GROUP BY state;
#
SELECT state, 100.0 * sum(CASE WHEN elevation >= 2000 THEN 1 ELSE 0 END) / count(*)  as percentage_high_elevation_airports FROM airports GROUP BY state;

# Virtual Fields
select *,
  case name
    when 'kale-smoothie'    then 'smoothie'
    when 'banana-smoothie'  then 'smoothie'
    when 'blt'              then 'sandwich'
    when 'tikka-masala'     then 'dinner'
    when 'chicken-parm'     then 'dinner'
     else 'other'
  end as category
from order_items;

# Virtual Tables: Common Table Expressions, or CTEs
with daily_revenue as
(
  select date(created_at),
  round(sum(price),2) from purchases group by 1
)
select * from daily_revenue;
#
# multiple virtual tables with a join:
with daily_revenue as (select date(created_at) as dt, sum(price) as rev from purchases group by 1), daily_players as (select date(created_at) as dt, count(distinct user_id) as players from gameplays group by 1) select daily_revenue.dt,  daily_revenue.rev / daily_players.players from daily_revenue join daily_players using (dt);

# Self-Join: joins every row to every other row!
select date(g1.created_at) as dt, g1.user_id from gameplays as g1 join gameplays as g2 on g1.user_id=g2.user_id order by 1 limit 100;