SQLite

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
• delete to delete records from a table (use where to constrain your search)
• select to show records in a table (use where to constrain your search)

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   Campuses table

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

insert into campuses (address) values ('Gainesville');
insert into campuses (address) values ('Dahlonega');
insert into campuses (address) values ('Cumming');
insert into campuses (address) values ('Oconee');
insert into campuses (address) values ('Blue Ridge');

3.2   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);

insert into products (name, description, cost)
       values ('Python basics', 'Optional textbook for GISC 3200K', 50);
insert into products (name, description, cost)
       values ('Web development', 'Optional textbook for GISC 4530K', 100);

3.3   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)
);

insert into students (campus_id, first_name, last_name, on_campus_housing, address)
       values (1, 'Foo', 'Bar', false, 'Oakwood');
insert into students (campus_id, first_name, last_name, on_campus_housing, address)
       values (2, 'Jane', 'Smith', true, 'Dahlonega');

3.4   Orders table

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

insert into orders (student_id, product_id) values (1, 1);
insert into orders (student_id, product_id) values (1, 2);
insert into orders (student_id, product_id) values (2, 2);