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.[1]

SQLite logo
SQLite logo

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.[2]

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.[3]

Readings

edit

Activities

edit

SQLite Environment

edit

Establish an SQLite environment using one of the following:

Docker Playground

edit

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

edit

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

edit

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

edit

Create a Database

edit
  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

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

Insert a Record

edit
  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

edit
  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

edit
  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

edit
  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

edit
  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

edit

References

edit