SQL Server Cheat Sheet

SQL Server is a popular relational database management system

developed by Microsoft. It is widely used for storing, managing, CREATING TABLES QUERYING DATA INSERTING DATA
and processing data in various environments. To create a table: To select data from a table, use the SELECT command. To insert data into a table, use the INSERT command:
CREATE TABLE Habitat ( INSERT INTO Habitat VALUES
Id INT, (1, 'River'),
An example of a single-table query:
Transact-SQL (T-SQL) is an extension of the SQL language, Name VARCHAR(64) (2, 'Forest');
SELECT Species, AVG(Age) AS AverageAge
designed specifically for SQL Server. It allows for advanced );
FROM Animal
database operations such as defining stored procedures, triggers,
WHERE Id != 3 You may specify the columns in which the data is added. The
and indexes. Use IDENTITY to increment the ID automatically with each new GROUP BY Species remaining columns are filled with default values or NULLs.
record. HAVING AVG(Age) > 3 INSERT INTO Habitat (Name) VALUES
CREATE TABLE Habitat ( ORDER BY AVG(Age) DESC; ('Savanna');
SQL Server Management Studio (SSMS) is the official graphical
Id INT PRIMARY KEY IDENTITY,
tool for managing SQL Server databases. It offers a
Name VARCHAR(64)
comprehensive interface for administrators and developers to An example of a multiple-table query:
design databases, write queries, and optimize database
);
SELECT City.Name, Country.Name UPDATING DATA
performance, among other tasks. FROM City To update the data in a table, use the UPDATE command:
To create a table with a foreign key: [INNER | LEFT | RIGHT | FULL] JOIN Country UPDATE Animal
CREATE TABLE Animal ( ON City.CountryId = Country.Id; SET
Download Microsoft SQL Server here: Id INT PRIMARY KEY IDENTITY, Species = 'Duck',
https://www.microsoft.com/en-us/sql-server/sql-server- Name VARCHAR(64), Name = 'Quack'
downloads Species VARCHAR(64), AGGREGATION AND GROUPING WHERE Id = 2;
Age INT,
AVG(expr) − average value of expr for the group.
HabitatId INT,
COUNT(expr) − count of expr values within the group.
CREATING AND DISPLAYING FOREIGN KEY (HabitatId)
MAX(expr) − maximum value of expr values within the DELETING DATA
REFERENCES Habitat(Id)
group. To delete data from a table, use the DELETE command:
DATABASES );
MIN(expr) − minimum value of expr values within the DELETE FROM Animal
To create a database: group. WHERE Id = 1;
CREATE DATABASE Zoo;
MODIFYING TABLES SUM(expr) − sum of expr values within the group.

Use the ALTER TABLE or the EXEC statement to modify a table This deletes all rows satisfying the WHERE condition.
To list all databases on a server: structure. To count the rows in the table: To delete all data from a table, use the TRUNCATE TABLE
SELECT * SELECT COUNT(*) statement:
FROM sys.databases; FROM Animal; TRUNCATE TABLE Animal;
To change a table name:
EXEC sp_rename 'AnimalSchema.Animal', 'Pet'
To count the non-NULL values in a column:
To use a specified database: SELECT COUNT(Name) SQL SERVER CONVENTIONS
USE Zoo; To add a column to a table:
FROM Animal; In SQL Server, use square brackets to handle table or column
ALTER TABLE Animal
names that contain spaces, special characters, or reserved
ADD COLUMN Name VARCHAR(64);
keywords. For example:
To delete a specified database: To count unique values in a column:
SELECT
DROP DATABASE Zoo; To change a column name: SELECT COUNT(DISTINCT Name)
[First Name],
EXEC sp_rename 'AnimalSchema.Animal.Id', FROM Animal;
[Age]
'Identifier', 'COLUMN'; FROM [Customers];
To create a schema:
CREATE SCHEMA AnimalSchema; GROUP BY
To change a column data type: To count the animals by species: Often, you refer to a table by its full name that consists of the
ALTER TABLE Animal SELECT Species, COUNT(Id) schema name and the table name (for example,
DISPLAYING TABLES ALTER COLUMN Name VARCHAR(128); FROM Animal
GROUP BY Species;
AnimalSchema.Habitat, sys.databases). For simplicity,
we use plain table names in this cheat sheet.
To list all tables in a database:
To delete a column:
SELECT *
ALTER TABLE Animal To get the average, minimum, and maximum ages by habitat:
FROM sys.tables;
DROP COLUMN Name; SELECT HabitatId, AVG(Age), THE GO SEPARATOR
MIN(Age), MAX(Age) In SQL Server, GO is a batch separator used to execute multiple
To get information about a specified table: To delete a table: FROM Animal SQL statements together. It is typically used in SQL Server
exec sp_help 'Animal' DROP TABLE Animal; GROUP BY HabitatId; Management Studio and similar tools.

TEXT FUNCTIONS NUMERIC FUNCTIONS DATE AND TIME DATE ARITHMETICS

Character strings are enclosed in single quotes: Use +, -, *, / to do some basic math. There are 6 main time-related types in MySQL: To add or subtract from a DATE, use the DATEADD() function:
SELECT 'Michael'; To get the number of seconds in a week: DATEADD(day, -3, '2014-04-05');
SELECT 60 * 60 * 24 * 7; -- result: 604800 DATE – stores the year, month, and day in the YYYY-MM-DD -- result: '2014-04-02'
Unicode strings are enclosed in single quotes and prefixed with format.
capital N: The supported range is '0001-01-01' to '9999-12-31'.
SELECT N'Michél'; In SQL Server, the division operator / performs an integer division
on integer arguments. For example: To find the difference between two dates, use the DATEDIFF()
SELECT 25 / 4; -- result 6 TIME – stores the hours, minutes, seconds, and nanoseconds in function:
CONCATENATION the HH:MM:SS[.nnnnnnn] format. SELECT DATEDIFF(year, '2019-05-15',
Use the CONCAT() function to concatenate two strings: The supported range is '00:00:00.0000000' to '2017-05-15');
SELECT CONCAT(N'Hi ', N'there!'); To avoid the integer division, make sure at least one of the '23:59:59.9999999'. -- result: -2
-- result: Hi there! arguments is not an integer: SELECT DATEDIFF(month, '2019-06-15',
SELECT CAST(25 AS DECIMAL) / 4; '2023-12-15');
SMALLDATETIME – stores the date and time in the YYYY-MM-
CONCAT() treats NULL as an empty string: -- result 6.25 -- result: 54
DD HH:MM:SS format.
SELECT CONCAT(N'Learn ', NULL, N'SQL.com'); SELECT 25.0 / 4; The supported date parts are: year, quarter, month,
The supported range is '1900-01-01' to '2079-06-06'.
-- result: LearnSQL.com -- result 6.25 dayofyear, day, week, hour, minute, second,
millisecond, microsecond, nanosecond.
SQL Server allows specifying a separating character (separator) DATETIME – stores the date and time in the YYYY-MM-DD
using the CONCAT_WS() function. The separator is placed To get the remainder of a division: HH:MM:SS[.nnn] format.
between the concatenated values: SELECT MOD(13, 2); -- result: 1 The supported range is '1753-01-01' to '9999-12-31'.
SELECT CONCAT_WS(' ', 1, N'Olivier', EXTRACTING PARTS OF DATES
N'Norris'); -- result: 1 Olivier Norris DATETIME2 – stores the date and time in the YYYY-MM-DD
To round a number to three decimal places: To extract a part of a date, use the functions YEAR(), MONTH(),
HH:MM:SS[.nnnnnnn] format. or DAY():
SELECT ROUND(1234.56789, 3);
FILTERING THE OUTPUT -- result: 1234.568
The supported range is '0001-01-01 00:00:00.0000000' SELECT YEAR(CAST('2021-12-31' AS date));
To fetch the city names that are not Berlin: to '9999-12-31 23:59:59.9999999'. -- result: 2021
SELECT Name SELECT MONTH(CAST('2021-12-31' AS date));
FROM City To round a number up: DATETIMEOFFSET – stores the date and time in the YYYY-MM- -- result: 12
WHERE Name != N'Berlin'; SELECT CEILING(13.1), CEILING(-13.9); DD HH:MM:SS[.nnnnnnn][+|-]hh:mm format. SELECT DAY(CAST('2021-12-31' AS date));
-- result: 14, -13 The supported range is '0001-01-01 00:00:00.0000000' -- result: 31
TEXT OPERATORS to '9999-12-31 23:59:59.9999999' in UTC.
To fetch the city names that start with a 'P' or end with an 's': To round a number down:
SELECT Name
FROM City
SELECT FLOOR(13.8), FLOOR(-13.2);
-- result: 13, -14
WHAT TIME IS IT? You may also use the DATEPART() function:
SELECT DATEPART(year, '2013-09-15');
WHERE Name LIKE N'P%' OR Name LIKE N'%s'; To get the current datetime without the time-zone offset: -- result: 2013
SELECT GETDATE(); -- result: 2023-07-27 Supported date parts are: year, quarter, month,
To fetch the city names that start with any letter followed by
'ublin' (like Dublin in Ireland or Lublin in Poland):
USEFUL NULL FUNCTIONS 07:21:13.937 dayofyear, day, week, weekday, hour, minute, second,
millisecond, microsecond, nanosecond, tzoffset,
SELECT Name To fetch the names of the cities whose rating values are not
To get the current datetime without the time-zone offset in iso_week.
FROM City missing:
DATETIME2 data type (higher fractional seconds precision):
WHERE Name LIKE N'_ublin'; SELECT Name
SELECT SYSDATETIME(); -- result: 2023-07-27
FROM City
07:21:13.9398213
OTHER USEFUL TEXT FUNCTIONS WHERE Rating IS NOT NULL;
CHANGING THE TIME ZONE
To get the count of characters in a string: To get the current datetime in UTC: Use AT TIME ZONE to convert a date and time value into a
SELECT LEN(N'LearnSQL.com'); -- result: 12 COALESCE(x, y, ...) SELECT GETUTCDATE(); -- result: 2023-07-27 target time zone. You may use meaningful time zone names such
07:21:13.937 as 'Pacific Standard Time'. SQL Server uses the names
To replace NULL in a query with something meaningful:
To convert all letters to lowercase: stored in the Windows Registry.
SELECT Domain,
SELECT LOWER(N'LEARNSQL.COM'); or in datetime2 data type (higher fractional seconds precision):
COALESCE(Domain, 'domain missing')
-- result: learnsql.com SELECT SYSUTCDATETIME(); -- result: 2023-07-
FROM Contacts;
The COALESCE() function takes any number of arguments and 27 07:21:13.9398213 To add the target time-zone offset to a datetime value without
To convert all letters to uppercase:
returns the value of the first argument that is not NULL. offset information:
SELECT UPPER(N'LearnSQL.com');
To get the current datetime with the time-zone offset: SELECT start_time AT TIME ZONE 'UTC';
-- result: LEARNSQL.COM
SELECT SYSDATETIMEOFFSET();
To get just a part of a string: NULLIF(x, y) -- result: 2023-07-27 07:21:13.9398213 +00:00
SELECT SUBSTRING(N'LearnSQL.com', 1, 5); To save yourself from division-by-0 errors: To convert values between different time zones:
-- result: Learn SELECT LastMonth, ThisMonth, SELECT '2023-07-20 12:30:00'
ThisMonth * 100.0 / NULLIF(LastMonth, 0) CREATING VALUES AT TIME ZONE 'UTC'
To replace a part of a string: AS BetterByPercent To create a date, time, or datetime, write the value as a string and AT TIME ZONE 'Eastern Standard Time';
SELECT REPLACE(N'LearnSQL.com', 'SQL', FROM VideoViews; cast it to the proper type. -- result: 2023-07-20 08:30:00
'Python'); The NULLIF(x, y) function returns NULL if x equals y, else it SELECT CAST('2021-12-31' AS date), Specify the known time-zone offset first (here, UTC) and then the
-- result: LearnPython.com returns the value of x value. CAST('2021-12-31 23:59:29' AS DATETIME2); time zone to which you want to convert.

LearnSQL.com is owned by Vertabelo SA

Check out our interactive SQL from A to Z in MS SQL Server track and other online courses at LearnSQL.com vertabelo.com | CC BY-NC-ND Vertabelo SA