====== SQL language (General) ======
===== SQL General Data Types =====
Each column in a database table is required to have a name and a data
type.
SQL developers have to decide what types of data will be stored inside
each and every table column when creating a SQL table. The data type is
a label and a guideline for SQL to understand what type of data is
expected inside of each column, and it also identifies how SQL will
interact with the stored data.
===== The following table lists the general data types in SQL: =====
Data type Description
CHARACTER(n) Character string. Fixed-length n
VARCHAR(n) or Character string. Variable length. Maximum length n
CHARACTER VARYING(n)
BINARY(n) Binary string. Fixed-length n
BOOLEAN Stores TRUE or FALSE values
VARBINARY(n) or Binary string. Variable length. Maximum length n
BINARY VARYING(n)
INTEGER(p) Integer numerical (no decimal). Precision p
SMALLINT Integer numerical (no decimal). Precision 5
INTEGER Integer numerical (no decimal). Precision 10
BIGINT Integer numerical (no decimal). Precision 19
Exact numerical, precision p, scale s. Example:
DECIMAL(p,s) decimal(5,2) is a number that has 3 digits before the
decimal and 2 digits after the decimal
NUMERIC(p,s) Exact numerical, precision p, scale s. (Same as
DECIMAL)
Approximate numerical, mantissa precision p. A
FLOAT(p) floating number in base 10 exponential notation. The
size argument for this type consists of a single
number specifying the minimum precision
REAL Approximate numerical, mantissa precision 7
FLOAT Approximate numerical, mantissa precision 16
DOUBLE PRECISION Approximate numerical, mantissa precision 16
DATE Stores year, month, and day values
TIME Stores hour, minute, and second values
TIMESTAMP Stores year, month, day, hour, minute, and second
values
INTERVAL Composed of a number of integer fields, representing
a period of time, depending on the type of interval
ARRAY A set-length and ordered collection of elements
MULTISET A variable-length and unordered collection of
elements
XML Stores XML data
===== SQL Data Type Quick Reference =====
However, different databases offer different choices for the data type
definition.
The following table shows some of the common names of data types between
the various database platforms:
Data type Access SQLServer Oracle MySQL PostgreSQL
boolean Yes/No Bit Byte N/A Boolean
integer Number Int Number Int Int
(integer) Integer Integer
float Number Float Number Float Numeric
(single) Real
currency Currency Money N/A N/A Money
string (fixed) N/A Char Char Char Char
string Text (<256) Varchar Varchar Varchar Varchar
(variable) Memo (65k+) Varchar2
Binary (fixed up to
binary object OLE Object 8K) Long Blob Binary
Memo Varbinary (<8K) Raw Text Varbinary
Image (<2GB)
Note: Data types might have different names in different database. And
even if the name is the same, the size and other details may be
different\! Always check the documentation\!
===== Microsoft Access Data Types =====
Data type Description Storage
Text Use for text or combinations of text and numbers.
255 characters maximum
Memo is used for larger amounts of text. Stores up
Memo to 65,536 characters. Note: You cannot sort a memo
field. However, they are searchable
Byte Allows whole numbers from 0 to 255 1 byte
Integer Allows whole numbers between -32,768 and 32,767 2 bytes
Long Allows whole numbers between -2,147,483,648 and 4 bytes
2,147,483,647
Single Single precision floating-point. Will handle most 4 bytes
decimals
Double Double precision floating-point. Will handle most 8 bytes
decimals
Use for currency. Holds up to 15 digits of whole
Currency dollars, plus 4 decimal places. Tip: You can 8 bytes
choose which country's currency to use
AutoNumber AutoNumber fields automatically give each record 4 bytes
its own number, usually starting at 1
Date/Time Use for dates and times 8 bytes
A logical field can be displayed as Yes/No,
Yes/No True/False, or On/Off. In code, use the constants 1 bit
True and False (equivalent to -1 and 0). Note:
Null values are not allowed in Yes/No fields
Ole Object Can store pictures, audio, video, or other BLOBs up to 1GB
(Binary Large OBjects)
Hyperlink Contain links to other files, including web pages
Lookup Wizard Let you type a list of options, which can then be 4 bytes
chosen from a drop-down list
===== MySQL Data Types =====
In MySQL there are three main data types : text, number, and Date/Time
types.
Text types:
Data type Description
Holds a fixed length string (can contain letters,
CHAR(size) numbers, and special characters). The fixed size is
specified in parenthesis. Can store up to 255 characters
Holds a variable length string (can contain letters,
numbers, and special characters). The maximum size is
VARCHAR(size) specified in parenthesis. Can store up to 255 characters.
Note: If you put a greater value than 255 it will be
converted to a TEXT type
TINYTEXT Holds a string with a maximum length of 255 characters
TEXT Holds a string with a maximum length of 65,535 characters
BLOB For BLOBs (Binary Large OBjects). Holds up to 65,535
bytes of data
MEDIUMTEXT Holds a string with a maximum length of 16,777,215
characters
MEDIUMBLOB For BLOBs (Binary Large OBjects). Holds up to 16,777,215
bytes of data
LONGTEXT Holds a string with a maximum length of 4,294,967,295
characters
LONGBLOB For BLOBs (Binary Large OBjects). Holds up to
4,294,967,295 bytes of data
Let you enter a list of possible values. You can list up
to 65535 values in an ENUM list. If a value is inserted
that is not in the list, a blank value will be inserted.
ENUM(x,y,z,etc.)
Note: The values are sorted in the order you enter them.
You enter the possible values in this format:
ENUM('X','Y','Z')
SET Similar to ENUM except that SET may contain up to 64 list
items and can store more than one choice
Number types:
Data type Description
TINYINT(size) -128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number
of digits may be specified in parenthesis
SMALLINT(size) -32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum
number of digits may be specified in parenthesis
MEDIUMINT(size) -8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The
maximum number of digits may be specified in parenthesis
-2147483648 to 2147483647 normal. 0 to 4294967295
INT(size) UNSIGNED*. The maximum number of digits may be specified
in parenthesis
-9223372036854775808 to 9223372036854775807 normal. 0 to
BIGINT(size) 18446744073709551615 UNSIGNED*. The maximum number of
digits may be specified in parenthesis
A small number with a floating decimal point. The maximum
FLOAT(size,d) number of digits may be specified in the size parameter.
The maximum number of digits to the right of the decimal
point is specified in the d parameter
A large number with a floating decimal point. The maximum
DOUBLE(size,d) number of digits may be specified in the size parameter.
The maximum number of digits to the right of the decimal
point is specified in the d parameter
A DOUBLE stored as a string , allowing for a fixed decimal
DECIMAL(size,d) point. The maximum number of digits may be specified in
the size parameter. The maximum number of digits to the
right of the decimal point is specified in the d parameter
* The integer types have an extra option called UNSIGNED. Normally, the integer goes from an negative to positive value. Adding the UNSIGNED attribute will move that range up so it starts at zero instead of a negative number.
Date types:
Data type Description
A date. Format: YYYY-MM-DD
DATE()
Note: The supported range is from '1000-01-01' to '9999-12-31'
*A date and time combination. Format: YYYY-MM-DD HH:MI:SS
DATETIME()
Note: The supported range is from '1000-01-01 00:00:00' to
'9999-12-31 23:59:59'
*A timestamp. TIMESTAMP values are stored as the number of
seconds since the Unix epoch ('1970-01-01 00:00:00' UTC).
TIMESTAMP() Format: YYYY-MM-DD HH:MI:SS
Note: The supported range is from '1970-01-01 00:00:01' UTC to
'2038-01-09 03:14:07' UTC
A time. Format: HH:MI:SS
TIME()
Note: The supported range is from '-838:59:59' to '838:59:59'
A year in two-digit or four-digit format.
YEAR() Note: Values allowed in four-digit format: 1901 to 2155.
Values allowed in two-digit format: 70 to 69, representing
years from 1970 to 2069
* Even if DATETIME and TIMESTAMP return the same format, they work very differently. In an INSERT or UPDATE query, the TIMESTAMP automatically set itself to the current date and time. TIMESTAMP also accepts various formats, like YYYYMMDDHHMISS, YYMMDDHHMISS, YYYYMMDD, or YYMMDD.
===== SQL Server Data Types =====
String types:
Data type Description Storage
char(n) Fixed width character string. Maximum Defined width
8,000 characters
varchar(n) Variable width character string. Maximum 2 bytes + number
8,000 characters of chars
varchar(max) Variable width character string. Maximum 2 bytes + number
1,073,741,824 characters of chars
text Variable width character string. Maximum 4 bytes + number
2GB of text data of chars
nchar Fixed width Unicode string. Maximum 4,000 Defined width x 2
characters
nvarchar Variable width Unicode string. Maximum
4,000 characters
nvarchar(max) Variable width Unicode string. Maximum
536,870,912 characters
ntext Variable width Unicode string. Maximum
2GB of text data
bit Allows 0, 1, or NULL
binary(n) Fixed width binary string. Maximum 8,000
bytes
varbinary Variable width binary string. Maximum
8,000 bytes
varbinary(max) Variable width binary string. Maximum 2GB
image Variable width binary string. Maximum 2GB
Number types:
Data type Description Storage
tinyint Allows whole numbers from 0 to 255 1 byte
smallint Allows whole numbers between -32,768 and 32,767 2 bytes
int Allows whole numbers between -2,147,483,648 and 4 bytes
2,147,483,647
Allows whole numbers between
bigint -9,223,372,036,854,775,808 and 8 bytes
9,223,372,036,854,775,807
Fixed precision and scale numbers.
Allows numbers from -10^38 +1 to 10^38 –1.
The p parameter indicates the maximum total
number of digits that can be stored (both to the
decimal(p,s) left and to the right of the decimal point). p 5-17 bytes
must be a value from 1 to 38. Default is 18.
The s parameter indicates the maximum number of
digits stored to the right of the decimal point.
s must be a value from 0 to p. Default value is
0
Fixed precision and scale numbers.
Allows numbers from -10^38 +1 to 10^38 –1.
The p parameter indicates the maximum total
number of digits that can be stored (both to the
numeric(p,s) left and to the right of the decimal point). p 5-17 bytes
must be a value from 1 to 38. Default is 18.
The s parameter indicates the maximum number of
digits stored to the right of the decimal point.
s must be a value from 0 to p. Default value is
0
smallmoney Monetary data from -214,748.3648 to 214,748.3647 4 bytes
money Monetary data from -922,337,203,685,477.5808 to 8 bytes
922,337,203,685,477.5807
Floating precision number data from -1.79E + 308
to 1.79E + 308.
float(n) The n parameter indicates whether the field 4 or 8 bytes
should hold 4 or 8 bytes. float(24) holds a
4-byte field and float(53) holds an 8-byte
field. Default value of n is 53.
real Floating precision number data from -3.40E + 38 4 bytes
to 3.40E + 38
Date types:
Data type Description Storage
datetime From January 1, 1753 to December 31, 9999 with 8 bytes
an accuracy of 3.33 milliseconds
datetime2 From January 1, 0001 to December 31, 9999 with 6-8 bytes
an accuracy of 100 nanoseconds
smalldatetime From January 1, 1900 to June 6, 2079 with an 4 bytes
accuracy of 1 minute
date Store a date only. From January 1, 0001 to 3 bytes
December 31, 9999
time Store a time only to an accuracy of 100 3-5 bytes
nanoseconds
datetimeoffset The same as datetime2 with the addition of a 8-10 bytes
time zone offset
Stores a unique number that gets updated every
time a row gets created or modified. The
timestamp timestamp value is based upon an internal clock
and does not correspond to real time. Each table
may have only one timestamp variable
Other data types:
Data type Description
sql_variant Stores up to 8,000 bytes of data of various data types,
except text, ntext, and timestamp
uniqueidentifier Stores a globally unique identifier (GUID)
xml Stores XML formatted data. Maximum 2GB
cursor Stores a reference to a cursor used for database
operations
table Stores a result-set for later processing
===== SQL Server Date Functions =====
The following table lists the most important built-in date functions in
SQL Server:
Function Description GETDATE() Returns the current date and time
DATEPART() Returns a single part of a date/time DATEADD() Adds or
subtracts a specified time interval from a date DATEDIFF() Returns the
time between two dates CONVERT() Displays date/time data in different
formats
===== SQL Date and Time Data Types and Functions =====
Function Description FORMAT() Formats how a field is to be displayed
NOW() Returns the current system date and time
===== SQL Dates =====
The most difficult part when working with dates is to be sure that the
format of the date you are trying to insert, matches the format of the
date column in the database.
As long as your data contains only the date portion, your queries will
work as expected. However, if a time portion is involved, it gets more
complicated.
===== SQL Date Data Types =====
MySQL comes with the following data types for storing a date or a
date/time value in the database:
* DATE - format YYYY-MM-DD
* DATETIME - format: YYYY-MM-DD HH:MI:SS
* TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
* YEAR - format YYYY or YY
SQL Server comes with the following data types for storing a date or a
date/time value in the database:
* DATE - format YYYY-MM-DD
* DATETIME - format: YYYY-MM-DD HH:MI:SS
* SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS
* TIMESTAMP - format: a unique number
Note: The date types are chosen for a column when you create a new table
in your database\!
For an overview of all data types available, go to our complete Data
Types reference.
===== SQL Working with Dates =====
You can compare two dates easily if there is no time component
involved\!
Assume we have the following “Orders” table:
OrderId ProductName OrderDate
1 Geitost 2008-11-11
2 Camembert Pierrot 2008-11-09
3 Mozzarella di Giovanni 2008-11-11
4 Mascarpone Fabioli 2008-10-29
Now we want to select the records with an OrderDate of “2008-11-11” from
the table above.
We use the following SELECT statement:
SELECT * FROM Orders WHERE OrderDate='2008-11-11'
The result-set will look like this:
OrderId ProductName OrderDate
1 Geitost 2008-11-11
3 Mozzarella di Giovanni 2008-11-11
Now, assume that the “Orders” table looks like this (notice the time
component in the “OrderDate” column):
OrderId ProductName OrderDate
1 Geitost 2008-11-11 13:23:44
2 Camembert Pierrot 2008-11-09 15:45:21
3 Mozzarella di Giovanni 2008-11-11 11:12:01
4 Mascarpone Fabioli 2008-10-29 14:56:59
If we use the same SELECT statement as above:
SELECT * FROM Orders WHERE OrderDate='2008-11-11'
we will get no result\! This is because the query is looking only for
dates with no time portion.
Tip: If you want to keep your queries simple and easy to maintain, do
not allow time components in your dates\!
===== SQL Aggregate Functions =====
SQL aggregate functions return a single value, calculated from values in
a column.
Function Description
AVG() Returns the average value
COUNT() Returns the number of rows
FIRST() Returns the first value
LAST() Returns the last value
MAX() Returns the largest value
MIN() Returns the smallest value
ROUND() Rounds a numeric field to the number of decimals specified
SUM() Returns the sum
===== SQL String Functions =====
Function Description
CHARINDEX Searches an expression in a string expression and
returns its starting position if found
CONCAT()
LEFT()
LEN() / LENGTH() Returns the length of the value in a text field
LOWER() / LCASE() Converts character data to lower case
LTRIM()
SUBSTRING() / MID() Extract characters from a text field
PATINDEX()
REPLACE()
RIGHT()
RTRIM()
UPPER() / UCASE() Converts character data to upper case
===== SQL ISNULL(), NVL(), IFNULL() and COALESCE() Functions =====
Look at the following “Products” table:
P_Id ProductName UnitPrice UnitsInStock UnitsOnOrder
1 Jarlsberg 10.45 16 15
2 Mascarpone 32.56 23
3 Gorgonzola 15.67 9 20
Suppose that the “UnitsOnOrder” column is optional, and may contain NULL
values.
We have the following SELECT statement:
SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder)
FROM Products
In the example above, if any of the “UnitsOnOrder” values are NULL, the
result is NULL.
Microsoft’s ISNULL() function is used to specify how we want to treat
NULL values.
The NVL(), IFNULL(), and COALESCE() functions can also be used to achieve
the same result.
In this case we want NULL values to be zero.
Below, if “UnitsOnOrder” is NULL it will not harm the calculation,
because ISNULL() returns a zero if the value is NULL:
MS Access
SELECT
ProductName,UnitPrice*(UnitsInStock+IIF(ISNULL(UnitsOnOrder),0,UnitsOnOrder))
FROM Products
SQL Server
SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))
FROM Products
Oracle
Oracle does not have an ISNULL() function. However, we can use the NVL()
function to achieve the same result:
SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0))
FROM Products
MySQL
MySQL does have an ISNULL() function. However, it works a little bit
different from Microsoft's ISNULL() function.
In MySQL we can use the IFNULL() function, like this:
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products
or we can use the COALESCE() function, like this:
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products
===== SQL Arithmetic Operators =====
Operator Description
+ Add
- Subtract
* Multiply
/ Divide
% Modulo
===== SQL Bitwise Operators =====
Operator Description
& Bitwise AND
OR
^ Bitwise exclusive OR
===== SQL Comparison Operators =====
Operator Description
= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
===== SQL Compound Operators =====
Operator Description
+= Add equals
-= Subtract equals
*= Multiply equals
/= Divide equals
%= Modulo equals
&= Bitwise AND equals
^-= Bitwise exclusive equals
equals
===== SQL Logical Operators =====
Note: ALL and ANY are not supported in Web SQL databases. Chrome, Safari
and Opera are using Web SQL in our examples.
Operator Description
ALL TRUE if all of the subquery values meet the condition
AND TRUE if all the conditions separated by AND is TRUE
ANY TRUE if any of the subquery values meet the condition
BETWEEN TRUE if the operand is within the range of comparisons
EXISTS TRUE if the subquery returns one or more records
IN TRUE if the operand is equal to one of a list of
expressions
LIKE TRUE if the operand matches a pattern
NOT Displays a record if the condition(s) is NOT TRUE
OR TRUE if any of the conditions separated by OR is TRUE
SOME TRUE if any of the subquery values meet the condition
===== SQL Quick Reference From W3Schools =====
* AND / OR
* ALTER TABLE
* AS (alias)
* BETWEEN
* CREATE DATABASE
* CREATE TABLE
* CREATE INDEX
* CREATE VIEW
* DELETE
* DROP DATABASE
* DROP INDEX
* DROP TABLE
* EXISTS
* GROUP BY
* HAVING
* IN
* INSERT INTO
* INNER JOIN
* LEFT JOIN
* RIGHT JOIN
* FULL JOIN
* LIKE
* ORDER BY
* SELECT
* SELECT \*
* SELECT DISTINCT
* SELECT INTO
* SELECT TOP
* TRUNCATE TABLE
* UNION
* UNION ALL
* UPDATE
* WHERE