---------------
cd /tmp
wget http://www.sqlite.org/sqlite-3.2.1.tar.gz
tar zxfv sqlite-3.2.1.tar.gz
mkdir build
cd build
../sqlite-3.2.1/configure --prefix=/usr/local/sqlite-3.2.1 --with-tcl=/usr/local/tcl8.4.9/lib
make
make test # Requires tcl/tk. 19,747 tests; takes a few minutes.
make -n install | grep -v tclsh > my_make_install
sh my_make_install
ln -s /usr/local/sqlite-3.2.1/bin/sqlite3 /usr/local/bin/sqlite
---------------
cd /tmp
wget http://www.sqlite.org/sqlite-3.2.1.tar.gz
tar zxfv sqlite-3.2.1.tar.gz
mkdir build
cd build
CFLAGS=-O3 ../sqlite-3.2.1/configure --prefix=/usr/local/sqlite-3.2.1 --without-tcl
make
make install # simple installation without tcl
ln -s /usr/local/sqlite-3.2.1/bin/sqlite3 /usr/local/bin/sqlite
---------------
cd /tmp
wget
http://search.cpan.org/CPAN/authors/id/M/MS/MSERGEANT/DBD-SQLite-1.08.tar.gz
tar zxfv DBD-SQLite-1.08.tar.gz
cd DBD-SQLite-1.08
perl Makefile.PL # requires DBI
make
make test
make install
---------------
sqlite -version # should show 3.2.1
echo "create table T(a,b);" | sqlite simple.db
echo "insert into T values ('October', 9);" | sqlite simple.db
echo "insert into T values ('November', 13);" | sqlite simple.db
sqlite simple.db "insert into t values ('December', 11);"
sqlite simple.db '.tables'
sqlite simple.db '.schema'
sqlite simple.db '.dump'
sqlite simple.db 'select * from T where b > 10;'
sqlite simple.db "insert into T values (3.14159265, 'pi')"
---------------
perl -e 'for (1..3) { printf "insert into T values (\"string_%04d\", %4d);\n", $_, $_; }'
time perl -e 'for (1..1000) { printf "insert into T values (\"string_%04d\", %4d);\n", $_, $_; }' | sqlite simple.db
---------------
rm simple.db
echo "create table T(a,b);" > inserts.sql
echo "begin transaction;" >> inserts.sql
perl -e 'for (1..1000) { printf "insert into t values(\"string_%04d\", %d);\n", $_, $_; }' >> inserts.sql
echo "commit;" >> inserts.sql
time cat inserts.sql | sqlite simple.db
---------------
bunzip2 -dc baseball.sql.bz2 | sqlite bb.db
---------------
sqlite bb.db '.tables'
sqlite bb.db '.schema'
sqlite bb.db
 select count(*) from batting;
 select count(*) from fielding;
 select name,yearid,w,l from teams;
 select name,sum(w),sum(l) from teams group by name;
 select name,sum(w),sum(l),sum(w)/sum(l) from teams group by name;
 select name,sum(w),sum(l),sum(w)/sum(l) as WL from teams group by name order by WL;
---------------
select * from Fielding F where F.pos = "3B";
select * from Fielding F where F.pos = "3B" and F.lgID = "NL";
select M.namefirst, M.namelast from Fielding F, Master M
    where F.pos = "3B" and F.lgID = "NL" and M.playerID = F.playerID;
select M.namefirst, M.namelast, B.sb from Fielding F, Master M, Batting B
    where F.pos = "3B" and F.lgID = "NL" and
    M.playerID = F.playerID
    and B.playerID = M.playerID;
---------------
create index i1 on master(playerid);
create index i2 on fielding(playerid);
create index i3 on batting(playerid);
create index i4 on fielding(lgid);
select M.namefirst, M.namelast, B.sb from Fielding F, Master M, Batting B
    where F.pos = "3B" and F.lgID = "NL" and
    M.playerID = F.playerID
    and B.playerID = M.playerID;
---------------
select max(B.sb) from Fielding F, Master M, Batting B
    where F.pos = "3B" and F.lgID = "NL" and
    M.playerID = F.playerID and
    B.playerID = M.playerID;
select M.namefirst, M.namelast, B.sb from
    Fielding F, Master M, Batting B
    where F.pos = "3B" and F.lgID = "NL" and
    M.playerID = F.playerID and
    B.playerID = M.playerID and
    B.sb = 129;
---------------
select playerid from fielding where pos = "3B" and lgid = "NL";
select playerid,sb from batting where playerid in
    (select playerid from fielding where pos = "3B" and lgid = "NL");
select max(sb) from batting where playerid in
    (select playerid from fielding where pos = "3B" and lgid = "NL");
select playerid,sb from batting where sb = (
    select max(sb) from batting where playerid in
    (select playerid from fielding where pos = "3B" and lgid = "NL"));
select playerid,sb from batting where sb = 129;
---------------