The SQL Server Cheat Sheet provides you with the syntax of all basic data types, type casting, and functions, shows you how to create tables, views, and data related to different conditions, and has examples. So, let’s start SQL Server Cheat Sheet.
What is a 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…); |
Insert/Select
| INSERT INTO *table (column1,column2,…) SELECT statement;* (See below) |
Delete data
|
DELETE FROM table (Omit WHERE to delete all data) |
Updating Data
| UPDATE table SET column1=value1, column2=value2, … columnk=valuek [WHERE condition(s)]; |
Insert column
| ALTER TABLE table ADD COLUMN column type options; |
Delete Column
| ALTER TABLE table DROP COLUMN column; |
Delete table (Careful!)
| DROP TABLE [IF EXISTS] table; |
Create an Index
| CREATE UNIQUE INDEX name ON table (columns) |
Create a Stored Procedure
| CREATE PROCEDURE name @variable AS datatype = value AS — Comments SELECT FROM table* GO |
Leave a Comment