SQL language (General)

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.

  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

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\!

  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

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.

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

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

Function Description FORMAT() Formats how a field is to be displayed NOW() Returns the current system date and time

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.

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.

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 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

  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

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

  Operator                Description
  +                       Add
  -                       Subtract
  *                       Multiply
  /                       Divide
  %                       Modulo

  Operator                 Description
  &                        Bitwise AND
   OR
  ^                        Bitwise exclusive OR

  Operator Description
  =        Equal to
  >        Greater than
  <        Less than
  >=       Greater than or equal to
  <=       Less than or equal to
  <>       Not equal to

  Operator Description
  +=       Add equals
  -=       Subtract equals
  *=       Multiply equals
  /=       Divide equals
  %=       Modulo equals
  &=       Bitwise AND equals
  ^-=      Bitwise exclusive equals
   equals

  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

• 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