SQLSample.md

Copy & paste this sql code to a sqlite3 terminal.
The terminal can be your pc with a local copy of sqlite3 that ships with Android SDK.
(Check your local Android installation directory, under platform_tools)
You can use a GUI tool, such as http://sqlitebrowser.org/ Or sqliteman, or you can adb shell to your Android emulator, and look for your app under /data/data/....your.pname.here../databases and then type sqlite3 your.db.

sqlite3 

.headers on
.mode column

create table favcol (_id integer primary key not null, username text, fav_color text) ;

select * from favcol;

insert into favcol (_id,username,fav_color) values (1,'amir','blue'),(2,'liat','white'),(2,'david','blue');

    Error: UNIQUE constraint failed: favcol._id

insert into favcol (_id,username,fav_color) values (1,'amir','blue'),(2,'liat','white'),(3,'david','blue');

select * from favcol;


    _id          username    fav_color 
    ----------  ----------  ----------
    1           amir        blue      
    2           liat        white     
    3           david       blue      


insert into favcol (username,fav_color) values ('yaron','green');


    _id         username    fav_color 
    ----------  ----------  ----------
    1           amir        blue      
    2           liat        white     
    3           david       blue      
    4           yaron       green
         
    // notice the autoincrement id


select * from favcol where fav_color='blue';


    _id          username    fav_color 
    ----------  ----------  ----------
    1           amir        blue      
    3           david       blue      



select count(*) from favcol where fav_color='blue';

    count(*)  
    ----------
    2         


select * from favcol where username='Liat';

    // (empty result)


select * from favcol where username like 'L%';

    _id          username    fav_color 
    ----------  ----------  ----------
    2           liat        white     

    // (notice: not case sensitive)



create table colors ('clr' text, 'example' text) ;
insert into colors (clr,example) values ('red','blood'),('white','wall'),('yellow','sun'),('blue','sky'),('white','paper');
select * from colors;

    clr         example   
    ----------  ----------
    red         bloud     
    white       wall      
    yellow      sun       
    blue        sky       
    white       paper     


select * from favcol s join colors c on s.fav_color=c.clr;

    _id          username    fav_color   clr         example   
    ----------  ----------  ----------  ----------  ----------
    1           amir        blue        blue        sky       
    2           liat        white       white       paper     
    2           liat        white       white       wall      
    3           david       blue        blue        sky       


select * from favcol s left join colors c on s.fav_color=c.clr;

    _id         username    fav_color   clr         example   
    ----------  ----------  ----------  ----------  ----------
    1           amir        blue        blue        sky       
    2           liat        white       white       paper     
    2           liat        white       white       wall      
    3           david       blue        blue        sky       
    4           yaron       green                             

    `left join` also returns rows from s that has no match.
    `right join` is not supported in sqlite. (Some other DB's do, such as MySql).



select username,clr,example from favcol s join colors c on s.fav_color=c.clr;

    username    clr         example   
    ----------  ----------  ----------
    amir        blue        sky       
    liat        white       paper     
    liat        white       wall      
    david       blue        sky       



.tables 

    colors  favcol

drop table favcol;
drop table colors;