SpatiaLite

1.1   Basic SQLite commands

• .tables to list all tables in the database.
• .schema table-name to see the schema of a table.
• create table to create a new table.
• autoincrement only support integer so we will use integer for ID columns.
• not null to create a not nullable column.
• drop table to delete an existing table.
• insert to insert a new record to a table.
• select to show records in a table.
• delete to delete records from a table.

2.1   On Bash

# create a temporary directory
mkdir tmp
# see if tmp is created
ls
# move into the tmp directory
cd tmp
# create a new SQLite database file called ung_students.db
sqlite3 ung_students.db

3.1   Student table

create table students (
    student_id integer primary key autoincrement,
    campus_id integer not null,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    on_campus_housing boolean,
    address varchar(100)
);

3.2   Orders table

create table orders (
    order_id integer primary key autoincrement,
    student_id integer not null,
    product_id integer not null
);

3.3   Campuses table

create table campuses (
    campus_id integer primary key autoincrement,
    address varchar(100));

3.4   Products table

create table products (                                                            
    product_id integer primary key autoincrement,
    name varchar(100) not null,
    description text not null,
    cost double precision not null);