Home » Courses » SQL Crash Course

SQL Data Types

Lesson 3: SQL Data Types

In SQL, data types define the type of data that can be stored in a column of a table. Choosing the right data type for each column is essential for efficient data storage and retrieval. In this lesson, we will cover the most common SQL data types, their usage, and provide examples.

1. What are SQL Data Types?

SQL data types are used to specify the kind of data that can be stored in a database table’s column. They define the nature and size of the data in a column, ensuring that the data is stored in a way that fits its type.

2. Common SQL Data Types

1. Numeric Data Types

Numeric data types are used to store numbers. There are several types, including integers and decimals:

INT: Stores integer values. Example: 1, 25, -100.
DECIMAL: Stores numbers with decimals. Example: 10.75, 100.5.
FLOAT: Stores approximate numeric values. Example: 3.14, -10.9876.

Example – Numeric Data Types


-- Creating a table with numeric columns
CREATE TABLE Employees (
  EmployeeID INT,
  Salary DECIMAL(10, 2),
  Bonus FLOAT
);

2. String Data Types

String data types are used to store textual data. Common string types include:

VARCHAR: Stores variable-length strings. Example: “John”, “Alice”.
CHAR: Stores fixed-length strings. Example: “M” for Male, “F” for Female.
TEXT: Used for larger text fields. Example: “Lorem ipsum dolor sit amet…”.

Example – String Data Types


-- Creating a table with string columns
CREATE TABLE Customers (
  CustomerID INT,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  Address TEXT
);

3. Date and Time Data Types

These data types are used to store date and time values:

DATE: Stores only the date (year, month, and day). Example: 2024-04-25.
TIME: Stores only the time (hours, minutes, seconds). Example: 14:30:00.
DATETIME: Stores both date and time. Example: 2024-04-25 14:30:00.
TIMESTAMP: Similar to DATETIME but automatically updates when a record is modified.

Example – Date and Time Data Types


-- Creating a table with date and time columns
CREATE TABLE Events (
  EventID INT,
  EventName VARCHAR(100),
  EventDate DATE,
  EventTime TIME
);

4. Boolean Data Type

The Boolean data type is used to store TRUE or FALSE values:

BOOLEAN: Stores a value of TRUE or FALSE. Example: TRUE, FALSE.

Example – Boolean Data Type


-- Creating a table with a boolean column
CREATE TABLE Users (
  UserID INT,
  IsActive BOOLEAN
);

5. Binary Data Types

Binary data types are used to store binary data (such as images, files, etc.). The most common binary data types are:

BLOB: Stores binary data. Example: images, video files, etc.
VARBINARY: Stores variable-length binary data.

Example – Binary Data Type


-- Creating a table with binary data column
CREATE TABLE Files (
  FileID INT,
  FileData BLOB
);

3. Choosing the Right Data Type

When designing a database, it’s important to choose the appropriate data type for each column. This ensures that the data is stored efficiently and accurately. Here’s a guideline for choosing the right data type:

Use INT for integer values that don’t require decimals.
Use VARCHAR for variable-length strings, such as names and addresses.
Use DECIMAL for storing prices, salaries, or financial figures.
Use DATE for storing birth dates, hire dates, etc.
Use BOOLEAN for flags, like “is_active”.

4. Practice Exercise

Try the following SQL statements to test your knowledge of SQL data types:

Create a table called Products with the following columns: ProductID (INT), ProductName (VARCHAR), Price (DECIMAL), InStock (BOOLEAN).
Insert a product into the Products table with values: ProductID = 1, ProductName = ‘Laptop’, Price = 799.99, InStock = TRUE.
Retrieve all products from the Products table where the price is greater than 500.

Conclusion

Choosing the correct SQL data types is crucial for building efficient databases. Understanding the different types of data you can store, and knowing when to use them, will help you design better database structures. Keep practicing with real-world examples, and you’ll master SQL data types in no time!

SQL Crash Course