Oracle SQL Cheat Sheet

SELECT Query Modifying Data Create Table

SELECT col1, col2
FROM table Insert INSERT INTO tablename CreateTable CREATE TABLE tablename (
JOIN table2 ON table1.col = table2.col (col1, col2...) column_name data_type
WHERE condition VALUES (val1, val2); );
GROUP BY column_name Insert from a
HAVING condition INSERT INTO tablename
Table
ORDER BY col1 ASC|DESC; (col1, col2...) Create Table with Constraints
SELECT col1, col2...

SELECT Keywords Insert Multiple

Rows
INSERT
INTO tablename (col1, col2)
CREATE TABLE tablename (
column_name data_type NOT NULL,
VALUES (valA1, valB1) CONSTRAINT pkname PRIMARY KEY (col),
DISTINCT: Removes SELECT DISTINCT product_name INTO tablename (col1, col2) CONSTRAINT fkname FOREIGN KEY (col)
duplicate results FROM product; VALUES (valA2, valB2) REFERENCES other_table(col_in_other_table),
SELECT * FROM dual; CONSTRAINT ucname UNIQUE (col),
BETWEEN: Matches a CONSTRAINT ckname CHECK (conditions)
SELECT product_name
value between two FROM product Update UPDATE tablename );
other values (inclusive) WHERE price BETWEEN 50 AND 100; SET col1 = val1
WHERE condition;
Create Temporary CREATE GLOBAL TEMPORARY TABLE
SELECT product_name Table tablename (
IN: Matches to any of
FROM product Update with UPDATE t colname datatype
the values in a list
WHERE category IN a Join SET col1 = val1 ) ON COMMIT DELETE ROWS;
('Electronics', 'Furniture'); FROM tablename t
INNER JOIN table x Drop Table DROP TABLE tablename;
LIKE: Performs SELECT product_name ON t.id = x.tid
wildcard matches using FROM product WHERE condition;
_or% WHERE product_name Alter Table
LIKE '%Desk%"; Delete DELETE FROM tablename
WHERE condition;
Add Column ALTER TABLE tablename
ADD columnname datatype;
Joins Indexes
Drop Column ALTER TABLE tablename
Create Index CREATE INDEX indexname DROP COLUMN columnname;
SELECT t1.*, t2.*
ON tablename (cols);
FROM t1
join_type t2 ON t1.col = t2.col; Modify Column ALTER TABLE tablename MODIFY
Drop Index DROP INDEX indexname; columnname newdatatype;
Table 1 Table 2

A A Set Operators Rename Column ALTER TABLE tablename RENAME COLUMN

currentname TO newname;
B B

C D UNION: Shows unique

Add Constraint ALTER TABLE tablename ADD
rows from two result sets.
CONSTRAINT constraintname
INNER JOIN: show all matching A A constrainttype (columns);
records in both tables. UNION ALL: Shows all
B B
rows from two result sets.
Drop Constraint ALTER TABLE tablename DROP
constraint_type constraintname;
LEFT JOIN: show all records from left A A
table, and any matching records from INTERSECT: Shows rows that
B B exist in both result sets. Rename Table sp_rename
right table.
C
'old_table_name',
'new_table_name';
EXCEPT: Shows rows that exist
RIGHT JOIN: show all records from A in the first result set but not
Window/Analytic Functio
A
right table, and any matching records the second.
B B
from left table.
D
Aggregate Functions function_name ( arguments ) OVER
( [query_partition_clause]
FULL JOIN: show all records from A A SUM: Finds a total of the numbers provided
[ORDER BY order_by_clause
both tables, whether there is a match COUNT: Finds the number of records
[windowing_clause] ] )
or not. B B
AVG: Finds the average of the numbers provided
Example using RANK, showing the student details and their rank
C MIN: Finds the lowest of the numbers provided
according to the fees_paid, grouped by gender:
MAX: Finds the highest of the numbers provided
D

Common Functions SELECT

student_id, first_name, last_name, gender, fees_paid,
CASE Statement LENGTH(string): Returns the length of the provided string RANK() OVER (
INSTR(string, substring, [start_position], [occurrence]): Returns the PARTITION BY gender ORDER BY fees_paid
position of the substring within the specified string. ) AS rank_val
Simple Case CASE name TO_CHAR(input_value, [fmt_mask], [nls_param]): Converts a date FROM student;
WHEN 'John' THEN 'Name John' or a number to a string
WHEN 'Steve' THEN 'Name Steve' TO_DATE(charvalue, [fmt_mask], [nls_date_lang]): Converts a

Subqueries
ELSE 'Unknown' string to a date value.
END TO_NUMBER(input_value, [fmt_mask], [nls_param]): Converts a
string value to a number.
Single Row SELECT id, last_name, salary
Searched Case CASE ADD_MONTHS(input_date, num_months): Adds a number of
FROM employee
WHEN name='John' THEN 'Name John' months to a specified date.
SYSDATE: Returns the current date, including time. WHERE salary = (
WHEN name='Steve' THEN 'Name Steve' SELECT MAX(salary)
ELSE 'Unknown' CEIL(input_val): Returns the smallest integer greater than the
provided number. FROM employee
END );
FLOOR(input_val): Returns the largest integer less than the
provided number.
Common Table Expression ROUND(input_val, round_to): Rounds a number to a specified
Multi Row SELECT id, last_name, salary
FROM employee
number of decimal places.
WHERE salary IN
TRUNC(input_value, dec_or_fmt): Truncates a number or date to a
WITH queryname AS ( SELECT salary
number of decimals or format.
( SELECT col1, FROM employee
REPLACE(whole_string, string_to_replace, [replacement_string]):
col2 FROM WHERE last_name LIKE 'C%'
Replaces one string inside the whole string with another string.
firsttable) );
SUBSTR(string, start_position, [length]): Returns part of a value,
SELECT col1, col2..
based on a position and length.
FROM queryname...;
 SQL Server Cheat Sheet
SELECT Query Modifying Data Create Table
SELECT col1, col2
FROM table Insert INSERT INTO tablename CreateTable CREATE TABLE tablename (
JOIN table2 ON table1.col = table2.col (col1, col2...) column_name data_type
WHERE condition VALUES (val1, val2); );
GROUP BY column_name Insert from a
HAVING condition INSERT INTO tablename
Table
ORDER BY col1 ASC|DESC; (col1, col2...) Create Table with Constraints
SELECT col1, col2...

SELECT Keywords Insert Multiple

Rows
INSERT INTO tablename
(col1, col2...) VALUES
CREATE TABLE tablename (
column_name data_type NOT NULL,
(valA1, valB1), CONSTRAINT pkname PRIMARY KEY (col),
DISTINCT: Removes SELECT DISTINCT product_name (valA2, valB2), CONSTRAINT fkname FOREIGN KEY (col)
duplicate results FROM product; (valA3, valB3); REFERENCES other_table(col_in_other_table),
CONSTRAINT ucname UNIQUE (col),
BETWEEN: Matches a CONSTRAINT ckname CHECK (conditions)
SELECT product_name
value between two FROM product Update UPDATE tablename );
other values (inclusive) WHERE price BETWEEN 50 AND 100; SET col1 = val1
WHERE condition;
Create Temporary SELECT cols
SELECT product_name Table INTO #tablename
IN: Matches to any of
FROM product Update with UPDATE t FROM table;
the values in a list
WHERE category IN a Join SET col1 = val1
('Electronics', 'Furniture'); FROM tablename t
INNER JOIN table x Drop Table DROP TABLE tablename;
LIKE: Performs SELECT product_name ON t.id = x.tid
wildcard matches using FROM product WHERE condition;
_or% WHERE product_name Alter Table
LIKE '%Desk%"; Delete DELETE FROM tablename
WHERE condition;
Add Column ALTER TABLE tablename
ADD columnname datatype;
Joins Indexes
Drop Column ALTER TABLE tablename
Create Index CREATE INDEX indexname DROP COLUMN columnname;
SELECT t1.*, t2.*
ON tablename (cols);
FROM t1
join_type t2 ON t1.col = t2.col; Modify Column ALTER TABLE tablename ALTER COLUMN
Drop Index DROP INDEX indexname; columnname newdatatype;
Table 1 Table 2

A A
Set Operators Rename Column sp_rename
'table_name.old_column_name',
B B
'new_column_name', 'COLUMN';
C D UNION: Shows unique
rows from two result sets. Add Constraint ALTER TABLE tablename ADD
CONSTRAINT constraintname
INNER JOIN: show all matching A A
constrainttype (columns);
records in both tables. UNION ALL: Shows all
B B
rows from two result sets.
Drop Constraint ALTER TABLE tablename
DROP CONSTRAINT constraintname;
LEFT JOIN: show all records from left A A
table, and any matching records from INTERSECT: Shows rows that
right table. B B exist in both result sets. Rename Table ALTER TABLE tablename
RENAME TO newtablename;
C

MINUS: Shows rows that exist

in the first result set but not
RIGHT JOIN: show all records from
right table, and any matching records
A A
the second. Window/Analytic Functio
B B
from left table.
D
Aggregate Functions function_name ( arguments ) OVER
( [query_partition_clause]
FULL JOIN: show all records from SUM: Finds a total of the numbers provided [ORDER BY order_by_clause
A A
both tables, whether there is a match COUNT: Finds the number of records [windowing_clause] ] )
or not. B B AVG: Finds the average of the numbers provided
MIN: Finds the lowest of the numbers provided Example using RANK, showing the student details and their rank
C
MAX: Finds the highest of the numbers provided according to the fees_paid, grouped by gender:
D
SELECT

Common Functions student_id, first_name, last_name, gender, fees_paid,

CASE Statement RANK() OVER (
PARTITION BY gender ORDER BY fees_paid
LEN(string): Returns the length of the provided string
) AS rank_val
Simple Case CASE name CHARINDEX(string, substring, [start_position], [occurrence]):
FROM student;
WHEN 'John' THEN 'Name John' Returns the position of the substring within the specified string.
WHEN 'Steve' THEN 'Name Steve' CAST(expression AS type [(length)]): Converts an expression to
another data type.
ELSE 'Unknown'
END
GETDATE: Returns the current date, including time. Subqueries
CEILING(input_val): Returns the smallest integer greater than the
provided number. Single Row SELECT id, last_name, salary
Searched Case CASE FLOOR(input_val): Returns the largest integer less than the FROM employee
WHEN name='John' THEN 'Name John' provided number. WHERE salary = (
WHEN name='Steve' THEN 'Name Steve' ROUND(input_val, round_to, operation): Rounds a number to a SELECT MAX(salary)
ELSE 'Unknown' specified number of decimal places. FROM employee
END REPLACE(whole_string, string_to_replace, replacement_string): );
Replaces one string inside the whole string with another string.
Common Table Expression SUBSTRING(string, start_position, [length]): Returns part of a
value, based on a position and length.
Multi Row SELECT id, last_name, salary
FROM employee
WHERE salary IN
WITH queryname (col1, col2...) AS ( SELECT salary
( SELECT col1, col2 FROM employee
FROM firsttable) WHERE last_name LIKE 'C%'
SELECT col1, col2.. );
FROM queryname...;
 SELECT Query MySQL Cheat Sheet Modifying Data
SELECT col1, col2

Create Table
FROM table Insert INSERT INTO tablename Create Table CREATE TABLE tablename (
JOIN table2 ON table1.col = table2.col (col1, col2...) column_name data_type
WHERE condition VALUES (val1, val2); );
GROUP BY column_name Insert from a
INSERT INTO tablename
HAVING condition Table
ORDER BY col1 ASC|DESC; (col1, col2...) Create Table with Constraints
SELECT col1, col2...

SELECT Keywords Insert Multiple

Rows
INSERT INTO tablename (col1,
col2…)
CREATE TABLE tablename (
column_name data_type NOT NULL,
VALUES CONSTRAINT pkname PRIMARY KEY (col),
DISTINCT: Removes SELECT DISTINCT product_name (valA1, valB1), CONSTRAINT fkname FOREIGN KEY (col)
duplicate results FROM product; (valA2, valB2), REFERENCES other_table(col_in_other_table),
(valA3, valB3); CONSTRAINT ucname UNIQUE (col),
BETWEEN: Matches a CONSTRAINT ckname CHECK (conditions)
SELECT product_name
value between two FROM product Update UPDATE tablename );
other values (inclusive) WHERE price BETWEEN 50 AND 100; SET col1 = val1
WHERE condition;
Create Temporary CREATE TEMPORARY TABLE
SELECT product_name Table tablename (
IN: Matches to any of
FROM product Update with UPDATE t colname datatype
the values in a list
WHERE category IN a Join SET col1 = val1 );
('Electronics', 'Furniture'); FROM tablename t
INNER JOIN table x Drop Table DROP TABLE tablename;
LIKE: Performs SELECT product_name ON t.id = x.tid
wildcard matches using FROM product WHERE condition;
_or% WHERE product_name Alter Table
LIKE '%Desk%"; Delete DELETE FROM tablename
WHERE condition;
Add Column ALTER TABLE tablename
ADD columnname datatype;
Joins Indexes
Drop Column ALTER TABLE tablename
Create Index CREATE INDEX indexname DROP COLUMN columnname;
SELECT t1.*, t2.*
ON tablename (cols);
FROM t1
join_type t2 ON t1.col = t2.col; Modify Column ALTER TABLE tablename CHANGE
Drop Index DROP INDEX indexname; columnname newcolumnname newdatatype;
Table 1 Table 2

A A Set Operators Rename Column ALTER TABLE tablename CHANGE

COLUMN currentname TO newname;
B B

C D UNION: Shows unique

Add Constraint ALTER TABLE tablename ADD
rows from two result sets.
CONSTRAINT constraintname
INNER JOIN: show all matching A A constrainttype (columns);
records in both tables. UNION ALL: Shows all
B B
rows from two result sets.
Drop Constraint ALTER TABLE tablename DROP
constraint_type constraintname;
LEFT JOIN: show all records from left A A
table, and any matching records from INTERSECT: Shows rows that
right table. B B exist in both result sets. Rename Table ALTER TABLE tablename
C
RENAME TO newtablename;

MINUS: Shows rows that exist

in the first result set but not
RIGHT JOIN: show all records from
right table, and any matching records
A A
the second. Window/Analytic Functio
B B
from left table.
D function_name ( arguments ) OVER
Aggregate Functions ( [query_partition_clause]
[ORDER BY order_by_clause
FULL JOIN: show all records from A A
both tables, whether there is a match [windowing_clause] ] )
B
SUM: Finds a total of the numbers provided
or not. B
COUNT: Finds the number of records Example using RANK, showing the student details and their rank
C AVG: Finds the average of the numbers provided according to the fees_paid, grouped by gender:
MIN: Finds the lowest of the numbers provided
D
MAX: Finds the highest of the numbers provided SELECT
student_id, first_name, last_name, gender, fees_paid,
CASE Statement RANK() OVER (

Common Functions PARTITION BY gender ORDER BY fees_paid

) AS rank_val
Simple Case CASE name FROM student;
WHEN 'John' THEN 'Name John' LENGTH(string): Returns the length of the provided string
INSTR(string, substring): Returns the position of the substring
WHEN 'Steve' THEN 'Name Steve'
within the specified string.
ELSE 'Unknown'
END
CAST(expression AS datatype): Converts an expression into the Subqueries
specified data type.
ADDDATE(input_date, days): Adds a number of days to a Single Row SELECT id, last_name, salary
Searched Case CASE specified date. FROM employee
WHEN name='John' THEN 'Name John' NOW: Returns the current date, including time. WHERE salary = (
WHEN name='Steve' THEN 'Name Steve' CEILING(input_val): Returns the smallest integer greater than SELECT MAX(salary)
ELSE 'Unknown' the provided number. FROM employee
END FLOOR(input_val): Returns the largest integer less than the );
provided number.

Common Table Expression ROUND(input_val, [round_to]): Rounds a number to a specified

number of decimal places.
Multi Row SELECT id, last_name, salary
FROM employee
TRUNCATE(input_value, num_decimals): Truncates a number to WHERE salary IN
WITH queryname AS a number of decimals. ( SELECT salary
( SELECT col1, REPLACE(whole_string, string_to_replace, replacement_string): FROM employee
col2 FROM Replaces one string inside the whole string with another string. WHERE last_name LIKE 'C%'
firsttable) SUBSTRING(string, start_position): Returns part of a value, );
SELECT col1, col2.. based on a position and length.
FROM queryname...;
 PostgreSQL Cheat Sheet
SELECT Query Modifying Data Create Table
SELECT col1, col2
FROM table Insert INSERT INTO tablename CreateTable CREATE TABLE tablename (
JOIN table2 ON table1.col = table2.col (col1, col2...) column_name data_type
WHERE condition VALUES (val1, val2); );
GROUP BY column_name Insert from a
HAVING condition INSERT INTO tablename
Table
ORDER BY col1 ASC|DESC; (col1, col2...) Create Table with Constraints
SELECT col1, col2...

SELECT Keywords Insert Multiple

Rows
INSERT INTO tablename
(col1, col2...) VALUES
CREATE TABLE tablename (
column_name data_type NOT NULL,
(valA1, valB1), CONSTRAINT pkname PRIMARY KEY (col),
DISTINCT: Removes SELECT DISTINCT product_name (valA2, valB2), CONSTRAINT fkname FOREIGN KEY (col)
duplicate results FROM product; (valA3, valB3); REFERENCES other_table(col_in_other_table),
CONSTRAINT ucname UNIQUE (col),
BETWEEN: Matches a CONSTRAINT ckname CHECK (conditions)
SELECT product_name
value between two FROM product Update UPDATE tablename );
other values (inclusive) WHERE price BETWEEN 50 AND 100; SET col1 = val1
WHERE condition;
Create Temporary CREATE TEMP TABLE tablename
SELECT product_name Table ( colname datatype
IN: Matches to any of
FROM product Update with UPDATE t );
the values in a list
WHERE category IN a Join SET col1 = val1
('Electronics', 'Furniture'); FROM tablename t
INNER JOIN table x Drop Table DROP TABLE tablename;
LIKE: Performs SELECT product_name ON t.id = x.tid
wildcard matches using FROM product WHERE condition;
_or% WHERE product_name Alter Table
LIKE '%Desk%"; Delete DELETE FROM tablename
WHERE condition;
Add Column ALTER TABLE tablename ADD COLUMN
columnname datatype;
Joins Indexes
Drop Column ALTER TABLE tablename DROP COLUMN
Create Index CREATE INDEX indexname columnname;
SELECT t1.*, t2.*
ON tablename (cols);
FROM t1
join_type t2 ON t1.col = t2.col; Modify Column ALTER TABLE tablename ALTER COLUMN
Drop Index DROP INDEX indexname; columnname TYPE newdatatype;
Table 1 Table 2

A A Set Operators Rename Column ALTER TABLE tablename RENAME COLUMN

currentname TO newname;
B B

C D UNION: Shows unique

Add Constraint ALTER TABLE tablename ADD CONSTRAINT
rows from two result sets.
constraintname constrainttype
INNER JOIN: show all matching A A (columns);
records in both tables. UNION ALL: Shows all
B B
rows from two result sets.
Drop Constraint ALTER TABLE tablename DROP
constraint_type constraintname;
LEFT JOIN: show all records from left A A
table, and any matching records from INTERSECT: Shows rows that
B B exist in both result sets. Rename Table ALTER TABLE tablename
right table.
C
RENAME TO newtablename;

EXCEPT: Shows rows that exist

RIGHT JOIN: show all records from A in the first result set but not
Window/Analytic Functio
A
right table, and any matching records the second.
B B
from left table.
D
Aggregate Functions function_name ( arguments ) OVER
( [query_partition_clause]
FULL JOIN: show all records from A A SUM: Finds a total of the numbers provided
[ORDER BY order_by_clause
both tables, whether there is a match COUNT: Finds the number of records
[windowing_clause] ] )
or not. B B
AVG: Finds the average of the numbers provided
Example using RANK, showing the student details and their rank
C MIN: Finds the lowest of the numbers provided
according to the fees_paid, grouped by gender:
MAX: Finds the highest of the numbers provided
D
SELECT
Common Functions student_id, first_name, last_name, gender, fees_paid,
CASE Statement RANK() OVER (
LENGTH(string): Returns the length of the provided string PARTITION BY gender ORDER BY fees_paid
POSITION(string IN substring): Returns the position of the ) AS rank_val
Simple Case CASE name
substring within the specified string. FROM student;
WHEN 'John' THEN 'Name John' CAST(expression AS datatype): Converts an expression into the
WHEN 'Steve' THEN 'Name Steve' specified data type.

Subqueries
ELSE 'Unknown' NOW: Returns the current date, including time.
END CEIL(input_val): Returns the smallest integer greater than the
provided number.
Single Row SELECT id, last_name, salary
Searched Case CASE FLOOR(input_val): Returns the largest integer less than the
FROM employee
WHEN name='John' THEN 'Name John' provided number.
ROUND(input_val, [round_to]): Rounds a number to a specified WHERE salary = (
WHEN name='Steve' THEN 'Name Steve' SELECT MAX(salary)
number of decimal places.
ELSE 'Unknown' FROM employee
TRUNC(input_value, num_decimals): Truncates a number to a
END
number of decimals. );
REPLACE(whole_string, string_to_replace, replacement_string):
Common Table Expression Replaces one string inside the whole string with another string. Multi Row SELECT id, last_name, salary
FROM employee
SUBSTRING(string, [start_pos], [length]): Returns part of a value,
based on a position and length. WHERE salary IN
WITH queryname AS ( SELECT salary
( SELECT col1, FROM employee
col2 FROM WHERE last_name LIKE 'C%'
firsttable) );
SELECT col1, col2..
FROM queryname...;