Create, Insert and Select Statements
In this chapter, you will learn how to create a database, create tables, insert data and retreive data from database.
Install Sqlite3
Install the sqlite3 gem.
$ gem install sqlite3
Output of running the command.
Fetching: sqlite3-1.3.9.gem (100%)
Building native extensions. This could take a while...
Successfully installed sqlite3-1.3.9
1 gem installed
Create a Database
When you type sqlite3 with a file name that ends in .sqlite3, sqlite3 gem will automatically create an empty database.
$ sqlite3 play.sqlite3
SQLite version 3.7.7 2011-06-25 16:35:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
To view the tables:
sqlite> .tables
This will return no output, since we don't have any tables yet.
Create a Table
Use CREATE TABLE <table name> as follows:
sqlite> CREATE TABLE users(
...> id int primary key not null,
...> first_name char(50),
...> last_name char(50),
...> email char(50) not null);
sqlite> .tables
users
We see only one table called users.
Counting Records
To count the number of records in a table, use count(*) in the select statement as follows:
sqlite> select count(*) from users;
0
Here we see no records, since we don't have any data in the users table yet.
Inserting Rows
Let's insert some rows in the user table.
sqlite> INSERT INTO users(first_name, last_name, email)
...> VALUES('bugs', 'bunny', '[email protected]');
Error: users.id may not be NULL
We get an error because we have a 'not null' constraint on the primary key id of the users table. So, we need to provide it:
sqlite> INSERT INTO users(id, first_name, last_name, email)
...> VALUES(1,'bugs', 'bunny', '[email protected]');
Now if we count again, we get:
sqlite> select count(*) from users;
1
Retrieving All Columns
To retrieve data for all columns, use * after the select keyword as follows:
sqlite> select * from users;
1|bugs|bunny|[email protected]
Let's turn on the column names to be displayed.
sqlite> .headers on
sqlite> select * from users;
id|first_name|last_name|email
1|bugs|bunny|[email protected]
Let's insert a second row into the users table.
sqlite> INSERT INTO users(id, first_name, last_name, email)
...> VALUES(2,'daffy', 'duck', '[email protected]');
sqlite> select count(*) from users;
count(*)
2
Now we have two rows in the users table.
Retrieving Single Column
sqlite> select first_name from users where id=2;
first_name
daffy
Let's make the output pretty with the column name and it's data aligned.
sqlite> .mode column
sqlite> select first_name from users where id=2;
first_name
----------
daffy
If you retrieve all the columns, you will get a nice output:
sqlite> select * from users;
id first_name last_name email
---------- ---------- ---------- -----------------
1 bugs bunny [email protected]
2 daffy duck [email protected]
Retrieving Multiple Columns
To retrieve data for multiple columns from a table, specify the column names after the select keyword separated by a comma, like this:
sqlite> select first_name, last_name from users;
first_name last_name
---------- ----------
bugs bunny
daffy duck
Exercise
Insert three records into users table. One of the record must have the same first_name any one of the existing record.
Answer
sqlite> INSERT INTO users(id, first_name, last_name, email)
...> VALUES(3,'porky', 'pig', '[email protected]');
sqlite> INSERT INTO users(id, first_name, last_name, email)
...> VALUES(4,'pluto', 'dog', '[email protected]');
sqlite> INSERT INTO users(id, first_name, last_name, email)
...> VALUES(5,'pluto', 'pet', '[email protected]');
sqlite> select count(*) from users;
count(*)
----------
5
Limiting Results
To limit the number of rows to retrieve from the database, use the 'limit' keyword:
sqlite> select * from users limit 2;
id first_name last_name email
---------- ---------- ---------- -----------------
1 bugs bunny [email protected]
2 daffy duck [email protected]
To get the next two rows, specify where to start and the number of rows to retrieve:
sqlite> select * from users limit 2 offset 2;
id first_name last_name email
---------- ---------- ---------- ---------------
3 porky pig [email protected]
4 pluto dog [email protected]
In this example it starts from row 3 (offset = 2) and retrieves two records (limit = 2).
Retrieving Distinct Rows
We have 5 records in the users table.
sqlite> select * from users;
id first_name last_name email
---------- ---------- ---------- -----------------
1 bugs bunny [email protected]
2 daffy duck [email protected]
3 porky pig [email protected]
4 pluto dog [email protected]
5 pluto pet [email protected]
If we want to retrieve only unique user names :
sqlite> select DISTINCT(first_name) from users;
first_name
----------
bugs
daffy
pluto
porky
Summary
In this chapter, you learned how to create a new database, create a table, populate the table with data and the basics of SQL SELECT statement. In the next article, you will learn how to sort the retrieved data.