Problem Set 7: Fiftyville

Evidence of a 5-point Submission

  • Comments clearly detail the progression of the queries—may include an updating list of suspects
  • Very few pieces of hard-coded information

Evidence of a 4-point Submission

  • Specifically includes a comment in log.sql when the thief, location, and accomplice when uncovered
  • Does not use more than two JOINs in any one query, without creating an index to speed them up
  • Relatively few pieces of hard-coded information

Evidence of a 3-point Submission

  • Does not use more than three JOINs in any one query, without creating an index to speed them up
  • Most every query is preceded by a comment that adequately describes it and logically continues the line of investigation
  • Occasionally uses SELECT * where it would be more appropriate to select a specific column

Evidence of a 2-point Submission

  • Hard-codes IDs into queries
  • May include queries with little or no specification
  • May use many SELECT * where it would be more appropriate to select a specific column
  • May use many SELECT to inspect an entire table (does not use the WHERE keyword)
  • Uses CAST unnecessarily
  • Includes one or more queries with no preceding or subsequent comments

Evidence of a 1-point Submission

  • Hard-codes IDs into queries
  • Line of investigation does not follow a logical progression

Example Implementations (Worse vs. Better)

Inspecting Interviews

Worse Implementation

The example below will return an unnecessarily large output

SELECT transcript FROM interviews;

Better Implementation

The example below specifies the output using relevant information

SELECT * FROM interviews 
WHERE transcript LIKE '%bakery%' AND month = 7 AND day = 28

Inspecting Phone Calls

Worse Implementation

The example below hard-codes information and uses three separate queries to determine the receiver of a phone call

SELECT phone_number
FROM people
WHERE name = "Bruce";

SELECT receiver
FROM phone_calls
WHERE caller = "(367) 555-5533" AND year = 2021
AND month = 7 AND day = 28 AND duration < 60;

SELECT name
FROM people
WHERE phone_number = "(367) 555-8161";

Better Implementation

The example below uses one query with nested sub-queries to avoid hard-coding information

SELECT name FROM people WHERE phone_number = (
    SELECT receiver FROM phone_calls WHERE caller = (
        SELECT phone_number FROM people WHERE name = 'Bruce')
    AND duration < 60 AND month = 7 AND day = 28 LIMIT 1
);