Microsoft SQL Server Commands Cheat Sheet

Introduction

Microsoft SQL Server is a relational database management system developed by Microsoft. It is widely used for managing and storing information. Mastering SQL Server commands is essential for database administration and development. This cheat sheet provides a quick reference to some of the most commonly used SQL Server commands.

Microsoft SQL Server 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 NVARCHAR(50), last_name NVARCHAR(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 NVARCHAR(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 scott;
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 scott;

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, start_position, length) AS substring FROM table_name;	SELECT SUBSTRING(first_name, 1, 3) AS substring FROM students;
LEN()	Returns the length (number of characters) of a string.	SELECT LEN(string) AS length FROM table_name;	SELECT LEN(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
GETDATE()	Returns the current date and time.	SELECT GETDATE() AS current_date_time;	SELECT GETDATE() AS current_date_time;
SYSDATETIME()	Returns the current date and time with higher precision.	SELECT SYSDATETIME() AS current_datetime;	SELECT SYSDATETIME() AS current_datetime;
DATEPART()	Extracts a specific part (e.g., year, month, day) from a date or time.	SELECT DATEPART(part, date_expression) AS extracted_part;	SELECT DATEPART(year, GETDATE()) AS year;
DATEADD()	Adds a specified number of days, months, or years to a date.	SELECT DATEADD(datepart, number, date_expression) AS new_date;	SELECT DATEADD(day, 1, GETDATE()) AS new_date;
DATEDIFF()	Calculates the difference between two dates.	SELECT DATEDIFF(datepart, start_date, end_date) AS difference;	SELECT DATEDIFF(day, '2024-04-10', '2024-04-11') AS difference;
FORMAT()	Formats a date or time to a specified format.	SELECT FORMAT(date_expression, 'format') AS formatted_date;	SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS formatted_date;
CONVERT()	Converts a value from one data type to another.	SELECT CONVERT(datatype, expression, style) AS converted_value;	SELECT CONVERT(VARCHAR, GETDATE(), 120) AS converted_date;
EOMONTH()	Returns the last day of the month for a specified date.	SELECT EOMONTH(date_expression) AS end_of_month;	SELECT EOMONTH(GETDATE()) AS end_of_month;

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;
IIF()	Evaluates a condition and returns one of two values based on the evaluation.	SELECT IIF(condition, true_value, false_value) AS alias FROM table_name;	SELECT first_name, IIF(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;

Conclusion

Mastering SQL Server 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 SQL Server databases more effectively. Keep this guide handy to make the most of SQL Server. 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.