For this week’s homework, we will continue to practice with the most widely distributed relational DBMS engine available - SQLite. This tutorial will refresh our understanding of DDL and DML (defining a simple relational schema and loading in data), and then give you a chance to design and implement queries. You will submit your solutions for Section 3 using Gradescope. At a minimum, you will submit a homework with your Lab 4 partner; you may join also decide to merge two lab-pairs together for one submission. In other words, your group must be a minimum of 2 (you and your Lab 4 partner) and a maximum of 4 members (you, your Lab 4 partner, and another Lab 4 pair).

1. Warmup

Practice SQL queries in using the online tutorial SQLZoo. You can do this on your own or with a partner. Feel free to skim/skip Tutorials 0 and 1 - they cover some simpler concepts so you can come back if you are struggling with subsequent tutorials. Skim tutorial 2 to make sure you are okay with the basics. Complete tutorials 3,4, and 5 including the quizzes to ensure you have a good understanding of writing various SQL queries.

2. Setting up the universityDB

The rest of this assignment should be done with your Homework 7 group.

To begin, copy my settings file into your home directory to automatically change default sqlite3 settings. You may have already done this in Week 4 SQLite tutorial; if your queries are not printing a header and in lined up columns, that means your defaults are not set up properly and you need to repeat this step:

$ cp /home/soni/public/cs44/.sqliterc ~/.

Next, create a weekly lab director fir this week run sqlite3 on the command line and the name of the database you are creating:

Start by creating a week09 directory in your cs44/weeklylabs subdirectory and copying over some files:

cd ~/cs44/weeklylabs
mkdir week09
cd week09
pwd
 /home/you/cs44/weeklylabs/week09

and then run sqlite3 with the name of your workspace database:

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 (which should have a value of 1):

$ sqlite3 universityDB
-- Loading resources from /home/asas/.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

2.1. Creating Tables

Using relational schema below, create 4 tables using the CREATE TABLE command. Be sure to specify types, primary key constraints (bold), 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,
  major VARCHAR,
  level VARCHAR,
  age INTEGER);

These are the foreign key constraints:

  • studentID refers to Student(id)

  • className refers to Class(name)

  • facultyID refers to Faculty(id)

When done, use .table to see all tables created and .schema to review your full schema.

2.2. Importing data

Most SQL engines have some built-in command to bulk-load entries from some file on disk (as opposed to performing thousands of INSERT VALUE commands). I have provided 4 files, one each for the tables you just defined. These files are located in my public space:

$ ls ~soni/public/cs44/universityDB/
class_start  enrolled_start  faculty_start  student_start

To import, use the following template:

.import 'filename' Table

For example:

.import '/home/soni/public/cs44/universityDB/student_start' Student

Load all four tables in order of dependencies to maintain referential integrity. Errors in importing mean that your schema was not correct; you may have forgotten a column or used the wrong order or types. You will need to use the DROP TABLE command to redefine your schema.

Verify the following queries are correct in your database:

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

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 so you must return to this folder to reload the database.

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

Conversely, if you want to restart, you will need to delete the file in Unix

rm -f universityDB
sqlite3 universityDB

3. Homework

Answer the queries below and submit your solutions through Gradescope HW 7. The files 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 ; . While spacing does not impact query parsing, you may want to use it to make you queries easier to read.

Here is some documentation about queries in SQLite:

SELECT S.name
FROM Student S
WHERE S.level != 'FR' AND S.age >
      (SELECT MIN(S2.age)
      FROM Student S2
      WHERE S2.level = 'FR');

For each query, you will need to submit your query. We have provided the expected result for questions 1 and 2 as well as hints for the others to help you check your work. In Gradescope you will need to submit just the query for questions 1 and 2 and both the SQL query and output for questions 3-7. Recall that you can save your Gradescope submission along the way so you do not have to solve all of the queries in one sitting.

  1. Find the names of all CS Majors (Major = 'CS') who are enrolled in the course 'Math06'. (1 result)

    name
----------
Mo

  2. Find the name and age of all Junior (Level = 'JR') Math Majors. (2 results)

    name        age
----------  ----------
Jo          22
Tanya       21

  3. Find the names of all classes that either meet in room R300 or are taught by 'Kelemen' (7 results)

    C.name
----------
CS10
...(remaining results omitted)

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