The CS50 Duck has been stolen! The town of Fiftyville has called upon you to solve the mystery of the stolen duck. Authorities believe that the thief stole the duck and then, shortly afterwards, took a flight out of town with the help of an accomplice. The Fiftyville authorities have taken some of the town’s records from around the time of the theft and prepared a SQLite database for you, fiftyville.db, which contains tables of data from around the town.

You need to solve the crime, and fill out the following info in answers.txt (SPOILER ALERT: I filled my correct answers):

• The THIEF is:
• The thief ESCAPED TO:
• The ACCOMPLICE is:

CSV (static unchanging data) = flat-file database (spreadsheet): convenient; but querying is inefficient , especially when db get large

COME the Relational databases: program that manage data (via tables)

Processing CSV files:

from csv import reader

SQLite: each database is just a binary file (filled with 0101010110...)

FUNCTIONS: AVG, COUNT, DISTINCT, LOWER, MAX, MIN, UPPER

CONDITIONS: WHERE, LIKE, ORDER BY, LIMIT, GROUP BY

EXAMPLES:

• SELECT title FROM shows WHERE title LIKE "%Office$"'; NOTE: (%: zero or more characters)
• SELECT DISTINCTS(UPPER(title)) FROM shows ORDER BY UPPER(title);
• SELECT UPPER(title), COUNT(title) FROM shows GROUP BY UPPER(title) ORDER BY COUNT(title) DESC/ASC;
• SELECT UPPER(TRIM(title)), COUNT(title) FROM shows GROUP BY UPPER(TRIM((title)) ORDER BY COUNT(title) DESC/ASC LIMIT 10;
• INSERT INTO shows (Timestamp, title, genres) VALUES("now", "The Muppet Show", "Comedy, Musical");

• PRIMARY KEY: a column in a table that uniquely identifies every row (shows col) -- V.S. Unique key: Primary key will not accept NULL values whereas Unique key can accept one NULL value. (a unique column might have rows with NULL values). A table can have only primary key (column), and multiple unique keys (columns).

• FOREIGN KEY: refer to a column with primary key.

INDEX: helps achieve Logarithmic search time (using a B-tree: wide, and short i.e. A Stocky Tree):

e.g. CREATE INDEX name ON table (column,...);

Watchout for SQL Injection ATTACK: dont use Python f-string inside db.execute commands. For example:

• db.execute(f"... {username} {password}") >> ignore password check = BAD :(
• Use placeholder (?) db.execute(" username = ? AND password = ?", username, password) >> this is good & safe :)