Here is the database schema:
Below is for the database in the respository ../Database/Student database solution....
To start program:
cd
sqlite3 student_data.sqlite
Commands:
sqlite> .schema
CREATE TABLE "Club" ("id" INTEGER PRIMARY KEY NOT NULL ,"name" TEXT NOT NULL );
CREATE TABLE "Status" ("id" INTEGER PRIMARY KEY NOT NULL ,"label" TEXT NOT NULL );
CREATE TABLE "Student" ("id" INTEGER PRIMARY KEY NOT NULL ,"first_name" TEXT NOT NULL ,"last_name" TEXT NOT NULL , "status_id" INTEGER NOT NULL DEFAULT 0);
CREATE TABLE "student_to_club" ("student_id" INTEGER NOT NULL , "club_id" INTEGER NOT NULL , PRIMARY KEY ("student_id", "club_id"));
CREATE TABLE "student_to_supervisor" ("student_id" INTEGER NOT NULL , "supervisor_id" INTEGER NOT NULL , PRIMARY KEY ("student_id", "supervisor_id"));
CREATE TABLE "supervisor" ("id" INTEGER PRIMARY KEY NOT NULL ,"name" TEXT NOT NULL ,"room" TEXT NOT NULL );
sqlite> select count(*) FROM student;
100
sqlite> select count(first_name) FROM student;
100
sqlite> select count() FROM student;
100
sqlite> SELECT * FROM student LIMIT 5;
1|Cara|Rogers|1
2|Ori|Mejia|2
3|Leandra|Stevens|3
4|Danielle|Moody|1
5|Josiah|Barber|1
sqlite> SELECT first_name FROM student LIMIT 5;
Cara
Ori
Leandra
Danielle
Josiah
sqlite> SELECT first_name FROM student ORDER BY first_name ASC LIMIT 5;
Adara
Aileen
Alfreda
Amaya
Amber
sqlite> SELECT first_name FROM student ORDER BY first_name DESC LIMIT 5;
Yeo
Xantha
Wing
Wade
Timothy
sqlite> SELECT first_name, last_name FROM student ORDER BY last_name DESC LIMIT 5;
Galena|Zimmerman
Aileen|Wilkinson
Josephine|Wilkinson
Nerea|Whitney
Elmo|Webb
sqlite> SELECT first_name, last_name FROM student WHERE id=5;
Josiah|Barber
sqlite> SELECT first_name, last_name FROM student WHERE first_name like 'F%';
Fritz|Mccormick
Florence|Lang
sqlite> SELECT id, first_name, last_name FROM student WHERE id BETWEEN 10 and 15;
10|Leroy|Kent
11|Sandra|Carrillo
12|Raya|Thompson
13|Jael|Craig
14|Joshua|Forbes
15|Eve|Hinton
sqlite> SELECT id, first_name, last_name FROM student WHERE id in (15, 23, 6, 56, 9);
6|Wing|Gordon
9|Libby|Osborn
15|Eve|Hinton
23|Magee|Petersen
56|Philip|Parks
sqlite> SELECT id, first_name, last_name FROM student WHERE first_name in ('Libby', 'Philip');
9|Libby|Osborn
56|Philip|Parks
sqlite> SELECT sum(id) FROM student;
5050
sqlite> SELECT min(id) FROM student;
1
sqlite> SELECT max(id) FROM student;
100
sqlite> SELECT first_name, last_name, label FROM student JOIN status ON student.status_id = status.id LIMIT 5;
Cara|Rogers|Sophomore
Ori|Mejia|Senior
Leandra|Stevens|Freshman
Danielle|Moody|Sophomore
Josiah|Barber|Sophomore
sqlite> SELECT first_name, last_name, name FROM student JOIN student_to_club ON student_to_club.student_id = student.id JOIN club ON student_to_club.club_id = club.id LIMIT 5;
Cara|Rogers|Chess
Cara|Rogers|Improvisation
Cara|Rogers|Rugby
Cara|Rogers|Debate
Ori|Mejia|Debate
sqlite> SELECT first_name, last_name FROM student JOIN student_to_club ON student_to_club.student_id = student.id JOIN club ON student_to_club.club_id = club.id WHERE name = 'Chess'LIMIT 5;
Cara|Rogers
Wing|Gordon
Eagan|Hogan
Jael|Craig
Joshua|Forbes
sqlite> CREATE VIEW student_clubs AS SELECT first_name, last_name, name FROM student JOIN student_to_club ON student_to_club.student_id = student.id JOIN club on student_to_club.club_id=club.id;
sqlite> select * from student_clubs LIMIT 5;
Cara|Rogers|Chess
Cara|Rogers|Improvisation
Cara|Rogers|Rugby
Cara|Rogers|Debate
Ori|Mejia|Debate
sqlite> INSERT INTO student(first_name, last_name, status_id) VALUES ('Egon', 'Spengler', 4);
sqlite> UPDATE student SET status_id = (SELECT id FROM status WHERE label = 'Freshman') WHERE id = 101;
sqlite> SELECT * from student WHERE id = 101;
101|Egon|Spengler|3
Bash Command
jessica@mac Student database solution % echo "SELECT * FROM student LIMIT 5;" | sqlite3 -header -separator ' ' student_data.sqlite
id first_name last_name status_id
1 Cara Rogers 1
2 Ori Mejia 2
3 Leandra Stevens 3
4 Danielle Moody 1
5 Josiah Barber 1
