S Q L cheat sheet

Comprehensive

Data Manipulation Language DML Commands

Command Description Syntax Example

SELECT The S E L E C T command retrieves SELECT column1, column2 SELECT first_name,

data from a database. FROM table_name; last_name FROM customers;

INSERT The INSERT command adds new INSERT INTO table_name INSERT INTO customers
records to a table. (column1, column2) (first_name,
VALUES (value1, value2); last_name) VALUES
('Mary', 'Doe');
UPDATE The UPDATE command is used UPDATE table_name SET
to modify existing records in a column1 = value1, column2 = UPDATE employees SET
table. value2 WHERE condition; employee_name = ‘John
Doe’, department =
‘Marketing’;
DELETE The D E L E T E command removes DELETE FROM table_name
records from a table. WHERE condition;
DELETE FROM employees
WHERE employee_name =
‘John Doe’;

Data Definition Language DDL Commands

Command Description Syntax Example

CREATE The CREATE command creates a CREATE TABLE table_name CREATE TABLE employees
new database and objects, such (column1 datatype1, ( employee_id INT
as a table, index, view, or stored column2 datatype2, PRIMARY KEY,
procedure. ); first_name
VARCHAR(50),
last_name
VARCHAR(50),
age INT
);

ALTER The ALTER command adds, ALTER TABLE customers

deletes, or modifies columns in ALTER TABLE table_name ADD email VARCHAR(100);
an existing table. ADD column_name
datatype;
DROP The DROP command is used to DROP TABLE customers;
drop an existing table in a
database. DROP TABLE table_name;

TRUNCATE The TRUNCATE command is TRUNCATE TABLE customers;

used to delete the data inside a
table, but not the table itself. TRUNCATE
TABLE
table_name;
The S Q L tool with the highest user satisfaction. DBVIS . CO M
Data Control Language D C L Commands

Command Description Syntax Example

GRANT The GRANT command is used to GRANT SELECT, INSERT ON GRANT SELECT, INSERT ON
give specific privileges to users table_name TO employees TO ‘John
or roles. user_name; Doe’;

REVOKE The REVOKE command is used

to take away privileges REVOKE SELECT, INSERT REVOKE SELECT, INSERT
previously granted to users or ON table_name FROM ON employees FROM ‘John
roles. user_name; Doe’;

Querying Data Commands

Command Description Syntax Example

SELECT Statemen The S E L E C T statement is the SELECT column1, SELECT first_name,

t primary command used to column2 FROM last_name FROM
retrieve data from a database table_name; customers;

The WHERE clause is used to

WHERE Clause filter rows based on a specified SELECT * FROM SELECT * FROM
condition. table_name WHERE customers WHERE age >
condition; 30;
The ORDER BY clause is used to
ORDER BY Clause sort the result set in ascending
or descending order based on a SELECT * FROM SELECT * FROM
specified column. table_name ORDER BY products ORDER BY
column_name ASC|DESC; price DESC;
The GROUP BY clause groups
GROUP BY Clause rows based on the values in a
specified column. It is often SELECT column_name,
used with aggregate functions COUNT(*) FROM SELECT category,
like COUNT, SUM, AVG, etc. table_name GROUP BY COUNT(*) FROM products
column_name; GROUP BY category;
The HAVING clause filters
HAVING Clause grouped results based on a
specified condition.
SELECT column_name, SELECT category,
COUNT(*) FROM COUNT(*) FROM products
table_name GROUP BY GROUP BY category HAVING
column_name HAVING COUNT(*) > 5;
condition;
The S Q L tool with the highest user satisfaction. DBVIS . CO M
Joining Commands

Command Description Syntax Example

INNER JOIN The INNER JOIN command SELECT * FROM SELECT * FROM employees
returns rows with matching table1 INNER JOIN INNER JOIN departments
values in both tables. table2 ON ON
table1.column = employees.department_id
table2.column; = departments.id;

LEFT JOIN/LEFT OUTER The L E F T JOIN command

JOIN returns all rows from the left SELECT * FROM table1 SELECT * FROM employees
table (first table) and the LEFT JOIN table2 ON LEFT JOIN departments ON
matching rows from the right table1.column = employees.department_id =
table (second table). table2.column; departments.id;

The RIGHT JOIN command

RIGHT JOIN/RIGHT returns all rows from the right SELECT * FROM SELECT *
OUTER JOIN table (second table) and the table1 RIGHT JOIN FROM employees
matching rows from the left table2 ON RIGHT JOIN departments
table (first table). table1.column = ON employees.department_id
table2.column; =
departments.department_id;
The F U L L JOIN command
FULL JOIN/FULL OUTER returns all rows when there is a
JOIN match in either the left table or SELECT * FROM table1 SELECT *
the right table. FULL JOIN table2 ON FROM employees
table1.column = LEFT JOIN departments
table2.column; ON
employees.employee_id =
departments.employee_id
UNION
SELECT *
FROM employees
RIGHT JOIN departments
The C R O S S JOIN command ON employees.employee_id
combines every row from the =
CROSS JOIN first table with every row from SELECT * FROM departments.employee_id;
the second table, creating a table1 CROSS JOIN
Cartesian product. table2; SELECT * FROM
employees CROSS JOIN
The S E L F JOIN command joins departments;
a table with itself.
SELF JOIN
SELECT * FROM table1 t1,
table1 t2 WHERE
t1.column = t2.column; SELECT * FROM employees
The NATURAL JOIN command t1, employees t2
matches columns with the WHERE t1.employee_id
NATURAL JOIN same name in both tables. = t2.employee_id;
SELECT * FROM
table1 NATURAL JOIN
table2; SELECT * FROM employees
NATURAL JOIN
departments;
The S Q L tool with the highest user satisfaction. DBVIS . CO M
Subqueries in S Q L

Command Description Syntax Example

IN The IN command is used to SELECT column(s) SELECT * FROM

determine whether a value FROM table WHERE customers WHERE city
matches any value in a subquery value IN (subquery); IN (SELECT city FROM
result. It is often used in the suppliers);
WHERE clause.

ANY The ANY command is used to SELECT column(s) FROM

compare a value to any value table WHERE value < SELECT * FROM products
returned by a subquery. It can ANY (subquery) ; WHERE price < ANY
be used with comparison (SELECT unit_price FROM
operators like =, >, <, etc. supplier_products);

ALL The ALL command is used to SELECT column(s) FROM

compare a value to all values table WHERE value > SELECT * FROM orders
returned by a subquery. It can ALL (subquery); WHERE order_amount >
be used with comparison ALL (SELECT total_amount
operators like =, >, <, etc. FROM previous_orders);

Aggregate Functions Commands

Command Description Syntax Example

COUNT() The COUNT command counts SELECT SELECT COUNT(age)

the number of rows or non-null COUNT(column_name) FROM FROM employees;
values in a specified column. table_name;

SUM() The SUM command is used to SELECT SUM(revenue)

calculate the sum of all values in SELECT FROM sales;
a specified column. SUM(column_name) FROM
table_name;

AVG() The AVG command is used to SELECT AVG(price)

calculate the average (mean) of FROM products;
all values in a specified column. SELECT
AVG(column_name) FROM
table_name;
MIN() The MIN command returns the SELECT MIN(price)
minimum (lowest) value in a FROM products;
specified column.
SELECT
MAX() The MAX command returns the MIN(column_name) FROM SELECT MAX(price)
maximum (highest) value in a table_name; FROM products;
specified column.

SELECT
MAX(column_name) FROM
table_name;
The S Q L tool with the highest user satisfaction. DBVIS . CO M
String Functions in S Q L

Command Description Syntax Example

CONCAT() The CONCAT command SELECT CONCAT(string1, SELECT

concatenates two or more string2, ) AS CONCAT(first_name, ' ',
strings into a single string. concatenated_string last_name) AS full_name
FROM table_name; FROM employees;

SUBSTRING()/SUBSTR() The SUBSTRING command SELECT SUBSTRING(string

extracts a substring from a FROM start_position SELECT
string. [FOR length]) AS SUBSTRING(product_name
substring FROM FROM 1 FOR 5) AS
table_name; substring FROM
CHAR_LENGTH()/ The LENGTH command returns products;
LENGTH() the length (number of SELECT
characters) of a string. CHAR_LENGTH(string) AS SELECT
length FROM CHAR_LENGTH(product_name
The UPPER command converts table_name; ) AS length FROM
UPPER() all characters in a string to products;
uppercase. SELECT UPPER(string)
AS uppercase_string SELECT
FROM table_name; UPPER(first_name) AS
The LOWER command converts uppercase_first_name
LOWER() all characters in a string to FROM employees;
lowercase. SELECT LOWER(string)
AS lowercase_string
The TRIM command removes FROM table_name; SELECT
TRIM() specified prefixes or suffixes (or LOWER(last_name) AS
whitespace by default) from a SELECT TRIM([LEADING | lowercase_last_name
string. TRAILING | BOTH] FROM employees;
characters FROM
string) AS SELECT TRIM(TRAILING '
The L E F T command returns a trimmed_string FROM ' FROM full_name) AS
LEFT() specified number of characters table_name; trimmed_full_name FROM
from the left of a string. customers;
SELECT
LEFT(string,
num_characters) AS SELECT
left_string FROM LEFT(product_name,
The RIGHT command returns a
table_name;
RIGHT() specified number of characters 5) AS
from the right of a string. left_product_name
FROM products;
SELECT
The REPL ACE command RIGHT(string,
replaces occurrences of a num_characters) AS SELECT
REPLACE()
substring within a string. right_string FROM RIGHT(order_number, 4)
table_name; AS right_order_number
FROM orders;
SELECT
REPLACE(string, SELECT
old_substring, REPLACE(description,
new_substring) AS 'old_string',
replaced_string FROM 'new_string') AS
table_name; replaced_description
FROM
product_descriptions;
The S Q L tool with the highest user satisfaction. DBVIS . CO M
Date and Time S Q L Commands

Command Description Syntax Example

CURRENT_DATE() The CURRENT_DATE command SELECT CURRENT_DATE()

returns the current date. AS current_date;

CURRENT_TIME() The CURRENT_TIME command SELECT CURRENT_TIME()

returns the current time. AS current_time;

CURRENT_TIMESTAMP() The CURRENT_TIMESTAMP SELECT

command returns the current CURRENT_TIMESTAMP()
date and time. AS current_timestamp;

DATE_PART() The DATE_PART command SELECT SELECT

extracts a specific part (e.g., DATE_PART('part', DATE_PART('year',
year, month, day) from a date or date_expression) AS '2024-04-11') AS
time. extracted_part; extracted_part;

DATE_ADD()/ The DATE_ADD command adds

DATE_SUB() or subtracts a specified number SELECT DATE_ADD
of days, months, or years DATE_ADD(date_expression Example SELECT
to/from a date. , INTERVAL value unit) DATE_ADD('2024-04-
AS new_date; 11', INTERVAL 1 DAY)
AS new_date;

DATE_SUB
Example SELECT
DATE_SUB('2024-04-
11', INTERVAL 1 DAY)
AS new_date;

EXTRACT() The E XT R AC T command SELECT EXTRACT(part SELECT EXTRACT(YEAR

extracts a specific part (e.g., FROM date_expression) FROM '2024-04-11') AS
year, month, day) from a date or AS extracted_part; extracted_part;
time.

TO_CHAR() The TO_CHAR command SELECT SELECT

converts a date or time to a TO_CHAR(date_expression TO_CHAR('2024-04-
specified format. , 'format') AS 11', 'YYYY-MM-DD')
formatted_date; AS formatted_date;

TIMESTAMPDIFF( The TIMESTAMPDIFF command SELECT SELECT

) calculates the difference TIMESTAMPDIFF(unit, TIMESTAMPDIFF(DAY,
between two timestamps in a timestamp1, '2024-04-10',
specified unit (e.g., days, hours, timestamp2) AS '2024-04-11')
minutes). difference; AS difference;

The DATEDIFF command

DATEDIFF() calculates the difference in days SELECT SELECT
between two dates. DATEDIFF(date1, DATEDIFF('2024-04-
date2) AS 11', '2024-04-10') AS
difference_in_days; difference_in_days;
The S Q L tool with the highest user satisfaction. DBVIS . CO M
Conditional Expressions

Command Description Syntax Example

CASE Statement The C A S E statement allows you SELECT SELECT

to perform conditional logic column1, order_id,
within a query. column2, total_amount
CASE , CASE
WHEN WHEN
condition1 THEN total_amount > 1000 THEN
result1 'High Value Order'
WHEN WHEN
condition2 THEN total_amount > 500 THEN
result2 'Medium Value Order'
ELSE ELSE 'Low
default_resul Value Order'
t END AS
END AS alias order_status FROM
FROM orders;
IF() Function The IF function evaluates a table_name;
condition and returns a value SELECT
based on the evaluation. name,
age,
SELECT IF(condition, IF(age > 50,
true_value, false_value) 'Senior', 'Junior')
AS alias FROM AS employee_category
table_name; FROM employees;

COALESCE() The C OA L E S C E function

Function returns the first non-null value SELECT
from a list of values. COALESCE(first_name
,
middle_name)
SELECT AS
COALESCE(value1, preferred_name
The NULLIF function returns value2, ) AS alias FROM
NULLIF() Function null if two specified expressions FROM table_name; employees;
are equal.

SELECT
NULLIF(total_amount,
SELECT discounted_amount) AS
NULLIF(expression1, diff_amount FROM
expression2) AS orders;
alias FROM
table_name;
The S Q L tool with the highest user satisfaction. DBVIS . CO M
Set Operations

Command Description Syntax Example

UNION The UNION operator SELECT column1, column2 SELECT first_name,

combines the result sets of FROM table1 last_name FROM customers
two or more S E L E C T UNION UNION
statements into a single SELECT column1, column2 SELECT first_name,
result set.
FROM table2; last_name FROM employees;

INTERSECT The IN T E R SE CT operator SELECT column1, column2 SELECT first_name,

returns the common rows FROM table1 last_name FROM customers
that appear in both result INTERSECT INTERSECT
sets. SELECT column1, column2 SELECT first_name,
FROM table2; last_name FROM employees;

EXCEPT The E X C E P T operator SELECT column1, column2 SELECT first_name,

returns the distinct rows FROM table1 last_name FROM customers
from the left result set that EXCEPT EXCEPT
are not present in the right SELECT column1, column2 SELECT first_name,
result set. FROM table2; last_name FROM employees;

Transaction Control Commands

Command Description Syntax Example

COMMIT The COMMIT command is COMMIT BEGIN TRANSACTION;

used to save all the changes ;
made during the current SQL statements and changes within the
transaction and make them transaction
permanent.
INSERT INTO employees (name, age) VALUES
('Alice', 30);
UPDATE products SET price = 25.00 WHERE
category = 'Electronics';

COMMIT;

ROLLBACK The ROLLBACK command is BEGIN TRANSACTION;

ROLLBACK ; used to undo all the SQL statements and changes within the
changes made during the transaction
current transaction and
discard them.
INSERT INTO employees (name, age) VALUES ('Bob',
35); UPDATE products SET price = 30.00 WHERE
category = 'Electronics';

ROLLBACK;
The S Q L tool with the highest user satisfaction. DBVIS . CO M
SAVEPOINT The SAVEPOINT command SAVEPOINT BEGIN TRANSACTION;
is used to set a point within savepoint_n
a transaction to which you ame; INSERT INTO employees (name, age) VALUES
can later roll back. ('Carol', 28);

SAVEPOINT before_update;

UPDATE products SET price = 40.00 WHERE

category = 'Electronics';

SAVEPOINT after_update;

DELETE FROM customers WHERE age > 60;

ROLLBACK TO before_update;

At this point, the DELETE is rolled back, but

the UPDATE remains.

COMMIT;

ROLLBACK TO The ROLLBACK TO ROLLBACK TO BEGIN TRANSACTION;

SAVEPOINT SAVEPOINT command is SAVEPOINT
used to roll back to a savepoint_n INSERT INTO employees (name, age) VALUES
specific savepoint within a ame; ('David', 42);
transaction.
SAVEPOINT before_update;

UPDATE products SET price = 50.00 WHERE

category = 'Electronics';

SAVEPOINT after_update;

DELETE FROM customers WHERE age > 60;

Rollback to the savepoint before the

update ROLLBACK TO SAVEPOINT before_update;

At this point, the UPDATE is rolled back, but

the INSERT remains.

COMMIT;

SET TRANSACTION The S E T TRANSACTION SET BEGIN TRANSACTION;

command is used to TRANSACTION
configure properties for the [ISOLATION Set the isolation level to READ COMMITTED
current transaction, such as LEVEL { SET TRANSACTION ISOLATION LEVEL READ
isolation level and
READ COMMITTED;
transaction mode.
COMMITTED |
SERIALIZABL SQL statements and changes within the
E }] transaction

INSERT INTO employees (name, age) VALUES

('Emily', 35);
UPDATE products SET price = 60.00 WHERE
category = 'Electronics';

COMMIT;
The S Q L tool with the highest user satisfaction. DBVIS . CO M