SQL Cheat Sheet

calendar Sep 28, 2022 · 7 min read  ·
Share on: linkedin copy

Get a reference for many of the common SQL commands and features on this SQL Cheat Sheet page.

This SQL Cheat Sheet applies to Oracle, SQL Server, MySQL, and Postgres.

You can also find out more details on these features on the SQL Roadmap page.

SELECT Query

1SELECT col1, col2
2FROM table
3JOIN table2 ON table1.col = table2.col
4WHERE condition
5GROUP BY column_name
6HAVING condition
7ORDER BY col1 ASC|DESC;

SELECT Keywords

DISTINCT: Removes duplicate results

1SELECT DISTINCT product_name
2FROM product;

BETWEEN: Matches a value between two other values (inclusive)

1SELECT product_name
2FROM product
3WHERE price BETWEEN 50 AND 100;

IN: Matches to any of the values in a list

1SELECT product_name
2FROM product
3WHERE category IN ('Electronics', 'Furniture');

LIKE: Performs wildcard matches using _ or %

1SELECT product_name
2FROM product
3WHERE product_name LIKE '%Desk%";

I've also created a range of handy PDF versions of this cheat sheet (one for each vendor). Enter your email address below and I'll send them to you.

Joins

Syntax:

1SELECT t1.*, t2.*
2FROM t1
3join_type t2 ON t1.col = t2.col;

join tables

Join Type Image
INNER JOIN: Show all matching records in both tables.
LEFT JOIN: Show all records from left table, and any matching records from right table.
RIGHT JOIN: Show all records from right table, and any matching records from left table.
FULL JOIN: Show all records from both tables, whether there is a match or not.

CASE Statement

Simple Case

1CASE name
2  WHEN 'John' THEN 'Name John'
3  WHEN 'Steve' THEN 'Name Steve'
4  ELSE 'Unknown'
5END

Searched Case

1CASE
2  WHEN name='John' THEN 'Name John'
3  WHEN name='Steve' THEN 'Name Steve'
4  ELSE 'Unknown'
5END

Common Table Expression

1WITH queryname AS (
2  SELECT col1, col2
3  FROM firsttable)
4SELECT col1, col2..
5FROM queryname...;

Modifying Data

Insert

1INSERT INTO tablename (col1, col2...)
2VALUES (val1, val2);

Insert from a table

1INSERT INTO tablename (col1, col2...)
2SELECT col1, col2...

Insert multiple rows (SQL Server, MySQL, Postgres)

1INSERT INTO tablename (col1, col2…)
2VALUES
3(valA1, valB1),
4(valA2, valB2),
5(valA3, valB3);

Insert multiple rows (Oracle)

1INSERT ALL
2INTO tablename (col1, col2) VALUES (valA1, valB1)
3INTO tablename (col1, col2) VALUES (valA2, valB2)
4SELECT * FROM dual;

Update

1UPDATE tablename
2SET col1 = val1
3WHERE condition;

Update with a Join

1UPDATE t
2SET col1 = val1
3FROM tablename t
4INNER JOIN table x
5ON t.id = x.tid
6WHERE condition;

Delete

1DELETE FROM tablename
2WHERE condition;

Indexes

Create Index

1CREATE INDEX indexname
2ON tablename (cols);

Drop Index

1DROP INDEX indexname;

Set Operators

Operator Image
UNION: Shows unique rows from two result sets.
UNION ALL: Shows all rows from two result sets.
INTERSECT: Shows rows that exist in both result sets.
MINUS (MySQL), EXCEPT (Oracle): Shows rows that exist in the first result set but not the second.

Aggregate Functions

  • SUM: Finds a total of the numbers provided
  • COUNT: Finds the number of records
  • AVG: Finds the average of the numbers provided
  • MIN: Finds the lowest of the numbers provided
  • MAX: Finds the highest of the numbers provided

Common Functions

MySQL

  • LENGTH(string): Returns the length of the provided string
  • INSTR(string, substring): Returns the position of the substring within the specified string.
  • CAST(expression AS datatype): Converts an expression into the specified data type.
  • ADDDATE(input_date, days): Adds a number of days to a specified date.
  • NOW: Returns the current date, including time.
  • CEILING(input_val): Returns the smallest integer greater than the provided number.
  • FLOOR(input_val): Returns the largest integer less than the provided number.
  • ROUND(input_val, [round_to]): Rounds a number to a specified number of decimal places.
  • TRUNCATE(input_value, num_decimals): Truncates a number to a number of decimals.
  • REPLACE(whole_string, string_to_replace, replacement_string): Replaces one string inside the whole string with another string.
  • SUBSTRING(string, start_position): Returns part of a value, based on a position and length.

Oracle

  • LENGTH(string): Returns the length of the provided string
  • INSTR(string, substring, [start_position], [occurrence]): Returns the position of the substring within the specified string.
  • TO_CHAR(input_value, [fmt_mask], [nls_param]): Converts a date or a number to a string
  • TO_DATE(charvalue, [fmt_mask], [nls_date_lang]): Converts a string to a date value.
  • TO_NUMBER(input_value, [fmt_mask], [nls_param]): Converts a string value to a number.
  • ADD_MONTHS(input_date, num_months): Adds a number of months to a specified date.
  • SYSDATE: Returns the current date, including time.
  • CEIL(input_val): Returns the smallest integer greater than the provided number.
  • FLOOR(input_val): Returns the largest integer less than the provided number.
  • ROUND(input_val, round_to): Rounds a number to a specified number of decimal places.
  • TRUNC(input_value, dec_or_fmt): Truncates a number or date to a number of decimals or format.
  • REPLACE(whole_string, string_to_replace, [replacement_string]): Replaces one string inside the whole string with another string.
  • SUBSTR(string, start_position, [length]): Returns part of a value, based on a position and length.

SQL Server

  • LEN(string): Returns the length of the provided string
  • CHARINDEX(string, substring, [start_position], [occurrence]): Returns the position of the substring within the specified string.
  • CAST(expression AS type [(length)]): Converts an expression to another data type.
  • GETDATE: Returns the current date, including time.
  • CEILING(input_val): Returns the smallest integer greater than the provided number.
  • FLOOR(input_val): Returns the largest integer less than the provided number.
  • ROUND(input_val, round_to, operation): Rounds a number to a specified number of decimal places.
  • REPLACE(whole_string, string_to_replace, replacement_string): Replaces one string inside the whole string with another string.
  • SUBSTRING(string, start_position, [length]): Returns part of a value, based on a position and length.

Postgres

  • LENGTH(string): Returns the length of the provided string
  • POSITION(string IN substring): Returns the position of the substring within the specified string.
  • CAST(expression AS datatype): Converts an expression into the specified data type.
  • NOW: Returns the current date, including time.
  • CEIL(input_val): Returns the smallest integer greater than the provided number.
  • FLOOR(input_val): Returns the largest integer less than the provided number.
  • ROUND(input_val, [round_to]): Rounds a number to a specified number of decimal places.
  • TRUNC(input_value, num_decimals): Truncates a number to a number of decimals.
  • REPLACE(whole_string, string_to_replace, replacement_string): Replaces one string inside the whole string with another string.
  • SUBSTRING(string, [start_pos], [length]): Returns part of a value, based on a position and length.

 

Create Table

Create Table

1CREATE TABLE tablename (
2  column_name data_type
3);

Create Table with Constraints

1CREATE TABLE tablename (
2  column_name data_type NOT NULL,
3  CONSTRAINT pkname PRIMARY KEY (col),
4  CONSTRAINT fkname FOREIGN KEY (col) REFERENCES other_table(col_in_other_table),
5  CONSTRAINT ucname UNIQUE (col),
6  CONSTRAINT ckname CHECK (conditions)
7);

Create Temporary Table (MySQL)

1CREATE TEMPORARY TABLE tablename (
2  colname datatype
3);

Create Temporary Table (Oracle)

1CREATE GLOBAL TEMPORARY TABLE tablename (
2  colname datatype
3) ON COMMIT DELETE ROWS;

Create Temporary Table (SQL Server)

1SELECT cols
2INTO #tablename
3FROM table;

Create Temporary Table (Postgres)

1CREATE TEMP TABLE tablename (
2  colname datatype
3);

Drop Table

1DROP TABLE tablename;

Alter Table

Add Column

1ALTER TABLE tablename
2ADD columnname datatype;

Drop Column

1ALTER TABLE tablename
2DROP COLUMN columnname;

Modify Column (MySQL)

1ALTER TABLE tablename
2CHANGE columnname newcolumnname newdatatype;

Modify Column (Oracle)

1ALTER TABLE tablename
2MODIFY columnname newdatatype;

Modify Column (SQL Server)

1ALTER TABLE tablename
2ALTER COLUMN columnname newdatatype;

Modify Column (Postgres)

1ALTER TABLE tablename
2ALTER COLUMN columnname TYPE newdatatype;

Rename Column (MySQL)

1ALTER TABLE tablename
2CHANGE COLUMN currentname TO newname;

Rename Column (Oracle, Postgres)

1ALTER TABLE tablename
2RENAME COLUMN currentname TO newname;

Rename Column (SQL Server)

1sp_rename 'table_name.old_column_name',
2'new_column_name', 'COLUMN';

Add Constraint

1ALTER TABLE tablename
2ADD CONSTRAINT constraintname constrainttype (columns);

Drop Constraint

1ALTER TABLE tablename
2DROP constraint_type constraintname;

Rename Table (MySQL, Oracle, Postgres)

1ALTER TABLE tablename
2RENAME TO newtablename;

Rename Table (SQL Server)

1sp_rename 'old_table_name', 'new_table_name';

Window/Analytic Functions

Syntax

1function_name ( arguments ) OVER (
2[query_partition_clause]
3[ORDER BY order_by_clause
4[windowing_clause] ] )

Example using RANK, showing the student details and their rank according to the fees_paid, grouped by gender:

 1SELECT
 2student_id,
 3first_name,
 4last_name,
 5gender,
 6fees_paid,
 7RANK() OVER (
 8  PARTITION BY gender ORDER BY fees_paid
 9) AS rank_val
10FROM student;

Subqueries

Single Row

1SELECT id, last_name, salary
2FROM employee
3WHERE salary = (
4  SELECT MAX(salary)
5  FROM employee
6);

Multi-Row

1SELECT id, last_name, salary
2FROM employee
3WHERE salary IN (
4  SELECT salary
5  FROM employee
6  WHERE last_name LIKE 'C%'
7);
comments powered by Disqus