SQL Data Types 🎯

Welcome to the SQL Data Types tutorial at CodeYourCraft! In this lesson, we'll explore the fundamental data types used in SQL, understand their properties, and learn how to use them effectively in your SQL queries. Let's dive right in! 💡

What are SQL Data Types? 📝

SQL data types are categories that define the type and format of data that can be stored in a database table. Understanding SQL data types is essential for creating efficient and accurate SQL queries.

Basic SQL Data Types 📝

1. Integer (INT) 💡

An integer is a whole number, positive or negative, without decimals. In SQL, you can use the INT data type to store integers.

sql

-- Create a table with an integer column
CREATE TABLE ExampleTable (
    id INT
);

-- Insert an integer value
INSERT INTO ExampleTable (id) VALUES (1);

📝 Note: SQL automatically converts small integers (like INT, SMALLINT, TINYINT) to the same data type, so you don't have to worry about choosing the right one for small numbers.

2. Decimal (DECIMAL) 💡

A decimal is a number with a fractional part. In SQL, the DECIMAL data type allows you to store decimal numbers with a specific precision and scale.

sql

-- Create a table with a decimal column
CREATE TABLE ExampleTable (
    price DECIMAL(5, 2)
);

-- Insert a decimal value
INSERT INTO ExampleTable (price) VALUES (12.34);

📝 Note: In the example above, (5, 2) means the decimal can have up to 5 digits in total, and 2 digits after the decimal point.

3. Character (CHAR) 💡

A character is a sequence of one or more alphanumeric characters. In SQL, the CHAR data type is used to store fixed-length character strings.

sql

-- Create a table with a character column
CREATE TABLE ExampleTable (
    name CHAR(10)
);

-- Insert a character value
INSERT INTO ExampleTable (name) VALUES ('John');

📝 Note: When storing characters with the CHAR data type, you must specify the exact length of the string. If the string is shorter, it will be padded with spaces.

4. VarChar (VARCHAR) 💡

A variable character (VARCHAR) is a sequence of one or more alphanumeric characters. In SQL, the VARCHAR data type is used to store variable-length character strings.

sql

-- Create a table with a variable character column
CREATE TABLE ExampleTable (
    name VARCHAR(20)
);

-- Insert a variable character value
INSERT INTO ExampleTable (name) VALUES ('John Doe');

📝 Note: Unlike the CHAR data type, the VARCHAR data type automatically adjusts the storage space based on the length of the string.

Advanced SQL Data Types 💡

1. Date and Time 💡

SQL provides several data types to store dates and times:

DATE: Represents a date (year, month, day).
TIME: Represents a time of day (hours, minutes, seconds, fractional seconds).
DATETIME: Combines both date and time.

sql

-- Create a table with a datetime column
CREATE TABLE ExampleTable (
    date DATETIME
);

-- Insert a datetime value
INSERT INTO ExampleTable (date) VALUES ('2022-01-01 12:00:00');

📝 Note: When working with dates and times, it's essential to understand SQL functions like CURDATE(), NOW(), and DATE_FORMAT() for formatting and manipulating date and time values.

2. Binary and Large Objects 💡

SQL provides data types for storing binary data (like images, audio, and video) and large objects (like text documents).

BLOB (Binary Large OBject): Used to store large binary files.
TEXT: Used to store large text documents.

sql

-- Create a table with a blob column
CREATE TABLE ExampleTable (
    image BLOB
);

-- Insert a binary value
INSERT INTO ExampleTable (image) VALUES (0x00110203...);

📝 Note: To insert a binary file into a database, you'll need to convert the file into its hexadecimal representation (0x00110203...).

Quiz 💡

That's it for our SQL Data Types tutorial! Now you're well-equipped to work with various data types in SQL. Keep practicing, and remember to check out our other tutorials for more SQL goodness at CodeYourCraft. Happy coding! ✅