Let's just try something simple:
script # start a script session, the default file name is typescript ls -l # run some example commands that write to stdout history # should only see commands entered since start of script exit # exit the script session ls -l # lists the script output file typescript history # should NOT see commands run in the script session dos2unix -f typescript # clean-up some weird chars in typescript less typescriptYou can also specify the name of the file that script will write terminal contents to (myoutfile):
script myoutfile echo "hello there" exit dos2unix -f myoutfileSome more detailed information about script: script and dos2unix. Also look at the man pages for script and dos2unix.
In our weekly lab from week 4 (squlite tutorial)), we tried out some features with sqlite. Today we are going to try out a few more. In particular, we will try executing SQL queries.
We are going to practice using SQLite. to define a simple relational schema, load in data, and execute SQL queries.
To begin, copy my settings file into your home directory to automatically change default sqlite3 settings:
$ cp /home/newhall/public/cs44/.sqliterc ~/.Next, create a week13 subdirectory, cd into it and run sqlite3 on the command line and the name of the database you are creating. For example:
cd cs44 mkdir week13 cd week13 $ sqlite3 universityDBTo verify that you properly imported settings, you should see a message about "Loading resources". You can also use .show and examine foreign_keys:
$ sqlite3 universityDB -- Loading resources from /home/you/.sqliterc sqlite> .show echo: off explain: off headers: on mode: column nullvalue: "" output: stdout separator: "$" stats: off width: sqlite> PRAGMA foreign_keys; foreign_keys ------------ 1We are going to create some relations, load some data into them, and try out some SQL queries. Since it is easy to make small syntax errors, it is useful to open a "sqlite_commands" file in vim or emacs, edit the SQL commands in the file, and then copy and paste them into sqlite.
Using the given relational schema, create 4 tables using the CREATE TABLE command. I suggest typing these command into a file and cutting and pasting into sqlite3 to create. Be sure to specify types, primary constraints, and foreign key constraints
Student(id:integer, name:varchar, major:varchar, level:varchar, age:integer)As an example this is the SQL command for Student:
Faculty(id:integer, name:varchar, dept:varchar)
Class(name:varchar, time:varchar, room:varchar, facultyID:integer))
CREATE TABLE Student( id INTEGER PRIMARY KEY, name VARCHAR(20), major VARCHAR(10), level VARCHAR(10), age INTEGER);
In the above schema, italics form the primary key. Add foreign key constraints (studentID refers to Student(id), className refers to Class(name), facultyID refers to Faculty(id)). (Some more information about defining foreign keys in sqlite)
SQL types (it is good to use the N max size for VARCHAR):
When done, use .table to see all tables created and .schema to review your full schema.
Most SQL engines have some built-in command to bulk-load entries from some file on disk. I have provided 4 files, one each for the tables you just defined. These files are located in my public space:
$ ls ~newhall/public/cs44/week04/ class_start enrolled_start faculty_start student_startTo import, use the following command:
.import 'filename' TableFor example:
.import '/home/newhall/public/cs44/week04/student_start' StudentLoad all four tables in order of dependencies (do not load Enrolled before Student otherwise your foreign key will not be satisfied).
sqlite> SELECT * FROM Student; id name major level age ---------- ---------- ---------- ---------- ---------- 1111 Mo CS SO 20 1122 Peter English JR 20 1234 Tyler Math FR 18 2222 Jo Math SR 21 2323 Jo Math JR 22 3333 Tanya Math JR 21 4444 Malik CS JR 20 4545 Sarah English SR 21 5555 Chris Math FR 19 6666 Charles English FR 18 6767 Sarah Math FR 18 7777 Josh CS SO 19 7878 Heather Math SR 22 8888 Elmo CS SO 20 9999 Jo Math FR 19 sqlite> SELECT COUNT(*) FROM Student; COUNT(*) ---------- 15 sqlite> SELECT COUNT(*) FROM Enrolled; COUNT(*) ---------- 47 sqlite> SELECT COUNT(*) FROM Faculty; COUNT(*) ---------- 6 sqlite> SELECT COUNT(*) FROM Class; COUNT(*) ---------- 11Do your results match the original files?
At this point, you should be able to exit and reload the database. All contents are saved in the file you originally invoked when running sqlite (i.e., universityDB). Note that the file is saved locally.
sqlite> .exit $ sqlite3 universityDB SQLite version 3.7.9 2011-11-01 00:52:41 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .table Class Enrolled Faculty Student
SELECT S.name FROM Student S WHERE S.level != 'FR' AND S.age > (SELECT MIN(S2.age) FROM Student S2 WHERE S2.level = 'FR');