CS44 Weekly Lab: week 13

Topics: script and dos2unix, SQL Practice in sqlite

script, dos2unix

In lab 6 you will capture to a file terminal output from running queries in sqlite, and submit the cleaned-up file with your lab 6 solution. script is useful for capturing terminal contents (stdin, stdout, stderr) to a file. dos2unix helps to clean up the resulting file after a script session.

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 typescript
You can also specify the name of the file that script will write terminal contents to (myoutfile):
script myoutfile
echo "hello there"
dos2unix -f myoutfile
Some more detailed information about script: script and dos2unix. Also look at the man pages for script and dos2unix.

SQL Practice in sqlite

We will be using sqlite in Lab 6 as an embedded language within Python. In lab 6 you will be creating a very large database, defining schema and indices, and running efficient queries on the database.

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.

Getting started

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 universityDB
To 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

sqlite> PRAGMA foreign_keys;
We 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.

Creating Tables

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)
Faculty(id:integer, name:varchar, dept:varchar)
Class(name:varchar, time:varchar, room:varchar, facultyID:integer))
Enrolled(studentID:integer, className:varchar)

As an example this is the SQL command for Student:
  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.

Importing data

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_start

To import, use the following command:
.import 'filename' Table
For example:
.import '/home/newhall/public/cs44/week04/student_start' Student
Load 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;
sqlite> SELECT COUNT(*) FROM Enrolled;
sqlite> SELECT COUNT(*) FROM Faculty;
sqlite> SELECT COUNT(*) FROM Class;
Do 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 

Practice queries

See if you can answer the queries below. The relations are small enough that you can manually check for correctness. Sqlite recognizes most of standard SQL query syntax. You need to remember to terminate an sqlite query with ; . Here is some documentation about queries in sqlite: Queries (try out some or all of these):
  1. Find the names of all CS Majors (Major = 'CS') who are enrolled in the course 'Math06'. (1 result)
  2. Find the name and age of all Junior (Level = 'JR') Math Majors. (2 results)
  3. Find the names of all classes that either meet in room R300 or are taught by 'Kelemen' (7 results)
  4. Find the names of all CS Majors (Major = 'CS') who are enrolled in the course 'Math21' and are older than some Math freshman (Level='FR') (2 results).
  5. For each Level, print the Level and the average age of students of that Level. (4 results)
  6. Find the names of all students who are not enrolled in any class taught by 'Kelemen'. (4 results)
  7. Find the names of all pairs of students who are enrolled in some class together (54 results).
  8. Find the names of faculty members who teach in every room (0 results).
    (you could add tuples to the Class relation to get a non-zero result and see if your query works in this case too)