Exploration of SQLite

Recently got a chance to work on SQLite for a requirement to Purge Data based on Date Conditions.Collaborating all learning as part of perks of working on something new.

1. Date Function to Add Days in SQLite.

As part of DATE functions, we can specified Days we need to add or delete.

SELECT DATE('now') ,  DATE('now' , '1 days') , DATE('now' , '-1 days')

2. Rebuild Index in SQLite

We have “REINDEX” command in SQLite to Rebuild a particular index or all indexes on a table.

3. ListAGG in SQLite

As Grouping function, SQLite provide “GROUP_CONCAT” to Concat and Group it.

WITH ALIAS1(COL1) AS
(SELECT 'HELLO' UNION 
SELECT 'WORLD')
SELECT group_concat(COL1, ' ') FROM ALIAS1;

4. Database Metadata Information.

SELECT *
FROM SQLITE_MASTER