This article features a fantastic SQL Cheat Sheet with the most commonly used SQL Commands. View it or download in the PDF or Image (JPG or PNG) format.
Our SQL Cheat Sheet enables you to revise almost the entire set of SQL Commands quickly. It is highly recommended to go through this cheat sheet if you are:
- Preparing for your SQL exam
- Going for an interview
- Or you an absolute beginner who quickly wants to have a hold on the different SQL Commands.
Download SQL Cheat Sheet in PDF Format
Download SQL Cheat Sheet in PNG Format
Download SQL Cheat Sheet in JPG Format
Table of Contents
Fetching Data from a Single Table
All of the demonstrated queries are fetching data from a table named abc
.
Fetching data in columns, col_1
, col_2
and col_3
from the table
1 |
SELECT col_1, col_2, col_3 from abc; |
Fetching data in all the columns of the table
1 |
SELECT * from abc; |
Fetching data in columns, col_1
, col_2
and col_3
from the table after filtering rows according to a condition.
1 2 |
SELECT col_1, col_2, col_3 from abc WHERE condition; |
Fetching only distinct rows from the table
1 2 |
SELECT DISTINCT col_1 FROM abc WHERE condition; |
Fetching rows in either Ascending or Descending order
1 2 |
SELECT col_1, col_2 FROM abc ORDER BY col_2 ASC [DESC]; |
Fetching n
number of rows after skipping offset
number of rows from the start
1 2 3 |
SELECT col_1, col_2 FROM t ORDER BY col_2 LIMIT n OFFSET offset; |
Using the aggregate function for grouping of rows
1 2 3 |
SELECT col_1, aggregate(col_2) FROM abc GROUP BY col_1; |
Fetching filtered rows using having
clause
1 2 3 4 |
SELECT col_1, aggregate(col_2) FROM abc GROUP BY col_1 HAVING condition; |
Fetching data from Multiple Tables
The queries are demonstrated for the two tabled named table1
and table2
.
Note. Colum names are represented by col_1
and col_2
but any of these two columns may belong to either of the tables. table1.col_1
, table2.col_1
, can be used to fetch the data in columns if both of the tables contain the same column named col_1
.
Applying inner join on table1
and table2
1 2 3 |
SELECT col_1, col_2 FROM table1 INNER JOIN table2 ON condition; |
Left joining table1
with table2
1 2 3 |
SELECT col_1, col_2 FROM table1 LEFT JOIN table2 ON condition; |
Right joining table1
with table2
1 2 3 |
SELECT col_1, col_2 FROM table1 RIGHT JOIN table2 ON condition; |
Applying Full Outer Join
1 2 3 |
SELECT col_1, col_2 FROM table1 FULL OUTER JOIN table2 ON condition; |
Fetching rows generated by performing cartesian product
1 2 3 |
SELECT col_1, col_2 FROM table1 CROSS JOIN table2; |
OR
1 2 |
SELECT col_1, col_2 FROM table1, table2; |
Self Joining table1
on itself by applying INNER JOIN
1 2 3 |
SELECT col_1, col_2 FROM table1 A INNER JOIN table2 B ON condition; |
SQL Operators
Fetching combined rows selected using two queries
1 2 3 |
SELECT col_1, col_2 FROM table1 UNION [ALL] SELECT col_1, col_2 FROM table2; |
Fetching the intersecting rows of the two queries
1 2 3 |
SELECT col_1, col_2 FROM table1 INTERSECT SELECT col_1, col_2 FROM table2; |
Subtracting the rows fetched using a query from another query
1 2 3 |
SELECT col_1, col_2 FROM table1 MINUS SELECT col_1, col_2 FROM table2; |
Fetching rows having column value matching or not with a given particular pattern
1 2 |
SELECT col_1, col_2 FROM abc WHERE col_1 LIKE pattern; |
OR
1 2 |
SELECT col_1, col_2 FROM abc WHERE col_1 NOT LIKE pattern; |
Fetching rows having column value in a given list
1 2 |
SELECT col_1, col_2 FROM abc WHERE col_1 [NOT] IN value_list; |
Fetching rows having column value in a given range
1 2 |
SELECT col_1, col_2 FROM abc WHERE col_1 BETWEEN low AND high; |
Fetching rows having column values either NULL or not NULL
1 2 |
SELECT col_1, col_2 FROM abc WHERE col_1 IS NULL; |
OR
1 2 |
SELECT col_1, col_2 FROM abc WHERE col_1 IS NOT NULL; |
Tables Management Commands
Following commands are illustrated with the database table named info
.
Creating a new table named info
with three different columns id
, name
and age
.
1 2 3 4 5 |
CREATE TABLE info ( id INT PRIMARY KEY, name VARCHAR NOT NULL, age INT DEFAULT 0 ); |
Delete or drop a database table
1 |
DROP TABLE info; |
Adding a new table column
1 |
ALTER TABLE info ADD col_x; |
Deleting a particular column from a given table
1 |
ALTER TABLE info DROP COLUMN col_x; |
Adding a constraint to a given table
1 |
ALTER TABLE info ADD constraint; |
Deleting a constraint from a given table
1 |
ALTER TABLE info DROP constraint; |
Renaming a particular table
1 |
ALTER TABLE current_name RENAME TO new_name; |
Renaming a particular table column
1 |
ALTER TABLE info RENAME current_col_name TO new_col_name; |
Removing all table data (Empty a database table)
1 |
TRUNCATE TABLE info; |
Modifying Table Data
Inserting a single row in a table named info
.
1 2 |
INSERT INTO info(col_1, col_2, col_3) VALUES(val_1, val_2, val_3); |
Inserting multiple rows into a table in a single query.
1 2 3 4 |
INSERT INTO info(col_1, col_2, col_3) VALUES (val_1, val_2, val_3), (val_1, val_2, val_3), (val_1, val_2, val_3), ...; |
Inserting all rows from table2
into table1
1 2 3 |
INSERT INTO t1(col_1, col_2, col_3) SELECT col_1, col_2, col_3 FROM t2; |
Updating a particular column value in all of the table rows.
1 2 |
UPDATE info SET col_1 = val_1; |
Updating multiple columns values in a single or multiple rows according to a given condition.
1 2 3 |
UPDATE info SET col_1 = val_1, col_2 = val_2 WHERE condition; |
Deleting all of the table rows.
1 |
DELETE FROM info; |
Deleting all of the table rows that satisfy a condition.
1 2 |
DELETE FROM info WHERE condition; |
SQL Constraints
Creating table1
with three columns col_1
, col_2
and col_3
such that col_1
and col_2
are the primary keys.
1 2 3 4 |
CREATE TABLE table1( col_1 INT, col_2 INT, col_3 VARCHAR, PRIMARY KEY (col_1, col_2) ); |
Creating a new table and setting a foreign key col_2
that refers to a column in another table.
1 2 3 4 5 |
CREATE TABLE table1( col_1 INT PRIMARY KEY, col_2 INT, FOREIGN KEY (col_2) REFERENCES table2(col_2) ); |
Creating a table with UNIQUE value columns, col_1
and col_2
1 2 3 4 |
CREATE TABLE table1( col_1 INT, col_2 INT, UNIQUE(col_1, col_2) ); |
Creating a table with VALUE IN RANGE constraint
1 2 3 4 |
CREATE TABLE table1( col_1 INT, col_2 INT, CHECK(col_1> 10 AND col_1= < col_2) ); |
Creating a table with a column that should not contain NULL values
1 2 3 4 |
CREATE TABLE table1( col_1 INT PRIMARY KEY, col_2 VARCHAR NOT NULL ); |
Views Management
Creating a view of two columns col_1
and col_2
of table1
.
1 2 3 4 |
CREATE VIEW view_1(col_1,col_2) AS SELECT col_1, col_2 FROM table1; |
Creating a view along with the CHECK
option.
1 2 3 4 5 |
CREATE VIEW view_1(col_1,col_2) AS SELECT col_1, col_2 FROM table1; WITH [CASCADED | LOCAL] CHECK OPTION; |
Recursive View creation
The first select statement is the anchor part while the second select statement is the recursive part in the following query.
1 2 3 4 5 |
CREATE RECURSIVE VIEW view_1 AS select-statement UNION [ALL] select-statement; |
Temporary View Creation
1 2 3 4 |
CREATE TEMPORARY VIEW view_1 AS SELECT col_1, col_2 FROM table1; |
Deleting a particular View
1 |
DROP VIEW view_1; |
Index Management
Creating an index on the col_1
and col_2
of table1
.
1 2 |
CREATE INDEX index_1 ON table1(col_1,col_2); |
Creating a UNIQUE index on the col_1
and col_2
of table1
.
1 2 |
CREATE UNIQUE INDEX index_1 ON table1(col_1,col_2) |
Deleting a given index
1 |
DROP INDEX index_1; |
Triggers Management
A Single query is written to demonstrate the creation of all the different possible triggers. It created a create trigger_1
on table1
. You have to replace WHEN, EVENT and TYPE with the terms specified after the query.
1 2 3 4 |
CREATE OR MODIFY TRIGGER trigger_1 WHEN EVENT ON table1 TRIGGER_TYPE EXECUTE stored_procedure; |
WHEN
BEFORE
to invoke the trigger before the event occurs and AFTER
to invoke the trigger after the event occurs.
EVENT
INSERT
, UPDATE
or DELETE
to perform the insertion, updation and deletion actions respectively.
TYPE
It can be FOR EACH ROW
and FOR EACH STATEMENT
Deleting a given Trigger
1 |
DROP TRIGGER trigger_1 |
SQL Functions
You can use these functions within queries to perform some sort of mathematical calculations for the table data or to perform some scalar operations. All of these function returns a single value, not a resultset. Further, you can use these functions in the queries to get resultsets, mostly, by applying conditions using WHERE or HAVING clause.
Aggregate Functions
- AVG()
- COUNT()
- FIRST()
- LAST()
- MAX()
- MIN()
- SUM()
As their names suggest, these functions return the average, count, first, last, maximum, minimum and sum values of a list.
Scalar Functions
- UCASE()
- LCASE()
- MID()
- LEN()
- ROUND()
- NOW()
- FORMAT()
These function also works as their name suggests.
I hope you find this guide useful. If so, do share it with others who are willing to learn SQL. If you have any questions related to this article, feel free to ask them in the comments section.
Also, don’t forget to Subscribe to WTMatter!