Language: EN
LuisLlamas Logo
Search
⏲️ ~ 9 minutes Beginner
cheatsheet-sql

T-SQL CheatSheet

T-SQL (Transact-SQL) is an extension of the SQL language developed by Microsoft for SQL Server. It adds features such as variables, flow control, error handling, and more.

Database Connection

Connect to a database in SQL Server

USE database_name;

Connection with Windows Authentication

sqlcmd -S server_name -d database_name -E

Connection with SQL Server Authentication

sqlcmd -S server_name -U username -P password -d database_name

Data Selection

Select all records

Retrieves all data from a table.

SELECT * FROM table_name;

Select specific columns

Allows you to select only the columns you are interested in.

SELECT column1, column2 FROM table_name;

Rename a column in the output

AS is used to rename columns in the output.

SELECT column1 AS new_name FROM table_name;

Limit number of rows

The TOP clause is used to limit the number of rows returned.

SELECT TOP 10 * FROM table;

Filters with WHERE

Filter records with conditions

The WHERE command filters the results based on a condition.

SELECT * FROM table_name WHERE column = 'value';

Comparison operators

  • = Equal
  • <> or != Not equal
  • > Greater than
  • < Less than
  • >= Greater than or equal
  • <= Less than or equal

Sorting and Grouping

Sort results

ORDER BY is used to sort the data.

SELECT * FROM table_name ORDER BY column1 DESC;

Group results with GROUP BY

Groups rows that have the same values in specified columns.

SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

Filter groups with HAVING

HAVING filters the groups created with GROUP BY.

SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) > 1;

Logical Operators

AND

The AND operator is used to combine multiple conditions, and all must be true.

SELECT * FROM table WHERE condition1 AND condition2;

OR

The OR operator is used to combine multiple conditions, and at least one must be true.

SELECT * FROM table WHERE condition1 OR condition2;

NOT

The NOT operator is used to negate a condition.

SELECT * FROM table WHERE NOT condition;

Joins

Joins are used to combine rows from two or more tables based on a related condition.

INNER JOIN

Returns rows when there are matches in both tables.

SELECT * FROM table1
INNER JOIN table2 ON table1.column = table2.column;

LEFT JOIN

Returns all rows from the left table, and the matches from the right, if any.

SELECT * FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

RIGHT JOIN

Returns all rows from the right table, and the matches from the left.

SELECT * FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

FULL OUTER JOIN

Returns all rows when there are matches in one of the tables or both.

SELECT * FROM table1
FULL OUTER JOIN table2 ON table1.column = table2.column;

Subqueries

Subquery in SELECT

A query within another query to obtain results based on an inner query.

SELECT column1 FROM table_name WHERE column2 = (SELECT MAX(column2) FROM another_table);

Subquery in FROM

Allows using the results of a query as a temporary table.

SELECT temp_table.column1
FROM (SELECT * FROM another_table WHERE column = 'value') AS temp_table;

Data Manipulation

Insert data into a table

Inserts a new record into the table.

INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');

Update records

Modifies existing data in the table.

UPDATE table_name SET column1 = 'new_value' WHERE column2 = 'value';

Delete records

Deletes rows from the table.

DELETE FROM table_name WHERE column = 'value';

Flow Control Variables

Variable Declaration

Define a variable

Local variables are used to store temporary values.

DECLARE @my_variable INT;
SET @my_variable = 100;

Use variables in queries

Variables can be used in T-SQL queries.

SELECT * FROM table_name WHERE column = @my_variable;

Conditionals

IF…ELSE

Allows executing different blocks of code depending on a condition.

IF (@my_variable > 50)
    PRINT 'Greater than 50';
ELSE
    PRINT 'Less than or equal to 50';

CASE

Evaluates an expression and returns a value based on conditions.

SELECT column,
    CASE
        WHEN column > 100 THEN 'High'
        WHEN column BETWEEN 50 AND 100 THEN 'Medium'
        ELSE 'Low'
    END AS classification
FROM table_name;

Functions in T-SQL

Aggregate Functions

Count

Counts the number of rows.

SELECT COUNT(*) FROM table_name;

Sum

Sums the values of a column.

SELECT SUM(numeric_column) FROM table_name;

Avg

Calculates the average of the values of a column.

SELECT AVG(numeric_column) FROM table_name;

Max / Min

Returns the maximum or minimum value of a column.

SELECT MAX(numeric_column) FROM table_name;

String Functions

Concat

Concatenates two or more text strings.

SELECT CONCAT(column1, column2) FROM table_name;

Len

Returns the length of a string.

SELECT LEN(column) FROM table_name;

SubString

Extracts a portion of a text string.

SELECT SUBSTRING(column, start, length) FROM table_name;

Date and Time Functions

GetDate

Returns the current date and time.

SELECT GETDATE();

DateAdd

Adds a time interval to a date.

SELECT DATEADD(day, 10, GETDATE());

DateDiff

Calculates the difference between two dates.

SELECT DATEDIFF(day, date1, date2);

Format

Formats a date or number according to a specific format.

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd');

Transaction Management

Start a transaction

Starts a transaction that groups several SQL operations.

BEGIN TRANSACTION;

Commit and Rollback

  • COMMIT saves the changes.
  • ROLLBACK reverts the changes if there is an error.
BEGIN TRANSACTION;

-- Execution of SQL commands
COMMIT TRANSACTION;
-- Or, if there is an error
ROLLBACK TRANSACTION;

Try Catch

Used to handle errors in T-SQL.

BEGIN TRY
    -- SQL commands that may fail
    SELECT * FROM table_name;
END TRY
BEGIN CATCH
    PRINT 'An error occurred';
END CATCH;

Table Creation and Modification

Create table

Used to create a new table in the database.

CREATE TABLE table_name (
    column1 data_type1,
    column2 data_type2,
    ...
);

Modify table

Used to modify an existing table.

ALTER TABLE table_name ADD column data_type;

Delete table

Used to delete an existing table.

DROP TABLE table_name;

Delete table only if it exists

IF OBJECT_ID('table_name', 'U') IS NOT NULL 
DROP TABLE table_name;

Here’s a paragraph you can add to your cheat sheet about temporary tables in T-SQL:

Temporary Tables

Temporary tables are structures used to store data temporarily within a SQL Server session.

CREATE TABLE #temporaryTable (
    column1 INT,
    column2 VARCHAR(50)
);

Indexes and Optimization

Create an index

Indexes improve the search speed on tables.

CREATE INDEX idx_column_name
ON table_name (column);

Delete an index

Removes an index from a table.

DROP INDEX idx_column_name ON table_name;

Views

Views are stored queries that can be used as virtual tables.

Create a View

CREATE VIEW view_name AS
SELECT column1, column2 FROM table WHERE condition;

Use a View

SELECT * FROM view_name;

Stored Procedures

Create a stored procedure

A stored procedure is a set of SQL commands that can be executed as a unit.

CREATE PROCEDURE procedure_name
AS
BEGIN
    SELECT * FROM table_name;
END;

Execute a stored procedure

To execute the procedure.

EXEC procedure_name;

Procedures with parameters

Stored procedures can accept parameters.

CREATE PROCEDURE procedure_name
    @parameter1 INT,
    @parameter2 VARCHAR(50)
AS
BEGIN
    SELECT * FROM table_name WHERE column = @parameter1;
END;

Security and Users

Create a User

CREATE LOGIN username WITH PASSWORD = 'password';

Grant Permissions

GRANT permission ON object TO username;

Revoke Permissions

REVOKE permission ON object FROM username;

On this page