Database Systems with SQL - Full Course - Database Systems - Collection Data Types - Set Multiset List and Array - APPFICIAL

There are 4 collection types Set – no repeated values, not ordered Multiset – can be repeated, not ordered List – can be repeated, ordered Array – an indexed list You should make sure whatever database you are using has support for these collection types before you try to use them. MySQL only supports sets. Oracle and PostgreSQL support arrays. Set The SET type i(for MySQL) is similar to the ENUM type because they both have a base type consisting of character strings., Each SET value may contain zero, one, or many elements, while each ENUM value must contain exactly one element. Create a SET type by using the keywords SET followed by the base type strings. Ex: SET (‘Green’, ‘Yellow’, ‘RED’) is the type for values of traffic lights You can have a SET with no elements (which is not the same as NULL) CREATE TABLE Person ( ID SMALLINT UNSIGNED AUTO_INCREMENT, Name VARCHAR(100), Sport SET (‘Baseball’, ‘Basketball’, ‘Football’, ‘Hockey’, ‘Soccer’), PRIMARY KEY (ID), ); INSERT INTO Person (Name, Sport) VALUES (‘Bob’, ‘Basketball, Hockey’ ), (’Mary’, ‘Basketball, ‘Soccer’ ); SELECT Name, Sport FROM Person WHERE Sport LIKE %Soccer%; // get list of everyone who likes soccer Array You can specify an array type by adding a pair of brackets with the array size to any base type. Ex: INTEGER[5]. The data is entered in curly braces, and each element is separated by a comma. Ex: { 1, 17, 79, 42, 7}. You can access any element in the array by the index in the square backets: CREATE TABLE Person ( ID SMALLINT UNSIGNED AUTO_INCREMENT, Name VARCHAR(100), FavoriteNumbers INTEGER[3] PRIMARY KEY (ID), ); INSERT INTO Person (Name, FavoriteNumbers) VALUES (‘Bob’, ‘{69, 8, 72}’ ), (’Mary’, ‘{69, 143, 21}’ ); SELECT Name FROM Person WHERE FavoriteNumber[0] = 69; // will return Bob and Mary Subscribe to Appficial for more programming videos coming soon. Also, don't forget to click LIKE and comment on the video if it helped you out!