Data types used in database and their length and usage of data types in database
Answers
For the most basic datatypes, nearly all DBMS either support the ANSI data types directly or at least allow to specify them.
The following statement:
create table products
(
product_id integer not null primary key,
product_name varchar(100),
price decimal(10,2)
);
works on nearly every DBMS. The data types will however be translated to the DBMS-specific ones (e.g. integer will be changed to number in Oracle, or varchar to varchar2).
When it comes to more "advanced" datatypes like CLOB, BLOB, XML, Json, or geospatial types then this doesn't hold true any more (CLOB, BLOB is pretty common, but e.g. not understood by Postgres although you can easily create a domain or a user data type with that name to make it more compatible).
The basic data types as defined by the ANSI standard are:
CHARACTER
CHARACTER VARYING (or VARCHAR)
CHARACTER LARGE OBJECT
NCHAR
NCHAR VARYING
BINARY
BINARY VARYING
BINARY LARGE OBJECT
NUMERIC
DECIMAL
SMALLINT
INTEGER
BIGINT
FLOAT
REAL
DOUBLE PRECISION
BOOLEAN
DATE
TIME
TIMESTAMP
INTERVAL
Not all DBMS suppor all of them (MySQL doesn't have an interval data type, MySQL and SQL Server don't have a real boolean type, Oracle does not have a boolean type at all or a real DATE or TIME data type, Postgres doesn't have nchar - that list could go on and on). But the most common ones usually work quite well.
From my experience, the following ones work without (major) problems across different DBMS (although that is my experience - this is by far not a complete or definite list!)
VARCHAR
INTEGER
DECIMAL
DATE (with surprises: Oracle has it but includes a time)
TIMESTAMP (does something different than expected on SQL Server and MySQL)
If you stick with those, you are pretty much "universal", but anything else will give you some (unpleasant) surprises when you go from one DBMS to another.