WHAT IS Set Data Type in SQL?

In SQL, a Set data type is a collection of unique values that are unordered and do not have any duplicates. It is a useful data type when dealing with a group of values where the order does not matter, and each value should only appear once.

Creating a Set Data Type

To create a set data type in SQL, there are certain syntaxes you need to follow. Here is an example:

CREATE TYPE FruitSet AS TABLE OF VARCHAR2(20);

In the above example, we created a set data type named FruitSet, which can store up to 20 characters of fruit names. You can replace the maximum length with your desired value based on your requirements.

Using Set Data Type

Once you have created the set data type, you can use it in various ways within your SQL queries. Here are some common scenarios:

1. Declaring Variables

You can declare variables of the set data type to store multiple values. For example:

DECLARE
   fruits FruitSet;
BEGIN
   fruits := FruitSet('Apple', 'Banana', 'Orange');
END;

In this example, we declared a variable named fruits of the FruitSet type and assigned three fruit names to it using the constructor method.

2. Using Set Data Type in Table Columns

You can also use the set data type as a column in your database tables. This is helpful when you want to store multiple values associated with each row. Here is an example:

CREATE TABLE Orders
(
   order_id   NUMBER,
   fruits     FruitSet
);

In this example, we created a table named Orders with two columns – order_id of type NUMBER and fruits of type FruitSet. Each row in the table can have multiple fruit values associated with it.

Set Operations on Set Data Type

The set data type allows you to perform various operations to manipulate and retrieve data. Here are some commonly used set operations:

1. Union

The union operation combines two sets and returns a new set that contains all the unique elements from both sets. For example:

SELECT FruitSet('Apple', 'Banana') UNION SELECT FruitSet('Orange', 'Mango') FROM DUAL;

This query will return a set containing four elements: Apple, Banana, Orange, and Mango. Intersection

The intersection operation returns a new set that contains only the common elements between two sets. For example:

SELECT FruitSet('Apple', 'Banana') INTERSECT SELECT FruitSet('Banana', 'Orange') FROM DUAL;

This query will return a set containing one element: Banana.

3. Difference

The difference operation returns a new set that contains the elements from the first set that are not present in the second set. For example:

SELECT FruitSet('Apple', 'Banana') MINUS SELECT FruitSet('Banana', 'Orange') FROM DUAL;

This query will return a set containing one element: Apple.

Conclusion

The set data type in SQL is a powerful tool for dealing with collections of unique values. It allows you to store, manipulate, and retrieve sets of data efficiently. By understanding how to create and use the set data type, you can enhance your SQL queries and improve the organization and efficiency of your databases.