Basic SQLite Commands

Photo by Max Duzij on Unsplash

Being able to work with a database is important. With SQLite we’re able to create new data tables, retrieve certain information from those tables, run queries to match certain data we want to retrieve, and much more. Here are the SQL commands that are most commonly used when working with a SQLlite database.

Creating a table

When creating a new table for a database, the command we use is “CREATE TABLE” followed by the name of the table and the attributes you want to include and their respective data types. It’s also important to have some form of ID in your tables and list it as a primary key

CREATE TABLE bears (id INTEGER PRIMARY KEY, name TEXT, age INTEGER, gender TEXT, color TEXT, temperament TEXT);

Inserting data into a table

To insert data into our newly created table we use “INSERT INTO” with the name of the table we’re inserting data into. Then we list the attributes of the table we want to add, followed by the keyword “VALUES” with the values of the new data you’re inserting matching the order of the attributes.

INSERT INTO bears (id, name, age, gender, color, temperament, alive) VALUES(1,"Pooh", 20, "Male", "dark brown", "calm")

Adding Columns

Let’s say that, after creating a database and creating a table to live inside that database, we decide we want to add a column. We can do so with the “ALTER TABLE” statement.


Delete a table

Deleting a table is very simple we just simply use the “DROP TABLE” statement followed by the name of the table


Retrieving data from a table

So we have data within our table now and now we want to retrieve some of that data. The “SELECT” Statement helps us do just that. We select the names of the columns we are going to select(you can use a * if you want to select all) then use the FROM keyword and pick the table we are selecting from.

SELECT * From bears;

But this will return us every instance from that table, if we want to be more specific on which data we want to get back we would include conditionals using the “WHERE” statement.

SELECT * FROM bears WHERE name = "Pooh";SELECT * from bears WHERE age > 5;

Updating data

If we wanted to update, or change, data in our table rows. We do this with the “UPDATE” keyword. The Update statement uses a “WHERE” clause to grab the row you want to update. It identifies the table name you are looking in and resets the data in a particular column to a new value.

UPDATE bears SET age = 21 WHERE name = "Pooh";

Delete data from a table

If we wanted to delete some data from a table we use the “DELETE” statement, the table we want to access, then the conditional to find a specific piece of data to delete

DELETE FROM bears WHERE id = 1;

Ordered list

If we wanted to retrieve data in a certain order based on certain attributes in a table we use the “ORDER BY” statement. This modifier allows us to order the table rows returned by a certain “SELECT” statement. we can use “ASC” for ascending order and “DESC” for descending order.



If we want to select extremes from a database table — for example, what if we only wanted the youngest or oldest bear? we can use ORDER BY in conjunction with LIMIT.

LIMIT is used to determine the number of records you want to return from a dataset.



Count is a SQL aggregate function that will count the number of records that meet a certain condition.

SELECT COUNT(gender) FROM bears WHERE gender = "Female";

This will return us the total count of bears in our database that are female.

Grouping data

If we wanted to group our data based on certain column we use the “GROUP BY” statement.

SELECT gender, COUNT(gender) FROM bears GROUP BY gender;

This will return us a data table with a count of how many male and female bears we have.

These are some of the common SQLite commands that are used mst frequently and are good to have handy!




Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

FlowKat — aggregate messages and metrics from your Kafka Cluster in a cozy application

What are the most common technology stacks for mobile + web apps development?

Leapfrog to JFrog's Artifactory with Oracle Developer Cloud Service

The Golden Rule of Clean Code

The ultimate cheat sheet for a faster environment set up using WebHooks

10 Articles Java Developers Should Read this Week

Python command-line script for PostgreSQL bulk insert, update, update-on-conflict

Horizontal Pod Autoscaler in Kubernetes

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Sean Dever

Sean Dever

More from Medium

Mongo DB


CS373 Spring 2022: Kristina Zhou