SQL Server Cheat Sheet

The SQL Server Cheat Sheet provides you with the syntax of all basic data type, type casting, functions, shows you how to create table, views and data related different conditions, and has examples. So, Let’s start SQL Server Cheat Sheet.

What is Database?

To get introduced to SQL, first we need to know about Data and Databases. Data is basically a collection of facts related to some object. A Database is a collection of small units of data arranged in a systematic manner.

What is SQL?

After getting the concept of data and databases, we can finally learn about SQL. SQL (Structured Query Language) is basically the language that we (the user) use to communicate with the Databases and get our required interpretation of data out of it. It is used for storing, manipulating and retrieving data out of a database.

Coloumn Type

Purpose	Data Type	Example
Integers	int(M)	int(5)
Floating-point (real) numbers	float(M,D)	float(12,3)
Double-precision floating-point	double(M,D)	double(20,3)
Dates and times	timestamp(M)	timestamp(8) (for YYYYMMDD)
Fixed-length strings	char(M)	char(10)
Variable-length strings	varchar(M)	varchar(20)
A large amount of text	blob	blob
Values chosen from a list	enum(‘value1’,value2’,…)	enum(‘apples’,’oranges’,’bananas’)

Mathematical Functions

What	How
Count rows per group	COUNT(column \| *)
Average value of group	AVG(column)
Minumum value of group	MIN(column)
Maximum value of group	MAX(column)
Sum values in a group	SUM(column)
Absolute value	abs(number)
Rounding numbers	round(number)
Largest integer not greater	floor(number)
Smallest integer not smaller	ceiling(number)
Square root	sqrt(number)
nth power	pow(base,exponent)
random number n, 0<n < 1	rand()
sin (similar cos, etc.)	sin(number)

String Functions

What	How
Compare strings	strcmp(string1,string2)
Convert to lower case	lower(string)
Convert to upper case	upper(string)
Left-trim whitespace (similar right)	ltrim(string)
Substring of string	substring(string,index1,index2)
Encrypt password	password(string)
Encode string	encode(string,key)
Decode string	decode(string,key)
Get date	curdate()
Get time	curtime()
Extract day name from date string	dayname(string)
Extract day number from date string	dayofweek(string)
Extract month from date string	monthname(string)

Date Functions

DATEADD (datepart, number , date)

DATEDIFF (datepart , start , end)

DATENAME (datepart, date)

DATEPART (datepart, date)

DAY (date)

GETDATE()

GETUTCDATE()

MONTH(date)

YEAR(date)

Date Parts

Year	yy, yyyy
Quarter	qq, q
Day	dd, d
Day of Year	dy, y
Month	mm, m
Week	wk, ww
Hour	hh
Minute	mi, n
Second	ss, s
Milisecond	ms

Type Conversion

CAST (expressions AS datatype)

CONVERT (datatype , expression)

Ranking Functions

RANK	NTILE
DENSE_RANK	ROW_NUMBER

Gruoping Functions

AVG	MAX
BINARY_CHECKSUM	MIN
CHECKSUM	SUM
CHECKSUM_AVG	STDEV
COUNT	STDEVP
COUNT_BIG	VAR
GROUPING	VARP

Table Functios

ALTER	DROP
CREATE	TRUNCATE

Create a Trigger

CREATE TRIGGER name
ON
table
FOR
DELETE, INSERT, UPDATE
AS
— Comments
SELECT * FROM table
GO

Create a View

CREATE VIEW name
AS
— Comments
SELECT FROM table*
GO

Create Table

CREATE TABLE table (
column1 type [[NOT] NULL]
[AUTO_INCREMENT],
column2 type [[NOT] NULL]
[AUTO_INCREMENT],
…
other options,
PRIMARY KEY (column(s)) );

Insert Data

INSERT INTO table VALUES
(list of values);
INSERT INTO table SET
column1=value1,
column2=value2,
…
columnk=valuek;
INSERT INTO table (column1,column2,…)
VALUES (value1,value2…);