Skip to content

Instantly share code, notes, and snippets.

@jeromecovington
Last active August 13, 2021 01:37
Show Gist options
  • Save jeromecovington/3646b3c41dfe8dbd015c836321d32737 to your computer and use it in GitHub Desktop.
Save jeromecovington/3646b3c41dfe8dbd015c836321d32737 to your computer and use it in GitHub Desktop.

ALTER

ALTER TABLE table_name
ADD column_name datatype;

AND

SELECT column_name(s) 
FROM table_name
WHERE condition
AND condition;

AS

SELECT column_name(s) AS 'alias'
FROM table_name;

AVG (the average value of a column)

SELECT AVG(column_name)
FROM table_name;

BETWEEN

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value_1 AND value_2;

CASE

SELECT column_name
CASE
WHEN condition THEN 'result_1'
WHEN condition THEN 'result_2'
ELSE 'result_3'
END
FROM table_name;

COUNT

SELECT COUNT(column_name)
FROM table_name;

CREATE

CREATE TABLE table_name (
column_1 datatype,
column_2 datatype,
column_3 datatype
);

GROUP BY (aggregation)

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;

HAVING (used instead of WHERE in aggregating function)

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > value;

INNER JOIN (combines rows from both tables if the JOIN condition is TRUE)

SELECT column_name(s)
FROM table_1
JOIN table_2
ON table1.column_name = table_2.column_name;

INSERT (adds new rows to the table)

INSERT INTO table_name
(column_1, column_2, column_3)
VALUES
(value_1, value_2, value_3);

IS NULL / IS NOT NULL

SELECT column_name(s)
FROM table_name
WHERE column_name IS NOT NULL;

LIKE (matches on a pattern in a specific column)

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

LIMIT (max number of rows)

SELECT column_name(s)
FROM table_name
LIMIT number;

MAX (largest value of column_name)

SELECT MAX(column_name)
FROM table_name;

MIN (smallest value of column_name)

SELECT MIN(column_name)
FROM table_name;

OR

SELECT column_name(s) 
FROM table_name
WHERE condition
OR condition;

ORDER BY

SELECT column_name(s)
FROM table_name
ORDER BY column_name ASC | DESC;

OUTER JOIN (combines rows from both tables if condition is TRUE, and rows from "left" table even if condition is FALSE)

SELECT column_name(s)
FROM table_1
LEFT JOIN table_2
ON table1.column_name = table_2.column_name;

ROUND

SELECT ROUND(column_name, decimal_places)
FROM table_name;

SELECT

SELECT column_name
FROM table_name;

SELECT DISTINCT (unique values only)

SELECT DISTINCT column_name
FROM table_name;

SUM (sums all values from a column)

SELECT SUM(column_name)
FROM table_name;

UPDATE (edit rows in a table)

UPDATE table_name
SET column_name = new_value
WHERE column_name = old_value;

WHERE (filter the result when the condition is TRUE)

SELECT column_name(s)
FROM table_name
WHERE columns_name operator value;

WITH (store the result of a query as a temporary aliased table)

WITH temporary_table_name AS
(SELECT * FROM table_name)
SELECT * from temporary_table_name
WHERE column_name operator value;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment