SQL Statements Cheatsheet to Get You the Data That You Want

Product managers need to be familiar with SQL to do database queries rather than relying solely on engineers for information. Here’s a good start:

DATA TYPES: text, integer, real (float), blob, boolean: (stored as 0 for false and 1 for true)

PRIMARY KEY: auto-incrementing (starting at 1) unique identifier for a row

* wildcard selects all the data from all the columns

CREATE TABLE table_name (id INTEGER PRIMARY KEY column_name DATA_TYPE column_name DATA_TYPE);

ALTER TABLE: adds a new column to our table

DROP TABLE — deletes the whole table

INSERT INTO table(which columns) VALUES (data to be inserted)— inserts data into your table

SELECT [names of columns we are going to select] FROM [table we are selecting from]; Also written as table.column.

UPDATE [table name] SET [column name] = [new value] WHERE [column name] = [value];

DELETE FROM [table name] WHERE [column name] = [value];

SELECT column_name FROM table_name ORDER BY column_name ASC;

ASC = ascending (low to high)

DESC = descending (high to low)

LIMIT (n) is used to determine the number of records you want to returned

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

NULL : add data with missing values

INSERT INTO table (columns) VALUES (NULL, NULL, value);

SELECT * FROM table WHERE column IS NULL;

COUNT : SQL aggregate function to return number of records that meet conditions

SELECT COUNT(column_name) FROM table_name WHERE column name = value;

SELECT column, COUNT(column) FROM table GROUP BY column;

WHERE: to select data individual columns based on specific conditions. Comes before group by.

SELECT * FROM [table name] WHERE [column name] (operator: =, < etc) [some value];

HAVING: supports aggregate functions by selecting groups of rows. Comes after group by.

AVG( ) — returns the average value of a column

SELECT AVG(column_name) FROM table_name;

SUM( ) — returns the sum of all the values in a column

SELECT SUM(column_name) FROM table_name;

MIN( ) — returns minimum value from a column

SELECT MIN(column_name) FROM table_name;

MAX( ) — returns maximum value from a column

SELECT MAX(column_name) FROM table_name;

COUNT( ) — returns the number of rows that meet a certain condition

SELECT COUNT(column_name) FROM table_name;

AS — keyword to rename the column (of the returned value)

INNER JOIN — Returns all rows when there is at least one match in BOTH tables

LEFT [OUTER] JOIN — Returns all rows from the left table, and the matched rows from the right table

RIGHT JOIN* — Returns all rows from the right table, and the matched rows from the left table

FULL JOIN* — Returns all rows when there is a match in ONE of the tables