Using SQLite

These flashcards review the unique features of SQLite, its minimal data types, primary-key behavior, table management statements, join operations, and common use cases.

What makes SQLite a "serverless" database?

It runs directly within the host application and does not require a separate database server process or installation.

Which programming language is SQLite written in?

C

Name two characteristics that make SQLite ideal for mobile and embedded systems.

Very small footprint/minimal overhead and self-contained (no external server needed).

Is SQLite fully ACID-compliant?

Yes, it supports Atomicity, Consistency, Isolation, and Durability to protect data integrity.

List the five core data types supported by SQLite.

INTEGER, REAL, TEXT, BLOB, and NULL.

How does SQLite handle inserting the string "9876" into an INTEGER column?

It automatically converts the numeric string to the integer 9876 and stores it.

What happens if you insert a non-numeric string like "abcd" into an INTEGER column in SQLite?

SQLite stores the literal string "abcd" instead of throwing an error.

How are Boolean values stored in SQLite?

As INTEGER values 0 (false) and 1 (true); there is no separate BOOLEAN type.

In what format does SQLite store dates by default?

As TEXT strings (or optionally as the number of seconds since 1970-01-01 00:00:00).

Write the SQL command to create a table with no declared data types in SQLite.

CREATE TABLE myTable (a, b, c);

What is the primary purpose of a PRIMARY KEY in SQLite?

To uniquely identify each record (row) in a table.

Are PRIMARY KEY columns automatically indexed in SQLite?

Yes, SQLite automatically creates an index on PRIMARY KEY columns to speed up lookups.

Can a PRIMARY KEY in SQLite contain NULL values?

Yes in general, but NOT if the primary-key column is declared as INTEGER; however using NULL defeats the purpose and is discouraged.

What is a composite key in SQLite?

A PRIMARY KEY that is defined using more than one column (e.g., PRIMARY KEY (col1, col2)).

Which SQL statement is used to add a new column to an existing SQLite table?

ALTER TABLE … ADD COLUMN …

Name four common table-level constraints available when creating a table in SQLite.

PRIMARY KEY, UNIQUE, NOT NULL, FOREIGN KEY.

What SQLite statement would you use to remove rows that meet a condition?

DELETE FROM table_name WHERE condition;

Which SQL clause in a SELECT query is commonly used to filter rows?

WHERE

Why should columns used in JOIN conditions often be indexed?

Indexing those columns can greatly improve JOIN performance by speeding up row matching.

Describe the result of an INNER JOIN in SQLite.

Returns only the rows that have matching values in both joined tables.

What distinguishes a LEFT JOIN from a RIGHT JOIN?

LEFT JOIN returns all rows from the left table and matching rows from the right; RIGHT JOIN does the reverse (all from right, matching from left).

What does a FULL OUTER JOIN return?

All rows from both tables, placing NULL in columns where no match exists.

Give two real-world applications that commonly embed SQLite.

Mobile apps (Android/iOS) and web browsers (e.g., Chrome, Firefox).

Why can SQLite’s lenient typing make porting to other databases difficult?

Other RDBMSs enforce stricter data types and length limits, so data that SQLite freely stores (e.g., oversized strings or mismatched types) may cause errors elsewhere.