Meritshot Tutorials

  1. Home
  2. »
  3. SQL Data Types Overview
SQL

SQL Tutorial

SQL Data Types Overview

SQL data types define the nature of data that can be stored in a column of a database table. Choosing the appropriate data type is crucial for data integrity, performance, and efficient storage. SQL databases provide a variety of data types to handle different kinds of data, including numeric, character, date/time, and binary data.

Common SQL Data Types

  1. Numeric Data Types
  • INT / INTEGER: Stores whole numbers.
    • Example: INT can store values like 1, 100, -45.
  • FLOAT: Stores floating-point numbers with decimal places.
    • Example: FLOAT can store values like 3.14, -0.007.
  • DECIMAL / NUMERIC: Stores fixed-point numbers with a specified precision and scale.
    • Example: DECIMAL(10,2) can store values like 12345.67.
  • BIGINT: Stores large integers.
    • Example: BIGINT can store values like 9223372036854775807.
  1. Character Data Types
  • CHAR(n): Stores fixed-length character strings. If the string is shorter than n, it is padded with spaces.
    • Example: CHAR(10) can store John Doe (with padding).
  • VARCHAR(n): Stores variable-length character strings. The length can be up to n.
    • Example: VARCHAR(50) can store John Doe without padding.
  • TEXT: Stores large text data.
    • Example: TEXT can store long paragraphs or documents.
  1. Date and Time Data Types
  • DATE: Stores date values (year, month, day).
    • Example: DATE can store 2024-08-29.
  • TIME: Stores time values (hour, minute, second).
    • Example: TIME can store 15:30:00.
  • DATETIME / TIMESTAMP: Stores both date and time values.
    • Example: DATETIME can store 2024-08-29 15:30:00.
  1. Binary Data Types
  • BINARY(n): Stores fixed-length binary data.
    • Example: BINARY(16) can store a 16-byte binary value.
  • VARBINARY(n): Stores variable-length binary data.
    • Example: VARBINARY(255) can store binary data up to 255 bytes.
  • IMAGE: Stores large binary data (e.g., images or files).
    • Example: IMAGE can store files like .jpg, .png.
  1. Other Data Types
  • BOOLEAN: Stores true or false values.
    • Example: BOOLEAN can store TRUE or FALSE.
  • ENUM: Stores one value from a predefined list of values (used in MySQL).
    • Example: ENUM(‘Red’, ‘Green’, ‘Blue’) can store Red.

Example:

1 Creating a Table with Various Data Types

CREATE TABLE Employees (

    EmployeeID INT PRIMARY KEY,

    FullName VARCHAR(100),

    HireDate DATE,

    Salary DECIMAL(10, 2),

    IsActive BOOLEAN

);

  • EmployeeID: An integer to uniquely identify each employee.
  • FullName: A variable-length character string for the employee’s name.
  • HireDate: A date value for when the employee was hired.
  • Salary: A fixed-point number with two decimal places for the employee’s salary.
  • IsActive: A boolean value to indicate if the employee is currently active.

2 Inserting Data into the Table

INSERT INTO Employees (EmployeeID, FullName, HireDate, Salary, IsActive)

VALUES (1, ‘Amit Sharma’, ‘2024-08-29’, 50000.00, TRUE);

3 Querying Data from the Table

SELECT EmployeeID, FullName, HireDate, Salary, IsActive

FROM Employees

WHERE IsActive = TRUE;

Tips to Remember

  1. Choose Appropriate Data Types: Select data types that match the nature of the data and the expected size to optimize storage and performance.
  2. Consider Precision and Scale: For numeric data, consider precision and scale requirements to ensure accurate calculations and storage.
  3. Avoid Using TEXT for Short Strings: Use VARCHAR for variable-length strings to avoid unnecessary storage overhead for short strings.

Frequently Asked Questions

Q1: Can I change the data type of an existing column?
A1: Yes, you can use the ALTER TABLE statement to modify a column’s data type. Be cautious, as this may impact existing data.

Q2: What happens if I insert data that doesn’t match the column’s data type?
A2: The database will either reject the data with an error or perform implicit conversion if possible, which might lead to unexpected results.

Q3: Are there any limitations on the size of VARCHAR columns?
A3: Yes, there are limits depending on the database system. For example, MySQL supports VARCHAR up to 65,535 bytes, while other systems may have different limits.

Q4: Can I use BOOLEAN data type in all SQL databases?
A4: Not all databases have a native BOOLEAN data type. Some databases use TINYINT or other types to represent boolean values.

Q5: How should I handle large amounts of text data?
A5: Use TEXT or equivalent data types designed for large text storage, depending on the database system you’re using.

❮ Previous
Next ❯
ads website