Using the OCCUR table type

If a table contains many columns, all of which contain similar types of data, it can be difficult to answer questions which deal with comparing those values. This is where the OCCUR data type can prove useful.

How to do it...

1. Launch the mysql command-line client application and connect to the test database on our MariaDB server. If the test database does not exist, create it first.

2. Run the following CREATE TABLE statement to create a gadgets table:

   CREATE TABLE gadgets (
     who varchar(64),
     phone int,
     tablet int,
     mp3player int,
     camera int
   
   );
   

3. Add some data to our gadgets table using the following statement:

   INSERT gadgets VALUES
     ("Jim",1,2,1,2),
     ("Bob",0,0,3,0),
     ("Tom",1,1,1,0),
     ("Joe",1,1,1,1),
     ("Rob",2,2,0,0),
     ("Tim",0,3,1,1)
   ;
   

4. Run the following statement to create our OCCUR table (replace username with a user that has read access rights to the gadget table without needing a password):

   CREATE TABLE gadgets_occur (
     who varchar(64) NOT NULL,
   ...