In SQL, a composite data type is a type that can hold multiple values within it. It is also referred to as a structured data type or a complex data type. Unlike simple data types, which can only hold single values, composite data types allow you to store multiple values in a single column of a table or variable.
Types of Composite Data Types
There are several types of composite data types in SQL:
1. Structured Types
A structured type is a user-defined composite data type that represents a structure or record.
It consists of multiple attributes, each with its own name and data type. Structured types are useful when you want to store related information together.
2. Arrays
An array is an ordered collection of elements with the same data type.
It allows you to store multiple values of the same data type in a single column or variable. Arrays are useful when you want to store multiple similar values, such as a list of names or a series of numeric values.
3. Multi-dimensional Arrays
A multi-dimensional array is an array with more than one dimension.
It allows you to store values in rows and columns, forming a matrix-like structure. Multi-dimensional arrays are useful when you want to represent tabular data or matrices.
Working with Composite Data Types
To work with composite data types in SQL, you need to define the structure and characteristics of the composite type using the appropriate syntax.
1. Defining Structured Types
To define a structured type, you use the CREATE TYPE statement followed by the name of the structured type and its attributes:
CREATE TYPE employee_type AS (
id INT,
name VARCHAR(50),
salary DECIMAL(10,2)
);
In the example above, we define a structured type called employee_type with three attributes: id, name, and salary.
2. Defining Arrays
To define an array, you use the square brackets ([]) after the data type of the array:
CREATE TABLE students (
id INT,
subjects VARCHAR(50)[]
);
In the example above, we define a table called students with two columns: id and subjects. The subjects column is an array of strings.
Using Composite Data Types in Queries
You can use composite data types in SQL queries to retrieve, insert, update, and delete data.
Selecting Data from Structured Types:
To select data from structured types, you use dot notation to access the attributes of the composite type:
SELECT employee.name, employee.salary
FROM employees employee;
In the example above, we select the name and salary attributes from the employee_type.
Selecting Data from Arrays:
To select data from arrays, you can use functions like UNNEST():
SELECT student.id, subject
FROM students,
UNNEST(students.subjects) AS subject;
In the example above, we use the UNNEST() function to unnest the subjects array and retrieve each subject as a separate row.
In conclusion, composite data types in SQL allow you to store and manipulate multiple values within a single column or variable. They provide a powerful way to organize and manage structured data. By understanding the different types of composite data types and how to work with them, you can enhance your SQL skills and create more efficient and flexible database structures.