CS44: week 13

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"
exit
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
    width: 

sqlite> PRAGMA foreign_keys;
foreign_keys
------------
1

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:

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):

INT or INTEGER
CHAR(N) fixed length string of size N
VARCHAR(N) variable-length of up to size N
REAL or FLOAT
DATE (‘yyyy-mm-dd’)
TIME (‘hh:mm:ss’)

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;
COUNT(*)  
----------
15        
sqlite> SELECT COUNT(*) FROM Enrolled;
COUNT(*)  
----------
47        
sqlite> SELECT COUNT(*) FROM Faculty;
COUNT(*)  
----------
6         
sqlite> SELECT COUNT(*) FROM Class;
COUNT(*)  
----------
11

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:

Sqlite SELECT stmt
nested queries in sqlite
sqlite aggregate operators
Sqlite does not have support for op ANY or op ALL to link nested queries, but it does support just op to link them. You can do something like the following in place of op ALL:
```
SELECT S.name
FROM Student S
WHERE S.level != 'FR' AND S.age >    
      (SELECT MIN(S2.age)
      FROM Student S2
      WHERE S2.level = 'FR');
```

Queries (try out some or all of these):