Comprehensive Guide to Testing SQLite SQL Statement Categories

Testing SQLite SQL Statement Categories

Table Structure Design

To thoroughly test SQLite-supported SQL statements, we need a database with various table structures. We'll create a database with four tables: users, products, orders, and order_items.

-- Create users table
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create products table
CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    price REAL NOT NULL,
    stock INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create orders table
CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    total REAL NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users (id)
);

-- Create order_items table
CREATE TABLE order_items (
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    price REAL NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders (id),
    FOREIGN KEY (product_id) REFERENCES products (id)
);

Data Definition Language (DDL)

Creating Tables

-- Basic table creation
CREATE TABLE basic_table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    salary REAL,
    hire_date DATE DEFAULT CURRENT_DATE
);

-- Table creation with various constraints
CREATE TABLE constraint_table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    phone TEXT CHECK (LENGTH(phone) = 10),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id) REFERENCES basic_table (id)
);

Modifying Table Structure

-- Adding a column
ALTER TABLE basic_table ADD COLUMN address TEXT;

-- Deleting a column (SQLite doesn't support direct column deletion)
-- Workaround: Create a new table and migrate data
CREATE TABLE new_basic_table AS SELECT id, name, age, salary, hire_date FROM basic_table;
DROP TABLE basic_table;
ALTER TABLE new_basic_table RENAME TO basic_table;

-- Modifying column properties (SQLite doesn't support direct modification)
-- Workaround: Create a new table with modified structure
CREATE TABLE modified_basic_table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    salary REAL,
    hire_date DATE DEFAULT CURRENT_DATE,
    address TEXT
);
INSERT INTO modified_basic_table (id, name, age, salary, hire_date, address)
SELECT id, name, age, salary, hire_date, address FROM basic_table;
DROP TABLE basic_table;
ALTER TABLE modified_basic_table RENAME TO basic_table;

Basic Data Types

-- DEFAULT: Specify default values when creating a table
CREATE TABLE test_default (
    id INTEGER PRIMARY KEY,
    name TEXT DEFAULT 'Unknown'
);

-- UNSIGNED: Unsigned integer types
CREATE TABLE test_unsigned (
    id INTEGER UNSIGNED PRIMARY KEY,
    value INTEGER UNSIGNED
);

Date Operations

-- Inserting data with dates
INSERT INTO users (username, password, email, created_at) 
VALUES ('user1', 'password1', 'user1@example.com', '2024-05-20');
INSERT INTO products (name, description, price, stock, created_at) 
VALUES ('Product 1', 'Description for Product 1', 100.00, 50, '2024-05-20');
INSERT INTO orders (user_id, total, created_at) 
VALUES (1, 100.00, '2024-05-20');
INSERT INTO order_items (order_id, product_id, quantity, price) 
VALUES (1, 1, 2, 200.00);

-- Querying data for specific dates
SELECT * FROM users WHERE created_at = '2024-05-20';
SELECT * FROM orders 
WHERE created_at BETWEEN '2024-05-20 00:00:00' AND '2024-05-20 23:59:59';

-- Updating date data
UPDATE users SET created_at = '2024-05-21' WHERE id = 1;
UPDATE orders SET created_at = '2024-05-21 10:00:00' WHERE id = 1;

-- Querying updated date data
SELECT * FROM users WHERE created_at = '2024-05-21';
SELECT * FROM orders 
WHERE created_at BETWEEN '2024-05-21 00:00:00' AND '2024-05-21 23:59:59';

-- Using date functions for queries
SELECT * FROM users WHERE DATE(created_at) = '2024-05-21';

-- Deleting data for specific dates
DELETE FROM users WHERE DATE(created_at) = '2024-05-21';
DELETE FROM orders WHERE DATE(created_at) = '2024-05-21';

-- Verifying deletion of date-specific data
SELECT * FROM users;
SELECT * FROM orders;

Dropping Tables

-- Dropping a table
DROP TABLE constraint_table;

Creating Indexes

-- Creating indexes
CREATE INDEX idx_basic_table_name ON basic_table (name);
CREATE UNIQUE INDEX idx_constraint_table_email ON constraint_table (email);

-- Creating composite indexes
CREATE INDEX idx_basic_table_name_age ON basic_table (name, age);

-- Deleting indexes
DROP INDEX idx_basic_table_name;

Creating Views

-- Creating a simple view
CREATE VIEW view_basic_table AS 
SELECT id, name, salary FROM basic_table;

-- Creating a view with conditions
CREATE VIEW view_high_salary AS 
SELECT id, name, salary FROM basic_table WHERE salary > 50000;

-- Deleting views
DROP VIEW view_basic_table;

Creating Triggers

Triggers automatically execute SQL statements when specific events occur.

-- Creating a log table
CREATE TABLE log_table (
    log_id INTEGER PRIMARY KEY AUTOINCREMENT,
    table_name TEXT,
    operation TEXT,
    operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Creating a trigger to log inserts
CREATE TRIGGER trg_after_insert_basic_table
AFTER INSERT ON basic_table
BEGIN
    INSERT INTO log_table (table_name, operation) 
    VALUES ('basic_table', 'INSERT');
END;

-- Creating a trigger to log deletions
CREATE TRIGGER trg_after_delete_basic_table
AFTER DELETE ON basic_table
BEGIN
    INSERT INTO log_table (table_name, operation) 
    VALUES ('basic_table', 'DELETE');
END;

-- Deleting triggers
DROP TRIGGER trg_after_insert_basic_table;

Creating Complex Table Structures

Includes advanced features like foreign keys and cascading operations.

-- Creating tables with foreign keys
CREATE TABLE department (
    dept_id INTEGER PRIMARY KEY AUTOINCREMENT,
    dept_name TEXT NOT NULL
);

CREATE TABLE employee (
    emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
    emp_name TEXT NOT NULL,
    dept_id INTEGER,
    FOREIGN KEY (dept_id) REFERENCES department (dept_id) 
    ON DELETE SET NULL ON UPDATE CASCADE
);

Creating Constraints

Constraints enforce data integrity through primary keys, unique constraints, foreign keys, and check constraints.

-- Creating primary key constraints
CREATE TABLE constraint_table (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT UNIQUE
);

-- Creating unique constraints
CREATE TABLE unique_constraint_table (
    id INTEGER PRIMARY KEY,
    username TEXT UNIQUE,
    email TEXT
);

-- Creating foreign key constraints
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total REAL,
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

-- Creating check constraints
CREATE TABLE check_constraint_table (
    id INTEGER PRIMARY KEY,
    age INTEGER CHECK (age >= 18)
);

Managing Databases

Includes creating and deleting databases.

-- Creating a database
CREATE DATABASE test_database;

-- Deleting a database
DROP DATABASE test_database;

Creating and Managing Users

SQLite typically doesn't support user management, but some SQLite extensions may implement this functionality.

-- Creating a user (not supported in standard SQLite)
CREATE USER new_user IDENTIFIED BY 'password';

-- Deleting a user (not supported in standard SQLite)
DROP USER old_user;

-- Granting privileges (not supported in standard SQLite)
GRANT SELECT ON table_name TO user_name;

-- Revoking privileges (not supported in standard SQLite)
REVOKE INSERT ON table_name FROM user_name;

Managing Stored Procedures and Functions

SQLite typically doesn't support stored procedures and functions, but some SQLite extensions may implement this functionality.

-- Creating a stored procedure (not supported in standard SQLite)
CREATE PROCEDURE calculate_tax (
    IN price DECIMAL(10,2), 
    OUT tax DECIMAL(10,2)
)
BEGIN
    SET tax = price * 0.1;
END;

-- Deleting a stored procedure (not supported in standard SQLite)
DROP PROCEDURE calculate_tax;

-- Creating a function (not supported in standard SQLite)
CREATE FUNCTION get_customer_name (customer_id INT) 
RETURNS VARCHAR(100)
BEGIN
    DECLARE customer_name VARCHAR(100);
    SELECT name INTO customer_name FROM customers WHERE id = customer_id;
    RETURN customer_name;
END;

-- Deleting a function (not supported in standard SQLite)
DROP FUNCTION get_customer_name;

Creating and Managing Sequences

Sequences generate unique number sequences, commonly used for generating primary key values.

-- Creating a sequence
CREATE SEQUENCE seq_id START WITH 1 INCREMENT BY 1;

-- Deleting a sequence
DROP SEQUENCE seq_id;

-- Getting the next sequence value
SELECT NEXT VALUE FOR seq_id;

-- Resetting a sequence
ALTER SEQUENCE seq_id RESTART WITH 100;

Managing Storage Space and Tablespaces

Storage space management allows administrators to manage the physical storage structure of the database, while tablespace management allows storing tables in different physical locations.

-- Creating a tablespace (not supported in SQLite)
CREATE TABLESPACE ts1 LOCATION '/path/to/disk1';

-- Deleting a tablespace (not supported in SQLite)
DROP TABLESPACE ts1;

-- Moving a table to a specific tablespace (not supported in SQLite)
ALTER TABLE table_name SET TABLESPACE ts1;

Defining Events and Scheduled Tasks

Events and scheduled tasks allow the database to automatically execute specific operations.

-- Creating an event (not supported in SQLite)
CREATE EVENT event_name 
ON SCHEDULE AT '2024-05-25 03:00:00' 
DO DELETE FROM logs 
WHERE timestamp < NOW() - INTERVAL 1 MONTH;

-- Deleting an event (not supported in SQLite)
DROP EVENT event_name;

-- Creating a scheduled task (not supported in SQLite)
CREATE SCHEDULE schedule_name
    ON SCHEDULE EVERY 1 DAY
    STARTS '2024-05-25 00:00:00'
    DO BACKUP DATABASE;

-- Deleting a scheduled task (not supported in SQLite)
DROP SCHEDULE schedule_name;

Backing Up and Restoring Databases

Backup and restore operations are critical parts of database management, but in SQLite they are typically handled by external tools.

-- Backing up a database (SQLite typically uses external tools)
.backup test_database backup_file;

-- Restoring a database (SQLite typically uses external tools)
.restore backup_file;

Creating and Managing Virtual Tables

Virtual tables allow SQLite to access external data sources or implement specialized storage engines.

-- Creating a full-text search virtual table
CREATE VIRTUAL TABLE documents USING fts5(content TEXT);

-- Inserting data
INSERT INTO documents(content) VALUES ('This is a document.');

-- Full-text search
SELECT * FROM documents WHERE documents MATCH 'document';

-- Deleting a full-text search virtual table
DROP TABLE documents;

Loading and Managing Extensions

SQLite supports loading dynamic link libraries to extend its functionality with custom data types, functions, virtual tables, etc.

-- Loading an extension module
.load /path/to/module.so

-- Unloading an extension module
.UNLOAD module_name;

Data Manipulation Language (DML)

Inserting Data

Beyond basic single-row inserts, we test batch inserts and inserting query results.

-- Single row insert
INSERT INTO basic_table (name, age, salary) 
VALUES ('Alice', 30, 50000.00);

-- Batch insert
INSERT INTO basic_table (name, age, salary) 
VALUES 
    ('Bob', 35, 60000.00),
    ('Charlie', 40, 70000.00),
    ('David', 45, 80000.00);

-- Inserting data from another table
INSERT INTO basic_table (name, age, salary)
SELECT name, age, salary FROM temp_table;

-- Normal insert operations
INSERT INTO users (username, password, email) 
VALUES ('user1', 'password1', 'user1@example.com');
INSERT INTO users (username, password, email) 
VALUES ('user2', 'password2', 'user2@example.com');

INSERT INTO products (name, description, price, stock) 
VALUES ('Product 1', 'Description 1', 10.99, 100);
INSERT INTO products (name, description, price, stock) 
VALUES ('Product 2', 'Description 2', 20.99, 200);

-- Duplicate data insert operations (unique constraint)
-- Attempting to insert existing username and email
INSERT INTO users (username, password, email) 
VALUES ('user1', 'password3', 'user3@example.com'); -- Should fail, username exists
INSERT INTO users (username, password, email) 
VALUES ('user3', 'password3', 'user1@example.com'); -- Should fail, email exists

-- Foreign key constraint test
-- Attempting to insert non-existent user_id
INSERT INTO orders (user_id, total) 
VALUES (100, 50.00); -- Should fail, user_id doesn't exist

-- Normal insert of orders and order items
INSERT INTO orders (user_id, total) 
VALUES (1, 100.00);
INSERT INTO order_items (order_id, product_id, quantity, price) 
VALUES (1, 1, 2, 20.00);

Updating Data

Data updates include single-table updates, conditional updates, and cross-table updates.

-- Single table update
UPDATE basic_table SET salary = 55000.00 WHERE name = 'Alice';

-- Conditional update
UPDATE basic_table SET salary = salary * 1.1 WHERE age > 40;

-- Cross-table update (SQLite doesn't support JOIN UPDATE, but can be simulated with subqueries)
UPDATE basic_table 
SET salary = (
    SELECT salary * 1.05 FROM basic_table WHERE name = 'Alice'
) 
WHERE name = 'Alice';

-- Batch update
UPDATE basic_table 
SET salary = salary * 1.05 
WHERE age BETWEEN 30 AND 40;

-- Update user information
UPDATE users SET password = 'new_password' WHERE username = 'john_doe';

-- Update product information
UPDATE products SET description = 'Powerful gaming laptop' 
WHERE name = 'Laptop';

-- Update order information
UPDATE orders SET total = 1200 WHERE total > 1000;

-- Update order item information
UPDATE order_items SET quantity = 2 
WHERE order_id = 1 AND product_id = 1;

-- Update non-existent user
UPDATE users SET email = 'new_email@example.com' 
WHERE username = 'non_existent_user';
-- Expected result: No rows affected since user doesn't exist

-- Update violating unique constraint
UPDATE users SET email = 'john_new@example.com' 
WHERE username = 'john_doe';
-- Expected result: Update fails, email already used by another user

-- Update violating foreign key constraint
UPDATE orders SET user_id = 999 WHERE id = 1;
-- Expected result: Update fails, user_id doesn't exist

-- Inline update
UPDATE orders SET total = total * 1.1 WHERE total < 100;
UPDATE orders SET total = total + 50 WHERE user_id = 1;

Deleting Data

Data deletion involves single-table deletes and conditional deletes.

-- Single table delete
DELETE FROM basic_table WHERE name = 'David';

-- Conditional delete
DELETE FROM basic_table WHERE age > 40;

-- Delete all data
DELETE FROM basic_table;

-- Delete all data from a single table
DELETE FROM users;

-- Delete data from a single table with conditions
DELETE FROM products WHERE price > 1000.00;

-- Using subqueries to delete conditional data
DELETE FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000.00);

-- Delete data from related tables
DELETE FROM order_items 
WHERE order_id IN (SELECT id FROM orders WHERE user_id = 1);

-- Delete data with cascading deletes from parent table
DELETE FROM users WHERE id = 1;

-- Delete data from child table (if cascading delete not used)
DELETE FROM orders WHERE user_id = 2;

-- Delete all data from all tables (use with caution!)
DELETE FROM users;
DELETE FROM products;
DELETE FROM orders;
DELETE FROM order_items;

-- Test inline conditional delete
-- Prepare data
INSERT INTO users (username, password, email) 
VALUES ('user1', 'password1', 'user1@example.com'),
       ('user2', 'password2', 'user2@example.com'),
       ('user3', 'password3', 'user3@example.com');

-- Delete user with username 'user2'
DELETE FROM users WHERE username = 'user2';

-- Clear users table (SQLite doesn't support TRUNCATE, so we use DELETE)
DELETE FROM users;

Merging Data

SQLite doesn't support the MERGE statement, but we can simulate it using INSERT and UPDATE statements.

<code-- conflict="" data="" do="" from="" insert="" into="" merging="" name="" on="" select="" set="" source_id="" source_name="" source_table="" source_value="" target_table="" update="" value=""></code-->

Data Copying

-- Copying data to another table
INSERT INTO new_table (name, age, salary)
SELECT name, age, salary FROM basic_table;

Data Deletion and Backup

<code-- and="" as="" backing="" backup_table="" basic_table="" create="" data="" delete="" deleting="" from="" select="" table="" up=""></code-->

Data Cleanup and Maintenance

<code-- and="" as="" backup="" backup_table="" basic_table="" cleaning="" compress="" create="" data="" database="" date="" delete="" file="" from="" hire_date="" maintenance="" old="" select="" table="" up="" vacuum="" where="" year=""></code-->

Transaction Processing in Data Operations

<code-- a="" age="" basic_table="" begin="" commit="" committing="" data="" insert="" inserting="" into="" salary="" starting="" the="" transaction="" values=""></code-->

Data Conversion and Formatting

<code-- age="" and="" as="" basic_table="" cast="" conversion="" data="" formatted_salary="" formatting="" from="" name="" select="" text=""></code-->

Data Statistics and Analysis

<code-- analysis="" and="" as="" avg="" avg_salary="" basic_table="" count="" data="" from="" max="" max_salary="" min="" min_salary="" select="" statistics="" total_employees=""></code-->

Data Random Generation

<code-- age="" basic_table="" data="" generating="" insert="" into="" random="" round="" salary="" values=""></code-->

Data Verification and Validation

<code-- above="" and="" basic_table="" be="" before="" begin="" case="" constraints="" create="" data="" end="" insert="" must="" new.age="" on="" or="" raise="" select="" then="" through="" trg_check_age="" trigger="" triggers="" verification="" when=""></code-->

Data Cleaning and Filtering

<code-- and="" basic_table="" cleaning="" data="" delete="" filtering="" from="" salary="" statements="" through="" where=""></code-->

Data Aggregation and Classification

<code-- age="" aggregation="" and="" by="" case="" classification="" data="" group="" select="" then="" through="" when="">= 30 AND age < 40 THEN 'Middle-aged'
        ELSE 'Senior'
    END AS age_group,
    COUNT(*) AS count
FROM 
    basic_table
GROUP BY 
    age_group;
</code-->

Data Partitioning and Summary

<code-- age="" and="" as="" basic_table="" by="" data="" from="" functions="" name="" over="" partitioning="" salary="" select="" sum="" summary="" through="" total_salary_by_age="" window=""></code-->

Data Connection and Matching

<code-- and="" basic_table="" basic_table.dept_id="department.dept_id;" basic_table.name="" connection="" data="" department="" department.dept_name="" from="" join="" matching="" on="" select="" through=""></code-->

Data Time Processing and Calculation

<code-- and="" as="" basic_table="" calculation="" data="" date="" days_since_hire="" from="" functions="" hire_date="" hire_year="" name="" processing="" select="" strftime="" through="" time=""></code-->

Data Sequencing and Numbering

<code-- and="" as="" basic_table="" by="" data="" desc="" from="" name="" numbering="" over="" ranking="" row_number="" salary="" select="" sequencing="" through=""></code-->

Data Archiving and Compression

<code-- and="" archive="" archived_basic_table="" archiving="" as="" basic_table="" compression="" create="" data="" database="" date="" from="" hire_date="" select="" table="" tables="" through="" vacuum="" where="" year=""></code-->

Data Auditing and Logging

<code-- after="" and="" audit_log="" auditing="" autoincrement="" basic_table="" begin="" case="" create="" current_timestamp="" data="" default="" delete="" else="" end="" id="" insert="" integer="" into="" is="" key="" logging="" new.="" new.id="" new_data="" null="" old.="" old.id="" old_data="" on="" operation="" or="" primary="" table="" table_name="" text="" then="" through="" timestamp="" trg_audit_log="" trigger="" triggers="" update="" values="" when=""></code-->

Transcation Control Language (TCL)

Starting Transactions

<code-- a="" begin="" simple="" starting="" transaction=""></code-->

Committing Transactions

<code-- a="" commit="" committing="" simple="" transaction=""></code-->

Rolling Back Transactions

<code-- a="" back="" rollback="" rolling="" simple="" transaction=""></code-->

Savepoints and Rolling Back to Savepoints

<code-- a="" back="" begin="" commit="" committing="" creating="" data="" email="" insert="" inserting="" into="" more="" password="" rollback="" rolling="" savepoint="" savepoint1="" savepoints="" some="" the="" to="" transaction="" users="" using="" values=""></code-->

Nested Transactions (Simulated through Savepoints)

SQLite doesn't support true nested transactions, but they can be simulated through savepoints.

<code-- back="" begin="" beginning="" commit="" committing="" data="" email="" first="" insert="" inserting="" into="" password="" rollback="" rolling="" savepoint="" savepoint1="" savepoint2="" second="" to="" transaction="" users="" values=""></code-->

Checking Transaction Consistency and Integrity

Using transactions to ensure consistency and integrity.

<code-- a="" above="" and="" begin="" checking="" commit="" email="" failed="" from="" id="" if="" insert="" inserting="" into="" operations="" order="" orders="" password="" rollback="" select="" starting="" succeeded="" successful="" the="" total="" transaction="" user="" user_id="(SELECT" username="transaction_user" users="" values="" was="" where=""></code-->

Exception Handling in Transactions

Simulating exceptions during transaction execution and rolling back.

<code-- a="" begin="" commit="" data="" duplicate="" email="" exception="" failed="" if="" insert="" inserting="" into="" key="" password="" rollback="" simulating="" starting="" succeeded="" to="" transaction="" unique="" users="" values=""></code-->

Advanced Transaction Management

Simulating complex transaction management with multi-table operations.

<code-- a="" back="" begin="" commit="" committing="" creating="" email="" first="" from="" id="" insert="" inserting="" into="" items="" order="" order_items="" orders="" password="" price="" product_id="" quantity="" rollback="" rolling="" savepoint="" savepoint1="" savepoint2="" second="" starting="" the="" to="" total="" transaction="" user="" user_id="(SELECT" username="complex_user" users="" values="" where=""></code-->

Using Transactions for Atomic Operations

Using transactions in logical units with multiple operations to ensure atomicity.

<code-- a="" begin="" commit="" committing="" email="atomic@example.com" from="" id="" information="" insert="" inserting="" into="" order="" orders="" querying="" select="" set="" starting="" the="" total="" transaction="" update="" updating="" user="" username="atomic_user" users="" values="" where=""></code-->

Rolling Back to Specific Savepoints

During a transaction, rolling back to a previously created savepoint to undo partial operations.

<code-- a="" back="" begin="" commit="" committing="" creating="" email="" from="" id="" information="" insert="" inserting="" into="" items="" order="" order_items="" orders="" password="" price="" product_id="" quantity="" rollback="" rolling="" savepoint="" savepoint1="" starting="" the="" to="" total="" transaction="" user="" user_id="(SELECT" username="rollback_user" users="" values="" where=""></code-->

Multiple Savepoints and Rollbacks

Creating multiple savepoints within a transaction and rolling back to different savepoints as needed.

<code-- a="" back="" begin="" commit="" committing="" creating="" email="" from="" id="" information="" insert="" inserting="" into="" items="" order="" order_items="" orders="" password="" price="" product_id="" quantity="" rollback="" rolling="" savepoint="" savepoint1="" savepoint2="" starting="" the="" to="" total="" transaction="" user="" user_id="(SELECT" username="multi_savepoint_user" users="" values="" where=""></code-->

Data Query Language (DQL)

Simple Queries

<code-- all="" columns="" email="" from="" query="" select="" specific="" username="" users=""></code-->

Conditional Queries

<code-- and="" between="" complex="" conditional="" conditions="" email="" from="" in="" like="" or="" price="" products="" query="" select="" username="alice" users="" using="" where=""></code-->

Aggregate Functions

SQLite supports various aggregate functions for data analysis:

avg(X) - Returns the average of all non-NULL values in a group
count(X) and count(*) - Returns the number of non-NULL values or total rows
group_concat(X) and string_agg(X,Y) - Concatenates non-NULL values
max(X) - Returns the maximum value in a group
min(X) - Returns the minimum value in a group
sum(X) and total(X) - Returns the sum of all non-NULL values

<code-- all="" all_order_totals="" all_product_names="" all_usernames="" as="" average="" avg="" avg_password_length="" calculating="" concatenating="" count="" custom="" date="" earliest="" earliest_registration="" expensive="" finding="" from="" group_concat="" in="" items="" length="" max="" max_price="" min="" most="" names="" number="" of="" order="" order_count="" order_items="" orders="" password="" product="" product_count="" products="" registration="" select="" separator="" string_agg="" sum="" table="" the="" total="" total_order_items="" total_sales="" totals="" usernames="" users="" value="" with=""></code-->

Grouping Queries

<code-- and="" as="" average_salary="" avg="" by="" calculating="" count="" dept_id="" employees="" filter="" from="" group="" grouped="" grouping="" having="" order="" order_count="" order_items="" orders="" per="" product="" product_id="" quantity="" results="" select="" sold="" sum="" to="" total="" total_quantity="" user="" user_id="" using=""> 50000;
</code-->

Sorting Queries

<code-- asc="" by="" dept_id="" desc="" descending="" employees="" from="" in="" multi-column="" order="" price="" products="" salary="" select="" sorting="" username="" users=""></code-->

Join Queries

<code-- as="" be="" by="" can="" doesn="" from="" inner="" join="" joins="" left="" on="" order_id="" order_items="" order_items.price="" order_items.product_id="products.id;" order_items.quantity="" orders="" orders.id="" orders.total="" orders.user_id="users.id" product_name="" products="" products.name="" query="" right="" select="" simulated="" support="" swapping="" tables="" users="" users.id="orders.user_id;" users.username=""></code-->

Subqueries

<code-- from="" id="" in="" orders="" select="" simple="" subquery="" total="" user_id="" users="" where=""> 100.00);

-- Correlated subquery
SELECT username, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count 
FROM users;

-- Selecting from a subquery
SELECT * FROM (SELECT username, email FROM users) AS subquery 
WHERE email LIKE '%example.com';

-- Using subquery for aggregation
SELECT 
    dept_id, 
    (SELECT AVG(salary) FROM employees AS e WHERE e.dept_id = d.dept_id) AS average_salary 
FROM 
    department AS d;
</code-->

Union Queries

<code-- all="" archived_users="" email="" except="" from="" intersect="" like="" operation="" select="" union="" username="" users="" where=""></code-->

Window Functions

<code-- and="" as="" average="" avg="" between="" by="" created_at="" cumulative="" cumulative_sum="" current="" email="" from="" function:="" moving="" moving_avg="" order="" order_date="" order_items="" over="" preceding="" price="" product_id="" rank="" ranking="" row="" rows="" select="" sum="" username="" users="" window=""></code-->

Arithmetic Expressions

<code-- age="" as="" by="" divided="" doubled_quantity="" example="" from="" half_price="" minus="" modified_age="" modulo="" multiplied="" new_price="" order="" orders="" plus="" price="" product="" products="" quantity="" query="" remainder="" select="" user="" users=""></code-->

Comparison Operators

<code-- age="" from="" older="" or="" query="" select="" users="" where="" years="">= 18;

-- Query products with price greater than 50
SELECT * FROM products WHERE price > 50;

-- Query orders with quantity less than or equal to 10
SELECT * FROM orders WHERE quantity <= 10;

-- Query orders with quantity less than 10
SELECT * FROM orders WHERE quantity < 10;

-- Query products with name not equal to 'Apple'
SELECT * FROM products WHERE name <> 'Apple';

-- Query products with name not equal to 'Apple'
SELECT * FROM products WHERE name != 'Apple';
</code-->

Common Keywords

<code-- age="" alias="" as="" asc="" ascending="" by="" columns="" desc="" descending="" distinct="" email="" fields="" for="" from="" keyword="" name="" names="" non-product="" order="" orders="" price="" products="" quantity="" query="" result="" select="" sort="" specify="" to="" user_email="" user_name="" username="" users="" using=""></code-->

Logical Operators

<code-- age="" and="" email="" from="" in="" operator="" or="" query="" role="" roles="" select="" specific="" to="" users="" using="" where="" with="">= 18;

-- Using NOT operator to query users not in specific role
SELECT * FROM users WHERE NOT role = 'admin';

-- Using BETWEEN operator to query orders with quantity between 10 and 20
SELECT * FROM orders WHERE quantity BETWEEN 10 AND 20;

-- Using EXISTS keyword for correlated subquery
SELECT username FROM users WHERE EXISTS (SELECT * FROM orders WHERE orders.user_id = users.id);

-- Using NOT EXISTS keyword for correlated subquery
SELECT username FROM users WHERE NOT EXISTS (SELECT * FROM orders WHERE orders.user_id = users.id);
</code-->

Fuzzy Queries

<code-- and="" containing="" email="" ending="" example="" from="" length="" like="" name="" not="" products="" query="" select="" starting="" username="" users="" where="" with=""></code-->

Glob Clause

<code-- and="" containing="" email="" ending="" example="" from="" glob="" length="" name="" not="" or="" products="" query="" select="" starting="" username="" users="" where="" with=""></code-->

Case Statements

<code-- branching="" case="" conditional="" for="" salary="" select="" statement="" username="" using="" when="">= 50000 THEN 'High'
        ELSE 'Low'
    END AS salary_level 
FROM 
    employees;

-- Using CASE expression for conditional queries
SELECT 
    username, 
    email, 
    CASE 
        WHEN email LIKE '%@example.com' THEN 'Internal'
        ELSE 'External'
    END AS email_type 
FROM 
    users;

-- Using CASE statement for grouping and aggregation
SELECT 
    dept_id, 
    SUM(CASE WHEN salary > 50000 THEN 1 ELSE 0 END) AS high_salary_count,
    SUM(CASE WHEN salary <= 50000 THEN 1 ELSE 0 END) AS low_salary_count 
FROM 
    employees 
GROUP BY 
    dept_id;
</code-->

Common Table Expressions (CTEs)

<code-- all="" as="" by="" cte="" e="" e.id="" e.name="" employees="" employeesales="" from="" group="" join="" number="" numbers="" o="" on="" orders="" recursive="" select="" simple="" sum="" total_sales="" union="" where="" with=""></code-->

Queries with LIMIT and OFFSET

<code-- and="" as="" by="" complex="" date="" desc="" first="" from="" join="" limit="" next="" offset="" on="" order="" order_id="" order_items="" order_items.price="" order_items.product_id="products.id" order_items.quantity="" orders="" orders.created_at="" orders.id="" orders.total="" orders.user_id="users.id" product_name="" products="" products.name="" query="" records="" select="" skip="" sorted="" to="" user="" users="" users.username="" with=""></code-->

SQLite Core Functions

String Functions

<code-- and="" as="" char="" concat="" description="REPLACE(description," employees="" first_three_chars="" from="" full_name="" function="" functions="" hello="" instr="" last_name="" length="" lower="" lowercase_username="" position="" products="" replace="" return="" select="" set="" should="" substr="" testing="" trim="" trimmed_text="" update="" upper="" uppercase_username="" username_length="" users="" world=""></code-->

Numeric Functions

<code-- abs="" absolute_value="" as="" average_price="" avg="" from="" function="" functions="" max="" max_price="" min="" min_price="" products="" random="" random_value="" round="" rounded_value="" select="" sum="" testing="" total="" total_price=""></code-->

Date and Time Functions

<code-- as="" current_date="" current_time="" date="" datetime="" day="" days="" formatted_date="" function="" julian_day="" julianday="" last_week="" modifiers="" month="" next_month="" select="" strftime="" testing="" time="" tomorrow="" with=""></code-->

Aggregate Functions

<code-- all_usernames="" and="" as="" average_price="" avg="" count="" earliest_order="" from="" function="" functions="" group_concat="" largest_order="" max="" min="" orders="" products="" select="" sum="" testing="" total_sales="" user_count="" users=""></code-->

Type Conversion Functions

<code-- as="" cast="" function="" integer="" integer_value="" real="" real_value="" select="" testing="" text="" text_value="" type="" typeof=""></code-->

Other Functions

<code-- as="" available="" changes="" coalesce="" description="" email="" email_address="" from="" function="" id="1;" ifnull="" insert="" into="" last_id="" last_insert_rowid="" nullif="" password="" price_or_null="" product_description="" products="" rows_affected="" select="" set="" testing="" update="" users="" values="" where=""></code-->

SQLite Native Syntax

ANALYZE

<code-- analyze="" analyzing="" and="" between="" create="" creating="" examining="" explain="" for="" from="" generate="" idx_product_name="" idx_user_username="" index="" indexes="" on="" plan="" plans="" price="" products="" query="" select="" statistics="" tables="" testing="" to="" usage="" username="john_doe" users="" verify="" where=""></code-->

ATTACH DATABASE

<code-- a="" all="" and="" as="" attach="" attached="" attempting="" checking="" database="" from="" if="" name="" select="" sqlite_master="" successfully="" test_db="" test_db.sqlite_master="" to="" type="table" union="" was="" where=""></code-->

Transaction Control

<code-- a="" begin="" checking="" commit="" committed="" committing="" delete="" email="" exists="" from="" if="" insert="" into="" operations="" password="" performing="" rollback="" select="" set="" starting="" successfully="" the="" transaction="" update="" user="" username="test_user" users="" values="" was="" where="" within=""></code-->

Comments

SQLite supports two types of comments:

Double-dash comments (--): Extend to the end of the line
C-style comments (/* */): Can span multiple lines

<code-- a="" c-style="" comment="" double-dash="" from="" is="" multi-line="" products="" select="" this="" users=""></code-->

CREATE INDEX

<code-- composite="" create="" creating="" faster="" for="" idx_order_item="" idx_price="" idx_user_id="" idx_username="" index="" on="" order_items="" orders="" price="" product_id="" products="" queries="" regular="" unique="" user-based="" user_id="" username="" users=""></code-->

CREATE TABLE

The CREATE TABLE command is used to create new tables in SQLite. It specifies:

The table name
The database where the table is created
Column names and types
Default values or expressions
Constraints (primary key, unique, not null, check, foreign key)
Generated column constraints
Whether the table is WITHOUT ROWID
Whether the table has strict type checking

CREATE TRIGGER

<code-- a="" added="" after="" are="" begin="" create="" creating="" default="" end="" from="" id="NEW.order_id;" insert="" insert_default_order="" into="" is="" items="" join="" new="" oi="" oi.order_id="NEW.order_id" oi.product_id="p.id" on="" order="" order_items="" orders="" p="" p.price="" products="" select="" set="" sum="" to="" total="" trigger="" update="" update_order_total="" user="" users="" values="" when="" where=""></code-->

CREATE VIEW

CREATE VIEW commands specify a name for a pre-packaged SELECT statement. After creating a view, it can be used in the FROM clause of another SELECT instead of a table name.

<code-- a="" as="" complex="" create="" creating="" email="" from="" id="" join="" joins="" on="" order_details="" order_id="" order_items="" order_items.price="" order_items.product_id="products.id;" order_items.quantity="" orders="" orders.id="" orders.user_id="users.id" product_name="" products="" products.name="" querying="" role="admin" select="" simple="" the="" total_item_price="" user_summary="" username="" users="" users.username="" view="" where="" with=""> 100;
</code-->

CREATE VIRTUAL TABLE

Virtual tables are interfaces to external storage or computation engines that appear to be tables but do not store information in the database file.

<code-- a="" about="" an="" content="" create="" creating="" custom="" custom_data="" custom_module="" data="" document="" documents_fts="" external="" external_module="" from="" fts5="" full-text="" insert="" inserting="" into="" is="" match="" module="" orders_external="" param2="" performing="" provider="" search="" select="" sqlite.="" table="" the="" total="" user_id="" using="" values="" virtual="" where=""></code-->

DELETE

<code-- all="" an="" and="" caution="" clearing="" conditions="" data="" delete="" deleting="" from="" id="1;" insufficient="" items="" its="" order="" order_items="" orders="" products="" related="" stock="" username="john_doe" users="" where="" with=""></code-->

DETACH

<code-- a="" an="" as="" attach="" attempting="" create="" creating="" data="" database="" detach="" detached="" detaching="" error="" for="" from="" id="" in="" insert="" inserting="" integer="" into="" key="" name="" not="" null="" primary="" query="" querying="" result="" second="" second_db="" second_db.test_table="" select="" should="" table="" testing="" text="" the="" to="" values=""></code-->

DROP

<code-- a="" after="" an="" as="" attempting="" begin="" create="" creating="" drop="" email="" end="" existing="" exists="" from="" id="" idx_nonexistent_index="" idx_username="" if="" index="" insert="" logic="" my_trigger="" non-existent="" nonexistent_table="" nonexistent_trigger="" nonexistent_view="" on="" select="" syntax="" table="" testing="" to="" trigger="" user_details="" username="" users="" view=""></code-->

END TRANSACTION

END TRANSACTION is an alias for COMMIT.

<code-- a="" begin="" commit="" committed="" committing="" email="" end="" from="" insert="" inserted="" inserting="" into="" new="" not="" password="" querying="" record="" select="" starting="" successful="" table="" the="" to="" transaction="" username="test_user" users="" values="" verify="" was="" where="" yet=""></code-->

EXPLAIN

SQL statements can be prefixed with the "EXPLAIN" or "EXPLAIN QUERY PLAN" phrases. The output of EXPLAIN and EXPLAIN QUERY PLAN is intended for interactive analysis and troubleshooting only.

<code-- as="" by="" conditional="" count="" desc="" execution="" explain="" for="" from="" group="" grouping="" id="" in="" join="" multi-table="" on="" order="" order_count="" order_id="" order_items="" order_items.price="" order_items.product_id="products.id;" order_items.quantity="" orders="" orders.id="" orders.user_id="users.id" plan="" price="" product_name="" products="" products.name="" query="" select="" sorting="" subquery="" sum="" table="" total="" total_spent="" user_id="" username="john_doe" users="" users.username="" where=""> 1000);
</code-->

INSERT

INSERT statements have three basic forms:

INSERT IN TO table VALUES(...);
INSERT INTO table SELECT ...;
INSERT INTO table DEFAULT VALUES;

<code-- ...="" a="" another="" data="" default="" first="" form:="" from="" insert="" inserting="" into="" order="" orders="" product="" products="" products_backup="" record="" second="" select="" table="" testing="" third="" user="" users="" values=""></code-->

PRAGMA

PRAGMA statements are SQLite-specific SQL extensions used to modify the operation of the SQLite library or query internal (non-table) data of the library.

<code-- a="" about="" all="" auto-increment="" auto-vacuum="" auto_vacuum="" autoincrement="1;" behavior="" bytes="" cache="" cache_size="" cache_spill="" check="" checking="" compile_options="" configuration="" database="" database_list="" databases="" detailed="" encoding="" for="" foreign="" foreign_key_check="" foreign_keys="" free="" freelist_count="" getting="" global="" if="" in="" index="" index_info="" index_list="" index_xinfo="" indexes="" information="" is="" journal_mode="" key="" limits="" listing="" lock="" locking_mode="" log="" max_page_count="" maximum="" mode="" modifying="" name="" options="" page="" page_count="" page_size="" pages="" pragma="" primary="" primary_key="" reserved="" reserved_bytes="" schema="" setting="" settings="" size="" specific="" sqlite="" starting="" status="" storage="" sync="" synchronous="" table="" table_info="" table_list="" tables="" tablespace="" tablespace_name="" the="" usage="" user="" user_version="" version="" wal="" wal_checkpoint="" writable="" writable_schema=""></code-->

REINDEX

The REINDEX command deletes and rebuilds indexes from scratch. This is useful when the definition of a collation sequence changes or when the definition of an index involving functions changes.

<code-- an="" checking="" create="" creating="" exists="" for="" idx_username="" if="" index="" index_info="" index_list="" on="" pragma="" rebuild="" rebuilding="" reindex="" successful="" testing="" the="" users="" was=""></code-->

RELEASE SAVEPOINT

SAVEPOINTs are a way to create transactions similar to BEGIN and COMMIT, except that SAVEPOINT and RELEASE commands have names and can be nested.

<code-- a="" all="" been="" checking="" commit="" creating="" data="" email="" execute="" following="" from="" has="" if="" insert="" inserted="" into="" is="" name="Phone" needed="" operations="" password="" performing="" price="" products="" rollback="" savepoint="" select="" sp1="" statement="" stock="" successful="" the="" to="" transaction="" under="" username="alice" users="" values="" were="" where=""></code-->

REPLACE

The REPLACE command is an alias for INSERT OR REPLACE. It replaces existing records when there's a unique constraint or primary key conflict.

<code-- a="" again="" attempting="" cleaning="" conflicting="" create="" creating="" data="" drop="" email="" existing="" for="" from="" have="" id="" insert="" inserting="" integer="" into="" key="" not="" now="" null="" one="" only="" primary="" querying="" record="" replace="" select="" should="" syntax="" table="" temp="" temp_users="" temporary="" testing="" text="" the="" to="" unique="" up="" updated="" username="" using="" values="" with=""></code-->

RETURNING Clause

The RETURNING clause returns data from inserted, updated, or deleted rows.

<code-- a="" an="" and="" as="" begin="" by="" commit="" delete="" deleted="" deleting="" demonstrating="" email="" from="" id="" insert="" inserted="" inserting="" into="" item="" join="" multiple="multiple" new="" o="" o.id="" oi="" oi.id="" on="" order="" order_id="" order_item_id="" order_items="" orders="" password="" price="" product="" product_id="" products="" quantity="" record="" return="" returning="" select="" set="" stock="" the="" total="" transaction="" update="" updated="" updating="" user="" username="" users="" values="" where=""></code-->

UPSERT

UPSERT is a clause added to INSERT statements that makes the INSERT behave like an UPDATE or do nothing if the insert violates a uniqueness constraint.

<code-- a="" and="" conflict="" data="" do="" doing="" email="" existing="" exists="" expected="" from="" id="" if="" ignore="" ignoring="" index="" insert="" inserting="" into="" key="" new="" nothing="" on="" operation="" or="" order="" orders="" password="" primary="" record="" related="" replace="" result:="" set="" table="" the="" to="" total="" unique="" update="" updating="" user="" username="john_doe" users="" values="" where="" while=""></code-->

VACUUM

The VACUUM command rebuilds the database file, reducing it to the minimum disk space required.

<code-- a="" as="" attach="" bytes="" closing="" command="" connection="" contains="" creating="" data="" database="" defined="" description="" detach="" disk="" earlier="" email="" ensure="" executing="" file="" from="" has="" insert="" inserting="" into="" is="" opening="" optimize="" optimized="" order_items="" orders="" page_count="" page_size="" password="" pragma_page_count="" pragma_page_size="" price="" product="" product_id="" products="" quantity="" querying="" reasonable="" select="" size="" size_after_vacuum="" size_before_vacuum="" some="" stock="" structure="" table="" test="" test.db="" test_db="" the="" this="" to="" total="" users="" vacuum="" vacuum:="" values="" written=""></code-->

WITH Clause

The WITH clause defines common tible expressions (CTEs) that can be used within SELECT, INSERT, UPDATE, or DELETE statements.

<code-- as="" by="" clause="" creating="" data="" description="" email="" from="" group="" insert="" into="" join="" on="" order_items="" orders="" password="" price="" product_id="" products="" quantity="" select="" stock="" sum="" test="" testing="" total="" total_spent="" total_spent.total_spent="" user_id="" users="" users.id="total_spent.user_id;" users.username="" values="" with=""></code-->

Tags: sqlite sql database testing SQL DDL SQL DML

Posted on Tue, 13 Oct 2026 16:12:39 +0000 by rawb

Freaks City

Comprehensive Guide to Testing SQLite SQL Statement Categories

Testing SQLite SQL Statement Categories

Creating Tables

Modifying Table Structure

Basic Data Types

Date Operations

Dropping Tables

Creating Indexes

Creating Views

Creating Triggers

Creating Complex Table Structures

Creating Constraints

Managing Databases

Creating and Managing Users

Managing Stored Procedures and Functions

Creating and Managing Sequences

Managing Storage Space and Tablespaces

Defining Events and Scheduled Tasks

Backing Up and Restoring Databases

Creating and Managing Virtual Tables

Loading and Managing Extensions

Inserting Data

Updating Data

Deleting Data

Merging Data

Data Copying

Data Deletion and Backup

Data Cleanup and Maintenance

Transaction Processing in Data Operations

Data Conversion and Formatting

Data Statistics and Analysis

Data Random Generation

Data Verification and Validation

Data Cleaning and Filtering

Data Aggregation and Classification

Data Partitioning and Summary

Data Connection and Matching

Data Time Processing and Calculation

Data Sequencing and Numbering

Data Archiving and Compression

Data Auditing and Logging

Starting Transactions

Committing Transactions

Rolling Back Transactions

Savepoints and Rolling Back to Savepoints

Nested Transactions (Simulated through Savepoints)

Checking Transaction Consistency and Integrity

Exception Handling in Transactions

Advanced Transaction Management

Using Transactions for Atomic Operations

Rolling Back to Specific Savepoints

Multiple Savepoints and Rollbacks

Simple Queries

Conditional Queries

Aggregate Functions

Grouping Queries

Sorting Queries

Join Queries

Subqueries

Union Queries

Window Functions

Arithmetic Expressions

Comparison Operators

Common Keywords

Logical Operators

Fuzzy Queries

Glob Clause

Case Statements

Common Table Expressions (CTEs)

Queries with LIMIT and OFFSET

SQLite Core Functions

String Functions

Numeric Functions

Date and Time Functions

Aggregate Functions

Type Conversion Functions

Other Functions

SQLite Native Syntax

ANALYZE