Learning of SQL Day 4

 

Day 4: Data Types in SQL

Introduction:

• Data types define the type of data that can be stored in a table's column. They ensure data integrity and efficient data management.

Key Concepts:

• Numeric Data Types: Represent numbers.

• String Data Types: Represent text.

• Date and Time Data Types: Represent dates and times.

• Binary Data Types: Represent binary data such as images.

• Miscellaneous Data Types: Include boolean and other specialized types.

SQL Commands and Examples:

1. Numeric Data Types:

   • INT: Integer numbers.

   • FLOAT: Floating-point numbers.

   • DECIMAL: Exact decimal numbers.

sql

-- Creating a table with numeric data types
CREATE TABLE NumericExample (
    ID INT,
    Score FLOAT,
    Salary DECIMAL(10, 2)
);

2. String Data Types:

   • CHAR: Fixed-length character data.

   • VARCHAR: Variable-length character data.

   • TEXT: Large amount of text data.

sql

-- Creating a table with string data types
CREATE TABLE StringExample (
    Name VARCHAR(255),
    Description TEXT
);

3. Date and Time Data Types:

   • DATE: Date values.

   • TIME: Time values.

   • DATETIME: Date and time values.

sql

-- Creating a table with date and time data types
CREATE TABLE DateTimeExample (
    EventDate DATE,
    EventTime TIME,
    EventTimestamp DATETIME
);

4. Binary Data Types:

   • BINARY: Fixed-length binary data.

   • VARBINARY: Variable-length binary data.

   • BLOB: Large binary objects.

sql

-- Creating a table with binary data types
CREATE TABLE BinaryExample (
    Image BLOB
);

5. Miscellaneous Data Types:

   • BOOLEAN: True or false values.

   • ENUM: Enumeration, a set of predefined values.

sql

-- Creating a table with miscellaneous data types
CREATE TABLE MiscExample (
    IsActive BOOLEAN,
    Status ENUM('Active', 'Inactive', 'Pending')
);

Practice Exercise:

1. Create a table named Employee with columns for EmployeeID (INT), FirstName (VARCHAR), LastName (VARCHAR), DateOfBirth (DATE), Salary (DECIMAL), and IsActive (BOOLEAN).

2. Insert sample data into the Employee table.

3. Retrieve and display the data to ensure the correct data types are being used.

Understanding data types is crucial for defining tables and ensuring data integrity.