SQLite

What is SQLite

SQLite is a software library that provides a relational database management system. The lite in SQLite means lightweight in terms of setup, database administration, and required resources.

SQLite has the following noticeable features: self-contained, serverless, zero-configuration, transactional.

Serverless

SQLite database is integrated with the application that accesses the database.

Self-Contained

SQLite is self-contained means it requires minimal support from the operating system or external library.

Zero-configuration

Because of the serverless architecture, you don’t need to “install” SQLite before using it. There is no server process that needs to be configured, started, and stopped.

Transactional

All transactions in SQLite are fully ACID-compliant. It means all queries and changes are Atomic, Consistent, Isolated, and Durable.

SQLite distinctive features

SQLite uses dynamic types for tables.
SQLite allows a single database connection to access multiple database files simultaneously.

How Tto download SQLite:

• Go to the https://www.sqlite.org website.
  

• Open the download page https://www.sqlite.org/download.html

SQLite provides various tools for working across platforms e.g., Windows, Linux, and Mac. You need to select an appropriate version to download.

Installing SQLite

• Create a new folder e.g., C:\sqlite
• Extract the content of the file that you downloaded in the previous section to the C:\sqlite folder. You should see three programs in the C:\sqlite folder as shown below:
  

• Open the command line window:

• Navigate to the C:\sqlite folder

• Type sqlite3 and press enter, you should see the following output:

• You can type the .help command from the sqlite> prompt to see all available commands in sqlite3.

• To quit the sqlite>, you use .quit command as follows:

Connect to an SQLite database

• To start the sqlite3, you type the sqlite3 as follows:
  

• To open a database file, you use the .open FILENAME command. The following statement opens the chinook.db database:
  

• If you want to open a specific database file when you connect to the SQlite database, you use the following command:
  

• Following command creates a database named sales in the C:\sqlite\db\ directory:

• To show all available commands and their purpose, you use the .help command as follows:

• Following command shows all the databases of the current connection:

• To add an additional database in the current connection, you use the statement ATTACH DATABASE. The following statement adds the chinook database to the current connection.

• Now if you run the .database command again, the sqlite3 returns two databases: main and chinook.

• To exit the sqlite3 program, you use the .exit command.

• To display all the tables in the current database, you use the .tables command. The following commands open a new database connection to the chinook database and display the tables in the database.

• To display the structure of a table, you use the .schema TABLE command. The TABLE argument could be a pattern. If you omit it, the .schema command will show the structures of all the tables. The following command shows the structure of the albums table.
  

Save the result of a query into a file

• Following commands select the title from the albums table and write the result to the albums.txt file.

Execute SQL statements from a file

• Suppose we have a file named commands.txt in the c:\sqlite\ folder with the following content:

• To execute the SQL statements in the commands.txt file, you use the .read FILENAME command as follows:

SQLite Data Types

• NULL - NULL values mean missing information or unknown.
• INTEGER - Integer values are whole numbers (either positive or negative). An integer can have variable sizes such as 1, 2,3, 4, or 8 bytes.
• REAL - Real values are real numbers with decimal values that use 8-byte floats.
• TEXT - TEXT is used to store character data. The maximum length of TEXT is unlimited. SQLite supports various character encodings.
• BLOB - BLOB stands for a binary large object that can store any kind of data. The maximum size of BLOB is, theoretically, unlimited.

SQL statements

Simple query

• Select – query data from a single table using SELECT statement.

Sorting rows

• Order By – sort the result set in ascending or descending order.

Filtering data

• Select Distinct – query unique rows from a table using the DISTINCT clause.
• Where – filter rows of a result set using various conditions.
• Limit – constrain the number of rows returned by a query and how to get only the necessary data from a table.
• Between – test whether a value is in a range of values.
• In – check if a value matches any value in a list of values or subquery.
• Like – query data based on pattern matching using wildcard characters: percent sign (%) and underscore (_).
• Glob – determine whether a string matches a specific UNIX-pattern.
• IS NULL – check if a value is null or not.

Joining tables

• Inner Join – query data from multiple tables using the inner join clause.
• Left Join – combine data from multiple tables using the left join clause.
• Cross Join – show you how to use the cross join clause to produce a cartesian product of result sets of the tables involved in the join.
• Self Join – join a table to itself to create a result set that joins rows with other rows within the same table.
• Full Outer Join – show you how to emulate the full outer join in the SQLite using left join and union clauses.

Grouping data

• Group By – combine a set of rows into groups based on specified criteria. The GROUP BY clause helps you summarize data for reporting purposes.
• Having – specify the conditions to filter the groups summarized by the GROUP BY clause.

Set operators

• Union – combine result sets of multiple queries into a single result set. We also discuss the differences between UNION and UNION ALL clauses.
• Except – compare the result sets of two queries and returns distinct rows from the left query that are not output by the right query.
• Intersect – compare the result sets of two queries and returns distinct rows that are output by both queries.

More querying techniques

• Case – add conditional logic to the query.

Changing data

• Insert – insert rows into a table
• Update – update existing rows in a table.
• Delete – delete rows from a table.
• Replace – insert a new row or replace the existing row in a table.

Data definition

• Create Table – create a new table in the database.
• Alter Table – modify the structure of an existing table.
• Rename column – rename a column of a table.
• Drop Table – remove a table from the database.
• VACUUM – optimize database files.

Views

• Create View – create a new view in the database.
• Drop View – drop a view from its database schema.

Triggers

• Trigger – manage triggers in the SQLite database.
• Create INSTEAD OF triggers – create an INSTEAD OF trigger to update data via a view.

References

SQLite Links

• https://www.sqlite.org – SQLite homepage
• https://www.sqlite.org/features.html – SQLite features
• https://www.sqlite.org/copyright.html – SQLite license
• https://www.sqlite.org/docs.html – SQLite documentation
  

SQLite GUI tools

• https://github.com/pawelsalawa/sqlitestudio/releases - SQLiteStudio (SQLite GUI studio)
• https://dbeaver.io/ - DBeaver is another free multi-platform database tool. It supports all popular major relational database systems MySQL, PostgreSQL, Oracle, DB2, SQL Server, Sybase.. including SQLite.
• https://sqlitebrowser.org/ - DB Browser for SQLite – is an open-source tool to manage database files compatible with SQLite.