Transcript SQLite

1
SQLite
CS440
What is SQLite?
2





Open Source Database embedded in Android
SQL syntax
Requires small memory at runtime (250 Kbytes)
Lightweight
More info at: http://www.sqlite.org
CS440
Types in SQLite
3



TEXT (similar to String in Java)
INTEGER (similar to long in Java)
REAL (similar to double in Java)
CS440
Getting started
4



Download SQLite:
http://www.sqlite.org/download.html
Open a command prompt
Create your first db:
 sqlite3
myDB.db
 create table tbl1(one varchar(10), two smallint);
 insert into tbl1 values('hello!',10);
 insert into tbl1 values('goodbye', 20);
 select * from tbl1;
CS440
CREATE
5


Create a new table to add your data
Tables in databases are like excel spreadsheets
CREATE TABLE employers (
_id INTEGER PRIMARY KEY,
company_name TEXT);
CREATE TABLE employees (
name TEXT,
annual_salary REAL NOT NULL CHECK,
employer_id REFERENCES employers(_id));
CS440
SQLite Types
6




TEXT
REAL
BLOB
INTEGER
CS440
INSERT
7

Adds a new data row
INSERT INTO contacts(first_name) VALUES(“Thomas”);
INSERT INTO employers VALUES(1, “Acme Balloons”);
INSERT INTO employees VALUES(“Wile E. Coyote”,
1000000.000, 1);
CS440
SELECT
8


Querying a database
Returns one or multiple rows of results
SELECT * FROM contacts;
SELECT first_name, height_in_meters
FROM contacts
WHERE last_name = “Smith”;
SELECT employees.name, employers.name
FROM employees, employers
WHERE employee.employer_id = employer._id
ORDER BY employer.company_name ASC;
CS440
References
9

http://www.vogella.de/articles/AndroidSQLite/arti
cle.html#overview
CS440