SQL Pocket Guide, 4th Edition

Chapter 6. Data Types

In a SQL table, each column can only include values of a single data type. This chapter covers commonly used data types, as well as how and when to use them.

The following statement specifies three columns along with the data type for each column: id holds integer values, name holds values containing up to 30 characters, and dt holds date values:

CREATE TABLE my_table (
   id INT,
   name VARCHAR(30),
   dt DATE
);

INT, VARCHAR, and DATE are just three of the many data types in SQL. Table 6-1 lists four categories of data types, along with common subcategories. Data type syntax varies widely by RDBMS, and the differences are detailed out in each section of this chapter.

Table 6-1. Data types in SQL

Numeric	String	Datetime	Other
Integer (123) Decimal (1.23) Floating Point (1.23e10)	Character ('hello') Unicode ('西瓜')	Date ('2021-12–01') Time ('2:21:00') Datetime ('2021-12-01 2:21:00')	Boolean (TRUE) Binary (images, documents, etc.)

Table 6-2 lists example values of each data type to show how they are represented in SQL. These values are often referred to as literals or constants.

Table 6-2. Literals in SQL

Category	Subcategory	Example Values
Numeric	Integer	123 +123 -123
	Decimal	123.45 +123.45 -123.45
	Floating Point	123.45E+23 123.45e−23
String	Character	'Thank you!' 'The combo is 39-6-27.'
	Unicode	N'Amélie' N'♥♥♥'
Datetime	Date	'2022-10-15' '15-OCT-2022' (Oracle)
	Time	'10:30:00' '10:30:00.123456' '10:30:00 -6:00'
	Datetime	'2022-10-15 ...