SQL Basics : Create, Insert and Select Statements

Install Sqlite3

Install the sqlite3 gem.

$ gem install sqlite3
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

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', 'bbunny@disney.com');
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', 'bbunny@disney.com');

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|bbunny@disney.com

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|bbunny@disney.com

Let's insert a second row into the users table.

sqlite> INSERT INTO users(id, first_name, last_name, email)
   ...> VALUES(2,'daffy', 'duck', 'daffy@disney.com');

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       bbunny@disney.com
2           daffy       duck        daffy@disney.com 

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', 'ppig@disney.com');
sqlite> INSERT INTO users(id, first_name, last_name, email)
   ...> VALUES(4,'pluto', 'dog', 'pdog@disney.com');
sqlite> INSERT INTO users(id, first_name, last_name, email)
   ...> VALUES(5,'pluto', 'pet', 'pdog@disney.com');
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       bbunny@disney.com
2           daffy       duck        daffy@disney.com

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         ppig@disney.com
4           pluto       dog         pdog@disney.com

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       bbunny@disney.com
2           daffy       duck        daffy@disney.com 
3           porky       pig         ppig@disney.com  
4           pluto       dog         pdog@disney.com  
5           pluto       pet         pdog@disney.com  

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 article, 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.


Related Articles


Ace the Technical Interview

  • Easily find the gaps in your knowledge
  • Get customized lessons based on where you are
  • Take consistent action everyday
  • Builtin accountability to keep you on track
  • You will solve bigger problems over time
  • Get the job of your dreams

Take the 30 Day Coding Skills Challenge

Gain confidence to attend the interview

No spam ever. Unsubscribe anytime.