文件列表:

Final Project/
Week 0/
Week 1/
Week 2/
Week 3/
Week 4/
Week 5/
Week 6/
Code_of_Conduct.md
LICENSE

# CS50SQL 2024 Repository

**Welcome to the CS50's Introduction to Databases with SQL repository for the year 2024. This repository houses materials and projects pertaining to the course, offering a structured approach to learning database fundamentals, SQL queries, and practical applications. The organized layout facilitates easy navigation through weekly assignments, exercises, and the final project.**

**This repository contains my solutions for CS50's Introduction to Databases using SQL 2024. Please note that these codes are intended for educational purposes only and should not be used or submitted as your own solutions. Cheating violates the Academic Honesty of the course and undermines the learning process.** ### My Harvard CS50 Intro to Databses using SQL Certificate:  ## Final Project: AnimeHub Database Disclaimer: The following codes are for educational purpose only and not intended to be used / submitted as your own solutions. Cheating violates the Academic Honesty of the course, not to mention it's totally pointless if you actually want to learn programming. ## Table of Contents ### Week 0 | Querying | Problem Sets | Description | | ------------- | ------------- | ------------- | | week 0 | [Cyberchase](https://github.com/mejbass/CS50-Intro-to-Databases-Using-SQL/tree/master/Week%200/Cyberchase) |This problem set involves querying a database named cyberchase.db, with a table named episodes, to extract information related to the Cyberchase animated series. The queries range from listing episode titles to counting the number of episodes within specific time frames and exploring specific conditions using SQL commands. The distribution code provides necessary files, and the correctness of queries can be verified using provided test instructions or check50. | week 0 | [36 Views](https://github.com/mejbass/CS50-Intro-to-Databases-Using-SQL/tree/master/Week%200/36%20Views) |This problem set involves querying a database called views.db, containing information on 72 woodblock prints by Hokusai and Hiroshige, to answer questions about the prints' titles, average colors, counts, and statistical measures, using SQL queries provided in .sql files. Correctness can be verified through manual testing or check50, with specific expected results for each query. | week 0 | [Normals](https://github.com/mejbass/CS50-Intro-to-Databases-Using-SQL/tree/master/Week%200/Normals) |This problem set involves querying a database named normals.db, containing Climate Normal data on ocean surface temperatures at various depths and coordinates. The queries, written in .sql files, aim to extract specific information, such as temperatures at certain coordinates, depths, and the highest and lowest temperatures. The distribution code includes necessary files, and correctness can be verified through manual testing or check50, with expected results provided for each query. | week 0 | [Players](https://github.com/mejbass/CS50-Intro-to-Databases-Using-SQL/tree/master/Week%200/Players) |This problem set involves querying a database named players.db, containing information about MLB players who played from 1871 to 2023. The queries, written in .sql files, aim to extract specific information, such as hometowns, batting sides, debut dates, and player characteristics. The distribution code includes necessary files, and correctness can be verified through manual testing or check50, with expected results provided for each query. ### Week 1 | Relating | Problem Sets | Description | | ------------- | ------------- | ------------- | | week 1 | ["Packages, Please"](https://github.com/mejbass/CS50-Intro-to-Databases-Using-SQL/tree/master/Week%201/Packages%2C%20Please)|This problem set involves locating missing packages in Boston using a provided database. Utilize SQL queries to find each package's current address and details, maintaining a log of queries in log.sql and recording results in answers.txt. | week 1 | [DESE](https://github.com/mejbass/CS50-Intro-to-Databases-Using-SQL/tree/master/Week%201/DESE) |This problem set involves analyzing data from the Massachusetts Department of Elementary and Secondary Education (DESE) to answer questions related to public education in the state. Use SQL queries on the provided dese.db database to extract relevant information and address various aspects of education, such as district operations, expenditures, graduation rates, and more. | week 1 | [Moneyball](https://github.com/mejbass/CS50-Intro-to-Databases-Using-SQL/tree/master/Week%201/Moneyball) |This problem set involves analyzing data from the moneyball.db database, focusing on player salaries, performances, and team statistics in Major League Baseball up until 2001. Use SQL queries to extract relevant information and address various aspects such as average player salaries, specific player histories, team statistics, and more. ### Week 2¨ | Designing | Problem Sets | Description | | ------------- | ------------- | ------------- | | week 2| [ATL](https://github.com/mejbass/CS50-Intro-to-Databases-Using-SQL/tree/master/Week%202/ATL)|This problem set revolves around the analysis of data stored in the moneyball.db database, which encompasses player salaries, performances, and team statistics in Major League Baseball up until 2001. The primary objective is to employ SQL queries to extract pertinent information, addressing diverse aspects such as average player salaries, individual player histories, team statistics, and other key metrics. Through a series of tasks, this problem set assesses your ability to navigate and manipulate baseball-related data, honing your SQL querying skills in a real-world sports analytics context. | week 2 | [Happy to Connect](https://github.com/mejbass/CS50-Intro-to-Databases-Using-SQL/tree/master/Week%202/Happy%20to%20Connect)|This problem set tasks you with designing a SQLite database for LinkedIn, encompassing user details, educational institutions, companies, and connections. The goal is to ensure the database meets LinkedIn's specifications and can effectively store sample data provided in the prompt. | week 2 | [Union Square Donuts](https://github.com/mejbass/CS50-Intro-to-Databases-Using-SQL/tree/master/Week%202/Union%20Square%20Donuts) |This problem set involves designing a SQLite database for Union Square Donuts, a renowned shop in the Boston area. The task is to create a schema.sql file within the "donuts" folder, comprising SQL statements to construct a database suitable for managing the shop's ingredients, donuts, orders, and customer information. The goal is to meet the team's expectations and effectively represent the provided sample data. ### Week 3 | Writing | Problem Sets | Description | | ------------- | ------------- | ------------- | | week 3 | [Don't Panic!](https://github.com/mejbass/CS50-Intro-to-Databases-Using-SQL/tree/master/Week%203/Don't%20Panic) |This task involves simulating a penetration test on a SQLite database. In "hack.sql" within the "dont-panic" folder, write SQL statements to: 1) change the admin password to "oops!", 2) erase password change logs, and 3) add false data framing emily33. Test with `.read hack.sql`, reset with `.read reset.sql`, and check correctness with `check50`. | week 3 | [Meteorite Cleaning](https://github.com/mejbass/CS50-Intro-to-Databases-Using-SQL/tree/master/Week%203/Meteorite%20Cleaning) |This problem involves cleaning and importing a CSV file containing historical meteorite landings into an SQLite database. Write SQL statements in `import.sql` to create and clean a table named `meteorites` in a database named `meteorites.db`. The cleaning process includes handling empty values, rounding decimal values, excluding certain entries, sorting entries, and assigning IDs. Test using `cat import.sql | sqlite3 meteorites.db` and consider using `rm meteorites.db` to reset the database for re-testing. Check correctness with `check50 cs50/problems/2024/sql/meteorites`. ### Week 4 | Viewing | Problem Sets | Description | | ------------- | ------------- | ------------- | | week 4 | [Census Taker](https://github.com/mejbass/CS50-Intro-to-Databases-Using-SQL/tree/master/Week%204/Census%20Taker) |Create a view named `message` in `private.db` to decode a book cipher using the `sentences` table. The `private.sql` file contains the necessary SQL statements to reconstruct the view. Ensure it returns phrases when queried with `SELECT "phrase" FROM "message";`. Utilize the `substr` function for extracting substrings based on the triplet values (sentence number, starting character, and length) provided in the paper. | week 4 | [The Private Eye](https://github.com/mejbass/CS50-Intro-to-Databases-Using-SQL/tree/master/Week%204/The%20Private%20Eye) |Create a view named `message` in `private.db` to decode a book cipher using the `sentences` table. Write the necessary SQL statements in `private.sql` to reconstruct the view, and ensure it returns phrases when queried with `SELECT "phrase" FROM "message";`. Use the `substr` function to extract substrings based on the triplet values (sentence number, starting character, and length) given in the paper. | week 4 | [Bed and Breakfast](https://github.com/mejbass/CS50-Intro-to-Databases-Using-SQL/tree/master/Week%204/Bed%20and%20Breakfast) |Create SQL views in `bnb.db` to analyze AirBnB data, including a view excluding descriptions, one for one-bedroom listings, available dates, frequently reviewed listings, and June vacancies. ### Week 5 | Optimizing | Problem Sets | Description | | ------------- | ------------- | ------------- | | week 5 | [your.harvard](https://github.com/mejbass/CS50-Intro-to-Databases-Using-SQL/tree/master/Week%205) |Create SQL indexes in `harvard.db` to optimize querying for the my.harvard database. Analyze typical SELECT queries, use EXPLAIN QUERY PLAN to identify where indexes can improve performance, and write corresponding SQL statements in `indexes.sql`. Balance the trade-off between speed and disk space, minimize the number of indexes, and use DROP INDEX to remove unnecessary indexes. Aim to enhance the efficiency of my.harvard's database for common queries. ### Week 6 | Scaling | Problem Sets | Description | | ------------- | ------------- | ------------- | | week 6 | [Happy to Connect (Sentimental)](https://github.com/mejbass/CS50-Intro-to-Databases-Using-SQL/tree/master/Week%206/Happy%20to%20Connect%20(Sentimental)) |Create a MySQL database schema for LinkedIn in a file called `schema.sql` in the folder `sentimental-connect`. Design the schema to represent users, schools, universities, companies, and connections. Ensure the database can store relevant information for each entity, such as user details, school information, company details, and connection data. Use appropriate MySQL data types and consider normalization for efficiency. Include SQL statements in `schema.sql` to create the required tables and relationships. Test your database with sample data provided in the problem statement. Use MySQL commands to start and connect to the MySQL server, create a new database named `linkedin`, and switch to the `linkedin` database. Check your schema's correctness with `check50 cs50/problems/2024/sql/sentimental/connect`. | week 6 | [Don’t Panic! (Sentimental) with Python](https://github.com/mejbass/CS50-Intro-to-Databases-Using-SQL/tree/master/Week%206/Don't%20Panic%20(Sentimental)%20in%20Python) |Write a Python program `hack.py` using CS50 library to connect to SQLite database `dont-panic.db`. Prompt user for a new password and use a prepared statement to update the administrator's password in the database. ### Final Project | Final Project | Description | | ------------- | ------------- | |[AnimeHub Database](Final%20Project) | AnimeHub, created by Bassam Mejlaoui, is a comprehensive anime database providing users with detailed information on anime titles, characters, studios, episodes, genres, and voice actors. Users can browse, search, and contribute to the database, while the project intentionally excludes user-specific data and detailed reviews, focusing on being a centralized resource for anime enthusiasts, For a quick overview, watch the [AnimeHub Overview](https://youtu.be/aZ7WCZzZUg0). Feel free to explore the repository for a comprehensive overview of the CS50SQL 2024 curriculum, whether you're a current student seeking reference or someone interested in delving into database concepts.

近期下载者：

相关文件：

评论：[我要评论] [举报此文件]

收藏者：