This repository contains my journey through SQL challenges on HackerRank, where I earned a 5-star rating in SQL programming. Each solution demonstrates different aspects of SQL querying, from basic SELECT statements to advanced window functions, aggregations, and complex joins. These solutions are compatible with MySQL, PostgreSQL, and SQL Server.
5 Star SQL Badge on HackerRank
SelectAll.sql- Basic SELECT statement for all columnsSelectByID.sql- Filtering with WHERE clause using specific IDSelectQueryI.sql- Multiple conditions with AND operatorSelectQueryII.sql- Selecting specific columns with conditionsJapaneseCitiesNames.sql- Filtering by country codeJapaneseCitiesAttributes.sql- All attributes for specific country
RevisingAggregationsTheCountFunction.sql- COUNT function with conditionsRevisingAggregationsTheSumFunction.sql- SUM function with filteringRevisingAggregationsAverages.sql- AVG function for statistical analysisAveragePopulations.sql- ROUND function with AVGJapanPopulations.sql- SUM aggregation with country filterPopulationDensityDifference.sql- MAX and MIN functions
WeatherObservationStation1.sql- Basic multi-column selectionWeatherObservationStation2.sql- ROUND function with SUMWeatherObservationStation3.sql- Modulo operator for even IDsWeatherObservationStation4.sql- COUNT vs COUNT DISTINCTWeatherObservationStation5.sql- LENGTH function with ORDER BY and LIMITWeatherObservationStation6.sql- LIKE operator for vowel patternsWeatherObservationStation7.sql- SUBSTR function for ending charactersWeatherObservationStation8.sql- LEFT and RIGHT functionsWeatherObservationStation9.sql- NOT IN with SUBSTR for consonantsWeatherObservationStation10.sql- String ending conditionsWeatherObservationStation11.sql- OR conditions with string functionsWeatherObservationStation12.sql- Complex NOT IN conditions
WeatherObservationStation13.sql- TRUNCATE with range conditionsWeatherObservationStation14.sql- MAX with TRUNCATE functionWeatherObservationStation15.sql- Subquery with MAX and conditionsWeatherObservationStation16.sql- MIN with ROUND and rangeWeatherObservationStation17.sql- Subquery with MIN in WHERE clauseWeatherObservationStation18.sql- Manhattan distance calculationWeatherObservationStation19.sql- Euclidean distance with SQRT and POWERWeatherObservationStation20.sql- Median calculation using subqueries
EmployeeNames.sql- Basic ORDER BY for sortingEmployeeSalaries.sql- Multiple conditions with ANDTopEarners.sql- MAX function with subquery and COUNTNewCompanies.sql- Complex JOIN with GROUP BY and COUNT DISTINCT
HigherThan75Marks.sql- SUBSTR for sorting by last 3 charactersThePADS.sql- CONCAT function with string formattingTheBlunder.sql- REPLACE function with mathematical operations
TypeofTriangle.sql- Nested CASE statements for triangle classificationBinaryTreeNode.sql- CASE with subqueries for tree node classificationOccupation.sql- PIVOT operations with ROW_NUMBER() window function
- SELECT, FROM, WHERE clauses
- Filtering with conditions (=, >, <, >=, <=, <>)
- Logical operators (AND, OR, NOT)
- DISTINCT for unique values
LIKEpatterns with wildcards (%, _)SUBSTR()/SUBSTRING()for character extractionLEFT()andRIGHT()functionsCONCAT()for string concatenationREPLACE()for character substitutionLENGTH()for string length
COUNT(),SUM(),AVG(),MAX(),MIN()GROUP BYfor data groupingHAVINGclause for filtered aggregations
ROUND(),TRUNCATE(),CEIL(),FLOOR()SQRT(),POWER()for calculations- Mathematical operators (+, -, *, /, %)
- Subqueries and correlated subqueries
- Window functions (
ROW_NUMBER(),OVER()) CASEstatements for conditional logicPIVOToperations for data transformationJOINoperations (LEFT JOIN)
- MySQL syntax and functions
- PostgreSQL compatibility
- SQL Server T-SQL features
- Standard SQL compliance
-
Clone the repository:
git clone https://github.com/yourusername/SQL-with-Problem-Solving.git cd SQL-with-Problem-Solving -
Execute any solution:
-- Copy the SQL code from any .sql file -- Run it in your preferred SQL environment (MySQL, PostgreSQL, SQL Server)
-
Example execution:
-- From SelectAll.sql SELECT * FROM CITY;
| Category | Problems Solved | Difficulty | Key Concepts |
|---|---|---|---|
| Basic Select | 6 | 🟢 Easy | SELECT, WHERE, filtering |
| Advanced Select | 5 | 🟡 Medium | String functions, patterns |
| Aggregation | 6 | 🟡 Medium | COUNT, SUM, AVG, GROUP BY |
| Weather Stations | 20 | 🟡 Medium | String manipulation, math functions |
| Join Operations | 1 | 🔴 Hard | LEFT JOIN, GROUP BY |
| Advanced Topics | 7 | 🔴 Hard | CASE, subqueries, window functions |
- ✅ Proficiency in SQL query writing and optimization
- ✅ String manipulation and pattern matching expertise
- ✅ Advanced aggregation and mathematical functions
- ✅ Subquery design and correlated queries
- ✅ Window functions and analytical queries
- ✅ Database schema understanding and relationships
- ✅ Performance optimization techniques
- ✅ Cross-platform SQL compatibility
- Basic and advanced SELECT statements
- Multi-table queries with JOINs
- Filtering and sorting large datasets
- Statistical calculations (averages, medians, ranges)
- Distance calculations (Manhattan, Euclidean)
- Pattern recognition and classification
- Text pattern matching and extraction
- Character-based filtering and sorting
- String formatting and manipulation
- Employee hierarchy analysis
- Geographic data processing
- Educational grading systems
- HackerRank Profile: Your HackerRank Profile
- LinkedIn: Your LinkedIn Profile
- GitHub: Your GitHub Profile
This project is licensed under the MIT License - see the LICENSE file for details.
Feel free to fork this repository and submit pull requests for any improvements or additional solutions! Contributions for SQL optimization or alternative approaches are welcome.
⭐ If you found this repository helpful, please star it! ⭐
Made with ❤️ by Ak-Rajak