SQL data types with example

To perform operations on the database we have syntax for SQL. SQL syntax has some rules to be followed

SQL uses keywords like SELECT, CREATE, INSERT, etc.., and is not case sensitive. Mostly they are written in Uppercase.

SQL queries are called as SQL statements and they can be written on one line or multiple lines

SQL Statements are generally started with SQL keywords like SELECT, INSERT, UPDATE, etc.., and should be ended with a semicolon (;).

 

Example of an SQL Statement

 

SELECT  *  FROMTable;


The semicolon is used to separate the SQL statements. It must be used according to SQL standards

 

SQL Commands
These are some important commands that we use generally.
 

  • CREATE
  • SELECT
  •  INSERT
  •  DELETE
  •  ALTER
  •  DROP
  •  UPDATE

 

SQL Datatypes

SQ data types define a type of value that is to be inserted into the database.
Every column in the Table should be provided with name and data type
Data types differ from Database to Database.


General Data types used in SQL are:


Data type Syntax Explanation

 

Data typeData typeExplanation
IntegerINTEGERThe integer data type is used to specify an integer value
SmallintSMALLINTThe smallint data type is used to specify small integer value
NumericNUMERIC(P,S)The smallint data type is used to specify small integer value
RealREALThe real integer is used to specify a single precision floating point number
DecimalDECIMAL(P,S)It specifies a decimal value. Here 'p' is precision value and’s’ is scale value.
Double precisionDOUBLE PRECISIONIt specifies double precision floating point number
FloatFLOAT(P)It specifies floating-point value e.g. 12.3, 4.5 etc. Here, 'p' is precision value.
CharacterCHAR(X)Here, 'x' is the character's number to store
Character VaryingVARCHAR(X)Here, 'x' is the character's number to store
BitBIT (X)Here, 'x' is the number of bits to store
Bit VaryingBIT VARYING (X)Here, 'x' is the number of bits to store (length can vary up to x)
DateDATEIt stores year, month and days values.
TimeTIMEIt stores hour, minute and second values
Time with time zoneTIME WITH TIME ZONEThe timestamp data type is used to store year, month, day, and hour, minute and second values
TimestampTIMESTAMPIt is exactly same as time but also store an offset from UTC of the time specified.
Timestamp with time zoneTIMESTAMP WITH TIME ZONEIt is same as timestamp but also stores an offset from UTC of the time specified