Table 3354. Cheatsheet of SQL (Structured Query Language).
| Command |
Description |
Syntax |
Example |
| Data Control Language (DCL) Commands |
| GRANT |
The GRANT command is used to give specific privileges to users or roles. |
GRANT SELECT, INSERT ON table_name TO username |
GRANT SELECT ON employees TO John; |
| REVOKE |
The REVOKE command is used to take away privileges previously granted to users or roles |
REVOKE SELECT, INSERT ON table_name FROM username |
REVOKE SELECT ON employees FROM John; |
| Querying Data Commands |
| SELECT Statement |
The SELECT statement is the primary command used to retrieve data from a database. |
SELECT column1, column2
FROM table_name; |
SELECT first_name,
last_name FROM customer_name; |
| WHERE Clause |
The WHERE clause is used to filter rows based on a specified condition. |
SELECT * FROM table_name WHERE condition; |
SELECT * FROM customers
WHERE age > 30; |
| ORDER BY Clause |
The ORDER BY clause is used to sort the result set in ascending or descending order based on a specified column. |
SELECT * FROM table_name ORDER BY column_name ASC|DESC; |
SELECT * FROM products
ORDER BY price DESC; |
| GROUP BY Clause |
The GROUP BY clause groups rows based on the values in a specified column. It is often used with aggregate functions like COUNT, SUM, AVG, etc. |
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name; |
SELECT category,
FROM products GROUP BY
category; |
| HAVING Clause |
The HAVING clause filters grouped results based on a specified condition. |
SELECT column_name, COUNT(*) FROM table_name
GROUP BY column_name
HAVING condition; |
SELECT category, COUNT(*)
FROM products GROUP BY
category HAVING COUNT(*)
> 5; |
Data Manipulation Language (DML) commands |
| SELECT |
Retrieves data from a database. |
SELECT column1, column2 FROM table_name; |
SELECT first_name, last_name FROM customers; |
| INSERT |
Adds new records to a table. |
INSERT INTO table_name (column1, column2) VALUES (value1, value2); |
INSERT INTO customers (first_name, last_name) VALUES ('Harry', 'Doe'); |
| UPDATE |
Modifies existing records in a table. |
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition; |
UPDATE employees SET employee_name = 'John Doe', department = 'Marketing' WHERE employee_id = 1; |
| DELETE |
Removes records from a table. |
DELETE FROM table_name WHERE condition; |
DELETE FROM employees WHERE employee_name = 'John Doe'; |
| Data Definition Language (DDL) commands |
| CREATE |
Creates a new table, a view of a table, or other object in the database. |
CREATE TABLE table_name (column1 datatype, column2 datatype, ...); |
CREATE TABLE employees (id INT, name VARCHAR(50), age INT); |
| ALTER |
Modifies an existing database object, such as a table. |
ALTER TABLE table_name ADD column_name datatype; |
ALTER TABLE employees ADD email VARCHAR(100); |
| DROP |
Deletes an entire table, a view of a table, or other object in the database. |
DROP TABLE table_name; |
DROP TABLE employees; |
| TRUNCATE |
Removes all records from a table, including all spaces allocated for the records are removed. |
TRUNCATE TABLE table_name; |
TRUNCATE TABLE employees; |
| COMMENT |
Adds comments to the data dictionary. |
COMMENT ON TABLE table_name IS 'comment'; |
COMMENT ON TABLE employees IS 'Stores employee records.'; |
| Data Control Language (DCL) commands |
| GRANT |
Used to give users access privileges to the database |
GRANT privileges ON object TO user; |
GRANT SELECT ON employees TO user1; |
| REVOKE |
Used to take back permissions from users |
REVOKE privileges ON object FROM user; |
REVOKE SELECT ON employees FROM user1; |
| Querying Data Language (usually referred to as Data Query Language or DQL) commands |
| SELECT |
Retrieves data from a database. |
SELECT column1, column2 FROM table_name; |
SELECT first_name, last_name FROM customers; |
| FROM |
Specifies the table to query data from. |
SELECT column1 FROM table_name; |
SELECT * FROM employees; |
| WHERE |
Filters the result set to include only records that match a specified condition. |
SELECT column1 FROM table_name WHERE condition; |
SELECT * FROM employees WHERE department = 'Sales'; |
| JOIN |
Combines rows from two or more tables based on a related column between them. |
SELECT columns FROM table1 JOIN table2 ON table1.column_name = table2.column_name; |
SELECT orders.order_id, customers.customer_name FROM orders JOIN customers ON orders.customer_id = customers.customer_id; |
| GROUP BY |
Groups rows that have the same values in specified columns into summary rows, like "find the number of customers in each country". |
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name; |
SELECT department, COUNT(*) FROM employees GROUP BY department; |
| HAVING |
Filters groups created by GROUP BY based on a condition. |
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING condition; |
SELECT department, COUNT() FROM employees GROUP BY department HAVING COUNT() > 5; |
| ORDER BY |
Sorts the result set in ascending or descending order. |
SELECT column1 FROM table_name ORDER BY column1 ASC |
DESC; |
| LIMIT |
Specifies the maximum number of records to return in the result set. |
SELECT column1 FROM table_name LIMIT number; |
SELECT * FROM customers LIMIT 10; |
| DISTINCT |
Returns only distinct (different) values in the result set. |
SELECT DISTINCT column1 FROM table_name; |
SELECT DISTINCT country FROM customers; |
| Joining commands |
| INNER JOIN |
Retrieves records that have matching values in both tables. |
SELECT columns FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name; |
SELECT a.name, b.salary FROM employees a INNER JOIN salaries b ON a.employee_id = b.employee_id; |
| LEFT JOIN (or LEFT OUTER JOIN) |
Returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side if there is no match. |
SELECT columns FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name; |
SELECT a.name, b.salary FROM employees a LEFT JOIN salaries b ON a.employee_id = b.employee_id; |
| RIGHT JOIN (or RIGHT OUTER JOIN) |
Returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side when there is no match. |
SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name; |
SELECT a.name, b.salary FROM employees a RIGHT JOIN salaries b ON a.employee_id = b.employee_id; |
| FULL JOIN (or FULL OUTER JOIN) |
Returns all records when there is a match in either left (table1) or right (table2) table records. |
SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name; |
SELECT a.name, b.salary FROM employees a FULL OUTER JOIN salaries b ON a.employee_id = b.employee_id; |
| CROSS JOIN |
Returns all records where each row from the first table is combined with each row from the second table. |
SELECT columns FROM table1 CROSS JOIN table2; |
SELECT a.name, b.salary FROM employees a CROSS JOIN salaries b; |
| SELF JOIN |
A regular join, but the table is joined with itself. |
SELECT columns FROM table1 a, table1 b WHERE condition; |
SELECT a.name, b.name FROM employees a, employees b WHERE a.manager_id = b.employee_id; |
| NATURAL JOIN |
Performs a join using all columns with the same name for comparison. |
SELECT columns FROM table1 NATURAL JOIN table2; |
SELECT * FROM employees NATURAL JOIN salaries; |
| Subqueries (known as inner queries or nested queries) |
| Subquery |
A SELECT statement nested within another SQL statement |
(SELECT column_name FROM table_name WHERE condition) |
WHERE EXISTS (SELECT 1 FROM table WHERE condition) |
| EXISTS |
Checks if subquery returns any rows |
EXISTS (subquery) |
SELECT * FROM table WHERE EXISTS (SELECT * FROM table2 WHERE table.id = table2.id) |
| IN |
Checks if a value is within a set of subquery results |
value IN (subquery) |
SELECT * FROM table WHERE column IN (SELECT column FROM table2) |
| ANY/SOME |
Compares value to each value returned by subquery |
value operator ANY (subquery) |
SELECT * FROM table WHERE column > ANY (SELECT column FROM table2) |
| ALL |
Compares value to every value returned by subquery |
value operator ALL (subquery) |
SELECT * FROM table WHERE column > ALL (SELECT column FROM table2) |
| Nested SELECT |
Subquery in the SELECT clause for column computation |
SELECT (SELECT SUM(column) FROM table2) AS total FROM table |
SELECT (SELECT COUNT(*) FROM table2) AS count FROM table |
| FROM Subquery |
Subquery in the FROM clause acting as a temporary table |
SELECT * FROM (SELECT column FROM table2) AS sub_table |
SELECT * FROM (SELECT column FROM table2) sub_table WHERE sub_table.column > 100 |
| ANY |
Compares a value to each value returned by subquery |
value operator ANY (subquery) |
SELECT * FROM table WHERE column = ANY (SELECT column FROM table2) |
| Aggregate functions |
| COUNT |
Counts the number of rows |
COUNT(column_name) |
SELECT COUNT(quantity) FROM sales; |
| SUM |
Adds together all values in a column |
SUM(column_name) |
SELECT SUM(price) FROM sales; |
| AVG |
Calculates the average value of a column |
AVG(column_name) |
SELECT AVG(price) FROM sales; |
| MAX |
Finds the maximum value in a column |
MAX(column_name) |
SELECT MAX(price) FROM sales; |
| MIN |
Finds the minimum value in a column |
MIN(column_name) |
SELECT MIN(price) FROM sales; |
| GROUP_CONCAT |
Concatenates column values into a string |
GROUP_CONCAT(column_name SEPARATOR ', ') |
SELECT GROUP_CONCAT(quantity SEPARATOR ', ') FROM sales;
Or:
SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id;
|
| String functions |
| CHAR_LENGTH or LENGTH |
Returns the number of characters in a string. |
CHAR_LENGTH(string) |
SELECT CHAR_LENGTH('Hello World'); |
| CONCAT |
Concatenates two or more strings together. |
CONCAT(string1, string2, ...) |
SELECT CONCAT('Hello', ' ', 'World'); |
| UPPER |
Converts all characters in a string to uppercase. |
UPPER(string) |
SELECT UPPER('Hello World'); |
| LOWER |
Converts all characters in a string to lowercase. |
LOWER(string) |
SELECT LOWER('Hello World'); |
| SUBSTRING |
Extracts a substring from a string. |
SUBSTRING(string FROM start FOR length) |
SELECT SUBSTRING('Hello World', 1, 5); |
| TRIM |
Removes spaces or specified characters from the start and end of a string. |
TRIM([characters FROM] string) |
SELECT TRIM(' Hello World '); |
| REPLACE |
Replaces all occurrences of a specified string. |
REPLACE(string, old_string, new_string) |
SELECT REPLACE('Hello World', 'World', 'SQL'); |
| POSITION |
Finds the position of a substring in a string. |
POSITION(substring IN string) |
SELECT POSITION('World' IN 'Hello World'); |
| ASCII |
Returns the ASCII value of the first character of the string. |
ASCII(string) |
SELECT ASCII('A'); |
| CHAR |
Returns the character based on the ASCII code. |
CHAR(number) |
SELECT CHAR(65); |
| LEFT |
Returns the left part of a character string with the specified number of characters. |
LEFT(string, number_of_chars) |
SELECT LEFT('Hello World', 5); |
| RIGHT |
Returns the right part of a character string with the specified number of characters. |
RIGHT(string, number_of_chars) |
SELECT RIGHT('Hello World', 5); |
| LTRIM |
Removes spaces from the beginning of a string. |
LTRIM(string) |
SELECT LTRIM(' Hello World'); |
| RTRIM |
Removes spaces from the end of a string. |
RTRIM(string) |
SELECT RTRIM('Hello World '); |
| Date and time commands |
| GETDATE() |
Returns the current database system timestamp |
GETDATE() |
SELECT GETDATE(); |
| SYSDATETIME() |
Returns the current system date and time |
SYSDATETIME() |
SELECT SYSDATETIME(); |
| CURRENT_TIMESTAMP |
Returns the current date and time of the database server |
CURRENT_TIMESTAMP |
SELECT CURRENT_TIMESTAMP; |
| DATEADD() |
Adds an interval to a specified date |
DATEADD(interval, number, date) |
SELECT DATEADD(day, 30, '2024-01-01'); |
| DATEDIFF() |
Returns the count of the specified datepart between two dates |
DATEDIFF(interval, startdate, enddate) |
SELECT DATEDIFF(day, '2024-01-01', '2024-02-01'); |
| DATEPART() |
Returns a single part of a date/time |
DATEPART(interval, date) |
SELECT DATEPART(year, '2024-01-01'); |
| CONVERT() |
Converts an expression of one data type to another |
CONVERT(data_type(length), expression, style) |
SELECT CONVERT(VARCHAR, GETDATE(), 102); |
| CAST() |
Converts one built-in data type into another |
CAST(expression AS data_type(length)) |
SELECT CAST('2024-01-01' AS DATETIME); |
| FORMAT() |
Formats how a field is to be displayed |
FORMAT(value, format) |
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd'); |
| DATEDIFF_BIG() |
Returns the big integer count of the specified datepart between two dates |
DATEDIFF_BIG(interval, startdate, enddate) |
SELECT DATEDIFF_BIG(ms, '2024-01-01', '2024-02-01'); |
| Conditional expressions |
| BETWEEN |
Checks if a value is within a range of values |
value BETWEEN low AND high |
age BETWEEN 18 AND 65 |
| LIKE |
Searches for a specified pattern in a column |
column LIKE pattern |
name LIKE 'J%' |
| IN |
Checks if a value is within a set of specified values |
value IN (value1, value2, ...) |
country IN ('USA', 'Canada', 'Mexico') |
| IS NULL |
Checks for NULL values |
column IS NULL |
phone IS NULL |
| IS NOT NULL |
Checks for non-NULL values |
column IS NOT NULL |
email IS NOT NULL |
| AND |
Combines two or more conditions and returns true if all are true |
condition1 AND condition2 |
age > 18 AND gender = 'F' |
| OR |
Combines two or more conditions and returns true if any are true |
condition1 OR condition2 |
status = 'New' OR status = 'Pending' |
| NOT |
Reverses the result of a condition |
NOT condition |
NOT age < 18 |
| > |
Greater than |
column > value |
price > 100 |
| < |
Less than |
column < value |
quantity < 20 |
| >= |
Greater than or equal to |
column >= value |
discount >= 5 |
| <= |
Less than or equal to |
column <= value |
stock <= 100 |
| = |
Equal to |
column = value |
type = 'Electronics' |
| <> or != |
Not equal to |
column <> value |
region <> 'West' |
| CASE |
Allows for conditional logic in SQL statements |
CASE WHEN condition THEN result [ELSE result] END |
CASE WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END |
| IF() |
Function that returns one value if a condition is true, and another if false (Note: This is MySQL specific) |
IF(condition, value_if_true, value_if_false) |
IF(age >= 18, 'Adult', 'Minor') |
| COALESCE() |
Returns the first non-NULL value in a list |
COALESCE(value1, value2, ...) |
COALESCE(phone, email, 'N/A') |
| NULLIF() |
Returns NULL if two values are equal, otherwise returns the first value |
NULLIF(value1, value2) |
NULLIF(division, 'N/A') |
| Set operations |
| UNION |
Combines the results of two SELECT queries and removes duplicate rows. |
SELECT column1 FROM table1 UNION SELECT column1 FROM table2; |
SELECT city FROM customers UNION SELECT city FROM suppliers; |
| UNION ALL |
Combines the results of two SELECT queries and includes duplicate rows. |
SELECT column1 FROM table1 UNION ALL SELECT column1 FROM table2; |
SELECT city FROM customers UNION ALL SELECT city FROM suppliers; |
| INTERSECT |
Returns the common rows between two SELECT queries. |
SELECT column1 FROM table1 INTERSECT SELECT column1 FROM table2; |
SELECT city FROM customers INTERSECT SELECT city FROM suppliers; |
| EXCEPT |
Returns the rows from the first SELECT query that are not in the second. |
SELECT column1 FROM table1 EXCEPT SELECT column1 FROM table2; |
SELECT city FROM customers EXCEPT SELECT city FROM suppliers; |
| Transaction Control Language (TCL) commands |
| BEGIN |
Starts a transaction. |
BEGIN TRANSACTION; |
BEGIN TRANSACTION; |
| BEGIN TRANSACTION |
Starts a transaction. |
BEGIN TRANSACTION; |
BEGIN TRANSACTION; |
| COMMIT |
Saves the changes made in the transaction. |
COMMIT; |
COMMIT; |
| ROLLBACK |
Reverts changes since the beginning of the transaction. |
ROLLBACK; |
ROLLBACK; |
| SAVEPOINT |
Sets a savepoint within a transaction. |
SAVEPOINT savepoint_name; |
SAVEPOINT sp1; |
| RELEASE |
Removes a savepoint previously set. |
RELEASE SAVEPOINT savepoint_name; |
RELEASE SAVEPOINT sp1; |
| ROLLBACK TO SAVEPOINT |
Rolls back to a set savepoint without ending the transaction. |
ROLLBACK TO SAVEPOINT savepoint_name; |
ROLLBACK TO SAVEPOINT sp1; |
| RELEASE SAVEPOINT |
Removes a savepoint previously set. |
RELEASE SAVEPOINT savepoint_name; |
RELEASE SAVEPOINT sp1; |
| SET TRANSACTION |
Establishes the properties for the next transaction. |
SET TRANSACTION [properties]; |
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
