A Relational Database (pp. 142–145)

Create a new subdirectory of your home directory on storm.cis.fordham.edu

Go to your home directory on storm.cis.fordham.edu and create a subdirectory named sqlite. Go there and download the \daquo;comma-separated values” files students.csv and majors.csv.

cd
pwd

mkdir sqlite3
ls -ld sqlite3

cd sqlite3
pwd

wget https://markmeretzky.com/fordham/1100/src/students.csv
ls -l students.csv

wget https://markmeretzky.com/fordham/1100/src/majors.csv
ls -l majors.csv

Create and populate a database

Create a database.
In the database, create two tables.
In the students table, create 19 records (rows).
In the majors table, create 12 records.

sqlite3 displays a prompt that looks like this: sqlite3>
Some sqlite3 commands end with a semicolon.
Other sqlite3 commands begin with a period.

sqlite3 -version
sqlite3 -help

sqlite3 database.db     (Create a database file named database.db)
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.

sqlite> create table students (
	fordhamid text,
	lastname text,
	firstname text,
	major text
);

sqlite> create table majors (
	major text,
	fullname text
);

sqlite> .tables
students
majors

sqlite> .schema students
sqlite> .schema majors

sqlite> .mode csv

sqlite> .show
	echo: off
         eqp: off
     explain: auto
     headers: off
        mode: csv
   nullvalue: ""
      output: stdout
colseparator: ","
rowseparator: "\r\n"
       stats: off
       width:
    filename: database.db

sqlite> .import students.csv students
sqlite> .import majors.csv majors

sqlite> select count(*) from students;
19
sqlite> select count(*) from majors;
12

sqlite> select * from students;
ab98,Buoninfante,Armando,XECO
jc208,Chen,Jake,INTS
ac98,Cuadros,Ana,PSYC
etc.

sqlite> .headers on
sqlite> .mode column
sqlite> .show

sqlite> select * from students;

fordhamid   lastname     firstname    major
----------  -----------  -----------  -----
ab98        Buoninfante  Armando      XECO
jc208       Chen         Jake         INTS
ac98        Cuadros      Ana          PSYC
etc.

sqlite> select * from majors;

major  fullname
-----  --------------------------
BIOC   Biochemistry
GLBU   Global Business
INTS   Information Tech & Systems
etc.

Play with the select command

sqlite> select count(*) from students;
sqlite> select count(*) from students where major = "PSYC";
sqlite> select firstname, lastname from students where major = "PSYC";

sqlite> select firstname, lastname from students where major = "PSYC"
   and firstname like "S%";

A “join” operation (p. 144)

The following join command makes sqlite3 behave as if there was another table, with five columns. We print out this new table below. Each row of this new table contains the four columns from a row in the students table, plus the last column from the row in the majors table whose first column matches the last column of the row from the first table.

sqlite> select fordhamid, firstname, lastname, students.major, fullname
	from students join majors
	on students.major = majors.major;

fordhamid   firstname    lastname     major  fullname
----------  -----------  -----------  -----  --------------------------
ab98        Armando      Buoninfante  XECO   Economics
jc208       Jake         Chen         INTS   Information Tech & Systems
ac98        Ana          Cuadros      PSYC   Psychology
etc.

Turn off sqlite3

sqlite> .quit

ls -l database.db       (See if sqlite3 created a new file named database.db)

If you want to turn on sqlite3 again, remember to go to the sqlite3 subdirectory of your home directory first, and give the argument database.db to the sqlite3 command.

cd
cd sqlite3
pwd

sqlite3 database.db