SQL = Structured Query Language.
All we are interested in is
CRUD. We want to learn how to create (insert), read (select), update and delete data. To continue any further, Download the SQLite Browser. It makes the work here easier.
Let's create a database,
sample.db, and save it into a folder of any choice. We recommend the folder in which you have done the practicals.
Copy and paste this SQL code into windows ( text area) when we click on the
Execute SQL tab.
CREATE TABLE `test_tb` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `name` TEXT );
- This code creates a table with the name,
- There are two fields in the table,
idfield has some properties
INTEGER- the type of data to store
NOT NULL- the column value must not be empty or null
PRIMARY KEY- makes every row unique
AUTOINCREMENT- increase the
PRIMARY KEYsequentially. Thus we ignore the values for the
idfield because it is
namefield has only one property, ie. the data type is a
We shall use this table in this discussion.
We may add data (a row) to the table by inserting.
INSERT INTO `test_tb` (`name`) VALUES('John Doe');
INSERT INTO `test_tb` (`name`) VALUES ('Swift Python'), ('kirito'), ('kevin'), ('spit fire');
Reading is done by selecting.
This will read all the data and with all the field displaying.
SELECT * FROM `test_tb`;
This will read all the data but display only the
SELECT `name` FROM `test_tb`;
And this will read all the data but display only the
SELECT `id` FROM `test_tb`;
This will read all the data where the
name field is equal to
SELECT * FROM `test_tb` WHERE `name` = 'John Doe';
This will read a row whose column (id) value equals 3
SELECT * FROM `test_tb` WHERE `id` = 3;
This will read a row whose column (id) value greater than 3
SELECT * FROM `test_tb` WHERE `id` > 3;
Let us update a row, with
id = 1 and change the
name value to
UPDATE `test_tb` SET `name` = 'Terry' WHERE `id` = 1;
Delete the row with
id = 1
DELETE FROM `test_tb` WHERE `id` = 1;
Delete the row with
name = 'kirito'
DELETE FROM `test_tb` WHERE `name` = 'kirito';
Be careful when we do this.
DELETE FROM `test_tb;
SQL is case insensitive
use the DB Browser to create some tables and experiment with them.
- SQL is the language of the databases.
- Inserting, reading, updating and deleting data is feasible using SQL on SQLite.