SQL Commands Cheat Sheet

Introduction

SQL (Structured Query Language) is the standard language for interacting with relational databases. It is used for querying, updating, and managing data. Understanding SQL commands is essential for database administration and development. This cheat sheet provides a quick reference to some of the most commonly used SQL commands.

SQL Commands Cheat Sheet

Data Manipulation Language (DML) Commands

Command	Description	Syntax	Example
SELECT	Retrieves data from a database.	SELECT column1, column2 FROM table_name;	SELECT first_name, last_name FROM students;
INSERT	Adds new records to a table.	INSERT INTO table_name (column1, column2) VALUES (value1, value2);	INSERT INTO students (first_name, last_name) VALUES ('Mary', 'Doe');
UPDATE	Modifies existing records in a table.	UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;	UPDATE students SET first_name = 'John' WHERE last_name = 'Doe';
DELETE	Removes records from a table.	DELETE FROM table_name WHERE condition;	DELETE FROM students WHERE last_name = 'Doe';

Data Definition Language (DDL) Commands

Command	Description	Syntax	Example
CREATE	Creates a new database and objects, such as a table, index, view, or stored procedure.	CREATE TABLE table_name (column1 datatype1, column2 datatype2, ...);	CREATE TABLE students (student_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), age INT);
ALTER	Adds, deletes, or modifies columns in an existing table.	ALTER TABLE table_name ADD column_name datatype;	ALTER TABLE students ADD email VARCHAR(100);
DROP	Deletes an existing table from a database.	DROP TABLE table_name;	DROP TABLE students;
TRUNCATE	Deletes the data inside a table, but not the table itself.	TRUNCATE TABLE table_name;	TRUNCATE TABLE students;

Data Control Language (DCL) Commands

Command	Description	Syntax	Example
GRANT	Gives specific privileges to users or roles.	GRANT SELECT, INSERT ON table_name TO user_name;	GRANT SELECT, INSERT ON students TO 'John Doe';
REVOKE	Takes away privileges previously granted to users or roles.	REVOKE SELECT, INSERT ON table_name FROM user_name;	REVOKE SELECT, INSERT ON students FROM 'John Doe';

Querying Data Commands

Command	Description	Syntax	Example
SELECT	Retrieves data from a database.	SELECT column1, column2 FROM table_name;	SELECT first_name, last_name FROM students;
WHERE	Filters rows based on a specified condition.	SELECT * FROM table_name WHERE condition;	SELECT * FROM students WHERE age > 18;
ORDER BY	Sorts 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 students ORDER BY last_name DESC;
GROUP BY	Groups rows based on the values in a specified column, often used with aggregate functions like COUNT, SUM, AVG, etc.	SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;	SELECT class, COUNT(*) FROM students GROUP BY class;
HAVING	Filters grouped results based on a specified condition.	SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING condition;	SELECT class, COUNT(*) FROM students GROUP BY class HAVING COUNT(*) > 5;

Joining Commands

Command	Description	Syntax	Example
INNER JOIN	Returns rows with matching values in both tables.	SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;	SELECT * FROM students INNER JOIN subjects ON students.student_id = subjects.student_id;
LEFT JOIN	Returns all rows from the left table and the matching rows from the right table.	SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;	SELECT * FROM students LEFT JOIN subjects ON students.student_id = subjects.student_id;
RIGHT JOIN	Returns all rows from the right table and the matching rows from the left table.	SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;	SELECT * FROM students RIGHT JOIN subjects ON students.student_id = subjects.student_id;
FULL JOIN	Returns all rows when there is a match in either the left table or the right table.	SELECT * FROM table1 FULL JOIN table2 ON table1.column = table2.column;	SELECT * FROM students FULL JOIN subjects ON students.student_id = subjects.student_id;
CROSS JOIN	Combines every row from the first table with every row from the second table, creating a Cartesian product.	SELECT * FROM table1 CROSS JOIN table2;	SELECT * FROM students CROSS JOIN subjects;
SELF JOIN	Joins a table with itself.	SELECT * FROM table1 t1, table1 t2 WHERE t1.column = t2.column;	SELECT * FROM students s1, students s2 WHERE s1.student_id = s2.student_id;
NATURAL JOIN	Matches columns with the same name in both tables.	SELECT * FROM table1 NATURAL JOIN table2;	SELECT * FROM students NATURAL JOIN subjects;

Subqueries in SQL

Command	Description	Syntax	Example
IN	Determines whether a value matches any value in a subquery result.	SELECT column(s) FROM table WHERE value IN (subquery);	SELECT * FROM students WHERE age IN (SELECT age FROM teachers);
ANY	Compares a value to any value returned by a subquery.	SELECT column(s) FROM table WHERE value < ANY (subquery);	SELECT * FROM students WHERE age < ANY (SELECT age FROM teachers);
ALL	Compares a value to all values returned by a subquery.	SELECT column(s) FROM table WHERE value > ALL (subquery);	SELECT * FROM students WHERE age > ALL (SELECT age FROM teachers);

Aggregate Functions Commands

Command	Description	Syntax	Example
COUNT()	Counts the number of rows or non-null values in a specified column.	SELECT COUNT(column_name) FROM table_name;	SELECT COUNT(student_id) FROM students;
SUM()	Calculates the sum of all values in a specified column.	SELECT SUM(column_name) FROM table_name;	SELECT SUM(age) FROM students;
AVG()	Calculates the average (mean) of all values in a specified column.	SELECT AVG(column_name) FROM table_name;	SELECT AVG(age) FROM students;
MIN()	Returns the minimum (lowest) value in a specified column.	SELECT MIN(column_name) FROM table_name;	SELECT MIN(age) FROM students;
MAX()	Returns the maximum (highest) value in a specified column.	SELECT MAX(column_name) FROM table_name;	SELECT MAX(age) FROM students;

String Functions in SQL

Command	Description	Syntax	Example
CONCAT()	Concatenates two or more strings into a single string.	SELECT CONCAT(string1, string2, ...) AS concatenated_string FROM table_name;	SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM students;
SUBSTRING()	Extracts a substring from a string.	SELECT SUBSTRING(string FROM start_position [FOR length]) AS substring FROM table_name;	SELECT SUBSTRING(first_name FROM 1 FOR 3) AS substring FROM students;
CHAR_LENGTH()	Returns the length (number of characters) of a string.	SELECT CHAR_LENGTH(string) AS length FROM table_name;	SELECT CHAR_LENGTH(first_name) AS length FROM students;
UPPER()	Converts all characters in a string to uppercase.	SELECT UPPER(string) AS uppercase_string FROM table_name;	SELECT UPPER(first_name) AS uppercase_first_name FROM students;
LOWER()	Converts all characters in a string to lowercase.	SELECT LOWER(string) AS lowercase_string FROM table_name;	SELECT LOWER(last_name) AS lowercase_last_name FROM students;
TRIM()	Removes specified prefixes or suffixes (or whitespace by default) from a string.	SELECT TRIM([LEADING | TRAILING | BOTH] characters FROM string) AS trimmed_string FROM table_name;	SELECT TRIM(TRAILING ' ' FROM first_name) AS trimmed_name FROM students;
LEFT()	Returns a specified number of characters from the left of a string.	SELECT LEFT(string, num_characters) AS left_string FROM table_name;	SELECT LEFT(first_name, 3) AS left_name FROM students;
RIGHT()	Returns a specified number of characters from the right of a string.	SELECT RIGHT(string, num_characters) AS right_string FROM table_name;	SELECT RIGHT(first_name, 3) AS right_name FROM students;
REPLACE()	Replaces occurrences of a substring within a string.	SELECT REPLACE(string, old_substring, new_substring) AS replaced_string FROM table_name;	SELECT REPLACE(first_name, 'a', 'o') AS replaced_name FROM students;

Date and Time SQL Commands

Command	Description	Syntax	Example
CURRENT_DATE()	Returns the current date.	SELECT CURRENT_DATE() AS current_date;	SELECT CURRENT_DATE() AS current_date;
CURRENT_TIME()	Returns the current time.	SELECT CURRENT_TIME() AS current_time;	SELECT CURRENT_TIME() AS current_time;
CURRENT_TIMESTAMP()	Returns the current date and time.	SELECT CURRENT_TIMESTAMP() AS current_timestamp;	SELECT CURRENT_TIMESTAMP() AS current_timestamp;
DATE_PART()	Extracts a specific part (e.g., year, month, day) from a date or time.	SELECT DATE_PART('part', date_expression) AS extracted_part;	SELECT DATE_PART('year', '2024-04-11') AS extracted_part;
DATE_ADD()	Adds a specified number of days, months, or years to a date.	SELECT DATE_ADD(date_expression, INTERVAL value unit) AS new_date;	SELECT DATE_ADD('2024-04-11', INTERVAL 1 DAY) AS new_date;
DATE_SUB()	Subtracts a specified number of days, months, or years from a date.	SELECT DATE_SUB(date_expression, INTERVAL value unit) AS new_date;	SELECT DATE_SUB('2024-04-11', INTERVAL 1 DAY) AS new_date;
EXTRACT()	Extracts a specific part (e.g., year, month, day) from a date or time.	SELECT EXTRACT(part FROM date_expression) AS extracted_part;	SELECT EXTRACT(YEAR FROM '2024-04-11') AS extracted_part;
TO_CHAR()	Converts a date or time to a specified format.	SELECT TO_CHAR(date_expression, 'format') AS formatted_date;	SELECT TO_CHAR('2024-04-11', 'YYYY-MM-DD') AS formatted_date;
TIMESTAMPDIFF()	Calculates the difference between two timestamps in a specified unit (e.g., days, hours, minutes).	SELECT TIMESTAMPDIFF(unit, timestamp1, timestamp2) AS difference;	SELECT TIMESTAMPDIFF(DAY, '2024-04-10', '2024-04-11') AS difference;
DATEDIFF()	Calculates the difference in days between two dates.	SELECT DATEDIFF(date1, date2) AS difference_in_days;	SELECT DATEDIFF('2024-04-11', '2024-04-10') AS difference_in_days;

Conditional Expressions

Command	Description	Syntax	Example
CASE	Allows you to perform conditional logic within a query.	SELECT column1, column2, CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default_result END AS alias FROM table_name;	SELECT student_id, first_name, CASE WHEN age > 18 THEN 'Adult' ELSE 'Minor' END AS age_group FROM students;
IF()	Evaluates a condition and returns a value based on the evaluation.	SELECT IF(condition, true_value, false_value) AS alias FROM table_name;	SELECT first_name, IF(age > 18, 'Adult', 'Minor') AS age_group FROM students;
COALESCE()	Returns the first non-null value from a list of values.	SELECT COALESCE(value1, value2, ...) AS alias FROM table_name;	SELECT COALESCE(middle_name, first_name) AS preferred_name FROM students;
NULLIF()	Returns null if two specified expressions are equal.	SELECT NULLIF(expression1, expression2) AS alias FROM table_name;	SELECT NULLIF(age, 18) AS age_comparison FROM students;

Set Operations

Command	Description	Syntax	Example
UNION	Combines the result sets of two or more SELECT statements into a single result set.	SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2;	SELECT first_name, last_name FROM students UNION SELECT first_name, last_name FROM teachers;
INTERSECT	Returns the common rows that appear in both result sets.	SELECT column1, column2 FROM table1 INTERSECT SELECT column1, column2 FROM table2;	SELECT first_name, last_name FROM students INTERSECT SELECT first_name, last_name FROM teachers;
EXCEPT	Returns the distinct rows from the left result set that are not present in the right result set.	SELECT column1, column2 FROM table1 EXCEPT SELECT column1, column2 FROM table2;	SELECT first_name, last_name FROM students EXCEPT SELECT first_name, last_name FROM teachers;

Transaction Control Commands

Command	Description	Syntax	Example
COMMIT	Saves all the changes made during the current transaction and makes them permanent.	COMMIT;	BEGIN TRANSACTION; INSERT INTO students (first_name, age) VALUES ('Alice', 20); COMMIT;
ROLLBACK	Undoes all the changes made during the current transaction and discards them.	ROLLBACK;	BEGIN TRANSACTION; INSERT INTO students (first_name, age) VALUES ('Bob', 22); ROLLBACK;
SAVEPOINT	Sets a point within a transaction to which you can later roll back.	SAVEPOINT savepoint_name;	BEGIN TRANSACTION; INSERT INTO students (first_name, age) VALUES ('Ramesh', 21); SAVEPOINT before_update; UPDATE students SET age = 23 WHERE first_name = 'Ramesh'; SAVEPOINT after_update; DELETE FROM students WHERE age > 21; ROLLBACK TO before_update; COMMIT;
ROLLBACK TO SAVEPOINT	Rolls back to a specific savepoint within a transaction.	ROLLBACK TO SAVEPOINT savepoint_name;	BEGIN TRANSACTION; INSERT INTO students (first_name, age) VALUES ('Raj', 24); SAVEPOINT before_update; UPDATE students SET age = 25 WHERE first_name = 'Raj'; SAVEPOINT after_update; DELETE FROM students WHERE age > 24; ROLLBACK TO SAVEPOINT before_update; COMMIT;

Conclusion

Mastering SQL commands is essential for efficiently managing databases and performing administrative tasks. This cheat sheet provides a quick reference to some of the most commonly used commands, helping you navigate and operate your databases more effectively. Keep this guide handy to make the most of SQL. Happy coding!

By understanding and using these commands, you can simplify your database management processes, enhance your efficiency, and ensure your databases are well-maintained.