Learning SQL - SQLite Basics

These tips are mostly applicable to all forms of SQL, some are specific to SQLite and versions of SQLite like RealSQL Databases and RealSQL Server databases.

What does DDL mean?

DDL stands for Data Definition Language. You use DDL when you create objects like databases, tables, views, triggers etc.

How do I create a Table?

Here is a SQLite DDL script to create a table called QiSQL_Parents that you can use to play along with the following examples - first create yourself a SQLite database using a tool like SQLite Database Browser then execute this SQL script:

create table QiSQL_Parents (
id integer primary key,
first_name text,
last_name text );

How do I drop a Table?

Here's another DDL statement to remove the previously created table - go on have a go!

drop table QiSQL_Parents ;

Now run the table creation statement again to recreate your table

What does DML mean?

DML stands for Data Manipulation Language. You use DML when you INSERT, DELETE or UPDATE data in your database.

How do I add records to table?

Let's add some people to our People table using an INSERT statement:

insert into QiSQL_Parents ( first_name , last_name ) values ( 'John' , 'Smith' );

insert into QiSQL_Parents ( first_name , last_name ) values ( 'Harry' , 'Jones' );

insert into QiSQL_Parents ( first_name , last_name ) values ( 'Linda' , 'Brown' );

insert into QiSQL_Parents ( first_name , last_name ) values ( 'Susan' , 'Brown' );

insert into QiSQL_Parents ( first_name , last_name ) values ( 'Gordon' , 'Brown' );

Run these statements one at a time to add these nice folks to your database

How do I remove records from a table?

Well, lets get rid of Gordon Brown from our database using a DELETE statement!

delete from QiSQL_Parents
where first_name = 'Gordon'
and last_name ='Brown';

What do you think would have happened if we had not specified the first_name where clause above? Yes! Linda Susan Brown would have been wiped out as well as Gordon!

If you forget the WHERE clause altogether then ALL the records in the database will be zapped! Don't worry - you can undo what you just did with a ROLLBACK statement - more about that later!

How do I change a record in a table?

Harry Jones is a bit miffed as his name is actually Harold, so let's change his first_name using an UPDATE statement:

update QiSQL_Parents
set first_name = 'Harold'
where first_name = 'Harry'
and last_name ='Jones' ;

Summary - Insert, Update, Delete

That's DML (Data Manipulation Language) covered - You can Insert , Update and Delete records - easy huh?