SQLite Database

The header file sqlite3.h is in the directory /usr/local/include on i5.nyu.edu. The sqlite libraries are in the directory /usr/local/lib. The libraries require an older version of g++.

/opt/gcc/bin/g++ -I /usr/local/include -o sqlite sqlite.C -L/usr/local/lib -lsqlite3
ls -l sqlite
./sqlite

Dump the database file

sqlite3 database.db .dump
BEGIN TRANSACTION;
CREATE TABLE people (
        lastname text,
        firstname text,
        ss integer primary key
);
INSERT INTO "people" VALUES('Catalano','Michael',1);
INSERT INTO "people" VALUES('Dafferner','Michael',2);
INSERT INTO "people" VALUES('Fisher','Tristan',3);
INSERT INTO "people" VALUES('Gallucci','Gregory',4);
INSERT INTO "people" VALUES('Hunter','Andrew',5);
INSERT INTO "people" VALUES('Marsala','John',6);
INSERT INTO "people" VALUES('Mehta','Saumil',7);
INSERT INTO "people" VALUES('Meretzky','Mark',8);
INSERT INTO "people" VALUES('Onwuachi','Udoma',9);
INSERT INTO "people" VALUES('Ortiz','Florencia',10);
INSERT INTO "people" VALUES('Pennisten','John',11);
INSERT INTO "people" VALUES('Popa','Cristian',12);
INSERT INTO "people" VALUES('Raghfar','Mahdi',13);
INSERT INTO "people" VALUES('Roberd-Clark','Gates',14);
INSERT INTO "people" VALUES('Singh','Rana',15);
COMMIT;

Be selective

select * from people where lastname like 'M%' order by lastname;
select * from people where lastname like 'M%' and ss < 8 order by lastname;
select firstname, lastname from people where length(firstname) == length(lastname);

Be destructive

delete from people where lastname like 'M%';
delete from people;

Update

update people set firstname = firstname || ', Jr.' where ss > 10;
update people set firstname = replace(firstname, ', Jr.', '') where ss > 10;

Compile on Mac

g++ sqlite.C -lsqlite3