SQL Data Types(Numeric, String & Date)- Default Values- PL/SQL, Hive


— by

SQL (Structured Query Language) supports various data types to represent different kinds of data. These data types define the format and constraints of the data stored in each column of a table. Here are some common SQL data types:

  1. Numeric Types:
    • INT: Integer type, typically used for whole numbers.
    • FLOAT, REAL, DOUBLE PRECISION: Floating-point types, used for decimal numbers with varying precision.
    • DECIMAL(p, s), NUMERIC(p, s): Fixed-point types, used for exact decimal numbers with precision p and scale s.
  2. Character String Types:
    • CHAR(n): Fixed-length character string with length n.
    • VARCHAR(n), VARCHAR2(n), TEXT: Variable-length character string with maximum length n.
    • NCHAR(n), NVARCHAR(n): Unicode character string types, with fixed and variable lengths, respectively.
    • CLOB: Character large object, used for large text data.
  3. Date and Time Types:
    • DATE: Date type, representing a calendar date (year, month, day).
    • TIME: Time type, representing a time of day (hour, minute, second).
    • DATETIME, TIMESTAMP: Date and time combined, representing a specific point in time.
    • INTERVAL: Interval type, representing a duration of time.
  4. Boolean Type:
    • BOOLEAN, BOOL: Boolean type, representing true or false values.
  5. Binary Data Types:
    • BINARY(n): Fixed-length binary string with length n.
    • VARBINARY(n): Variable-length binary string with maximum length n.
    • BLOB: Binary large object, used for storing large binary data.
  6. Other Types:
    • ARRAY: Array type, used to store arrays of values.
    • JSON, JSONB: JSON data type, used to store JSON documents.
    • XML: XML data type, used to store XML documents.
    • ROW: Row type, used to represent a row of values.

These are some of the common SQL data types supported by most relational database management systems (RDBMS) like MySQL, PostgreSQL, Oracle, SQL Server, SQLite, etc. The exact set of supported data types may vary slightly between different database systems. Additionally, some database systems may provide additional custom or proprietary data types specific to their implementation.

String datatype is a generic term used for different string datatypes in the database. The most used string datatypes are CHAR, which stands for character. This datatype is used to hold characters of a fixed length. VARCHAR stands for variable character. This holds characters of the variable length. 

more commonly used examples of string datatypes. TINYTEXT is used to define columns that require less than 255 characters, like short paragraphs. TEXT is used to define columns of less than 65,000 characters, like an article. MEDIUMTEXT defined columns of 16.7 million characters. For example, the text of a book. The LONGTEXT datatype stores up to four gigabytes of text data. 

In SQL, you can specify default values for columns when defining a table. Default values are used to provide a predefined value for a column if no explicit value is specified during the insertion of a new row. Here’s how you can specify default values for different SQL data types:

  1. Numeric Types:
    • For numeric types such as INT, FLOAT, DECIMAL, etc., you can specify default numeric values.
    • Example: age INT DEFAULT 18
  2. Character String Types:
    • For character string types such as CHAR, VARCHAR, TEXT, etc., you can specify default string values.
    • Example: name VARCHAR(50) DEFAULT 'John'
  3. Date and Time Types:
    • For date and time types such as DATE, TIME, DATETIME, TIMESTAMP, etc., you can specify default date and time values.
    • Example: created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  4. Boolean Type:
    • For boolean types such as BOOLEAN, BOOL, you can specify default boolean values (TRUE or FALSE).
    • Example: is_active BOOLEAN DEFAULT TRUE
  5. Binary Data Types:
    • For binary data types such as BINARY, VARBINARY, BLOB, etc., you can specify default binary values.
    • Example: image BLOB DEFAULT NULL
  6. Other Types:
    • For other types such as ARRAY, JSON, XML, etc., you can specify default values appropriate for the respective data type.
    • Example: json_data JSON DEFAULT '{}'

Here are some important considerations when specifying default values:

  • Default values are applied only if no value is explicitly provided for the column during the insertion of a new row.
  • You can specify constants, expressions, or system functions (such as CURRENT_TIMESTAMP) as default values.
  • Some databases may have limitations on the types of default values that can be specified for certain data types.
  • You can modify the default value of a column using the ALTER TABLE statement.

Overall, default values provide a convenient way to ensure consistency and integrity in your database by automatically assigning predefined values to columns when necessary.

Apache Hive provides a variety of data types to handle different kinds of data. These data types can be categorized into several groups such as primitive data types, complex data types, and collection data types. Here’s a detailed overview of these data types in Hive QL:

1. Primitive Data Types

Primitive data types are the basic types of data that can hold a single value.

  • Numeric Types:
    • TINYINT: 1-byte integer, range -128 to 127
    • SMALLINT: 2-byte integer, range -32,768 to 32,767
    • INT: 4-byte integer, range -2,147,483,648 to 2,147,483,647
    • BIGINT: 8-byte integer, range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
    • FLOAT: 4-byte single-precision floating point
    • DOUBLE: 8-byte double-precision floating point
    • DECIMAL: Arbitrary precision numeric, e.g., DECIMAL(10,2) for fixed-point numbers
  • String Types:
    • STRING: Variable length string
    • VARCHAR: Variable length string with a specified maximum length, e.g., VARCHAR(20)
    • CHAR: Fixed length string, e.g., CHAR(10)
  • Date/Time Types:
    • TIMESTAMP: Date and time, with nanosecond precision
    • DATE: Date without time
    • INTERVAL: Represents a time interval, such as days or hours
  • Boolean Type:
    • BOOLEAN: True or false values
  • Binary Type:
    • BINARY: Variable length binary data

2. Complex Data Types

Complex data types can hold multiple values and are useful for handling more complex data structures.

  • ARRAY:
    • A collection of elements, all of the same type. For example, ARRAY<INT> for an array of integers.
    CREATE TABLE example (col1 ARRAY<STRING>);
  • MAP:
    • A collection of key-value pairs, where keys are unique. The key and value can be of different types. For example, MAP<STRING, INT> for a map with string keys and integer values.
    CREATE TABLE example (col1 MAP<STRING, INT>);
  • STRUCT:
    • A complex type that can contain multiple fields of different types. For example, STRUCT<name:STRING, age:INT> for a structure with a name and an age.
    CREATE TABLE example (col1 STRUCT<name:STRING, age:INT>);
  • UNIONTYPE:
    • A type that can store one of several types. For example, UNIONTYPE<INT, DOUBLE, STRING> can hold either an integer, a double, or a string.
    CREATE TABLE example (col1 UNIONTYPE<INT, DOUBLE, STRING>);

3. Collection Data Types

Hive also supports collection data types to store multiple values of a single data type.

  • LIST:
    • Synonym for ARRAY. A collection of elements, all of the same type.
    CREATE TABLE example (col1 LIST<STRING>);

Examples of Creating Tables with Various Data Types

Example 1: Table with Primitive Data Types

CREATE TABLE employees (
employee_id INT,
name STRING,
age INT,
salary DOUBLE,
hire_date DATE,
is_active BOOLEAN
);

Example 2: Table with Complex Data Types

CREATE TABLE company (
company_id INT,
name STRING,
employees ARRAY<STRUCT<name:STRING, age:INT>>,
properties MAP<STRING, STRING>
);

Example 3: Table with Collection Data Types

CREATE TABLE books (
book_id INT,
title STRING,
authors ARRAY<STRING>,
info MAP<STRING, STRING>
);

Discover more from AI HintsToday

Subscribe to get the latest posts sent to your email.

Newsletter

Our latest updates in your e-mail.

Comments


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Discover more from AI HitsToday

Subscribe now to keep reading and get access to the full archive.

Continue reading