Data Types

Data types define the data format stored in a database and determine how that data can be used and processed. What Are the Different Types of Data? Different data types have distinct properties with particular usages and possibilities of manipulation. For example, text and numeric have very different properties and require other methods to process and analyze. The SQL data types vary slightly depending on the database management system (DBMS). However, some of the typical data types in SQL include:

These are the Logical Operators:

• • Numeric: INT, BIGINT, DECIMAL, NUMERIC, FLOAT, REAL, SMALLINT, TINYINT
  • Character: CHAR, VARCHAR, TEXT
  • Date/time: DATE, TIME, DATETIME, TIMESTAMP

• Binary: BINARY, VARBINARY, BLOB
• Other data types: BOOLEAN, ARRAY, JSON, XML

Let’s take a closer look at some of the most common data types you’re likely to encounter as a data analyst. Integer Types Integers store whole numbers that have no fractional component.

• TINYINT: stores values between -128 and 127 (or 0 and 255 if unsigned)
• SMALLINT: stores values between -32,768 and 32,767 (or 0 and 65,535 if unsigned)
• INT: stores values between -2,147,483,648 and 2,147,483,647 (or 0 and 4,294,967,295 if unsigned)
• BIGINT: stores values between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 (or 0 and 18,446,744,073,709,551,615 if unsigned).

Floating-Point Types The floating-point data type store numeric values with a decimal component. It is helpful for scientific and mathematical values that require high precision and a wide range of possible values.

• FLOAT: stores single-precision floating-point values with a precision of 7 digits
• DOUBLE: stores double-precision floating-point values with a precision of 15 digits.

Character Types Characters store strings of text or character data.

• CHAR: stores fixed-length character strings of a specified length
• VARCHAR: stores variable-length character strings of up to a specified length.

Date and Time Types SQL supports several variations of date and time data types, including DATE, TIME, DATETIME, and TIMESTAMP, each with different characteristics and capabilities.

• DATE: YYYY-MM-DD
• TIME: HH:MM:SS
• DATETIME: YYYY-MM-DD HH:MM:SS
• TIMESTAMP: YYYY-MM-DD HH:MM:SS[.fraction] – fraction represents the fractional component which allows for nanosecond-level precision.

Boolean Types Boolean is true/false values for logical comparisons and conditions. Most programming languages and database systems have built-in boolean types, typically represented as TRUE and FALSE.

Most database platforms perform implicit type casting, automatically converting data types as appropriate for various functions. For example, the following three queries will convert the string value ‘2023-01-01’ to a DATE data type for use with the > comparison operator (function) to return all orders since the beginning of year 2023.

SELECT * FROM orders WHERE order_date >= CAST('2023-01-01' AS DATE);

SELECT * FROM orders WHERE order_date >= '2023-01-01'::DATE;

SELECT * FROM orders WHERE order_date >= '2023-01-01';

In cases where implicit type casting would be ambiguous (comparing a date to a timestamp, for example), explicit type casting should be used, as in the first two queries above.