1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
-- Create a new table
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER,
email TEXT UNIQUE
);
-- Insert some rows
INSERT INTO users (name, age, email)
VALUES
('Alice', 30, 'alice@example.com'),
('Bob', 25, 'bob@example.com'),
('Charlie', 35, 'charlie@example.com');
-- Query all rows
SELECT * FROM users;
-- Filter results
SELECT name, age FROM users WHERE age > 28;
-- Update a row
UPDATE users SET age = 31 WHERE name = 'Alice';
-- Delete a row
DELETE FROM users WHERE name = 'Bob';
-- Add a new column
ALTER TABLE users ADD COLUMN city TEXT;
-- Update new column values
UPDATE users SET city = 'Berlin' WHERE name = 'Alice';
UPDATE users SET city = 'Paris' WHERE name = 'Charlie';
-- Aggregate query
SELECT city, COUNT(*) AS user_count FROM users GROUP BY city;
-- Create an index for faster lookups
CREATE INDEX idx_users_email ON users(email);
-- Show table schema
.schema users;
-- Export query results to CSV (in sqlite3 CLI)
.headers on
.mode csv
.output users.csv
SELECT * FROM users;
.output stdout
|