SQL Cheatsheet

Xybernetics SQL CheatsheetSELECT Syntax

SELECT col_name, col_date, col_category, col_zonenum
FROM table_name
WHERE col_name LIKE '*abc*' AND
col_zonenum = 2
ORDER BY col_date ASC|DESC;

SELECT DISTINCT Syntax

SELECT DISTINCT col_name, col_date FROM table_name;

BETWEEN Syntax
For dates

SELECT * FROM Orders
WHERE OrderDate BETWEEN '2011/02/25' and '2011/02/27 23:59:59.999';

For numbers

SELECT NumOfQty FROM Orders
WHERE NumOfQty BETWEEN 2 AND 8;

HAVING Syntax
The HAVING is used in the SQL statement in cases when the WHERE keyword could not be used with aggregate functions.

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

INSERT Syntax
Data inserted in the order of the fields in the table

INSERT INTO table_name
VALUES (value1,value2,value3,...);

Data inserted to specific field.

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

UPDATE Syntax

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

DELETE Syntax

DELETE FROM table_name
WHERE some_column=some_value;

SQL Joins
Xybernetics SQL Cheatsheet Join Query

CREATE Database Syntax

CREATE DATABASE dbname;

CREATE Table Syntax

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

Drop Database Syntax

DROP INDEX index_name ON table_name


SQL Functions Syntax

AVG
The AVG() function returns the average value of a numeric column.

SELECT AVG(Price) AS PriceAverage FROM Products;

COUNT
The COUNT() function returns the number of rows that matches a specified criteria.

SELECT COUNT(column_name) FROM table_name;

FIRST
The FIRST() function returns the first value of the selected column.

SELECT FIRST(column_name) FROM table_name;

LAST
The LAST() function returns the last value of the selected column.

SELECT LAST(column_name) FROM table_name;

MAX
The MAX() function returns the largest value of the selected column.

SELECT MAX(column_name) FROM table_name;

MIN
The MIN() function returns the smallest value of the selected column.

SELECT MIN(column_name) FROM table_name;

SUM
The SUM() function returns the total sum of a numeric column.

SELECT SUM(column_name) FROM table_name;

UCASE
The UCASE() function converts the value of a field to uppercase.

SELECT UCASE(column_name) FROM table_name;

LCASE
The LCASE() function converts the value of a field to lowercase.

SELECT LCASE(column_name) FROM table_name;

MID
The MID() function is used to extract characters from a text field.
Example outputs first 4 letters of the “City” field. For example, if the field “City” has a value of “Singapore”, the SQL query returns “Sing”.

SELECT MID(City,1,4) AS ShortCity FROM Customers;

LEN
The LEN() function returns the length of the value in a text field.

SELECT LEN(column_name) FROM table_name;

ROUND
The ROUND() function is used to round a numeric field to the number of decimals specified.

SELECT ROUND(column_name,decimals) FROM table_name;

NOW
The NOW() function returns the current system date and time.

SELECT NOW() FROM table_name;

FORMAT
The FORMAT() function is used to format how a field is to be displayed.
Date format

SELECT FORMAT(Now(),'yyyy/MM/dd hh:mm:ss tt') FROM Products;
SELECT FORMAT(Now(),'h:m:s')
  FROM Products;                              -- Returns "1:10:47".
SELECT FORMAT(Now(),'hh:mm:ss tt')
  FROM Products;                              -- Returns "01:10:47 AM"
SELECT FORMAT(Now(),'dddd, MMM d yyyy')
  FROM Products;                              -- Returns "Thursday, Feb 22 2016"

Date numbers

SELECT FORMAT(1222.4, '##,##0.00')            -- Returns "1,222.40".
SELECT FORMAT(345.9, '###0.00')               -- Returns "345.90".
SELECT FORMAT(15, '0.00%')                    -- Returns "1500.00%".


Datatypes

Data
Category
Data
Type
Size
(bytes)
Value
Range
Exact
Numeric
Bit 1 1, 0 or NULL
Tinyint 1 0 to 255
Smallint 2 -2^15 (-32,768) to 2^15-1 (32,767)
Int 4 -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
Bigint 8 -2^63 (-9,223,372,036,854,775,808) to
2^63-1 (9,223,372,036,854,775,807)
Smallmoney 4 -214,748.3648 to 214,748.3647
Money 8 -922,337,203,685,477.5808 to
922,337,203,685,477.5807
Numeric [(p[,s])] 5 to 17  
Decimal [(p[,s])] 5 to 17  
Appro.
Numeric
Float 4 to 8 -1.79E+308 to -2.23E-308, 0 and
2.23E-308 to 1.79E+308
Real(Float 24) 4 -3.40E 38 to -1.18E-38, 0 and 1.18E-38 to 3.40E+38
Character
String
Char [(N)] N N=1 to 8000 non-Unicode characters bytes
Varchar [(N or max)] N or 2^31-1 N=1 to 8000 non-Unicode characters bytes
Max=2^31-1 bytes (2GB) non-Unicode characters bytes
Text 2^31-1 1 to 2^31-1 (2,147,483,647)
non-Unicode characters bytes
Unicode
Character
String
Char [(N)] N N = 1 to 4000 Unicode UCS – 2 bytes
Varchar [(N or max)] N or 2^31-1 N=1 to 4000 non-Unicode UCS – 2 bytes
Max=2^31-1 bytes (2GB) non-Unicode – 2 bytes
Ntext 2^30-1 Maximum size2^30 – 1 (1,073,741,823) bytes
Binary
String
Binary [(N)] N N = 1 to 8000 bytes
Varbinary [(N | max)] N or 2^31-1 N = 1 to 8000 bytes
Max = 0 to 2^31-1 bytes
Image 2^31-1 0 to 2^31-1 (2,147,483,647) bytes
Other
Datatype
Unique Identifier 16 XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX
(hexidecimal)
Timestamp 8 binary(8) or varbinary(8)
Rowversion 8 binary(8) or varbinary(8)
xml 2^31-1 XML(([CONTENT|DOCUMENT]xml_schema_collection)
sql_variant 8016 Data type that stores value of various
SQL Server-supported data types
Hierarchyid 892 6*logAn bits where n is child node
Cursor    
Table    
Sysname 256  
Date
And
Time
Date 3 0001-01-01 through 999-12-31
time
[ (fractional second precision) ]
3 to 5 00:00:00.0000000 through 23:59:59.9999999
Smalldatetime 4 Date: 1900-01-01 through 2079-06-06
Time: 00:00:00.0000000 through 23:59:59.9999999
datetime 8 Date: January 1, 1753 through December 31, 9999
Time: 00:00:00.0000000 through 23:59:59.9999999
datetime2
[ (fractional second precision) ]
6 to 8 Date: 0001-01-01 through 9999-12-31
Time: 00:00:00.0000000 through 23:59:59.9999999
datetimeoffset
[ (fractional second precision) ]
8 to 10 Date: 0001-01-01 through 9999-12-31
Time: 00:00:00.0000000 through 23:59:59.9999999
Time zone offset: -14:00 through +14:00
Spatial Geography 2^31-1  
Geometry 2^31-1