SQL Queries Basic

Tasks to accomplish with Todos table.

Inserting database table(Todos) of below schema.

Insert Query:

insert into todos (title, description, priority, complete) values ('Go to store', 'To pick up eggs', 4, False);

insert into todos (title, description, priority, complete) values ('Haircut', 'Need to get length 1mm', 3, False)

adding one record at a time. Until have all records inserted.

Select Query:

select * from todos;

* -> Means all row and columns from table.

select title from todos; -> for displaying only title column information.

select title, description from todos; -> multi column selection at a time.

It help to select column for display.

Where clause:

select * from todos where priority=5;

It help to filter records by using condition.

Update clause:

update todos set complete=Ture where id=5;

you have to filter the record for updating specific column of that record.

update todos set complete=True where title='Learn something new';

Delete clause:

delete from todos where id = 5;

RDBMS software provide temporary delete option as well.

delete from todos where complete=0;

It will be a risky query as you not sure of number of records you are deleting.

Sample code and working on terminal.

Microsoft Windows [Version 10.0.26100.4061]

C:\Users\punee\OneDrive\Desktop\FastAPI\virtualenvFastapi>cd TodoApp

C:\Users\punee\OneDrive\Desktop\FastAPI\virtualenvFastapi\TodoApp>sqlite3 todos.db

'sqlite3' is not recognized as an internal or external command,

operable program or batch file.

C:\Users\punee\OneDrive\Desktop\FastAPI\virtualenvFastapi\TodoApp>

* History restored

Microsoft Windows [Version 10.0.26100.4061]

C:\Users\punee\OneDrive\Desktop\FastAPI\virtualenvFastapi>sqlite3

SQLite version 3.50.0 2025-05-29 14:26:00

Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

sqlite> .quit

C:\Users\punee\OneDrive\Desktop\FastAPI\virtualenvFastapi>cd TodoApp

C:\Users\punee\OneDrive\Desktop\FastAPI\virtualenvFastapi\TodoApp>sqlite3 todo.db

SQLite version 3.50.0 2025-05-29 14:26:00

Enter ".help" for usage hints.

sqlite> .help

.archive ... Manage SQL archives

.auth ON|OFF Show authorizer callbacks

.backup ?DB? FILE Backup DB (default "main") to FILE

.bail on|off Stop after hitting an error. Default OFF

.cd DIRECTORY Change the working directory to DIRECTORY

.changes on|off Show number of rows changed by SQL

.check GLOB Fail if output since .testcase does not match

.clone NEWDB Clone data into NEWDB from the existing database

.connection [close] [#] Open or close an auxiliary database connection

.crlf ?on|off? Whether or not to use \r\n line endings

.databases List names and files of attached databases

.dbconfig ?op? ?val? List or change sqlite3_db_config() options

.dbinfo ?DB? Show status information about the database

.dbtotxt Hex dump of the database file

.dump ?OBJECTS? Render database content as SQL

.echo on|off Turn command echo on or off

.eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN

.excel Display the output of next command in spreadsheet

.exit ?CODE? Exit this program with return-code CODE

.expert EXPERIMENTAL. Suggest indexes for queries

.explain ?on|off|auto? Change the EXPLAIN formatting mode. Default: auto

.filectrl CMD ... Run various sqlite3_file_control() operations

.fullschema ?--indent? Show schema and the content of sqlite_stat tables

.headers on|off Turn display of headers on or off

.help ?-all? ?PATTERN? Show help text for PATTERN

.import FILE TABLE Import data from FILE into TABLE

.indexes ?TABLE? Show names of indexes

.intck ?STEPS_PER_UNLOCK? Run an incremental integrity check on the db

.limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT

.lint OPTIONS Report potential schema issues.

.load FILE ?ENTRY? Load an extension library

.log FILE|on|off Turn logging on or off. FILE can be stderr/stdout

.mode ?MODE? ?OPTIONS? Set output mode

.nonce STRING Suspend safe mode for one command if nonce matches

.nullvalue STRING Use STRING in place of NULL values

.once ?OPTIONS? ?FILE? Output for the next SQL command only to FILE

.open ?OPTIONS? ?FILE? Close existing database and reopen FILE

.output ?FILE? Send output to FILE or stdout if FILE is omitted

.parameter CMD ... Manage SQL parameter bindings

.print STRING... Print literal STRING

.progress N Invoke progress handler after every N opcodes

.prompt MAIN CONTINUE Replace the standard prompts

.quit Stop interpreting input stream, exit if primary.

.read FILE Read input from FILE or command output

.recover Recover as much data as possible from corrupt db.

.restore ?DB? FILE Restore content of DB (default "main") from FILE

.save ?OPTIONS? FILE Write database to FILE (an alias for .backup ...)

.scanstats on|off|est Turn sqlite3_stmt_scanstatus() metrics on or off

.schema ?PATTERN? Show the CREATE statements matching PATTERN

.separator COL ?ROW? Change the column and row separators

.session ?NAME? CMD ... Create or control sessions

.sha3sum ... Compute a SHA3 hash of database content

.shell CMD ARGS... Run CMD ARGS... in a system shell

.show Show the current values for various settings

.stats ?ARG? Show stats or turn stats on or off

.system CMD ARGS... Run CMD ARGS... in a system shell

.tables ?TABLE? List names of tables matching LIKE pattern TABLE

.timeout MS Try opening locked tables for MS milliseconds

.timer on|off Turn SQL timer on or off

.trace ?OPTIONS? Output each SQL statement as it is run

.version Show source, library and compiler versions

.vfsinfo ?AUX? Information about the top-level VFS

.vfslist List all available VFSes

.vfsname ?AUX? Print the name of the VFS stack

.width NUM1 NUM2 ... Set minimum column widths for columnar output

.www Display output of the next command in web browser

sqlite> .schema

sqlite> .quit

C:\Users\punee\OneDrive\Desktop\FastAPI\virtualenvFastapi\TodoApp>sqlite3 todos.db

SQLite version 3.50.0 2025-05-29 14:26:00

Enter ".help" for usage hints.

sqlite> .schema

CREATE TABLE todos (

id INTEGER NOT NULL,

title VARCHAR,

description VARCHAR,

priority INTEGER,

complete BOOLEAN,

PRIMARY KEY (id)

);

CREATE INDEX ix_todos_id ON todos (id);

sqlite> insert into todos (title, description, priority, complete) values ('Go to store', 'To pick up eggs', 4, False);

sqlite> select * from todos;

1|Go to store|To pick up eggs|4|0

sqlite> insert into todos (title, description, priority, complete) values ('Cut the lawn', 'Grass is getting long',

3, False);

sqlite> select * from todos;

1|Go to store|To pick up eggs|4|0

2|Cut the lawn|Grass is getting long|3|0

sqlite> insert into todos (title, description, priority, complete) values ('Feed the dog', 'He is getting hungry', 5, False);

sqlite> select * from todos;

1|Go to store|To pick up eggs|4|0

2|Cut the lawn|Grass is getting long|3|0

3|Feed the dog|He is getting hungry|5|0

sqlite> .mode column

sqlite> select * from todos;

id title description priority complete

-- ------------ --------------------- -------- --------

1 Go to store To pick up eggs 4 0

2 Cut the lawn Grass is getting long 3 0

3 Feed the dog He is getting hungry 5 0

sqlite> .mode markdown

sqlite> select * from todos;

|----|--------------|-----------------------|----------|----------|

| 1 | Go to store | To pick up eggs | 4 | 0 |

| 2 | Cut the lawn | Grass is getting long | 3 | 0 |

| 3 | Feed the dog | He is getting hungry | 5 | 0 |

sqlite> .mode box

sqlite> select * from todos;

┌────┬──────────────┬───────────────────────┬──────────┬──────────┐

│ id │ title │ description │ priority │ complete │

├────┼──────────────┼───────────────────────┼──────────┼──────────┤

│ 1 │ Go to store │ To pick up eggs │ 4 │ 0 │

│ 2 │ Cut the lawn │ Grass is getting long │ 3 │ 0 │

│ 3 │ Feed the dog │ He is getting hungry │ 5 │ 0 │

└────┴──────────────┴───────────────────────┴──────────┴──────────┘

sqlite> .mode table

sqlite> select * from todos;

+----+--------------+-----------------------+----------+----------+

+----+--------------+-----------------------+----------+----------+

| 1 | Go to store | To pick up eggs | 4 | 0 |

| 2 | Cut the lawn | Grass is getting long | 3 | 0 |

| 3 | Feed the dog | He is getting hungry | 5 | 0 |

+----+--------------+-----------------------+----------+----------+

sqlite> insert into todos (title, description, priority, complete) values ('Test element', 'He is getting hungry', 5

, False);

sqlite> select * from todos;

+----+--------------+-----------------------+----------+----------+

+----+--------------+-----------------------+----------+----------+

| 1 | Go to store | To pick up eggs | 4 | 0 |

| 2 | Cut the lawn | Grass is getting long | 3 | 0 |

| 3 | Feed the dog | He is getting hungry | 5 | 0 |

| 4 | Test element | He is getting hungry | 5 | 0 |

+----+--------------+-----------------------+----------+----------+

sqlite> delete from todos where id=4;

sqlite> select * from todos;

+----+--------------+-----------------------+----------+----------+

+----+--------------+-----------------------+----------+----------+

| 1 | Go to store | To pick up eggs | 4 | 0 |

| 2 | Cut the lawn | Grass is getting long | 3 | 0 |

| 3 | Feed the dog | He is getting hungry | 5 | 0 |

+----+--------------+-----------------------+----------+----------+

sqlite> insert into todos (title, description, priority, complete) values ('A new test element', 'He is getting hungry', 5, False);

sqlite> select * from todos;

+----+--------------------+-----------------------+----------+----------+

+----+--------------------+-----------------------+----------+----------+

| 1 | Go to store | To pick up eggs | 4 | 0 |

| 2 | Cut the lawn | Grass is getting long | 3 | 0 |

| 3 | Feed the dog | He is getting hungry | 5 | 0 |

| 4 | A new test element | He is getting hungry | 5 | 0 |

+----+--------------------+-----------------------+----------+----------+

sqlite> delete from todos where id = 4;

sqlite> select * from todos;

+----+--------------+-----------------------+----------+----------+

+----+--------------+-----------------------+----------+----------+

| 1 | Go to store | To pick up eggs | 4 | 0 |

| 2 | Cut the lawn | Grass is getting long | 3 | 0 |

| 3 | Feed the dog | He is getting hungry | 5 | 0 |

+----+--------------+-----------------------+----------+----------+

sqlite>

Search This Blog

Fast API learning