Table of Contents

SQLite

sqlite3 database.db sqlite> create table student(id integer, name text); sqlite> .schema insert into student values (111, “Smith”); select * from student

make new table: create table takes (id integer, course_id integer, grade integer);

joining / combining data from differet tables: select same from student, takes where student.id = takes.id and takes.course_id = 6242;

summarizing data (find id and avg grade): select id, avg(grade) from takes group by id;

filtering summarized results: select id, avg(grade) from takes group by id having avg(grade) > 90;

SQL General Form:

select a1, a2, … an from t1,t2 … tm where predicate [order by…] [group by…] [having…]

sqlite uses b-tree structure

create index student_id_index on student(id);