Project-1
Sailor Analysis in SQL
The Problem/Opportunity:
This project focuses on analyzing sailor and charter data using SQL. The objective was to design a relational database and extract business insights through SQL queries.
Step 1: Designed the database schema based on the project requirements.
Designed a relational database schema. Entities were identified and relationships between tables were defined. Primary keys and foreign keys were carefully selected to ensure data integrity and efficient querying
Step 2: Created tables and defined primary and foreign keys.
Using SQL Server Management Studio (SSMS), I created the database and all required tables. Constraints such as primary keys and foreign were implemented to enforce relationships between tables.
Step 3: Inserted and validated data using SQL scripts.
The provided datasets were inserted into the database using SQL scripts. Special attention was given to data types, null values, and formatting to ensure accurate data storage.
Step 4: Performed data quality checks and consistency validation.
After data insertion, validation queries were executed to verify record counts, detect missing values, and ensure referential integrity.
Step 5: Data Exploration.
Exploratory SQL queries were written to better understand the structure and distribution of the data.
Step 6: Wrote SQL queries to answer business-related questions.
SQL queries were developed to answer specific business questions defined in the project. These queries involved joins, aggregations, filtering, and subqueries to extract meaningful insights from the data.
Step 7: Optimized queries using joins, subqueries, and aggregations.
Where necessary, queries were optimized for better performance by refining joins and minimizing unnecessary calculations.
Step 8: Insight Extraction.
The query results were analyzed and translated into business insights. Rather than focusing only on numbers, the findings were interpreted to explain trends and behaviors within the data.
Step 9: Documentation.
Finally, the project was documented by organizing SQL scripts, summarizing results and describing the analysis process.
Key Insights:
Identified sailors who have chartered specific boats and those who never chartered certain boats, highlighting different usage behaviors
Analyzed sailor activity based on boat characteristics, such as color, revealing preferences for specific types of boats
Used pattern matching to identify sailors based on name characteristics, demonstrating advanced SQL filtering
Combined data from multiple tables to provide complete charter history, including sailor names, boat IDs, and charter dates.
The Outcome:
Created a project that clearly demonstrates analytical thinking and SQL proficiency, making it suitable for inclusion in a professional GitHub portfolio.