Goal

Creating and Loading Databases

Create Database: Set up a new database. Load the Dump Files: Import data into the database from provided dump files.

Data Querying

Joins: Use joins to query data across multiple tables, combining related information. Sub-Queries: Use sub-queries to filter and retrieve data.

Writing and Executing SQL Queries

List School Information: Query to list school names, community names, and average attendance for communities with a hardship index of 98. List Crime Information: Query to list all crimes that took place at schools, including case number, crime type, and community name.

Creating and Querying Views

Create Views: Create a view to present selected columns from a table with new names, ensuring user privacy by obscuring original data fields. Query Views: Write and execute SQL statements to retrieve specific data from the created view.

Writing and Running Stored Procedures

Create Stored Procedure: Develop a stored procedure to update score fields and calculate corresponding icon settings. Handle Transactions: Update the stored procedure to use transactions for ensuring data integrity, rolling back changes when invalid data is provided.

Using Transactions

Rollback and Commit: Implement transactions in the stored procedure to handle invalid input and commit valid changes.

Result

This project improve the ability to work with relational databases, focusing on SQL queries, views, stored procedures, and transactions.

Comprehensive Data Queries

Successfully retrieve and combine data from different tables.

Effective Data Management

Create views and stored procedures to manage data privacy and integrity.

Robust Error Handling

Use transactions to ensure data consistency and handle invalid inputs appropriately.