intro
In today's modern world, data has become more important than ever, making SQL the most widely used tool for managing and analysing this data. However, mastering all the SQL commands and queries can be daunting for beginners and even experienced developers. Therefore, I have created this SQL commands cheat sheet that provides the commonly used SQL statements for your reference.
Download the PDF Cheat Sheet here.
Here is what you will learn in this guide:
What is SQL?
SQL, a short form of Structured Query Language, is a programming language for managing and manipulating data in a database.
Data analysts, developers, and database administrators use SQL to store, retrieve, manage, and manipulate data within a database.
Data Manipulation Language (DML) Commands
Data Manipulation Language (DML) commands are SQL statements used for querying, inserting, updating, and deleting data from database tables.
Here are the main DML commands:
1. SELECT
The SELECT command retrieves data from a database.
Syntax:
1
SELECT column1, column2 FROM table_name;
In the syntax, column1 and column2, are the field names of the table you want to select data from. The table_name represents the name of the table you want to select data from.
2. INSERT
The INSERT command adds new records to a table.
Syntax:
1
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
Example:
1
INSERT INTO customers (first_name, last_name) VALUES ('Mary', 'Doe');
In the query above, value Mary is inserted into the first_name column and value Doe is inserted into the last_name column of a table called customers.
3. UPDATE
The UPDATE command is used to modify existing records in a table.
Syntax:
1
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
Example:
1
UPDATE employees SET employee_name = ‘John Doe’, department = ‘Marketing’;
In the query above, we're updating the employee_name column to John Doe and the department column to Marketing in a table called employees.
4. DELETE
The DELETE command removes records from a table.
Syntax:
1
DELETE FROM table_name WHERE condition;
Example:
1
DELETE FROM employees WHERE employee_name = ‘John Doe’;
In this example, we're deleting rows from the employees table where the employee_name is John Doe.
Data Definition Language (DDL) Commands
Data Definition Language (DDL) commands are SQL statements used to create, alter, and drop database objects like tables, indexes, views, and schemas.
Here are the main DDL commands:
1. CREATE
The CREATE command is used to create a new database and database objects, such as a table, index, view, or a stored procedure.
Syntax:
1
CREATE TABLE table_name (column1 datatype1, column2 datatype2, ...);
Example:
1
CREATE TABLE employees (
2
employee_id INT PRIMARY KEY,
3
first_name VARCHAR(50),
4
last_name VARCHAR(50),
5
age INT
6
);
The query above creates a table named employees that contains four columns which are employee_id, first_name, last_name, and age.
2. ALTER
The ALTER command is used to add, delete, or modify columns in an existing table.
Syntax:
1
ALTER TABLE table_name ADD column_name datatype;
Example:
1
ALTER TABLE customers ADD email VARCHAR(100);
The query above adds a new column named email with a datatype of VARCHAR(100) to an existing table named customers.
3. DROP
The DROP command is used to drop an existing table in a database.
Syntax:
1
DROP TABLE table_name;
Example:
1
DROP TABLE customers;
The query above drops a table named customers.
4. TRUNCATE
The TRUNCATE command is used to delete the data inside a table, but not the table itself.
Syntax:
1
TRUNCATE TABLE table_name;
Example:
1
TRUNCATE TABLE customers;
The query above truncates a table named customers, which removes all records from the table.
Data Control Language (DCL) Commands
Data Control Language (DCL) commands are SQL statements used to grant or revoke permissions and privileges to users and roles to perform specific actions on the database.
Here are the main DCL commands:
1. GRANT
The GRANT command is used to give specific privileges to users or roles.
Syntax:
1
GRANT SELECT, INSERT ON table_name TO user_name;
Example:
1
GRANT SELECT, INSERT ON employees TO ‘John Doe’;
The query above grants SELECT and INSERT privileges to the employees table to a user named John Doe.
2. REVOKE
The REVOKE command is used to take away privileges previously granted to users or roles.
Syntax:
1
REVOKE SELECT, INSERT ON table_name FROM user_name;
Example:
1
REVOKE SELECT, INSERT ON employees FROM ‘John Doe’;
The query above revokes SELECT and INSERT privileges to the employees table granted to user or role John Doe
Querying Data Commands
Querying data commands are SQL statements used to retrieve data from a database.
Here are the main Querying Data commands:
1. SELECT Statement
The SELECT statement is the primary command used to retrieve data from a database. It allows you to specify which columns you want to retrieve and from which table.
1
SELECT column1, column2, FROM table_name;
2. WHERE Clause
The WHERE clause is used to filter rows based on a specified condition.
Syntax:
1
SELECT * FROM table_name WHERE condition;
Example:
1
SELECT * FROM customers WHERE age > 30;
The query above selects all columns from a table named customers where the age is greater than 30.
3. ORDER BY Clause
The ORDER BY clause is used to sort the result set in ascending or descending order based on a specified column.
Syntax:
1
SELECT * FROM table_name ORDER BY column_name ASC|DESC;
Example:
1
SELECT * FROM products ORDER BY price DESC;
The query above selects all columns from a table named products and orders the results based on the price column in descending order (highest to lowest price).
4. GROUP BY Clause
The GROUP BY clause is used to group rows based on the values in a specified column. It is often used with aggregate functions like COUNT, SUM, AVG, etc.
Syntax:
1
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
Example:
1
SELECT category, COUNT(*) FROM products GROUP BY category;
The query above selects the category column and the count of each distinct category from a table named products. The results will provide the count of items in each category.
5. HAVING Clause
The HAVING clause is used to filter grouped results based on a specified condition.
Syntax:
1
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING condition;
Example:
1
SELECT category, COUNT(*) FROM products GROUP BY category HAVING COUNT(*) > 5;
The query above selects the category column and the count of each distinct category from a table named products, but only for categories where the count is greater than 5.
Joining Commands
Joining commands are SQL statements used to combine data from two or more tables based on related columns to create a single result set.
Here are the main types of join commands:
1. INNER JOIN
The INNER JOIN command returns rows with matching values in both tables.
Syntax:
1
SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;
Example:
1
SELECT * FROM employees INNER JOIN departments ON employees.department_id = departments.id;
The query above selects all columns from tables employees and departments, and joins them based on the department_id in employees and id in departments. This will retrieve records where the department IDs match in both tables.
2. LEFT JOIN/LEFT OUTER JOIN
The LEFT JOIN command returns all rows from the left table (first table) and the matching rows from the right table (second table).
Syntax:
1
SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
Example:
1
SELECT * FROM employees LEFT JOIN departments ON employees.department_id = departments.id;
The query above selects all columns from the employees table and the departments table and performs a LEFT JOIN based on the department_id in employees and id in departments. This will retrieve all employee records and their corresponding department information.
3. RIGHT JOIN/RIGHT OUTER JOIN
The RIGHT JOIN command returns all rows from the right table (second table) and the matching rows from the left table (first table).
Syntax:
1
SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
Example:
1
SELECT *
2
FROM employees
3
RIGHT JOIN departments
4
ON employees.department_id = departments.department_id;
The query above uses a RIGHT JOIN to retrieve all records from the departments table and the matching records from the employees table based on the department_id.
4. FULL JOIN/FULL OUTER JOIN
The FULL JOIN command returns all rows when there is a match in either the left table or the right table.
Syntax:
1
SELECT * FROM table1 FULL JOIN table2 ON table1.column = table2.column;
Example:
1
SELECT *
2
FROM employees
3
LEFT JOIN departments ON employees.employee_id = departments.employee_id
4
UNION
5
SELECT *
6
FROM employees
7
RIGHT JOIN departments ON employees.employee_id = departments.employee_id;
The query above first performs a LEFT JOIN on employees and departments, then it performs a RIGHT JOIN on the same tables. Finally, it uses UNION to combine the results from both joins, effectively simulating a FULL JOIN.
5. CROSS JOIN
The CROSS JOIN command combines every row from the first table with every row from the second table, creating a Cartesian product.
Syntax:
1
SELECT * FROM table1 CROSS JOIN table2;
Example:
1
SELECT *
2
FROM employees
3
CROSS JOIN departments;
The query above performs a CROSS JOIN between the employees and departments tables.
6. SELF JOIN
The SELF JOIN command joins a table with itself.
Syntax:
1
SELECT * FROM table1 t1, table1 t2 WHERE t1.column = t2.column;
Example:
1
SELECT *
2
FROM employees t1, employees t2
3
WHERE t1.employee_id = t2.employee_id;
The query above uses a self-join on the employees table by aliasing it as "t1" and "t2". It is comparing the employee_id from one instance of the table (t1) to the employee_id from another instance of the table (t2). This will retrieve rows where the employee_id is the same in both instances.
7. NATURAL JOIN
The NATURAL JOIN command matches columns with the same name in both tables.
Syntax:
1
SELECT * FROM table1 NATURAL JOIN table2;
Example:
1
SELECT *
2
FROM employees
3
NATURAL JOIN departments;
The query above performs a NATURAL JOIN between the employees and departments tables.
Subqueries in SQL
Subqueries in SQL are queries that are embedded within another query, allowing for more complex and dynamic queries. Subqueries can be used within various SQL commands, including the IN, ANY, and ALL commands, to perform comparisons or operations based on the results of the subquery.
1. IN Command
The IN command is used to determine whether a value matches any value in a subquery result. It is often used in the WHERE clause.
Syntax:
1
SELECT column(s) FROM table WHERE value IN (subquery);
Example:
1
SELECT * FROM customers WHERE city IN (SELECT city FROM suppliers);
The query above selects all customers whose city matches any city in the subquery result from the suppliers' table.
2. ANY Command
The ANY command is used to compare a value to any value returned by a subquery. It can be used with comparison operators like =, >, <, etc.
Syntax:
1
SELECT column(s) FROM table WHERE value < ANY (subquery);
Example:
1
SELECT * FROM products WHERE price < ANY (SELECT unit_price FROM supplier_products);
The query above selects products with a price less than any of the unit prices obtained from the subquery.
3. ALL Command
The ALL command is used to compare a value to all values returned by a subquery. It can be used with comparison operators like =, >, <, etc.
Syntax:
1
SELECT column(s) FROM table WHERE value > ALL (subquery);
Example:
1
SELECT * FROM orders WHERE order_amount > ALL (SELECT total_amount FROM previous_orders);
The query above selects orders with order amounts greater than all the total amounts obtained from the subquery.
Aggregate Functions Commands
Aggregate functions commands are SQL statements used to perform calculations on a set of values and return a single value as a result.
Here are some common aggregate functions in SQL:
1. COUNT()
The COUNT command counts the number of rows or non-null values in a specified column.
Syntax:
1
SELECT COUNT(column_name) FROM table_name;
Example:
1
SELECT COUNT(age) FROM employees;
The query above uses the COUNT function to count the non-null values in the age column of the employees table. This will give you the count of records where the age is not null.
2. SUM()
The SUM command is used to calculate the sum of all values in a specified column.
Syntax:
1
SELECT SUM(column_name) FROM table_name;
Example:
1
SELECT SUM(revenue) FROM sales;
The query above uses the SUM function to calculate the total value of the revenue column in the sales table. This will give you the sum of all values in the revenue column.
3. AVG()
The AVG command is used to calculate the average (mean) of all values in a specified column.
Syntax:
1
SELECT AVG(column_name) FROM table_name;
Example:
1
SELECT AVG(price) FROM products;
The query above uses the AVG function to calculate the average value of the price column in the products table. This will give you the average price of the products.
4. MIN()
The MIN command returns the minimum (lowest) value in a specified column.
Syntax:
1
SELECT MIN(column_name) FROM table_name;
Example:
1
SELECT MIN(price) FROM products;
The query above uses the MIN function to find the minimum value in the price column of the products table. This will give you the minimum price of all the products.
5. MAX()
The MAX command returns the maximum (highest) value in a specified column.
Syntax:
1
SELECT MAX(column_name) FROM table_name;
Example:
1
SELECT MAX(price) FROM products;
The query above uses the MAX function to find the maximum value in the price column of the products table. This will give you the maximum price among all the products.
String Functions in SQL
String functions in SQL are used to manipulate and perform operations on string values (character data). These functions can help with tasks such as extracting substrings, converting case, concatenating strings, and more.
Here are some commonly used string functions in SQL:
1. CONCAT()
The CONCAT command concatenates two or more strings into a single string.
Syntax:
1
SELECT CONCAT(string1, string2, ...) AS concatenated_string FROM table_name;
Example:
1
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
The query above uses the CONCAT function to concatenate the first_name and last_name columns from the employees table, separated by a space. The result is returned as a new column named full_name.
2. SUBSTRING()/SUBSTR()
The SUBSTRING command extracts a substring from a string.
Syntax:
1
SELECT SUBSTRING(string FROM start_position [FOR length]) AS substring FROM table_name;
Example:
1
SELECT SUBSTRING(product_name FROM 1 FOR 5) AS substring FROM products;
The query above uses the SUBSTRING function to extract a substring starting from position 1 (the beginning of the string) with a length of 5 characters from the product_name column in the products table. The result is returned as a new column named substring.
3. CHAR_LENGTH()/LENGTH()
The LENGTH command returns the length (number of characters) of a string.
Syntax:
1
SELECT CHAR_LENGTH(string) AS length FROM table_name;
Example:
1
SELECT CHAR_LENGTH(product_name) AS length FROM products;
The query above uses the CHAR_LENGTH function to calculate the length of the product_name column in the products table. The result is returned as a new column named length.
4. UPPER()
The UPPER command converts all characters in a string to uppercase.
Syntax:
1
SELECT UPPER(string) AS uppercase_string FROM table_name;
Example:
1
SELECT UPPER(first_name) AS uppercase_first_name FROM employees;
The query above uses the UPPER function to convert the first_name column values to uppercase in the employees table. The result is returned as a new column named uppercase_first_name.
5. LOWER()
The LOWER command converts all characters in a string to lowercase.
Syntax:
1
SELECT LOWER(string) AS lowercase_string FROM table_name;
Example:
1
SELECT LOWER(last_name) AS lowercase_last_name FROM employees;
The query above uses the LOWER function to convert the last_name column values to lowercase in the employees table. The result is returned as a new column named lowercase_last_name.
6. TRIM()
The TRIM command removes specified prefixes or suffixes (or whitespace by default) from a string.
Syntax:
1
SELECT TRIM([LEADING | TRAILING | BOTH] characters FROM string) AS trimmed_string FROM table_name;
Example:
1
SELECT TRIM(TRAILING ' ' FROM full_name) AS trimmed_full_name FROM customers;
The query above uses the TRIM function to remove trailing spaces from the full_name column values in the customers table. The result is returned as a new column named trimmed_full_name. The TRAILING option is used to specify that we want to remove trailing spaces.
7. LEFT()
The LEFT command returns a specified number of characters from the left of a string.
Syntax:
1
SELECT LEFT(string, num_characters) AS left_string FROM table_name;
Example:
1
SELECT LEFT(product_name, 5) AS left_product_name FROM products;
The query above uses the LEFT function to extract the first 5 characters from the product_name column values in the products table. The result is returned as a new column named left_product_name.
8. RIGHT()
The RIGHT command returns a specified number of characters from the right of a string.
Syntax:
1
SELECT RIGHT(string, num_characters) AS right_string FROM table_name;
Example:
1
SELECT RIGHT(order_number, 4) AS right_order_number FROM orders;
The query above uses the RIGHT function to extract the last 4 characters from the order_number column values in the orders table. The result is returned as a new column named right_order_number.
9. REPLACE()
The REPLACE command replaces occurrences of a substring within a string.
Syntax:
1
SELECT REPLACE(string, old_substring, new_substring) AS replaced_string FROM table_name;
Example:
1
SELECT REPLACE(description, 'old_string', 'new_string') AS replaced_description FROM product_descriptions;
The query above uses the REPLACE function to replace occurrences of old_string with new_string in the description column values in the product_descriptions table. The result is returned as a new column named replaced_description. Replace old_string and new_string with the actual strings you want to replace and replace them with.
Date and Time SQL Commands
Date and time functions in SQL are used to manipulate and perform operations on date and time values.
Here are some commonly used date and time functions in SQL:
1. CURRENT_DATE()
The CURRENT_DATE command returns the current date.
Syntax:
1
SELECT CURRENT_DATE() AS current_date;
2. CURRENT_TIME()
The CURRENT_TIME command returns the current time.
Syntax:
1
SELECT CURRENT_TIME() AS current_time;
3. CURRENT_TIMESTAMP()
The CURRENT_TIMESTAMP command returns the current date and time.
Syntax:
1
SELECT CURRENT_TIMESTAMP() AS current_timestamp;
4. DATE_PART()
The DATE_PART command extracts a specific part (e.g., year, month, day) from a date or time.
Syntax:
1
SELECT DATE_PART('part', date_expression) AS extracted_part;
5. DATE_ADD()/DATE_SUB()
The DATE_ADD command adds or subtracts a specified number of days, months, or years to/from a date.
Syntax:
1
SELECT DATE_ADD(date_expression, INTERVAL value unit) AS new_date;
1
SELECT DATE_SUB(date_expression, INTERVAL value unit) AS new_date;
6. EXTRACT()
The EXTRACT command extracts a specific part (e.g., year, month, day) from a date or time.
Syntax:
1
SELECT EXTRACT(part FROM date_expression) AS extracted_part;
7. TO_CHAR()
The TO_CHAR command converts a date or time to a specified format.
Syntax:
1
SELECT TO_CHAR(date_expression, 'format') AS formatted_date;
8. TIMESTAMPDIFF()
The TIMESTAMPDIFF command calculates the difference between two timestamps in a specified unit (e.g., days, hours, minutes).
Syntax:
1
SELECT TIMESTAMPDIFF(unit, timestamp1, timestamp2) AS difference;
9. DATEDIFF()
The DATEDIFF command calculates the difference in days between two dates.
Syntax:
1
SELECT DATEDIFF(date1, date2) AS difference_in_days;
Conditional Expressions
Conditional expressions in SQL allow for decision-making within queries, enabling you to retrieve data based on specific conditions. Commonly used conditional expressions include CASE, IF, COALESCE, and NULLIF.
1. CASE Statement
The CASE statement allows you to perform conditional logic within a query.
Syntax:
1
SELECT
2
column1,
3
column2,
4
CASE
5
WHEN condition1 THEN result1
6
WHEN condition2 THEN result2
7
ELSE default_result
8
END AS alias
9
FROM table_name;
Example:
1
SELECT
2
order_id,
3
total_amount,
4
CASE
5
WHEN total_amount > 1000 THEN 'High Value Order'
6
WHEN total_amount > 500 THEN 'Medium Value Order'
7
ELSE 'Low Value Order'
8
END AS order_status
9
FROM orders;
The query above uses a CASE statement to evaluate conditions based on the total_amount and create an order_status accordingly for each order. The result is returned with the order_id, total_amount, and the calculated order_status.
2. IF() Function
The IF() function evaluates a condition and returns a value based on the evaluation.
Syntax:
1
SELECT IF(condition, true_value, false_value) AS alias FROM table_name;
Example:
1
SELECT
2
name,
3
age,
4
IF(age > 50, 'Senior', 'Junior') AS employee_category
5
FROM employees;
The query above uses the IF function to categorize employees based on their age. If the age is greater than 50, they are categorized as Senior; otherwise, they are categorized as Junior. The result is returned with the name, age, and the calculated employee_category.
3. COALESCE() Function
The COALESCE() function returns the first non-null value from a list of values.
Syntax:
1
SELECT COALESCE(value1, value2, ...) AS alias FROM table_name;
Example:
1
SELECT
2
COALESCE(first_name, middle_name) AS preferred_name
3
FROM employees;
The query above uses the COALESCE function to select the first non-null name (either first_name or middle_name) as the preferred_name for each employee. The result is returned with the calculated preferred_name for each employee.
4. NULLIF() Function
The NULLIF() function returns null if two specified expressions are equal.
Syntax:
1
SELECT NULLIF(expression1, expression2) AS alias FROM table_name;
Example:
1
SELECT NULLIF(total_amount, discounted_amount) AS diff_amount FROM orders;
The query above uses the NULLIF function to check if total_amount is equal to discounted_amount. If they are equal, it returns NULL; otherwise, it returns total_amount. The result is returned with the diff_amount indicating the difference (or NULL) between the total amount and the discounted amount for each order.
Set Operations
Set operations in SQL allow you to perform operations on multiple sets of data, such as combining sets (UNION), finding the intersection of sets (INTERSECT), and finding the difference between sets (EXCEPT).
Here are the main set operation commands with examples:
1. UNION
The UNION operator combines the result sets of two or more SELECT statements into a single result set.
Syntax:
1
SELECT column1, column2 FROM table1
2
UNION
3
SELECT column1, column2 FROM table2;
Example:
1
SELECT first_name, last_name FROM customers
2
UNION
3
SELECT first_name, last_name FROM employees;
The query above uses the UNION operator to combine the first_name and last_name columns from both the customers and employees tables. The result will include unique combinations of first_name and last_name from both tables.
2. INTERSECT
The INTERSECT operator returns the common rows that appear in both result sets.
Syntax:
1
SELECT column1, column2 FROM table1
2
INTERSECT
3
SELECT column1, column2 FROM table2;
Example:
1
SELECT first_name, last_name FROM customers
2
INTERSECT
3
SELECT first_name, last_name FROM employees;
The query above uses the INTERSECT operator to find the common first_name and last_name between the customers and employees tables. The result will include rows where both the first name and last name are present in both tables.
3. EXCEPT
The EXCEPT operator returns the distinct rows from the left result set that are not present in the right result set.
Syntax:
1
SELECT column1, column2 FROM table1
2
EXCEPT
3
SELECT column1, column2 FROM table2;
Example:
1
SELECT first_name, last_name FROM customers
2
EXCEPT
3
SELECT first_name, last_name FROM employees;
The query above uses the EXCEPT operator to find the first_name and last_name in the customers table that are not present in the employees table. The result will include rows where the first name and last name are in the customers table but not in the employees table.
Transaction Control Commands
Transaction Control Language (TCL) commands in SQL are used to manage transactions within a database.
Here are the main TCL commands:
1. COMMIT
The COMMIT command is used to save all the changes made during the current transaction and make them permanent.
Syntax:
1
COMMIT;
Example:
1
BEGIN TRANSACTION;
2
3
-- SQL statements and changes within the transaction
4
5
INSERT INTO employees (name, age) VALUES ('Alice', 30);
6
UPDATE products SET price = 25.00 WHERE category = 'Electronics';
7
8
COMMIT;
In the query above, the COMMIT statement is used to permanently save the changes made to the employees and products tables during the transaction.
2. ROLLBACK
The ROLLBACK command is used to undo all the changes made during the current transaction and discard them.
Syntax:
1
ROLLBACK;
Example:
1
BEGIN TRANSACTION;
2
3
-- SQL statements and changes within the transaction
4
5
INSERT INTO employees (name, age) VALUES ('Bob', 35);
6
UPDATE products SET price = 30.00 WHERE category = 'Electronics';
7
8
ROLLBACK;
In the query above, the ROLLBACK statement is used to undo the changes made during the transaction, so the insertion of 'Bob' into the employees table and the update of prices in the products table are rolled back, and the database is restored to its state before the transaction started.
3. SAVEPOINT
The SAVEPOINT command is used to set a point within a transaction to which you can later roll back.
Syntax:
1
SAVEPOINT savepoint_name;
Example:
1
BEGIN TRANSACTION;
2
3
INSERT INTO employees (name, age) VALUES ('Carol', 28);
4
5
SAVEPOINT before_update;
6
7
UPDATE products SET price = 40.00 WHERE category = 'Electronics';
8
9
SAVEPOINT after_update;
10
11
DELETE FROM customers WHERE age > 60;
12
13
ROLLBACK TO before_update;
14
15
-- At this point, the DELETE is rolled back, but the UPDATE remains.
16
17
COMMIT;
The query above sets two savepoints: before_update and after_update. It later rollback to before_update, effectively undoing the DELETE operation, but the UPDATE operation remains in effect when the transaction is committed.
4. ROLLBACK TO SAVEPOINT
The ROLLBACK TO SAVEPOINT command is used to roll back to a specific savepoint within a transaction.
Syntax:
1
ROLLBACK TO SAVEPOINT savepoint_name;
Example:
1
BEGIN TRANSACTION;
2
3
INSERT INTO employees (name, age) VALUES ('David', 42);
4
5
SAVEPOINT before_update;
6
7
UPDATE products SET price = 50.00 WHERE category = 'Electronics';
8
9
SAVEPOINT after_update;
10
11
DELETE FROM customers WHERE age > 60;
12
13
-- Rollback to the savepoint before the update
14
ROLLBACK TO SAVEPOINT before_update;
15
16
-- At this point, the UPDATE is rolled back, but the INSERT remains.
17
18
COMMIT;
The query above sest two savepoints: before_update and after_update. It then rollback to before_update, undoing the UPDATE operation, but leaving the INSERT operation intact. Finally, the transaction is committed, preserving the INSERT operation.
5. SET TRANSACTION
The SET TRANSACTION command is used to configure properties for the current transaction, such as isolation level and transaction mode.
Syntax:
1
SET TRANSACTION [ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE }]
Example:
1
BEGIN TRANSACTION;
2
3
-- Set the isolation level to READ COMMITTED
4
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
5
6
-- SQL statements and changes within the transaction
7
8
INSERT INTO employees (name, age) VALUES ('Emily', 35);
9
UPDATE products SET price = 60.00 WHERE category = 'Electronics';
10
11
COMMIT;
The query above sets the isolation level to READ COMMITTED for the transaction. The changes made in the transaction will be visible to other transactions only after they are committed. The SERIALIZABLE isolation level would ensure the highest level of isolation, making sure that no other transactions can access the data being modified by this transaction until it's committed.
Conclusion
In conclusion, I have covered the commonly used SQL statements such as Data Manipulation Language (DML) Commands, Data Definition Language (DDL) Commands, Data Control Language (DCL) Commands and more.
Whether you're a beginner learning SQL or an experienced developer looking to brush up your SQL skills, this SQL commands cheat sheet is the perfect companion for you.
Feel free to try some of the SQL queries provided using DbVisualizer. We hope that you’ve enjoyed this blog and that you will stick around for more content - have a read through other blogs on our website, and we’ll see you in the next one.
