Sqlite3


Overview

This page contains some notes on sqlite3.


Getting started

To install sqlite3 under Ubuntu:

sudo apt-get install sqlite3

Let's create and use a simple database named store. Note that lines which are long and wrap around are still just one line:

sqlite3 store.sqlite3

This should take you to a prompt that looks like this:

sqlite>

At that prompt, try the following command:

create table foods (name text, category text, stock integer, atleast integer, buycost real, sellcost real);

The preceding command adds a table named foods to the store database. This is a table of foods. Each entry has a name (product name), type (category name), stock (the number of units in stock), atleast (the minimum number of units that should be stocked), buycost (what you pay per unit), and sellcost (what you sell for per unit).

Add some entries to the table

insert into foods values ("Ben & Jerry Cherry", "dairy", 100, 35, 2.40, 2.99);
insert into foods values ("Tastee Horse Bacon", "meat", 200, 500, 1.00, 2.99);
insert into foods values ("Dryers Half Gallon Bacon Ripple", "meat", 80, 100, 3.75, 4.80);
insert into foods values ("Olden Daze Cucumbersicles", "dairy", 15, 10, 0.25, 0.15);

List everything in the table:

select * from foods;

Show everything that is understocked:

select * from foods where stock < atleast;

Here's everything with a margin of at least 25%:

select * from foods where (sellcost/buycost) >= 1.25;

Show everything that costs customers than $3.00 per unit:

select * from foods where sellcost > 3.00;

List all the dairy products:

select * from foods where category == "dairy";

To exit the database, press Control D. Or type .exit (period exit) and press Enter.

To go into the database again, use this again:

sqlite3 store.sqlite3

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License