SQL Cheat Sheet

The SQL cheat sheet provides you with the most commonly used SQL statements for your reference. You can download the SQL cheat sheet as follows:

SQL Cheet Sheet 1

Querying data from a table

Query data in columns c1, c2 from a table

SELECT c1, c2 FROM t; Code language: SQL (Structured Query Language) (sql)

Query all rows and columns from a table

SELECT * FROM t; Code language: SQL (Structured Query Language) (sql)

Query data and filter rows with a condition

SELECT c1, c2 FROM t WHERE condition;Code language: SQL (Structured Query Language) (sql)

Query distinct rows from a table

SELECT DISTINCT c1 FROM t WHERE condition; Code language: SQL (Structured Query Language) (sql)

Sort the result set in ascending or descending order

SELECT c1, c2 FROM t ORDER BY c1 ASC [DESC];Code language: SQL (Structured Query Language) (sql)

Skip offset of rows and return the next n rows

SELECT c1, c2 FROM t ORDER BY c1 LIMIT n OFFSET offset;Code language: SQL (Structured Query Language) (sql)

Group rows using an aggregate function

SELECT c1, aggregate(c2) FROM t GROUP BY c1; Code language: SQL (Structured Query Language) (sql)

Filter groups using HAVING clause

SELECT c1, aggregate(c2) FROM t GROUP BY c1 HAVING condition; Code language: SQL (Structured Query Language) (sql)

Querying from multiple tables

Inner join t1 and t2

SELECT c1, c2 FROM t1 INNER JOIN t2 ON condition; Code language: SQL (Structured Query Language) (sql)

Left join t1 and t1

SELECT c1, c2 FROM t1 LEFT JOIN t2 ON condition; Code language: SQL (Structured Query Language) (sql)

Right join t1 and t2

SELECT c1, c2 FROM t1 RIGHT JOIN t2 ON condition; Code language: SQL (Structured Query Language) (sql)

Perform full outer join

SELECT c1, c2 FROM t1 FULL OUTER JOIN t2 ON condition; Code language: SQL (Structured Query Language) (sql)

Produce a Cartesian product of rows in tables

SELECT c1, c2 FROM t1 CROSS JOIN t2; Code language: SQL (Structured Query Language) (sql)

Another way to perform cross join

SELECT c1, c2 FROM t1, t2; Code language: SQL (Structured Query Language) (sql)

Join t1 to itself using INNER JOIN clause

SELECT c1, c2 FROM t1 A INNER JOIN t1 B ON condition; Code language: SQL (Structured Query Language) (sql)

Using SQL Operators

Combine rows from two queries

SELECT c1, c2 FROM t1 UNION [ALL] SELECT c1, c2 FROM t2; Code language: SQL (Structured Query Language) (sql)

Return the intersection of two queries

SELECT c1, c2 FROM t1 INTERSECT SELECT c1, c2 FROM t2; Code language: SQL (Structured Query Language) (sql)

Subtract a result set from another result set

SELECT c1, c2 FROM t1 MINUS SELECT c1, c2 FROM t2; Code language: SQL (Structured Query Language) (sql)

Query rows using pattern matching %, _

SELECT c1, c2 FROM t1 WHERE c1 [NOT] LIKE pattern; Code language: SQL (Structured Query Language) (sql)

Query rows in a list

SELECT c1, c2 FROM t WHERE c1 [NOT] IN value_list; Code language: SQL (Structured Query Language) (sql)

Query rows between two values

SELECT c1, c2 FROM t WHERE c1 BETWEEN low AND high; Code language: SQL (Structured Query Language) (sql)

Check if values in a table is NULL or not

SELECT c1, c2 FROM t WHERE c1 IS [NOT] NULL; Code language: SQL (Structured Query Language) (sql)

Managing tables

Create a new table with three columns

CREATE TABLE t ( id INT PRIMARY KEY, name VARCHAR NOT NULL, price INT DEFAULT 0 ); Code language: SQL (Structured Query Language) (sql)

Delete the table from the database

DROP TABLE t ; Code language: SQL (Structured Query Language) (sql)

Add a new column to the table

ALTER TABLE t ADD column; Code language: SQL (Structured Query Language) (sql)

Drop column c from the table

ALTER TABLE t DROP COLUMN c ; Code language: SQL (Structured Query Language) (sql)

Add a constraint

ALTER TABLE t ADD constraint; Code language: SQL (Structured Query Language) (sql)

Drop a constraint

ALTER TABLE t DROP constraint; Code language: SQL (Structured Query Language) (sql)

Rename a table from t1 to t2

ALTER TABLE t1 RENAME TO t2; Code language: SQL (Structured Query Language) (sql)

Rename column c1 to c2

ALTER TABLE t1 RENAME c1 TO c2 ; Code language: SQL (Structured Query Language) (sql)

Remove all data in a table

TRUNCATE TABLE t; Code language: SQL (Structured Query Language) (sql)

Using SQL constraints

Set c1 and c2 as a primary key

CREATE TABLE t( c1 INT, c2 INT, c3 VARCHAR, PRIMARY KEY (c1,c2) ); Code language: SQL (Structured Query Language) (sql)

Set c2 column as a foreign key

CREATE TABLE t1( c1 INT PRIMARY KEY, c2 INT, FOREIGN KEY (c2) REFERENCES t2(c2) ); Code language: SQL (Structured Query Language) (sql)

Make the values in c1 and c2 unique

CREATE TABLE t( c1 INT, c1 INT, UNIQUE(c2,c3) ); Code language: SQL (Structured Query Language) (sql)

Ensure c1 > 0 and values in c1 >= c2

CREATE TABLE t( c1 INT, c2 INT, CHECK(c1> 0 AND c1 >= c2) ); Code language: SQL (Structured Query Language) (sql)

Set values in c2 column not NULL

CREATE TABLE t( c1 INT PRIMARY KEY, c2 VARCHAR NOT NULL ); Code language: SQL (Structured Query Language) (sql)

Modifying Data

Insert one row into a table

INSERT INTO t(column_list) VALUES(value_list); Code language: SQL (Structured Query Language) (sql)

Insert multiple rows into a table

INSERT INTO t(column_list) VALUES (value_list), (value_list), …; Code language: SQL (Structured Query Language) (sql)

Insert rows from t2 into t1

INSERT INTO t1(column_list) SELECT column_list FROM t2; Code language: SQL (Structured Query Language) (sql)

Update new value in the column c1 for all rows

UPDATE t SET c1 = new_value; Code language: SQL (Structured Query Language) (sql)

Update values in the column c1, c2 that match the condition

UPDATE t SET c1 = new_value, c2 = new_value WHERE condition; Code language: SQL (Structured Query Language) (sql)

Delete all data in a table

DELETE FROM t; Code language: SQL (Structured Query Language) (sql)

Delete subset of rows in a table

DELETE FROM t WHERE condition; Code language: SQL (Structured Query Language) (sql)

Managing Views

Create a new view that consists of c1 and c2

CREATE VIEW v(c1,c2) AS SELECT c1, c2 FROM t; Code language: SQL (Structured Query Language) (sql)

Create a new view with check option

CREATE VIEW v(c1,c2) AS SELECT c1, c2 FROM t; WITH [CASCADED | LOCAL] CHECK OPTION;Code language: SQL (Structured Query Language) (sql)

Create a recursive view

CREATE RECURSIVE VIEW v AS select-statement -- anchor part UNION [ALL] select-statement; -- recursive part Code language: SQL (Structured Query Language) (sql)

Create a temporary view

CREATE TEMPORARY VIEW v AS SELECT c1, c2 FROM t; Code language: SQL (Structured Query Language) (sql)

DROP VIEW view_name; Code language: SQL (Structured Query Language) (sql)

Managing indexes

Create an index on c1 and c2 of the t table

CREATE INDEX idx_name ON t(c1,c2); Code language: SQL (Structured Query Language) (sql)

Create a unique index on c3, c4 of the t table

CREATE UNIQUE INDEX idx_name ON t(c3,c4) Code language: SQL (Structured Query Language) (sql)

DROP INDEX idx_name; Code language: SQL (Structured Query Language) (sql)

Managing triggers

Create or modify a trigger

CREATE OR MODIFY TRIGGER trigger_name WHEN EVENT ON table_name TRIGGER_TYPE EXECUTE stored_procedure; Code language: SQL (Structured Query Language) (sql)

BEFORE – invoke before the event occurs
AFTER – invoke after the event occurs

INSERT – invoke for INSERT
UPDATE – invoke for UPDATE
DELETE – invoke for DELETE

TRIGGER_TYPE

Delete a specific trigger

DROP TRIGGER trigger_name; Code language: SQL (Structured Query Language) (sql)