MS_SQL Projects Repository

Repository Overview

Welcome to my MSSQL projects repository. This collection showcases a range of SQL projects that demonstrate my proficiency in SQL query writing, table creation, data manipulation, and complex querying. Each project is designed to solve different real-world problems, providing insights into various SQL techniques and best practices.

1. Analyzing Top Customers by Order Volume

• Objective: Retrieve company details and count of lead managers, senior managers, managers, and employees.
• Key Features: Table creation, data insertion, hierarchical querying.
• Key Insights:
  • Demonstrated proficiency in managing and querying hierarchical data.
  • Highlighted the ability to aggregate and analyze data across related tables.

2. Finding the Second Highest Salary Using Aggregate Function

• Objective: Retrieve the second highest salary from the employee table without using the LIMIT clause.
• Key Features: Table creation, data insertion, data retrieval, aggregation.
• Key Insights:
  • Showcased the ability to write complex queries using subqueries and aggregate functions.
  • Demonstrated proficiency in SQL table creation and data manipulation.

3. Querying City Names Without Vowels

• Objective: Retrieve city names from a table that do not end with vowels.
• Key Features: Table creation, data insertion, pattern matching.
• Key Insights:
  • Highlighted the use of SQL LIKE operator for pattern matching.
  • Demonstrated the ability to filter data based on specific conditions.

4. Retrieving Employee Names by Department Count

• Objective: Identify employees who have worked in at least three different departments.
• Key Features: Table creation, data insertion, filtering and aggregation.
• Key Insights:
  • Highlighted the use of COUNT and GROUP BY to filter employees based on department count.
  • Showcased the ability to write complex queries involving multiple conditions.

5. Analyzing Employee Data for Promotions Using Data Retrieval and Filtering

• Objective: Identify employees eligible for promotions based on their duration with the company and salary increments.
• Key Features: Table creation, data insertion, data transformation, complex querying.
• Key Insights:
  • Showcased the use of SQL window functions and common table expressions (CTEs).
  • Demonstrated the ability to write advanced queries for data analysis.

6. Identifying Employees with Above-Average Salaries Using Window Functions

• Objective: Identify employees who have salaries higher than the average salary of their respective departments.
• Key Features: Table creation, data insertion, data retrieval, window functions.
• Key Insights:
  • Highlighted the use of window functions to calculate department averages.
  • Demonstrated the ability to compare individual values to aggregate results.

7. Analyzing Company Structure with Hierarchical Data

• Objective: Count the number of lead managers, senior managers, managers, and employees for each company.
• Key Features: Table creation, relationship management, data retrieval, hierarchical querying.
• Key Insights:
  • Showcased the ability to define multiple related tables with foreign key relationships.
  • Demonstrated proficiency in hierarchical querying and data aggregation.

8. Exploring SQL Join Operations

• Objective: Demonstrate the use of various SQL join operations to retrieve and analyze data from multiple tables.
• Key Features: Table creation, data insertion, join operations.
• Key Insights:
  • Highlighted the use of different types of joins (Left Join, Right Join, Inner Join, Full Outer Join, Self Join, Cartesian Join).
  • Showcased the ability to analyze relationships between tables through join operations.

9. Managing Data with Local and Global Temporary Tables

• Objective: Create, use, and delete both local and global temporary tables in SQL Server.
• Key Features: Temporary table creation, data insertion, table deletion.
• Key Insights:
  • Demonstrated the use of local and global temporary tables for managing intermediate data.
  • Highlighted the process of inserting, verifying, and deleting temporary tables.

10. Converting an ER Diagram to Relational Tables

- **Objective**: Convert an Entity-Relationship (ER) diagram into relational tables in SQL Server.
- **Key Features**: Table creation, relationship management, data integrity.
- **Key Insights**:
  - Showcased the ability to define multiple related tables with primary and foreign key relationships.
  - Demonstrated proficiency in ensuring data integrity and consistency.

Conclusion

This repository highlights my ability to apply advanced SQL techniques to solve diverse problems. Each project demonstrates a strong foundation in SQL query writing, table creation, data manipulation, and complex querying. I am confident that these projects reflect my readiness for a challenging role in data management and SQL development.

Feel free to explore each project and reach out if you have any questions or feedback!