Basic SQLite commands

In this article, we have listed and explained how to use some of the basic SQLite commands in detail.

Table of contents:

1. Introduction to SQL and SQLite
2. Basic SQLite commands to get you started quickly

Introduction to SQL and SQLite

SQL stands for Structured Query Language is a standard programming language used for managing relational data. It is used to create, update and delete data in database and also to retrieve data.

You can perform all sorts of task in a database like creating new databases, creating tables and columns. It is used by database administrators, developers data scientist to analyze the data by using several filtering methods like insert, update and delete data. It is backed my many relational database management systems like MySQL, Oracle and SQL servers.

Relational database managament system is a type of database that organinzes database in rows and columns forming relations between the data, which makes it easier to find and update data.

We will be using SQLite which is lighter and faster version of SQL which is beginner friendly and you can later transition into SQL easier after this.

Basic SQLite commands to get you started quickly

First step would to make sure you have a SQL database with .db extension and install SQL lite (sudo apt install sqlite for linux) latest version on you computer to get started.

open terminal in the folder where you databse file exist and open it with the this command.

sqlite3 database.db

it will show a prompt like this

now you can run your commands to search, filter, add, subtract, insert, delete etc commands in this prompt for the opened database.

SELECT * FROM "database";

To select from all the data. here SELECT is SQL commands to select and display the selected data and '*' represents to select all data and 'database' is the name of the database of the file.

It is convention to write SQL commands in Capital letter, it will work in small letters too, but later down the line it will cause many problem, so it is better to follow the convention.

SELECT anything FROM "database";
To select a specific topic from database

SELECT "title" FROM "longlist" LIMIT 10;
limit the title search to 10.

SELECT "title", "author" FROM "longlist" WHERE "year" = 2023;

WHERE command filters the data. like it is searching for the title and authors having 2023 as year.

SELECT "title", "format" FROM "longlist" WHERE "format" != 'hardcover'; 
SELECT "title", "format" FROM "longlist" WHERE "format" <> 'hardcover'; 
SELECT "title", "format" FROM "longlist" WHERE NOT "format" = 'hardcover';

'!=', '<>' and NOT is working as same which is not equals to

SELECT "title", "author" FROM "longlist" WHERE "year" = 2022 OR "year" = 2023;
SELECT "title", "author" FROM "longlist" WHERE "year" = 2022 OR "year" = 2023 AND "format" != 'hardcover;

SELECT "title", "author" FROM "longlist" WHERE "title" LIKE '%love%'; (search for a word in a sentence everywhere)
SELECT "title", "author" FROM "longlist" WHERE "title" LIKE 'The%'; (search the sentece beginnig with The)
SELECT * FROM table_name WHERE column_name LIKE '%\%%' ESCAPE '\' (to select a sentence begginning with % sign )

LIKE searches for specific words in the given title or any data.

• in first example, it will search for the literal word love in title and author data.
• In second example, it will search sentence or word begginng with The
• In third exmaple, it will search for the literal '%' percent symbol as it is tricky because it uses % to case the words.

SELECT "title", "author" FROM "longlist" WHERE "title" LIKE 'P_re'; (search the data for pyre of pire of any word)
SELECT "title", "author" FROM "longlist" WHERE "title" LIKE 'ty___';

If you are not sure of the spelling of the term you want to search use _ (underscore) symbol to replace the letters you don't know.

SELECT "title", "year" FROM "longlist" WHERE "year" >= 2019 AND "year" <= 2023; (range)
SELECT "title", "year" FROM "longlist" WHERE "year" BETWEEN 2019 AND 2023; (range)
SELECT "title", "year" FROM "longlist" WHERE "rating" > 4.0;
SELECT "title", "year" FROM "longlist" WHERE "rating" > 4.0 AND "votes" > 10000;

You can also use '>' grater than, '<' less than and BETWEEN with AND filter to search between a range of data.

SELECT "title", "rating" FROM "longlist" ORDER BY "rating" DESC LIMIT 10;
SELECT "title", "rating", "votes"  FROM "longlist" ORDER BY "rating" DESC, "votes" DESC LIMIT 10;

ORDER BY is used to order the data in ascending ASCE or descending DESC order. You can add LIMIT or any function to further filter your data.

SELECT AVG("rating"), FROM "longlist";
SELECT ROUND(AVG("rating"), 2) FROM "longlist";
SELECT ROUND(AVG("rating"), 2) AS "average rating" FROM "longlist";

You can use Math functions too, like average AVG, ROUND (to round up the decimal point), MAX, MIN, SUM etc.

SELECT COUNT(*) FROM "longlist"; 
SELECT COUNT(DISTINCT "publisher") FROM "longlist";

COUNT function to count rumber of rows in database you can add DISTINCT to count a specific term in the database.

It is mandatory to add semicolon ' ; ' at the end of the command to tell the programme that command is over.
you can type commands in different line if things make you confusing to right in one line like.

SELECT "title", "rating", "votes"
FROM "longlist" 
ORDER BY "rating" DESC, "votes" DESC 
LIMIT 10;

With this article at OpenGenus, you must have a good idea of the basic SQLite commands.