Basics of SQL

21 Aug, 2025

For Learning SQL I've used SQLite !

Its a very Simple and lightweight application that uses Structured Query Language (SQL). It does not need a running server or daemon and can be setup within seconds !

LETS BEGIN !

👋 Introduction

I'll keep it short ! SQL is basically a query language (language used to manipulate data in databases), mainly used with Relational Database Management Systems (RDBMS). RDBMS are the DBs which use tables i.e. rows and columns for data storage.

I will use the term "DBs" for Databases through out this thing and keep the tone personal and less formal.

📦 Installing SQLite

Installing and using SQLite is pretty straightforward ! Head over to the Download Page of SQLite and download the sqlite-tools as per your Operating System and CPU architecture.

In there you will have the command line tool. In my case I am on windows 11 and am running a x64 architecture so I download this ! Follow the same if you are on a similar system.

Once downloaded there are a bunch of tools inside and we are going to need the command line tool i.e. sqlite3 binary (on windows its a .exe file), Extract it (Yes you will need a software to extract zip files, windows 11 has a built-in zip extractor, or you can use WinRAR or 7-Zip) wherever you want it.

Next you will need to set the sqlite3 binary path as an environment variable of your OS.

Copy the path where you extracted the sqlite3 binary and head over to advanced system settings and add it to your system's PATH.

We are ready to go now, SQLite is installed successfully and we can use it from anywhere we want !

🚀 Getting Started

We got 2 things to do to get started with SQLite.

1. Create a database : Open up terminal or cmd and write this command.

mkdir db && cd db

This command will create a folder named "db" and navigate into it ! If you want it to be in a specific location navigate to it first then run this command.

cd <path to your desired location>
mkdir db && cd db

2. Create a Database and a table : Now that we are where we want to keep our DBs, we use SQLite to create and handle them.

sqlite3 gameShop.db

This creates a database with the name "gameShop" ! Congrats you have successfully created a database now we create a table. Running that previous command ran SQLite, therefore SQLite is now active and we can query.

Lets make a table called customers !

CREATE TABLE customers(id INTEGER PRIMARY KEY, name VARCHAR(25) NOT NULL, age INTEGER, address VARCHAR(30));

This creates a table named customers with 4 fields -> id, name, age and address. id is an integer and is set as PRIMARY KEY. This ensures that no 2 ids are same in the table.

Next is name, set as VARCHAR(25), it stands for "Variable-character" and 25 is its length. It allows us to store characters, maximum of 25, in this case. NOT NULL makes sure that the field is not empty. i.e. any entry created must fill name !

age is just an integer and address is VARCHAR with length of 30 characters.

The table is created and ready to be used !

🌟 SQL Clauses

An important part of whole SQL, Clauses are TERMS or WORDS which have specific meaning. SQL has its own meaning for these words, Its exactly like how KEYWORDS are in programming languages.

Clauses are the very basics and needed things for querying because without them SQL wont understand a thing.

Clauses can be written in uppercase i.e. capital letters only or lowercase i.e. small letters only but I prefer to write them in uppercase because it makes the query more clear that which parts are clause and which parts are variables.

These are few basic clauses needed for now, as we go on we will discover even more clauses.

⚡ CRUD

CRUD stands for Create Read Update Delete ! The very basics of data handling and manipulation ! The very backbone of any data and database.

We Create Data, Read Data, Update Data and Delete Data ! That is the very essence of CRUD !

Lets Create some data.

1. CREATE

INSERT INTO customers VALUES(1, 'Nexus', 22, 'Cloud');

This creates and entry in our customers table. The sequence in which we insert the data must match the sequence of the columns in our table.

The general syntax for inserting is:

INSERT INTO <table_name> VALUES(value1), (value2), (value3), (...), (value_n);

We can even enter multiple values at the same time by separating using , (comma).

INSERT INTO customers
VALUES
(2, 'Niko', 30, 'Liberty City'),
(3, 'CJ', 24, 'Groove Street'),
(4, 'Tommy', 35, 'Vice City'),
(5, 'Kratos', 40, 'Greece');

Make sure to not enter duplicate ids that will cause SQLite to throw error and not insert those values. And the ; (semicolon) is necessary. It marks end of a query !

2. Read

SELECT * FROM customers;

Storing done, now we read what we stored ! We use the above query to read every entry in the table. SELECT lets one select the columns needed followed by the column name, * in this case denotes "All", means each and every column will be shown. Then we select from table we need the data, customers in this case.

To select just a single column we can do:

SELECT name FROM customers;

To get only specific fields we mention them each separated by , (comma).

SELECT name, age FROM customers;

This will give use just the name and age fields from our table.

The general read syntax for reading is:

SELECT <columns> FROM <table_name>;

And yes don't forget the ; (Semicolon) ! We can apply many filters and conditions on these queries but will do them later.

3. Update

UPDATE customers SET age = 1000 WHERE name = 'Kratos'; 

We can update data as we like for instance in this example I wanted to change the age for the name "Kratos" from what's stored to a new value, 1000 in this case.

We use the UPDATE clause followed by the table name and then we specify what we want to change and to what, then we set the filter using the WHERE clause. That way only the specified rows are affected !

If we had multiple values for the filter i.e. if we had multiple rows with name set as "Kratos", all of them will be affected, all of them will get their age field changed to 1000 !

HERE IS A TRICK : READ IT AS AN ENGLISH SENTENCE NOT A QUERY WILL MAKE IT A BIT MORE CLEAR

Similar to Read we can update multiple Fields by separating fields using , (comma):

UPDATE customers SET address = 'Midgard', age = 1001 WHERE name = 'Kratos'; 

The general syntax for updating is:

UPDATE <table_name> SET <column> = <new_value> WHERE <filter>; 

A quick heads up: Without the filter it will apply the update to every row.

UPDATE <table_name> SET <column> = <new_value>

If the filter is not mentioned using the WHERE clause its gone, each row is is not set as the new value for that column...

4. Delete

DELETE FROM customers WHERE name = 'Kratos';

That's it as easy as it gets. Unlike read or update we cannot delete a single field, we have to delete the whole row itself.

To "delete" a single field we actually update it to be blank or NULL. Trying to set NOT NULL columns to NULL will cause an error.

The general syntax for deleting is:

DELETE FROM <table_name> WHERE <filter>

One thing to keep in mind : Don't do this by mistake ->

DELETE FROM <table_name>

This will delete every row from the table. And then you have to explain to your senior where the data went...🤣

THAT WAS CRUD, at least the basics of it. We do even more CRUD later on !

Well that's a good hands on start with SQL !

Follow More on CRUD to continue from here.

Browse Full SQL Page.

#SQL on the go