SQL data types

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  *  FROM Table;


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 type Data type Explanation
Integer INTEGER The integer data type is used to specify an integer value
Smallint SMALLINT The smallint data type is used to specify small integer value
Numeric NUMERIC(P,S) The smallint data type is used to specify small integer value
Real REAL The real integer is used to specify a single precision floating point number
Decimal DECIMAL(P,S) It specifies a decimal value. Here 'p' is precision value and’s’ is scale value.
Double precision DOUBLE PRECISION It specifies double precision floating point number
Float FLOAT(P) It specifies floating-point value e.g. 12.3, 4.5 etc. Here, 'p' is precision value.
Character CHAR(X) Here, 'x' is the character's number to store
Character Varying VARCHAR(X) Here, 'x' is the character's number to store
Bit BIT (X) Here, 'x' is the number of bits to store
Bit Varying BIT VARYING (X) Here, 'x' is the number of bits to store (length can vary up to x)
Date DATE It stores year, month and days values.
Time TIME It stores hour, minute and second values
Time with time zone TIME WITH TIME ZONE The timestamp data type is used to store year, month, day, and hour, minute and second values
Timestamp TIMESTAMP It is exactly same as time but also store an offset from UTC of the time specified.
Timestamp with time zone TIMESTAMP WITH TIME ZONE It is same as timestamp but also stores an offset from UTC of the time specified