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:

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>
);