Creating and Populating Tables
Before we can do any fancy SQL we need to create some tables using a form of SQL called DDL. You don't really need to know what DDL means but you can make friends at geeky parties by chatting about the two types of SQL, DDL and DML (more later).
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,
last_name text );
How do I delete 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!