Case statements can be utilized as part of your SQLite query language to further simplify the data returned from the database. As we noticed in the first SQLite query lab, many applications will utilize flags to symbolize different application features, actions, or to represent the flow of data from one device to the next.

Those underlying flags must be researched for their meaning but and they most certainly can be returned as flags, but a final step in clarifying the output is to have the query output the action or feature that was represented by a flag. We also saw how necessary a case statement was to solving the initial bug that broke most queries on the SMS.db file from iOS devices when they introduced a new timestamp into a particular column. Case statements basically allow for data inside of a particular column to be represented in a myriad of ways by supplying the different conversions or interpretations.

In the example above, we’ve utilized a symbol query to output the meaning behind a flag to the screen vs the raw flag data (as it was stored in the database). In the upcoming lab, we will use this same concept to correct some erroneous timestamps.

Examine the raw database:

Develop your query language, then determine the meaning of the flags:

Use a CASE STATEMENT so that the meaning instead of the underlying flag is returned: