SQLite

SQLite logo

SQLite is a relational database management system (RDBMS) contained in a C library. In contrast to many other database management systems, SQLite is not a client–server database engine. Rather, it is embedded into the end program.

SQLite is ACID-compliant and implements most of the SQL standard, generally following PostgreSQL syntax. However, SQLite uses a dynamically and weakly typed SQL syntax that does not guarantee domain integrity. This means that one can, for example, insert a string into a column defined as an integer. SQLite will attempt to convert data between formats where appropriate, but does not guarantee such conversions and will store the data as-is if such a conversion is not possible.

SQLite is a popular choice as embedded database software for local/client storage in application software such as web browsers. It is arguably the most widely deployed database engine, as it is used today by several widespread browsers, operating systems, and embedded systems (such as mobile phones), among others.

Readings

• Wikipedia: SQLite

Activities

SQLite Environment

Establish an SQLite environment using one of the following:

• Docker Playground
• SQLite in Docker
• Install SQLite

Docker Playground

Docker Playground is a free online Docker environment. It requires no installation or configuration.

1. Use Play with Docker. Create an account and/or log in.
2. Start an interactive session and add a new instance.
3. In the terminal window, enter the following commands:
   • docker run -it --rm alpine
   • apk update
   • apk add sqlite
   • sqlite3

SQLite in Docker

You can use your own Docker environment to run SQLite.

1. Install Docker Desktop or the Docker Engine.
2. In a terminal window, run the following commands:
   • docker run -it --rm alpine
   • apk update
   • apk add sqlite
   • sqlite3

Install SQLite

Install SQLite on your own system.

1. Review ServerMania: How to Install SQLite
2. Download and install SQLite.
3. Use the following terminal command to access the SQLite command interface:
   • sqlite3

SQLite Activities

Create a Database

1. Use the following SQLite command to create a temperature database:
   .open temperature
2. Use the following SQL command to create a Countries table:
   CREATE TABLE Countries(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
Country TEXT UNIQUE NOT NULL,
Temperature REAL NOT NULL);
3. Use the following SQLite command to show existing tables in the database:
   .tables
4. Use the following SQL command to insert records into the Countries table:
   INSERT INTO Countries (Country, Temperature)
VALUES('Bulgaria', 45.2),
('Canada', 45),
('Federated States of Micronesia', 36.1),
('Finland', 37.2),
('Germany', 40.3),
('Japan', 41),
('Marshall Islands', 35.6),
('Palau', 35),
('Turkmenistan', 50.1);

Query an SQLite Database

1. Use the following SQL command to query the Countries table:
   SELECT * FROM Countries;

Insert a Record

1. Use the following SQL command to insert a record into the Countries table:
   INSERT INTO Countries (Country, Temperature)
VALUES('United States of America', 56.7);
2. Use the following SQL command to display the inserted record:
   SELECT * FROM Countries;

Update a Record

1. Use the following SQL command to update a record in the Countries table:
   UPDATE Countries
SET Temperature = 56.5
WHERE Country = 'United States of America';
2. Use the following SQL command to display the updated record:
   SELECT * FROM Countries;

Delete a Record

1. Use the following SQL command to delete a record from the Countries table:
   DELETE FROM Countries
WHERE Country = 'United States of America';
2. Use the following SQL command to display the remaining records:
   SELECT * FROM Countries;

Delete All Records

1. Use the following SQL command to delete all records from the Countries table:
   DELETE FROM Countries;
2. Use the following SQL command to display the empty table:
   SELECT * FROM Countries;

Remove a Table

1. Use the following SQL command to remove the Countries table:
   DROP TABLE Countries;
2. Use the following SQLite command to verify that the table was removed:
   .tables

See Also

• Databases
• Wikipedia: SQLite
• SQLite: Command Line Shell
• SQLite Browser: DB Browser for SQLite
• SQLite Tutorial