CS50sql is an online introductory course focused exclusively on database management with SQL. It teaches foundational SQL concepts such as querying single and multiple tables, data manipulation (inserting, updating, deleting data), and database design principles, alongside practical skills like schema creation, managing different data types, using aggregate functions, and implementing constraints and indexes.
An important part of the course, it's submitting the problem sets, as well as the final project for feedback. This repository contains the solutions I've come up with for the problem sets.
Also down below are links and short descriptions for each of the projects.
Keep in mind the course's academic honesty. You should try figuring out a solution yourself before looking at other implementations. Also, bear in mind that I am a beginner, I've taken the course to learn, so the solutions might not be the best implementations.
- Notes - contains notes I took from various sources, mostly from each week's notes section.
-
Cyberchase - Write SQL queries to answer questions about a television series' episodes using a provided database.
-
36 Views or Normals - Write SQL queries to analyze a database of Japanese art prints and extract specific information.
-
Players - Write SQL queries to extract specific information about Major League Baseball players from a provided database covering the years 1871 to 2023.
-
"Packages, Please" - Write SQL queries to determine the current address, location type, and contents of missing packages using a mail delivery service's database.
-
DESE - Write SQL queries to analyze data from the Massachusetts Department of Elementary and Secondary Education regarding districts, schools, expenditures, and performance.
-
Moneyball - Write SQL queries to analyze baseball statistics from a database, focusing on player performance and salaries to identify undervalued players for a team.
-
ATL - Design a database schema for an airport to manage passengers, flights, airlines, and check-ins, then write SQL statements to create the tables.
-
Happy to Connect - Design and create a database schema for a professional networking platform, defining tables and relationships for users, schools, companies, connections, and affiliations.
-
Union Square Donuts. - _Design and create a database schema for a donut shop, defining tables and relationships to manage ingredients, donuts, customers, and orders..
-
Don't Panic! - Exploit a SQLite database by altering an admin password, erasing logs, and adding misleading data, all within a time limit.
-
Meteorite Cleaning - Import and clean data from a CSV file on historical meteorite landings into a SQLite database for further analysis by NASA engineers.
-
Census Taker - Create SQL views from provided census data to assist the Nepali government with record-keeping for remote Himalayan villages.
-
The Private Eye - Write SQL queries to decode a hidden message by extracting specific characters from sentences in "The Adventures of Sherlock Holmes" based on given triplets.
-
Bed and Breakfast - Create SQL views from a database of AirBnB data to analyze the platform's impact on the local tourist scene in Boston.
-
In a Snap - Write efficient SQL queries, utilizing indexes, for a fictional messaging app where pictures expire shortly after being viewed, to implement its core features.
-
your.harvard - Optimize a database for a course registration system by creating indexes to improve query performance for managing course and student data efficiently.
-
"Don't Panic!" (Sentimental) in Python - Write a Python program to automate a penetration test on a SQLite database, specifically to connect to it and alter an administrator's password.
-
From the Deep - Analyze trade-offs and design a distributed database system for a remote submarine, determining data partitioning strategies across multiple surface boats for efficiency.
-
Happy to Connect (Sentimental) - Design a MySQL database schema for a professional networking platform, enabling storage of user, school, company, and connection data, including affiliations.
- Final Project - db for my college project
First of all, a huge thank you to Prof. David J. Malan and the rest of the CS50 staff for giving us this free learning opportunity. I thoroughly recommend the CS50 course for anyone who wants to get into or improve their skills in the Computer Science field.
If you'd like to know more about CS50, I'll leave a few link down below.