Week 7 (SQL)

Topics

  • SQL
    • Databases
    • Types
    • CREATE TABLE
    • INSERT
    • SELECT
    • UPDATE
    • DELETE
    • SQL Injection
    • Indexes

Programming Exercises

Below are a few example programming exercises that engage students in hands-on practice on the week’s topics. Keep in mind that class might not be long enough to include all exercises, so be sure to pick just a few! For this week, in particular, you might choose one exercise from each of these topics.

These exercises go through the steps for creating a new database, adding tables to that database, and inserting data into a database. It might be best to do some of these steps together, and leave other steps for students to try on their own or in small groups (for instance, you might create the first table together, but let students come up with the schema for other tables).

These examples center around creating tables for keeping track of students, classes, and instructors: feel free to modify these or use your own examples as well!

  • Creating a Database
    • Create a new database by running the command touch students.db
    • Run sqlite by running sqlite3 students.db
    • Create tables: 
      -- people
CREATE TABLE people (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL
);

-- courses
CREATE TABLE courses (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  code TEXT NOT NULL,
  title TEXT NOT NULL
);

-- students
CREATE TABLE students (
  person_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL
);

-- instructors
CREATE TABLE instructors (
  person_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL
);
  • Inserting Data into a Table:
    • SQL 
      -- Add people
INSERT INTO people (name) VALUES ("Alice");
INSERT INTO people (name) VALUES ("Boaz");
INSERT INTO people (name) VALUES ("Charlie");
INSERT INTO people (name) VALUES ("David");
INSERT INTO people (name) VALUES ("Erin");
INSERT INTO people (name) VALUES ("Fiona");
INSERT INTO people (name) VALUES ("Greg");
INSERT INTO people (name) VALUES ("Helen");
INSERT INTO people (name) VALUES ("Irene");
INSERT INTO people (name) VALUES ("Jason");
INSERT INTO people (name) VALUES ("Stuart");

-- Add courses
INSERT INTO courses (code, title) VALUES ("CS50", "Introduction to Computer Science");
INSERT INTO courses (code, title) VALUES ("ECON 10a", "Principles of Economics");
INSERT INTO courses (code, title) VALUES ("CS51", "Abstraction and Design in Computation");
INSERT INTO courses (code, title) VALUES ("CS121", "Introduction to Theoretical Computer Science");
INSERT INTO courses (code, title) VALUES ("CS182", "Artificial Intelligence");

-- Add instructors
INSERT INTO instructors (person_id, course_id) VALUES (4, 1);
INSERT INTO instructors (person_id, course_id) VALUES (10, 2);
INSERT INTO instructors (person_id, course_id) VALUES (11, 3);
INSERT INTO instructors (person_id, course_id) VALUES (2, 4);

-- Add students
INSERT INTO students (person_id, course_id) VALUES (1, 1);
INSERT INTO students (person_id, course_id) VALUES (3, 1);
INSERT INTO students (person_id, course_id) VALUES (5, 1);
INSERT INTO students (person_id, course_id) VALUES (6, 1);
INSERT INTO students (person_id, course_id) VALUES (3, 2);
INSERT INTO students (person_id, course_id) VALUES (6, 2);
INSERT INTO students (person_id, course_id) VALUES (7, 2);
INSERT INTO students (person_id, course_id) VALUES (8, 2);
INSERT INTO students (person_id, course_id) VALUES (7, 3);
INSERT INTO students (person_id, course_id) VALUES (8, 3);
INSERT INTO students (person_id, course_id) VALUES (9, 3);
INSERT INTO students (person_id, course_id) VALUES (1, 4);
INSERT INTO students (person_id, course_id) VALUES (9, 4);
  • SELECT Queries to Answer Questions
    • Sample Solution 
      -- What is Alice's student id?
SELECT id FROM people WHERE name = "Alice";

-- What is the course title for CS51?
SELECT title FROM courses WHERE code = "CS51";

-- What are the course codes and titles for all of the CS courses?
-- (Assume that all CS courses have a course code that begins with 'CS')
SELECT code, title FROM courses WHERE code LIKE "CS%";

-- How many courses are there?
SELECT COUNT(*) FROM courses;

-- How many students are taking CS50?

    -- First, do this in two steps:
    SELECT id FROM courses WHERE code = "CS50"; -- finds that the id is 1
    SELECT COUNT(*) FROM students WHERE course_id = 1; -- using the course id from previous step

    -- Then, combine into a single nested query:
    SELECT COUNT(*) FROM students WHERE course_id = (SELECT id FROM courses WHERE code = "CS50");

    -- Then, show the same result from joining tables:
    SELECT COUNT(*) FROM students JOIN courses ON students.course_id = courses.id WHERE code = "CS50";

-- What are the names of all of the instructors? Generate a table with all instructors' names and the course they teach.
SELECT name, title FROM people JOIN instructors ON people.id = instructors.person_id JOIN courses ON instructors.course_id = courses.id;

-- What are the names of all of the students taking CS50?

    -- First, do this using JOINs:
    SELECT name FROM people JOIN students ON people.id = students.person_id JOIN courses ON students.course_id = courses.id WHERE code = "CS50";

    -- Then, do this with nested queries:
    SELECT name FROM people WHERE id IN (SELECT person_id FROM students WHERE course_id = (SELECT id FROM courses WHERE code = "CS50"));
  • UPDATE and DELETE Queries.
    • Sample Solutions 
      -- Alice wants to switch from CS50 into CS51.

    -- First, do this as a multi-step process.
    SELECT id FROM people WHERE name = "Alice"; -- Alice is student 1
    SELECT id FROM courses WHERE code = "CS50"; -- CS50 is course 1
    SELECT id FROM courses WHERE code = "CS51"; -- CS51 is course 3
    UPDATE students SET course_id = 3 WHERE person_id = 1 AND course_id = 1;

    -- Then, show nested query.
    UPDATE students SET course_id = (SELECT id FROM courses WHERE code = "CS51") WHERE person_id = (SELECT id FROM people WHERE name = "Alice") AND course_id = (SELECT id FROM courses WHERE code = "CS50");

-- CS182 was cancelled.

    DELETE FROM courses WHERE code = "CS182";
  • Python and SQL
    • enroll.py to add new students to the roster and enroll them in courses.
    • Sample Solution: 
      from cs50 import get_string, SQL

db = SQL("sqlite:///students.db")

# Add new person
name = get_string("Name: ")
student_id = db.execute("INSERT INTO people (name) VALUES (?)", name)

# Prompt for courses to enroll in
while True:
    code = get_string("Course Code: ")

    # If no input, then stop adding courses
    if not code:
        break

    # Query for course
    results = db.execute("SELECT id FROM courses WHERE code = ?", code)

    # Check to make sure course exists
    if len(results) == 0:
        print(f"No course with code {code}.")
        continue

    # Enroll student
    db.execute("INSERT INTO students (person_id, course_id) VALUES (?, ?)", student_id, results[0]["id"])
    print(f"Added {name} to {code}")

Conceptual Exercises

These exercises involve thinking and problem-solving, though writing a program is less of the focus here. They still engage students in practice that helps them better understand the week’s concepts!

  • YouTunes
    • Students view a fictional database, youtunes.db and generate criticisms of each table (i.e., sheet) that relate to database design.
    • Afterwards, students generate their own database that addresses some of the flaws in youtunes.db.

Discussion Questions

One technique to promote participation in class is a quick, 2–3 minute discussion question. Letting students posit their own reasoning, even if they’re not entirely sure of an answer, can help reinforce material from lecture. One model for introducing these questions is the “Think, Pair, Share” approach, in which students take 30 seconds to think of their own answer and 60 seconds to share their answer with a partner. Afterwards, you can call on random pairs to share their thinking with the larger group. It’s also best to follow up with your own answer, too!

  • Recall from lecture how SQL indexes speed up queries on the field (column) on which they’re defined. If that’s the case, why wouldn’t it make sense to define an index on every field?

Annotated Sample Slides

Here you’ll find sample slides to adopt or adapt when teaching Week 7.

Some slides contain speaker notes to illustrate why the sample slides take a certain approach to illustrating a concept or leading an exercise. You’re welcome to modify these slides as you see fit, though do try to keep some of the same elements of active learning that have been included in these samples.

Past versions of sample slides are also available under the “Additional Slide Resources” dropdown.